Texarkanine/stockroom

sr-query

sr-query runs read-only SQL against the stockroom warehouse — the single-file DuckDB database of your captured Cursor + Claude Code history. The surface is read-only by construction: DuckDB rejects any write attempted through it, so you cannot corrupt anything by querying.

When to use sr-query

Reach for sr-query when the question has a known shape and the answer is an exact or structured lookup over fields you can name:

• A specific row by id (WHERE message_id = '…', WHERE session_id = '…').
• Filters, counts, and rollups (COUNT(*), GROUP BY harness, WHERE harness = 'claude', token sums, date ranges).
• Per-session token rollups via VIEW session_token_usage (prefer this over hand-rolled SUM on messages).
• Joins across sessions / messages / tool_calls.
• "How many", "which sessions", "list the tool calls", "what models" — anything you can express as SQL over named columns.

Do not use sr-query for meaning-based recall ("find conversations about flaky tests"). SQL ILIKE is a literal substring match, not semantic search — for meaning, use the sr-semantic skill. When you are not sure which is right, that judgement belongs to the sr-search skill.

How to invoke the engine

stockroom query "SELECT DISTINCT harness FROM sessions ORDER BY harness"

You can also pipe SQL from stdin with -:

echo "SELECT count(*) FROM messages" | stockroom query -

If command -v stockroom fails, the machine isn't set up yet: tell the user to run the sr-initialize skill, and don't attempt any other invocation.

Output discipline: --format and --detail

The defaults are already safe for an agent — a bare call gives bounded, parseable output — so reach for the flags only when a situation calls for it.

--format — output shape, default tsv

Value	Shape	Use it when
tsv (default)	Header row + tab-separated rows, no count trailer	Default. Stream-friendly for you and for unix pipes (cut, awk, wc -l).
json	A single {"columns": [...], "rows": [...]} object	A user asks for structured/machine output, or you want to hand it to jq.
table	Column-aligned ASCII with a (N rows) trailer	A user asks for something human-readable / a copy-paste command to look at.

Lead with the default tsv. Offer --format table or --format json when the user asks for human-readable or structured output — "these other shapes exist if you want them."

--detail — per-field width, default snippet

Wide string fields (notably messages.text and tool_calls.tool_input) are truncated at read time so one fat column can't flood your context. Truncation is display-only — full text is always retrievable.

Value	Budget per field	Use it when
compact	~40 chars	Scanning many candidate rows cheaply before picking one.
snippet (default)	~120 chars	Default. Enough to recognize a row without dumping it.
full	unbounded, single-line	You need the whole field length, and single-line collapse is fine (tables/TSV stay safe).
raw	unbounded, exact whitespace	You need text as stored — newlines and internal spaces intact. Prefer with --format json.

An over-budget field is elided with a marker reporting how many characters were hidden, e.g. …(+2284). That marker is your signal that more exists: if you actually need it, re-fetch just that field for the specific row at --detail full (length) or --format json --detail raw (exact whitespace) — see guardrails.

Guardrails

These are the failure modes this skill exists to prevent:

• Don't blow out your context. Never SELECT * (or a wide text column) at --detail full/raw across many rows. Instead: scan narrow at --detail compact/snippet with an explicit column list and a LIMIT, identify the row you want, then re-fetch only that row's wide field with a WHERE on its id. A single SELECT text FROM messages WHERE message_id = '…' is cheap; SELECT * FROM messages --detail full is a context bomb. When you need exact whitespace (markdown tables, code blocks), use --format json --detail raw.
• It is read-only — never attempt writes. INSERT / UPDATE / DELETE / CREATE all fail (query failed: …). Do not retry a write through a different phrasing; the surface only interrogates.
• tool_input is heterogeneous JSON. Its keys vary per tool (Shell has command, Read has path, …), so a naive tool_input->>'key' can raise a cast error. Extract safely by filtering tool_name in a subquery/CTE first and using the explicit function:

stockroom query "SELECT json_extract_string(tool_input, '\$.command') AS cmd
  FROM (SELECT tool_input FROM tool_calls WHERE tool_name = 'Shell') LIMIT 5"

Handle errors without thrashing

Each failure is a clean stderr message + exit code — read it and take the matching action, don't loop:

Message	Exit	What it means / next action
error: empty query (…)	2	No SQL was passed. Provide a statement.
error: no warehouse found at … — run \stockroom ingest` first`	1	The warehouse hasn't been built. Tell the user to run stockroom ingest (or sr-initialize if the machine was never set up); don't retry the query.
query failed: …	1	Invalid SQL, or a write was rejected. Fix the statement (read the DuckDB message) — don't re-run the same SQL.

