Write a migration script you can sleep on.
Idempotent, reversible, and specific about what it assumes. The script your future-oncall version won't hate you for.
You need to move data from schema A to schema B without breaking production. The default AI-generated migration is a happy-path SQL block that will crash on the first edge case. This prompt produces something you can actually deploy.
THE PROMPT
Write a data-migration script that moves data from [SOURCE] to [TARGET]. I'll paste the source schema, target schema, and constraints below. Requirements: - **Idempotent** — re-running the script produces the same result. Use ON CONFLICT clauses or explicit existence checks; do not assume the target is empty. - **Reversible** — return a companion rollback script that undoes what the migration did, without dropping anything the rollback didn't create. - **Batched** — process in chunks of 10,000 rows with progress logging, not one giant transaction. - **Loud on errors** — log the specific row that failed with enough context to debug, then decide (per constraint) whether to skip or halt. - **Guarded** — start with a dry-run mode that reports counts and diffs without writing. Also return: a short pre-flight checklist (what to verify in prod before running), and the specific query I'd run afterward to confirm success. No cleverness. Boring, readable, defensive. Schema + constraints: [PASTE HERE]
-- migration.sql
BEGIN;
-- pre-flight lock, no destructive ops in this block
SELECT 1 FROM users LIMIT 1;
COMMIT;
DO $$
DECLARE
batch_size INT := 10000;
processed INT := 0;
total INT;
BEGIN
SELECT COUNT(*) INTO total FROM users WHERE new_email IS NULL AND email IS NOT NULL;
RAISE NOTICE 'Total to migrate: %', total;
LOOP
UPDATE users
SET new_email = LOWER(TRIM(email))
WHERE id IN (
SELECT id FROM users
WHERE new_email IS NULL AND email IS NOT NULL
LIMIT batch_size
);
GET DIAGNOSTICS processed = ROW_COUNT;
EXIT WHEN processed = 0;
RAISE NOTICE 'Processed %/%', processed, total;
COMMIT;
END LOOP;
END $$;
-- rollback.sql
UPDATE users SET new_email = NULL WHERE new_email IS NOT NULL;
-- Pre-flight
-- 1. Verify replication lag < 30s: SELECT extract(epoch from now() - pg_last_xact_replay_timestamp());
-- 2. Confirm no active migrations: SELECT count(*) FROM pg_stat_activity WHERE query ILIKE '%migration%';
-- Post-check
-- SELECT COUNT(*) FROM users WHERE new_email IS NULL AND email IS NOT NULL;
-- Should return 0.