# Safe database migration workflows for AI coding agents

Production database changes deserve a different workflow from ordinary code edits. An AI coding agent can inspect a schema, trace application queries, and draft migration files faster than most teams can schedule a review. It must not turn that speed into permission to alter production because a prompt happened to include the word "deploy".

Safe database migration workflows split planning from authority. The agent can prepare evidence and a precise change set. A human can review the operational consequences, prove recovery works, and approve one bounded execution. That separation prevents the familiar failure where a harmless-looking `ALTER TABLE` becomes a blocked checkout flow, a runaway rewrite, or a half-finished deployment that nobody can explain.

This article uses PostgreSQL commands because its locking and transaction behavior are explicit in its documentation. The workflow applies to other relational databases, but do not copy PostgreSQL syntax or assumptions into another engine without checking that engine's manual.

## Production changes need separate authority from code generation

A migration file and a production migration are different acts. The first describes intent. The second spends scarce authority against a live system with active users, replication, backups, and application versions that may not agree with one another.

Teams often make the mistake of granting an agent one broad database connection because it makes local development easy. That connection then crosses environments, and the agent cannot tell a staging hostname from a production cluster if both accept the same credentials. Worse, an agent that can execute arbitrary SQL has no natural reason to pause before a table rewrite. It sees a task and a tool.

Give the work distinct phases with different inputs and different permissions:

1. Discovery reads schema metadata, migration history, query code, and operational constraints.
2. Planning produces SQL, expected lock behavior, data effects, preflight checks, verification queries, and a recovery decision.
3. Review confirms that the plan matches the actual production state and that the organization accepts its risk.
4. Execution runs one approved artifact against one named target.
5. Verification proves the application and database reached the intended state before anyone calls the deployment complete.

The useful distinction is between *reversibility* and *recoverability*. Adding a nullable column is commonly reversible: a later statement can remove it if no code depends on it. Updating millions of rows based on a flawed expression may not be reversible, even if someone writes an apparent reverse update. You may not know which original values were overwritten. Recoverability means you have a tested way to restore or contain the damage. Treat those as separate fields in every migration plan.

An agent should never infer production authority from a repository branch, a ticket label, or an environment variable supplied in a chat prompt. Those signals describe intent, and intent is frequently wrong. Execution should require a target selected outside the agent's text context, a prepared artifact whose digest is recorded, and a person who sees exactly what will run.

## Make the migration plan a reviewable artifact

A reviewable plan gives an operator enough information to reject a migration before it reaches the database. "Add an index" is not a plan. The table size, query purpose, index form, transaction constraint, lock exposure, and verification query make it a plan.

Ask the agent to produce a directory with fixed files rather than a paragraph in a pull request. For example:

```text
migrations/2025-04-add-orders-status-index/
  up.sql
  verify.sql
  preflight.sql
  recovery.md
  manifest.json
```

The manifest should bind the files to the intended target and state what the agent learned. This example does not expose credentials and does not claim a backup exists merely because someone requested one.

```json
{
  "migration_id": "2025-04-add-orders-status-index",
  "engine": "postgresql",
  "target": "production-orders",
  "change": "create index for filtered order status query",
  "transaction_mode": "outside_transaction",
  "requires_backup_restore_check": true,
  "expected_write_blocking": "none during index build",
  "stop_conditions": [
    "target schema differs from preflight result",
    "backup restore check fails",
    "index is invalid after execution"
  ]
}
```

A human reviewer should be able to answer five questions from this artifact:

- Which database and schema will receive the change?
- What exact SQL will execute, and does the runner wrap it in a transaction?
- What operation will block, rewrite, scan, or consume extra storage?
- What evidence proves the database can recover if the change goes wrong?
- Which queries or application signals prove success?

Require the agent to include its uncertainty. If it cannot establish the PostgreSQL version, table size, current migration version, or the calling application's compatibility range, it should state that as a stop condition. Inventing confidence from partial repository access is worse than leaving an item open.

Keep generated SQL immutable after review. If a reviewer edits `up.sql`, regenerate the checksum and send the artifact through review again. A common failure starts with a reviewed migration, followed by a hurried "small fix" in the deployment shell. The live command is then no longer the reviewed command, and the audit record tells a comforting but false story.

## Classify the operation before deciding how to run it

