Skip to content

Database Migrations ​

MUST READ before creating any Alembic migration.

This guide defines the exact conventions for creating database migrations in Hegemony. AI agents and developers must follow these patterns precisely.

Quick Reference ​

AspectConvention
File namingYYYYMMDD_NNN_description.py (e.g., 20260204_024_add_jobs.py)
Revision IDNNN_short_description (e.g., 024_add_jobs)
down_revisionPrevious migration's revision ID (e.g., 023_remove_timezone)
Locationapps/api/alembic/versions/

Baseline: the migration chain of the public 1.0.0 starts at a single revision, 001_baseline (file 20261022_001_baseline.py). Versions before 1.0.0 were internal and never published, and upgrading a database from them is not supported. The migrate command recognizes one pre-1.0.0 revision, 085_rename_interface_graph_to_form (PRE_BASELINE_HEAD in apps/api/migrate.py): it records the baseline for a database at that revision and refuses any other. Numbering continues at 002_*; find the current head as described below. The baseline is never edited: a schema change is a new revision on top of it.

Before Creating a Migration ​

1. Find the Latest Migration ​

bash
ls -la apps/api/alembic/versions/ | tail -5

Note the highest NNN number and its revision ID. Your new migration:

  • Uses NNN + 1 for the sequence number
  • Sets down_revision to the previous migration's revision ID

2. Verify the Revision Chain ​

bash
task db:history

Ensure no gaps or duplicates in the chain.


Migration File Template ​

Copy this exact structure. Replace placeholders in <angle_brackets>.

