I’ve been building a personal project with Codex. Doesn’t matter what it does — the relevant part is that it needs a server, and for that Codex told me to use Supabase. Fine. I have no opinions about backend-as-a-service, I just want something that stores rows and doesn’t make me think about it.

Then it created a jobs table and put it in a file called supabase/migrations/20260821143210_create_jobs.sql:

create table jobs (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  created_at timestamptz not null default now()
);

alter table jobs enable row level security;

Fine. That’s a table. The next day I asked for a status column, and it made another file, supabase/migrations/20260822091544_add_status_to_jobs.sql:

alter table jobs add column status text not null default 'draft';

One line. In its own file. Forever.

And that’s where I got stuck. Why two files? I have one table. It has one shape. Why isn’t there a single schema.sql that says “here’s jobs, here are its columns, go make the database look like this”?

My instinct as an iOS engineer is that the current state is the truth. I don’t keep a folder of every edit I’ve ever made to a struct. I keep the struct.

I had to ask a couple of questions here and there until I was able to put it together.

1. Your database already has data in it

This is the part I kept skipping past.

Locally, my Postgres database is basically empty and I could drop the whole thing and rebuild it from a snapshot. Nobody gets hurt. Production is different — it has real rows in it.

Say you have a users table with 10,000 rows and a single full_name column, and you decide to split it into first_name and last_name.

With only the old and new shapes: a schema-diff tool can see that full_name disappeared and two new columns appeared. What it can’t infer is how the existing data should get from one shape to the other. Should full_name be renamed, split between the new columns, or deleted?

It can generate the ADD and DROP statements that reach the new shape, but it can’t invent the data movement in the middle. If you accept that output without reviewing it, you can get the schema you asked for and still lose 10,000 people’s names.

With a migration: the file records the transition you chose. Add the two columns. Take the text in full_name, split it at the space, copy the pieces over. Then drop full_name.

That file — supabase/migrations/20260822104501_split_users_name.sql — is just SQL, and it’s short:

alter table users add column first_name text;
alter table users add column last_name text;

update users
set first_name = split_part(full_name, ' ', 1),
    last_name  = nullif(split_part(full_name, ' ', 2), '');

alter table users drop column full_name;  -- 👈👈👈 last, and only because the line above ran

Nothing clever in there. What matters is that it’s three statements in an order. The middle one — the backfill — is the entire reason the desired state isn’t enough on its own. There’s no place in “here’s what users looks like now” to say “and move the data across on the way.”

💡 That split_part is naive and I know it. “Mary Jane Watson” loses “Watson”, and one-word names end up with a NULL last_name. Which is sort of the point — the hard part of the migration is deciding what to do with the messy 3%, and that decision only exists in a file that describes steps.

A desired-state schema describes the destination. By itself, it has nowhere to put “and by the way, carry the data across on the way there.” Same reason a diff tool can’t always tell the difference between a rename and a drop-then-add — the before and after look identical, but one keeps your data and one doesn’t.

2. Two people, one file

I’m working alone, so I almost dismissed this one. Then I remembered I run Codex tasks in parallel across worktrees, which is the same problem wearing a hat.

If you’re adding a messages table while someone else adds invoices, and the schema lives in one master file, you might both touch the same area and get a merge conflict over changes that aren’t actually in conflict.

With migrations, you usually write one new file and they write another. That makes textual conflicts less likely. It doesn’t remove semantic conflicts though — your migration can still depend on theirs, or both can change the same table in incompatible ways.

3. Undo. Sort of

Migrations are a sequence, but that does NOT make them automatically reversible.

A lot of migration tools ask you for an up and a down — do the change, then describe how to undo it. But a down that adds full_name back can’t recover the 10,000 names you already dropped. It can restore the old shape. Getting the old data back requires a safe migration design, preserved data, or a backup.

