704 lines of SQL, 66,007 lines of JSON, and the one migration Drizzle never generated

CogniPrep's schema is one 1,238-line TypeScript file. Its migration history is 48 SQL files holding 704 lines between them. Alongside them sits a meta/ directory of 1.8 MB, which is 66,007 lines of JSON.

So by line count, the migration history is about 99 per cent files no human wrote or reads. That ratio surprises people the first time they look, and then the two files that break the pattern turn out to be the ones worth knowing about.

What the JSON is

Drizzle Kit works by diffing. drizzle-kit generate compares your schema file against the shape the database is supposed to be in already, and emits SQL for the difference. To do that it needs the previous shape, so every generated migration writes a complete snapshot of the whole schema next to it.

Complete, not incremental. The most recent migration here is four statements:

ALTER TABLE "affiliate_codes" ADD COLUMN "archived_at" timestamp;
ALTER TABLE "affiliate_commissions" ADD COLUMN "clawback_payout_id" text;
ALTER TABLE "affiliate_commissions" ADD CONSTRAINT "affiliate_commissions_clawback_payout_id_affiliate_payouts_id_fk" FOREIGN KEY ("clawback_payout_id") REFERENCES "public"."affiliate_payouts"("id") ON DELETE set null ON UPDATE no action;
CREATE INDEX "affiliate_commissions_clawback_payout_idx" ON "affiliate_commissions" USING btree ("clawback_payout_id");

Two nullable columns, a foreign key and an index, which is the entire database side of supporting affiliate commission clawbacks. Its snapshot is 3,773 lines, because it describes every table in the database, not the two it touched.

Three practical consequences follow, and they are the whole reason to understand this before your first merge conflict.

They are load-bearing, so they are committed. The snapshot is the diff base. Lose it and the next generate compares your schema against the wrong starting point, which does not error: it emits a migration that tries to create things that already exist.

Hand-editing one is never the fix. The snapshot is a derived artifact with internal ids. Editing it to "correct" a column makes your recorded idea of the schema disagree with both the schema file and the database, and you find out two migrations later.

A conflict in meta/ is a real conflict. Two branches that each add a migration produce two snapshots claiming to describe the state after the same parent. Git will happily merge them into something describing neither. The resolution is to take one branch's migration, rebase the other's schema change on top, and regenerate, which is the same discipline you would apply to any generated lockfile.

What makes the directory tolerable despite its size is that the ordering does not live in the filenames. It lives in meta/_journal.json:

{ "idx": 48, "version": "7", "when": 1791384894935,
  "tag": "0048_gifted_betty_brant", "breakpoints": true }

An explicit list, with an index and a tag per entry. Which brings us to the exceptions.

Exception one: the migration with no snapshot

There are 48 SQL files and 47 snapshots. The one without a snapshot is 0023_deduplicate_device_sessions.sql, and it has no snapshot because Drizzle Kit did not write it. A person did.

Its header says what it is for:

-- Deduplicate device_sessions_table rows that share the same (user_id, device_fingerprint).
-- These were created by a bug where the fingerprint fallback in checkDeviceLimit allowed
-- a device through, but upsertDeviceSession then inserted a new row (different token)
-- instead of updating the existing one.

That is a data migration. It cleans up rows a bug produced; it does not change the shape of anything. And that distinction is exactly why the missing snapshot is correct rather than a hole: a snapshot records the intended shape of the schema, so a migration that changes no shape has nothing new to record. The previous snapshot is still an accurate description of the database after this file runs.

It was added by hand, with a hand-written entry in the journal, and it works because drizzle-kit migrate applies what the journal lists in order. Generated SQL and hand-written SQL are the same kind of thing to the runner. Only generate cares about snapshots, and generate diffs against the newest one.

This is the useful mental model: the SQL files are the history of what ran, the snapshots are a cache of what the schema was supposed to look like at each step, and those two sequences are allowed to have different lengths.

Exception two: index 18 does not exist

The journal has 48 entries, indices 0 through 48, with 18 absent. There is no 0018 SQL file, no 0018 snapshot, and nothing matching 0018 anywhere in the repository's git history, including deleted paths.

I cannot tell you what happened, and that is the honest version. The most likely story is a generated migration discarded along with its journal entry before it was ever committed, while a later one kept its number. It was not committed and then removed, because a deletion would show up in the log.

The point is what it cost: nothing. Nobody noticed for 30 migrations. Because the runner reads the journal rather than counting files or assuming contiguity, a gap in the numbering is not a state the tooling can be confused by. That is a small piece of design worth copying. Any system that derives order from a filename pattern eventually meets a human who renamed something, and the ones that keep an explicit manifest survive it.

The part I would still change

The snapshots are not reviewable. A pull request that adds four lines of SQL also adds 3,773 lines of JSON, and no reviewer reads it, which means no reviewer would catch a snapshot that disagreed with its own SQL.

What we do instead is review the SQL and the schema diff, and treat meta/ as generated output: never edited, never resolved by hand, regenerated when it conflicts. It is the same contract as a lockfile, and like a lockfile the discipline only fails when somebody decides they can see what the file should say.

Worth saying that none of the above is a complaint about Drizzle. A full snapshot per migration is what buys the thing I actually want, which is schema changes that start as a TypeScript diff rather than as hand-written DDL. For a product where the schema grows every time we add a provider to the games catalogue or another one-time purchase option, that trade pays for itself monthly, and 1.8 MB of JSON in git is not a cost anyone can measure.