Skip to content

Warehouse

The DuckDB warehouse as rebuildable ETL output, and the doctrines that shape what it stores and how processes open it.

What the warehouse is

Rebuildable projection of harness history — not the system of record — how rows get in, and read surfaces that cannot write by construction.

Rebuildable ETL

The warehouse is a single-file DuckDB database under stockroom home ($XDG_DATA_HOME/stockroom or ~/.local/share/stockroom, overridable via STOCKROOM_HOME). It is rebuildable ETL from the harnesses' own session records — never the system of record. Ingestion re-derives rows from those sources; the warehouse is the queryable projection.

Ingest pipeline

Per-harness parsers emit shared dataclasses; the writer is the only SQL touchpoint. Default ingest is incremental (per-harness watermarks in _sync_state). The warehouse is allowed to outlive its sources: rows whose transcripts later vanish are never pruned. Observation-time fields (for example messages.first_seen_at) are not rebuildable from sources alone — that is why “delete and re-ingest” is not a free reset of every column.

Cursor has two discovery roots (IDE agent-transcripts and Agent CLI store.db chats) with independent watermarks; on session_id collision the chats store wins. CLI parsing is fail-soft: a locked/corrupt store.db or unrecognized root-blob layout skips that session without aborting the batch (and without advancing the chats watermark), while committed fixture tests fail loudly when the known layout drifts.

Read-only by construction

The read surfaces (query, semantic) open the warehouse read-only at the connection level — DuckDB itself rejects writes through them. “You cannot corrupt anything by querying” is a property of the connection mode, not of good manners.

What we store

Fidelity doctrines: which fields are kept, whole text at rest, uniform identity, honest workspace paths, and UTC timestamps.

Kept fields

Shared tables are sessions, messages, tool_calls, embeddings, and _sync_state. Prompts and responses are stored whole; tool inputs are kept; tool result payloads are dropped. Thinking/reasoning blocks the harness keeps separate are not stored. There is no raw mirror layer beside the typed model.

No truncation at rest

Kept fields are stored whole. Truncation is a read-time display bound so one fat column does not flood a context window. Elision markers report how much was withheld; the full content remains in the warehouse for a targeted re-fetch. Both read surfaces print through one render chokepoint (--detail / --format); see Embeddings for the search-side note and Advanced → CLI for flags.

Harness-labeled identity

Every row carries a harness column. Columns mean one thing independent of harness — extraction may differ; meaning must not. Identity is uniform: (harness, session_id) for sessions, message_id = '{session_id}#{ordinal}' for messages. Native harness identifiers are demoted to source_* provenance — kept for traceability, never used as join keys, because they exist at different grains and formats per harness. A value that only exists for one harness is NULL for the other, never fabricated.

sessions.entrypoint is nullable surface provenance within a harness, e.g.

  • Claude Code text UI vs Claude Code desktop app?
  • Cursor IDE vs agent CLI?

Values are taken from source data verbatim if present, synthesized based on our knowledge of harness' data provenance if not.

Workspace identity

sessions.project_id is the harness slug verbatim; sessions.cwd is best-effort real path, NULL when unknown. Path candidates are accepted only when encoding them for that harness reproduces the slug — verify, don't invert. Guessing a workspace from a slug without that check invents false identity.

sessions.workspace_key is a nullable cross-harness rollup key derived at ingest (per-harness strategies in stockroom.ingest.paths.workspace_key_for). Same machine + same absolute cwd ⇒ same key when both sides can derive it; different on-disk paths stay different keys; underivable inputs stay NULL. Chart Sessions by Project and SQL GROUP BY workspace_key share that key — project_id is never rewritten for merge convenience.

Dual-grain token usage

Token fields follow the same one-meaning-per-column rule as models (messages.model vs sessions.models):

  • Message grain — messages.input_tokens / output_tokens / cache_creation_tokens / cache_read_tokens. Claude keeps one row per content-block line and stores each API response's four counts once: the field-wise max on one row of the lines that share message.id and requestId, and NULL on the other rows of that response. A subagent line that repeats its parent's response id does not carry those counts. Cursor leaves the columns NULL. Claude rows stored before this rule keep the per-line counts they were written with once the transcript is gone. message.id and requestId were not stored on the row, and the transcript was the only copy. A user row or a time gap between lines that share a cache triple also happens inside one response, so that shape is not a way to regroup the old rows.
  • Session grain — the same four names on sessions, for harnesses that report conversation-level totals only. Claude and Cursor leave them NULL today. Ingest never invents session totals from message sums, and never invents per-message splits from session totals.

