A coding agent can write a migration in seconds. Deploying it is a different matter: a migration changes shared, long-lived state, runs against production-sized tables, and often has to work while an older version of your application is still serving traffic. A diff that looks tidy can still drop data, lock a busy table, or break the code that is running during the deploy.

This guide walks through one small example migration and the questions a reviewer should ask before it ships. The SQL examples use PostgreSQL, and the behavior described links to the PostgreSQL 18 documentation. Other databases behave differently, so check your own engine's documentation for each point.

The example: a small request, a larger migration

Suppose you asked an agent: "Show a display name on the profile page. Use the user's full name for existing users."

The agent changed the profile template, the user model, and added this migration:

-- EXAMPLE ONLY: migrations/20260924_add_display_name.sql
-- Written to illustrate review problems. Do not run it.

ALTER TABLE users ADD COLUMN display_name text NOT NULL DEFAULT '';   -- (1)
UPDATE users SET display_name = full_name;                             -- (2)
ALTER TABLE users DROP COLUMN full_name;                               -- (3)
CREATE INDEX users_display_name_idx ON users (display_name);           -- (4)
ALTER TABLE orders ALTER COLUMN total TYPE numeric(12,2);              -- (5)

Each line is plausible on its own. Together they deserve several questions. Work through them in the order below: the first ones are the hardest to undo.

1. Check for data loss first

Look for any statement that removes or narrows information: DROP TABLE, DROP COLUMN, TRUNCATE, DELETE, an UPDATE without a WHERE clause, and type changes that could truncate or round values.

In the example, line (3) drops full_name. The request asked to copy full names into a new column, not to delete the source. If anything else reads full_name (a report, an export job, another service), that data is gone after the deploy.

Two details make this easy to misjudge:

  • Dropping a column is fast, which can make it feel harmless. PostgreSQL's ALTER TABLE notes explain that DROP COLUMN makes the column invisible rather than rewriting the table. That speed says nothing about whether you can get the data back. Plan on restoring from a backup if you are wrong.
  • Type changes can lose precision. Line (5) converts orders.total to numeric(12,2). Values with more than two decimal places are rounded to fit the new scale, and values too large for the precision make the migration fail (numeric types). Check the current type and real data before accepting it.

Also check line (2). It copies full_name exactly, including NULL. Because display_name is NOT NULL, any user without a full name makes the UPDATE fail. A reviewer should ask what the data actually contains, not only what the schema allows.

What to ask: Which statements destroy or reshape existing values? Did the request call for that? Is there a tested backup or copy of anything being removed?

2. Check compatibility with the application that is already running

During most deploys there is a window when the new schema is live and the old application code is still running, or the reverse. A migration is safe to deploy only if every version of the code that can run against it still works.

Here, the moment line (3) runs, any old application instance that still selects full_name starts failing. The agent updated the user model in the same change, but that does not help the instances deployed before it.

A common way to handle this is Martin Fowler's parallel change pattern, also called expand and contract:

  1. Expand: add the new column and keep the old one. Deploy code that writes to both, or reads the new one with a fallback.
  2. Migrate: backfill existing rows and move every reader to the new column.
  3. Contract: in a later, separate deploy, drop the old column once nothing uses it.

For this request, the agent's migration should stop after the expand step. The DROP COLUMN belongs in a later change, if it is wanted at all.

Search the codebase, not just the diff, for the old name. Raw SQL strings, reporting queries, background jobs, and other services that share the database are easy to miss.

What to ask: Does the old version of the app work against the new schema? Does the new version work against the old schema, if the code deploys first? Is anything removed that something outside this diff still uses?

3. Check locks and deployment order

A migration that is correct can still cause an outage if it blocks traffic on a busy table. Read each statement for how long it runs and what it locks.

  • Adding a column with a constant default. Line (1) is usually fine in current PostgreSQL. When ADD COLUMN has a non-volatile default, the value is stored in metadata and existing rows are not rewritten (ALTER TABLE notes). A volatile default, such as a function that returns a different value per row, does require a rewrite.
  • Changing a column's type. Line (5) normally rewrites the whole table and its indexes, according to the same notes. On a large orders table, that can take a long time while holding a strong lock.
  • Creating an index. Line (4) uses a plain CREATE INDEX, which blocks inserts, updates, and deletes on the table until the build finishes. CREATE INDEX CONCURRENTLY avoids that, but it cannot run inside a transaction block, and a failed build leaves an invalid index that you need to drop and retry. Many migration tools wrap each migration in a transaction by default, so check how yours handles this.
  • Large updates. Line (2) updates every row in one statement. On a big table, that is one long transaction. Consider whether the backfill should run in batches, separate from the schema change.
  • Adding constraints. If an agent adds a CHECK or foreign key, PostgreSQL can add it as NOT VALID and check existing rows later with VALIDATE CONSTRAINT, which takes a weaker lock (ALTER TABLE).

