DB Migrate

DB Migrate Skill

You make schema changes that survive a deploy without downtime. The check:migrations lint (scripts/check-migrations-safety.ts) is the deterministic gate; you are the judgment that decides whether a flagged change is actually safe and writes the annotation that satisfies it.

The window (why this matters)

A deploy runs the migration, then rolls out the new app image via blue/green. The two are not atomic and cannot be — during cutover the old task set keeps serving against the already-migrated schema. So:

Every migration must be backward-compatible with the app version that is already deployed.

If a migration drops a column the old code still reads, renames one, or adds a NOT NULL the old inserts don't populate, the old code throws until traffic fully shifts — the downtime we're guarding against. You can't fix this by reordering the pipeline; the only fix is discipline.

Expand / contract

Split every breaking change across two deploys:

1. Expand (this PR): additive, backward-compatible schema + code that tolerates both the old and new shape.
2. Contract (a later PR, after expand is fully deployed): remove the old thing, now that nothing reads it.

Never put expand and contract in the same PR. If this PR both removes the code that used a column and drops the column, the old code is still live during cutover — split it.

Per-operation playbook

A CREATE INDEX, ADD COLUMN, or ADD CONSTRAINT against a table created in the same migration is always safe (no rows, no live traffic) — the lint already suppresses those.

Tracking the contract (don't let it rot)

The contract half is deferred to a later deploy — and that is exactly when it gets forgotten, leaving dead columns, orphaned tables, and NOT NULLs that never land. Every deferred contract must become a durable, greppable TODO.

When an expand defers a drop, leave a contract-pending marker on the legacy column/table in packages/db/schema.ts — that is the file you will be editing when you finally do the drop, so the reminder lives where the work happens:

// contract-pending(after #5035 is fully deployed): drop once permission-check.ts stops reading it
workspaceId: text('workspace_id'),

Format: contract-pending(<precondition>): <what to drop> — <why it's safe once the precondition holds>. The precondition names the PR/release that removes the last reader and must be fully deployed before the contract ships.

• The TODO list is a grep — always accurate, never drifts: grep -rn "contract-pending" packages/db apps/sim. Run it when starting migration work to see what is owed.
• While a drop is pending, tag each doomed column @deprecated and make every read of that table name its columns: no argless select().from(t), findFirst() without columns, argless .returning(), or bare getTableColumns(t) — use omit(getTableColumns(t), [...]). check:pending-drop-tables enforces this.
• For anything with a real owner or schedule, also open a tracking issue and put its number in the marker.
• Close the loop in the contract PR: the contract migration's -- migration-safe: annotation references the expand, and you delete the contract-pending marker in the same PR:

  -- migration-safe: contract of #5035 — workspace_id readers removed there, deployed 2026-06-10
  ALTER TABLE "permission_group" DROP COLUMN "workspace_id";

• An expand merged without a marker for the drop it defers, or a contract merged without removing its marker, is a bug — flag it in review.