migration-helper · diff
git:20260325.da398da to git:20260410.d55a9ea
27 added, 153 removed. Audit A to A.
---
name: migration-helper
description: "Guide safe database and code migrations with zero-downtime strategies."
autoInvoke: false
priority: medium
triggers:
- "database migration"
- "code migration"
- "zero-downtime"
---
# Skill: Migration Helper
- **Category:** Dev Expert
- **Used By:** architect, backend agents
-
- ---
-
- ## Overview
-
- Guide safe database and code migrations with zero-downtime strategies.
+ Safe database and code migrations with zero-downtime strategies.
---
- ## 1. Migration Safety Rules
+ ## Safety Rules
- ```
1. Always backup before migration
- 2. Test migration on staging first
+ 2. Test on staging first
3. Make migrations reversible
4. Deploy in small batches
5. Monitor during migration
- ```
---
- ## 2. Database Migration Patterns
-
- ### Schema Changes
+ ## Schema Changes
| Change | Safe Approach |
|--------|---------------|
- | Add column | Add nullable first, backfill, then NOT NULL |
+ | Add column | Add nullable → backfill → NOT NULL |
| Remove column | Stop using → deploy → drop |
- | Rename column | Add new → copy data → deploy → drop old |
+ | Rename column | Add new → copy → deploy → drop old |
| Add index | CREATE INDEX CONCURRENTLY |
- | Change type | Add new column → migrate → drop old |
+ | Change type | Add new col → migrate → drop old |
- ### Zero-Downtime Pattern
- ```
- Phase 1: Add new column (nullable)
- Phase 2: Dual-write (write to both columns)
- Phase 3: Backfill old data
- Phase 4: Switch reads to new column
- Phase 5: Remove old column writes
- Phase 6: Drop old column
- ```
+ ### Zero-Downtime (6 phases)
+ Add nullable → dual-write → backfill → switch reads → remove old writes → drop old column.
+
---
- ## 3. Migration Script Template
+ ## Migration Script Pattern
```typescript
- // migrations/20250128_add_user_status.ts
export async function up(db: Database) {
- // 1. Add column
- await db.query(`
- ALTER TABLE users
- ADD COLUMN status VARCHAR(20) DEFAULT 'active'
- `)
-
- // 2. Backfill in batches
- let lastId = 0
- while (true) {
- const rows = await db.query(`
- UPDATE users SET status = 'active'
- WHERE id > $1 AND status IS NULL
- LIMIT 1000
- RETURNING id
- `, [lastId])
- if (rows.length === 0) break
- lastId = rows[rows.length - 1].id
- }
-
- // 3. Add constraint
- await db.query(`
- ALTER TABLE users
- ALTER COLUMN status SET NOT NULL
- `)
+ await db.query(`ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active'`);
+ // Backfill in batches of 1000
+ // Add NOT NULL constraint
}
-
export async function down(db: Database) {
- await db.query(`ALTER TABLE users DROP COLUMN status`)
- }
- ```
-
- ---
-
- ## 4. Code Migration Strategies
-
- ### Feature Flag Migration
- ```typescript
- // Phase 1: Behind flag
- if (featureFlags.newPaymentSystem) {
- return newPaymentService.process(order)
+ await db.query(`ALTER TABLE users DROP COLUMN status`);
}
- return oldPaymentService.process(order)
-
- // Phase 2: Gradually increase flag percentage
- // Phase 3: Remove flag, delete old code
```
- ### Strangler Fig Pattern
- ```
- 1. Identify bounded context to migrate
- 2. Create new implementation alongside old
- 3. Route traffic gradually to new
- 4. Remove old when new is stable
- ```
-
---
- ## 5. Data Migration Checklist
-
- Before:
- - [ ] Full backup created
- - [ ] Migration tested on staging
- - [ ] Rollback plan documented
- - [ ] Monitoring alerts configured
-
- During:
- - [ ] Run in batches (not all at once)
- - [ ] Monitor performance/errors
- - [ ] Verify data integrity
+ ## Code Migration
- After:
- - [ ] Data validation queries
- - [ ] Application smoke tests
- - [ ] Monitor for 24-48 hours
- - [ ] Document changes
+ - **Feature flags:** Behind flag → gradually increase % → remove flag + old code
+ - **Strangler Fig:** New impl alongside old → route traffic gradually → remove old
---
- ## 6. Common Migration Scenarios
-
- ### Adding Required Column
- ```sql
- -- Wrong: Locks table, fails on existing rows
- ALTER TABLE users ADD COLUMN email VARCHAR NOT NULL;
-
- -- Right: Three-phase approach
- ALTER TABLE users ADD COLUMN email VARCHAR;
- UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;
- ALTER TABLE users ALTER COLUMN email SET NOT NULL;
- ```
-
- ### Large Table Index
- ```sql
- -- Wrong: Blocks writes
- CREATE INDEX idx_orders_user ON orders(user_id);
-
- -- Right: Non-blocking
- CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);
- ```
-
- ### Type Conversion
- ```sql
- -- Add new column
- ALTER TABLE products ADD COLUMN price_cents INTEGER;
-
- -- Migrate data
- UPDATE products SET price_cents = (price * 100)::INTEGER;
+ ## Checklist
- -- Switch application to use new column
- -- Drop old column
- ALTER TABLE products DROP COLUMN price;
- ```
+ **Before:** Backup, tested on staging, rollback plan, monitoring alerts.
+ **During:** Batch processing, monitor performance, verify integrity.
+ **After:** Validation queries, smoke tests, monitor 24-48h, document.
---
- ## 7. Rollback Strategies
+ ## Rollback Strategies
- | Strategy | When to Use |
- |----------|-------------|
- | Script rollback | Data changes, can undo |
- | Backup restore | Major failure, data loss |
+ | Strategy | When |
+ |----------|------|
+ | Script rollback | Reversible data changes |
+ | Backup restore | Major failure |
| Feature flag off | Code changes |
| Traffic reroute | Service migration |
---
-
- ## Best Practices
-
- ### Do's
- - Test migrations on copy of prod data
- - Make migrations idempotent
- - Batch large data updates
- - Monitor during execution
- - Keep rollback scripts ready
-
- ### Don'ts
- - Run untested migrations on prod
- - Make irreversible changes without backup
- - Migrate during peak traffic
- - Skip staging environment
- - Assume migrations are fast
-
- ---
-