A Database Migration Agent That Proves the Recovery Path

Suppose a hypothetical subscription service needs to replace a free-text account code with a structured identifier. The application has multiple deployed versions, a nightly export, and an old reporting job that reads the column directly. An agent that generates a syntactically valid rename can still break the business before the first customer request reaches the new code.

A database migration agent should prepare and verify a change plan, not improvise SQL against production. Its output is an immutable migration artifact with compatibility evidence, execution limits, approval, validation queries, and a recovery decision tree. This reference design assumes PostgreSQL examples; other databases require engine-specific behavior checks.

Inventory Consumers Before Editing the Schema

Read schema metadata, migration history, application queries, scheduled jobs, and observed access patterns. Treat static references and runtime evidence as complementary. Dynamic SQL may evade repository search, while a sampled workload may miss a month-end report. Record coverage gaps and ask the owning team to confirm them.

Pin the database version, extensions, schema digest, table sizes, and representative workload. The PostgreSQL ALTER TABLE documentation explains that lock requirements vary by subcommand and that many forms take an ACCESS EXCLUSIVE lock. “Metadata-only” does not mean “no locking,” and a quick operation can wait behind a long transaction.

The LLM can explain dependencies and propose a staged plan. Deterministic software must validate SQL artifacts, inspect the live schema, enforce allowed operations, and stop when assumptions differ from the approved snapshot. A stale plan should become a new review, not a prompt asking the model to adapt during execution.

Expand, Backfill, Switch, Then Contract

For the account-code change, first add a nullable new column. Deploy code that can tolerate both representations. Backfill in bounded batches using a reviewed mapping, record exceptions, and verify new writes remain consistent. Only then switch reads. Remove the old column in a later release after all consumers and the rollback window have been addressed.

Choose one authoritative write path. Two independent application writes can diverge when one succeeds and the other fails. Within one database, a transaction can maintain both fields atomically where the business mapping permits. Across systems, use a durable replication or event strategy with reconciliation rather than pretending two successful API calls form a transaction.

The minimum architecture needs a metadata reader, artifact generator, isolated rehearsal environment, migration runner, approval service, and verification recorder. A specialist performance-analysis sub-agent earns its complexity only when it produces evaluated workload analysis. SQL execution, backup inspection, and checkpointing are deterministic tools, not autonomous planners.

Make Lock Budgets Visible

The following illustrative PostgreSQL SQL adds a check without scanning old rows in the first transaction. It is not a complete migration, and it assumes the named schema and column already exist. Run it only in a reviewed environment with suitable ownership privileges.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
ALTER TABLE app.accounts
  ADD CONSTRAINT account_code_present
  CHECK (account_code_v2 IS NOT NULL) NOT VALID;
COMMIT;

-- Later, after backfill and exception review:
BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '5min';
ALTER TABLE app.accounts
  VALIDATE CONSTRAINT account_code_present;
COMMIT;

The limits are proposed examples, not safe defaults for every workload. PostgreSQL documents that NOT VALID skips the initial old-row scan while enforcing the constraint for subsequent inserts and updates. Therefore, add this check only when all active writers can populate the new field; otherwise even an unrelated update to an old incomplete row can fail.

Production dependencies omitted include migration identity, transaction error handling, schema precondition checks, advisory coordination, monitoring, and approval. On timeout, inspect transaction outcome and migration history before retrying. An existing constraint with the same name is not proof it has the expected expression.

Recovery Is Not Always Reverse SQL

Before the change, rehearse restoring a backup into an isolated environment and running the application against it. PostgreSQL continuous archiving documentation describes point-in-time recovery using a base backup and a continuous WAL sequence. A logical dump is not interchangeable with that recovery chain.

Restoring an earlier cluster state can discard legitimate writes made after the chosen recovery point. It is not an ordinary per-table undo button. Define when application rollback is safe, when a forward correction is preferable, and when incident leadership must authorize restoration and transaction reconciliation.

Backfill checkpoints need a deterministic key range, mapping version, and completed-batch record. After a crash, rerun only idempotent transformations or verify the batch’s postcondition before continuing. Avoid treating “last processed ID” as complete if rows can be inserted behind that cursor; add a final coverage scan and concurrent-write strategy.

The runner receives short-lived, environment-scoped credentials with only approved privileges. Server-side authorization binds approval to artifact digest, target database, schema preconditions, and execution window. Tenant-scoped data migrations require explicit predicates and isolation tests; schema changes in a shared database may affect every tenant even when the request originated from one.

Repository comments, database descriptions, and sampled text values are untrusted input. They cannot instruct the agent to disable security, drop tables, or fetch secrets. MCP exposure does not authorize destructive SQL. Contraction and any broad deletion require a separate human approval, bounded blast radius, and documented recovery limits.

Acceptance Tests Before Production Approval

1. Run old and new application versions against the expanded schema. Both complete the critical read and write flows without relying on the removed representation.
2. Hold a long transaction that blocks the change. The runner exits within its approved lock budget, records failure, and does not increase the timeout automatically.
3. Crash halfway through a backfill batch. Resumption produces the same mapped values and no duplicate side effects, with complete row coverage afterward.
4. Change the schema after artifact approval. The precondition digest fails, and no SQL executes until the revised plan is reviewed.
5. Restore from the chosen backup chain in isolation. Application checks and data invariants pass at the selected recovery point, with measured restoration duration.

Include malformed source values and cross-tenant rows in fixtures. A migration that works only for clean rows is not ready for historical production data.

Measure Compatibility and Operational Cost

Baseline change lead time, migration-related failures, database latency, and replication lag for comparable changes. Measure validated-row coverage as rows satisfying the new invariant divided by all in-scope rows. Report exception counts separately, with owners; do not exclude difficult rows to make coverage look complete.

Guardrails include application error rate, lock wait, replica lag, storage growth, and unauthorized operations. Begin with read-only planning, then rehearsals, then one approved additive change. Proposed thresholds must be calibrated against workload measurements. Record operator time as well as model cost.

The data quality guide develops invariant checks, while the legacy modernization guide explains staged consumer replacement. Data engineering services support the data boundary. Start with an additive rehearsal and recovery drill, using the Strangler Fig pattern as context for gradual transition rather than a justification for an immediate rewrite.

Sources

• PostgreSQL: ALTER TABLE
• PostgreSQL: Continuous archiving and PITR
• Microsoft: Strangler Fig pattern