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.sqlEvery 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 TABLEstatements that would fail with “duplicate column name” are tolerated (matches SQLite semantics forADD COLUMN IF NOT EXISTSwhich 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=[]