How to Plan a Database Migration With AI Safely
The ALTER is one line. The migration is everything around it — because for a while, two versions of your code share one database. Here's how to turn a schema change on a live table into phases that stand alone, a backfill that can resume, and one step you can never take back.
Build a Migration Plan PromptThe one-line change that isn't one
Splitting a name column into two is a five-second thought and a one-line statement. On an empty table it *is* a one-line statement. On four million rows with signups landing every second and four different things touching that column, the statement is the smallest part of the job — and the part least likely to hurt you. What hurts you is everything it doesn't say: that the old code is still deployed and still selecting the column you're about to drop, that a single UPDATE across four million rows is a very different event on your engine than on the one the model was thinking of, that once the original string is gone you have no way to check whether the split was right, and that nobody has decided how to split "Mary van der Berg" yet.
That gap is why asking an AI to "write the migration" gets you a confident script and no plan. The script is the easy half. A migration is a *schedule* — a sequence of changes, each of which has to leave the system working for whatever code happens to be running at that moment, with a way back from every step until the one where there isn't. The model can draft that schedule, but only from things it cannot know unless you write them down: your current shape and target shape, how many rows, what reads and writes them, what your traffic looks like, how you deploy, and how much you can tolerate going wrong. NewPrompt's part is the prompt and its structure — nothing touches your database: no connection to it, no reads of it, no writes to it, no DDL, and nothing run on your behalf. Any SQL that comes back is candidate text rather than a safe command: it hasn't met your engine, your version, your indexes, or your data, and it should not be assumed safe because it looks right. The plan is a draft your database owner, the people who own the applications reading that table, and whoever runs your operations all sign off on. And no plan buys a guarantee — not zero downtime, not a safe migration, not this time.
For a while, two versions of your code share one database
Almost every hard thing about migrating a live database comes out of one fact, and it's worth sitting with before any planning starts: a deploy is not instant. For some window — a rolling restart, a canary, a client that caches, a worker that's mid-job — old code and new code are both running, and they are both talking to the same tables. The old code knows the old shape. The new code wants the new one. The database can only be in one state.
That's the constraint every technique in this guide exists to satisfy. It's why the change gets split into phases instead of shipped as one statement: each phase has to be survivable by *both* versions, which means the schema has to stay compatible with the old code right up until the old code is gone — and the same rule, read backwards, is why the destructive step has to wait until nothing writes the old shape either. It's why a backfill isn't a one-shot UPDATE. And it's why the interesting question in a migration is never "does this statement work" — it's "what is running against this table while the statement runs, and what does it see?" A plan that can't answer that isn't a migration plan. It's a script with optimism around it.
- Old code has to keep working — so nothing it reads or writes can move or disappear while it's still deployed. That cuts both ways, and the second way is the one people miss: you cannot drop a column that a running build still writes.
- New code has to keep working — so the new shape has to exist before the code that uses it ships.
- Existing rows are in the old shape and don't fix themselves — that's the backfill, and it runs while traffic keeps arriving.
- Rows written *during* the migration are the ones people forget — if new writes keep landing in the old shape, your backfill is chasing a target that refills as fast as it drains.
Step 1: Write down both shapes, and everything that touches them
Start with the two shapes side by side: what the schema is now, exactly, and what you want it to be. Then the number that changes every decision downstream — how many rows. A migration on 4,000 rows and the same migration on 40 million are different problems with the same DDL, and a model given no row count will cheerfully hand you a plan that works fine on the first and takes the site down on the second. Add what the table's traffic actually looks like: reads and writes per second, whether there's a quiet window, whether writes are bursty. And name your engine and its version, because the same statement is free on one and a table rewrite on another.
Then the part people consistently skip, which is the inventory of everything that touches the table — and it has two columns, not one. The readers: the application, the admin tool nobody maintains, the nightly export, the dashboard someone pointed at a replica. The writers: the application, that same admin tool, the queue consumer, the support script someone runs by hand. Readers determine when it's safe to *change* a shape. Writers determine when it's safe to *stop maintaining* one — and they're the ones that decide whether your backfill ever finishes. A reader you forgot breaks at deploy time and you find out immediately. A writer you forgot keeps quietly producing rows in the old shape, and you find out from a count that won't settle. The model cannot discover either list for you; it will assume your application is the only thing touching the table, because that's the common case and it has nothing else to go on.
Step 2: Split it into phases that each stand alone
The sequencing pattern that falls out of the two-versions problem is expand, migrate, contract: add the new shape alongside the old one without disturbing it; move the data and the code over while both shapes are live; and only then remove the old shape, once nothing reads *or writes* it. The reason it's the default isn't elegance — it's that every phase is independently shippable and, for most of the sequence, independently reversible. You can stop after any of the early phases and be in a consistent state. A one-step migration gives you exactly one moment where everything must be correct at once, and no way to stand still.
It's worth separating the two things the word "dual" hides, because drafts blur them and they have different price tags. Dual-write — new code writing both shapes on every insert and update — is what makes the backfill's set finite; without it, old instances keep producing old-shape rows and the set never stably empties. You skip it only if you can freeze writes. Dual-read — both shapes being correct for two different readers at the same time — is the expensive half, and it's the one you can often skip: when nothing reads the new columns until cutover, they have to be right, not right-in-step. A model will propose both by default, because both are the conservative answer and conservative is a safe guess when you know nothing about the system. Only the first is usually the one you can't do without.
The order inside the pattern carries real weight, and it's where drafts most often go subtly wrong in both directions. At the front: dual-write has to be *fully deployed* before the backfill starts, not alongside it — ship them together and old instances keep creating old-shape rows while your backfill tries to finish, so you can never tell done from stuck. At the back, the mirror image: before you drop the old column, the code that still writes it has to be gone. Cutover switches *reads*; it doesn't stop writes. If the drop lands while the deployed build is still writing that column, every insert and update fails on contact — which is the same two-versions mistake as the naive plan, just wearing a more careful-looking schedule.
Step 3: Make the backfill boring
A backfill touches every row you have, and the goal is for it to be the least interesting thing that happens that week. Three properties get it there. **Batched**: a bounded number of rows per pass, walking a key range, so no single transaction runs long enough to matter. **Resumable**: it can stop at any point — a deploy, a timeout, someone's laptop closing — and pick up where it left off, which means its progress lives in the data rather than in the process's memory. **Idempotent**: running it twice over the same rows changes nothing the second time, because the condition that selects rows is the condition the work satisfies. A status column is the whole trick: rows leave the set as they're marked, so a rerun is free and a partial run is just a shorter run.
Run it beside production rather than through it. A backfill that borrows the application's connection pool competes with the traffic it's supposed to be invisible to, and one that loops row-by-row through an ORM turns four million rows into four million round trips. Throttle on purpose, and gate each batch on replica lag if you have replicas — finishing in twenty minutes by saturating your write capacity is worse than taking six hours in the background, because the fast one is an incident and the slow one is a job. Days is normal on a genuinely large table.
The other half of boring is refusing to guess. When a row can't be transformed unambiguously, the backfill marks it for review — and *marking* is not the same as leaving it alone. A row left silently unconverted is indistinguishable from a row the backfill never reached, which quietly breaks the completeness check in the next step and puts that row on a path toward the drop with nothing in the new columns. Give the backfill three outcomes rather than two: converted, needs-review, and not-yet-touched. Then the exceptions are a work queue with a name, instead of a slow leak you find later.
Step 4: Prove the data before you trust it
"The backfill finished" is a statement about a job, not about your data. Before anything switches over, the plan needs checks that establish the new shape is actually correct — written down as part of the plan, not improvised at cutover by whoever's awake. Completeness is the obvious one: every row has been triaged, so nothing is still untouched. The more interesting one is drift: with dual-write fully deployed, no *new* untouched rows should be appearing at all, so a count that won't settle is telling you something is still writing the old shape and you haven't found it. That's the check that catches the writer you missed in Step 1 — and it's worth being precise about what it looks like, because it rarely looks like a count stuck above zero. It looks like a count that reaches zero, then doesn't stay there.
The exceptions need their own gate, separate from completeness. Rows marked needs-review are not done; they're a decision someone owes you. They have to be resolved by a person before the old column can be dropped, because after that the material those rows were flagged against is gone. A plan that counts them as "handled" because they're recorded has smuggled the failure past the one check designed to catch it.
Then reconcile rather than count, because a count only proves rows were touched, not that the values are right. Compare a sample of real rows across both shapes by hand — a few hundred, chosen randomly rather than from the top of the table, since the top is your oldest and least representative data. Look specifically at the ugly cases you already know about. And hold parity across a full traffic cycle before you believe it: a quiet Sunday proves nothing about Monday's signup burst, and the failure you're looking for is a code path that only runs under load or only for one kind of customer.
Step 5: Find the step where back stops being an option
Every migration has a moment after which "undo it" stops being a real answer, and the plan's job is to name that moment before you're standing on it. It's almost always the contract step — the drop. Up to that point the old shape still exists, so reverting a deploy puts you back somewhere consistent. After it, a code rollback doesn't restore the column: the old code comes back and finds the data gone. Recovery becomes a restore to a point before the drop — losing whatever happened after that point, or reconciling it by hand — or a fix that goes forward. This is the practical reason a backup is not a rollback plan: it's a floor under total loss, not a way back to five minutes ago, and the difference stops being academic at exactly the hour you'd rather it didn't.
So mark the phases by what they cost to reverse, and let that drive the schedule. The early ones are cheap to undo, which is why they can ship quickly. The drop is not, which is why it ships alone, later, deliberately — after reads have moved, after the writes to the old column have stopped, and after the new shape has survived real traffic for a while. You want distance between the moment you stop needing the old column and the moment you destroy it. For irreversible steps the question also changes from "how do we roll back" to "what's the forward fix": sometimes going forward is genuinely safer than going back, and a plan that only knows how to reverse has no answer at the one moment it matters.
Cutover deserves a criterion rather than a date. "We switch reads on Thursday" is a plan to switch reads whether or not the data is ready. "We switch reads when every row is triaged, no exceptions are outstanding, and the sample comparison has been accepted by the person who owns this data" is a criterion the data either meets or doesn't. Same for done: the migration is complete when the old shape is gone *and* nothing broke, not when the last statement returned. Name who signs off on crossing into the irreversible phase, by role — not because process is nice, but because that's the one boundary where you want a second person to have looked.
Common mistakes
The failures that don't show up in the plan, and do show up in production:
- Dropping a column the deployed build still writes. Cutover moves reads; it doesn't stop writes. If the drop ships before the build that stopped writing that column has fully rolled out, every insert fails on contact — the naive plan's mistake with a better schedule around it.
- Assuming an added column is invisible to old code. It usually is, until something does `SELECT *` into a strict deserializer, or `INSERT`s without a column list, or uses an ORM whose model is frozen at the old shape. The admin tool nobody maintains is exactly that code.
- Running the backfill through the application. Borrowing the production connection pool means competing with the traffic you're trying not to disturb, and looping row-by-row through an ORM turns millions of rows into millions of round trips. Run it beside the app, batched, throttled, and gated on replica lag.
- Testing the migration against an empty staging database. Every interesting failure here is a function of volume and concurrency. A migration that passes on 200 rows with no traffic has demonstrated that the syntax is valid and nothing else.
- Re-adding NOT NULL at the end as if it were free. Validating a constraint against a full table can hold it for a long time; engines that support adding it unvalidated and checking it in a separate pass exist for exactly this reason. It's the last step and it deserves the same care as the first.
- Counting rows and calling it verified. A count proves rows were touched, not that the values are right — and a count that reaches zero and won't stay there is a writer you haven't found, not a rounding error.
- Running the AI's SQL because it parses. It hasn't seen your engine, your version, your indexes, or your row count, and those are what decide whether a statement is instant or an outage — NewPrompt never touches your database, so every statement is a draft for someone who knows your system.
Splitting a name column on a live table
A change small enough to describe in one sentence, worked out to the point where the interesting parts are visible.
A name-column split on 4.2M live rows, phase by phase, each marked by what it costs to reverse — with the split rule pulled out as a decision that blocks phase 2, and the drop gated behind both the writes stopping and every flagged row being resolvedWHAT'S CHANGING:
users.full_name TEXT NOT NULL -> users.given_name + users.family_name
4.2M rows | live signup and login traffic, ~40 writes/sec at peak
engine and version: <yours -- it decides half of what follows>
readers: the web app | the admin tool | a nightly export |
a dashboard pointed at the replica
writers: the web app | the admin tool | the signup queue consumer
^ this column is the one that decides when you can drop
PHASE 0 - DECIDE THE SPLIT RULE
not a migration step. a product decision, and it blocks phase 2.
how do you split "Mary van der Berg"? "Dr. Jose Luis Garcia
Marquez"? a mononym? asked to fill the column, a model will split on
the first space and write family_name = "van" 4.2 million times.
this is a question about your users, not about SQL.
owner: whoever owns the customer record.
rows the rule can't decide get marked, not guessed (phase 3).
THE ONE-STEP VERSION, AND ITS THREE FAILURES:
ALTER TABLE users ADD COLUMN given_name TEXT NOT NULL;
-> engine-dependent, and the failure modes differ in kind.
Postgres rejects it: the column would contain nulls.
MySQL will often accept it and fill 4.2M rows with an implicit
empty string -- at which point given_name IS NULL matches
nothing and a backfill keyed on it reports success having done
no work at all. that is the dangerous one: it looks like it
worked. (the variant that rewrites the table under lock is
ADD COLUMN ... NOT NULL DEFAULT 'x', on Postgres before 11.)
this is the whole argument for phase 1: add it nullable and
let the backfill own the values.
UPDATE users SET given_name = split_part(full_name, ' ', 1);
-> 4.2M rows in one statement. depending on the engine it either
locks or bloats -- and it discards "der Berg" on every name
with more than two words. outside a transaction, a failure at
row 3M leaves the table half done with nothing recording where
it stopped. (split_part is Postgres-specific; yours varies.)
ALTER TABLE users DROP COLUMN full_name;
-> needs no help from the database to hurt you. every instance
still running the old build selects full_name and starts
failing the moment it lands.
PHASE 1 - EXPAND reversible: drop them
add given_name TEXT NULL, family_name TEXT NULL, split_status TEXT NULL
nullable, no defaults. nothing reads or writes them yet.
old code cannot tell this happened -- unless it does SELECT * into a
strict mapper or INSERTs without a column list. that's what the
reader inventory is for.
PHASE 2 - DUAL-WRITE reversible: redeploy
new build writes full_name AND the split columns on every insert and
update, using the phase 0 rule. it still READS full_name.
wait until every instance runs this build before phase 3. this is
the ordering that makes the backfill's set finite.
PHASE 3 - BACKFILL reversible: nothing reads them
UPDATE ... WHERE split_status IS NULL -- batched by id range,
5k rows a pass, sleep between passes, gate on replica lag.
idempotent and resumable: rows leave the set as they're marked, so a
rerun is free and a partial run is just shorter.
three outcomes, not two:
split_status = 'auto' the rule decided it
split_status = 'needs_review' the rule couldn't -- a human owes
an answer. columns stay NULL.
split_status IS NULL not reached yet
run it beside the app, not through its connection pool.
PHASE 4 - VERIFY nothing to reverse yet
complete: rows WHERE split_status IS NULL -> must be zero
drift: that count must reach zero AND STAY there. if it settles
and then refills, something still writes the old shape --
the writer you missed, not the reader.
exceptions: rows WHERE split_status = 'needs_review' -> a work queue.
these are NOT done. they are the reason phase 7 has a
gate, because phase 7 destroys what they'd be resolved
against.
sample: 200 random rows compared by hand, ugly cases included.
hold parity across a full traffic cycle. Sunday proves nothing.
PHASE 5 - CUTOVER reversible: switch reads back
reads move to given_name/family_name. writes STILL fill both.
the criterion is the data, not the calendar:
zero untouched, zero needs_review outstanding, sample accepted
by the data owner
PHASE 6 - STOP DUAL-WRITE reversible: redeploy
new build writes only the split columns. wait until every instance
runs it.
this phase exists because phase 5 didn't do it. cutover moved the
reads; the writes kept going. skip this and the drop takes down
every write on the next phase.
PHASE 7 - CONTRACT IRREVERSIBLE - the last exit
gate: nothing writes full_name (phase 6 fully rolled out) AND no row
is still needs_review.
drop full_name.
ships alone, in its own release, after phase 5 has lived through real
traffic. from here a code rollback does NOT bring the column back:
recovery is a restore to a point before the drop, or a forward fix.
WHY THE DROP IS TRULY ONE-WAY:
the split loses two different things. the naive rule throws tokens
away -- "Mary van der Berg" split on the first space gives given
"Mary", family "van", and "der Berg" is simply gone. and even a split
that keeps every token can be wrong in a way you can never test:
once full_name is dropped there is no original to check a partition
against, and nothing to re-derive a corrected one from. that's what
makes a bad split permanent AND unprovable -- not that you can't
concatenate the parts back, but that you can't tell which partition
was ever the right one.Where this fits in NewPrompt
The concrete pieces are small and specific. The Database Migration Plan Prompt is a fill-in prompt whose role line is "planning a database migration that must deploy without downtime or data loss, while old and new code run simultaneously" — it asks for the expand step, the backfill, the dual-write and cutover, the contract, the reversibility of each step, and the steps that could lock a large table. You paste your current schema, your target, and your data into its one input slot; the Markdown Output Builder is the tool that builds that prompt, with those sections already in its setup, and it emits prompt text you run in your own AI assistant. Worth knowing before you use it: its sections list the backfill before the dual-write. That's a document order, not an execution order — and the execution order is the thing this guide is about, which is a good illustration of the general rule that a template hands you a default and your case decides whether it holds. Two neighbours sit either side: the Zero-Downtime Deployment Prompt covers keeping the service up across versions and explicitly sends the data specifics back to the migration prompt, and the Rollback Plan Prompt — built with the Multi-Step Prompt Builder, which emits an ordered sequence of prompts rather than running anything — does the reversal thinking as its own document.
It's worth being precise about which database problem this is, because there are several and they don't share solutions. Designing a schema is deciding what the shape should be, on paper, before there's data — the choices are about the domain, and being wrong is cheap. This is the other end: the shape is already decided, the table is already full, and the difficulty is entirely in getting from one to the other without breaking the code that's running. Tuning a slow query is a third thing, and it assumes a schema that isn't moving. There's also a version of this problem that isn't this guide at all: moving data from one system into another, exporting and importing and switching over. That's a data move, and its risks live in the transfer. What's here is in-place evolution — the same database, the same table, changing shape underneath live traffic — where the risk lives in the window when both shapes have to be true at once.
Migrations fail in a specific and unfair way: not when you run them, but later, quietly, to a subset of your rows. A backfill that guessed produces data that looks fine until someone reads it. A drop that landed while something was still writing takes down the writes, not the deploy you were watching. A count that said zero was measuring the wrong thing. None of it surfaces during the migration itself, so the tests you'd normally trust don't catch it and the graph stays flat. What a good plan buys you isn't confidence — it's phases small enough that each one can be wrong on its own, checks that would notice, and one clearly marked step where you stop being able to change your mind. The model can draft that structure from what you tell it in minutes. Whether the row count is right, whether the fifth writer exists, and how to split the awkward names are things it has no way to know, and they're what the migration actually turns on.