Database migrations

FitKit uses Drizzle ORM. Migrations are SQL files committed to libs/db/drizzle/, tracked in libs/db/drizzle/meta/_journal.json, and applied by the drizzle-kit migrator.

This runbook is the single source of truth for the migration workflow. Diverging from it has caused at least one production outage (2026-04-18).

Hard rules

1. Never run pnpm db:push. It bypasses the journal and skews dev vs prod. The script is in package.json only because Drizzle’s docs reference it; treat it as deleted.
2. Never run pnpm db:migrate without explicit approval from the owner. Dev and prod both hold real data.
3. The _journal.json when values must be strictly monotonic. See the monotonic-when section below.

Standard workflow

# 1. Edit the schema
$EDITOR libs/db/src/lib/schema/<domain>.ts
 
# 2. Generate the SQL migration
pnpm db:generate
 
# 3. Review the generated SQL — do NOT blindly trust drizzle-kit
git diff libs/db/drizzle/
 
# 4. Apply locally
pnpm db:migrate
 
# 5. Verify the change in Drizzle Studio
pnpm db:studio

What pnpm db:generate does

1. Compares libs/db/src/lib/schema/*.ts against the current snapshot.
2. Emits a new SQL file in libs/db/drizzle/0XXX_<slug>.sql.
3. Appends to libs/db/drizzle/meta/_journal.json an entry like:

{
  "idx": 41,
  "version": "7",
  "when": 1748000000000,
  "tag": "0041_<slug>",
  "breakpoints": true
}

The when is a millisecond timestamp at generation time. This timestamp is load-bearing — see below.

Monotonic when: the 2026-04-18 outage

Drizzle’s migrator picks pending migrations with:

SELECT ... FROM __drizzle_migrations
WHERE created_at > (
  SELECT COALESCE(MAX(created_at), 0) FROM __drizzle_migrations
)

If you merge a branch whose migration was generated before main’s most recent migration was generated, your migration’s when is smaller than the latest applied one. The migrator silently skips it — pnpm db:migrate reports success, but your DDL never ran.

The 2026-04-18 outage happened exactly this way: a branch added a NOT NULL column with no backfill; the migration silently skipped in prod; the next deploy’s code expected the column and crashed on startup.

Symptoms

• pnpm db:migrate says “migrations applied successfully” but the schema is unchanged.
• API boot fails with column "X" does not exist.
• _journal.json has an entry whose when is less than MAX(when) of previously applied entries.

Mitigation when merging a branch with a stale migration

1. After rebasing/merging onto main, regenerate the migration so its when bumps:

   rm libs/db/drizzle/00XX_<your_slug>.sql
   git checkout libs/db/drizzle/meta/_journal.json  # revert your entry
   pnpm db:generate

2. Confirm when in the new entry exceeds the previous entry’s.
3. Apply.

Destructive changes (drops, type changes, NOT NULL on existing data)

pnpm db:generate will produce the SQL but will not warn you about data loss. Before merging:

• Renames: split into two migrations (add new column → backfill → drop old column).
• New NOT NULL column: add nullable → backfill → enforce NOT NULL in a follow-up.
• Type narrowing: same pattern.
• Drops: confirm no code references the table/column; consider archiving data before dropping.

There is no automatic backfill mechanism — write data backfills as a separate script in scripts/ or as part of a deploy.

Production deploys

The application does NOT run migrations automatically on boot. Migrations are applied by the deployer (currently the owner) as a separate step before rolling the API. The Railway deploy pipeline runs pnpm db:migrate against the production DB before promoting the new container.

Until audit logging lands (FIT-20), there is no auditable record of who ran a migration when, beyond __drizzle_migrations.created_at.

Pooler vs direct connection

• Application reads/writes go through PgBouncer (DATABASE_URL).
• Migrations bypass the pooler via DIRECT_DATABASE_URL (set in libs/db/drizzle.config.ts). DDL holds server connections for long durations and would starve the pooler.

Rollback

There is no down-migration support. Drizzle generates forward-only SQL. Rollback strategies:

1. Same-day: write a follow-up forward migration that reverses the schema change. Apply it.
2. Mid-deploy data loss: restore from the latest managed-DB snapshot (Railway / Neon / Supabase backup), then forward-migrate again.

Plan migrations so that the new code is compatible with both the pre-migration and post-migration schema for at least one deploy cycle. That way a partial rollout isn’t catastrophic.

Migration review checklist

Before merging any PR that touches libs/db/drizzle/:

• The generated SQL matches the schema diff (no unexpected drops).
• _journal.json when is greater than the previous entry’s.
• No NOT NULL added to a populated column without a backfill.
• No column rename without the two-step pattern above.
• No FK added to a column that may have orphan rows.
• The PR description explains the change in plain English (for future you).
• If destructive: a clear rollback plan in the PR.