Database and Migrations

Verified against starter commit 2a1a04a.

By the end of this page, you will know which database to point each environment at, how to turn a schema edit into reviewable SQL, and how to apply it separately from an application release.

The starter deliberately supports PostgreSQL through Drizzle rather than offering several database adapters. Locally, pnpm setup can run PostgreSQL in Docker; in production, choose any compatible managed or self-hosted PostgreSQL service and set DATABASE_URL. 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:lint
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. This is the shipped safety choice: database and app releases can be observed, retried, and rolled out independently.

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

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.
• Identity references remain logical because account erasure uses irreversible subject tombstones. High-value task-to-ledger, job, and output-file relationships are now foreign-key constrained; lifecycle services still own deletion order.
• Reservations also use a PostgreSQL exclusion constraint to prevent overlapping confirmed/held slots.

Choices to Make Before Production

• Choose who runs migrations: a CI release job or an operator. Do not run them from every web instance.
• Choose a backup and restore policy appropriate to your data. The runner coordinates migrations; it does not replace backups.
• Run db:integrity before and after relationship changes. The migration linter rejects destructive drops, unsafe renames, unbounded rewrites, and blocking uniqueness unless a reviewable exception explains the rollout.
• Prefer expand/contract migrations for zero-downtime releases. A destructive one-step rename is simpler, but it couples schema and code deployment and makes rollback unsafe.

You are ready to deploy when pnpm db:check:prod reports the expected pending set, the backup is current, the migration works against a staging copy, and both old and new application versions can run during the rollout.

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

Related: Review Architecture and Error Contracts for data-layer boundaries and Authentication and Admin for the identities those records belong to.