Database migrations

The canonical, detailed runbook is packages/db/MIGRATIONS.md. This skill is a short safety wrapper; if anything here seems incomplete, trust that file and the current package.json scripts.

Also load the learnings skill. It carries the incident rules that apply while WRITING a migration — above all: never backfill data inside a single-transaction .sql migration (2026-08-10 v0.12.7 prod outage; enforced by the backfill-safe lint guard, but the rule explains what to build instead: a batched .concurrent.ts pass or an out-of-band runbook).

Current model

• Engine: node-pg-migrate, invoked by packages/db/scripts/migrate.ts.
• Schema source: packages/db/src/schema/kortix.ts for the Drizzle-modeled kortix schema.
• Migration files: immutable SQL files in packages/db/migrations/, named with a 17-digit UTC timestamp (YYYYMMDDHHMMSSmmm_slug.sql).
• Applied-state table: kortix_migrations.pgmigrations (node-pg-migrate tracks migration names; it does not checksum file contents).
• Deploy: deploy-dev.yml and deploy-prod.yml run pnpm --filter @kortix/db migrate before the ECS Fargate rollout (infra/scripts/ecs-deploy.sh). Migrations always land before new code.

Rules

1. Never edit a migration that has been applied anywhere. Write a new migration.
2. Never use drizzle-kit push or hand-apply production DDL outside the migration flow.
3. Review every generated SQL file before it reaches a shared DB.
4. Prefer expand/contract for destructive or compatibility-sensitive changes. Any migration that drops/alters a constraint, unique index, column, or enum value needs a -- mixed-version-safe: <...> (or -- enum-value-checked: <...> for ADD VALUE) annotation, or CI fails it — see MIGRATIONS.md's worked examples (the 20260713220001000 unique-index-drop incident and the sandbox_provider "platinum" enum-drift incident).
5. Adding an index/dropping an index on an EXISTING table: use pnpm migrate:create <slug> --concurrent (the .concurrent.ts escape hatch), never a plain CREATE INDEX — see MIGRATIONS.md "Roll-forward safety" for why plain SQL migrations structurally can't run CONCURRENTLY here, and the multi-statement pgm.sql() footgun to avoid.
6. Prod is live: show the exact SQL and get explicit go-ahead before any manual prod migration action.

Commands from repo root

Command	What it does
pnpm migrate	Apply pending migrations using node-pg-migrate.
pnpm migrate:status	List pending migrations; read-only (reads the ledger, never runs a migration); exits non-zero if any are pending.
pnpm migrate:create <slug>	Scaffold a hand-written SQL migration with the house-rules template.
pnpm migrate:create <slug> --concurrent	Scaffold the .concurrent.ts CONCURRENTLY escape hatch.
pnpm migrate:generate <slug>	Generate SQL from a kortix.ts schema change and update the Drizzle snapshot.
pnpm migrate:fake	Mark pending migrations as applied without running them (for baselining existing envs).
pnpm --filter @kortix/db lint	Full local check: filename/order rules + mixed-version/enum-value guard + squawk (deterministic Postgres zero-downtime linter). Run before every push touching packages/db/migrations.
pnpm migrate:lint	Just the filename/order/mixed-version/enum-value checks (no squawk, no network).

Safe schema-change loop

1. Edit packages/db/src/schema/kortix.ts for schema-shape changes, or create a hand-written migration for data/RLS/functions/grants/custom SQL, or a --concurrent migration for index create/drop.
2. Run pnpm migrate:generate <slug> or pnpm migrate:create <slug> [--concurrent].
3. Read the SQL top-to-bottom. Stop on accidental DROP, unsafe type changes, immediate NOT NULL on populated tables, non-idempotent backfills, or a missing mixed-version/enum-value annotation.
4. Run pnpm --filter @kortix/db lint and the relevant package tests/checks.
5. Apply to a local/throwaway DB before dev/prod when the change is non-trivial.
6. Commit both the migration SQL and any generated Drizzle snapshot changes.

See packages/db/MIGRATIONS.md for the full baseline, preview/prod behavior, self-hosting command, and failure drill.