Worth knowing: Supabase migration files aren’t generated as up/down pairs. The CLI now has supabase migration down, but that doesn’t magically invent a safe inverse for the SQL you wrote. For a migration already deployed to production, Supabase’s guidance is to make another migration that moves the schema forward to the state you want.

Locally, supabase db reset recreates the database and replays the whole chain from zero. That’s useful for testing the history. It is not a production rollback strategy.

The bookkeeping

There’s a table in your database (supabase_migrations.schema_migrations) that tracks which migration versions have already been applied. On push, the CLI compares that history against your folder and runs whatever’s missing, in order. The order comes from the version prefix in the filename, which Supabase normally generates as a timestamp.

Which is why editing a migration you already pushed does nothing to that remote database on the next push. Its version is already in the table, so it’s already “done”. A local db reset will replay your edited file, which means you can accidentally make local history disagree with what production actually ran. I found that out the honest way.

# format
<version>_<name>.sql

# examples
20260821143210_create_jobs.sql
20260822091544_add_status_to_jobs.sql

It’s git history, basically

The thing that made it click: migration files play the role of commit history, while a declarative schema plays the role of the checked-out files.

Git commits are snapshots internally, so this isn’t an implementation analogy. The useful part is that the history and the current view answer different questions. The checked-out files tell you what exists now. The history tells you how the project got there.

Migrations Desired-state schema
What it is Executable transitions The result you want
Git analogy Ordered history The checked-out files
Can update a DB with data? Yes Yes, after a diff generates a migration
Can carry a backfill or rename operation? Yes Not by itself
Merge conflicts on a team Usually less likely Depends on how files are split
Reversible Sometimes, with a safe inverse Not by itself
Good for “what does this table look like today?” Not really Yes

And the bit I didn’t expect: you can get both.

Supabase has a declarative workflow where files under supabase/schemas/ are the source of truth. You edit the current shape there, then supabase db diff generates a migration containing the transition. The schema files don’t replace migrations. They sit next to them and answer a different question.

In the traditional migration workflow, supabase db pull writes remote schema changes into a new migration file. It does not keep one always-current schema file for you. If you want a readable export of the current remote schema, that’s supabase db dump.

If you needed to publish these changes manually

Codex and Supabase handle this for me. But underneath, the workflow looks roughly like:

  • Change the schema locally however I want.
  • supabase db diff -f add_status_to_jobs — it looks at what my local DB has that my migrations don’t, and writes the SQL commands into a new migration file.
  • Inspect the migrations and carfully read it. A generated DROP COLUMN looks exactly as innocent as a generated ADD COLUMN. But a DROP action is a destructive one, while an ADD is not. Fix anything needed.
  • supabase db reset so the whole chain replays from zero on an empty database.
  • supabase db push --dry-run to see what the remote would apply.
  • Then supabase db push.

With the declarative workflow, the first step changes: edit the files in supabase/schemas/. Then db diff compares those files against the migrations. Direct changes made through Studio or the SQL editor aren’t picked up by that diff.

That reset is the step I skipped for the first week and it’s the one that gets you. If migration 4 depends on something migration 2 created, and you later “fixed” 2 in place, your local database is fine — it applied the old 2 long ago — and the chain is broken for everyone who isn’t you. On a personal project, that’s future me.

Summary

  • A desired-state schema describes where you want to be. A migration describes how to get there from where you are.
  • Supabase can keep supabase/schemas/ as the source of truth and generate migrations from it. You don’t have to choose one or the other.
  • Production already has data. That’s the whole reason this exists.
  • Intent — backfills, ambiguous renames, splits — only fits in the steps, never in the result alone.
  • The timestamp in the filename is the key that tracks what’s been applied. Never edit a migration you’ve already pushed; add a new one.
  • Migrations are not automatically reversible. Restoring an old shape is different from restoring deleted data.

I still think it’s a lot of files for one table. But I stopped being annoyed at it, which is its own kind of progress.

References