# 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:
```python
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:
```python
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.