schema-migration-review

name	schema-migration-review
enabled	true
description	Use when performing schema migration review — reviews database schema migrations for safety, performance impact, and backward compatibility. This template ensures migrations are evaluated for lock contention, data integrity, rollback capability, and deployment sequencing before execution in production environments.
required_connections	[{"prefix":"database","label":"Database Platform"},{"prefix":"vcs","label":"Version Control System"}]
config_fields	[{"key":"database_type","label":"Database Type","required":true,"placeholder":"e.g., PostgreSQL, MySQL, MongoDB"},{"key":"migration_id","label":"Migration ID","required":true,"placeholder":"e.g., 20260314_add_user_preferences"},{"key":"table_row_count","label":"Affected Table Row Count","required":false,"placeholder":"e.g., 50 million"}]
features	["SCHEMA_MIGRATION","DATABASE","ARCHITECTURE"]

Schema Migration Review

Phase 1: Migration Inventory

Document all changes in this migration.

Change	Table	Column/Index	Operation	Nullable	Default
			ADD/DROP/ALTER/RENAME	Y/N

Phase 2: Safety Assessment

Lock Analysis:

Operation	Lock Type	Table Size	Estimated Lock Duration	Acceptable?
	AccessExclusive/ShareLock/None			Y/N

Safety Checklist:

• No ALTER TABLE on large tables without online DDL strategy
• No DROP COLUMN without confirming no application reads
• No NOT NULL constraint added without default value
• No full table rewrite on tables >1M rows during peak hours
• No index creation without CONCURRENTLY (PostgreSQL) or equivalent
• Foreign key constraints evaluated for lock implications
• No data type changes that could truncate or corrupt data

Phase 3: Backward Compatibility

Decision Matrix:

Change Type	Backward Compatible	Strategy
Add nullable column	Yes	Deploy migration, then code
Add non-nullable column with default	Depends on DB	Test lock behavior first
Drop column	No	Deploy code ignoring column, then migration
Rename column	No	Add new column, migrate data, update code, drop old
Change column type	No	Add new column, dual-write, backfill, switch, drop old
Add index	Yes (if concurrent)	Deploy anytime
Drop index	Yes	Verify no queries depend on it first

• Application code compatible with both pre- and post-migration schema
• Deployment order documented (code first or migration first)
• Multi-phase migration plan for breaking changes

Phase 4: Rollback Plan

• Rollback migration script exists and tested
• Rollback does not cause data loss
• Rollback can complete within acceptable downtime window
• Rollback has been tested against a copy of production data

Rollback Steps:

1. • Step description
2. • Verification after rollback

Phase 5: Execution Plan

• Migration tested against production-size dataset
• Execution time estimated: ___
• Maintenance window required: Y/N
• Application deployment coordination plan
• Monitoring plan during migration
• Communication plan for stakeholders

Pre-execution Checklist:

• Database backup taken
• Replica lag acceptable
• Connection pool capacity sufficient
• Statement timeout configured
• Lock timeout configured

Counter-Rationalizations

Shortcut	Counter	Why
"We can skip some steps for this case"	Adapt the workflow steps, don't skip them	Skipped steps are where incidents and oversights originate
"The user seems to already know what to do"	Complete all workflow phases with the user	The workflow catches blind spots that experience alone misses
"This is a minor case, full process is overkill"	Scale the process down, don't turn it off	Minor cases become major when unstructured; the process scales, not disappears
"I'll fill in the details later"	Complete each section before moving on	Deferred details are forgotten; real-time capture is more accurate
"The template output isn't necessary"	Always produce the structured output format	Structured output enables comparison, audit trails, and handoff to other teams

Output Format

Summary

• Migration: ___
• Database: ___
• Risk level: Low / Medium / High / Critical
• Estimated execution time: ___
• Backward compatible: Y/N
• Requires maintenance window: Y/N

Action Items

• Address all safety checklist failures
• Test migration on staging with production-size data
• Test rollback procedure
• Schedule execution window
• Notify affected teams
• Monitor database metrics during and after migration