how should agents test database migrations without touching production?

Agents make schema changes cheap to propose. They do not make state cheap to recover.

A migration can pass locally, pass CI, and still fail in production because one historical row violates a new constraint, an index holds a lock longer than expected, or an older application instance writes a shape the new schema no longer accepts. The SQL may be correct. The transition is not.

The right test target is therefore not “the database after the migration.” It is the whole path from the current application and data shape to the next one.

That path belongs in an isolated production-shaped preview, not in production and not in a shared development database.

an empty database proves less than it appears to

An empty database is useful. It proves that the migration history can construct the schema from zero, every revision is present, statements are valid, and the application can start against the result.

That is the bootstrap contract. Every migration suite should retain it.

But most dangerous migration failures depend on data that an empty database does not contain:

• a nullable column with historical nulls becomes NOT NULL;
• two old rows collide under a new unique constraint;
• an enum contains a value the new application no longer recognizes;
• an orphaned child row breaks a foreign key;
• a backfill assumes every account has a related profile;
• a type conversion fails on one legacy string;
• a large update holds locks or produces more load than expected.

If an agent tests only the final schema on empty state, it has tested construction, not migration.

The distinction matters because database changes operate on two inputs: the migration code and the state already present when it runs. Source control contains the first. A useful test environment must deliberately provide the second.

build a data ladder, not one perfect fixture

There is no single test dataset that proves every migration safe. Use a ladder in which each rung answers a different question.

1. empty state

Run the entire migration history from zero. This catches broken revision ordering, missing extensions, invalid SQL, incorrect defaults, and applications that cannot boot on a freshly constructed database.

Empty state should be the fastest and most frequent check. It is also the weakest evidence about historical data.

2. small adversarial fixtures

Seed the preview with rows selected to break the migration's assumptions. A new constraint deserves fixtures on both sides of the boundary. A backfill deserves nulls, missing relationships, duplicate candidates, old formats, and rows at size limits.

The goal is not production volume. It is semantic coverage.

For a migration that replaces published boolean with a status enum, useful fixtures include published and unpublished rows, rows created by older code paths, nulls if the old contract allowed them, and an unexpected value if the source was previously unvalidated.

Fixtures belong in version control. A reviewer can see which historical shapes the agent considered, rerun them, and add the missing case without copying customer data.

3. an approved development clone

Some failures depend on relationships that are too costly to recreate by hand. A branch-scoped copy of development data can reveal realistic table density, foreign-key graphs, unexpected nulls, and accumulated state from older application versions.

This remains non-production evidence. Development data may be cleaner, smaller, or less diverse than production. It complements adversarial fixtures rather than replacing them.

4. production-shaped migration rehearsal

High-risk changes may need a sanitized snapshot or production-shaped synthetic dataset outside the normal preview loop. That rehearsal should preserve the properties relevant to the migration, such as cardinality, skew, row widths, constraint violations, and table sizes, without exposing customer data to the agent.

If that capability does not exist, say so. “Tested on development data” is useful evidence. It is not evidence about production volume or every historical row.

test the transition, not just the destination

Deployments are not instantaneous. During a rolling release, an old application instance may still run after the schema expands. A new instance may start before a data backfill completes. A worker can lag behind the API. A rollback can send traffic to old code after the new migration has already committed.

A migration test should therefore exercise a compatibility matrix:

The last row is the one teams most often skip. Moving traffic to an earlier application image does not reverse a committed migration. If the old code cannot run against the new schema, application rollback is not a recovery plan.

This is why the safest schema changes are usually additive. Old code ignores the new column. New code tolerates the old column. Both versions can coexist while the data moves.

expand, migrate, contract

The expand-and-contract pattern separates a risky replacement into reviewable transitions.

Suppose the application is replacing published boolean with a richer status enum.

expand

Add the new field without removing the old one. Deploy code that can read the old representation and write both representations. Verify that old and new application versions work against the expanded schema.

migrate

Backfill the new field in bounded batches. Measure progress. Make the operation retryable. Verify that newly written rows and historical rows converge on the same contract.

For large tables, the backfill should not be hidden inside a startup migration that holds the release open. GitLab's database guidance recommends background migrations for large changes so the work is spread over time rather than placing sustained pressure on the deployment path. Its migration guide also describes adding a new representation, copying data, switching application code, and migrating the remainder.

switch

Move reads to the new field after the backfill is complete and verified. Stop writing the old field only after every still-running application version can tolerate that change.

contract

Remove the old field in a later change. This is the destructive boundary, not an incidental cleanup line at the bottom of the additive migration.

