ADR-009: Versioned Database Migration Framework¶

Status: Accepted Date: 2026-07-04 Deciders: Kamerplanter Development Team

Context¶

Database seeds and one-off data migrations used to live as a loose collection of functions in app/migrations/, called individually, hard-coded, and unordered during application startup (app/main.py) — roughly 18 calls in sequence, all of which re-ran on every start. This left several gaps:

• No tracking of which migration had already run against a given database
• No error isolation — a single failing migration took down the entire startup
• No declared order — execution order lived implicitly in the call sequence
• No shared conventions for report format and --dry-run
• No versioning/rollback concept to show which database state is currently applied

These gaps caused two consecutive backend startup crashes on 2026-07-04: first on an enum value (harvest) retired since Issue #306, for which no data migration existed; then on a blocked Home Assistant endpoint. In both cases a single step took down the whole startup.

Constraints: ArangoDB, the primary database, is schemaless and AQL-based (ADR-001) — an Alembic-/SQL-style migration framework does not fit. The existing pattern of "idempotent YAML seed jobs on every startup" (docs/adr/005, YAML-based Seed Jobs at Startup) remains valid and is cleanly separated from one-off migrations, not replaced. Multiple backend replicas in production must not trigger duplicate or concurrent migration execution.

Decision¶

We introduce a versioned, tracked migration framework and draw a clear conceptual line between migrations (one-off, versioned) and seeds (idempotent, always running):

Core building blocks:

• Tracking collection schema_migrations — one document per applied version, holding the version number, name, a checksum of the up() source, timestamp, and duration. If the checksum of an already-applied migration drifts, a warning is logged — applied migrations are treated as immutable; corrections ship as a new migration.
• Concurrency lock — prevents multiple backend replicas starting at the same time from applying the same migration twice. The same lock also serialises the seed registry: one replica seeds while the others wait. If another replica completed a full run of the same seed inputs in the meantime, the waiting replica does not seed again. If the bounded wait runs out, startup fails instead of silently skipping the seeds.
• Strictly linear version history — migrations are sequentially numbered (v0001, v0002, …); gaps before the current head are an error, and out-of-order application is not allowed.
• Migration protocol with up()/down(): rollback is supported where it makes sense; non-reversible data transformations declare that honestly (reversible = False) instead of faking a reversal.
• Baseline migration v0001 marks the database state prior to the framework's introduction; the five existing migrations were wrapped as v0002–v0006.
• Startup order: all pending migrations run first (fatal on error), then the seed registry (isolated, non-fatal for reference data).

Database migrations — CLI reference¶

A CLI entry point exists for controlled operational use and for scaffolding new migrations:

python -m app.migrations upgrade      # apply all pending migrations
python -m app.migrations downgrade    # roll back to a target version (reversible migrations only)
python -m app.migrations current      # show the highest applied version
python -m app.migrations history      # list all applied migrations with timestamps
python -m app.migrations create <slug> # scaffold a new vNNNN_<slug>.py from a template

upgrade and downgrade support --dry-run to compute the plan without writing anything — a prerequisite for controlled production execution. This CLI does not change the public HTTP API; it is a pure ops/developer tool.

Migrations currently still run inside the FastAPI lifespan on startup (protected by the concurrency lock). The medium-term target is to run them instead as a dedicated Kubernetes Job / Helm pre-upgrade hook, so app replicas never migrate themselves — the CLI entry point is already the foundation for that. As of 2026-10-10 (issue #2125): the operator decided to keep migrations and seeds in the lifespan; instead of a job, a startupProbe gives the start up to 900 s, documented under Backend start-up time.

Rejected alternatives¶

Consequences¶

Positive¶

• A failing reference-data seed no longer takes down the startup.
• One-off migrations run exactly once, tracked, and in a defined order.
• The database state is inspectable at any time via current/history.
• Replica races on parallel pod startup are excluded by the lock — for migrations and seeds.
• New migrations follow a single, tested pattern (create <slug> scaffolding).

Negative¶

• Additional framework complexity and a new schema_migrations collection.
• Migration authors must follow the versioning and idempotency rules.
• Most data transformations remain effectively irreversible — rollback is not a cure-all, but an honestly declared exception.

Follow-ups¶

• Move migrations into a dedicated Kubernetes Job/Helm hook rather than running them in the app pod lifespan, as a medium-term step.
• Any removal or renaming of a persisted enum value or field must ship a data migration in the same change (a direct lesson from Issue #306).

References¶

• Canonical decision: spec/decisions/ADR-005-versioned-migration-framework.md (note: this uses its own numbering under spec/decisions/, independent of the docs/adr/ count here — this page is the published version of Canonical ADR-005, under the next free number, 009)
• Normative requirement: spec/nfr/NFR-016_Datenbank-Migrationsstrategie.md
• ADR-001 — ArangoDB as a schemaless primary database
• docs/adr/005 — YAML-based Seed Jobs at Startup (the separate seed pattern)
• Issue #306 — retirement of the harvest phase (triggering incident)