Database and Migrations

Synced with starter commit 7d452a6.

The starter uses PostgreSQL and Drizzle. src/db/schema.ts is the schema source of truth, while committed SQL under src/db/migrations/ is the deployable history. Only files under src/models/** may call db().

Local Workflow

pnpm setup creates separate development and test databases. The test database name must contain test; the safety harness refuses to truncate any other database.

After changing the schema:

pnpm db:generate
pnpm db:migrate
pnpm test:db:setup
pnpm test:db

Commit the generated SQL and meta/_journal.json with the code. Do not edit an already deployed migration.

Production Workflow

Migrations never run automatically during application deployment:

pnpm db:check:prod
pnpm db:migrate:prod

The production runner is non-interactive, takes a PostgreSQL advisory lock, and verifies migration checksums. Back up first and use expand/contract changes so the currently deployed code and the next version can both run while schema and application releases are separated.

Data Conventions

• Numeric id values are internal; public APIs use uuid.
• Money uses integer minor units plus a currency code.
• Tenant-owned rows carry org_uuid; every model query must scope by it.
• Idempotency keys and transaction numbers have database uniqueness constraints.
• The starter intentionally avoids broad foreign-key coupling; services and lifecycle policies therefore own deletion order and referential checks.
• Reservations also use a PostgreSQL exclusion constraint to prevent overlapping confirmed/held slots.

Read docs/database.md and DEPLOYMENT.md in the starter before changing production data contracts.