Give your coding agent a narrow assignment: prepare a reviewable migration, not administer production. This walkthrough uses PostgreSQL and an example change: replacing a customer's display_name column with public_name. Keep the agent's work separate from the credentials and approval needed to deploy it.
1. Define the Migration Contract Before Generating SQL
Write the acceptance criteria before asking for a script. Specify the database version, migration framework, affected tables, data mapping, and application deployment order. For this example, require every existing display_name value to survive unchanged in public_name. State whether null values are valid and which application versions must coexist.
Use a prompt such as: Inspect the existing migrations. Propose an additive change, a resumable backfill, verification queries, and a recovery plan. Do not execute commands or remove display_name. Flag unknown assumptions instead of guessing.
2. Restrict the Agent's Files, Credentials, and Tools
Give the agent schema definitions, relevant application code, migration conventions, and synthetic fixtures. Exclude production credentials, customer exports, and unrelated secrets. Configure its shell and database access to reach only a disposable development environment; do not rely on a prompt alone as the boundary.
If your coding workflow needs a model relay, consider SwiftRelay with Claude Code, Codex, or Gemini CLI. Keep the relay configuration separate from database credentials and review what context you send. Sign up for SwiftRelay if you want pay-as-you-go, per-token access for this workflow.
3. Split the Column Replacement Into Compatible Releases
Ask for an expand-and-contract plan rather than an immediate rename. Add public_name, deploy application code that keeps both columns consistent, backfill existing rows, verify equivalence, and switch reads. Schedule removal of display_name only after older application versions and background jobs no longer depend on it. This staged approach separates adding the replacement from removing the original. ([prisma.io](https://www.prisma.io/docs/guides/database/data-migration?utm_source=openai))
Require an explicit concurrency rule: which value wins if a customer updates their name during the backfill? Have the agent explain how its update condition and application write path preserve that rule. Reject a plan that leaves this decision implicit.
4. Review Locks and Transaction Boundaries Statement by Statement
Require an explanation beside every proposed schema operation: expected locks, possible scans, transaction requirements, and failure behavior. In PostgreSQL, a standard index build blocks writes, while CREATE INDEX CONCURRENTLY avoids locks that prevent concurrent inserts, updates, and deletes. It still requires operational review. ([postgresql.org](https://www.postgresql.org/docs/current/sql-createindex.html?utm_source=openai))
Do not approve a blanket instruction to wrap everything in one transaction: PostgreSQL does not allow CREATE INDEX CONCURRENTLY inside a transaction block. Check how your migration runner handles that exception. ([postgresql.org](https://www.postgresql.org/docs/current/sql-createindex.html?utm_source=openai))
Set migration-session timeout policies deliberately. PostgreSQL's lock_timeout limits lock waits, while statement_timeout limits statement execution; a lock timeout at least as large as an active statement timeout offers no useful earlier cutoff. Choose values with the database owner. ([postgresql.org](https://www.postgresql.org/docs/18/runtime-config-client.html?utm_source=openai))
5. Make the Backfill Resumable and Its Results Auditable
Request bounded batches using a stable key, a recorded checkpoint, and a precise eligibility condition. Have the agent demonstrate what happens after interruption, repeated execution, and concurrent application writes. Use the migration framework's history tracking for schema changes; do not assume every migration should be blindly rerunnable.
If the change adds a supported foreign-key or check constraint, consider PostgreSQL's NOT VALID followed by VALIDATE CONSTRAINT. This separates the initial constraint addition from checking existing rows; validation does not lock out concurrent updates. Confirm applicability to the actual constraint. ([postgresql.org](https://www.postgresql.org/docs/17/sql-altertable.html?utm_source=openai))
6. Rehearse Failure and Require Human Deployment Approval
Run the reviewed artifacts against a production-like, sanitized environment. Test empty tables, null values, interruption, concurrent writes, and application compatibility before and after the backfill. Compare expected row counts and name values, not merely successful process exits.
- Review the exact SQL diff and deployment sequence.
- Confirm a tested restore procedure and recovery owner.
- Define stop conditions for blocking, errors, and data mismatches.
- Execute only the approved artifact through your deployment process.
Keep recovery distinct from reversal: recreating a dropped column is not a data-restoration plan. Leave destructive cleanup for a separately approved release. Let the agent prepare evidence; let an accountable human authorize production changes.