Migration Workflow — Complete Guide

# 1. Create a feature branch
git checkout -b feat/add-phone-to-users

# 2. Make changes to models/schema
# (Modify SQLAlchemy models, Django models, schema.prisma, etc.)

# 3. Generate the migration
# Alembic:
alembic revision --autogenerate -m "add phone to users"

# Django:
python manage.py makemigrations

# Prisma:
npx prisma migrate dev --name add_phone_to_users

# 4. Apply locally and verify
alembic upgrade head
python manage.py migrate
npx prisma migrate deploy

# 5. Test the migration
# - Run the application
# - Verify new features work
# - Test downgrade

# Migration Review Checklist

## Schema Changes
- [ ] Does the migration add, remove, or rename columns?
- [ ] Are new columns nullable or have defaults?
- [ ] Are removed columns actually unused?
- [ ] Is the column type appropriate (VARCHAR length, DECIMAL precision)?

## Data Safety
- [ ] Does the migration preserve existing data?
- [ ] Are data backfills batched (not one massive UPDATE)?
- [ ] Is there a down migration that restores data?
- [ ] Will the migration lock tables for more than a few seconds?

## Performance
- [ ] Does the migration add indexes for new query patterns?
- [ ] Does the migration remove unused indexes?
- [ ] Will the migration complete within the deployment window?
- [ ] Are there online alternatives for large tables?

## Rollback
- [ ] Is there a tested down migration?
- [ ] Does the down migration restore exact previous state?
- [ ] Is there a communication plan if rollback is needed?

## Code Changes
- [ ] Does application code match the new schema?
- [ ] Are there any references to removed columns?
- [ ] Are environment variables updated for new config?

# .github/workflows/migrations.yml
name: Migration Tests

on:
    pull_request:
        paths:
            - 'migrations/**'
            - '**/models/**'
            - 'prisma/schema.prisma'

jobs:
    test-migration:
        runs-on: ubuntu-latest
        services:
            postgres:
                image: postgres:16
                env:
                    POSTGRES_PASSWORD: postgres
                options: >-
                    --health-cmd pg_isready
                    --health-interval 10s
                    --health-timeout 5s
                    --health-retries 5

        steps:
            - uses: actions/checkout@v4

            - name: Set up Python
              uses: actions/setup-python@v5
              with:
                  python-version: '3.12'

            - name: Install dependencies
              run: pip install -r requirements.txt

            - name: Create test database
              run: createdb -h localhost -U postgres test_migrations

            - name: Apply all migrations
              run: alembic upgrade head
              env:
                  DATABASE_URL: postgresql://postgres:postgres@localhost/test_migrations

            - name: Verify schema matches models
              run: python -c "from app import db; db.engine.connect()"

            - name: Test rollback
              run: |
                  alembic downgrade -1
                  alembic upgrade head

# Deployment pipeline
stages:
  - migrate
  - deploy
  - verify

# Stage 1: Run migrations
migrate:
  stage: migrate
  script:
    - alembic upgrade head
  environment: production
  only:
    - main

# Stage 2: Deploy application
deploy:
  stage: deploy
  script:
    - kubectl apply -f deployment.yaml
  environment: production
  needs: ["migrate"]
  only:
    - main

# Stage 3: Verify deployment
verify:
  stage: verify
  script:
    - python scripts/verify_schema.py
    - python scripts/run_smoke_tests.py
  environment: production
  needs: ["deploy"]
  only:
    - main

# scripts/rollback.py - Automated rollback script
import subprocess
import sys
import time

def rollback_migration(steps=1):
    print(f"Rolling back {steps} migration(s)...")

    try:
        # 1. Run rollback
        result = subprocess.run(
            ['alembic', 'downgrade', f'-{steps}'],
            capture_output=True, text=True, check=True
        )
        print("Schema rollback successful")

        # 2. Verify rollback
        result = subprocess.run(
            ['alembic', 'current'],
            capture_output=True, text=True, check=True
        )
        print(f"Current version: {result.stdout.strip()}")

        # 3. Run health checks
        time.sleep(5)
        health_result = subprocess.run(
            ['python', 'scripts/health_check.py'],
            capture_output=True, text=True
        )

        if health_result.returncode == 0:
            print("Health checks passed after rollback")
            return True
        else:
            print(f"Health check failed: {health_result.stderr}")
            return False

    except subprocess.CalledProcessError as e:
        print(f"Rollback failed: {e.stderr}")
        return False

if __name__ == '__main__':
    steps = int(sys.argv[1]) if len(sys.argv) > 1 else 1
    success = rollback_migration(steps)
    sys.exit(0 if success else 1)

// migration-communication.js
const migrationPlan = {
    migration: '20260628_add_phone_to_users',
    author: 'developer@example.com',
    reviewer: 'senior@example.com',

    // Timing
    plannedWindow: '2026-06-29 02:00 - 02:30 UTC',
    expectedDuration: '5 minutes',
    riskLevel: 'low', // low, medium, high

    // Schema changes
    changes: [
        {
            type: 'add_column',
            table: 'users',
            column: 'phone',
            details: 'VARCHAR(20), nullable, no default',
        },
    ],

    // Rollback
    rollbackProcedure: 'alembic downgrade -1',
    rollbackDuration: '1 minute',
    rollbackTested: true,

    // Communication
    notify: [
        '#engineering',
        '@backend-team',
        '@on-call',
    ],
};

module.exports = migrationPlan;