migrations#

alembic configuration#

location: _migrations/
config file: alembic.ini
filename format: YYYY_MM_DD_HHMMSS_REV_SLUG
template: script.py.mako supports dialect-specific code via ${dialect} variable

separate migration chains#

key design decision: separate migration files per database dialect

_migrations/versions/
├── postgresql/   # 113 PostgreSQL migrations
└── sqlite/       # 109 SQLite migrations

benefits:

different revision IDs but same semantic versioning timestamp
prevents cross-dialect conflicts
enables dialect-specific optimizations

migration notes tracking#

MIGRATION-NOTES.md documents schema changes with parallel revision IDs:

# Add `deployment_version` table
SQLite: `bbca16f6f218`
Postgres: `06b7c293bc09`

# Add `labels` column to Flow, FlowRun, TaskRun, and Deployment
SQLite: `5952a5498b51`
Postgres: `68a44144428d`

creates natural merge conflicts if migrations diverge, keeping developers synchronized.

dialect-specific patterns#

PostgreSQL migrations#

uses native postgresql.ENUM() types
direct DDL operations (no batch mode needed)
supports postgresql_using="gin" for full-text search indexes
supports postgresql_include and postgresql_where clauses

example:

from sqlalchemy.dialects import postgresql

deployment_status = postgresql.ENUM("READY", "NOT_READY", name="deployment_status")
deployment_status.create(op.get_bind())
op.add_column("deployment", sa.Column("status", deployment_status, ...))

SQLite migrations#

batch mode required: render_as_batch=True (SQLite requires table recreation for ALTER)
PRAGMA foreign_keys management: disabled during migrations
transaction mode: uses SQLITE_BEGIN_MODE context variable set to "IMMEDIATE"
enum handling: uses sa.Enum() which SQLite converts to VARCHAR

example:

with op.batch_alter_table("deployment", schema=None) as batch_op:
    batch_op.add_column(
        sa.Column("status", sa.Enum("READY", "NOT_READY", name="deployment_status"), ...)
    )

auto-generation logic (env.py)#

include_object() function filters out:

dialect-specific indexes that don't apply
trigram/GIN indexes on SQLite
functional indexes (asc/desc variants)
enum column mismatches between reflection and ORM

transaction handling#

transaction_per_migration=True - each migration runs in its own transaction
SQLite disables foreign key constraints during migration
context variables manage SQLite transaction mode

thread safety#

ALEMBIC_LOCK prevents concurrent migration execution.

async support#

uses run_async_from_worker_thread() to run async migrations.