skillsight: Skill for Claude Code

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 <= any already-applied entry's when, that entry will be silently skipped forever. No error. No warning. No log line. The migration just doesn't run, and the production DB diverges from the schema your code expects.