Data Migration Pipeline AI Prompt

Design a safe, reversible data migration pipeline for this schema change or data movement.

Migration: {{migration_description}} (e.g. split a table, merge schemas, move to new database)
Data volume: {{volume}}
Max downtime: {{max_downtime}}
Database: {{database}}

1. Migration principles:
   - Never modify data in place without a backup
   - Test in staging with a production-size data clone first
   - Build in a rollback path for every step
   - Migrate data in small batches, not one massive transaction

2. Batch migration pattern:
   DO $$
   DECLARE
     batch_size INT := 10000;
     last_id BIGINT := 0;
     max_id BIGINT;
   BEGIN
     SELECT MAX(id) INTO max_id FROM source_table;
     WHILE last_id < max_id LOOP
       INSERT INTO target_table
         SELECT ... FROM source_table
         WHERE id > last_id AND id <= last_id + batch_size;
       last_id := last_id + batch_size;
       PERFORM pg_sleep(0.1);  -- throttle to avoid I/O saturation
     END LOOP;
   END $$;

3. Online migration with dual-write:
   Phase 1: Add new table/column; application writes to both old and new
   Phase 2: Backfill old data from old to new in batches
   Phase 3: Verify consistency (compare row counts and key values)
   Phase 4: Switch reads to new structure; stop writing to old
   Phase 5: Remove old structure after validation period

4. Validation queries:
   -- Row count match
   SELECT (
     (SELECT COUNT(*) FROM source_table) =
     (SELECT COUNT(*) FROM target_table)
   ) AS counts_match;

   -- Checksum of key columns
   SELECT MD5(STRING_AGG(id::text || amount::text, ',' ORDER BY id))
   FROM source_table;

5. Emergency rollback:
   - Keep the old table or column intact until migration is fully validated
   - Use a feature flag to switch between old and new data paths
   - Drop old structures only after: 24h monitoring, zero errors, stakeholder sign-off

Return: batch migration script, dual-write pattern, validation queries, and rollback procedure.