GitLab CI/CD Database Migration Workflow Prompt

You are a senior DevOps / backend engineer who has run thousands of production database migrations via GitLab CI/CD. You know that "deploy code + run migrations" is a recipe for outages when migrations are non-backward-compatible, and you know how to avoid it.

I will provide:
- The database (Postgres / MySQL / Mongo / etc.)
- The migration tool (Alembic, Flyway, Liquibase, Rails, Django, custom)
- The deployment strategy (blue/green, rolling, canary)
- The current pipeline structure
- A specific migration that's risky OR the goal: design a safe workflow

Your job:

1. **Categorize the migration**:
   - **Additive (safe)** — new column nullable, new table, new index CONCURRENTLY → backward-compatible
   - **Destructive (unsafe)** — drop column, change type, NOT NULL on existing column, rename → breaks old code
   - **Read-heavy (slow)** — adding index on big table without CONCURRENTLY, backfilling data, ALTER TABLE on huge table
2. **Apply the expand-contract pattern** for destructive migrations:
   - **Phase 1 (expand)**: deploy migration that ADDS new schema (e.g., new column); old code keeps working
   - **Phase 2 (migrate)**: deploy new code that writes to both old AND new (dual-write)
   - **Phase 3 (verify)**: confirm new schema has full data; old usage gone
   - **Phase 4 (contract)**: deploy migration that REMOVES old schema
   - Each phase is a SEPARATE deploy
3. **For pipeline structure**:
   ```
   test → migrate-staging → smoke-test → migrate-prod (manual) → deploy-code (manual)
   ```
   - Migrations run BEFORE code deploy (if backward-compat)
   - Migrations run AFTER code deploy (if backward-incompat → bad! redesign)
4. **For Postgres-specific**:
   - `CREATE INDEX CONCURRENTLY` — non-blocking; required for large tables
   - `ALTER TABLE ADD COLUMN ... NOT NULL DEFAULT ...` rewrites table (Postgres < 11) or instant (Postgres ≥ 11)
   - `ALTER TABLE DROP COLUMN` is cheap; data isn't reclaimed without VACUUM FULL
5. **For MySQL-specific**:
   - Online DDL: `ALGORITHM=INPLACE, LOCK=NONE` where supported
   - pt-online-schema-change / gh-ost for complex changes
   - InnoDB row format affects what's possible online
6. **For rollback**:
   - Migrations should be **forward-only** — don't write `down` that drops data
   - If something fails, deploy a NEW migration that fixes it
   - Have a `--dry-run` test on staging snapshot before prod
7. **For long migrations (hours)**:
   - Run outside CI pipeline (Kubernetes Job, dedicated worker)
   - CI triggers and monitors; doesn't block pipeline
   - Background backfill with batch updates
8. **For migration ordering**:
   - Migrations apply in order — usually filename-prefixed by timestamp
   - Concurrent merges can interleave migrations; resolve via rebase

Mark DESTRUCTIVE: DROP COLUMN in same deploy as code that removes its usage (deploy ordering matters), TRUNCATE / DELETE without backup, schema change on a busy table during peak.

---

Database + migration tool: [DESCRIBE]
Deployment strategy: [DESCRIBE]
Migration to plan / debug: [DESCRIBE]
Current pipeline: [DESCRIBE]
Goal: [design / specific migration safety]

Why this prompt works

Database migrations are the highest-risk part of any deploy. Naive “deploy code + run migrations” coupled with non-backward-compatible changes causes incidents. This prompt walks the expand-contract pattern.

How to use it

1. Categorize the migration — safe vs unsafe.
2. For unsafe, plan the expand-contract phases.
3. Run migrations in pipeline BEFORE code deploy for backward-compat.
4. For long migrations, use background workers, not CI jobs.

Useful commands

# Postgres — find long-running migrations
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC LIMIT 10;

# Postgres — check table size before ALTER
SELECT pg_size_pretty(pg_total_relation_size('mytable'));

# Postgres — find indexes
SELECT indexname FROM pg_indexes WHERE tablename = 'mytable';

# MySQL — show table status
SHOW TABLE STATUS LIKE 'mytable';

# Alembic — preview SQL
alembic upgrade head --sql > migration.sql

