Database Schema (Local SQLite)

Last updated: 2026-07-14

Status: Reference
Surface: Shared — Desktop (Tauri v2, tauri-plugin-sql/SQLCipher), CLI (better-sqlite3, ADR-0021/ADR-0050), and VS Code (a wasm SQLite build). One schema, three drivers — see Concurrency & transaction behavior and Encryption at rest for the per-surface divergences.
Scope: Phase 1, local-first. Schema managed by Drizzle ORM in packages/db (see project-structure.md).
Related: desktop/keychain-and-secrets.md, desktop/tauri-plugins.md, ../contracts/workflow-yaml-spec.md, ../contracts/sse-event-schema.md, ../../architecture/cloud-phase-2.md, ../../architecture/managed-inference.md, ../../architecture/local-first-and-security.md

This is the canonical reference for the local run-history and catalog database that the desktop app (and the Phase-2 CLI) persists on the user's machine. There is no cloud, no account, and no server in Phase 1 — every table below lives in a single local SQLite file. At-rest encryption is per-surface: the desktop opens history.db with SQLCipher, while the Phase-2 CLI opens the same path with better-sqlite3 unencrypted, guarded by 0600/0700 OS permissions (ADR-0050). Because a standard better-sqlite3 build cannot open a SQLCipher file (nor vice-versa), the two surfaces cannot share one file — see Encryption at rest and the cross-host callout under the agent-session tables. The Phase-2 PostgreSQL divergences are described at the end and detailed in ../../architecture/cloud-phase-2.md.

Storage layout

flowchart TB
  subgraph Global["~/.relavium/ (global, cross-project)"]
    H["history.db<br/>(desktop: SQLCipher · CLI: 0600/0700 OS perms)<br/>full run + event + cost history"]
  end
  subgraph Project["{projectRoot}/.relavium/ (per-project, git-committed)"]
    R["runs.db<br/>(run metadata only,<br/>no event payloads)"]
    WF[".relavium/*.relavium.yaml<br/>workflows (source of truth)"]
    AG[".relavium/agents/*.agent.yaml<br/>agents (source of truth)"]
  end
  WF -. parsed at run time .-> H
  AG -. parsed at run time .-> H
  H -. metadata mirror .-> R

Two SQLite databases exist:

Database	Path	Encryption	Contents	Git
Global history	`~/.relavium/history.db`	Desktop: SQLCipher (key from OS keychain) · CLI: none — `0600`/`0700` OS perms (ADR-0050)	Full runs, every event, every cost row, the catalog tables	Never committed
Project history	`{projectRoot}/.relavium/runs.db`	None	Run metadata only (no event payloads) so teammates see historical run summaries after a `git pull`	Committed

The database is opened with PRAGMA journal_mode = WAL (readers never block the writer and vice-versa — but SQLite still allows only one writer at a time, so engine authors must funnel run_events and other hot-path writes through a single serialized writer, never concurrent writers) and PRAGMA foreign_keys = ON per connection (SQLite does not enforce foreign keys by default). Run events in history.db are pruned after 90 days by a background job that runs on app launch.

Workflows and agents are not the database's source of truth. The git-committable YAML files (.relavium.yaml / .agent.yaml) are authoritative; see ../contracts/workflow-yaml-spec.md and ../contracts/agent-yaml-spec.md. The catalog tables below cache and snapshot them for fast querying, run reproducibility, and offline browsing.

SQLite type conventions

Because this schema is adapted from a Postgres-first design (see ../../analysis/_archive/), the following local-first conventions apply consistently across every table:

Concept	Postgres (Phase 2)	SQLite (Phase 1, here)
Primary key	`UUID DEFAULT gen_random_uuid()`	`TEXT` UUID generated in application code (Drizzle)
Structured blob	`JSONB`	`TEXT` (JSON string); query with `json_extract()`
String array (tags)	`TEXT[]`	`TEXT` (JSON array, e.g. `["review","ci"]`); query with `json_each()`
Timestamp	`TIMESTAMPTZ`	`INTEGER` (Unix epoch ms) for reliable ordering; timezone handled in app code
Money / cost	`NUMERIC(14,8)`	`INTEGER` micro-cents (USD x 100,000,000, i.e. cents x 1,000,000 — one micro-cent = 1e-8 USD = 1e-6 cent) to avoid IEEE-754 rounding. The `NUMERIC(14,8)` Postgres form is consistent: 8 fractional digits = 1e-8 USD = one micro-cent. Canonical unit definition: ../shared-core/llm-provider-seam.md.
Enum	`CREATE TYPE ... AS ENUM`	`TEXT` with a `CHECK` constraint
Soft delete	`deleted_at TIMESTAMPTZ` partial index	`deleted_at INTEGER NULL`; partial indexes supported since SQLite 3.8.9

A full 14-item porting table lives in ../../architecture/cloud-phase-2.md.

Tables