The read surface for conversation rollups is VIEW session_token_usage: *_from_messages (SUM of message columns), *_native (session columns), *_total (COALESCE(native, from_messages)), and token_grain (session | message | none). Totals are a warehouse rollup of reported fields, not a vendor invoice. Writers target base tables only; query the VIEW for session spend/usage. Message-level detail stays on messages.

UTC timestamps

DuckDB TIMESTAMP is timezone-naive; Stockroom's contract is that every persisted value is UTC wall clock. Clients that display times own timezone rendering.

How we open and evolve it

Connection chokepoints, concurrency, and forward-only schema migrations.

Concurrency and open paths

Every consumer reaches DuckDB through a warehouse chokepoint:

  • open() — path resolution and lazy migration. Writers take an exclusive coordination flock for the connection's lifetime; readers open read-only and back off to a typed busy error when the file stays locked.
  • open_current() — the dashboard exception: read-only, no migrate, typed stale/busy errors. A UI process must not become the migrator mid-browse.

Coordination uses fcntl.flock on a sidecar lock file; data integrity uses DuckDB's own file lock. Those are two layers with different jobs — do not collapse them into “just open the file.”

Migrations

There is one forward-only chain. The runner records schema_version.

open() runs the next migration, then the next, until the file is current.

Two kinds of change do not work that way.

A migration can need a dependency the engine does not load. open() cannot run that step. A file that has not reached the step stops. A file that is already past it runs the later migrations.

A change can be something an SQL statement cannot make. The storage format of the file is one. open() creates a new file in the engine's current format. An existing file keeps the format it has. Running the chain does not change that format.

When no warehouse file exists, open() does not run the chain from the start. It builds the file from a snapshot of the current schema, in the current storage format, then runs migrations added after the snapshot.

flowchart TD
  open["Writer open"]
  exists{"Warehouse file exists"}
  ready{"Past the step it cannot run"}
  stop["Stop and name the file"]
  chain["Run the chain forward"]
  build["Build from the schema snapshot"]
  later["Run migrations after the snapshot"]
  place["Rename into place"]

  open --> exists
  exists -->|No| build
  build --> later
  later --> place
  exists -->|Yes| ready
  ready -->|No| stop
  ready -->|Yes| chain

No warehouse file

The No arrow is a warehouse that does not exist yet, so there is no chain to continue.

open() does not start at the first migration. The early migrations include one that needs a dependency the engine does not load, and the engine cannot run that migration. Those migrations also cannot set the storage format. The format is chosen when the file is created, and an SQL statement cannot change the format later.

open() builds a snapshot of the current schema, already in the current storage format, and then runs only the migrations added after that snapshot.

The snapshot stays fixed, in the same way a migration stays fixed after the migration ships. A newer picture of the schema is a new snapshot file. The new warehouse records the snapshot in one schema_version row, and each later migration adds its own schema_version row.

open() writes the new warehouse under a temporary name and renames that file onto the warehouse path only while holding the writer flock, and only when no warehouse file is already there. A crash cannot leave a half-built file at the warehouse path.

Advanced → DuckDB says which DuckDB versions can open a file in the current storage format.

An existing warehouse

The Yes arrow is a warehouse that already exists. The chart then asks whether that file is past the migration the engine cannot run. That migration is the one that needs a dependency the engine does not load.

If the file has not reached that migration, the next SQL step would be the one the engine cannot run. The file holds no history, so open() stops, names the file, and tells you to move the file aside. open() does not change the file.

If the file is already past that migration, open() runs the chain forward from the schema version the file is on. The remaining migrations are SQL. SQL cannot change the storage format, so the file keeps that format. Those migrations leave the file with no vector index. Search ranks every vector exactly (Embeddings).

Released SQL

A migration that has shipped keeps its SQL. Comments may change. The schema snapshot follows the same rule. The check and the edit steps are in Contributing → Iteration.

Schema changes go through the open() chokepoint — Architecture does not list DDL here. The query-facing picture of the current schema is Advanced → Warehouse schema.