Database & migrations

One OBS_STORAGE, one database

Every table cairn owns lives in a single database selected by OBS_STORAGE — runs and events, artifacts and dataset versions, judges, reviews, pipelines, policies, profiles, connections, accounts, workspaces, the eval ledger, and the rest. Roughly 47 tables, one place.

OBS_STORAGE="sqlite:///$HOME/.cairn/cairn.db"          # local: one file
OBS_STORAGE="postgres://cairn@localhost:5432/cairn"    # shared: one database

Use Postgres as soon as more than one process touches the data (the server plus a Temporal worker, say). SQLite is single-writer and perfect for one developer on one machine.

Both absolute spellings work

sqlite:///abs/path and SQLAlchemy’s sqlite:////abs/path resolve to the same file. A path with no leading slash is relative to the working directory.

What lives beside it, and why

Some tables are owned by libraries, not by cairn, and they belong in their own databases — same instance is fine, same database is not:

Data	Where	Managed by
cairn’s ~47 tables	the OBS_STORAGE database	cairn
LangGraph checkpoints	OBS_CHECKPOINTER_DSN / its own SQLite file	LangGraph’s saver
Vector embeddings	PGVECTOR_DSN / OBS_SQLITE_VEC_DB	the vector store (tables are created per corpus, with dynamic names)
Workflow history	Temporal’s own database	Temporal
Experiments & models	MLFLOW_BACKEND_STORE_URI	MLflow
Gateway spend & keys	LiteLLM’s own store	LiteLLM

Keeping them separate means each tool migrates its own schema on its own schedule, and cairn’s migrations never have to reason about tables it doesn’t own.

How schema is created today

Each store issues its own CREATE TABLE IF NOT EXISTS the first time it is used. A fresh database therefore starts empty and grows as features are exercised — seeding demo fixtures creates a handful of tables, not all 47.

There is no migrate command to run. For fresh installs and for adding new tables this works fine and needs no ceremony.

The limitation, stated plainly

CREATE TABLE IF NOT EXISTS does nothing when the table already exists with an older shape. It cannot add a column, change a key, rewrite an index, or backfill data. Adding a new table is safe; changing an existing one is not covered.

Today that means a column change against a database with real data is a hand-run SQL statement — exactly the kind of ungoverned mutation cairn exists to eliminate everywhere else.

What’s coming

A migration chain is being added (tracked in the repo as the schema-migration work) with these decisions already settled:

• Alembic, already present in the dependency tree — no new dependency, and the industry-standard tool for this.
• One chain for both tiers. env.py resolves OBS_STORAGE, so the same migrations run against local SQLite and production Postgres. Not a Postgres-only mechanism.
• Raw SQL migrations (op.execute), dialect-branched where the two databases differ. No ORM is introduced; the existing raw-driver stores are untouched.
• An empty baseline. Existing databases are stamped, not rebuilt — nothing is touched on first upgrade.
• Autogenerate is never enabled. It would try to “reconcile” tables cairn doesn’t own; migrations stay hand-written.
• upgrade head at startup, with an env gate for operators who prefer to migrate by hand.
• A CI check that a database built by the bootstrap DDL and one built by the migration chain are identical, so the two paths can’t drift.

Until it lands, treat the schema as create-only: new tables are fine, and any change to an existing table needs a deliberate plan.

Backups

The compose stack ships a backup profile that runs a nightly pg_dump. For SQLite, the database is one file — copy it while the server is stopped, or use sqlite3 .backup for a consistent online copy.