Database Migrations (NON-NEGOTIABLE)

The golden rule: let Drizzle do its job

Drizzle auto-generates migrations by diffing your schema files against the last snapshot. This ONLY works when the snapshot chain is accurate. NEVER bypass the auto-diff with --custom for simple column/table additions.

Adding a column or table (90% of migrations)

# 1. Edit the schema file in shared/schema/
# 2. Generate migration (drizzle diffs, generates SQL + new snapshot automatically)
npm run db:gen "description"

# 3. Review the generated SQL
# 4. Apply
npm run db:migrate

That’s it. No manual SQL, no snapshot patching, no hand-editing _journal.json.

If db:gen prompts about unrelated changes → snapshot drift (see repair below). Fix the drift; don’t fall back to --custom.

When --custom is actually needed (10%)

ONLY for operations Drizzle can’t infer:

• Column renames — Drizzle sees drop + create, can’t distinguish from rename
• Data backfills — UPDATE statements, not DDL
• Complex DDL — partial indexes, custom constraints, DO $$ ... END $$ blocks

After creating a --custom migration, you MUST manually patch migrations/meta/0XXX_snapshot.json to add/rename the column/table. If you don’t, the snapshot drifts and future db:gen breaks.

Snapshot drift repair

If drizzle-kit generate says “data is malformed” or prompts about many unrelated tables/enums:

npm run db:snapshot:patch              # Patches last snapshot with DB-introspected data
npx drizzle-kit generate --name test   # Should now work without "malformed" error

How patch-snapshot.ts works: introspects local PostgreSQL → adds missing tables/columns/enums defined in shared/schema/*.ts. Preserves drizzle-kit’s internal format (array-based index columns, typeSchema on enum columns, stripped ::type casts on defaults).

Common drift causes: --custom migrations without snapshot update, or merging branches that added tables independently.

Prevention: always use npm run db:gen for standard column/table additions.

Startup migration guard

Environment	Behavior
Railway (production)	npm run db:migrate && npm start — migrations run BEFORE server starts
Local dev	npm run dev runs db:status at startup and warns if pending
CI/CD	Migration validation runs in build pipeline

Idempotent migrations (NON-NEGOTIABLE)

ALL migration SQL MUST be idempotent — Railway re-runs migrations on every deploy. Without idempotency guards, re-runs crash on “already exists” errors.

Operation	Guard
CREATE TABLE	IF NOT EXISTS
CREATE TYPE / CREATE TYPE ... AS ENUM	DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN null; END $$;
ADD CONSTRAINT (FK)	DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN null; END $$;
CREATE INDEX	IF NOT EXISTS
ALTER COLUMN DROP NOT NULL	Safe — no-op if already nullable

CI integrity test enforces IF NOT EXISTS on CREATE TABLE/INDEX from migration 0036+.

Statement breakpoints

• --> statement-breakpoint between EVERY SQL statement — including COMMENT ON, CREATE INDEX, everything. Neon rejects multiple commands in one prepared statement.
• NEVER put breakpoints inside DO $$ ... END $$ blocks (crashes Neon with unterminated dollar-quoted string)
• After npm run db:gen: verify _journal.json timestamp > previous, breakpoints present

Other rules

• NEVER use drizzle-kit directly — always go through npm run db:gen for sequential naming, timestamp validation, snapshot integrity
• MONOTONIC TIMESTAMPS — _journal.json when values MUST be strictly increasing. Drizzle silently skips migrations with when <= lastApplied.created_at. CI catches violations.
• npm run db:status — Check state before AND after changes. NOTE: can show false pending:0 when DB has more rows than journal entries — always run db:migrate to confirm (uses hash-based detection)
• NEVER manually edit _journal.json — use npm run db:gen which auto-validates timestamps
• Backfill migrations with JOINs — always add a separate query for the NULL FK case (docs with collectionId IS NULL are invisible to JOINs and will be silently missed — see FM-031)

Common pitfalls (from incidents)

Pitfall	Fix
Forgot IF NOT EXISTS → Railway redeploy crashes	Add the guard; all new migrations must be idempotent
Used --custom for a normal column add → snapshot drifts	Use db:gen; if you must --custom, patch snapshot manually
Migration timestamps not monotonic after branch merge	Renumber when values > last existing entry
Backfill misses NULL FK rows	Add explicit WHERE x IS NULL query in same migration
pgTable() string not updated when renaming table	Update ALL 7 layers — see Autonomous Agent Rules