database-schema.md

Database Schema (Local SQLite)

Last updated: 2026-10-13

• 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

Loading

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.

The Node client also uses secure_delete = ON. After migration and session-effect high-water initialization, it clears all legacy session effect results in an owned transaction, then checkpoints WAL with TRUNCATE; every session effect sweep repeats that after-commit checkpoint, including an empty sweep. Logical suppression is immediate; physical erasure requires a successful checkpoint. A busy reader and pages freed before the upgrade remain accepted residuals. See effect-journal.md §11.

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 20 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. run_effects (ADR-0080) is a third, for a different reason: an unresolved effect row is exactly the record an operator needs after the run is purged, so a cascade would delete it at the worst moment.

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
    }
    run_leases {
        uuid run_id PK
        text owner_id
        integer generation
        integer expires_at
    }
    run_effects {
        uuid id PK
        text scope
        integer slot
        text tool_id
        text state
    }
    agent_sessions {
        uuid id PK
        int effect_turn_high_water
        int usage_invocation_high_water
        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_usage_coverage {
        text session_id FK
        text model
        integer pending_invocations
        integer unknown_calls
    }
    session_usage_invocations {
        text session_id FK
        text producer_id
        integer ordinal
        text dispatch_id
        integer round
        integer chain_attempt
        text model
        text coverage_state
    }
    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"
    runs ||--o| run_leases : "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"
    agent_sessions ||--o{ session_usage_coverage : "session_id CASCADE"
    agent_sessions ||--o{ session_usage_invocations : "session_id CASCADE"
    media_objects ||--o{ media_references : "handle CASCADE"

Loading

A run has AT MOST one lease, not exactly one — hence ||--o|. A lease row exists only while some process owns the run: it is created on acquire and DELETED on release, so most runs have none for most of their lifetime, and a finished run has none at all. The diagram read ||--|| and would have told a future store author to make the row mandatory.

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 dedicated provider API-key field holds only a reference; the resolved key is stored in the OS keychain (see keychain-and-secrets.md). Historical default_headers values may contain credentials and remain sensitive, as specified below.

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;

Interim provider-header policy

ADR-0108 tightens the exported ProviderStore.upsert API without changing the column or activating HTTP headers. The store captures one defaultHeaders admission branch before its clock callback, UUID allocation, SQL or retry work:

Supplied field	Result
No own or inherited defaultHeaders property	Preserve existing raw JSON exactly.
Own data property whose value is undefined	Preserve existing raw JSON exactly.
Own data property containing an empty ordinary (Object.prototype) or null-prototype object	Explicitly clear to canonical '{}'.
Any other shape	Refuse with ProviderHeaderPolicyError.

Every nonempty map is refused, including benign-looking literals. Empty arrays, custom instances, primitives, null, own accessors and inherited fields are refused. Any own key, including a symbol or non-enumerable key, makes a map nonempty. The field's descriptor and map prototype/own keys are inspected without invoking a field getter or caller serializer; reflection failures become the same fixed refusal. This narrow field gate does not own the rest of ProviderUpsert or sandbox JavaScript reflection traps. The captured branch is not reread after callbacks.

An admitted new row always writes '{}'. An existing row preserves its exact stored JSON bytes on omission/own undefined, inside the existing IMMEDIATE transaction; explicit clearing writes the constant '{}' without serializing the caller object. Later ordinary updates preserve the cleared value. This is a logical clear: SQLite pages, WAL files and backups are not certified erased. There is no automatic rewrite, scrub, migration or keychain copy. The existing provider remove-key command deletes the keychain key and clears its reference; clearKeychainRef clears only that reference. Neither clears headers. This step adds no header-clear CLI command. A trusted store caller can explicitly clear through upsert({ ...providerFields, defaultHeaders: {} }).

@relavium/db exports ProviderHeaderPolicyError and isProviderHeaderPolicyError from its public barrel. A newly constructed refusal has these fixed own data fields and no cause:

Field	Value
code	provider_header_policy
name	ProviderHeaderPolicyError
message	Provider headers are disabled until keychain custody is implemented.

The guard accepts an Error with those exact own data discriminants, including a compatible error from another bundle; it does not require this class's identity. Accessors do not qualify, and inspection faults return false. The CLI maps a recognized refusal to invalid_invocation (exit 2) using the fixed message rather than an incoming diagnostic. Header names, values and caller inspection causes are not retained by the store refusal.

Trusted get, list and upsert results still expose the validated legacy map in ProviderRecord.defaultHeaders; this compatibility field is sensitive, not a public projection. Stored JSON must be an object with string members; malformed JSON, arrays, null and non-string members throw a content-free TypeError with message Stored provider headers are invalid. and no original parse cause. Valid own keys, including __proto__, remain own data without mutating the output prototype. These corruption errors remain unexpected CLI internal failures (exit 1), not policy refusals or silent empty maps.

The provider-list human and NDJSON paths, with or without --verify, omit the header map and its names/values. Diagnostic projections must also omit them. The first-party host factory does not consume either new or legacy maps as HTTP headers, on default or custom endpoints. These output/wire limits do not remove the trusted store API data. The CLI database remains unencrypted under ADR-0050's sensitive-history posture; retained header credentials share that limit.

Future activation remains outside this wave and requires the keychain-reference schema, trusted-host egress resolution, header precedence/validation, content-free reference failures, safe projections and reviewed legacy migration/recovery policy specified by ADR-0108. The interim gate does not implement keychain-backed headers or historical cleanup.

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 — and NULL means unpriced, not free. ADR-0089 §4 replaced the old degrade-to-zero behaviour this sentence used to describe: a missing rate is now reported as an unpriced modality on both cost paths, counted in unpriced_calls, and refused under strict_cost_cap with a runnable remedy. A 0 written here is a stated free rate and is honoured as one; the two are no longer the same value. 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
token_rates_stated	INTEGER (bool)	NOT NULL DEFAULT 0 — did the USER state the token rates (migration 0016, ADR-0089 §4)? The sibling of cached_input_stated, for the same reason: the two rate columns are NOT NULL DEFAULT 0, so "never said" and "stated free" are the same bytes. 0 ⇒ a reader inherits the catalog's rates; 1 ⇒ the numbers are the user's own, zero included. Derived by the store from the presence of both rates, so no writer can forget it. It became load-bearing when a media-only models pricing invocation became legal — without it, adding an image rate to a catalog-priced model wrote 0/0 into a row that outranks the catalog, billing every token on that model at nothing
media_image_cost_microcents	INTEGER	NULL — µ¢ per output image (1.AF/D17). NULL ⇒ unpriced; an explicit 0 ⇒ stated free. The distinction is load-bearing and is why these are NULLABLE where the token columns are NOT NULL DEFAULT 0 with a cached_input_stated companion: a reader that collapses the two makes a modality the cost cap cannot enforce look like one that costs nothing (ADR-0089 §4). Written by models pricing --image; carried verbatim (null included) through ModelCatalogListing into ModelPricing.mediaOutputRates
media_audio_cost_microcents	INTEGER	NULL — µ¢ per output audio-second; same NULL vs 0 rule. models pricing --audio
media_video_cost_microcents	INTEGER	NULL — µ¢ per output video-second; same NULL vs 0 rule. models pricing --video
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.

"The exact graph that ran" includes MCP-discovered tool grants. A surface that augments the parsed workflow before starting the engine — relavium run unions each inline agent's tools grant with the tools its declared mcp_servers discovered — must freeze the augmented definition, not the authored one. It did not, until ADR-0083 §5: on every workflow with mcp_servers, the column recorded a graph that never ran, and a cross-process relavium gate rebuilt the run from it. The grants are part of workflow identity, so an MCP server returning a different tool set on resume is a divergence — and one that can only be detected if what was frozen is what was executed.

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.

The SQLite reader validates every stored row before applying tolerant unknown-event parsing or excluding genuine streaming events from a state/discovery fold. The raw JSON type must agree with event_type; a known event must also parse and agree with its run-id and sequence projections. A mismatch is the existing CorruptRunEventError on display, state reads, interrupted-run discovery and strict replay; a damaged SQL streaming discriminator cannot hide a known suspension and permit reconciliation to append a terminal. Genuine streaming events remain excluded from the returned state fold, but validating persisted history includes their JSON/schema cost. A matching unknown type retains read-only display/discovery tolerance, while strict replay refuses the unreadable log. Discovery remains aggregate fail-closed, with corrupt evidence retained and no partially trusted result; see the event contract.

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);

run_leases

Which process currently owns a run, and at which fencing generation (ADR-0079). Two relavium processes can otherwise resume one paused run and become two independent side-effect producers for it — both dispatching the same nodes and calling the same tools. The compare-and-append guard below stops the second one writing the same row; it does not stop either of them doing the work, because the effect happens before anything is written.

Its own table rather than columns on runs, because that row is a derived projection the event fold rewrites (applyDerived): mixing authoritative, non-derived ownership state into it invites a fold to clobber it. A table is also queryable — by a human diagnosing a stuck run.

Column	Type	Constraints
run_id	TEXT	PRIMARY KEY REFERENCES runs(id) ON DELETE CASCADE
owner_id	TEXT	NOT NULL — the owning engine's identity, one per WorkflowEngine
generation	INTEGER	NOT NULL — the fencing token; bumps on every successful acquire, but not monotonic across a run's life (release deletes the row, so the next acquire restarts at 1). The fence is (owner_id, generation) pair-equality plus fail-closed on a missing row — ADR-0079 §1's 2026-08-17 amendment
expires_at	INTEGER	NOT NULL — epoch ms, compared store-side against the store's injected clock
created_at	INTEGER	NOT NULL
updated_at	INTEGER	NOT NULL

The FK is why a fresh run's lease is created just after its run:started is folded rather than in the same transaction as ADR-0079 §3 first described: the runs row must exist first. The window is uncontended by construction — the runId came from ids.newId() moments earlier and no other process has seen it.

TTL 60 s, heartbeat every 20 s (RUN_LEASE_TTL_MS / RUN_LEASE_HEARTBEAT_MS in @relavium/shared): three missed beats permit a takeover — wide enough that a long provider call or disk pressure is not mistaken for death, narrow enough that a crashed run is not locked for more than a minute.

run_effects

The durable effect journal (ADR-0080; the contract, identities and state machine live in effect-journal.md). One row per effect OCCURRENCE, written by a prepare before an effectful tool dispatch leaves the process and updated by a settle after it returns or fails.

Column	Type	Constraints
id	TEXT	PRIMARY KEY (UUID)
scope	TEXT	NOT NULL — run:<runId>:<nodeId> or session:<sessionId>:<turn>, the correlation with the retry attempt dropped
slot	INTEGER	NOT NULL — which effect within the correlation (an ordinal across the turn's tool calls)
tool_id	TEXT	NOT NULL
tier	INTEGER	NOT NULL — 1 | 2 | 3; everything that ships is 3
state	TEXT	NOT NULL — prepared | dispatched | committed | ambiguous | needs_attention
args_digest	TEXT	NOT NULL — SHA-256 over canonical JSON of the effective args with every secret-tainted key removed before hashing
target_idempotency_key	TEXT	NULL — tier 1 only; what a safe retry reuses verbatim
result_json	TEXT	Always NULL for session: scopes, including legacy rows after open-time clearing; RUN scopes retain the bounded result when re-delivery is possible
attempt_json	TEXT	NOT NULL — the audit occurrence (node attempt, provider attempt, tool-call id, owning fence)
created_at	INTEGER	NOT NULL
updated_at	INTEGER	NOT NULL

-- THE dedup constraint: two processes preparing the same effect collide here, so one loses and learns
-- another attempt exists. That is what makes `prepare` a concurrency boundary rather than a log line.
CREATE UNIQUE INDEX idx_run_effects_identity ON run_effects (scope, slot, tool_id);
CREATE INDEX idx_run_effects_scope ON run_effects (scope);  -- the resume gate reads one correlation

No foreign key to runs, deliberately — see the ER note above. The scope is an opaque string rather than a run_id column for the same reason it drops the attempt: a SESSION effect has no run at all, and the node-retry attempt resets to 1 on both a crash-resume and a budget approval, so a key containing it would miss the row the gate looks for. Retention is stated in effect-journal.md §9 and is partly implemented, by design: the committed sweeps SHIP — a run's rows go when it reaches a terminal; a resumed session's captured rows go only after a successful all-history disclosure read and active notice delivery, while a never-resumed one-shot sweeps its owned rows at teardown. There is no completed-turn-count bound. Unresolved rows (prepared / dispatched / ambiguous / needs_attention) are never swept by age. That asymmetry is the contract, not a gap: an unresolved row is the record an operator needs, and it outlives its run deliberately, which is the same reason the table carries no foreign key to runs. (This paragraph previously said no sweep touches the table at all, which contradicted both the shipped effect-retention.ts and §9 of the effect-journal reference.)

Agent-session tables

These 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
total_conservative_microcents	INTEGER	NOT NULL DEFAULT 0 — the session's conservative total (ADR-0074 §1/§4): money a provider MAY already have billed for an attempt that returned no trustworthy usage. An ESTIMATE, deliberately apart from total_cost_microcents — it consumes cap capacity across a resume without ever inflating a reported cost. Single-writer (recordSessionConservativeCommitment), like its realized sibling
effect_turn_high_water	INTEGER	NOT NULL DEFAULT 0 (migration 0017) — durable effect identity, written only by the session effect-key allocation operations; never SET by session updates/turn flushes or exposed as the reconstructed max_turns count
usage_mutation_revision	INTEGER	NOT NULL DEFAULT 0 — database-maintained session business revision; safe integer, advances for every relevant INSERT/UPDATE/DELETE, including an equal-valued business assignment
usage_ack_revision	INTEGER	NOT NULL DEFAULT 0 — new writer's final acknowledged revision, never greater than usage_mutation_revision
usage_unverified	INTEGER (bool)	NOT NULL DEFAULT 1 — migrated and predecessor-inserted history is unverified; sticky once inherited or detected
usage_pending_invocations	INTEGER	NOT NULL DEFAULT 0 — possibly invoked token-provider attempts without disposition; never an observed-call count
usage_unknown_calls	INTEGER	NOT NULL DEFAULT 0 — invoked incomplete outcomes with unknown charge, separate from missing price and conservative money
usage_invocation_high_water	INTEGER	NOT NULL DEFAULT 0 — safe integer monotonic allocator across all producers of this owner; metadata only
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.

That invariant is realized-only, and stays so. agent_sessions.total_conservative_microcents is a separate aggregate reconciled by SUM(session_costs.conservative_microcents) (ADR-0074 §4), written only by recordSessionConservativeCommitment in the same one-transaction, two-additive-writes shape. The two totals are never added together: one is spend, the other an upper bound on spend that may already have been billed, and folding them would present an estimate as an invoice.

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
conservative_microcents	INTEGER	NOT NULL DEFAULT 0 — the per-model conservative attribution (ADR-0074 §4), on the SAME (session_id, model, is_legacy) key as the realized figure so the two are joinable per model. An estimate; a surface renders it as estimated, possibly billed and never sums it into cost_microcents
call_count	INTEGER	NOT NULL DEFAULT 0 — every cost:updated folded into this row, priced or not. Since ADR-0074 §4 a row may exist with call_count = 0 and is_legacy = 0 — a conservative commitment made before any realized egress on that model. Any repair/audit query that treated call_count = 0 as "not a real egress row" must account for it
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.

Session usage coverage foundations — ADR-0104

Migration 0018_windy_rhodey.sql adds the five owner columns and two coverage-only tables below, without rebuilding any existing table or changing historical money, token, transcript or conservative-commitment rows. Its SQLite trigger DDL is migration-owned because Drizzle's table snapshot does not represent triggers. The schema-sync generator checks columns/tables; native migration and trigger-inventory tests also check the trigger mechanism.

W8 Steps 5–6 activation: the revision/acknowledgement protection remains installed on every business writer. Additive migration 0019_regular_stingray.sql adds agent_sessions.usage_invocation_high_water (safe integer, default zero, monotonic) and session_usage_invocations.coverage_state (pending or incomplete). The generated table rebuild copies existing inert invocation rows as pending; historical migrations and money are unchanged. Rebuild CHECKs use unqualified column identifiers: a temporary-table qualifier can fail during rename on SQLite 3.54. pnpm --filter @relavium/db db:generate normalizes only newly generated rebuild SQL, retaining Drizzle's schema snapshots and leaving applied migrations untouched. The upgrade control retains 0018 rows, money, monotonic identities, constraints and foreign-key enforcement through the real migration batch. Foreign keys stay enabled throughout; toggling that PRAGMA inside Drizzle's transaction would be ineffective. createSessionUsageStore now brackets invocations with live receipt authority. The W8 closing register owns shipping-host, crash, downgrade and every-reader evidence beyond this storage foundation.

createFreshSession accepts a seed with identity, agent/context and creation metadata only. It refuses supplied historical totals/lifecycle/coverage fields and any physically existing id, including a tombstone hidden by loadSession. In one transaction it inserts an empty active owner, clears only that fresh insertion's default taint and acknowledges the insertion revision. It never upserts/replaces, and generic createSession, resume, reseat, imported history and one-shot identity existing tombstones cannot acquire this privilege. A timestamp or zero numeric total is not proof of freshness.

loadCoverage reads aggregate and per-model coverage through one transaction handle and never acknowledges a revision. Its UsageCoverage is defined by the shared schema and the event contract; effective unverified is persisted taint OR usage_mutation_revision != usage_ack_revision. Every model inherits that effective owner taint, including a historical money row with no new coverage row. The returned models array is the union of money-only and coverage-only raw models; missing coverage rows carry zero known pending/unknown counts and the aggregate's effective unverified. loadSessionCostBreakdown returns { totalCostMicrocents, rows, coverage, models } from one SQLite snapshot, so a concurrent settlement cannot tear numeric amounts from their coverage. Absence never proves complete history. There is no independently clearable per-model taint or fabricated unknown-call count for an old writer.

Entry / operation	Committed result	Coverage interpretation
Migrated owner	(revision=0, ack=0, taint=true); amounts unchanged	Legacy/unverified, even if all amounts are zero
Predecessor INSERT	Default taint true; insertion advances revision without acknowledgement	Unverified
Explicit fresh-owner creation	Insertion revision acknowledged; taint false; pending/unknown zero	Complete zero, only before that new owner's first eligible invocation
Old write from (10,10,false)	(11,10,false)	Read classifies mismatch as unverified; it does not repair/acknowledge
New write entering (11,10,false)	Entry mismatch first latches taint; own writes advance to final revision, e.g. (12,12,true)	Acknowledged but permanently unverified
Subsequent new write or estimate release	Final revision acknowledged, prior taint retained	Cannot recover complete/free coverage
Business/acknowledgement failure or crash before COMMIT	Business writes, taint and acknowledgement all roll back	Original mismatch remains visible
Read	No write or acknowledgement	Classifies mismatch without laundering it
Pending/disposition/retirement transaction	No business revision increment; outer wrapper latches entry mismatch and acknowledges final revision	Prior taint and aggregate uncertainty survive
Complete correlated settlement	Coverage change and the existing actual-money writes commit together	Exact live authority only; duplicate cannot replay money

All session business store operations use the package-private withSessionUsageWrite wrapper: refuse nested transactions; one outer BEGIN IMMEDIATE; read all affected owners' entry revision, ack and taint before business writes; latch mismatches; perform writes; acknowledge each final trigger-produced revision; commit. All statements use the transaction handle. withBusyRetry wraps the whole outer transaction with the existing bounded deterministic policy. A lock retry cannot repeat only the acknowledgement or accidentally send a provider request. Even a no-op conservative release visits entry classification before returning zero. A multi-owner writeTurn classifies all message/session owners before the first mutation.

The concrete SQLite trigger inventory is:

Trigger	Covered columns / effect
session_usage_owner_insert	Every owner INSERT advances the inserted row's revision
session_usage_owner_update	UPDATE OF id, agent_id, agent_slug, agent_snapshot, title, model_id, working_dir, git_ref, fs_scope_tier, status, context_json, total_input_tokens, total_output_tokens, total_cost_microcents, total_conservative_microcents, exported_workflow_path, deleted_at, created_at, updated_at advances that owner once
session_messages_usage_insert / _update / _delete	Every transcript INSERT/UPDATE/DELETE, including canonical content and denormalized display metadata; deletion advances OLD owner; reparenting advances OLD and NEW owners once each
session_costs_usage_insert / _update / _delete	Every monetary/model/token/counter/attribution/timestamp INSERT/UPDATE/DELETE, with the same old/new owner rules
session_usage_bounds_insert / _update	Validate the five initial owner bookkeeping columns; revision/ack/counts are integers in [0, 9007199254740991], ack ≤ revision, taint is 0/1; these guards never advance a revision
session_usage_high_water_insert / _update	Validate integer usage_invocation_high_water in [0, 9007199254740991]; updates cannot decrease it; no business revision increment

The business UPDATE trigger is column-targeted, not a comparison of changed values: adding zero or flushing the same status is still an old business write. updated_at is included because a usage-less or aborted turn may leave only a session flush. Transcript's denormalized content, tool_calls, tool_call_id, name, finish_reason, model and timestamp are business data, not revision bookkeeping. All six usage owner fields and the two coverage tables are excluded from revision advancement, as is allocator-only effect_turn_high_water (including open-time seeding and sweep preservation). The compatibility one-shot tombstone INSERT follows unverified defaults. The explicit createFreshOneShotOwner({ sessionId, now }) operation uses the same original physical-id and historical-effect-scope refusal in one transaction, inserting the fresh hidden tombstone with complete-zero coverage and final acknowledgement. It returns an opaque SessionUsageOwner with effectTurnKey; only that exact connection-bound object can be passed as the second argument to beginInvocation for the deleted owner. Generic admission refuses deleted rows. A copied or reloaded owner gains no authority, and no transcript, visible/resumable session, prompt or context is introduced. The existing one-shot reservation API retains its numeric compatibility behavior.

SessionStore.visitSessionSummaries(visitor) is the full discovery/accounting read for chat-list --json: a synchronous visitor exhausts indexed 256-owner keyset pages ordered by (updated_at DESC, id DESC) in one read transaction. Each page reads owner identity, known money and coverage, then groups the independent unpriced-call counters for that page. It loads no transcript, context or per-model attribution and holds no all-session result array. The page bound does not truncate results; all pages and their accounting use the same SQLite snapshot. Visitor failures release the read transaction and propagate unchanged. listSessions retains its full-record/top-N compatibility interface for human listing and Home.

Permanent revision controls derive business columns from the Drizzle owner schema, excluding only the six usage bookkeeping fields and allocator-only effect_turn_high_water. The migrated native column set and actual UPDATE OF trigger inventory must match that derived set; every business column is exercised even with an unchanged assignment. A future unclassified native column or a missing trigger column fails the structural gate. Settlement guards check exact session/producer/ordinal binding and indexed plans for SELECT, UPDATE and DELETE, including cached statements; owner-prefix mutations are independently rejected.

loadSessionCostBreakdown reads the retained owner's numeric total directly by physical id in the same read transaction as per-model money and coverage. Hidden one-shot and soft-deleted owners retain their actual known sum; the visibility filter used by loadSession, loadFull and listSessions cannot replace it with zero. This accounting-only snapshot grants neither transcript discovery nor admission, resume or receipt authority. An absent owner remains unverified with no invented money.

Triggers never price, reconcile, change actual/conservative money, or acknowledge. Revision exhaustion refuses the entire business statement with the fixed session_usage_revision_exhausted marker; store boundaries expose SessionUsageError with revision_exhausted. There is no wrap, floating-point increment or saturating acknowledgement. An active outer transaction is refused with transaction_active, because a savepoint release is not an outer durability boundary. A child cascade never creates an absent parent. Explicit owner deletion cascades its coverage; generic reinsertion remains unverified.

session_usage_coverage

One coverage aggregate per raw model and session, separate from the money key and its legacy aggregate discriminator. Neither a model-catalog FK nor a reserved string is its identity. This keeps pending markers out of session_costs.call_count and preserves coverage independently of an old writer's monetary child deletion. Pending admission, disposition and complete settlement update aggregate and model counters atomically, without changing the sole actual-cost writer or money arithmetic.

The model breakdown joins this coverage table with non-legacy session_costs models. An is_legacy aggregate represents unattributable historical money, not a model or a zero-cost usage-only call. Its amount and unavailable-breakdown disclosure remain visible in the monetary rows. The discriminator is the flag, never the model string: a real money or coverage model literally named like the legacy sentinel remains attributable. Historical monetary rows retain their exact raw model identities under the existing money-read contract, even when a name exceeds the new coverage/invocation bound. That bound applies to new coverage records, not retroactively to valid retained money. Such history remains unverified; reading it neither grants a receipt nor permits an over-bound new invocation.

Column	Type	Constraints
session_id	TEXT	NOT NULL REFERENCES agent_sessions(id) ON DELETE CASCADE
model	TEXT	NOT NULL; CHECK length between 1 and 4096; shared model validation at the read boundary
pending_invocations	INTEGER	NOT NULL DEFAULT 0; integer CHECK in [0, 9007199254740991]
unknown_calls	INTEGER	NOT NULL DEFAULT 0; integer CHECK in [0, 9007199254740991]

CREATE UNIQUE INDEX idx_session_usage_coverage_model
  ON session_usage_coverage (session_id, model);

session_usage_invocations

Bounded correlation storage for unresolved or still-authorized invocations, not a session attempt ledger or second money deduplication key. The shared invocation schema owns the identity contract. Reading a row grants no invocation, allowance or receipt authority.

Column	Type	Constraints
session_id	TEXT	NOT NULL REFERENCES agent_sessions(id) ON DELETE CASCADE
producer_id	TEXT	NOT NULL; CHECK length between 1 and 256
ordinal	INTEGER	NOT NULL; integer CHECK in [1, 9007199254740991]; minted from the session-wide high-water mark; never caller-supplied
dispatch_id	TEXT	NOT NULL; CHECK length between 1 and 256
round	INTEGER	NOT NULL; integer CHECK in [0, 9007199254740991]
chain_attempt	INTEGER	NOT NULL; integer CHECK in [1, 9007199254740991]; not the node retry counter
coverage_state	TEXT	NOT NULL DEFAULT pending; CHECK pending or incomplete
model	TEXT	NOT NULL; CHECK length between 1 and 4096

CREATE UNIQUE INDEX idx_session_usage_invocations_identity
  ON session_usage_invocations (session_id, producer_id, ordinal);

beginInvocation({ sessionId, model, producerId, dispatchId, round, chainAttempt }) mints an owner-wide ordinal and commits its pending row plus both counters before returning an immutable SessionUsageReceipt. Its fields disclose correlation only; a package-private WeakMap binds the exact receipt object to the originating DB connection. A copied/serialized receipt is refused. No read/resume API recreates authority, and admission never accepts an old ordinal for re-admission.

disposition(receipt, disposition) changes pending to one unknown call for incomplete evidence, or removes pending for proven non-invocation/official refusal without adding money or a call. An incomplete row remains available for authorized late complete evidence. Complete evidence is accepted only by SessionStore.recordSessionCost({ ...entry, usageReceipt }), which resolves pending/unknown and writes both monetary aggregates inside its existing sole transaction. The method returns false for an already consumed/retired receipt, before another money write. It consumes in-memory authority only after successful commit, so an ACK failure rolls back coverage, money and revision together and permits the original receipt to retry. An uncorrelated legacy cost API call preserves numeric behavior and latches unverified coverage; native separate-endpoint volume accounting explicitly supplies accountingBasis: 'native_media' and retains its basis.

retire(receipt) ends positive live authority and deletes that identity, preserving pending/unknown aggregate counts. Settled identities have no lifetime registry or permanent per-producer tombstones. The single high-water mark prevents reusing prior ordinals; the WeakMap has no enumerable completed history. Partial/missing duplicate callbacks add no durable record. Metadata is bounded by models, unresolved identities and still-authorized late receipts. Permanent native SQLite query controls validate the exact session/producer/ordinal bindings on every receipt lookup and inspect the identity-index SEARCH plan once per SQL shape. An owner-prefix scan cannot satisfy that structural control merely by preserving monetary outcomes. Retired duplicates perform no additional receipt lookup or actual charge; legitimate outer revision/acknowledgement SQL remains permitted.

The separate 100,000-settlement scale control pins 100,064 admissions, 100,000 retirements, 64 unresolved identities, three model aggregates and a 100,064 high-water mark. It verifies 50,000 unknown calls, then refines one pending control to complete once, leaving 63 pending and 50,001 observed calls without changing the unknown aggregate. All 150,002 receipt lookups bind the exact identity; query-plan inspection is cached by SQL shape, while parameter validation runs every time. This state bound does not promise a constant-size append-only run ledger or automatic deletion of retained history.

No session lease is introduced. Resuming/reseating a session cannot declare another live instance's pending rows orphaned. ADR-0103's retained exact-fence receipt authority applies to runs; genuine session unresolved state must remain until its producing authority is positively ended. No constant bound is claimed for genuinely unresolved or receipt-eligible work.

An older executable cannot display these coverage fields or enforce the new accounting contract. Active downgrade execution is unsupported. Revision protection retains uncertainty after durable old writes; it cannot reconstruct usage/calls that left no durable trace, and existing conservative commitments do not cure the predecessor's synthetic-zero release behavior.

Incremental strict run replay

RunHistoryReader.visitRunEventsForReplay(runId, visitor, { streamingIncluded, visitSteps }) uses indexed 256-row sequence batches inside one SQLite read transaction. The method returns the typed RunRecord | undefined for that same snapshot: its first SELECT reads the owning runs row and pins the snapshot before visiting event rows. Coverage readers must qualify this returned row's totalCostMicrocents, not a prior listRuns/loadRun discovery amount. The existing stored-money projection remains authoritative; the reader does not introduce a second actual-cost fold. Raw replay remains keyed by run id, including retained deleted rows, and visibility remains the caller's responsibility. An absent owner returns undefined after strict validation of any stored rows. Its synchronous visitor folds each validated event without materializing the full prefix. Every stored row is validated before any stream exclusion; an unknown discriminator raises UnreadableRunEventLogError, and malformed or mismatched projections raise CorruptRunEventError. The caller discards its partial fold on failure. The event contract also classifies inconsistent coverage sequences as per-run corruption; strict reconstruction refuses them, while discovery preserves healthy neighbors with explicit degradation for the affected run. The original array-returning strict read remains available for compatibility; the production checkpointer uses the incremental port. Concurrent WAL writes cannot enter the snapshot halfway through replay. This does not compact or delete the authoritative append-only event log.

The optional synchronous visitSteps callback receives the existing per-node step projection after strict event validation succeeds, read through the same transaction handle as the owner and event batches. Status uses it to pair each step's amount and lifecycle with that snapshot's coverage. Each node:started establishes that node's coverage under the owner's current basis, including native-media and ordinary nodes without token-invocation events. A pre-basis node remains unverified; a later owner basis cannot retroactively qualify its history. It must not join a later standalone loadStepExecutions result to the earlier fold. Other visitors do not query or allocate step rows. A strict failure invokes no step callback; display degradation may still show standalone step detail only with explicitly unverified coverage and an unavailable usage marker. This grants no resume or mutation authority.

Session content parts

SessionMessage.content: SessionContentPart[] is the canonical body in content_parts. This strict session-only union retains text, signature-less reasoning and handle-only user media, and replaces raw tool values with the following structural parts:

Part	Required fields	Meaning
tool_call (assistant row)	type, id, name, argsBytes	Registry-resolved tool id, or fixed unknown_tool; UTF-8 bytes of the JSON arguments issued by the model. No arguments, signatures, provider ids or digests
tool_result (tool row)	type, toolCallId, resultBytes, outcome	Matching engine id; UTF-8 bytes of the bounded model-facing JSON result (0 when the result is absent), rather than the full host result or event summary; ok / error / denied / cancelled

An optional result media array contains strict handle-only media metadata (type, mimeType, source, optional byteLength / durationMs). It has no filename or transcript field. Sizes are non-negative safe integers. Names have the admitted tool charset [a-zA-Z0-9_-], at most 128 characters; the registry outcome, not syntax alone, establishes resolution. The completed-turn producer derives these fields from actual registry outcomes. A recovered host error without its own tool id retains the exact call name only after verifying registry membership; unresolved names still use the fixed marker. A recovered policy denial remains denied, while another recovered dispatch failure is error. An unrecovered failure has no completed-turn transcript. Byte measurement preserves native JSON encoding, including string/key escaping, and counts deeply nested plain JSON iteratively when native serialization exceeds the call stack; structural metadata does not prevent correction of valid model JSON. Generic durable run/event/IPC tool parts retain their existing shape.

The engine id is session-tool:<effect-turn-key>:<slot>, with canonical decimal safe integers, a positive turn key and a non-negative whole-turn slot. No provider- or model-chosen string is part of it. The key is separate from the reconstructed hard-turn-cap counter. The host allocates it in one BEGIN IMMEDIATE transaction, advancing effect_turn_high_water before dispatch; errors, aborts, crashes and missing transcript writes do not return a key. Exhaustion or an invalid persisted mark fails closed. Reservation must own its outer commit: an already-open transaction is refused with transaction_active, because a savepoint release cannot prevent an outer rollback from erasing an issued key. Open-time initialization, under the migration lock, seeds legacy rows from all historical terminal assistant rows (empty text counts; tool preambles do not), ignoring compaction, and from the greatest retained session effect scope key before any cleanup. Subsequent journal sweeps cannot lower it. Open-time discovery batches uninitialized sessions and inspects only those with historical assistant or matching effect evidence; empty idle sessions remain zero without per-session history reads or writes. An invalid legacy scope or historical assistant payload leaves only its affected session uninitialized, with the original evidence intact. Opening the database and initializing healthy sessions still succeeds; reserving an effect key for the affected session continues to refuse with history_invalid. No inferred floor, deleted evidence or reused identity repairs that corruption. Before deleting captured committed rows for an existing session, the sweep repeats the same initialization inside its deletion transaction. Invalid history refuses the sweep and preserves all rows; a trustworthy floor commits atomically with all deletion chunks. Rollback cannot consume evidence without retaining its floor. Row-less orphan privacy erasure remains available because those scopes cannot be resumed or allocated. Other initialization faults still fail the operation. Allocation repeats initialization for a new session whose mark is still zero. The required engine host allocator is late-bound by each interactive persister. The engine caches one key for idle ! commands and the next model turn, consuming it on every model-turn exit. Commands use disjoint negative slots and engine ids session-command:<key>:<ordinal>; model calls use non-negative slots across all tool rounds. Per-call ids reach run_effects.attempt_json and the structural transcript unchanged; provider ids remain within the live protocol.

The fresh-only reserveOneShotEffectTurnKey owns an outer BEGIN IMMEDIATE and commits an already-tombstoned, minimal identity row with high-water 1 for agent run. It stores no snapshot, context content, prompt or transcript. Existing live/tombstoned ids and row-less effect scope collisions are refused. The tombstone stays absent from listing, resume and export and is retained after teardown; normal allocation still refuses missing/deleted sessions.

All supplied scalar metadata is validated on write and read: content equals the canonical text parts joined with two newlines; tool_calls equals the canonical structural call array; name matches a single call; tool_call_id matches a single result; finish_reason belongs to the fixed stop-reason vocabulary on an assistant row. Absent projections are NULL. Unknown metadata/part fields, raw tool values and malformed JSON are refused, never stripped. Boundary errors carry fixed codes rather than raw JSON/parser/unknown-property diagnostics. Consistent legacy scalar metadata remains readable without rewriting it; a mismatch refuses the affected session rather than relaxing the structural boundary. Money attribution remains solely in session_costs.

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);

Media metadata admission

ADR-0106 separates mandatory pre-publication metadata admission from post-ACK best-effort reference retention. MediaReferenceStore.admitProducedMedia(batch) synchronously owns and validates every descriptor before deduplicating or entering SQLite. recordObject uses the same singleton-batch path. One BEGIN IMMEDIATE, with the existing busy retry, checks and writes the entire batch. Its timestamp is captured once and remains stable across retries. Statements use the transaction handle. There is no schema migration or second descriptor DTO.

For a given handle, canonical MIME, modality and actual nonnegative safe byte count are immutable, including on soft-deleted rows. The supported lexical aliases share canonical identity; alias normalization cannot relabel an incompatible format. Optional duration is a positive safe integer on audio/video only. Unknown duration may be enriched, equal known duration is idempotent, absence preserves known evidence, and conflicting known duration refuses. Compatible admission refreshes last_referenced_at and clears deleted_at; conflict changes neither.

A conflict in the final item rolls back earlier insertion, enrichment, resurrection and cursor refresh in that batch. Admission adds no media_references rows or read grants. The engine awaits it before state/save_to and before durable append. Append failure may leave compatible, unreferenced object metadata for ordinary grace GC. After ACK, recordRunMedia and reclaimRun retain their existing best-effort lifetime semantics; their failures cannot revoke publication. Byte/format verification and exact host shapes live in the media publication contract.

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 still use withBusyRetry where failure is user-visible data loss. ADR-0104 exception: session creation/update/message append now perform entry classification and final revision acknowledgement as well, so these formerly single-statement operations use the session usage outer transaction.
• 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.
• The durable append is compare-and-append (ADR-0078 §2). persistEvent takes an optional DurableWriteContext carrying expectedLastSequenceNumber — the sequence the caller last asked to append, not the last it saw succeed. When present, the store reads max(run_events.seq) for that run inside the same BEGIN IMMEDIATE transaction, through the transaction handle, and refuses the append with a typed AppendConflictError when the two differ. UNIQUE(run_id, seq) bars a duplicate and says nothing about order or holes; this is what makes the committed log a prefix of what was asked rather than merely a set. Three properties are deliberate: the read is max(seq) rather than a denormalized runs.last_event_seq column, so there is no migration and no second source of truth that can drift from the rows; the check runs before the derived runs/step_executions/run_costs writes, so a refusal leaves nothing behind; and AppendConflictError is not in the retryable-code set above — a conflict is a stale belief, not lock contention, and retrying it five times would waste five transactions and report the wrong cause. The engine exempts the run TERMINAL from the guard, because exactly-one-terminal (ADR-0036) outranks it; a terminal the store will not take is ADR-0078 §4's outbox to own.
• The same write is fenced by run ownership (ADR-0079 §2). DurableWriteContext carries a second, independent claim beside expectedLastSequenceNumber: a fence of { ownerId, generation }. When present, the store reads the run's run_leases row inside the same BEGIN IMMEDIATE transaction, through the transaction handle — outside it, the read and the write would be two statements another process could interleave, which is the exact race the check exists to close — and refuses with a typed LeaseFencedError when the owner or generation differs, or when the row is absent: a writer that cannot prove ownership fails closed. It is checked after the append guard, because a stale belief about the log is the more specific diagnosis when a writer has both problems. The two fields are independent because the run TERMINAL carries the fence without the append guard — exempt from one, not the other. An absent fence is a pass rather than a refusal: a caller holding no ownership claim has nothing stale to catch. Like AppendConflictError, LeaseFencedError is not in the retryable set — a stale fence is an expected refusal, not lock contention.
• 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. Relavium-managed provider keys stay in the OS keychain (ADR-0006) and a secret-typed input persists only as its { secret: true, ref } placeholder (ADR-0036). This custody guarantee does not imply that the file contains no credentials or sensitive content. The retained-content limits follow ADR-0050's note of 2026-09-14: tool-I/O redaction is shape-based and applied upstream to event copies, not by the bus; and user-injected content plus a committed tier-3 effect's bounded result are genuinely at rest (a run's until that run's terminal sweep). W7 Step 4, merged to main in PR #90 on 2026-10-10, implements ADR-0098: session effect results are SQL NULL, legacy result values are cleared, and sessions never replay them. 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: it does not re-mask opaque JSON or implement a runtime content-secret detector. Protected input slots are already { secret: true, ref } placeholders, and tool event copies receive the upstream shape-based scrub. The package's secrets fixture (run-history-store.test.ts) checks preservation of already-masked placeholders supplied to the writer. It does not exercise upstream input masking or tool-event sanitization, or prove that arbitrary user, model, tool or completed-output text contains no sensitive data.

The retained run_events.payload_json, step input/output/error JSON and workflow snapshots must therefore be read with the event security boundary and ADR-0050's at-rest correction. Raw provider credentials are not a history field, and declared secret inputs are recorded only as masked slots; arbitrary retained content can nevertheless contain credentials or other sensitive material. A future desktop/cloud writer inherits these upstream field protections, not a promise that a pass-through writer performs universal content redaction.

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.
• Session usage revision protection (ADR-0104): PostgreSQL must implement the same entry-mismatch/sticky-taint/final-acknowledgement transition table with dialect-specific triggers and locking/migration tests before that persistence target is enabled. SQLite's migration-owned trigger DDL is not portable by driver substitution. This local W8 foundation implements/tests SQLite only; it claims no PostgreSQL implementation or tested parity.
• 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;