The local schema is the Postgres 13-table design reduced to what a single-user, local-first app needs. The two LangGraph checkpoint tables are dropped (the engine is pure TypeScript — no LangGraph; see decision 0003); checkpoint/resume needs no dedicated table — engine state is reconstructed from step_executions + run_events (+ messages for an orchestrator's history), per execution-model.md. workflow_schedules is Phase 2 only (schedule/webhook triggers require a cloud listener; see ../../ideas/scheduled-and-webhook-triggers.md). The *_versions tables are unnecessary locally because version history is provided by git on the YAML files.

Entity relationships

The 16 tables below, trimmed to the columns that define structure (full column lists follow per table). Every edge is a real foreign key in schema.ts; two are deliberately absent — see the note beneath the diagram. model_metadata and catalog_meta (ADR-0072) are FK-less leaves (no edges): model_metadata is keyed by model_id, not the model_catalog UUID, on purpose.

erDiagram
    llm_providers {
        uuid id PK
        text name
        text display_name
        text kind
    }
    model_catalog {
        uuid id PK
        uuid provider_id FK
        text model_id
        text display_name
        text source
    }
    agents {
        uuid id PK
        uuid model_id FK
        text slug UK
        text name
    }
    workflows {
        uuid id PK
        text slug UK
        text name
    }
    runs {
        uuid id PK
        uuid workflow_id FK
        text status
        text execution_mode
    }
    step_executions {
        uuid id PK
        uuid run_id FK
        uuid agent_id FK
        uuid model_id FK
        text node_id
        text status
    }
    messages {
        uuid id PK
        uuid step_execution_id FK
        text run_id "denormalized, no FK"
        int sequence_number
        text role
    }
    run_events {
        uuid id PK
        uuid run_id FK
        text step_execution_id "denormalized, no FK"
        int seq
        text event_type
    }
    run_costs {
        uuid id PK
        uuid run_id FK
        uuid model_id FK
        text node_id
    }
    agent_sessions {
        uuid id PK
        uuid agent_id FK
        uuid model_id FK
        text status
        text title
    }
    session_messages {
        uuid id PK
        uuid session_id FK
        uuid model_id FK
        int sequence_number
        text role
    }
    session_costs {
        uuid id PK
        uuid session_id FK
        uuid model_catalog_id FK
        text model "raw string, deliberately not the FK key"
        bool is_legacy
    }
    media_objects {
        uuid id PK
        text handle UK
        text modality
        int byte_length
    }
    media_references {
        uuid id PK
        text handle FK
        text scope_kind
        text scope_id
    }
    model_metadata {
        text model_id PK "the CATALOG_SNAPSHOT key, NOT a UUID"
        text provider "coerced, no FK"
        text origin "shipped | refreshed"
        int catalog_schema_version
    }
    catalog_meta {
        int id PK "singleton, id = 1"
        text seeded_snapshot_sha
        int catalog_checked_at
    }

    llm_providers ||--o{ model_catalog : provider_id
    model_catalog ||--o{ agents : model_id
    model_catalog |o--o{ step_executions : "model_id (nullable)"
    model_catalog |o--o{ run_costs : "model_id (nullable)"
    model_catalog |o--o{ agent_sessions : "model_id (nullable)"
    model_catalog |o--o{ session_messages : "model_id (nullable)"
    model_catalog |o--o{ session_costs : "model_catalog_id (nullable)"
    workflows ||--o{ runs : workflow_id
    runs ||--o{ step_executions : "run_id CASCADE"
    runs ||--o{ run_events : "run_id CASCADE"
    runs ||--o{ run_costs : "run_id CASCADE"
    step_executions ||--o{ messages : "step_execution_id CASCADE"
    agents |o--o{ step_executions : "agent_id (nullable)"
    agents |o--o{ agent_sessions : "agent_id (nullable)"
    agent_sessions ||--o{ session_messages : "session_id CASCADE"
    agent_sessions ||--o{ session_costs : "session_id CASCADE"
    media_objects ||--o{ media_references : "handle CASCADE"

Two edges are deliberately missing above. messages.run_id and run_events.step_execution_id are denormalized for read-path efficiency — the schema comments say so explicitly ("the reference DDL declares no FK here"). They carry the value but not the constraint, so they're annotated on the entities above, not drawn as relationships.

Catalog tables

`llm_providers`

Registered LLM providers. The actual API key never lives here — only a reference; the key is stored in the OS keychain (see keychain-and-secrets.md).

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`name`	TEXT	NOT NULL UNIQUE (e.g. `anthropic`, `openai`)
`display_name`	TEXT	NOT NULL
`base_url`	TEXT	NOT NULL — a custom endpoint (2.5.G S9); actually used at routing time for an OpenAI-compatible provider (ADR-0065 §3)
`api_key_keychain_ref`	TEXT	NULL — keychain `account` identifier, not the key itself
`default_headers`	TEXT (JSON)	NOT NULL DEFAULT `'{}'`
`kind`	TEXT	NULL — the protocol `ProviderKind` (`anthropic`/`openai-compatible`/`gemini`), added by migration `0008` (ADR-0065 §5). Populated for uniformity; validated against `PROVIDER_KINDS` at the store read boundary (no DB CHECK — SQLite `ALTER ADD` limit; a foreign value ⇒ `undefined`). Load-bearing only for a future custom provider.
`pricing_reference_url`	TEXT	NULL — a pricing-page URL (a UX pointer for user-supplied pricing, S10), added by migration `0008` (ADR-0065 §5)
`is_active`	INTEGER (bool)	NOT NULL DEFAULT 1
`deleted_at`	INTEGER	NULL
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_llm_providers_name ON llm_providers (name) WHERE deleted_at IS NULL;

`model_catalog`

Models offered by each provider, including pricing used for local cost tracking. The *_per_mtok_microcents columns are price per million tokens, in integer micro-cents (one micro-cent = 1e-8 USD = cents x 1,000,000; see the money/cost convention). The three media_*_cost_microcents columns are the projection of ModelPricing.mediaOutputRates (1.AF/D17, ADR-0044 §3) — integer micro-cents per billed media-output unit (per image, per audio-second, per video-second); NULL when the model has no metered media rate (the realized fold + the pre-egress estimate degrade to 0 for it — H4). document/PDF is excluded (it bills as tokens). No shipped model carries a media rate yet, so these are NULL across the seeded catalog.

Live-discovery cache role (ADR-0064 §4/§5). As of 2.5.G this table doubles as the live-discovery cache — "which model ids a given key can reach" — filled by a bulk refresh over the seam's listModels, with the generated model catalog (ADR-0071) enriching at read time. This table's own cost columns are still reserved for source='user' pricing — the generated catalog's money is never written into them. As of ADR-0072 the generated catalog IS mirrored into the sibling model_metadata table (a durable backing for the refresh overlay), but that mirror is not a pricing authority: the binary snapshot floor stays terminal, and model_metadata never joins to this table (it is keyed by model_id, not the catalog UUID). The source discriminant records provenance: static (a hardcoded capability/media seed — the media-routing upsert path's default), live (discovered via listModels — the refresh writes it), user (user-supplied pricing, ADR-0065). last_refreshed_at is the freshness stamp backing the 24h TTL. The bulk refresh (replaceProviderModels) soft-deactivates (is_active = 0, deleted_at left NULL) every currently-active source='live' row of a provider whose model id vanishes from the new list, and reactivates a reappearing one by reusing the same row — it never hard-DELETEs (model_catalog.id is an FK target from six tables: agents, step_executions, run_costs, agent_sessions, session_messages, and session_costs since ADR-0070) and never touches a source='user' or source='static' row (a refresh must not clobber user pricing or regress the media-routing seed). The existing narrow media-routing projection (resolveMediaSurface / the D15 capability load-check) is untouched by the widening. Because SQLite ALTER TABLE ADD cannot carry a CHECK, the closed source value set is validated at the store read boundary (coerceModelCatalogSource, degrading a foreign value to static), like media_surface.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`provider_id`	TEXT	NOT NULL REFERENCES `llm_providers(id)`
`model_id`	TEXT	NOT NULL (e.g. `claude-sonnet-4-6`)
`display_name`	TEXT	NOT NULL
`context_window_tokens`	INTEGER	NOT NULL
`max_output_tokens`	INTEGER	NOT NULL
`input_cost_per_mtok_microcents`	INTEGER	NOT NULL DEFAULT 0
`output_cost_per_mtok_microcents`	INTEGER	NOT NULL DEFAULT 0
`cached_input_cost_per_mtok_microcents`	INTEGER	NOT NULL DEFAULT 0
`cached_input_stated`	INTEGER (bool)	NOT NULL DEFAULT 0 — did the USER state the cache rate (migration 0011, ADR-0071 §10)? `0` ⇒ derive it from the catalog discount; `1` ⇒ the number above is the user's own
`media_image_cost_microcents`	INTEGER	NULL — µ¢ per output image (1.AF/D17)
`media_audio_cost_microcents`	INTEGER	NULL — µ¢ per output audio-second
`media_video_cost_microcents`	INTEGER	NULL — µ¢ per output video-second
`media_surface`	TEXT	NOT NULL DEFAULT `'chat'` — `'chat'` \| `'generative'`; routes an agent node to the normal turn vs `generateMedia()` (1.AG/ADR-0045 §1)
`supports_tool_calling`	INTEGER (bool)	NOT NULL DEFAULT 0
`supports_vision`	INTEGER (bool)	NOT NULL DEFAULT 0
`supports_streaming`	INTEGER (bool)	NOT NULL DEFAULT 1
`supports_json_mode`	INTEGER (bool)	NOT NULL DEFAULT 0
`capabilities`	TEXT (JSON)	NOT NULL DEFAULT `'{}'`
`deprecation_date`	INTEGER	NULL
`source`	TEXT	NOT NULL DEFAULT `'static'` — provenance: `'static'` \| `'live'` \| `'user'` (the live-discovery cache discriminant, ADR-0064 §4); validated at the store read boundary (no DB CHECK — SQLite `ALTER ADD`)
`last_refreshed_at`	INTEGER	NULL — epoch-ms a live refresh last wrote this row (ADR-0064 §5 TTL freshness); NULL for a static/user or never-refreshed row
`visible`	INTEGER (bool)	NOT NULL DEFAULT 1 — per-model picker visibility (ADR-0072 point 4); a HARD filter ABOVE §6's "dim, never hide" availability rule, ORTHOGONAL to `is_active` (a reachable-but-hidden model is `is_active=1, visible=0`). Set by `/settings > /models`; every write path that rewrites a row must PRESERVE it (read-modify-write)
`is_active`	INTEGER (bool)	NOT NULL DEFAULT 1
`deleted_at`	INTEGER	NULL
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_model_catalog_provider_model ON model_catalog (provider_id, model_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_model_catalog_provider ON model_catalog (provider_id);
CREATE INDEX idx_model_catalog_active   ON model_catalog (is_active) WHERE deleted_at IS NULL;

`model_metadata`

The DB mirror of the models.dev catalog (ADR-0072) — price/limits/reasoning/capabilities + pure enrichment for every model, refreshed from models.dev and replacing the ~/.relavium file cache as the durable backing for the runtime overlay. Keyed by model_id alone — the same key space as the generated CATALOG_SNAPSHOT (Record<string, CatalogModel>), not the model_catalog UUID — so an entry is stored once, provider-agnostic (a CatalogModel carries its own provider). A leaf table: no FK referrers, and deliberately no FK to model_catalog (a long-tail model may name an unregistered provider). This table is never the terminal money source — the binary snapshot floor stays authoritative for shipped ids; the DB only backs the additive overlay, so a fresh/torn/older-schema DB degrades to the snapshot, offline (ADR-0072 point 2). The DB→CatalogModel projection maps a NULL cache rate to undefined, never 0 (ADR-0071 §10), and the shared admitRefreshedModels gate admits a refreshed row only if both base rates are finite and > 0.

Column	Type	Constraints
`model_id`	TEXT	PRIMARY KEY — the canonical id (the `CATALOG_SNAPSHOT` key), NOT a UUID
`provider`	TEXT	NOT NULL — a `ProviderId` as coerced text; no FK (a long-tail model may name an unregistered provider); validated at the read boundary
`display_name`	TEXT	NOT NULL
`context_window_tokens`	INTEGER	NOT NULL
`max_output_tokens`	INTEGER	NOT NULL
`input_cost_per_mtok_microcents`	INTEGER	NOT NULL — µ¢ per Mtok; a `refreshed` row must have this `> 0` (CHECK)
`output_cost_per_mtok_microcents`	INTEGER	NOT NULL — µ¢ per Mtok; a `refreshed` row must have this `> 0` (CHECK)
`cached_input_cost_per_mtok_microcents`	INTEGER	NULL — absent ≠ 0; projected to `undefined`, never `0` (ADR-0071 §10)
`cache_write_cost_per_mtok_microcents`	INTEGER	NULL — as above
`context_tiers`	TEXT (JSON)	NULL — `CatalogPriceTier[]`; absent ⇒ flat rate at every length
`reasoning`	TEXT (JSON)	NULL — `ReasoningControls`; NULL ⇒ the SAFE default (no reasoning/no knob) — not the `supports_*` reject default
`request_capabilities`	TEXT (JSON)	NULL — `RequestCapabilities`; NULL ⇒ accept-ALL (a field appears only when the model REJECTS that parameter)
`input_modalities`	TEXT (JSON)	NULL — pure enrichment (`string[]`); absent ⇒ assume text
`output_modalities`	TEXT (JSON)	NULL — pure enrichment (`string[]`)
`knowledge_cutoff`	TEXT	NULL — pure enrichment (e.g. `'2024-10'`)
`description`	TEXT	NULL — pure enrichment; models.dev publishes none today (carried per the maintainer's request)
`origin`	TEXT	NOT NULL — `'shipped'` (money+wire pinned to the reviewed snapshot; a refresh rewrites only its enrichment columns) \| `'refreshed'` (a long-tail model admitted through the shared gate); CHECK-enforced (initial CREATE)
`catalog_schema_version`	INTEGER	NOT NULL — the normalizer version that wrote this row; a read admits it only when it matches the running binary, else falls to the snapshot
`refreshed_at`	INTEGER	NULL — epoch-ms of the last refresh that wrote this row
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

-- Money floor at rest: a REFRESHED long-tail row must be priced on both sides; a SHIPPED row is whatever the
-- reviewed snapshot says (a free shipped model, input 0, is legitimate and pinned) — so the check is scoped.
CHECK (origin IN ('shipped', 'refreshed'));
CHECK (origin = 'shipped' OR (input_cost_per_mtok_microcents > 0 AND output_cost_per_mtok_microcents > 0));
CREATE INDEX idx_model_metadata_provider ON model_metadata (provider);

`catalog_meta`

The singleton cursor for the DB-backed catalog (ADR-0072 points 6-7) — at most one row (id = 1; the PK + CHECK forbid a second row and any id ≠ 1, but neither forces a row to exist — it is created lazily by the host's first upsertMeta, with no bootstrap seed, so the table is 0-or-1 rows). Holds the SHA of the snapshot the shipped rows were last seeded from (gates re-seeding after a binary upgrade), the normalizer version of the stored rows (a read admits DB rows only when it matches), the two independent per-axis TTL cursors (availability = first-party provider lists, default-ON; catalog = third-party models.dev, default-OFF), and the last upstream ETag for conditional revalidation.

Column	Type	Constraints
`id`	INTEGER	PRIMARY KEY — CHECK `id = 1` (singleton: PK + CHECK forbid a second row / any id ≠ 1 — at most one row; created lazily, not seeded)
`seeded_snapshot_sha`	TEXT	NULL — the `CATALOG_SNAPSHOT` SHA the shipped rows were last seeded from
`catalog_schema_version`	INTEGER	NULL — the normalizer version of the rows currently in `model_metadata`
`availability_checked_at`	INTEGER	NULL — epoch-ms of the last provider-list (first-party) refresh
`catalog_checked_at`	INTEGER	NULL — epoch-ms of the last models.dev (third-party) refresh
`catalog_source_etag`	TEXT	NULL — the last models.dev ETag, for conditional revalidation
`updated_at`	INTEGER	NOT NULL

`agents`

A cached/snapshot copy of agent definitions for fast catalog browsing and run reproducibility. The .agent.yaml file remains authoritative.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`name`	TEXT	NOT NULL
`slug`	TEXT	NOT NULL UNIQUE
`description`	TEXT	NULL
`model_id`	TEXT	NOT NULL REFERENCES `model_catalog(id)`
`system_prompt`	TEXT	NOT NULL DEFAULT `''`
`tools`	TEXT (JSON)	NOT NULL DEFAULT `'[]'`
`config`	TEXT (JSON)	NOT NULL DEFAULT `'{}'` (temperature, max_tokens, fallback_chain)
`input_schema`	TEXT (JSON)	NULL
`output_schema`	TEXT (JSON)	NULL
`tags`	TEXT (JSON array)	NOT NULL DEFAULT `'[]'`
`source_path`	TEXT	NULL — workspace-relative path to the `.agent.yaml` file
`is_active`	INTEGER (bool)	NOT NULL DEFAULT 1
`deleted_at`	INTEGER	NULL
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_agents_slug   ON agents (slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_agents_model         ON agents (model_id);
CREATE INDEX idx_agents_active        ON agents (is_active, created_at DESC) WHERE deleted_at IS NULL;
-- tags are queried with json_each(); SQLite has no GIN index (see cloud-phase-2.md)

Postgres version INTEGER + the separate agent_versions table are dropped locally — git history on the YAML file is the version record.

`workflows`

Cached/snapshot copy of workflow definitions. The definition column holds the parsed workflow graph (the canonical format is ../contracts/workflow-yaml-spec.md).

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`name`	TEXT	NOT NULL
`slug`	TEXT	NOT NULL UNIQUE
`description`	TEXT	NULL
`definition`	TEXT (JSON)	NOT NULL — parsed graph (nodes, edges, agents, context)
`input_schema`	TEXT (JSON)	NULL
`tags`	TEXT (JSON array)	NOT NULL DEFAULT `'[]'`
`source_path`	TEXT	NULL — workspace-relative path to the `.relavium.yaml` file
`is_active`	INTEGER (bool)	NOT NULL DEFAULT 1
`deleted_at`	INTEGER	NULL
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_workflows_slug ON workflows (slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_workflows_active      ON workflows (is_active, updated_at DESC) WHERE deleted_at IS NULL;

Run-history tables

`runs`

One row per workflow execution. workflow_definition_snapshot freezes the exact graph that ran, so a run can be replayed or inspected even after the YAML file changes. Cost is stored as integer micro-cents.

Logical Run vs persisted RunRow. @relavium/shared exports RunSchema — the narrow, engine-/surface-facing view of a run (status, trigger, inputs/outputs, token + cost totals, timestamps). This runs table is the persistence shape and carries additional columns that are a database concern, modeled by @relavium/db as a distinct RunRow mirroring the DDL below: workflow_definition_snapshot (the frozen graph for replay/resume), trigger_metadata, workflow_path/project_root, and the deleted_at soft-delete cursor. Those are intentionally absent from the logical RunSchema; a consumer that needs them reads the RunRow. The split keeps the engine view free of storage details while @relavium/db owns the row ↔ column mapping.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`workflow_id`	TEXT	NOT NULL REFERENCES `workflows(id)` — the surrogate UUID PK, not the authored kebab id (that lives in `workflows.slug`). `RunSchema.workflowId` mirrors this UUID FK (ADR-0022).
`workflow_path`	TEXT	NULL — source `.relavium.yaml` path
`project_root`	TEXT	NULL — workspace that owned the run
`workflow_definition_snapshot`	TEXT (JSON)	NOT NULL
`status`	TEXT	NOT NULL DEFAULT `'pending'` — `CHECK (status IN ('pending','running','paused','completed','failed','cancelled'))`
`execution_mode`	TEXT	NOT NULL DEFAULT `'local'` — `CHECK (execution_mode IN ('local','cloud','managed'))`; which mode the run used (cost/billing attribution + history)
`trigger_type`	TEXT	NOT NULL DEFAULT `'manual'` (`manual`, `file_change`, `mcp_call`; `webhook`/`schedule` are Phase 2)
`trigger_metadata`	TEXT (JSON)	NOT NULL DEFAULT `'{}'`
`input_json`	TEXT (JSON)	NOT NULL DEFAULT `'{}'`
`output_json`	TEXT (JSON)	NULL
`error_json`	TEXT (JSON)	NULL
`started_at`	INTEGER	NULL
`completed_at`	INTEGER	NULL
`total_input_tokens`	INTEGER	NOT NULL DEFAULT 0
`total_output_tokens`	INTEGER	NOT NULL DEFAULT 0
`total_cost_microcents`	INTEGER	NOT NULL DEFAULT 0
`deleted_at`	INTEGER	NULL
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE INDEX idx_runs_workflow      ON runs (workflow_id, created_at DESC);
CREATE INDEX idx_runs_status        ON runs (status, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_runs_cost          ON runs (workflow_id, created_at, total_cost_microcents) WHERE deleted_at IS NULL;
-- Backs the workflow-agnostic recency lists (listRuns/listActiveRuns + the 2.5.B Home strip): ORDER BY
-- (created_at DESC, id DESC) over the non-deleted set is served off this partial index instead of a filesort.
CREATE INDEX idx_runs_created       ON runs (created_at DESC, id DESC) WHERE deleted_at IS NULL;

`step_executions`

One row per node attempt within a run. This is what drives the per-node run trace, the Gantt timeline, retry-from-node, and per-node cost attribution. agent_snapshot freezes the agent config that executed the node.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`run_id`	TEXT	NOT NULL REFERENCES `runs(id)` ON DELETE CASCADE
`node_id`	TEXT	NOT NULL — graph node id
`node_type`	TEXT	NOT NULL — one of the engine node-type enum values, since this column records what the engine executed (see ../shared-core/node-types.md)
`agent_id`	TEXT	NULL REFERENCES `agents(id)`
`agent_snapshot`	TEXT (JSON)	NULL
`model_id`	TEXT	NULL REFERENCES `model_catalog(id)`
`attempt_number`	INTEGER	NOT NULL DEFAULT 1
`status`	TEXT	NOT NULL DEFAULT `'pending'` — `CHECK (status IN ('pending','running','completed','failed','skipped'))`
`input_json`	TEXT (JSON)	NOT NULL DEFAULT `'{}'`
`output_json`	TEXT (JSON)	NULL
`error_json`	TEXT (JSON)	NULL
`started_at`	INTEGER	NULL
`completed_at`	INTEGER	NULL
`duration_ms`	INTEGER	NULL
`input_tokens`	INTEGER	NOT NULL DEFAULT 0
`output_tokens`	INTEGER	NOT NULL DEFAULT 0
`cached_tokens`	INTEGER	NOT NULL DEFAULT 0
`cost_microcents`	INTEGER	NOT NULL DEFAULT 0
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE INDEX idx_step_exec_run       ON step_executions (run_id, created_at ASC);
CREATE INDEX idx_step_exec_run_node  ON step_executions (run_id, node_id, attempt_number);
CREATE INDEX idx_step_exec_agent     ON step_executions (agent_id, created_at DESC) WHERE agent_id IS NOT NULL;
CREATE INDEX idx_step_exec_model     ON step_executions (model_id, created_at DESC) WHERE model_id IS NOT NULL;
CREATE INDEX idx_step_exec_cost      ON step_executions (model_id, created_at, cost_microcents) WHERE model_id IS NOT NULL;

`messages`

The LLM conversation for each agent step (prompt, completion, tool calls). Cascades from step_executions. Used for run inspection and to seed retry-from-node.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`step_execution_id`	TEXT	NOT NULL REFERENCES `step_executions(id)` ON DELETE CASCADE
`run_id`	TEXT	NOT NULL
`sequence_number`	INTEGER	NOT NULL
`role`	TEXT	NOT NULL (`system`, `user`, `assistant`, `tool`)
`content`	TEXT	NULL
`content_parts`	TEXT (JSON)	NULL — multimodal/structured parts
`tool_calls`	TEXT (JSON)	NULL
`tool_call_id`	TEXT	NULL
`name`	TEXT	NULL
`finish_reason`	TEXT	NULL
`created_at`	INTEGER	NOT NULL

CREATE INDEX idx_messages_step ON messages (step_execution_id, sequence_number ASC);
CREATE INDEX idx_messages_run  ON messages (run_id, created_at ASC);

`run_events`

The append-only event log for a run — the persistent record of the SSE/RunEvent stream. This is what the run-detail log drawer replays and what powers reconnect/resync. seq is monotonic per run and is used for gap detection.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`run_id`	TEXT	NOT NULL REFERENCES `runs(id)` ON DELETE CASCADE
`step_execution_id`	TEXT	NULL
`seq`	INTEGER	NOT NULL — monotonic per run
`event_type`	TEXT	NOT NULL — e.g. `node:started`, `agent:token`, `human_gate:paused`
`level`	TEXT	NOT NULL DEFAULT `'info'`
`node_id`	TEXT	NULL
`payload_json`	TEXT (JSON)	NOT NULL DEFAULT `'{}'`
`ts`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_run_events_run_seq ON run_events (run_id, seq ASC);  -- seq is monotonic per run: (run_id, seq) is unique
CREATE INDEX idx_run_events_step        ON run_events (step_execution_id, ts ASC) WHERE step_execution_id IS NOT NULL;
CREATE INDEX idx_run_events_run_type    ON run_events (run_id, event_type, ts ASC);

token-level events are high volume. They are stored to support full replay but are the primary target of the 90-day pruning job; runs/step_executions metadata is retained longer.

Timestamp unit at the persistence boundary. The wire RunEvent.timestamp is an ISO-8601 string (sse-event-schema.md envelope), but it is persisted here as run_events.ts = epoch-milliseconds INTEGER (the table convention, for reliable ordering). The conversion ISO ↔ epoch-ms happens at the @relavium/db write/read boundary; the logical RunSchema timestamps (createdAt/startedAt/…) are already epoch-ms and pass through unchanged.

`run_costs`

Denormalized per-node cost rows for fast cost-waterfall rendering without re-aggregating step_executions. Stored as integer micro-cents.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`run_id`	TEXT	NOT NULL REFERENCES `runs(id)` ON DELETE CASCADE
`node_id`	TEXT	NOT NULL
`model_id`	TEXT	NULL REFERENCES `model_catalog(id)`
`input_tokens`	INTEGER	NOT NULL DEFAULT 0
`output_tokens`	INTEGER	NOT NULL DEFAULT 0
`cost_microcents`	INTEGER	NOT NULL DEFAULT 0
`created_at`	INTEGER	NOT NULL

CREATE INDEX idx_run_costs_run ON run_costs (run_id);

Agent-session tables

These two tables persist agent sessions (the agent-first chat entry point — ADR-0024, agent-session-spec.md). They live in the same ~/.relavium/history.db (desktop: SQLCipher-encrypted; CLI: unencrypted + 0600/0700 OS perms, ADR-0050) as run history — there is no separate sessions.db. They are bound to a session, deliberately distinct from the per-step run messages table (which is bound to step_executions within a workflow run); the two share a shape family but must not be merged, because a session and a run have different lifecycles.

`agent_sessions`

One row per chat session. context_json freezes the SessionContext (active file, selection, session variables); agent_snapshot freezes the agent config the session ran against.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`agent_id`	TEXT	NULL REFERENCES `agents(id)`
`agent_slug`	TEXT	NOT NULL — the authored `agent_ref` the session is bound to
`agent_snapshot`	TEXT (JSON)	NULL — frozen agent config for reproducibility
`title`	TEXT	NULL — display title (derived from the first message or user-set)
`model_id`	TEXT	NULL REFERENCES `model_catalog(id)` — the session's configured primary model (resolved at start); the actual per-turn model, which may differ under fallback, is `session_messages.model_id`
`working_dir`	TEXT	NULL — session-context workspace root
`git_ref`	TEXT	NULL — branch/commit at session start, for provenance
`fs_scope_tier`	TEXT	NOT NULL DEFAULT `'sandboxed'` — `CHECK (fs_scope_tier IN ('sandboxed','project','full'))` (the same tier enum as workflows; see built-in-tools.md)
`status`	TEXT	NOT NULL DEFAULT `'active'` — `CHECK (status IN ('active','idle','exported','ended'))`
`context_json`	TEXT (JSON)	NOT NULL DEFAULT `'{}'` — the frozen `SessionContext`
`total_input_tokens`	INTEGER	NOT NULL DEFAULT 0
`total_output_tokens`	INTEGER	NOT NULL DEFAULT 0
`total_cost_microcents`	INTEGER	NOT NULL DEFAULT 0
`exported_workflow_path`	TEXT	NULL — set when the session is exported to a `.relavium.yaml`
`deleted_at`	INTEGER	NULL
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE INDEX idx_agent_sessions_status ON agent_sessions (status, updated_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_agent_sessions_agent  ON agent_sessions (agent_id, created_at DESC) WHERE agent_id IS NOT NULL;
-- Backs listSessions + the 2.5.B Home "recent sessions" strip: ORDER BY (updated_at DESC, id DESC) over the
-- non-deleted set is served off this partial index rather than a filesort (the session counterpart of idx_runs_created).
CREATE INDEX idx_agent_sessions_updated ON agent_sessions (updated_at DESC, id DESC) WHERE deleted_at IS NULL;

`session_messages`

The append-only conversation transcript for a session — the session-scoped counterpart of the run messages table. Never updated or deleted in normal operation (mirrors the run-event-log pattern). Cascades from agent_sessions.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`session_id`	TEXT	NOT NULL REFERENCES `agent_sessions(id)` ON DELETE CASCADE
`sequence_number`	INTEGER	NOT NULL — monotonic per session (append-only)
`role`	TEXT	NOT NULL (`system`, `user`, `assistant`, `tool`)
`content`	TEXT	NULL
`content_parts`	TEXT (JSON)	NULL — multimodal/structured parts
`tool_calls`	TEXT (JSON)	NULL
`tool_call_id`	TEXT	NULL
`name`	TEXT	NULL
`finish_reason`	TEXT	NULL
`model_id`	TEXT	NULL REFERENCES `model_catalog(id)` — the model that produced an assistant turn (fallback-aware, so the transcript shows which model answered; NULL for non-assistant rows)
`compaction_dropped_through_sequence`	INTEGER	NULL — ADR-0062: set ONLY on a `role='system'` compaction/trim boundary marker; the durable `sequence_number` through which older messages are superseded (resume drops rows at/below it). Additive nullable column (migration 0006); NULL on every normal transcript row
`created_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_session_messages_seq ON session_messages (session_id, sequence_number);
CREATE INDEX idx_session_messages_session    ON session_messages (session_id, created_at ASC);

The per-message input_tokens / output_tokens / cost_microcents columns were DROPPED (migration 0009, ADR-0070 §5). They were never written — the persister never passed a metadata payload, so every shipped row held 0 — and, decisively, a per-message cost column is structurally incapable of holding the truth for a turn whose tool loop billed two models. Money attribution lives in session_costs. model_id stays: it is written (ADR-0059) and remains the per-message "which model wrote this reply" label.

`session_costs`

The durable per-(session, model) money attribution (ADR-0070) — what /cost renders.

The invariant, true by construction for every session: SUM(session_costs.cost_microcents) == agent_sessions.total_cost_microcents.

Both sides are fed by the same cost:updated egress, with the same arithmetic, in the same transaction, from a single owner (SessionStore.recordSessionCost — total_cost_microcents is written by nothing else). It holds across resume, reseat, failover, tool loops, compaction, errored turns and aborted turns. It is internal consistency, not the provider's invoice: an egress that streamed content but ended without a usage chunk, and a mid-stream failure, are recorded as 0 on both sides (ADR-0070 §3), so the reported spend is a systematic under-estimate of the bill.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY
`session_id`	TEXT	NOT NULL REFERENCES `agent_sessions(id)` ON DELETE CASCADE
`model`	TEXT	NOT NULL, CHECK (`model` <> '') — the RAW provider model string, and the attribution key
`model_catalog_id`	TEXT	NULL REFERENCES `model_catalog(id)` — a written join column for enrichment; never the key
`input_tokens`	INTEGER	NOT NULL DEFAULT 0
`output_tokens`	INTEGER	NOT NULL DEFAULT 0
`cost_microcents`	INTEGER	NOT NULL DEFAULT 0
`call_count`	INTEGER	NOT NULL DEFAULT 0 — every `cost:updated` folded into this row, priced or not
`unpriced_calls`	INTEGER	NOT NULL DEFAULT 0 — the subset of `call_count` we could not price (a `$0` row with real tokens is unpriced, not free). `unpriced_calls <= call_count` always; `/cost` renders it as "price unknown for N of M calls"
`is_legacy`	INTEGER (bool)	NOT NULL DEFAULT 0 — `1` only on the aggregate row a pre-2.6.C session was backfilled with (0009 writes the row, 0010 adds this column and flags it; ADR-0070 §4). It is in the unique index, so a real egress (always `0`) can never upsert onto that row. Branch on this column, never on the `model` string — the `(pre-2.6.C)` label is not reserved, and a custom model may legally be named it
`created_at`	INTEGER	NOT NULL
`updated_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_session_costs_session_model ON session_costs (session_id, model, is_legacy);
CREATE INDEX        idx_session_costs_session       ON session_costs (session_id, cost_microcents);

Why the key is the raw model string and NOT the catalog FK — a deliberate, load-bearing divergence from run_costs that a reviewer must not "fix" back. The FK target is a catalog UUID that resolves to NULL for an uncataloged model (custom, self-hosted, brand-new), and NULLs are DISTINCT under UNIQUE in both SQLite and Postgres — so keying on it would make ON CONFLICT never match: every uncataloged egress would insert a new row, and two different uncataloged models would collapse into one indistinguishable bucket.

Counters, not booleans. unpriced_calls is a count because 2.6.Q can price a model mid-session, so one (session, model) row folds both priced and unpriced egresses; a boolean would become meaningless the moment that happens.

Legacy sessions. Migration 0009 backfills one row per pre-existing session with the sentinel model (pre-2.6.C) carrying its whole legacy total — the per-attempt increments that would have split it were never kept. The invariant is therefore true for every row in the table, not only post-migration ones; /cost renders the sentinel as "per-model breakdown unavailable" rather than as an implied single-model session.

Secret-free by construction — no free-text or JSON column.

Mapping the durable SessionMessage to a row (1.X). @relavium/shared's SessionMessage (agent-session-spec.md §"Session messages") carries the transcript body as a single content: DurableContentPart[] array. That array is the canonical body and is stored as JSON in content_parts — the source of truth the @relavium/db mapper round-trips. The remaining scalar columns (content, tool_calls, tool_call_id, name, finish_reason, model_id) are optional denormalized metadata (a plain-text projection for display/search, plus the "which model wrote this reply" label) the persistence layer MAY populate; they are NULL when the durable parts array is the sole source of a row. They keep session_messages in the run messages shape family without forcing a session to decompose its parts. The per-message cost/token counters are gone — input_tokens, output_tokens and cost_microcents were dropped in migration 0009 (see the note above); durable money attribution lives in session_costs, which is the only table that can express a turn whose tool loop billed two models. The reasoning signature and inline media bytes are structurally impossible in content_parts — DurableContentPart has no signature field and only handle-only media (ADR-0030/ADR-0031), enforced at the mapper's parse boundary on both write and read.

A secret-typed value is never persisted into session_messages — per ADR-0029 secrets are rejected from prompt/tool text at parse, so they never reach a message body. The user's own conversational content is stored here and is protected at rest by history.db's SQLCipher encryption on the desktop, and by 0600/0700 OS file permissions on the CLI (ADR-0050).

Cross-host access (CLI / VS Code) and the at-rest divergence. The same history.db path is opened by the non-Tauri hosts: the CLI uses the better-sqlite3 path (ADR-0021, re-affirmed under the >=22 floor by ADR-0067); the VS Code extension host uses a wasm SQLite build (no native module — respects ADR-0003's no-arbitrary-native-modules constraint). At-rest encryption is per-surface and not yet unified: the desktop opens the file with SQLCipher, but the CLI opens it unencrypted (no passphrase), guarded by 0600/0700 OS permissions (ADR-0050). A standard better-sqlite3 build cannot open a SQLCipher file (nor vice-versa), so the desktop (SQLCipher) and the CLI (unencrypted) cannot share one file at ~/.relavium/history.db. In Phase 2 there is no live collision — the desktop does not exist yet (Phase 3) and the CLI is the sole writer. Cross-surface session/run resume across the desktop and CLI is therefore a named Phase-3 follow-on (ADR-0050): the desktop phase reconciles the shared-path posture (a uniformly-unencrypted shared store, per-surface separate files, or a CLI/Node SQLCipher-capable build). Until then there is no cross-surface shared session/run store.

Media tables (1.AF)

These two tables are the media retention/GC store (ADR-0042) — the first persisted, mutable state outside the ADR-0003 derived-from-run_events model (a retention store, NOT a checkpoint store). The byte blobs themselves live in a host content-addressed store (a filesystem CAS on CLI/VS Code, the Rust CAS on desktop — ADR-0032), not in the database; these tables track existence, metadata, references (the refcount), and the read_media authz scope-set. The content-addressed media://sha256-<64hex> handle is the integrity hash — there is no separate checksum column. (At P1+P2 the schema is landed but the row-writer / refcount / sweep wiring is not — P3/P4, D10/D11 — so these tables ship empty.)

`media_objects`

One row per distinct stored media blob, keyed by its content-address handle.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`handle`	TEXT	NOT NULL UNIQUE — the `media://sha256-<64hex>` content-address. A UNIQUE constraint (not merely an index) so the `media_references` Postgres FK can target it (ADR-0005 parity)
`mime_type`	TEXT	NOT NULL
`modality`	TEXT	NOT NULL — CHECK in (`image`, `audio`, `video`, `document`) (the `@relavium/shared` `MEDIA_MODALITIES` set)
`byte_length`	INTEGER	NOT NULL — what a `read_media` `Range` request is bounded against (host-populated; never a client-supplied size)
`duration_ms`	INTEGER	NULL — audio/video only
`last_referenced_at`	INTEGER	NOT NULL — epoch-ms; the grace-window basis for GC
`deleted_at`	INTEGER	NULL — soft-delete (the table convention); set by GC byte-reclamation
`created_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX media_objects_handle_unique ON media_objects (handle);
CREATE INDEX idx_media_objects_gc ON media_objects (last_referenced_at) WHERE deleted_at IS NULL;

`media_references`

The per-distinct-reference junction the refcount derives from, and the persistence home of the read_media scope-set authz. Cascades from media_objects.

Column	Type	Constraints
`id`	TEXT	PRIMARY KEY (UUID)
`handle`	TEXT	NOT NULL REFERENCES `media_objects(handle)` ON DELETE CASCADE
`scope_kind`	TEXT	NOT NULL — CHECK in (`run`, `node`, `session`, `workspace`) (the `MEDIA_SCOPE_KINDS` superset)
`scope_id`	TEXT	NOT NULL
`created_at`	INTEGER	NOT NULL

CREATE UNIQUE INDEX idx_media_references_unique ON media_references (handle, scope_kind, scope_id);
CREATE INDEX idx_media_references_scope  ON media_references (scope_kind, scope_id);
CREATE INDEX idx_media_references_handle ON media_references (handle);

Refcount ↔ authz keying — one junction, two roles (ADR-0042 §3 / ADR-0044). A handle's refcount is its row count here. scope_kind is a deliberate superset: run / node references are lifetime/refcount entries ONLY (the terminal-state sweep reclaims a run's run rows when it reaches run:completed|failed|cancelled), while session / (reserved) workspace are ALSO the read_media authz Scope kinds — read_media authz consults only the session/workspace rows, so a run/node reference never grants read. A handle may carry both a run reference (lifetime) and a session reference (authz); the terminal sweep removing the run row leaves the handle alive while a session row remains.

Retention / GC (ADR-0042 §4). A handle whose reference count reaches zero enters a grace window (default 7 days, measured from last_referenced_at); refcount-GC reclaims the bytes and sets deleted_at only after it elapses — distinct from the 90-day run_events prune. GC ownership is the host's (Rust on desktop / filesystem on CLI/VS Code).

Common query patterns

Pattern	Where it's used	Index relied on
Replay a run's event stream in order	Run-detail log drawer	`idx_run_events_run_seq`
Resume a paused run from its last checkpoint	Human-gate resume	`idx_step_exec_run`, `idx_run_events_run_seq`
Cost analytics grouped by workflow/model	Cost tracking screen	`idx_runs_cost`, `idx_step_exec_cost`
List workflows with their last-run status	Workflows list screen	`idx_workflows_active` + a `ROW_NUMBER()` subquery (SQLite has no `DISTINCT ON`)

Postgres DISTINCT ON (workflow_id) for "latest run per workflow" is not supported in SQLite. Use ROW_NUMBER() OVER (PARTITION BY workflow_id ORDER BY created_at DESC) instead — it works identically in both engines, easing the Phase-2 port.

Concurrency & transaction behavior

history.db is a single shared file two concurrent relavium processes may write at once — e.g. a run persisting events while a chat refreshes the live model catalog (ADR-0064 §5). The Node/CLI path (better-sqlite3, ADR-0021, re-affirmed by ADR-0067) hardens this at the connection and the transaction level; this is the one canonical home for the policy that packages/db/src/retry.ts and the store doc-comments cite.

Connection PRAGMAs (client.ts): journal_mode = WAL (readers never block the single writer, and vice-versa), busy_timeout = 5000 (SQLite's built-in busy handler waits up to 5 s for a contended lock before returning SQLITE_BUSY), synchronous = NORMAL (the recommended durability/throughput trade-off under WAL), and foreign_keys = ON.
Write transactions use BEGIN IMMEDIATE, never drizzle's DEFERRED default. A DEFERRED transaction that reads before it writes takes a read lock first and must upgrade to a write lock on the first write — if another connection committed in between, that upgrade fails immediately with SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT), which busy_timeout does not cover. BEGIN IMMEDIATE takes the write lock up front, so the upgrade race cannot occur. Applied to every multi-statement writer: persistEvent (run history), the model-catalog replaceProviderModels (bulk live-upsert) and upsert (per-model pricing), the provider upsert read-then-write, and the media-reference GC writes (addReference, removeRunReferences, reclaimExpired). It applies only to the OUTERMOST BEGIN — a store method called inside another transaction is demoted to a SAVEPOINT and the IMMEDIATE behavior is ignored, so a future batch-in-one-transaction caller must itself open BEGIN IMMEDIATE.
The bounded retry (packages/db/src/retry.ts, withBusyRetry / withBusyRetryAsync) wraps those write transactions and retries only SQLITE_BUSY / SQLITE_LOCKED / SQLITE_BUSY_SNAPSHOT / SQLITE_BUSY_RECOVERY up to a bounded attempt budget (default 5) with a deterministic linear backoff — no jitter, never Math.random, per the no-jitter/deterministic-replay convention of ADR-0040. better-sqlite3 reports the extended result code, so the stale-DEFERRED upgrade failure arrives as the distinct string SQLITE_BUSY_SNAPSHOT, not as a SQLITE_BUSY prefix — matched exactly, so an unrelated future SQLITE_BUSY_* code is not swept into the set. It is fail-loud: on an exhausted budget (or any non-lock fault) it rethrows the original error and never silently drops a write, preserving ADR-0050's durability-first persistEvent posture. A retried transaction rolls back with no partial write and re-runs the whole (idempotent) body. Because each attempt's BEGIN IMMEDIATE can itself wait up to busy_timeout (5 s) for the lock, the compounded worst case under sustained contention is ~25 s (5 attempts × 5 s + the sub-300 ms backoffs) of blocking before the fail-loud rethrow — a deliberate durability-over-latency trade on a path that only stalls under pathological multi-writer contention.
Two retry twins, one policy. withBusyRetry is synchronous, because the driver is. withBusyRetryAsync is the twin for the one call site whose caller is genuinely async today — persistEvent (run history) — where the backoff yields the event loop rather than parking the thread on Atomics.wait. Both share the retryable-code set, the attempt budget, the linear schedule and the fail-loud exhaustion; only the sleep differs. Read the scope precisely: this removes the sub-300 ms term of the ~25 s worst case above, not the dominant one — for SQLITE_BUSY/SQLITE_LOCKED, the codes that actually fire, each attempt still blocks inside the synchronous driver for up to busy_timeout (~5.2 s measured). SQLITE_BUSY_SNAPSHOT returns immediately, so for that code the backoff is the whole cost; it cannot reach a synchronous call site today because every one of them is IMMEDIATE, single-statement, or read-only. The remaining store methods stay synchronous deliberately: converting them would change five port interfaces and ripple through every CLI consumer without making a single write non-blocking. The async twin's await between attempts is an observable yield, so a sibling writer may commit during the backoff — the outcome the backoff exists to allow — which makes the "fn must be one self-contained, re-runnable transaction" requirement load-bearing rather than incidental.
Single-statement writes (e.g. appendMessage, setKeychainRef) go straight for the write lock and need no explicit transaction — BEGIN IMMEDIATE would buy nothing, since a lone INSERT/UPDATE takes the write lock directly and cannot hit a read→write upgrade race. They do still route through withBusyRetry where a failure is user-visible data loss: the three agent_sessions / session_messages writers do (#228), because busy_timeout waits 5 s and then fails, and the chat persister calls them from inside a RunEventBus subscriber.
Reads that must be consistent across statements use a read transaction: sessionStore.loadFull reads the session row and its transcript inside one deferred transaction so both reads observe a single consistent DB snapshot (never a two-SELECT straddle across a concurrent commit). This now composes with turn atomicity on the write side: sessionStore.writeTurn appends a turn's messages and flushes the session row in ONE BEGIN IMMEDIATE transaction (#228), so a snapshot can no longer observe messages ahead of their totals, and a failed write leaves neither a half-written turn nor an unanswered user row that resume cannot roll back. This discharges the per-turn-transaction follow-up this section previously tracked.
Cross-platform: BEGIN IMMEDIATE + the retry behave identically on every OS. The 0600/0700 at-rest guard below is a documented Windows no-op, so the concurrency test lane gates POSIX-permission assertions off Windows only.

This realizes the concurrent-process write requirement recorded in the ADR-0064 §5 amendment note (2.5.I).

Encryption at rest

At-rest encryption of history.db is per-surface:

Desktop: opened with SQLCipher. The passphrase is derived from a stable machine secret (combined with the OS keychain entry) so the database opens on restart without prompting the user; see keychain-and-secrets.md.
CLI (Phase 2): opened with better-sqlite3 unencrypted, guarded by owner-only OS file permissions — ~/.relavium/ at 0700 and history.db (with its -wal/-shm sidecars) at 0600, set with an explicit chmod (umask-independent, applied even to a pre-existing directory). On Windows, POSIX mode bits do not apply (chmod is a no-op); protection falls to the per-user %USERPROFILE% NTFS ACL. The file holds no credentials — keys stay in the OS keychain (ADR-0006) and the engine masks secrets at the bus before persistence (ADR-0036) — so the unencrypted-at-rest content is run data (prompts, outputs, costs), not secrets. Rationale and the cross-surface Phase-3 follow-on: ADR-0050.

The per-project runs.db is not encrypted on any surface because it is intentionally git-committed and contains only non-sensitive run metadata (no prompts, completions, or tokens).

Secrets at the write boundary

The history writer is pass-through for secrets — it never re-masks (the engine already masked secret-typed inputs and tool I/O at the RunEventBus, ADR-0036) and adds no runtime secret-detection (infeasible on opaque JSON). The no-raw-secret invariant on the unsafe columns — run_events.payload_json, the step_executions input_json / output_json / error_json, run_costs, and runs.workflow_definition_snapshot — is therefore the upstream masking guarantee, regression-guarded by the package's secrets fixture (run-history-store.test.ts): a raw API key, Authorization header, or secret-typed value must never appear in these columns; a secret-typed value persists only as its { secret: true, ref } placeholder. (A future desktop/cloud history writer inherits the same contract — masking is upstream, verification is by fixture; it must not be expected to implement a runtime secret scan.)

Phase 2 (PostgreSQL) divergences

The following applies only to Phase 2 cloud execution. None of it ships in Phase 1. See ../../architecture/cloud-phase-2.md for the full design and the complete 14-item SQLite-vs-Postgres porting table.

Drizzle ORM is used for both engines, so table and column names are identical and only the driver changes. The notable divergences:

Types restored to native Postgres: TEXT UUIDs → UUID DEFAULT gen_random_uuid(); JSON TEXT → JSONB (with GIN indexes on tags, definition); epoch INTEGER → TIMESTAMPTZ; micro-cent INTEGER → NUMERIC(14,8); CHECK-string statuses → native enums.
Multi-tenancy: an org_id column is added to every table with Postgres row-level security and team-level sharing permissions.
run_events partitioning: the unbounded event log uses Postgres declarative RANGE-by-month partitioning (or a TimescaleDB hypertable) with pg_cron retention DROP TABLE. SQLite has no partitioning, hence the local 90-day archive/prune job.
Concurrency: Postgres MVCC supports many concurrent writers; SQLite's single-writer WAL lock is adequate locally but would bottleneck cloud-scale parallel runs.
Reintroduced tables: workflow_schedules (cron/interval triggers) becomes functional in Phase 2; *_versions tables may return if portal-managed (non-git) versioning is needed.

Managed-inference tables (Phase 2)

These tables exist only in the Phase-2 managed-inference gateway and have no SQLite/local counterpart — managed inference is a cloud capability (Relavium holds the provider key and meters usage; see ../../architecture/managed-inference.md). They are Postgres-native and follow the same Phase-2 conventions as every other cloud table: integer micro-cents for money, an org_id column with row-level security, TIMESTAMPTZ timestamps, and JSONB blobs. They are governed by ADR-0013 (key vault/pools), ADR-0014 (metering/quota/billing), and ADR-0015 (data handling).

`provider_key_pool`

The pool of Relavium's own provider keys the gateway draws from, one row per key. The key value is never stored here — only a reference to the KMS entry that holds it, mirroring the local "keychain ref, not the key" rule for llm_providers.api_key_keychain_ref. Multiple rows per provider give per-provider rate-limit headroom, rotation, and 429-cooldown.

Column	Type	Constraints
`id`	UUID	PRIMARY KEY DEFAULT `gen_random_uuid()`
`org_id`	UUID	NOT NULL — RLS tenant (platform-org for the shared pool)
`provider`	TEXT	NOT NULL — `anthropic` / `openai` / `gemini` / `deepseek`
`region`	TEXT	NULL — segregation key for residency/ban containment
`kms_key_ref`	TEXT	NOT NULL — reference to the KMS entry; never the key value
`status`	TEXT	NOT NULL DEFAULT `'active'` — enum `('active','cooldown','rotating','retired','quarantined')`
`cooldown_until`	TIMESTAMPTZ	NULL — set on a 429; key skipped until then
`last_used_at`	TIMESTAMPTZ	NULL
`created_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`
`updated_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`

CREATE INDEX idx_pkp_provider_status ON provider_key_pool (provider, status) WHERE status = 'active';
ALTER TABLE provider_key_pool ENABLE ROW LEVEL SECURITY;

`subscriptions`

A mirror of the billing provider's subscription state — the control plane's source of truth for "what tier is this org on, and is it current." Synced from billing-provider webhooks (the merchant-of-record is the primary rail; a direct Stripe integration is the mutually-exclusive alternative — ADR-0014, tech-stack.md); never authoritative over the billing provider. The columns are provider-neutral: billing_provider records which rail issued the ids, and billing_customer_id / billing_subscription_id hold that rail's identifiers.

Column	Type	Constraints
`id`	UUID	PRIMARY KEY DEFAULT `gen_random_uuid()`
`org_id`	UUID	NOT NULL UNIQUE — RLS tenant
`billing_provider`	TEXT	NOT NULL — `paddle` / `lemonsqueezy` / `stripe` (MoR primary; Stripe is the alternative rail)
`billing_customer_id`	TEXT	NOT NULL — customer id in the configured billing provider
`billing_subscription_id`	TEXT	NULL — subscription id in the configured billing provider
`tier`	TEXT	NOT NULL — `free` / `pro` / `team` / `enterprise` (see ../portal/api-reference.md)
`status`	TEXT	NOT NULL — mirrors the billing provider (`active`,`past_due`,`canceled`,`trialing`,…)
`included_usage_microcents`	INTEGER	NOT NULL DEFAULT 0 — the hard included-usage cap for the period
`prepaid_credit_microcents`	INTEGER	NOT NULL DEFAULT 0 — remaining prepaid balance
`current_period_start`	TIMESTAMPTZ	NULL
`current_period_end`	TIMESTAMPTZ	NULL
`created_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`
`updated_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`

CREATE UNIQUE INDEX idx_subscriptions_org ON subscriptions (org_id);
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

`quota_policies`

The enforceable budget/quota policy per org (the control-plane record the gateway reads at reserve time). Separate from subscriptions so a tier can carry several policies (per-day, per-model) and so enterprise can set custom limits.

Column	Type	Constraints
`id`	UUID	PRIMARY KEY DEFAULT `gen_random_uuid()`
`org_id`	UUID	NOT NULL — RLS tenant
`scope`	TEXT	NOT NULL — `org` / `user` / `model`
`scope_ref`	TEXT	NULL — user id or canonical model id when scoped
`period`	TEXT	NOT NULL — `day` / `month`
`budget_microcents`	INTEGER	NOT NULL — the cap for the period
`enforcement`	TEXT	NOT NULL DEFAULT `'hard_stop'` — `warn` / `throttle` / `hard_stop`
`warn_threshold_pct`	INTEGER	NOT NULL DEFAULT 80
`is_active`	BOOLEAN	NOT NULL DEFAULT true
`created_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`
`updated_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`

CREATE INDEX idx_quota_policies_org_scope ON quota_policies (org_id, scope, period) WHERE is_active;
ALTER TABLE quota_policies ENABLE ROW LEVEL SECURITY;

`usage_events`

The immutable, append-only billing ledger — one row per metered managed request, written when the gateway settles the reserve→settle metering (see ../../architecture/managed-inference.md). No prompt or completion bodies are stored — only counts and costs (meter content, not text; ADR-0015). The UNIQUE request_id is what makes settle idempotent: a retried settle is a no-op, so a delivery retry can never double-bill. Because it is unbounded and time-ordered, it uses the same RANGE-by-month partitioning as run_events.

Column	Type	Constraints
`id`	UUID	PRIMARY KEY DEFAULT `gen_random_uuid()`
`org_id`	UUID	NOT NULL — RLS tenant
`request_id`	TEXT	NOT NULL UNIQUE — idempotency key for reserve→settle
`user_id`	UUID	NULL — the member who incurred the usage
`provider`	TEXT	NOT NULL — `anthropic` / `openai` / `gemini` / `deepseek`
`model_id`	TEXT	NOT NULL — canonical model id (the pricing key)
`pool_key_id`	UUID	NOT NULL REFERENCES `provider_key_pool(id)` — which Relavium key served it
`input_tokens`	INTEGER	NOT NULL DEFAULT 0
`output_tokens`	INTEGER	NOT NULL DEFAULT 0
`cache_read_tokens`	INTEGER	NOT NULL DEFAULT 0
`cache_write_tokens`	INTEGER	NOT NULL DEFAULT 0
`usage_source`	TEXT	NOT NULL DEFAULT `'streamed'` — `streamed` / `estimated` / `reconciled` (how the counts were obtained)
`provider_cost_microcents`	INTEGER	NOT NULL — COGS: Relavium's cost from the canonical pricing table
`billed_cost_microcents`	INTEGER	NOT NULL — what the tenant is charged (margin = billed − provider)
`occurred_at`	TIMESTAMPTZ	NOT NULL — partition key
`created_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`

-- immutable ledger: append-only, no UPDATE/DELETE in normal operation
CREATE UNIQUE INDEX idx_usage_events_request ON usage_events (request_id);
CREATE INDEX idx_usage_events_org_time ON usage_events (org_id, occurred_at DESC);
CREATE INDEX idx_usage_events_org_model ON usage_events (org_id, model_id, occurred_at DESC);
-- PARTITION BY RANGE (occurred_at) — monthly partitions, pg_cron retention, as for run_events
ALTER TABLE usage_events ENABLE ROW LEVEL SECURITY;

`usage_aggregates_daily`

A pre-rolled daily rollup of usage_events per org (and per model) so the portal's usage/quota dashboards and the reserve-time per-day budget check do not scan the raw ledger. Rebuilt by the nightly reconciliation job, so it is the reconciled view of spend.

Column	Type	Constraints
`id`	UUID	PRIMARY KEY DEFAULT `gen_random_uuid()`
`org_id`	UUID	NOT NULL — RLS tenant
`day`	DATE	NOT NULL
`model_id`	TEXT	NULL — NULL row = all-models total for the day
`input_tokens`	BIGINT	NOT NULL DEFAULT 0
`output_tokens`	BIGINT	NOT NULL DEFAULT 0
`request_count`	INTEGER	NOT NULL DEFAULT 0
`provider_cost_microcents`	BIGINT	NOT NULL DEFAULT 0
`billed_cost_microcents`	BIGINT	NOT NULL DEFAULT 0
`updated_at`	TIMESTAMPTZ	NOT NULL DEFAULT `now()`

CREATE UNIQUE INDEX idx_usage_agg_daily_org_day_model ON usage_aggregates_daily (org_id, day, COALESCE(model_id, ''));
CREATE INDEX idx_usage_agg_daily_org_day ON usage_aggregates_daily (org_id, day DESC);
ALTER TABLE usage_aggregates_daily ENABLE ROW LEVEL SECURITY;

Navigation Menu

Uh oh!

Database Schema (Local SQLite)

Storage layout

SQLite type conventions

Tables

Entity relationships

Catalog tables

`llm_providers`

`model_catalog`

`model_metadata`

`catalog_meta`

`agents`

`workflows`

Run-history tables

`runs`

`step_executions`

`messages`

`run_events`

`run_costs`

Agent-session tables

`agent_sessions`

`session_messages`

`session_costs`

Media tables (1.AF)

`media_objects`

`media_references`

Common query patterns

Concurrency & transaction behavior

Encryption at rest

Secrets at the write boundary

Phase 2 (PostgreSQL) divergences

Managed-inference tables (Phase 2)

`provider_key_pool`

`subscriptions`

`quota_policies`

`usage_events`

`usage_aggregates_daily`

Uh oh!

FilesExpand file tree

database-schema.md

Latest commit

History

database-schema.md

File metadata and controls

Database Schema (Local SQLite)

Storage layout

SQLite type conventions

Tables

Entity relationships

Catalog tables

llm_providers

model_catalog

model_metadata

catalog_meta

agents

workflows

Run-history tables

runs

step_executions

messages

run_events

run_costs

Agent-session tables

agent_sessions

session_messages

session_costs

Media tables (1.AF)

media_objects

media_references

Common query patterns

Concurrency & transaction behavior

Encryption at rest

Secrets at the write boundary

Phase 2 (PostgreSQL) divergences

Managed-inference tables (Phase 2)

provider_key_pool

subscriptions

quota_policies

usage_events

usage_aggregates_daily

`llm_providers`

`model_catalog`

`model_metadata`

`catalog_meta`

`agents`

`workflows`

`runs`

`step_executions`

`messages`

`run_events`

`run_costs`

`agent_sessions`

`session_messages`

`session_costs`

`media_objects`

`media_references`

`provider_key_pool`

`subscriptions`

`quota_policies`

`usage_events`

`usage_aggregates_daily`