28 migration files, two of which cancel each other out, and not one of them written by hand

Nakodo's schema is 23 tables and 36 indexes in a single 1,118 line schema.ts. The drizzle/ directory next to it holds 28 SQL files, numbered 0000 to 0027. None of them was typed by a person, three of them have names a person chose, one of them undoes another, and none of them runs on deploy.

That last point is the one worth arguing about, so start there.

Migrations are applied by hand, and the README says so

3. Apply the schema in `drizzle/` to the database (migrations are run by hand).

There is no migration step in the build, no drizzle-kit push anywhere, and no job that reconciles the database with the schema file. Shipping a schema change is two commands run by a human:

pnpm db:generate && pnpm db:migrate

The cost of that is obvious: you can forget. The benefit is that the deploy and the migration are separate events with a gap between them, and once you accept that gap you are forced to write changes that survive it. Whatever order you do them in, there is a window where one side is ahead:

• migrate first, and the old code is briefly running against the new schema;
• deploy first, and the new code is briefly running against the old schema.

An automatic migration on deploy does not remove that window, it just stops you from thinking about it. Doing it by hand means every change gets classified on the way out. Adding a nullable column is safe in both directions. Adding a NOT NULL column with a default is safe. Dropping a column is only safe after the code that read it has been live for a while, and renaming anything is two migrations with a copy in between, or it is an outage.

That rule is visible in the file list. The column drops arrive in their own migration, well after the commit that stopped using them:

DROP TABLE "quota_days" CASCADE;
ALTER TABLE "keywords" DROP CONSTRAINT "keywords_last_search_id_searches_id_fk";
ALTER TABLE "campaign_channels" DROP COLUMN "hit_count";
ALTER TABLE "keywords" DROP COLUMN "last_search_id";
ALTER TABLE "profiles" DROP COLUMN "email_signature";
ALTER TABLE "videos" DROP COLUMN "duration_seconds";
ALTER TABLE "videos" DROP COLUMN "category_id";

Nothing in that file changes behaviour. Every one of those columns had already stopped being read; the table had already stopped being written. It is a cleanup migration, which is the only kind of drop I am comfortable generating, because if it is wrong the failure is immediate and loud rather than subtle.

Generated, with three exceptions to the naming

drizzle-kit generate diffs schema.ts against a snapshot it keeps in drizzle/meta and writes the SQL. It also invents a name, from what appears to be a list of comic book characters, so most of the directory reads like this:

0019_goofy_gauntlet.sql
0020_broken_trauma.sql
0021_sparkling_goblin_queen.sql
0022_bizarre_the_hood.sql
0023_blushing_omega_red.sql

Three files break the pattern, and they are the three I had to reason about:

0010_keyword_pages.sql
0011_drop_keyword_pages.sql
0012_campaign_pause_reason.sql

Naming a migration is not a style preference. It is the only thing that tells you, a month later, which file to read when you want to know when a column appeared. 0023_blushing_omega_red could be anything. When a change is one you expect to come back to, pass --name.

The naming also exposes the pair in the middle. 0010 adds a paging column to two tables and widens an index to include it:

DROP INDEX "searches_lookup_idx";
ALTER TABLE "keywords" ADD COLUMN "last_page" integer DEFAULT 0 NOT NULL;
ALTER TABLE "searches" ADD COLUMN "page" integer DEFAULT 0 NOT NULL;
CREATE INDEX "searches_lookup_idx" ON "searches" USING btree ("platform","query","region_code","relevance_language","page","searched_at");

0011 takes it all back out. The journal in drizzle/meta timestamps them 43 minutes apart. Two migrations, one feature, one morning. The honest reading of that is that the schema change was the cheapest part of a decision I should have tested before migrating, and generating migrations made it cheap enough that I did not. I have stopped treating that as a mistake: 28 files is a log, and a log that only contains good decisions is a log somebody has been editing.

The statement a generated migration cannot know it needs

0011 is also the one file whose first line is not generated:

DELETE FROM "searches" WHERE "page" > 0;
DROP INDEX "searches_lookup_idx";
CREATE INDEX "searches_lookup_idx" ON "searches" USING btree ("platform","query","region_code","relevance_language","searched_at");
ALTER TABLE "keywords" DROP COLUMN "last_page";
ALTER TABLE "searches" DROP COLUMN "page";

drizzle-kit produced the four ALTER and index statements correctly. It could not have produced the DELETE, because the need for it is not in the schema, it is in what the rows mean.

The searches table is a cache: a row is a query, the region and language it was run for, when it ran, and the results it returned, and the lookup index is keyed on exactly those first four columns plus the time. While page existed, a row was identified by that tuple plus a page number. Remove the column and every row for page 1 and above collapses onto the key of its page 0 sibling. Nothing errors. The rows are valid, the index is fine, and a cache lookup starts returning a later page of results as though it were the first.

A schema diff tool cannot see that, and no test would have caught it either, because the data that breaks it only exists in a database that ran the previous migration. The only thing that catches it is asking, before writing the drop, what the rows mean once the column is gone. Then you add one line to the generated file.

That is the strongest argument I have for generating migrations rather than writing them: the generator reliably does the mechanical 90 percent, which leaves your attention for the 10 percent that is about meaning. Writing all of it by hand spends the same attention on ALTER TABLE syntax.

A three state rule as a CHECK, and an XOR in SQL

The last statement in 0022 is my favourite line of SQL in the project:

ALTER TABLE "campaigns" ADD CONSTRAINT "campaigns_pause_ck" CHECK (
  case when "campaigns"."status" = 'paused'
    then ("campaigns"."pause_mode" is null) <> ("campaigns"."pause_reason" is null)
    else "campaigns"."pause_mode" is null and "campaigns"."pause_reason" is null
  end
);

A campaign can be paused by its owner, in which case it has a mode, or paused by the system, in which case it has a reason. Not both. Not neither. And a campaign that is not paused has neither.

(a is null) <> (b is null) is exactly-one-of, written as inequality on two booleans, which is the shortest XOR Postgres will give you. The alternative is two and clauses with three is null tests between them, and it is harder to read and no faster.

The reason this belongs in the database rather than only in TypeScript is that there are three writers: the UI, a background runner that pauses a campaign when it hits a limit, and the odd command line fix. Two of those are code paths I would have to remember to check. A constraint checks all three, including the fix I run at 11pm, and the one that matters most is the one I would otherwise do by hand.

It also forced an ordering decision that was easy to get wrong: a column cannot be added and constrained in the same breath if existing rows violate the constraint. pause_reason arrived in 0012, nullable, ten migrations before the constraint that governs it. Schema changes and the rules about them are not the same deploy.

What the migration directory is for

Two things, and they pull in different directions.

It is a mechanism, which wants to be boring: generated, numbered, applied in order, never edited after it has run anywhere.

It is also a record of how the schema came to look like this, which wants names and comments and the occasional hand added statement. A DELETE in the middle of 0011 is a note to the next person that the drop had a cost.

Having both in one directory means the rule cannot be "never touch generated files". It has to be "never touch a generated file that has run", which is a different rule and the one worth teaching. For what the schema is ultimately shaped by, the public version is on the privacy notice: what is stored, where it came from, and what the daily job deletes. Most of the awkward migrations in that list exist because of a sentence on that page, and the method page is where the rest of the shape comes from.