SQLite Migrations#

Added in v0.5. Replaces the ad-hoc CREATE TABLE IF NOT EXISTS + one-off ALTER TABLE boot logic with a proper file-based migration runner.

Design goal: match the codebase’s async raw-SQL idiom without adding SQLAlchemy. The runner is ~130 lines and has no extra runtime deps.

Where migrations live#

infraguard/tracking/migrations/
├── __init__.py
├── 0001_baseline.sql            # baseline schema (was inline SCHEMA_SQL)
└── 0002_legacy_alter_backfill.sql

Every file matches NNNN_<slug>.sql and is applied once, in numeric order. Each applied migration is recorded in the schema_migrations table with (version, name, sha256, applied_at).

Adding a migration#

Create a new file with the next number and drop your SQL in it:

-- 0003_add_beacon_labels.sql
CREATE TABLE IF NOT EXISTS beacon_labels (
    beacon_id TEXT PRIMARY KEY,
    campaign  TEXT,
    target    TEXT,
    created_at TEXT NOT NULL
);

Restart the proxy. The runner detects the new file, applies it, and records the version.

Guarantees#

  • Multiple statements per file are supported (split on ; outside quoted strings).
  • ALTER TABLE statements that would fail with “duplicate column name” are tolerated (matches SQLite semantics for ADD COLUMN IF NOT EXISTS which the dialect does not implement).
  • An applied migration’s file cannot be edited: a checksum mismatch on startup aborts the boot with a clear error. Roll forward with a new file instead of editing history.
  • Migrations are idempotent when re-run against a DB where they already applied.

Current version#

Query at runtime:

from infraguard.tracking.migrator import current_version

async with db.conn:
    v = await current_version(db.conn)
    print(f"schema version: {v}")

The proxy logs the version at every boot:

2026-09-24 [info] database_connected  path=/app/data/infraguard.db  schema_version=2  newly_applied=[]