The SQL verb does not tell you enough about operational risk. `ALTER TABLE` covers changes that complete quickly and changes that take locks or rewrite enough data to fill storage. A safe workflow classifies the actual operation, database version, table size, and concurrent workload before assigning an execution path.

PostgreSQL's `ALTER TABLE` documentation spells out the uncomfortable part: many forms acquire an `ACCESS EXCLUSIVE` lock unless the manual says otherwise. That lock conflicts with reads and writes. "It worked quickly in staging" says little if staging has no long-running transactions, no report traffic, and a fraction of the data.

Use four practical classes:

| Class | Typical example | Execution expectation |
|---|---|---|
| Metadata-only | Add a nullable column with no default | Short operation, still verify lock behavior for your version |
| Concurrent build | Create a new index | Special command form and separate transaction handling |
| Batched data change | Backfill a new column | Small commits, measured rate, resumable progress |
| Rewrite or destructive change | Change a large column type or delete data | Maintenance decision with explicit recovery plan |

Do not call every online operation safe. PostgreSQL's `CREATE INDEX CONCURRENTLY` avoids blocking writes while it builds, as documented in `CREATE INDEX`. It also takes longer, cannot run inside a transaction block, and can leave an invalid index after failure. The popular advice to "always use concurrently" misses those conditions. Use it when write availability matters and your migration runner can handle its rules.

A planning query can give the reviewer a useful first estimate of table and index size:

```sql
SELECT
  pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size,
  pg_size_pretty(pg_relation_size('public.orders')) AS table_size,
  pg_size_pretty(pg_indexes_size('public.orders')) AS indexes_size;
```

Expected output has one row with three readable size values. Treat it as a sizing signal, not a promise about duration. Row width, cache state, concurrent writes, replication, disk throughput, and active transactions all change the result.

The agent should also inspect blockers before an operation with meaningful lock exposure. PostgreSQL exposes active sessions in `pg_stat_activity`; it does not give you permission to terminate them. The plan should name the owner of any long-running workload and state whether the migration waits, reschedules, or gets a maintenance window.

## A restored backup is the entry ticket

A successful backup command proves that a program wrote a file. It does not prove that the file restores, that it contains the objects you need, or that the restoration procedure works under pressure. Run a restore check before destructive or hard-to-reverse work.

For a PostgreSQL database, a custom-format logical dump can look like this:

```sh
pg_dump -Fc -d "$SOURCE_DATABASE" -f "orders-preflight.dump"
pg_restore -l "orders-preflight.dump" | sed -n '1,12p'
createdb migration_restore_check
pg_restore -d migration_restore_check "orders-preflight.dump"
psql -d migration_restore_check -c "SELECT count(*) FROM public.orders;"
```

The listing command should print archive entries such as a table definition, table data entry, and index definitions. The final query should print a one-column result with the restored row count. Record the command outcome, dump identifier, restore target, and check result in the migration record. Do not put database URLs or passwords in that record.

A logical restore check does not replace a physical recovery test, point-in-time recovery, replica promotion drill, or a provider's backup assurance. It answers a narrower question: can this dump restore into a database and yield the objects and data shape we expect? That narrow answer still catches damaged archives, missing extensions, role issues, and procedures that only worked on the laptop of the person who wrote them.

Choose the recovery method before execution, not after an error. The plan should say which of these applies:

- A reverse migration is safe because no data has been discarded and the application can tolerate reversal.
- A restore into a replacement database is the recovery route, with the owner who decides cutover.
- A replica or provider recovery procedure is the route, with a documented recovery point objective.
- The change cannot be recovered cleanly, so it needs a maintenance window and explicit acceptance.

That last category is legitimate. Pretending it has a rollback because the deployment template demands a field is not. I have seen teams write `DROP COLUMN` as the rollback for a backfill that changed customer data, then discover that deleting the new column merely erased their evidence while the old values remained wrong.

## Expand first and remove later

Most application-facing schema changes should preserve compatibility across mixed application versions. During a deployment, old and new processes can run together because workers drain slowly, users hold requests open, or a rollback restarts an older build. A migration that assumes one instantaneous application version turns normal deployment behavior into an outage.

Suppose you need to replace `orders.status_text` with a constrained `orders.status_code`. The unsafe version adds the new column, rewrites every row, swaps application code, and removes the old column in one release. It has several failure points and no quiet place to stop.

