Drizzle Migrations

Drizzle Migrations — skills-observability backend

This skill exists because we lost meaningful production time to a class of migration bug that produces no error, no warning, and no test failure — until prod and dev silently disagree about what tables exist. The rules below are designed to keep that from happening again. Where a rule looks heavy-handed, the Why explains the real failure it prevents, so you can apply judgment at the edges.

The Golden Path (schema-first)

When a database change is needed, follow this exact sequence. Do not deviate without reading the rest of this document first.

1. Edit backend/src/db/schema.ts        (the source of truth)
2. cd backend && bun run migrate:generate   (Drizzle writes the SQL + journal entry)
3. Open the generated *.sql and review it    (catch destructive ops, missing IF NOT EXISTS)
4. Optionally tighten the SQL for idempotency (see "SQL idempotency" below)
5. Commit BOTH the .sql file AND the updated meta/_journal.json + meta/*.json snapshot
6. Verify against a fresh DB before merging  (see "Verification" below)

If you find yourself hand-writing a .sql file under backend/src/db/migrations/ from scratch, stop and ask whether the change can be expressed in schema.ts instead. Hand-authored migrations are valid in special cases (data backfills, raw SQL Drizzle can't generate), but they are off the golden path and need extra care — see "Hand-authored migrations" below.

The invariant that bit us: journal monotonicity

The meta/_journal.json file is a list of entries with an idx, a tag, and a when (a Unix-epoch milliseconds timestamp). At runtime, drizzle-orm/postgres-js/migrator decides whether to apply a given migration like this:

> Walk the journal entries in idx order. For each entry, apply it only if its when is strictly greater than the maximum created_at currently recorded in the __drizzle_migrations table. Otherwise, skip it.

Read that sentence twice. The consequence is non-obvious and catastrophic: **if you add a new entry whose when is entries[j].when for every i > j. Strictly greater, no equality, no going backward.

Practical rules that keep the invariant true

1. Never hand-edit meta/_journal.json to "fix dates" or resolve a merge conflict. Re-run bun run migrate:generate or carefully merge by re-issuing the entries with new when values that are strictly greater than the current max. The journal is an append-only log in disguise.

1. When migrate:generate produces an entry, leave its when alone. Drizzle stamps it with Date.now() at generation time, which is by construction greater than every previous entry (assuming the clock isn't broken).

1. When two branches each add a migration and you merge, the resulting journal must still be monotonic. Two branches can each generate 0007_* legitimately. Resolve by:

• Pick a winning idx ordering (typically: keep both, the later-merged one becomes idx N+1, rename its file).
• Re-stamp the later entry's when to Date.now() so it is strictly greater than the prior max.
• If you can't be sure, re-run migrate:generate on a clean tree and let Drizzle re-emit the entry.

1. If you ever truly must hand-author a journal entry, set when: Date.now() at the moment you author it, and verify it is strictly greater than Math.max(...entries.map(e => e.when)) in the existing journal. Don't copy when values from other entries, don't pick "round" timestamps, don't reuse a value because "it looks similar."

1. Audit the journal whenever you touch it. A quick check in node:

``js const j = require("./backend/src/db/migrations/meta/_journal.json"); j.entries.reduce((p, e) => { if (e.when in psql against the new tables is enough.

• The number of rows in __drizzle_migrations equals the number of journal entries. If it's lower, a migration was silently skipped — start by auditing the journal monotonicity.

Why this matters: the migration runner is the only thing standing between your schema and prod. The fresh-DB run is the only test that exercises it the way prod will exercise it on next deploy (assuming prod isn't fresh — but prod will, on every new env, every disaster-recovery rebuild, every test environment provisioned by CI).

Reviewing a migration PR

When reviewing someone else's migration changes, check in this order:

1. meta/_journal.json is monotonic. Walk the when values; flag any non-strict-increase.
2. Each new .sql file is idempotent where the operation supports it.
3. The SQL matches the schema diff. If schema.ts adds a column notNull with a default, the SQL should reflect that — Drizzle is usually right, but it's the easiest place for a subtle mistake to slip in (e.g., forgetting that adding NOT NULL to a populated table requires a default or a backfill step).
4. For destructive changes (drop column, drop table, type narrowing) — confirm the data isn't needed, and whether a two-phase rollout (deploy code that ignores the column, then drop it later) is safer.
5. No edits to existing migration .sql files unless the migration is brand-new and unmerged. Editing a migration that has already run in any environment will not re-apply the change — the runner will see the entry as already applied. If you need to change something already shipped, write a new migration.

Common mistakes and what they look like

• "My migration ran in dev but the table doesn't exist in prod." → Almost always journal monotonicity. Audit _journal.json.
• "I edited the SQL in 0005_*.sql and re-deployed but the change didn't take." → Already-applied migrations are not re-run. Add a new migration.
• "I got a merge conflict in _journal.json and resolved it by hand." → High risk of breaking monotonicity. Re-audit, or re-generate.
• "migrate:generate produced a weird empty migration." → Often means your schema.ts change didn't actually change anything Drizzle can see (e.g., a comment-only edit, or a TS type without a runtime difference). Delete the empty migration before committing.
• "The migration runs but takes forever on prod." → You're probably backfilling or adding a non-concurrent index on a large table. For large tables, prefer CREATE INDEX CONCURRENTLY (which Drizzle won't generate — hand-author) and split data backfills out of the migration system.

Emergency: a migration was silently skipped in production

If you discover a journal entry was skipped (rows missing from __drizzle_migrations, or schema doesn't match), the fix is:

1. Don't try to "re-stamp" the old when value — that just hides the problem and doesn't trigger a re-run because the runner only looks forward.
2. Generate a new migration that does what the skipped one was supposed to do. Make it idempotent so it's safe even on environments where the original did run.
3. Audit every other environment (staging, dev databases, ephemeral CI envs) to figure out where the divergence exists.
4. Then fix the journal so future fresh-DB runs don't repeat the skip — strictly monotonic when values, no exceptions.

The reason to fix forward (new migration) rather than backward (edit old when) is that production schemas are built by running migrations in order; the only way to change one is to add another. Editing history doesn't propagate.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: PackmindHub
• Source: PackmindHub/skillsight
• License: Apache-2.0

Install and usage instructions live in the source repository linked above.