A Coding Assistant Workflow for Safe Schema Changes

A coding assistant can draft a migration quickly. The engineering work is proving that old and new application versions can coexist, production traffic stays healthy, and recovery remains possible. This walkthrough uses a PostgreSQL example: replacing customers.full_name with customers.display_name without requiring an atomic application-and-database deployment.

1. Give the assistant evidence, not production access

Start with the current schema, relevant application code, migration conventions, PostgreSQL version, and deployment sequence. Include approximate table size, write patterns, replication setup, and known latency constraints. Remove credentials and customer data. Give the assistant read-only repository access first, and require human approval before any database execution.

If your team uses CodeRelay Hub, configure a supported coding client against https://api.coderelayhub.com using its documented authentication settings. Keep database permissions separate from model access: routing an assistant through one API endpoint does not make its proposed SQL safe to execute.

2. Ask for a compatibility plan before SQL

Use a focused instruction: Plan an expand-and-contract migration from full_name to display_name. List every reader and writer, mixed-version behavior, lock risks, verification queries, and recovery steps. Do not generate destructive SQL yet.

Have the assistant inspect background jobs, imports, reporting queries, database functions, and bulk-update paths, not just HTTP handlers. Require it to distinguish facts found in the repository from assumptions. An unknown external writer should become a deployment prerequisite, not disappear inside a confident explanation.

3. Expand the schema without changing behavior

Ask for a small migration that adds nullable customers.display_name while leaving full_name intact. For this example, avoid introducing a default, index, or constraint unless the application actually needs one. Every additional operation has its own locking and compatibility implications.

Review the exact SQL and transaction boundaries. Even a lightweight schema change needs a lock and can queue behind a long-running transaction. Use a session-local lock timeout appropriate to your environment, and fail cleanly rather than waiting indefinitely. Confirm whether the migration framework already wraps migrations in transactions before adding transaction statements.

4. Deploy a bridge version

Have the assistant implement a compatibility release that still reads full_name but writes the same value to both columns. Both assignments must happen atomically in the same database transaction. Cover inserts, updates, imports, and administrative tools.

Do not start the backfill merely because the first new instance is healthy. Wait until all old application instances and workers have drained. An old writer can still change full_name without updating display_name. If external writers cannot be upgraded, design and review a database-level synchronization mechanism or postpone the migration.

5. Backfill in bounded, restartable batches

Ask the assistant for a separate backfill job, not one enormous update embedded in the deployment migration. Traverse stable primary-key ranges, commit each batch, and persist progress. Populate display_name from full_name only where display_name is still null.

Review concurrency behavior against the actual SQL and isolation level. Test that a concurrent bridge-version update cannot leave mismatched values. Measure batch duration, database load, replication lag, and application latency in staging, then start conservatively in production. Define pause thresholds before execution; do not let the assistant invent them.

6. Test failures and verify the invariant

Require tests for migration retries, interrupted backfills, concurrent updates, nullable names, and mixed application versions. Compare the two columns using PostgreSQL's null-safe comparison: display_name IS DISTINCT FROM full_name. A null count alone cannot detect stale, non-null values.

Run reconciliation after the backfill and again under normal traffic. Investigate mismatches before switching reads. Keep generated tests under review: tests that repeat the implementation's assumptions are not independent evidence of correctness.

7. Switch reads, then contract separately

Deploy reads from display_name while continuing dual writes through the rollback window. If problems appear, revert reads to full_name without undoing the additive schema change.

Only after the rollback window closes should a separate release stop referencing full_name. Confirm that older binaries, jobs, and external consumers are retired before scheduling its removal. Treat dropping the column as irreversible without a recovery plan; a generated down migration cannot reconstruct lost data.

Open a free workspace to try this workflow.