/session

name: observability:session
description: Query Claude Code session history via a DuckDB index over `~/.claude/projects/`. Use when asked about Claude Code activity ("how many tokens today?", "what did I work on this week?", "which sessions discussed X?") or instead of reading, grepping, or jq-ing session transcripts. Not for codebase search, git log queries, or arbitrary databases.
argument-hint: "[--refresh] [--host label] [--since date]"
allowed-tools:
  - Bash
  - Read

Session

Query Claude Code history from a DuckDB index over the JSONL session files. Report each figure with the named query or SQL that produced it, and any `index-health` alert or Known Blind Spot that bounds it.

Current session ID: `${CLAUDE_SESSION_ID}`

Arguments

• `--refresh`: run `refresh.ts --refresh` before querying. Default: the incremental refresh.
• `--host <label>`: scope queries through the `host` param. Default: every host, including `local`.
• `--since <date>`: pass as the `after_date` param. Default: the full index.

Database

The index is `~/.local/share/claude-sessions/session.duckdb` (under `$XDG_DATA_HOME` when set). Use that path directly in every query and agent prompt.

Refresh

Run `refresh.ts` before querying. It imports changed files, drops rows for deleted ones, and prints the DB path. Within `--max-age` (default 300 seconds) of the last refresh it exits without opening the database. Pass `--refresh` when the user asks for the latest data.

bun ${CLAUDE_SKILL_DIR}/scripts/refresh.ts

Querying

Open the index with `duckdb -readonly -json -init ${CLAUDE_SKILL_DIR}/resources/limits.sql`:

• `-readonly`: a read-write open blocks refreshes and other readers.
• `-init limits.sql`: caps memory and threads. DuckDB's per-process defaults let a few concurrent readers freeze the machine.
• `-json`: the default box format truncates to 40 rows, and `-csv` breaks rows on newlines embedded in `command` and `text`.

Parameterize with `SET VARIABLE` and read with `getvariable('key')`. Quote reserved words: `SET VARIABLE "limit" = 5`.

duckdb -readonly -json -init ${CLAUDE_SKILL_DIR}/resources/limits.sql ~/.local/share/claude-sessions/session.duckdb "SELECT model, SUM(output_tokens) FROM message_usage GROUP BY model"
duckdb -readonly -json -init ${CLAUDE_SKILL_DIR}/resources/limits.sql ~/.local/share/claude-sessions/session.duckdb < ${CLAUDE_SKILL_DIR}/resources/queries/stats.sql

`scripts/usage.ts` renders one session's token-burn timeline (`--session <id>`), or the top sessions by estimated cost (`--days <n>`). Its cost estimate comes from public API rates and comes to about 0.97 of billed spend.

Locking

Read-only opens share the lock. A refresh with work to do needs exclusive access. A reader and a writer that collide fail with `Could not set lock`. Retry after the other side finishes. `refresh.ts` retries on its own and exits 0 when a concurrent refresh holds the lock.

Parallel Queries (Workflows)

For a fan-out of agents over the corpus, the orchestrator runs `refresh.ts --refresh` once up front. Each agent then opens the DB path read-only with `-init limits.sql` and runs in the shared checkout. [`references/workflows.md`](references/workflows.md) shows param scoping and the breadth-first survey surfaces.

For mining config-change candidates across the whole corpus, follow [`references/discovery.md`](references/discovery.md).

Named Queries

Run the queries in `resources/queries/` by name with `SET VARIABLE` params before writing SQL from scratch. Every query takes `project` (a glob on the directory's last path component: `myapp*` matches the repo and its worktrees) and `host`.

The most used:

• `index-health`: the index auditing itself. Run it first in any analysis pass, since its alerts cap what the rest can claim.
• `activity`: session interaction profile, one row per prompt source, plus interruptions, compactions, API retries, hook friction, permission modes.
• `hooks`: per-hook runs, friction rate, and latency corrected for host-wide slowdowns (`excess_p95_ms`, not `p95_ms`).
• `hook-blocks`: hook overfiring, grouped by normalized reason signature, including the PreToolUse denies `hook_events` never records.
• `skill-auto-vs-explicit`: per skill, model-routed vs chained vs typed. The `disable-model-invocation` lever.
• `skill-config-vs-observed`: installed skills that never fire, the curation instrument (reads disk, needs `-init`).
• `sandbox-bypass-effective-command`: bypassed commands by their real verb, with how many followed a failed sandboxed run, the `excludedCommands` candidates.
• `repeat-read-waste`: repeat Reads split by cause, isolating the true context tax from pagination and fan-out.
• `delegation`: whether expensive parents push subagent spawns down to cheaper models.
• `usage-timeline`: one session's token burn per time bucket, with estimated cost and a context-size proxy.
• `outcomes`: session terminal states (shipped, abandoned-with-edits, handed off, no artifact), the outcome side the activity queries never measure.

To find sessions about a topic, run `search` first. Put the topic's synonyms, command names, and API names in one `query`, since a session scores on every term it contains:

duckdb -readonly -json -init ${CLAUDE_SKILL_DIR}/resources/limits.sql -cmd "SET VARIABLE query = 'taskpolicy renice setpriority cpu priority'" \
  ~/.local/share/claude-sessions/session.duckdb < ${CLAUDE_SKILL_DIR}/resources/queries/search.sql

`search` covers prompts, assistant prose, tool calls, and their output, and `matched_surfaces` names where terms hit. Use `ILIKE` only for what it leaves out: Read output, Write and Edit file contents, tool text past 2,000 characters per call, subagent turns, skill bodies, and hook output.

The rest:

<!-- generated:queries --> `already-allowed-still-prompting`, `bare-refs`, `catalog-reinjection-thrash-sessions`, `classifier`, `cli-health`, `field-drift`, `hook-block-then-retry-succ