Database Migrations

The rules

These rules exist because migrations run on 80+ agency databases simultaneously. Breaking them can corrupt production data or take down multiple agencies at once.

1. Only add in upgrade() — never ALTER TABLE ... DROP COLUMN or ALTER TABLE ... MODIFY COLUMN. Only CREATE TABLE and ADD COLUMN.
2. downgrade() must correctly reverse upgrade() — dropping a table that was created in the same migration is expected and correct.
3. Keep the chain linear — one head revision at all times. No branching.
4. Always review generated migration files — Alembic sometimes detects ghost changes or generates incorrect SQL.
5. Use a ticket number in the migration message — makes it traceable.

These rules are non-negotiable

Orchid’s GitHub Actions CI enforces a single head and valid revision chain on every PR. A migration that creates multiple heads will block the PR. A migration that drops or alters a column in upgrade() will break rolling deployments.

Commands

# Generate a new migration (after changing models)
flask db migrate -m "JMS-1234 add my_new_table"

# Apply pending migrations
flask db upgrade

# Revert the most recent migration
flask db downgrade

# Check current revision
flask db current

# Verify single head (must output exactly one revision)
flask db heads

# Show full migration history
flask db history

# Show migration chain summary
python scripts/show_migration_chain.py

The full migration workflow

1. Make your model changes in orchid/models/*.py

2. Generate the migration

   flask db migrate -m "JMS-XXXX add thing to model"

   Alembic inspects your models and the current database state, then generates a migration file in migrations/versions/.

3. Review the generated file

   Open migrations/versions/[hash]_jms_xxxx_...py and check:

   # ✅ Good upgrade():
   def upgrade():
       op.create_table('my_table', ...)
       op.add_column('existing_table', sa.Column('new_col', sa.String(255), nullable=True))
   
   # ❌ Bad upgrade() — DO NOT COMMIT:
   def upgrade():
       op.drop_column('table', 'col')  # breaks old instances
       op.alter_column('table', 'col', ...)  # same problem

   Also verify:

   • down_revision points to the correct parent
   • No unexpected tables or columns are included

4. Apply locally and test

   flask db upgrade
   flask db current  # should show your new revision

5. Verify single head

   flask db heads
   # Should output exactly one revision hash

6. Commit model + migration together

   git add orchid/models/my_model.py migrations/versions/abc123_jms_xxxx.py
   git commit -m "JMS-XXXX: add my_new_table"

Migration file anatomy

"""JMS-1234 add delivery_date to case_closing_details

Revision ID: a1b2c3d4e5f6
Revises: 9z8y7x6w5v4u    ← parent revision
Create Date: 2025-05-29 10:00:00.000000
"""
from alembic import op
import sqlalchemy as sa

# Required identifiers
revision = 'a1b2c3d4e5f6'
down_revision = '9z8y7x6w5v4u'
branch_labels = None
depends_on = None

def upgrade():
    op.add_column(
        'case_closing_details',
        sa.Column('delivery_date', sa.Date(), nullable=True)
    )

def downgrade():
    op.drop_column('case_closing_details', 'delivery_date')

MySQL-specific migrations

Sometimes you need MySQL-specific SQL that Alembic cannot generate automatically:

def upgrade():
    # Changing charset requires raw SQL
    op.execute("""
        ALTER TABLE email_message
        DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    """)

def downgrade():
    op.execute("""
        ALTER TABLE email_message
        DEFAULT CHARSET=utf8mb3
    """)

Note

Raw SQL in migrations (op.execute()) is fine for MySQL-specific operations. However, it will fail in the test suite (which uses SQLite). Wrap MySQL-specific migrations in a dialect check if you need them to be testable:

from sqlalchemy import text

def upgrade():
    bind = op.get_bind()
    if bind.dialect.name == 'mysql':
        op.execute(text("ALTER TABLE ... CHARSET=utf8mb4"))

Fixing a broken migration chain

If you have two heads (usually from a merge conflict where two migrations were created independently):

flask db heads
# Shows two revision IDs — need to merge

Option 1: Merge migration (preferred)

flask db merge heads -m "merge migration branches"

This creates a new migration that merges the two heads into one. Review the generated merge migration — it usually has empty upgrade() and downgrade() functions.

Option 2: Manual fix

Edit the down_revision in the newer migration file to point to the older one, creating a linear chain.

After fixing:

flask db heads  # must show exactly one head

The migration lock

In production, migrations run automatically on EB startup. The migration_lock table prevents concurrent runs when multiple EB instances start simultaneously:

1. Instance A starts → tries to acquire lock → succeeds → runs migrations → releases lock
2. Instance B starts (at the same time) → tries to acquire lock → waits → lock released → checks: already at latest revision → exits

The lock is stored in the migration_lock table in each agency’s database. If a migration fails mid-run and the lock is not released, you can manually clear it:

DELETE FROM migration_lock;

Then investigate and fix the migration before the next startup.