Use an expand and contract sequence instead:

1. Add `status_code` as nullable and add any supporting structures that do not break the current application.
2. Deploy application code that reads the new field when present and writes both fields, or derives one safely from the other.
3. Backfill existing rows in bounded batches, recording progress outside the agent's transient chat context.
4. Validate that all rows meet the new invariant and that readers use the new field.
5. In a later release, stop writing the old field, wait through the agreed retention period, then remove it.

The batch loop matters. One giant `UPDATE` holds resources for too long, generates a burst of write-ahead log, stresses replicas, and makes recovery harder to reason about. A bounded query gives the operator a point between commits to examine replication lag, error rate, and database load.

This PostgreSQL pattern updates a selected batch by primary key and returns the rows changed:

```sql
WITH batch AS (
  SELECT id
  FROM public.orders
  WHERE status_code IS NULL
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 500
)
UPDATE public.orders AS o
SET status_code = CASE o.status_text
  WHEN 'new' THEN 10
  WHEN 'paid' THEN 20
  WHEN 'shipped' THEN 30
  ELSE NULL
END
FROM batch
WHERE o.id = batch.id
RETURNING o.id;
```

The runner repeats this only while returned rows exist and while the mapping produces acceptable values. `SKIP LOCKED` can suit a controlled worker because it avoids waiting on rows another transaction holds. It is not a proof that every row was processed. The verification query must check for remaining nulls and unexpected source values:

```sql
SELECT status_text, count(*)
FROM public.orders
WHERE status_code IS NULL
GROUP BY status_text
ORDER BY count(*) DESC;
```

If that query returns an unfamiliar value, stop. Do not let an agent decide that an unknown production state can become a default enum value because it wants to finish the task.

## Execution needs hard stop conditions and bounded commands

An approved migration can still meet a different production state from the one the planner inspected. The runner must perform preflight checks immediately before execution and stop when they disagree. This is where a workflow becomes safer than a runbook that somebody follows from memory.

Use a dedicated executor that accepts only an artifact identifier and target selector. It should refuse inline SQL pasted from a terminal, reject an artifact whose checksum changed, and print the target identity before it begins. The executor can run read-only preflight SQL first, then require a separate approval for the mutating portion.

Set session time limits explicitly. PostgreSQL documents `lock_timeout` and `statement_timeout` as separate controls. The former aborts while waiting for a lock; the latter aborts a statement that runs too long. A reasonable preamble for a short metadata operation might be:

```sql
SET lock_timeout = '5s';
SET statement_timeout = '60s';
SELECT current_database(), current_user, now();
```

Do not carry those numbers blindly into index builds or backfills. A timeout is a budget that the plan justifies. If the command needs thirty minutes under expected load, a sixty-second statement timeout only creates a predictable failure. For concurrent index creation, use a runner path that does not wrap the command in a transaction:

```sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_status_code_idx
ON public.orders (status_code);
```

After this command, verify the index instead of assuming success from an exited process. PostgreSQL records validity in catalog metadata, and a failed concurrent build may leave an invalid index that you need to inspect and remove before retrying. Put that check in `verify.sql`, along with an application-facing query plan where appropriate.

A real abort decision needs named signals. Stop execution if preflight finds an unexpected migration version, insufficient storage headroom, an active blocker outside the allowed window, a failed backup restore, a failed checksum, or a result that differs from the verification predicate. The operator should not need to negotiate with an agent while a lock wait grows.

## Approval should bind a person to one live run

Approval fatigue appears when systems ask people to approve each SQL call. People then approve on reflex or turn prompts off. A single approval at the start of a defined agent process is useful when it identifies the process, expires when that process exits, and does not quietly cover a different session later.

The approval must show enough context for a person to reject the run: the signed process identity, selected target, artifact identifier, intended action class, and whether the action channel can write. It should not display a raw password or require the agent to handle one. The person authorizes the action, not secret disclosure.

Sallyport fits this boundary by keeping API and SSH credentials in its encrypted vault, letting an MCP-capable agent request actions through `sp mcp`, and returning action results rather than plaintext secrets. Its separate session approval and optional per-key approval can make a production database action require a human decision without placing a reusable credential in the agent context.