Prisma documents the same expand-and-contract shape: add the new representation, migrate the data, then remove the old representation after the application has switched. The useful principle is independent of the framework. Schema expansion, data movement, application cutover, and cleanup have different failure and recovery modes.

constraints deserve their own proof

Constraints turn assumptions into enforced contracts. They can also turn one unexpected historical row into a failed deployment.

Before adding a unique, foreign-key, check, or not-null constraint, an agent should:

1. query for violating rows in the preview dataset;
2. add adversarial fixtures that reproduce each known violation shape;
3. decide how historical violations are repaired or excluded;
4. verify new writes satisfy the constraint before validating old rows;
5. measure the validation step separately from adding the constraint.

Postgres supports adding foreign-key and check constraints as NOT VALID, which enforces them for new writes without first scanning every historical row. A later VALIDATE CONSTRAINT checks existing data with a less restrictive lock than adding the constraint in one operation. The Postgres ALTER TABLE documentation shows this two-step form.

That does not make every constraint change safe automatically. It creates a useful separation: prevent new violations, repair the existing set, then validate the invariant.

An agent can propose and test that sequence. The evidence should state which rows were examined, how many needed repair, whether validation completed, and what lock or runtime behavior remains unproven at production scale.

make the agent return a migration report

The output of the test loop should be a structured report, not “migration passed.”

At minimum, record:

• source commit and migration revision;
• preview environment and database branch identifiers;
• starting data mode: empty, seeded, or cloned from development;
• schema state before and after;
• row counts examined and changed by each backfill;
• constraint violations found before repair;
• compatibility results for old and new application versions;
• migration and backfill duration on the preview dataset;
• recovery behavior after a simulated application rollback;
• confirmation that development and production were not modified;
• unresolved differences between the preview dataset and production shape.

This makes the human decision legible. A reviewer does not need to reproduce the agent's entire session to understand what was tested and what remains risky.

It also prevents a common category error: treating a successful command exit as proof of a safe production transition. The exit code proves that one operation completed against one dataset. The report explains what that result means.

put the human at the irreversible boundary

Requiring approval for every additive migration turns the database workflow into a ticket queue. Allowing an agent to drop columns or rewrite shared state without review puts the boundary too late.

A useful policy separates actions by consequence.

Agents can usually create preview-owned state, run migrations there, reset it, seed it, exercise compatibility, and gather evidence without approval. These actions are isolated and disposable.

Human review belongs where the next action can cause durable harm:

• dropping a table or column;
• removing a managed database;
• narrowing a type or constraint in a way that rejects existing data;
• running a large or irreversible backfill;
• applying a migration whose lock or runtime behavior is not bounded;
• cutting over after compatibility evidence is incomplete.

The review should apply to the exact proposed change and its evidence, not grant the agent a broad production credential. That is the broader rule behind human approval gates for software agents: authority expands for a reviewed plan, not for an open-ended session.

Database platforms are converging on this model. PlanetScale's deploy requests make schema changes reviewable, check for incompatibilities and potential data loss, and can hold the final cutover behind a gate. Its deploy-request documentation describes those review and cutover stages. The implementation differs from Postgres, but the control principle is the same: separate preparation from consequential apply.

where floo fits

On floo, a branch can run in a production-shaped preview with a preview-owned database branch. The preview uses the application's migration lifecycle while development and production remain untouched.

The data mode states what evidence the test contains. Empty is the default and proves clean construction. A seed command can install adversarial fixtures after migrations. Clone-from-development copies supported floo-managed development state into the preview before migrations run. floo does not describe any of those modes as a production data copy.

The preview database branch is inspectable, resettable, and tied to the preview's lifecycle. An agent can rerun the transition from a known state, inspect the resulting branch, and return structured identifiers without receiving production database credentials.

That is enough substrate for the migration loop: construct, hydrate, migrate, run the application, inspect evidence, reset, and try again. The application still owns its fixtures, compatibility checks, and recovery design.

The boundary remains explicit when the proposed change becomes destructive. Auditable infrastructure changes flow through the repository, and floo keeps human review on data-loss operations rather than allowing an agent to turn preview evidence into an unreviewed production action.

The goal is not to prove that databases are risk-free. It is to make each claim narrow and testable. Empty state proves construction. Representative state tests assumptions. Compatibility checks test the rollout. A human decides whether the remaining production risk is acceptable.

Agents can make the loop much faster. Isolated state and explicit boundaries keep faster from meaning harder to recover.