{"product_id":"database-migration-pipeline-blueprint","title":"Database Migration Pipeline Blueprint","description":"\u003ch3\u003eDatabase Migration Pipeline Blueprint\u003c\/h3\u003e\n\u003cp\u003eDatabase migrations in production are the most anxiety-inducing part of any deployment. At Cigna, a migration that added an index to a 200-million-row table locked the table for 47 minutes during business hours. The application threw connection timeout errors. The on-call engineer could not roll back because the migration had already modified the schema, and the rollback script had not been tested. This template treats database changes as first-class pipeline citizens with dry-run validation, lock detection, rollback verification, and execution time estimation.\u003c\/p\u003e\n\n\u003ch3\u003ePipeline Stages\u003c\/h3\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cstrong\u003emigration-lint\u003c\/strong\u003e — \u003ccode\u003esquawk\u003c\/code\u003e (PostgreSQL) or \u003ccode\u003eskeema lint\u003c\/code\u003e (MySQL) checks migration SQL for dangerous patterns: \u003ccode\u003eALTER TABLE ... ADD COLUMN ... DEFAULT\u003c\/code\u003e (rewrites table in PostgreSQL \u0026lt; 11), \u003ccode\u003eCREATE INDEX\u003c\/code\u003e without \u003ccode\u003eCONCURRENTLY\u003c\/code\u003e, \u003ccode\u003eDROP COLUMN\u003c\/code\u003e without data backup.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003edry-run\u003c\/strong\u003e — \u003ccode\u003eflyway migrate -dryRun\u003c\/code\u003e or \u003ccode\u003eliquibase updateSQL\u003c\/code\u003e generates the SQL that would execute without applying it. Output posted as PR comment. Reviewers see exact DDL and DML statements.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003erollback-verify\u003c\/strong\u003e — Applies migration to a test database, then applies the rollback script, then verifies the schema matches the pre-migration state. If the rollback script is missing or produces a different schema, the pipeline fails.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003eestimate-execution\u003c\/strong\u003e — Runs \u003ccode\u003eEXPLAIN ANALYZE\u003c\/code\u003e (dry run) against a staging database with production-equivalent data volume. Estimates lock duration and execution time. Migrations exceeding 60 seconds are flagged for off-hours execution.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003edeploy-dev\u003c\/strong\u003e — \u003ccode\u003eflyway migrate\u003c\/code\u003e or \u003ccode\u003eliquibase update\u003c\/code\u003e against dev database. Runs application integration tests against the migrated schema to verify ORM compatibility.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003edeploy-staging\u003c\/strong\u003e — Migration against staging database with production-equivalent data. Manual approval required. Timing metrics compared against estimates.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003edeploy-prod\u003c\/strong\u003e — Two approvals required. Maintenance window notification sent via Slack. Migration executed with statement timeout. Automatic rollback if any statement exceeds the timeout. Post-migration health check verifies application connectivity.\u003c\/li\u003e\n\u003c\/ul\u003e\n\n\u003ch3\u003eSecurity Gates\u003c\/h3\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cstrong\u003eNo raw SQL in application code\u003c\/strong\u003e — Migration files are the only place DDL executes. Application code uses ORM\/query builder only.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003eCredential isolation\u003c\/strong\u003e — Migration credentials are separate from application credentials with elevated DDL permissions. Application database user cannot \u003ccode\u003eALTER TABLE\u003c\/code\u003e or \u003ccode\u003eDROP\u003c\/code\u003e.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003eAudit logging\u003c\/strong\u003e — Every migration execution is logged with: who approved it, when it ran, how long it took, and the exact SQL executed.\u003c\/li\u003e\n\u003c\/ul\u003e\n\n\u003ch3\u003eWhat Breaks First\u003c\/h3\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cstrong\u003eLock contention on large tables\u003c\/strong\u003e — \u003ccode\u003eALTER TABLE ADD COLUMN\u003c\/code\u003e acquires an \u003ccode\u003eACCESS EXCLUSIVE\u003c\/code\u003e lock in PostgreSQL. Fix: use \u003ccode\u003eALTER TABLE ... ADD COLUMN ... DEFAULT NULL\u003c\/code\u003e (lock-free in PostgreSQL 11+) and backfill in batches.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003eMigration ordering conflict between branches\u003c\/strong\u003e — Two branches create migration V5, but with different content. Flyway rejects the checksum mismatch. Fix: use timestamp-based versioning (\u003ccode\u003eV20260401120000\u003c\/code\u003e) instead of sequential integers.\u003c\/li\u003e\n\u003cli\u003e\n\u003cstrong\u003eORM schema drift after migration\u003c\/strong\u003e — The migration adds a column, but the ORM entity was not updated. The application ignores the new column, and data written to it is lost. Fix: generate the ORM schema from the database after migration and diff against the committed schema.\u003c\/li\u003e\n\u003c\/ul\u003e","brand":"Citadel Cloud Management","offers":[{"title":"Default Title","offer_id":54890411917603,"sku":"CCM-DEV-019","price":35.0,"currency_code":"USD","in_stock":true}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/0979\/8539\/7027\/files\/citadel-architecture-product_9f330659-4cdf-4355-804c-9437f1f96dbc.png?v=1775137970","url":"https:\/\/www.citadelcloudmanagement.com\/products\/database-migration-pipeline-blueprint","provider":"Citadel Cloud Management","version":"1.0","type":"link"}