Database migrations

Schema lives in apps/api/src/db/schema.ts. Migrations live in apps/api/src/db/migrations/. Both must stay in sync — the CI guard described below catches drift.

How it works

We use Drizzle Kit to generate migrations from the TypeScript schema, plus a hand-written file for things Drizzle can't express (RLS policies, roles, functions).

File	Source	Purpose
0000_initial.sql	drizzle-kit generate	All tables + indexes + FKs from schema.ts
0001_rls_policies.sql	Hand-written	RLS roles, current_account_id() function, FORCE ROW LEVEL SECURITY on every account-scoped table, per-table account-iso policy
meta/_journal.json	Drizzle	Ordered ledger of applied migrations
meta/<n>_snapshot.json	Drizzle	Schema state after each migration — used to diff against schema.ts for the next generate

__drizzle_migrations (in the Postgres drizzle schema) is the on-disk ledger of which migrations have actually run on this database. Hash-tracked: editing an applied migration is a CI failure waiting to happen.

Adding a migration

# 1. Edit apps/api/src/db/schema.ts — add a column, table, index, etc.

# 2. Generate the migration. The --name slug shows up in the filename.
cd apps/api
DATABASE_URL=postgres://race:race@localhost:6160/race \
  pnpm exec drizzle-kit generate --name "<short_descriptor>"

# 3. Inspect the generated SQL. If Drizzle picked anything weird (a
# rename it didn't detect, a destructive ALTER), fix it by hand and
# re-run with --custom if you need a starting template.

# 4. Apply locally:
make migrate

# 5. Commit BOTH the .sql file AND the updated meta/_journal.json
# AND the new meta/<n>_snapshot.json.

Things Drizzle can't generate

If your change involves any of these, edit the migration file by hand or add a separate <n>_hand_<descriptor>.sql:

• Postgres roles (CREATE ROLE, GRANT, BYPASSRLS)
• Functions (CREATE FUNCTION, CREATE OR REPLACE)
• RLS policies (CREATE POLICY, ALTER TABLE … ENABLE/FORCE ROW LEVEL SECURITY)
• Custom triggers
• Data backfills (UPDATE/INSERT against existing data) — these don't belong in migrations long-term, but a one-shot backfill is acceptable
• Renames — Drizzle often emits drop+create instead of rename. For a column rename, write ALTER TABLE … RENAME COLUMN … by hand.

For hand-written migrations, after creating the file:

1. Append an entry to meta/_journal.json: { "idx": <next>, "version": "7", "when": <ms-since-epoch>, "tag": "<filename without .sql>", "breakpoints": true }
2. There's no snapshot file for hand-written migrations — that's fine, the next drizzle-kit generate will catch up.

Running migrations

Where	Command
Local	make migrate
Prod (via deploy script)	scripts/deploy.sh runs migrate as step 7
Prod (manual)	ssh $HOST "docker exec race-api pnpm --filter @race/api db:migrate"

The migrate script is idempotent — re-running it on a fully-migrated DB is a no-op.

Wiping a database

# Local — wipe schema, keep volume + roles
docker exec race-postgres psql -U race -d race -c \
  "DROP SCHEMA public CASCADE; \
   CREATE SCHEMA public; \
   GRANT ALL ON SCHEMA public TO race, race_admin, public; \
   DROP SCHEMA IF EXISTS drizzle CASCADE;"
make migrate
make seed

# Prod — same, but via SSH
ssh $HOST 'docker exec race-postgres psql -U race -d race -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO race, race_admin, public; DROP SCHEMA IF EXISTS drizzle CASCADE;"'
ssh $HOST 'docker exec race-api pnpm --filter @race/api db:migrate'
ssh $HOST 'docker exec race-api pnpm --filter @race/api db:seed'

Common pitfalls (we've hit all of these)

• Two devs generate the same migration number simultaneously. The lower-number one always wins; the loser must regenerate after rebasing.
• Hand-edited an already-applied migration. Drizzle's hash check catches it — the next migrate aborts. Solution: revert the edit, write a NEW migration that does what you wanted.
• Forgot to commit meta/_journal.json. Migration file is on disk but Drizzle skips it because the journal doesn't list it. Always git status after generating.
• Drizzle generated a destructive rename as drop+create. Open the .sql before applying. If you see DROP TABLE … ; CREATE TABLE … for what should be a rename, edit it to ALTER TABLE … RENAME … and update the snapshot.
• Snapshot file out of sync with schema. Happens if you hand-write SQL without regenerating. The next drizzle-kit generate produces a confused diff. Fix: nuke the migrations folder and regenerate from scratch (we did this once — see the 2026-06-06 entry in active_context.md).

CI guard

pnpm --filter @race/api exec tsc --noEmit ensures schema.ts compiles. We don't yet have a drizzle-kit generate --check step in CI — adding it would catch "PR bumped schema.ts but didn't ship a migration". Tracked as a follow-up.