# Flyway
flyway info
flyway validate
flyway migrate -outOfOrder=true

# Liquibase
liquibase update-sql
liquibase update
liquibase status

Pipeline patterns

Standard pattern (backward-compat migration)

stages: [test, migrate-staging, smoke-test, migrate-prod, deploy-prod]

test-migrations:
  stage: test
  image: postgres:16
  services: [postgres:16]
  script:
    - alembic upgrade head --sql > migration.sql
    - cat migration.sql
    - alembic upgrade head            # run against test DB
    - alembic downgrade base          # ensure downgrade works (optional)
  rules:
    - if: $CI_PIPELINE_SOURCE == "merge_request_event"

migrate-staging:
  stage: migrate-staging
  image: python:3.12
  script:
    - export DATABASE_URL=$STAGING_DB_URL
    - alembic upgrade head
  environment: { name: staging-db }
  rules:
    - if: $CI_COMMIT_BRANCH == $CI_DEFAULT_BRANCH

smoke-test-staging:
  stage: smoke-test
  script: ./smoke-test.sh https://staging.example.com
  needs: [migrate-staging]

migrate-prod:
  stage: migrate-prod
  image: python:3.12
  script:
    - export DATABASE_URL=$PROD_DB_URL
    - alembic upgrade head
  environment: { name: prod-db }
  when: manual
  rules:
    - if: $CI_COMMIT_TAG =~ /^v\d+\.\d+\.\d+$/

deploy-prod:
  stage: deploy-prod
  needs: [migrate-prod]
  script: ./deploy.sh
  environment: { name: production }
  when: manual
  rules:
    - if: $CI_COMMIT_TAG =~ /^v\d+\.\d+\.\d+$/

Expand-contract pattern (across multiple MRs)

MR 1: Expand
  - Migration: ADD COLUMN new_name (nullable)
  - Code: dual-write (writes both old_name and new_name)
  - Deploy

MR 2: Migrate
  - No migration (backfill complete in background)
  - Code: dual-read (prefer new_name, fall back to old_name)
  - Deploy

MR 3: Verify
  - No migration
  - Code: only reads new_name (writes old_name still for safety)
  - Deploy, verify metrics

MR 4: Contract
  - Migration: DROP COLUMN old_name
  - Code: only uses new_name (stops writing old_name)
  - Deploy

Long-running migration (background)

trigger-backfill:
  stage: migrate-prod
  script:
    - kubectl create job --from=cronjob/backfill backfill-$(date +%s) -n prod
  when: manual
  rules:
    - if: $CI_COMMIT_TAG

# Backfill is a separate K8s Job that runs over hours; CI doesn't wait

Migration safety job (lint)

migration-safety-check:
  image: python:3.12
  script:
    - pip install squawk            # Postgres migration linter
    - squawk migrations/versions/*.sql
    # Or: check for dangerous patterns
    - ./scripts/check-migration-safety.sh migrations/versions/$(ls -t migrations/versions/ | head -1)
  rules:
    - if: $CI_PIPELINE_SOURCE == "merge_request_event"
      changes:
        - migrations/**/*

# scripts/check-migration-safety.sh
#!/bin/bash
MIGRATION=$1
if grep -i "DROP COLUMN\|DROP TABLE\|NOT NULL" "$MIGRATION" && ! grep -i "concurrently" "$MIGRATION"; then
    echo "WARNING: Destructive migration detected"
    echo "Ensure expand-contract pattern is followed"
    # Optionally exit 1 to block merge
fi

Common findings this catches

• Migration drops column in same deploy as code that removed usage → wrong order; risk of outage.
• CREATE INDEX without CONCURRENTLY on production table → table lock during build.
• Migration not tested against staging → broke production on unique edge cases.
• Backfill runs in CI for hours → blocks pipeline, blocks other deploys.
• ALTER TABLE ... NOT NULL without backfill or default → fails on existing rows.
• Migration script not idempotent → re-run fails on partial completion.
• Two MRs add migrations with same timestamp → ordering ambiguous; resolve via rebase.

When to escalate

• Schema change requires DBA review — engage early.
• Migration estimated to take many hours on production — coordinate maintenance window.
• Risk of data loss in any rollback path — pause; bring in DBA + product owner.