python
"""<Short description of what this migration does>.

Revision ID: <NNN>_<short_description>
Revises: <previous_revision_id>
Create Date: <YYYY-MM-DD HH:MM:SS.ffffff>

Adds:
- <table/column/index 1>
- <table/column/index 2>
"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "<NNN>_<short_description>"
down_revision: str | None = "<previous_revision_id>"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # === Tables ===

    # === Columns ===

    # === Indexes ===

    # === Constraints ===
    pass


def downgrade() -> None:
    # Reverse order of upgrade operations
    pass

Naming Conventions ​

File Name ​

text
YYYYMMDD_NNN_description.py
│        │   └── Snake_case, max 40 chars
│        └── Three-digit sequence, zero-padded (001, 024, 100)
└── Current date in UTC

Naming guidance:

  • Omit add_ for new tables: scheduled_jobs not add_scheduled_jobs
  • Use add_ when adding columns/enums to existing tables: add_device_status_enum
  • Use verbs for other operations: rename_foo_to_bar, drop_legacy_columns

Examples:

  • ✅ 20260204_024_scheduled_jobs.py (new table)
  • ✅ 20260204_025_add_device_status_enum.py (adding enum to existing table)
  • ❌ 024_scheduled_jobs.py (missing date)
  • ❌ 20260204_24_jobs.py (not zero-padded)

Revision ID ​

text
NNN_short_description
│   └── Matches file description, underscores
└── Same three-digit number as filename

Examples:

  • ✅ 024_scheduled_jobs
  • ✅ 025_add_device_status_enum
  • ❌ 20260204_024_scheduled_jobs (don't include date in revision ID)
  • ❌ scheduled_jobs (missing sequence number)

Common Patterns ​

Creating a Table ​

python
def upgrade() -> None:
    op.create_table(
        "scheduled_jobs",
        sa.Column("id", sa.UUID(), primary_key=True),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column("flow_id", sa.UUID(), sa.ForeignKey("flows.id", ondelete="CASCADE"), nullable=False),
        sa.Column("cron_expression", sa.String(100), nullable=False),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    # Always create indexes for foreign keys
    op.create_index("ix_scheduled_jobs_flow_id", "scheduled_jobs", ["flow_id"])


def downgrade() -> None:
    op.drop_index("ix_scheduled_jobs_flow_id", table_name="scheduled_jobs")
    op.drop_table("scheduled_jobs")

Adding a Column ​

python
def upgrade() -> None:
    op.add_column(
        "flows",
        sa.Column("schedule_id", sa.UUID(), sa.ForeignKey("scheduled_jobs.id"), nullable=True),
    )


def downgrade() -> None:
    op.drop_column("flows", "schedule_id")

Creating an Enum Type (PostgreSQL) ​

python
def upgrade() -> None:
    # Use EXECUTE for enum creation - allows IF NOT EXISTS
    op.execute("""
        DO $$
        BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'jobstatus') THEN
                CREATE TYPE jobstatus AS ENUM ('pending', 'running', 'completed', 'failed');
            END IF;
        END
        $$;
    """)

    op.add_column(
        "scheduled_jobs",
        sa.Column("status", sa.Enum("pending", "running", "completed", "failed", name="jobstatus"), nullable=False),
    )


def downgrade() -> None:
    op.drop_column("scheduled_jobs", "status")
    op.execute("DROP TYPE IF EXISTS jobstatus")

Caveat — enums declared inline in op.create_table. When a new table's column uses sa.Enum("a", "b", name="foo"), Postgres auto-creates the foo type as a side effect of the CREATE TABLE, but op.drop_table does not drop it. Downgrade must explicitly op.execute("DROP TYPE IF EXISTS foo CASCADE") for every such enum, or a subsequent upgrade head will fail with DuplicateObjectError: type "foo" already exists.

Adding Values to Existing Enum ​

python
def upgrade() -> None:
    # PostgreSQL: Add enum value (cannot be in transaction, use raw connection)
    op.execute("ALTER TYPE runstatus ADD VALUE IF NOT EXISTS 'scheduled'")


def downgrade() -> None:
    # Note: PostgreSQL doesn't support removing enum values easily
    # Document this limitation in the docstring
    pass

Creating Indexes ​

python
def upgrade() -> None:
    # Naming convention: ix_<table>_<column(s)>
    op.create_index("ix_runs_flow_id", "runs", ["flow_id"])
    op.create_index("ix_runs_status_created", "runs", ["status", "created_at"])  # Composite
    op.create_index("ix_runs_metadata", "runs", ["metadata"], postgresql_using="gin")  # GIN for JSONB


def downgrade() -> None:
    op.drop_index("ix_runs_metadata", table_name="runs")
    op.drop_index("ix_runs_status_created", table_name="runs")
    op.drop_index("ix_runs_flow_id", table_name="runs")

Foreign Key Constraints ​

python
def upgrade() -> None:
    # Naming convention: fk_<table>_<column>_<referenced_table>
    op.create_foreign_key(
        "fk_runs_schedule_id_scheduled_jobs",
        "runs",
        "scheduled_jobs",
        ["schedule_id"],
        ["id"],
        ondelete="SET NULL",
    )


def downgrade() -> None:
    op.drop_constraint("fk_runs_schedule_id_scheduled_jobs", "runs", type_="foreignkey")

Circular Foreign Keys (use_alter=True) ​

When two tables reference each other (e.g. platform_sync_profiles.last_run_id → platform_sync_runs.id and platform_sync_runs.profile_id → platform_sync_profiles.id), the cycle prevents SQLAlchemy from topologically sorting Base.metadata.sorted_tables and Alembic falls back to alphabetical ordering, producing migrations whose CREATE TABLE statements fail.

ORM side — declare the FK on the "breaking" side with use_alter=True and a stable name:

python
last_run_id: Mapped[uuid.UUID | None] = mapped_column(
    UUID(as_uuid=True),
    ForeignKey(
        "platform_sync_runs.id",
        ondelete="SET NULL",
        use_alter=True,
        name="fk_platform_sync_profiles_last_run_id",
    ),
    nullable=True,
)

Verify the cycle is broken before regenerating autogen:

python
python -c "from apps.api import models; from apps.api.db import Base; \
  print([t.name for t in Base.metadata.sorted_tables])"

No SAWarning: Cannot correctly sort tables should appear.

Migration side — autogenerate inlines the ForeignKeyConstraint(..., use_alter=True) into op.create_table, but Postgres silently drops it because the referenced table does not yet exist. You must add an explicit op.create_foreign_key after both tables are created, and a matching op.drop_constraint at the top of downgrade() (before any drop_table):

python
def upgrade() -> None:
    op.create_table("platform_sync_profiles", ...)  # FK omitted by Postgres
    op.create_table("platform_sync_runs", ...)
    op.create_foreign_key(
        "fk_platform_sync_profiles_last_run_id",
        "platform_sync_profiles",
        "platform_sync_runs",
        ["last_run_id"],
        ["id"],
        ondelete="SET NULL",
    )


def downgrade() -> None:
    op.drop_constraint(
        "fk_platform_sync_profiles_last_run_id",
        "platform_sync_profiles",
        type_="foreignkey",
    )
    op.drop_table("platform_sync_runs")
    op.drop_table("platform_sync_profiles")

Always confirm with SELECT count(*) FROM pg_constraint WHERE contype='f' before and after upgrade that the count matches the number of FKs in the ORM.


Autogenerate and extensions' tables ​

task db:revision compares the database with the platform's models (Base.metadata) only. Extensions keep tables of their own in the same database, made by their own migrations (the registry's registry_*; see Tables of its own). The models do not have them, so autogenerate would propose dropping them.

apps/api/alembic/env.py passes platform_autogenerate_includes (apps/api/extension_migrations.py) to Alembic as include_object. A table the models do not have is left out of the revision when it is:

  • an extension's version table, alembic_version_<prefix>;
  • named <prefix>_…, for the prefix of an installed or refused extension, or of one that a version table in the database names. So an extension that is not installed where autogenerate runs keeps its tables too;
  • image_transfers. The platform's baseline revision still makes it, for the registry to take over. The registry's first revision renames it to registry_image_transfers, rows included, and its downgrade to base gives it back. Until that revision runs, and after that downgrade, the table holds the registry's rows.

Such a table's indexes and constraints stay out with it. The prefix must match up to the _: registryx_items is not the registry's.

  • Any other table the models no longer have is still proposed for dropping. Read the revision before you commit it.
  • When an extension takes over another platform table, add the table's name to _KEPT_FOR_EXTENSIONS in apps/api/extension_migrations.py.
  • A platform table whose name starts with an extension's prefix (registry_credentials) is compared as usual while it is in the models. Once it leaves them, autogenerate does not propose dropping it: write the op.drop_table yourself.

Keeping the models in step with the migrations ​

On a database at the newest revision, task db:revision proposes nothing. Any operation it does propose is a difference between the models and the migrations. Every later revision would carry it, and one left in by mistake drops a real index or makes a duplicate one. So whatever a migration makes or removes, the model declares or loses too: an index a migration creates with op.create_index or raw SQL is declared on the model, under the same name.

tests/db/test_autogenerate_drift_postgresql.py runs autogenerate through apps/api/alembic/env.py on the migrated database of the CI job's PostgreSQL tests, and fails on any operation it proposes, printed as the revision would carry it. To run it locally, point HEGEMONY_TEST_DATABASE_URL at a database that python -m apps.api.migrate brought to the newest revision.

Autogenerate matches indexes by name and compares their uniqueness and their expressions, in order and with DESC. It does not compare the method (postgresql_using), the operator class (postgresql_ops) or the WHERE (postgresql_where), so declare those exactly as the migration makes them. A unique constraint has its own index: an Index on the same columns only duplicates it.


Pre-Flight Checklist ​

Before committing a migration, verify:

  • [ ] File name follows YYYYMMDD_NNN_description.py
  • [ ] Revision ID follows NNN_short_description (matches file)
  • [ ] down_revision points to the correct previous migration
  • [ ] Type annotations present: revision: str = ..., down_revision: str | None = ...
  • [ ] Docstring includes Revision ID, Revises, Create Date
  • [ ] upgrade() and downgrade() are both implemented
  • [ ] downgrade() reverses operations in correct order
  • [ ] Indexes created for all foreign key columns
  • [ ] Naming conventions followed for indexes (ix_), constraints (fk_, uq_)
  • [ ] Models match: every index, constraint and column the migration makes is declared on the model, so autogenerate proposes nothing (see Keeping the models in step)
  • [ ] Audit rows untouched, or the migration runs SET LOCAL hegemony.audit_maintenance = 'on' first: audit_logs is append-only at the database level (created by the baseline), and any UPDATE or DELETE on it is refused otherwise. Revisions may name that setting; application code may not, apart from the retention path in apps/api/audit.py (tests/db/test_migration_baseline.py scans everything else under apps/)
  • [ ] Chain still equals the models: tests/db/test_migration_baseline_postgresql.py passes (see below)

The chain must equal the models ​

tests/db/test_migration_baseline_postgresql.py upgrades an empty PostgreSQL database with the whole chain and compares it with Base.metadata through Alembic's autogenerate comparison; the diff must be empty. It runs against the server HEGEMONY_TEST_DATABASE_URL names (it creates a database of its own there and drops it afterwards):

bash
HEGEMONY_TEST_DATABASE_URL=postgresql+asyncpg://user:password@localhost/postgres \
  uv run pytest tests/db/test_migration_baseline_postgresql.py -p no:cacheprovider

Three tests of the file compare the chain with the models:

  • Alembic's comparison must give an empty diff. It sees tables, columns, column types, nullability, foreign keys, unique constraints, indexes (their columns, order and uniqueness) and the server defaults a model declares. A revision that changes one of those without the models (or the other way round) fails it.
  • CHECK constraints and the predicates of partial indexes, which Alembic does not compare, are compared with the models through PostgreSQL, both ways: a CHECK constraint that no model names fails, and so does a predicate on an index that the model declares without one.
  • The labels of every PostgreSQL enum type must equal those of the model's enum (as a set; the order ALTER TYPE ... ADD VALUE gave them is not compared). A new enum member in a model needs its ADD VALUE revision, and the other way round.

Two objects no model declares are left out of the comparison by name, with the reason beside the list, and the file's other tests assert that they exist: the view v_config_exchange_operations and image_transfers, the table the registry extension renames. The keyset and tag indexes are declared on the models, so the comparison covers their columns and order, and a test asserts the rest of their definition: access method, and no predicate, uniqueness or storage parameters. The audit trigger is asserted to fire BEFORE UPDATE OR DELETE FOR EACH ROW and to refuse both. A revision that adds an object no model can declare creates it and names it in the same place.

What the tests do not compare needs an assertion of its own in the revision that changes it: primary keys, the access method of an index a model declares, server defaults that no model declares (the comparison skips those columns on purpose), triggers and functions other than the audit log's, and the definition of the view (the test only selects from it).

Local Verification Commands ​

bash
# Apply migration
task db:migrate

# Verify it applied
task db:current

# Test downgrade (CAUTION: destroys data)
task db:downgrade

# Re-apply
task db:migrate

From the baseline, task db:downgrade drops every platform table. With the registry extension applied (the compose migrate service applies it), take its tables back first: task db:downgrade:extension -- registry base. Otherwise the baseline's drop_table("image_transfers") fails, because the extension renamed that table to registry_image_transfers; the downgrade then stops with nothing changed.


Troubleshooting ​

"Target database is not up to date" ​

The database has pending migrations. Run:

bash
task db:migrate

"Can't locate revision" ​

Either your down_revision points to a non-existent revision (check with task db:history), or the database is at a revision the chain no longer has.

The second is what a development database from before the 1.0.0 baseline shows: it is still at 085_rename_interface_graph_to_form, and the Taskfile's Alembic targets that read alembic_version (task db:current, task db:revision, task db:downgrade) fail with Can't locate revision identified by '085_rename_interface_graph_to_form'. Only python -m apps.api.migrate knows that revision. Run task db:migrate once, which records 001_baseline for the database, and retry. (task db:history and task db:heads do not read the database and keep working.)

"Revision already exists" ​

Duplicate revision ID. Change your NNN to the next available number.

The migrate command refuses the database ​

python -m apps.api.migrate (what task db:migrate and the compose migrate service run) reads the revision the database is at before it changes anything. A database at a revision the chain does not have is refused: the command exits with status 1, says the database is at revision '...', which this version of Hegemony does not have, and changes nothing. Such a database was created by a newer version, or before 1.0.0. The remedies:

  • an empty database, which the baseline is applied to;
  • the version of Hegemony that matches the database, for one made by a newer version;
  • none for a database from before 1.0.0: those versions were internal, and upgrading from them is not supported. The only pre-1.0.0 revision the command accepts is 085_rename_interface_graph_to_form, for which it records 001_baseline and carries on.

Multiple heads (branched history) ​

Two migrations share the same down_revision. They must be merged:

bash
task db:heads  # Lists current head revision(s); multiple entries indicate a branch

Use task db:heads to inspect current heads, create the merge revision using your standard migration workflow, then run task db:heads again to confirm a single head.

Then apply the resulting merge migration:

bash
task db:migrate

What NOT to Do ​

❌ Don't✅ Do Instead
Use timestamps in revision IDUse NNN_description format
Skip downgrade implementationAlways implement downgrade
Hardcode UUIDs in migrationsUse sa.text("gen_random_uuid()") for defaults
Create migration without testing locallyAlways run task db:migrate first
Use op.execute() for simple operationsUse typed Alembic operations
Forget indexes on foreign keysCreate index for every FK column

Released as open source under the AGPL-3.0-or-later license. Development is sponsored by Rexonix s.r.o. Contact — [email protected].