Safe AI-Written Migrations, From Editor to Production

Safe AI-Written Migrations, From Editor to Production

The dangerous part of an AI-written migration is rarely the syntax. It is the perfectly reasonable statement that locks a busy table, breaks an older application instance, or updates fifty million rows in one transaction. My 2026 rule is simple: let the assistant draft the change, but make the deployment contract explicit before accepting SQL.

This roundup uses PostgreSQL examples. Other databases have different locking, transactional DDL, and online schema-change behavior. Ask your pair programmer to name those differences, not quietly translate syntax and assume equivalent safety.

Give the Assistant a Database Contract

Start in the terminal with facts, not a vague request to “add a required field.” Provide the database version, migration framework, relevant schema, approximate table sizes, write rates, deployment order, and acceptable blocking time. Include existing indexes and constraints. Use sanitized schema extracts rather than production rows or credentials.

My opening prompt asks for four outputs: assumptions, a staged migration plan, candidate SQL, and failure scenarios. I also say: “Do not execute commands. Flag operations that scan, rewrite, or lock tables, and explain how you verified their behavior for this database version.”

• Require explicit approval before any command targets a shared database.
• Keep production credentials outside the assistant's accessible environment and tools.
• Ask the assistant to identify missing facts before proposing irreversible changes.

Use Expand-and-Contract for Live Schema Changes

Suppose accounts needs a required external_reference column. Do not combine adding it, populating it, enforcing uniqueness, and deleting the old identifier in one migration. Add a nullable column first, deploy compatible application code, backfill historical rows, verify invariants, and enforce constraints afterward. Remove the old path only after older application versions are gone.

For rolling deployments, write down which application versions can read and write each schema state. Dual writes need clear ownership and consistency checks; they are not automatically safe. Ask the assistant to describe what happens when an old worker processes a queued job halfway through deployment.

Review PostgreSQL Locks Before Running DDL

Read the generated SQL yourself. On PostgreSQL, CREATE INDEX CONCURRENTLY avoids blocking ordinary writes, but still consumes resources, can wait on transactions, and cannot run inside a transaction block. Check whether your migration framework wraps statements automatically. A failed concurrent build may leave an invalid index requiring deliberate cleanup.

Set a session-scoped lock_timeout and an appropriate statement_timeout for each operation, rather than changing global defaults. A short lock timeout lets DDL fail instead of waiting indefinitely; retry during a quieter window after investigating blockers. For required columns, consider a staged CHECK constraint with NOT VALID, followed by VALIDATE CONSTRAINT, before SET NOT NULL. Verify version-specific scan avoidance and locking instead of assuming this sequence is free.

Backfill in Batches With a Resume Cursor

Make large data migrations separate, observable jobs. Use bounded transactions and a stable, indexed cursor, such as an immutable primary key. Avoid OFFSET pagination over a changing table. Persist progress, make repeated execution safe, and define how concurrent inserts and updates receive the new value.

• Throttle batches when replication lag, database load, or request latency increases.
• Record processed rows, remaining work, failures, and the last committed cursor.
• Verify missing values and consistency before enabling stricter database constraints.

Rehearse Failure, Not Just the Happy Path

Run the migration against a disposable database with representative volume and skew. Tiny fixtures catch syntax errors, not operational behavior. Test cancellation, timeout, reruns, partial backfills, and old application code against the expanded schema. Review query plans and monitor lock waits during rehearsal.

Separate application rollback from data recovery. Dropping a newly populated column destroys information; a down migration cannot magically restore it. Prefer forward fixes when reversal would lose writes. Establish a tested backup and restore path, recovery objectives, and an owner for the deployment stop decision.

Keep AI Review Inside the Editor Workflow

Fluxo fits this workflow with one key and one base URL, https://api.fluxorelay.com, for Claude, GPT, Gemini, DeepSeek, Kimi, GLM, Qwen, Grok, and more. It works with Claude Code, Codex, Gemini CLI, and any OpenAI SDK. You might draft with Claude Sonnet 5.5 and ask GPT-6 Astra to challenge the locking assumptions; agreement is not verification.

Fluxo bills per real token, including cache reads and writes, with no monthly fee. Users also earn a referral share when people they invite pay. If that setup suits your terminal workflow, consider creating a Fluxo account.

Keep the final gate human: reviewed SQL, measured rehearsal, explicit approval, and a recovery plan you can actually execute.