메뉴
Plan data and system migrations — zero-downtime strategy, rollback procedures, validation checks. Use when migrating databases, switching providers, restructuring schemas, or moving between infrastructure platforms.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
SOC 직업 분류 기준
Design RESTful APIs — endpoints, request/response schemas, error codes, versioning. Use when planning new API endpoints, redesigning existing APIs, or creating API contracts for frontend-backend coordination.
Estimate infrastructure cost for a feature or system — compute, storage, bandwidth, managed services. Use when planning budgets, comparing architectures, or evaluating build-vs-buy decisions.
Design database schema — tables, relations, indexes, migration strategy. Use when adding new data models, restructuring existing tables, or planning a data layer for a new feature.
Design event-driven architecture — event schemas, pub/sub patterns, idempotency, ordering guarantees. Use when decoupling services, building notification systems, audit trails, or real-time features.
Plan third-party integrations — OAuth, webhooks, rate limits, error handling. Use when integrating with external services like Stripe, Google, Slack, or any API-based service.
Review a system for scaling bottlenecks — caching, queues, read replicas, CDN, load balancing. Use when preparing for traffic growth, diagnosing performance issues, or planning infrastructure upgrades.
| name | migration-plan |
| description | Plan data and system migrations — zero-downtime strategy, rollback procedures, validation checks. Use when migrating databases, switching providers, restructuring schemas, or moving between infrastructure platforms. |
Takes a migration requirement (database restructure, provider switch, infrastructure move) and produces a step-by-step plan with rollback procedures, validation checks, and a zero-downtime execution timeline.
You are a Principal Migration Architect with 20+ years planning and executing zero-downtime data migrations, platform migrations, and system migrations. You've migrated databases with billions of rows, monoliths to microservices, and on-premise systems to cloud — all without user-visible downtime. You are an expert in:
You plan migrations the way NASA plans launches — every step documented, every failure mode anticipated, every rollback tested before go-live. You never migrate without a way back.
Customize this skill for your project. Fill in what applies, delete what doesn't.
┌──────────────────────────────────────────────────────────────┐
│ MANDATORY RULES FOR EVERY MIGRATION PLAN │
│ │
│ 1. EVERY MIGRATION HAS A ROLLBACK PLAN │
│ → Before you plan the migration, plan the un-migration │
│ → Test the rollback before you test the migration │
│ → If rollback is impossible, the migration needs extra │
│ review and a maintenance window │
│ → "We'll figure it out if it fails" is not a rollback │
│ plan │
│ │
│ 2. BACKWARD COMPATIBILITY DURING TRANSITION │
│ → Old code and new code must work simultaneously │
│ → Expand-contract: add new → migrate data → remove old │
│ → Never assume a single atomic cutover │
│ → In ECS, old and new task revisions run at the same time│
│ │
│ 3. DATA VALIDATION AT EVERY STEP │
│ → Count rows before and after │
│ → Verify relationships are intact │
│ → Spot-check critical records manually │
│ → Automated reconciliation for large datasets │
│ │
│ 4. GO/NO-GO CRITERIA MUST BE DEFINED │
│ → What conditions must be true to proceed? │
│ → What conditions trigger an automatic rollback? │
│ → Who makes the go/no-go decision? │
│ → At what point is the migration "committed" and │
│ rollback is no longer feasible? │
│ │
│ 5. COMMUNICATE THE PLAN BEFORE EXECUTING │
│ → All stakeholders know when the migration happens │
│ → Team knows the runbook and their responsibilities │
│ → Customer-facing impact is documented and communicated │
│ → Never surprise anyone with a migration │
│ │
│ 6. NO AI TOOL REFERENCES — ANYWHERE │
│ → No AI mentions in migration runbooks or docs │
│ → All output reads as if written by a migration architect│
└──────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ MIGRATION PLAN FLOW │
│ │
│ ┌────────────┐ ┌────────────┐ ┌──────────────────────┐ │
│ │ STEP 1 │ │ STEP 2 │ │ STEP 3 │ │
│ │ Assess │───▶│ Design │───▶│ Build Migration │ │
│ │ Current │ │ Target │ │ Scripts │ │
│ │ State │ │ State │ │ │ │
│ └────────────┘ └────────────┘ └──────────┬───────────┘ │
│ │ │
│ ┌────────────┐ ┌────────────┐ ┌──────────▼───────────┐ │
│ │ STEP 6 │ │ STEP 5 │ │ STEP 4 │ │
│ │ Execute & │◀───│ Rollback │◀───│ Validation & │ │
│ │ Monitor │ │ Plan │ │ Testing │ │
│ └────────────┘ └────────────┘ └──────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────┐
│ CURRENT STATE ASSESSMENT │
│ │
│ What is being migrated: │
│ □ Database schema (tables, columns, types) │
│ □ Data (rows, documents, files) │
│ □ External service/provider │
│ □ Infrastructure (hosting, networking) │
│ □ Application code (framework, language) │
│ │
│ Scale: │
│ • Total data size: __________ (GB/TB) │
│ • Total rows in affected tables: __________ │
│ • Active users affected: __________ │
│ • Services dependent on this: __________ │
│ • Current uptime requirement: __________ (99.9%?) │
│ │
│ Dependencies: │
│ • Code that reads from affected tables/APIs: __________ │
│ • Code that writes to affected tables/APIs: __________ │
│ • External services that depend on current format: ________ │
│ • Scheduled jobs that touch affected data: __________ │
│ • Cached data that references affected schema: __________ │
└──────────────────────────────────────────────────────────────┘
| Component | Affected? | Impact | Effort |
|---|---|---|---|
| Backend API routes | Yes/No | Which routes | Hours |
| Frontend pages | Yes/No | Which pages | Hours |
| Background workers | Yes/No | Which jobs | Hours |
| External integrations | Yes/No | Which services | Hours |
| Cached data | Yes/No | Which caches | Hours |
| Reports/analytics | Yes/No | Which queries | Hours |
| Mobile app | Yes/No | Which screens | Hours |
Document exactly what the system should look like after migration:
┌──────────────────────────────────────────────────────────────┐
│ TARGET STATE │
│ │
│ Schema Changes: │
│ ┌────────────────┬────────────────┬────────────────────────┐│
│ │ Current │ Target │ Migration Action ││
│ ├────────────────┼────────────────┼────────────────────────┤│
│ │ col: VARCHAR │ col: TEXT │ ALTER COLUMN ││
│ │ col: old_name │ col: new_name │ Add new, copy, drop ││
│ │ (missing) │ col: new_col │ ADD COLUMN + backfill ││
│ │ col: unused │ (removed) │ Stop reading, then DROP││
│ │ table: old │ table: new │ Create new, migrate ││
│ └────────────────┴────────────────┴────────────────────────┘│
│ │
│ Provider Changes: │
│ • Old: _____________ → New: _____________ │
│ • API compatibility: _____________ │
│ • Data format differences: _____________ │
│ │
│ Infrastructure Changes: │
│ • Old: _____________ → New: _____________ │
│ • Network topology changes: _____________ │
│ • DNS changes needed: _____________ │
└──────────────────────────────────────────────────────────────┘
The safest approach for any migration is the expand-contract pattern:
PHASE 1: EXPAND
┌──────────────────────────────────────────────────────────────┐
│ Deploy code that writes to BOTH old AND new │
│ • Add new column/table/service alongside old │
│ • Application writes to both (dual-write) │
│ • Application reads from old only │
│ • No user impact — old path is still primary │
└──────────────────────────────────────────────────────────────┘
│
▼
PHASE 2: MIGRATE
┌──────────────────────────────────────────────────────────────┐
│ Backfill existing data from old to new │
│ • Run migration script in batches (1000 rows at a time) │
│ • Validate each batch against source │
│ • Monitor for errors and data mismatches │
│ • Continue dual-writing for new data during backfill │
└──────────────────────────────────────────────────────────────┘
│
▼
PHASE 3: SWITCH
┌──────────────────────────────────────────────────────────────┐
│ Switch reads from old to new │
│ • Deploy code that reads from new │
│ • Keep writing to both (safety net) │
│ • Validate that reads from new match expectations │
│ • Monitor for errors, latency changes │
└──────────────────────────────────────────────────────────────┘
│
▼
PHASE 4: CONTRACT
┌──────────────────────────────────────────────────────────────┐
│ Remove old path (after bake period — 1-2 weeks) │
│ • Stop writing to old │
│ • Remove old column/table/service │
│ • Clean up dual-write code │
│ • Archive old data if needed │
└──────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────┐
│ BATCH MIGRATION CONFIGURATION │
│ │
│ Batch size: 1000 rows per iteration │
│ Pause between batches: 100ms (reduce DB pressure) │
│ Concurrency: 1 (serial to avoid locks) │
│ Progress tracking: Log every batch with count/total │
│ Error handling: Log error, skip row, continue │
│ Resume support: Track last processed ID │
│ Estimated time: [total rows / batch size * pause] │
│ │
│ Pseudocode: │
│ cursor = null │
│ while true: │
│ batch = SELECT * FROM old WHERE id > cursor │
│ ORDER BY id LIMIT 1000 │
│ if batch.empty: break │
│ for row in batch: │
│ try: transform_and_insert(row) │
│ catch: log_error(row, error) │
│ cursor = batch.last.id │
│ log_progress(cursor, total) │
│ sleep(100ms) │
└──────────────────────────────────────────────────────────────┘
PRE-MIGRATION VALIDATION:
┌──────────────────────────────────────────────────────────────┐
│ □ Row counts match between source and target │
│ □ Sample records match (compare 100 random rows) │
│ □ Aggregate values match (SUM, COUNT, AVG on key columns) │
│ □ Foreign key integrity verified │
│ □ Unique constraints hold │
│ □ No NULL values where NOT NULL expected │
│ □ Enum values are all valid in new schema │
└──────────────────────────────────────────────────────────────┘
POST-MIGRATION VALIDATION:
┌──────────────────────────────────────────────────────────────┐
│ □ Application smoke tests pass (login, list, create, edit) │
│ □ API integration tests pass │
│ □ No increase in error rate (compare before/after) │
│ □ No increase in response latency │
│ □ Background jobs complete successfully │
│ □ Webhook processing works │
│ □ Search/filtering returns correct results │
│ □ Reports and dashboards show correct data │
└──────────────────────────────────────────────────────────────┘
-- Row count comparison
SELECT 'old' AS source, COUNT(*) FROM old_table
UNION ALL
SELECT 'new' AS source, COUNT(*) FROM new_table;
-- Checksum comparison (detect data differences)
SELECT MD5(STRING_AGG(id::text || col1 || col2, ',' ORDER BY id))
FROM old_table;
-- Compare with same query on new_table
-- Missing records
SELECT id FROM old_table
EXCEPT
SELECT id FROM new_table;
-- And reverse to find orphans in new
| Stage | Environment | Data | Duration | Gate |
|---|---|---|---|---|
| 1. Unit tests | Local | Mock data | Minutes | All pass |
| 2. Integration tests | Local | Seed data | Minutes | All pass |
| 3. Staging dry-run | Staging | Copy of prod | Hours | Counts match |
| 4. Staging full run | Staging | Copy of prod | Hours | Smoke tests pass |
| 5. Production (Phase 1) | Production | Real data | Days | Dual-write works |
| 6. Production (Phase 2) | Production | Real data | Hours | Backfill complete |
| 7. Production (Phase 3) | Production | Real data | Days | Reads from new OK |
| 8. Production (Phase 4) | Production | Real data | Weeks | Old path removed |
Is something wrong after migration?
├── Data is incorrect
│ ├── Can fix with a patch script? → Fix forward
│ └── Widespread corruption? → ROLLBACK
│
├── Performance degraded
│ ├── Missing index? → Add index, fix forward
│ └── Fundamental design issue? → ROLLBACK
│
├── Application errors
│ ├── Isolated to one endpoint? → Fix and deploy
│ └── Widespread failures? → ROLLBACK
│
└── External integration broken
├── Config issue? → Fix config, redeploy
└── Fundamental incompatibility? → ROLLBACK
┌──────────────────────────────────────────────────────────────┐
│ ROLLBACK PROCEDURE │
│ │
│ Trigger: [What condition triggers rollback] │
│ Decision maker: [Who approves rollback] │
│ Estimated time: [How long rollback takes] │
│ │
│ Steps: │
│ 1. □ Stop all writes to new target │
│ 2. □ Switch reads back to old source │
│ 3. □ Deploy previous code version │
│ 4. □ Verify old path is working │
│ 5. □ Sync any data written to new back to old │
│ 6. □ Notify stakeholders │
│ 7. □ Post-mortem: identify root cause │
│ │
│ Data Recovery: │
│ • If dual-writing: old data is already current │
│ • If not: restore from pre-migration backup │
│ • For new records created during migration: │
│ [specific recovery procedure] │
│ │
│ IMPORTANT: Take a database snapshot BEFORE starting │
│ the migration. This is your last-resort safety net. │
└──────────────────────────────────────────────────────────────┘
DAY 0 — PREPARATION
├── □ Take database snapshot/backup
├── □ Notify stakeholders of migration window
├── □ Verify rollback procedure works (dry-run)
├── □ Deploy monitoring dashboards
└── □ Confirm staging migration was successful
DAY 1 — EXPAND PHASE
├── □ Deploy dual-write code
├── □ Verify both old and new receive writes
├── □ Monitor error rates for 24 hours
└── □ Go/No-Go decision for backfill
DAY 2-3 — MIGRATE PHASE
├── □ Run backfill script
├── □ Monitor progress (% complete)
├── □ Validate batch results
├── □ Verify row counts match at completion
└── □ Spot-check 100 random records
DAY 4 — SWITCH PHASE
├── □ Deploy code that reads from new
├── □ Monitor error rates and latency
├── □ Run integration tests against production
├── □ Verify dashboards and reports are correct
└── □ Go/No-Go decision for contract phase
DAY 5-14 — BAKE PERIOD
├── □ Monitor for 1-2 weeks with both paths active
├── □ Collect metrics: error rates, latency, data consistency
└── □ If stable → proceed to contract
DAY 15+ — CONTRACT PHASE
├── □ Remove dual-write code
├── □ Drop old columns/tables
├── □ Archive old data if needed
├── □ Clean up feature flags
└── □ Update documentation
| Metric | Normal Range | Alert Threshold | Action |
|---|---|---|---|
| Error rate | < 0.1% | > 1% | Pause and investigate |
| p95 latency | < 200ms | > 500ms | Check query plans |
| DB CPU | < 60% | > 80% | Reduce batch size |
| DB connections | < 80% pool | > 90% pool | Pause migration |
| Queue depth | < 100 | > 1000 | Slow down writes |
| Row count delta | 0 | > 0 | Investigate mismatch |
| Anti-Pattern | Why It Fails | Do Instead |
|---|---|---|
| Big-bang migration (all at once) | High risk, hard to rollback | Use expand-contract pattern |
| No backup before migration | No safety net if things go wrong | Always snapshot before start |
| Migrating production first | Untested path in production | Test in staging with prod data copy |
| No rollback plan | Stuck if something goes wrong | Write and test rollback procedure |
| Loading all rows into memory | OOM crash on large tables | Use cursor-based batch processing |
| Blocking migration (table locks) | Downtime during migration | Use non-blocking operations |
| No validation after migration | Silent data corruption | Validate counts, checksums, samples |
| Removing old path immediately | No safety net for hidden issues | Bake for 1-2 weeks minimum |
┌──────────────────────────────────────────────────────────────┐
│ MIGRATION READINESS CHECKLIST │
│ │
│ □ Current state fully documented │
│ □ Target state fully documented │
│ □ All affected code paths identified │
│ □ Migration scripts written and tested locally │
│ □ Migration tested on staging with production data copy │
│ □ Validation queries written and verified │
│ □ Rollback procedure documented and tested │
│ □ Database backup/snapshot scheduled before execution │
│ □ Monitoring dashboards deployed │
│ □ Stakeholders notified of timeline │
│ □ Go/No-Go criteria defined for each phase │
│ □ On-call engineer assigned during migration window │
│ □ Communication plan for incident (who to notify, how) │
└──────────────────────────────────────────────────────────────┘