Skip to content

Migrations

Schema changes are managed with Alembic against PostgreSQL. The API service runs alembic upgrade head at startup; the pipeline relies on db/init.sql for the base events tables.

How Migrations Run

sequenceDiagram
  participant A as API (FastAPI)
  participant AL as Alembic
  participant PG as PostgreSQL
  A->>AL: alembic upgrade head (startup)
  AL->>PG: Apply pending revisions
  PG-->>AL: OK / version bumped
  AL-->>A: Continue startup

The docker-compose healthcheck waits for this to complete before the API accepts traffic.

Revision History

Revision Migration
0001 Initial pipeline tables (events, templates, …)
0002 Users & permissions
0003 Applications, incidents, alerts
0004 Sources & tasks
0005 Task assignee fields
0006 OLAP materialized views
0007 Search & indexes (GIN trigram)
0008 Fix OLAP views & indexes
0009 KPI performance indexes
0010 Events priority columns
0011 Templates priority columns
0012 Optimize events
0013 Partition events by range (see below)
0014 Composite PK (id, timestamp)
0015 Fix dedup guard
0016 AI insights → incidents FK
0017 Missing indexes

Migration 0013 — Partitioning

The most significant migration. Steps:

  1. Drop materialized views referencing events
  2. Rename events → events_old
  3. Create new events table PARTITION BY RANGE (timestamp)
  4. Create 13 monthly partitions in advance (autovacuum tuned per partition)
  5. Copy data from events_old, transfer sequence ownership, drop old table
  6. Recreate indexes (now inherited by partitions)
  7. Rebuild materialized views
# Creates events_2026_08 … events_2027_08
for i in range(13):
    start = today.replace(day=1) + timedelta(days=32 * i)
    start = start.replace(day=1)
    # PARTITION OF events FOR VALUES FROM (start) TO (end)

Per-partition autovacuum is tuned to aggressive thresholds since event tables grow fast.

Downgrade Policy

Downgrades are best-effort and mostly used in dev. Production rollbacks are avoided — the recommended path is forward-fix with a new revision.

Adding a Migration

# From backend/
alembic revision -m "add_column_x" --autogenerate
alembic upgrade head

Guidelines:

  • One logical change per revision
  • Keep materialized views in sync (drop → modify → recreate)
  • Never rename columns without a shim for the ORM
  • Test upgrade + downgrade in a scratch DB