Database Migration Workflow
Safe schema changes and data migration workflow with rollback strategies and zero-downtime deployment
The Database Migration workflow provides a structured, safe approach to schema changes and data migrations, ensuring data integrity, minimal downtime, and the ability to rollback if issues arise.
Overview#
| Property | Value |
|---|---|
| Phases | 4 |
| Tier | Free |
| Typical Duration | 5-6 days |
| Best For | Schema changes, data transformations, database refactoring |
Outcomes#
A successful database migration results in:
- Migration completed safely with rollback readiness
- Migration rehearsed in staging environment
- Rollback procedure tested and documented
- Data integrity checks passing
Phases#
Phase 1: Schema Design (1 day)#
Agents: database-expert
Design the new schema changes with careful consideration for backward compatibility and data integrity.
Tasks:
- Document current schema state
- Design target schema changes
- Identify breaking vs non-breaking changes
- Plan for backward compatibility
- Define data transformation requirements
- Create migration scripts
Migration Types:
┌────────────────────────────────────────────────────────────┐
│ Migration Types │
├─────────────────┬──────────────────────────────────────────┤
│ Non-Breaking │ Add column (nullable), add table, │
│ │ add index, expand column type │
├─────────────────┼──────────────────────────────────────────┤
│ Breaking │ Remove column, rename column, │
│ │ change column type, drop table │
├─────────────────┼──────────────────────────────────────────┤
│ Data Migration │ Transform existing data, backfill │
│ │ values, merge/split tables │
└─────────────────┴──────────────────────────────────────────┘
Schema Change Example:
Phase 2: Migration Plan (1 day)#
Agents: database-expert, devops-expert
Create a detailed migration plan including timing, rollback procedures, and communication strategy.
Tasks:
- Define migration window and timing
- Create step-by-step execution plan
- Document rollback procedures
- Plan data validation checks
- Coordinate with stakeholders
- Set up monitoring for migration
Migration Plan Template:
Phase 3: Implementation (2-3 days)#
Agents: database-expert, backend-expert
Execute the migration with proper staging validation and production deployment.
Tasks:
- Test migration in development
- Run migration in staging environment
- Validate staging data integrity
- Execute production migration
- Run data integrity checks
- Update application code as needed
Prisma Migration Commands:
Zero-Downtime Pattern:
Phase 1: Deploy code that handles both schemas
(reads new column, falls back to old)
│
▼
Phase 2: Run migration to add new column
│
▼
Phase 3: Backfill data for new column
│
▼
Phase 4: Deploy code that requires new column
│
▼
Phase 5: Run migration to remove old column
Backfill Script Example:
Phase 4: Verification (1 day)#
Agents: testing-expert
Verify the migration was successful and all data integrity requirements are met.
Tasks:
- Run data integrity checks
- Verify row counts match expectations
- Test application functionality
- Validate API responses
- Check for data inconsistencies
- Document migration results
Data Integrity Checks:
Starting the Workflow#
Completion Signals#
Track progress with these checkpoints:
- Migration rehearsed - Successfully tested in staging
- Rollback tested - Rollback procedure verified
- Data integrity checks passing - All validation queries pass
Best Practices#
Always Have a Backup#
Use Transactions#
Batch Large Updates#
Monitor During Migration#
Tips for Success#
- Never skip staging - Always test migrations in a production-like environment
- Plan for failure - Have a tested rollback procedure ready
- Communicate early - Notify stakeholders about planned downtime
- Batch large operations - Avoid long-running transactions
- Monitor closely - Watch for errors and performance issues during migration