Even a quick statement can wait behind a long-running query and block others behind it. PostgreSQL's lock_timeout setting lets a migration give up instead of waiting indefinitely. The explicit locking chapter lists which commands take which locks.

Then decide the order. For this example, a safer sequence is:

  1. Deploy a migration that only adds display_name (nullable, or with a default).
  2. Deploy code that writes display_name and reads it with a fallback to full_name.
  3. Backfill in batches, then build the index concurrently.
  4. Later, and only if it is still wanted, remove full_name.

What to ask: How big are the affected tables in production? Which statements rewrite a table or block writes? Does the code or the schema need to deploy first?

4. Check the rollback plan

Many migration tools let a migration define a "down" step. Read it as carefully as the "up" step, if the agent wrote one at all.

For this migration, a down step could re-add full_name, but it cannot restore the names that were deleted. Rolling back a column type change also cannot restore digits that were rounded away. When a migration destroys information, the realistic rollback is a restore from backup, and you should know how long that takes before deploying.

Expand-and-contract helps here too. If the first deploy only adds a column, rolling back the application is enough: the old code ignores the new column. The irreversible step is isolated in its own deploy, where you can review it on its own.

What to ask: If this deploy goes wrong halfway, what state is the database in? Can we roll back the code without rolling back the schema? Which steps can only be undone from a backup?

5. Check for changes that don't belong

Line (5) changes orders.total. The request was about display names on a profile page. The type change might be a reasonable fix the agent noticed, or it might be a guess. Either way, it has a different risk, a different owner, and a different rollback story, and it should not ride along with a profile-page feature.

Look beyond the migration file too. An agent working on a migration can also change seed data, the schema snapshot your ORM generates, fixtures, or the migration runner's configuration. Check that generated files match the migration you are keeping, and that no earlier migration was edited. Migrations that have already run elsewhere usually should not change after the fact, because environments that applied the old version will not rerun it.

If an unrelated change is useful, take it out of this change and review it as its own migration.

What to ask: Does every statement follow from the request? Were existing migration files edited? Do generated schema files match what you are keeping?

Where Diffward fits

Diffward is a VS Code extension that groups a local coding agent session into one review. It helps with the scope and review steps above. It does not analyze SQL, measure locks, or tell you whether a migration is safe.

  • Files under a migrations/ or migrate/ directory are treated as database migrations and brought forward with other high-risk changes, so they are less likely to get lost in a large diff. Files elsewhere may not be recognized this way.
  • Files that look unrelated to your request are shown first. This is a heuristic, not a verdict: treat it as a prompt to look.
  • You can ward (keep) or discard each hunk in the working tree and undo either decision, which makes it practical to keep the expand step and set aside the DROP COLUMN and the orders change for separate work.

The database questions in this guide still need a person who knows the schema, the data, and the deploy process.

A short checklist

Before deploying an agent-written migration, you should be able to answer:

  • Data: Which statements delete, overwrite, or narrow data, and did the request require them?
  • Compatibility: Will the old and new versions of the app both work while the deploy is in progress?
  • Locks: Which statements rewrite tables or block writes, and how large are those tables in production?
  • Order: Should the schema or the code go first, and which steps should be separate deploys?
  • Rollback: What does undoing this look like, and which parts can only be restored from backup?
  • Scope: Does every statement and file belong to this request?

Run the migration against a copy of production-like data where you can, and run the tests that cover the code reading these tables.

Conclusion

An agent can produce a migration that satisfies the request in the fewest lines. A reviewer's job is to ask what those lines do to data that already exists, to code that is already running, and to a deploy that might need to stop halfway. Most of the fixes in this example are small: keep the old column, split the change into steps, build the index concurrently, and move the unrelated change out. They are much easier to make in review than after the migration has run.