Keep approval scoped to the execution phase. The discovery agent can use a read-only path. The execution agent can receive a session that expires with its process and can access only the approved action route. Instant revocation matters because an operator needs a way to stop a run after a surprising preflight result, not merely after the deployment script finishes.

Do not substitute a policy language for clear operational design if your team cannot explain the policy under pressure. The reliable boundary is simple: locked access denies all actions, a new execution process requires authorization, and especially sensitive credentials can require a fresh decision on each use. That is easier to audit than a pile of inferred rules that nobody remembers writing.

## Audit records must reconstruct the change without secrets

After a failed migration, people ask simple questions: who ran it, against which target, what exact artifact executed, when did it stop, and did it touch data? A generic terminal transcript rarely answers all of them. It may omit the target, lose the command after a shell history cleanup, or include secrets that should never have been logged.

Record structured events for planning, approval, preflight, execution, verification, revocation, and failure. Each event should include the migration identifier and artifact digest so an investigator can connect the review with the live run. Include the database identity returned by preflight rather than only the name requested by the caller.

A minimal event shape looks like this:

```json
{
  "event": "migration.verify",
  "migration_id": "2025-04-add-orders-status-index",
  "artifact_sha256": "recorded-digest",
  "target_identity": "production-orders",
  "result": "passed",
  "observed": "index valid; query returned expected columns"
}
```

Avoid logging SQL parameters when they can contain personal data, tokens, or customer content. Log the artifact digest and a safe statement identifier instead. The execution system can retain protected diagnostics under a defined retention policy, but an audit journal should remain useful without becoming another secret store.

Tamper evidence changes the quality of an investigation. A record that someone can edit after a bad deployment only proves that someone had access to a record. If you use a hash-chained audit log, verify it independently as part of incident review. Sallyport's `sp audit verify` checks its encrypted audit chain offline without needing vault access, which is the right property for a reviewer who should not receive production credentials.

## Verification must test the workload, not only the DDL

A migration succeeds when the intended workload behaves correctly, not when the database accepts a statement. A new column can exist while application writes omit it. An index can be valid while the target query cannot use it because its predicate or data type differs. A constraint can validate while an older worker still sends data that violates the new application contract.

Write verification queries before execution, while reviewers can challenge their assumptions. For an index migration, check catalog state and inspect the query that motivated it. For a backfill, count remaining rows, group unexpected source values, and confirm that the new application write path produces the expected representation. For a constraint, test valid and invalid writes in a disposable database before asking production to enforce it.

PostgreSQL's `EXPLAIN` is useful but easy to misuse. A planner decision depends on statistics, parameter values, data distribution, and configuration. `EXPLAIN (ANALYZE, BUFFERS)` runs the query and measures it, so do not point it casually at a costly production query. Use it on a representative safe query, and define an acceptable result before you see the output. Otherwise every plan becomes something a tired operator can rationalize.

Watch live application indicators during and after the change: request error rate, query latency for the affected path, connection pressure, replication health, and worker failures. The agent can collect and present those readings, but the release owner decides whether they meet the stated exit condition.

Do not schedule the destructive contraction merely because the expansion deployment passed. Wait until observability and release history show that old application processes no longer depend on the old schema. Then run the removal as its own reviewed migration. That extra change is cheaper than discovering during a rollback that the old build expects a column you removed an hour earlier.

## The first production run should be deliberately boring

The first agent-assisted production migration should add a low-risk, observable change, not redesign a table under load. Pick a nullable additive column, a comment, or another operation whose behavior you already understand. Use it to test the boundaries: artifact generation, target selection, approval, backup evidence, audit events, preflight failure, execution, and verification.

Make the exercise prove that the system can refuse work. Point the executor at a target with the wrong schema fingerprint and confirm it stops. Change the reviewed file and confirm the digest check rejects it. Revoke authorization during a harmless staged run and confirm later actions fail. Those tests reveal whether the controls work when someone needs them, rather than merely looking sensible in a design document.

After that, expand the allowed migration classes one at a time. A team that can safely add a column has not proved it can backfill a large table, create a concurrent index, or recover from a bad data transformation. Each class needs its own observed run and its own failure criteria.

Treat production authority as something the agent borrows for one narrow job, then loses. That operational habit will prevent more damage than any clever prompt instruction ever will.