Two migrations, one table, and a journal whose timestamps describe a database that no longer exists

Notifio has one table. I have written about that before: no subscriptions means no billing state over time, so a customer is a row and the row is the whole story.

This post is about the other directory, the one next to the schema that nobody opens unless something has gone wrong:

server/drizzle/migrations/
├── 0000_perfect_mauler.sql
├── 0001_regular_lorna_dane.sql
└── meta/
    ├── 0000_snapshot.json
    ├── 0001_snapshot.json
    └── _journal.json

Five files describing two changes. I went in to answer a question I had been putting off since we moved the database: which of these files still means what it says?

The two changes

The first one made the table:

CREATE TABLE "licenses" (
    "id" text PRIMARY KEY NOT NULL,
    "email" text NOT NULL,
    "token" text NOT NULL,
    "stripe_session_id" text NOT NULL,
    "active" boolean DEFAULT true NOT NULL,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL,
    "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT "licenses_email_unique" UNIQUE("email"),
    CONSTRAINT "licenses_token_unique" UNIQUE("token"),
    CONSTRAINT "licenses_stripe_session_id_unique" UNIQUE("stripe_session_id")
);

Seven columns, three unique constraints. The second one is the day we started selling a second thing:

ALTER TABLE "licenses" ADD COLUMN "auto_reply" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "licenses" ADD COLUMN "auto_reply_session_id" text;--> statement-breakpoint
ALTER TABLE "licenses" ADD COLUMN "auto_reply_purchased_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "licenses" ADD CONSTRAINT "licenses_auto_reply_session_id_unique" UNIQUE("auto_reply_session_id");

That is the whole history. Seven columns to ten, one new unique constraint, and then nothing.

The fourth statement is the one doing real work. auto_reply_session_id is the Stripe Checkout session that paid for the upgrade, and the uniqueness is what makes the webhook safe to run twice. Stripe will happily redeliver an event, and a redelivery that tried to apply the same upgrade again would hit that constraint instead of quietly charging the row a second time. The business rule "you cannot buy this twice" is not enforced in a handler. It is enforced here, in a DDL statement from July.

I also like that auto_reply_purchased_at is nullable with no default and nothing reads it. The schema says so out loud:

/** Kept for support and accounting; nothing gates on it. */
autoReplyPurchasedAt: timestamp("auto_reply_purchased_at", { withTimezone: true }),

A column that exists for a human to look at during a support email is a legitimate column. Marking it as such in the type is how you stop a later version of yourself from writing a query against it.

The --> statement-breakpoint comments

Those are not decoration, and they are not for Postgres. They come from this, in the journal:

{
  "idx": 1,
  "version": "7",
  "when": 1785155471183,
  "tag": "0001_regular_lorna_dane",
  "breakpoints": true
}

With breakpoints: true, drizzle-kit marks the statement boundaries in the file so the migrator can split it and send the statements one at a time, rather than handing the driver one string with four semicolons in it. Some drivers and some pooler modes will not take a multi-statement string. Ours is behind a transaction-mode pooler that already cost us the prepared statements, so a tool that splits by default is a tool making the right assumption about my setup.

The file names, incidentally, are random. perfect_mauler and regular_lorna_dane are generated codenames, and they carry no information whatsoever. The ordering is entirely in the four-digit prefix and in the journal's idx. I have seen people try to rename these to something meaningful. Do not: the tag in the journal has to match the filename, and the sequence number is the only thing anybody should be reading.

The snapshots are the part that actually works

Each migration has a snapshot of the whole schema as of that point, and they are chained:

0000_snapshot.json   id 4f9adae8   prevId 00000000   7 columns
0001_snapshot.json   id 65d78d4e   prevId 4f9adae8   10 columns

This is what drizzle-kit generate diffs against. It does not inspect your database to work out what changed, and it does not parse the SQL files. It compares your current schema.ts to the last snapshot in that chain, and the difference is the migration it writes. The prevId link is what makes a gap or a reorder detectable instead of silently producing a migration from the wrong base.

Which means these two JSON files, not the SQL, are the real state of the repository's idea of the database. Delete the snapshots and keep the SQL and the next generate will try to create the table from scratch all over again. Delete the SQL and keep the snapshots and you lose the ability to replay, but the next diff is still correct.

Then we replaced the database

On the 12th of October we moved to a new database. The commit is called Redeploy with new database, and the one before it is the one that added the maintenance switch so the site could answer 503 while there was no database to answer with.

Now go back and read the journal:

"when": 1784553760932,   // 2026-07-20T13:22:40Z
"when": 1785155471183,   // 2026-07-27T12:31:11Z

July. Both of them. Today is the 19th of October, and the database those statements are running against has existed since the 12th.

Those timestamps are not lying, exactly. when records the moment drizzle-kit generated the file, which is a fact about my laptop in July and was never a claim about any particular database. But it is the only date in the directory, so it is the date you will read if you go looking for "when did this change go in", and the answer it gives you is wrong for the database you are actually connected to.

The record of when a migration ran lives in the database itself, in the table the migrator keeps for exactly that purpose, and that record went wherever the old database went. Whatever rows exist now are from the day the new one was filled. So the repository knows what changed and in what order, and no longer knows when any of it happened. That is the correct trade, and it is worth knowing which half you have.

The decision sitting in .gitignore as a comment

Two lines near the bottom of the ignore file say, in effect: drizzle's output is generated, and you should commit it if you want the history. The rule that would exclude the directory is written out and then commented out.

I have come round to thinking a commented-out ignore rule is a good artefact rather than untidiness. An empty line tells you nothing. A line with a # in front of it tells you somebody stood at that fork, and which way they went. For a two-file history on a one-table schema, the argument for committing it is thin on its own: the schema file is the source of truth, and a fresh database could be created from it directly.

The argument that wins is replay. drizzle-kit migrate against a brand new database produces the table as it was in July and then upgrades it, which is a path that has been taken before, and this month it got taken again. The alternative is pushing the schema straight on, which works right up until the day it decides a column rename is a drop and a create.

Which brings up the one thing in this setup I am not comfortable with:

"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate",

These are scripts a person runs. Nothing in the deploy runs them. For a schema that changes twice in three months, with one person deploying it, that is defensible and I would not defend it much further than that. The failure mode is a deploy that goes out expecting a column the database has not got, and the thing standing between us and it is that I remember. It has not changed in 84 days, which is both why it is fine and why I would not notice if it stopped being fine.

What I would take from this

A migrations directory contains three different kinds of claim and they age at different rates:

• The SQL says what changed. Permanently true.
• The snapshots say what the shape was. True for as long as the chain is intact, and the thing that actually drives the next diff.
• The journal says when. True about the generating machine, and quietly meaningless the moment the database it ran against is replaced.

If you are treating the journal as a changelog, you are reading a build artefact as a history book. Use git log on that directory for when. Use the snapshots for what. And if you ever swap the database underneath a project, go and look at what the swap took with it, because the answer is usually "the only thing that knew what had already been applied".

The two products all ten of those columns exist to sell are on notifio.app/pricing, and what the second one does is explained on the help page.