Skip to main content

ADR-0006: Re-adopt Alembic for schema migrations

Status: Accepted Date: 2026-04-20 Supersedes (in part): ADR-0002 — Dependency / license policy §Consequences

Context​

In ADR-0002 (Accepted 2026-02-06) we kept a reflective auto-migrator that ran ALTER TABLE at startup based on SQLModel introspection (chaoscypher_core.adapters.sqlite.engine.apply_schema_updates). Alembic was temporarily removed from dependencies in favour of this approach (noted in ADR-0002 on 2026-04-18).

As the schema grew, three problems surfaced:

  1. Drift hazards — branch merges with conflicting model changes left the running database in an undefined intermediate state.
  2. No rollback — once the auto-migrator applied an ALTER, there was no recorded operation to reverse it.
  3. Unsafe constraint changes — adding NOT NULL, UNIQUE, foreign key, or rebuild-table changes with the reflective migrator silently failed when existing rows didn't qualify, or carried stale constraints forward through upgrades undetected.

Decision​

Re-adopt Alembic as the canonical schema migration framework, effective 2026-04-20.

  • All schema changes ship as Alembic revision files under packages/core/src/chaoscypher_core/database/migrations/versions/.
  • Cortex runs alembic upgrade head on startup before serving any request.
  • The reflective auto-migrator (apply_schema_updates) is retained only for two responsibilities:
    • Idempotent data backfills that do not change schema.
    • The constraint-drift logger that warns when models and the live schema disagree.

Consequences​

Positive​

  • Reviewable migrations; reversible operations; tested before merge.
  • Clear upgrade/downgrade story for operators.
  • Constraint, FK, and rebuild-table changes are now expressible and safe.

Negative​

  • Adds an Alembic dependency (re-added after the brief April 2026 removal).
  • Contributors must learn alembic revision --autogenerate -m "...".
  • A migration step now sits in the Cortex startup path.

Neutral​

  • Existing data backfill code in apply_schema_updates continues to run after alembic upgrade head for now; over time, those backfills migrate into Alembic data-migration revisions and the function shrinks to the constraint-drift logger only.
  • An initial revision baselines the existing schema (no DDL changes; just records the starting point in alembic_version). All future model changes require a new revision.

Implementation notes​

  • See internal/procedures/add-migration.md for the contributor workflow.
  • The initial baseline revision carries no DDL — it exists only to establish the alembic_version table so subsequent revisions have a known starting point.