Our migration folder has 51 numbers, 50 files, 49 snapshots, and 289 lines of SQL the tool will never touch

CogniPrep's schema is one 1,225-line schema.ts with 26 tables in it, and lib/db/migrations/ holds the history of how it got there. I went through that folder file by file this week, and it is a more honest account of the project than the schema is.

Three numbers that should match do not.

• 51 numbers. The files run 0000 to 0050.
• 50 files. There is no 0018. Not in the working tree, not in meta/_journal.json, not in meta/, and git log --all -- 'lib/db/migrations/0018*' returns nothing, so it was never committed in the first place.
• 49 snapshots. meta/ is missing 0018_snapshot.json, which follows, and 0023_snapshot.json, which does not.

None of this is broken. It is worth understanding why not.

The missing number is harmless, and it tells you what the journal is for

It is tempting to read a migration folder as a sequence the tool walks by counting. Drizzle does not count. meta/_journal.json is a list of entries, each with an idx and a tag, and the tool applies the tags in that list in order. Ours jumps from idx: 17 to idx: 19.

So a generated migration that was thrown away before commit, which is what almost certainly happened to 0018, leaves a hole and no consequence. The thing that would break is editing the journal: delete an entry whose migration has already run in production, and the next deploy tries to apply it again.

The practical rule: the .sql files are not the source of truth about what has been applied. The journal is, and the __drizzle_migrations table in the database is the other half of the answer. Count files, learn nothing.

The missing snapshot is a hand-written migration

0023_deduplicate_device_sessions.sql is one of two files in the folder with a name a human chose. The other 48 carry Drizzle's generated codenames (0017_cloudy_betty_brant, 0024_many_namora), and the second human-named one is 0047_first_purchase_at.

A hand-named file is a hint; a missing snapshot is proof. Drizzle writes a snapshot of the whole schema next to every migration it generates, and diffs the newest snapshot against your schema.ts to work out what the next migration should contain. 0023 has no snapshot because Drizzle did not generate it. It was written by hand, because it does not change the schema at all:

DELETE FROM "device_sessions_table"
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (
            PARTITION BY user_id, device_fingerprint
            ORDER BY last_activity_at DESC
        ) AS rn
        FROM "device_sessions_table"
    ) ranked
    WHERE rn > 1
);

That is a data repair, not a schema change: a bug had let two rows exist for one device, and this keeps the most recently active row per (user_id, device_fingerprint) and deletes the rest. A schema diffing tool can never write that statement for you, because there is nothing in the schema to diff.

Its journal entry carries the other tell. The when timestamps either side of it are 2026-04-15T14:49:33Z and 2026-04-19T13:28:08Z, and 0023's is 2025-04-19T00:00:00Z: midnight, round number, and a year early. Somebody typed it. The timestamps in that file are therefore not monotonic, which matters not at all to Drizzle, and quite a lot to anybody who reads them as a history.

289 lines of SQL that are deliberately outside all of this

Three more .sql files live next to the migrations folder and are never applied by the tool:

File	Lines	Who runs it
auth-user-deletion-trigger.sql	39	A person, once, as the postgres role
statement-timeout.sql	132	A person, once, after reading it
observability.sql	118	A person, whenever they are investigating

The trigger is the one that matters most, and it cannot be a migration for a boring reason: it lives on Supabase's auth schema, which Drizzle does not manage, and creating a trigger there needs a privilege the migration role does not have. Its job is to be the only link between Supabase's auth.users and our users_table, whose id is a bare text primary key with no foreign key pointing at the auth table. The trigger deletes the matching app row, and 22 onDelete: 'cascade' relationships in the schema do the rest.

That is why the account-deletion routes can be as short as they are: they call one Supabase admin function, name no tables, and the database removes the entire data graph underneath. The file header says to keep it in sync with the live database and that both statements are CREATE OR REPLACE, so re-running is a no-op. For a rebuild or a restore from backup, that file is the only record that this behaviour exists at all.

observability.sql is the odd one out: it is a runbook rather than a change. Its own header says the query work in this codebase was prioritised structurally, by reading code and generated SQL, because no production query statistics were available, and the file exists to close that gap with pg_stat_statements queries for next time. Eight numbered sections, one of which creates an extension and the rest of which only read.

What the folder is actually for

Two things I now believe about migration folders:

1. They are an artifact with provenance, not a build input. The generated names are noise, so the files with human names are the ones to read first: they are the ones that exist because something went wrong in production.
2. The SQL you cannot generate deserves to live in the repository anyway. A trigger on somebody else's schema, a role setting, and a set of diagnostic queries are not migrations and should not pretend to be. What they need is a file, a header saying who applies it and how, and the discipline to keep it current.

Generating and applying migrations here is a deliberate human step either way. package.json has db:generate, db:migrate, db:push and db:studio; the build command is next build and nothing else, and vercel.json contains three cron entries and no build hooks. Nothing in CI applies a migration; the only scheduled workflow in the repository is a nightly backup, which only reads.

See it

The deletion behaviour the hand-applied trigger implements is promised in public: open cogniprep.app/privacy and read the Data Retention section, which commits to "Account data: retained until you delete your account", game sessions deleted after 2 years "or immediately when you delete your account", and exercise attempts "removed immediately when you delete your account". The 2 year rule is a nightly job. Everything that says immediately is 22 cascade relationships and one trigger that no migration will ever recreate for you. The terms page is the other half of that pair, and both are worth reading against each other if you are the one who has to make the sentences true.