Versioned forward-only database migrations with a schema version check at boot and in CI #583

Context

backend/src/db/migrate.ts replays all of db/schema.sql on every run. The file mixes CREATE ... IF NOT EXISTS with ALTER, UPDATE and DO convergence blocks, and it is not safely re-runnable. The DO block looks up invoices_payment_tx_hash_unique in pg_constraint, but creates it with CREATE UNIQUE INDEX, which never registers a constraint. The file also creates a second index, uq_invoices_payment_tx_hash. InvoiceService.markAsPaid maps 23505 to PaymentClaimError only for that name, so a database where the other index fires returns a 500 instead of the typed claim rejection. CI has no Postgres service, so invoice-postgres.integration.test.ts is skipped. deploymentReadiness hard-codes storageReady: true, and payment_events.created_at and transactions.processed_at are TIMESTAMP without a time zone.

Goal

Schema changes ship as numbered, checksummed migration files. Each is applied once, in its own transaction, under a lock. A server whose database is behind the bundled schema reports not-ready.

Work

• Split db/schema.sql into db/migrations/0001_baseline.sql (the current end state) and later files. Existing databases adopt the baseline through a detection step instead of a replay
• Add a schema_migrations table (version, name, checksum, applied_at). The runner applies pending files under pg_advisory_lock, one transaction per file, and errors if an applied file's checksum changed
• Write migration 0002 to:
  • keep one tx-hash unique index and drop the other
  • convert payment_events.created_at and transactions.processed_at to TIMESTAMPTZ, treating stored values as UTC
  • add the PAID-row CHECK from docs/postgres-cutover-draft.sql, with a pre-check that fails loudly on violating rows
• Map 23505 in InvoiceService by the single remaining index name, and assert it in a test
• In Postgres mode, readiness checks connectivity and that the latest applied version equals the latest bundled one
• Add a postgres service to the backend CI job, run migrate twice, and run the Postgres integration suites unskipped
• Point db/seed.ts, the cutover script and the cutover docs at the runner, updating the existing docs in place

Expected diff

15–20 files. A migration runner and migration files, the service constraint mapping, readiness, the CI job, cutover tooling and the Postgres tests.

Areas involved

db/schema.sql, db/seed.sql, backend/src/db/migrate.ts, backend/src/db/seed.ts, backend/src/config/database.ts, backend/src/services/invoice.service.ts, backend/src/services/cutover.service.ts, backend/scripts/cutover.ts, backend/src/health.ts, backend/src/config/runtime.ts, backend/package.json, .github/workflows/ci.yml, backend/tests/invoice-postgres.integration.test.ts, backend/tests/postgres-restart-persistence.test.ts, backend/tests/health-readiness.test.ts, docs/POSTGRES_CUTOVER.md, docs/postgres-cutover-draft.sql, db/migrations/ (new), backend/src/db/migration-runner.ts (new), backend/tests/migration-runner.test.ts (new).

Done when

• npm run db:migrate run twice on an empty database succeeds, and the second run applies nothing
• A database built from today's schema.sql adopts the baseline and upgrades with its rows intact, shown in a fixture test
• Editing an applied migration fails the runner with a checksum error, and two concurrent runs never apply the same version
• /api/ready returns 503 in Postgres mode while a migration is pending
• The CI backend job runs the Postgres integration suite without skipping it
• Settling one tx hash on two invoices returns the typed claim error on a migrated database