Migrations, the flow and the gotchas

Part 1 covered the collection config I write; part 2 covered the Postgres schema it becomes, closing on one detail I hadn't explained yet: the .json snapshot sitting next to every migration file. This post is about that file itself: how migrate:create turns a config edit into SQL, the conventions that make a migration safe to write and safe to re-run, and the two ways this repo's migration history has actually broken.

Why a migration file has to exist at all

Payload's Postgres adapter has a "push" mode: edit a collection, and the database syncs to match live, with no file produced. It's genuinely pleasant to develop against; I used it for this site's first month or so. It's also explicitly unsupported for production, and the reason is simple once you've felt it: push mode leaves no record. There's nothing to diff, nothing to replay, and nothing that tells you a change happened at all. Turning push off is the fix: migrations for every change, in dev as well as prod, so the migration file is the only source of truth for what the schema actually is.

The authoring loop

migrate:create diffs the config-derived schema against the previous migration's .json snapshot and writes plain SQL, plus a new snapshot for the next diff. Reading that SQL is worth doing every time, because it sometimes contains a decision I didn't consciously make. The migration that turned on drafts is the clearest example from this series: alongside the new _posts_v table, it silently ran ALTER TABLE posts ALTER COLUMN title DROP NOT NULL on every required column, because a draft has to be saveable half-finished. I asked for drafts; the generated SQL is what actually decided required would stop being a database-level guarantee.

Not every migration is pure DDL, either. Adding user roles needed a data decision, not just a column: before roles existed, every account was effectively a full admin, since nothing gated publishing. Add a role column with its real default (author) and stop there, and every existing account would be silently demoted to draft-only on the next deploy, locking the site owner out of publishing. The migration backfills instead:

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'users' AND column_name = 'role'
  ) THEN
    ALTER TABLE "users" ADD COLUMN "role" "enum_users_role" DEFAULT 'author' NOT NULL;
    UPDATE "users" SET "role" = 'admin';
  END IF;
END $$;

The guard around the column-add and the backfill matters as much as the backfill itself. Postgres has no CREATE TYPE IF NOT EXISTS, so this repo's convention is to check first (IF NOT EXISTS, or a DO block that swallows duplicate_object) and wrap the whole change in that check. Nesting the UPDATE inside the "column doesn't exist yet" branch is what makes it idempotent: a re-run finds the column already there and skips the block entirely, so it can never re-promote an author or editor back to admin after the fact. A migration that isn't safe to run twice is a migration that's dangerous to run once, because payload migrate retries on failure, and a partially-applied migration is exactly the state a retry runs into.

payload migrate can also prompt for a y/N confirmation under some conditions. Fine in a terminal; fatal in a script, where a non-interactive process with no stdin just hangs forever. Locally I pipe an answer in; the CI build script runs payload migrate </dev/null, so if a prompt ever does appear during a build, EOF makes it abort instead of wedging the deploy indefinitely.

The chain has a beginning, and ours predates it

This site spent its early life in push mode, which means production's tables were never created by the migration runner, and its payload_migrations bookkeeping table started out completely empty. Point payload migrate at that database as-is and it sees zero recorded migrations, concludes it's starting from nothing, and tries to CREATE TABLE users against a table that already exists. It fails before it ever reaches the change you actually wanted to ship.

The fix is a one-time baseline: record the initial-schema migration as already-applied, a bookkeeping INSERT with no DDL attached, so the runner skips it and only applies what's genuinely new:

INSERT INTO payload_migrations (name, batch)
SELECT '<timestamp>_initial_schema', 1
WHERE NOT EXISTS (
  SELECT 1 FROM payload_migrations WHERE name = '<timestamp>_initial_schema'
);

Guarded and idempotent, so once prod is tracked, the step is a harmless no-op forever after. Migrations run the same way in every environment after that: locally against Docker Postgres, on a PR's own Neon branch during its preview build, and against production on merge. Where those databases physically live, and everything involved in getting a change safely onto each one, is its own story: part 4.

Two ways the chain itself has broken

Both of these are failures of the migration mechanism, not of the infrastructure around it.

A migration's own SQL can half-commit. Payload runs a generated migration's SQL with per-statement autocommit, not as one transaction. While shipping the comments feature, a CREATE TABLE statement failed partway through a migration, but the CREATE TYPE statements ahead of it in the same file had already committed. The result was an enum type that no table referenced, and the next migration attempt on that database immediately died on type "..." already exists, a fresh failure caused entirely by the wreckage of the first one. Nothing in the migration file was wrong; the execution model just doesn't give a failed migration the clean, all-or-nothing rollback you'd get from a single transaction.

The chain is a literal array, and it's not commutative. The list of migrations Payload actually runs is exactly what that sounds like: a hand-generated array, in order, of every migration this repo has ever run.

export const migrations = [
  { up: migration_initial_schema.up, down: ..., name: '<timestamp>_initial_schema' },
  { up: migration_series.up, down: ..., name: '<timestamp>_series' },
  // ...
];

Each entry's .json snapshot is the diff base for the next one. Branch two schema-changing PRs off the same commit and their migrations are siblings, not links in the same chain: the second one's snapshot was generated with no knowledge of the first. When taxonomy and versioning shipped as parallel PRs and the two collections turned out to interact, the migration that had already merged was quietly incomplete the moment the second one landed. There's now a CI job for exactly this: pnpm db:verify-migrations applies every committed migration to a throwaway, empty Postgres and asserts the result has zero drift from the current config. A sibling migration shows up as CI red instead of a discovery made later, against a branch that already has real rows in it.

What actually makes a migration safe

Every gotcha in this post comes down to the same thing: a migration file is a piece of code with its own failure modes, not a passive record of what changed. It has to be idempotent, because it can be retried. It has to assume the database it's running against might already be partway through the change, because per-statement autocommit means that's a real state, not a hypothetical one. And it has to be written with the knowledge that its snapshot might already be stale by the time it merges, because someone else's migration could land first. None of that is enforced by the tool. It's a set of conventions this repo learned by breaking each one exactly once.