Skip to content

Database schema of record

The repository must be able to rebuild production's schema from nothing. Until 2026-08-07 it could not, and the gap was invisible because production already had the missing objects.

How a database is built

# 1. base schema
bun -e "import { setupDatabase } from './src/utils/setupDb.ts'; await setupDatabase()"
# 2. the ordered migration chain
bun run --cwd apps/api migrate:all

setupDatabase() creates the base topology. migrate:all runs scripts/runMigrations.ts, which executes every entry in scripts/migrationChain.ts in order and records each in the schema_migrations ledger. Every migration is idempotent, so the chain is safe to replay; it runs on every deploy.

The chain currently applies 25 migrations. Verify replay safety by running migrate:all two more times against the same database — the count must not change and nothing must fail.

The 2026-08-07 drift

Diffing production against a database built the way above found production carrying objects nothing in the repository created:

  • 4 tables: customer_merchant_summary, intents, merchant_app_sessions, smart_nudges
  • ~30 columns, including merchants.phone, merchants.account_status (an account_status_enum), customers.ens_name, products.status, payment_requests.merchant_id, rewards.merchant_id, and nine transactions.adyen_* / terminal_* columns
  • 1 function + trigger: update_customer_merchant_summary() and trigger_update_customer_merchant_summary

Shipped code reads them. On a fresh database that meant:

Endpoint Failure
GET /menu/merchant/:id — the consumer's entry point menuController selects m.phone in three places → 500
GET /customers customerService joins customer_merchant_summary in eight places → 500

Production was fine. Every new environment, new developer machine, and disaster-recovery rebuild was not.

customer_merchant_summary was the worst case: the table, its maintaining function, and its trigger were all absent, so creating only the table would have left every customer's order count and spend permanently at zero.

migrations/production_schema_reconciliation.sql closes this. It is additive and idempotent — on production every guard is already satisfied and it is a no-op.

Accepted divergence

Two differences remain by decision, not omission:

  • merchant_notification_settings.id — production has a surrogate integer primary key; this repository keys the table on merchant_id. Both satisfy the application, and converting would mean a primary-key change on a live table.
  • spatial_ref_sys — PostGIS. The extension is not available locally; the geospatial index falls back to basic indexing with a logged warning.

Anything else appearing in a diff is drift and should be reconciled.

The rollup trigger carries a deliberate fix

Production's update_customer_merchant_summary() reads NEW unconditionally while also firing on DELETE. NEW is NULL for a delete, so the lookup matches nothing and the rollup is silently left stale — measured on a rebuilt schema, deleting a customer's only order left the summary still reporting total_orders = 1. It does not raise; do not repeat that claim without checking.

The version in this repository recomputes from OLD on delete and drops the row when the last order goes.

Verifying a rebuild

-- tables production has that a fresh build does not
SELECT table_name FROM information_schema.tables
 WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY 1;

-- columns, for the same comparison
SELECT table_name, column_name FROM information_schema.columns
 WHERE table_schema='public' ORDER BY 1,2;

Compare the two sorted outputs. The only expected differences are the two accepted divergences above.

Integration cover lives in apps/api/src/__tests__/integration/, gated behind SETUP_DB_INTEGRATION_TESTS=1 plus DATABASE_URL (they skip silently otherwise):

  • setupDbFresh.integration.test.ts — builds an isolated database, runs the chain twice, and asserts the ledger count against MIGRATIONS.length rather than a hardcoded number, which had already drifted once
  • productionSchemaReconciliation.integration.test.ts — asserts the reconciled tables and columns exist and that the rollup stays correct across insert and delete
  • readyNotificationTrigger.integration.test.ts — drives a ready transition through PostgreSQL; catches the 42883 class of trigger bug that no unit test can reach

PAYMENT_REQUEST_CONCURRENCY_TESTS=1 additionally un-skips the payment-request lifecycle and settlement-arbitration suites.

Local PostgreSQL

Homebrew initdb/pg_ctl are available. A unix socket path under a scratch directory can exceed the 103-byte limit, so start on TCP:

initdb -D "$PGDATA" -U postgres --auth=trust
pg_ctl -D "$PGDATA" -o "-p 55432 -k /tmp -h 127.0.0.1" -l "$PGDATA/server.log" start

Migration scripts must use pg.Client, not Neon's HTTP driver — the HTTP driver issues the file as a prepared statement and PostgreSQL rejects multi-statement scripts.