Regenerate Drizzle migration snapshots so drizzle:generate produces a clean diff (DoD #3 of WXYC/wxyc-shared#82)

[jakebromberg]

Context

WXYC/wxyc-shared#82 ("Phase 4: Operationalize ReconciledIdentity") has three Definition-of-Done criteria. Verification on 2026-04-27 found criteria 1 and 2 met, criterion 3 not met:

3. `npm run drizzle:generate` produces a clean diff against current schema state without hand-patching the `meta/` directory.

The epic was reopened. This issue tracks the fix.

Symptom

Run `npm run db:start` against a freshly-created local Postgres so all 67 migrations apply, then `npm run drizzle:generate`:

```
[✓] Your SQL migration file ➜ shared/database/src/migrations/0069_.sql
```

The generated SQL contains operations that are already in the database and were already applied through earlier migrations:

• `CREATE TABLE flowsheet_linkage_review` (already in 0067)
• `flowsheet.{linkage_source, linkage_confidence, linked_at, legacy_link_attempted_at}` (already in 0062/0063)
• `library.{artist_name, canonical_entity_id, canonical_entity_confidence, canonical_entity_resolved_at, search_doc}` (already in 0058/0061)
• `flowsheet_artwork_lookup_idx`, `flowsheet_show_id_idx`, `library_artist_name_trgm_idx`, `library_search_doc_idx`, `library_canonical_entity_id_idx` (already in 0057/0058/0061/0068)

Running `drizzle:migrate` against the resulting SQL would fail (every `CREATE TABLE` / `ADD COLUMN` collides). Anyone wanting to add a real new migration first has to manually delete those operations from the generated file or hand-patch `meta/`. Either is the failure mode #82's DoD #3 was meant to prevent.

Cause

`shared/database/src/migrations/meta/` has 44 snapshot files but the journal has 67 entries. Missing snapshots:

Range	Count	Origin
0001, 0005, 0008	3	Genuinely dropped historical idxs (validator's `DROPPED_IDXS`)
0036, 0041, 0047–0054	10	Ancient cut-over per #505; tolerated but never regenerated
0057–0068	12 (this is the new rot)	Each migration since 0056 was committed by hand-editing SQL + journal without re-running `drizzle-kit generate`

drizzle-kit's `generate` picks the latest snapshot by filename sort, diffs against `schema.ts`, and emits both the SQL and a fresh snapshot. The hand-edit pattern shipping today's migrations skips the snapshot emission. Cumulatively, the latest snapshot drizzle-kit knows about is `0056_snapshot.json`, so every diff is "everything since 0056."

The pattern was actively encouraged by #511's Phase 0 plan ("The hash of the file changes; that's expected — the migration is unapplied in production, so drizzle's hash check will simply attempt the new (fast) statement on next migrate.") and reinforced by today's recovery commits (`7f35fc3`, `8045b73`, `fe95168`, `c1e4fc3`, `9a915b4`). It's a fast and correct path for the SQL+journal but it leaves the snapshot dimension empty.

Why this matters

• Today: any contributor running `drizzle:generate` has to hand-patch the result before committing. The repo's instructions tell them to run the command; the command produces a wrong file.
• Tomorrow: as more migrations land via the same hand-edit pattern, the gap widens. Each new "real" generate has to slough off more accumulated drift.
• Eventually: the latest valid snapshot ages out far enough that diffing it produces a 1000-line "catch-up" that's no longer reviewable.
• Tooling broken: `drizzle-kit drop` (which uses snapshots to undo a migration) doesn't work for any migration past 0056. It's rarely used, but it's broken.

The runtime safety net (validate-migrations.mjs Check 6) walks the latest snapshot's `prevId` chain back to genesis. The latest is currently `0056_snapshot.json`, whose prevId points to 0055's id, whose prevId points to 0046's id (via `DROPPED_IDXS` tolerance for the 0047–0054 gap). Chain reaches genesis. The validator does NOT detect the 0057–0068 missing-snapshot gap because there is no latest snapshot for those idxs to anchor a chain check.

Approaches

Option A — full catch-up (regenerate every missing snapshot)

For each missing idx N from 47 to 68 (skipping 47/47-duplicate and the dropped ones):

1. Stand up a fresh Postgres, apply migrations 0–(N-1)
2. Run a custom snapshot emitter against that DB state, write `000N_snapshot.json` with `prevId` pointing to the previous snapshot
3. Apply migration N, repeat for N+1

drizzle-kit doesn't expose a "snapshot only" command; we'd reverse-engineer the JSON format or write a wrapper that calls drizzle-kit internals. Cost: high (~22 round-trips, custom tooling). Benefit: `drizzle-kit drop` works for every migration; future archaeology is possible.

Option B — latest-only snapshot (recommended)

Generate one new snapshot reflecting the current schema state, name it to pair with the latest migration (0068), accept that 0047–0067 stay missing.

Mechanics:

1. Fresh DB, apply all 67 migrations.
2. Run `drizzle-kit generate --name=catchup`. This emits both a no-longer-needed SQL file and a snapshot with `prevId = 0056_snapshot.json's id`.
3. Discard the SQL file and the journal entry drizzle-kit added.
4. Rename the emitted `_snapshot.json` to `0068_snapshot.json`.
5. Verify: run `drizzle-kit generate` again — should report no changes.

The result satisfies DoD #3 and unblocks future generates. The 0047–0067 gap remains in `meta/`, equivalent to the existing `0047–0054` gap that's been tolerated since #505.

Option C — Option B + validator hardening

Same as B, plus extend `scripts/validate-migrations.mjs` to require a snapshot per journal entry going forward (with an allowlist for the historically-missing idxs). Catches the next contributor who hand-edits the journal without running `generate`.

Recommended: Option C. Option B alone fixes today's problem. Option C also stops the rot from coming back.

Mechanical procedure (Option C)

Step 1 — generate the catch-up snapshot

```bash

fresh local DB

npm run db:stop && npm run db:start # applies all 67 migrations

emit a snapshot reflecting the post-0068 state

npm run drizzle:generate -- --name=snapshot_catchup # accept the prompt

inspect what was generated

ls -la shared/database/src/migrations/0069_*.sql shared/database/src/migrations/meta/0069_snapshot.json
```

Step 2 — keep only the snapshot, discard the SQL

```bash

remove the SQL file and the journal entry drizzle-kit added

git checkout -- shared/database/src/migrations/meta/journal.json
rm shared/database/src/migrations/0069___snapshot_catchup_.sql

rename the snapshot to pair with the existing latest migration (0068)

mv shared/database/src/migrations/meta/0069_snapshot.json \
shared/database/src/migrations/meta/0068_snapshot.json
```

Step 3 — verify a clean re-generate

```bash
npm run drizzle:generate -- --name=verify

expected: "No schema changes, nothing to migrate" (or similar)

```

If the second generate produces non-empty SQL, the snapshot doesn't match `schema.ts` and we have either drift (column declared in `schema.ts` but never migrated) or an emitter bug. Investigate before committing.

Step 4 — extend validate-migrations.mjs

Add a new check: every journal entry must have a corresponding `_snapshot.json` file in `meta/`, with allowlists for:

• `HISTORICAL_MISSING_SNAPSHOT_IDXS = new Set([36, 41, 47, 48, 49, 50, 51, 52, 53, 54, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67])`

(0001/0005/0008 are already in `DROPPED_IDXS` so they're already tolerated.)

The new check is: for each journal entry, if its idx is not in DROPPED_IDXS or HISTORICAL_MISSING_SNAPSHOT_IDXS, require a snapshot file. Going forward, every new migration MUST emit a snapshot or fail CI.

Step 5 — extend the validator's prevId-chain walk

Currently the chain walker tolerates breaks via `DROPPED_IDXS`. After Step 4, the latest snapshot will be `0068_snapshot.json` with `prevId` pointing to `0056_snapshot.json`'s id. The walker traverses:

```
0068 → 0056 → 0055 → 0046 → 0045 → 0044 → 0043 → 0042 → 0040 → 0039 → 0038 → 0037 → 0035 → 0034 → 0033 → 0032 → 0031 → 0030 → ... → 0000
```

(`0036` and `0041` are missing per the existing tolerance.)

The walker needs to know that `0068.prevId → 0056` skips a contiguous block. Easiest fix: when the walker encounters a prevId that resolves to a snapshot whose idx is N where the walking idx is M with M − N > 1, and every idx between N+1 and M−1 inclusive is in (DROPPED_IDXS ∪ HISTORICAL_MISSING_SNAPSHOT_IDXS ∪ HISTORICAL_DUPLICATE_IDXS), accept the jump.

If implementing this correctly is too fiddly, the alternative is to allowlist the `prevId` mismatch directly (e.g., `SKIP_PREVID_VALIDATION_FROM_IDXS = {68}`) and accept that the chain isn't strictly walkable across the gap. Less elegant but pragmatic.

Edge cases & corner cases

EC1 — Replay migrations vs. originals

In production:

• 0054 (`flowsheet-search-doc-with-dj-name`) was never applied; replayed via 0065
• 0055/0056/0062/0063 hashes were hand-inserted into `drizzle.__drizzle_migrations`
• 0064 (`propagate-v012-mojibake`) was never applied; replayed via 0066

On a fresh dev DB used to generate the catch-up snapshot, all migrations apply normally:

Migration	Fresh-DB effect	Prod state
0054	DROP+ADD search_doc with dj_name	Skipped; 0065 produces same end-state
0055	ADD COLUMN reconciled-identity columns	Hand-applied
0056	CREATE OR REPLACE VIEW with new columns	Hand-applied
0062	ADD COLUMN linkage_source / linkage_confidence / linked_at	Hand-applied
0063	ADD COLUMN legacy_link_attempted_at	Hand-applied
0064	UPDATE corrupted-form rows to corrected form	Skipped; 0066 produces same end-state
0065	DROP+ADD search_doc (no-op vs 0054 on fresh DB)	Applied (the prod path)
0066	UPDATE rows (no-op vs 0064 on fresh DB)	Applied (the prod path)

Cumulative end-state schema converges: same columns, indexes, view definition, table contents (modulo dev-vs-prod data). The catch-up snapshot generated against a fresh DB matches the schema prod has via the replays. No correctness issue here, but call it out so a future archaeologist who reads the snapshot doesn't conclude prod has applied 0054 directly.

EC2 — Drizzle's hash check

Drizzle's migrator records each migration's SQL hash in `drizzle.__drizzle_migrations` at apply time and never re-checks it. So changing `meta/*` does not affect the hash-applied check. Snapshots can be safely regenerated without forcing re-application. (Tested in this session via the 0064 SQL edit + drop-explicit-BEGIN-COMMIT change — hash changed, prod was unaffected.)

EC3 — Out-of-order journal whens (now resolved)

Journal whens were briefly out-of-order between 0061 and 0062/0063 earlier in this session. The 2026-04-27 commits restored monotonicity (`fe95168` and predecessors). Confirmed today: `jq 'sort_by(.when) | .[].idx'` produces 53,54,55,56,57,…,68 — strictly monotonic. Apply order on a fresh DB matches idx order, so the catch-up snapshot reflects the same cumulative state regardless of how drizzle orders applies.

EC4 — schema.ts / DB drift

If `schema.ts` declares something the migrations don't (or vice versa), the catch-up snapshot would lock in whichever side won (drizzle-kit reads `schema.ts` so it'd lock in schema.ts's version). Pre-flight: a fresh `db:start` after Step 1 should produce `All 67 migrations verified as applied` and no orphan-relation errors. After Step 3, run `drizzle-kit generate` once more — if a non-empty SQL is produced, that's the drift. Resolve before committing.

EC5 — drizzle-kit version stability

The snapshot format is internal to drizzle-kit. A future drizzle-kit upgrade may change it. Mitigation: regenerate the snapshot with the version that's pinned in `package.json` at the time of this issue (currently `0.31.10`), and treat any drizzle-kit upgrade as a "regenerate snapshots" trigger.

EC6 — Concurrent migrations / rebase conflicts

The repo has a custom `git-merge-append` driver for `_journal.json`. Snapshot files are unique per idx, so two PRs adding migrations concurrently won't conflict on snapshot content, but they will conflict on which idx to claim (both want 0069). The existing rebase-the-second-PR convention handles this. Out of scope here; mentioned for completeness.

EC7 — Hash mismatch on the snapshot file in prod

When prod's migrate container runs `drizzle:migrate` with the new snapshot present, drizzle ignores snapshots — it only reads the journal and SQL files. So adding 0068_snapshot.json doesn't trigger any prod-side action. Verifier in `init-db.mjs` doesn't read snapshots either. Zero prod impact.

EC8 — Future hand-edit attempts after Step 4 lands

A contributor who follows the same hand-edit pattern (edit SQL + journal, skip generate) will be caught by the new validator check from Step 4. Their PR's CI will fail with:

```
ERROR: Missing snapshot for journal entry idx 0069: shared/database/src/migrations/meta/0069_snapshot.json
```

…which directs them to run `drizzle:generate` properly.

EC9 — Allowing the rot to stay (do nothing)

If we close this issue without fixing it, every PR that runs `drizzle:generate` produces a wrong-by-default file that contributors must learn to hand-edit. That's a tax of ~5–15 minutes per migration PR plus the cognitive load of knowing which lines to delete. After enough PRs land via the hand-edit-only convention, the rot becomes structural — drizzle-kit may simply stop being usable. The cost of doing nothing accumulates; the cost of fixing it once is bounded.

EC10 — Allowlists growing instead of shrinking

The proposed `HISTORICAL_MISSING_SNAPSHOT_IDXS` set codifies the gap. Once the next-migration-must-have-snapshot rule fires, the set should never grow. If a future PR proposes adding a new idx to this set, that should require explicit reviewer pushback ("regenerate the snapshot instead, here's how"). Document this in CLAUDE.md alongside the migration pattern note.

Out of scope

• Regenerating individual snapshots for 0047–0067 (Option A). Recoverable later if anyone needs `drizzle-kit drop` to work for those migrations.
• Backfilling the Repair Drizzle migrations metadata (missing snapshots, broken prevId chain) #505 gap (0047–0054 specifically). Repair Drizzle migrations metadata (missing snapshots, broken prevId chain) #505 closed prematurely; this issue treats the larger 0047–0067 gap as a single unit.
• Auditing migrations for unrelated rot (e.g., DDL in DML migrations, missing IF EXISTS guards). Migration 0053 wedge: split DDL from backfill, harden migration runner #511's Phase 3 covers some of that.

Risk

Low. The catch-up is a write-only change to `meta/`. Prod doesn't read snapshots. The only failure mode is "the snapshot doesn't reflect schema.ts" which Step 3 explicitly verifies before commit.

The validator extension (Step 4) is a new failing check; it must allowlist the historical gap to keep CI green for unrelated PRs that don't touch migrations. Test cases:

• A PR that touches `apps/backend` but not `shared/database/src/migrations/`: validator passes (allowlist tolerates the historical gap).
• A PR that adds a new migration with snapshot: validator passes.
• A PR that adds a new migration without snapshot (the hand-edit pattern): validator fails with a clear message.
• A PR that touches an existing migration's SQL but not its snapshot: behavior unchanged from today (hash-edit convention). The validator doesn't enforce snapshot freshness against SQL content, only existence.

Definition of Done

• `shared/database/src/migrations/meta/0068_snapshot.json` exists and reflects current schema.
• `npm run drizzle:generate` against a fresh DB produces no schema changes.
• `scripts/validate-migrations.mjs` extended with the per-journal-entry snapshot check + `HISTORICAL_MISSING_SNAPSHOT_IDXS` allowlist.
• CI on this PR passes (validator + format + lint + typecheck + tests).
• CLAUDE.md updated: under "Database / Migration workflow", note that any migration must include its snapshot file (drizzle-kit emits it; don't hand-edit the journal).
• Phase 4: Operationalize ReconciledIdentity wxyc-shared#82 epic can close on this PR landing (DoD Auth middleware #3 met).

Part of WXYC/wxyc-shared#82. Related to #505 (predecessor; closed prematurely), #511 (sibling — runtime hardening, this is metadata hygiene).