What's in the warehouse

Discover the live schema first — this stays correct as the schema evolves:

stockroom query "SELECT table_name, column_name, data_type
  FROM information_schema.columns ORDER BY table_name, ordinal_position"

Quick reference of the load-bearing columns as of migrations 0001–0008 (confirm with the introspection query above):

• sessions — one row per conversation. harness ('cursor'|'claude'), session_id, project_id (verbatim project-dir slug — the grouping key; for Cursor CLI chats this is the chats hash directory), cwd (real path, may be NULL), workspace_key (cross-harness path rollup, may be NULL), entrypoint (surface provenance from the harness when known; may be NULL — use SELECT DISTINCT entrypoint rather than guessing literals), models (VARCHAR[], Cursor only), title, git_branch, is_subagent, parent_session_id, started_at, ended_at, source_mtime (source transcript mtime at last ingest), and four nullable session-grain token BIGINTs (input_tokens, output_tokens, cache_creation_tokens, cache_read_tokens — filled only when a harness reports conversation-level usage; Claude/Cursor leave them NULL). PK (harness, session_id).
• messages — one row per turn. message_id = '{session_id}#{ordinal}' (uniform across harnesses), parent_id, ordinal, role ('user'|'assistant'), text (whole; thinking is not captured), model (per-message, Claude only), ts, first_seen_at (when stockroom first observed the message), and four token BIGINTs: input_tokens, output_tokens, cache_creation_tokens, cache_read_tokens (Claude message usage; Cursor NULL). PK (harness, session_id, message_id).
• session_token_usage — VIEW over sessions + message token sums. Prefer this for conversation rollups. Columns: *_from_messages (SUM), *_native (session columns), *_total (COALESCE(native, from_messages)), token_grain ('session'|'message'|'none'). Totals are warehouse rollups of reported fields, not vendor invoices. Do not also SUM message tokens on top of *_total.
• tool_calls — tool inputs only (never outputs). message_id (the turn that emitted it), ordinal, tool_name, tool_input (heterogeneous JSON, stored whole — see the guardrail). PK (harness, session_id, message_id, ordinal).
• embeddings — per-chunk vectors for semantic search (owner_table, owner_id, chunk_index, embed_model, vector FLOAT[384]). You rarely query this directly — use the sr-semantic skill.
• _sync_state — ingest watermark bookkeeping; not interesting to query.

One identity rule worth knowing before you write a join: always join on the uniform message_id / (harness, session_id), never on the source_* provenance columns. A value that only exists at one grain per harness is honestly NULL for the other (e.g. messages.model is Claude-only; sessions.models is Cursor-only; the same dual-grain honesty applies to tokens).

Worked examples

All verified against a real warehouse.

# Which harnesses are present? (tsv default)
stockroom query "SELECT DISTINCT harness FROM sessions ORDER BY harness"

# Session counts per harness, human-readable for a user:
stockroom query --format table "SELECT harness, count(*) AS n FROM sessions GROUP BY harness ORDER BY n DESC"

# Busiest tools:
stockroom query "SELECT tool_name, count(*) AS calls FROM tool_calls GROUP BY tool_name ORDER BY calls DESC LIMIT 5"

# Scan candidate messages cheaply, then re-fetch the one you want in full:
stockroom query --detail compact "SELECT message_id, text FROM messages WHERE text ILIKE '%flaky test%' LIMIT 10"
stockroom query --format json --detail raw \
  "SELECT text FROM messages WHERE message_id = '<id-from-the-scan>'"

# Structured output for a user/tool to consume:
stockroom query --format json "SELECT harness, session_id, title FROM sessions WHERE title IS NOT NULL LIMIT 5"

# Per-session token rollups (VIEW session_token_usage):
stockroom query --format table \
  "SELECT harness, session_id, input_tokens_total, output_tokens_total, token_grain
   FROM session_token_usage
   ORDER BY input_tokens_total DESC NULLS LAST
   LIMIT 10"

More variants (unbounded tools with activity window, day/harness token rollups, per-harness skill-use SQL): see the Cookbook.

Cookbook

Gnarly starter SQL: references/cookbook/. Open the index, then only the recipe you need.

Relaying to a human

You are the tool's operator, not its display. Run the SQL, read the result, and answer the user in natural language. Don't paste raw tsv at a human unless they asked to see it. When they do want the raw output — or a command to run themselves — hand them a --format table (to read) or --format json (to process) variant.

Understanding the system

To understand why these contracts look the way they do — the packaging, the torch contract, read-only-by-construction, the truncation doctrine, identity/provenance — read the shared system model: ../sr-search/references/system-model.md.