claude-code:session

Session

Search and analyze Claude Code conversation history via a DuckDB index over JSONL session files.

Current Session ID: ${CLAUDE_SESSION_ID}

Arguments

Map any arguments to the mechanisms below:

• --refresh: force a rescan via refresh.ts --refresh before querying. Default: incremental refresh keyed on a per-file (mtime, size) catalog, skipped entirely while the freshness stamp is younger than --max-age.
• --host <label>: scope queries to one imported machine through the host param. Default: span every host, including local. See [Cross-Machine History](#cross-machine-history).
• --since <date>: pass as the after_date param to scope queries from that date forward. Default: the full index.

Database

The session index is a DuckDB database at ${CLAUDE_PLUGIN_DATA}/session.duckdb. That path is stable: use it directly in every query and agent prompt. refresh.ts prints the same path, resolved, for callers outside this skill.

Refresh

Run refresh.ts before querying. It scans ~/.claude/projects/**/*.jsonl plus any imported hosts, imports files whose mtime or size changed, drops rows for deleted files, and prints the DB path. When a prior refresh finished within --max-age (default 300 seconds), it prints the path and exits without opening the database, so calling it before every query is cheap. Pass --refresh to rescan regardless when the user asks for the latest data.

${CLAUDE_SKILL_DIR}/scripts/refresh.ts
${CLAUDE_SKILL_DIR}/scripts/refresh.ts --refresh

Querying

After refresh, query with the duckdb CLI or any DuckDB client, always -readonly: querying never writes, and a read-write open would block refreshes and other readers. Named SQL files in resources/queries/ provide common queries. Use SET VARIABLE for parameterization and getvariable('key') in SQL. Quote variable names that are reserved words: SET VARIABLE limit = 5 is a parser error (limit is reserved), SET VARIABLE "limit" = 5 works; getvariable('limit') is unaffected.

duckdb -readonly ${CLAUDE_PLUGIN_DATA}/session.duckdb "SELECT model, SUM(output_tokens) FROM message_usage GROUP BY model"
duckdb -readonly ${CLAUDE_PLUGIN_DATA}/session.duckdb < ${CLAUDE_SKILL_DIR}/resources/queries/stats.sql

scripts/usage.ts renders a session's token-burn timeline (--session <id>) in the terminal, or the top sessions by estimated cost (--days <n>) when no session is given. It opens the index read-only. Cost is an estimate from public API rates, useful as a relative weight rather than billed spend.

Locking

DuckDB locks the database file per process. Read-only opens take a shared lock, and any number of them coexist. A write open (a refresh that has work to do) needs exclusive access: it cannot start while readers hold the file, and a reader cannot open mid-refresh. Either collision fails with Could not set lock; retry after the other side finishes. refresh.ts retries briefly on its own, and when a concurrent refresh holds the lock it prints the path and exits 0, since the other run is doing the same work.

Parallel Queries (Workflows)

To investigate the corpus with a fan-out of agents (breadth search for leads, then a depth pass per lead), refresh once up front, then have every agent open ${CLAUDE_PLUGIN_DATA}/session.duckdb read-only. The path is stable, so agent prompts need no path threading.

${CLAUDE_SKILL_DIR}/scripts/refresh.ts --refresh                # orchestrator, once
duckdb -readonly ${CLAUDE_PLUGIN_DATA}/session.duckdb < ${CLAUDE_SKILL_DIR}/resources/queries/activity.sql   # agents, in parallel

Read-only opens coexist, so agents never contend with each other. Never let a fanned-out agent call refresh.ts: past the stamp's --max-age it opens read-write, which collides with every reader. Queries read a shared file, so the agents need no worktree.

Params work the same from the CLI: getvariable returns NULL for an unset variable and every named query null-guards its params, so a bare read-only run of a query file runs unfiltered. Prepend SET VARIABLE lines to scope it:

duckdb -readonly ${CLAUDE_PLUGIN_DATA}/session.duckdb <<'SQL'
SET VARIABLE after_date = '2026-05-01';
SET VARIABLE hook = '*tropes*';
.read ${CLAUDE_SKILL_DIR}/resources/queries/hook-blocks.sql
SQL

Breadth-first leads come from the survey surfaces (records taxonomy, fields for schema inference, activity, hooks, diagnostics, skill-activity); a depth pass is then custom read-only SQL over whatever table or view the survey pointed at.

For self-improvement discovery (fanning out over the whole corpus to mine config-change candidates, then grounding them against the live config), [references/discovery.md](references/discovery.md) carries the full recipe: the dimension-to-query cheat sheet, the mandatory grounding pass, the host-safety rules, and the Tier-2 query catalog (six discovery queries shipped as SQL but kept out of the catalog above).

Named Queries

Built-in queries in resources/queries/ run by name with SET VARIABLE params. Prefer these over writing SQL from scratch.

The project param matches against the directory name (last path component) using glob syntax: project=myapp matches exactly, project=myapp* matches the repo and its worktrees.

Every query also takes an optional host param. Omit it to span every machine (including local); pass host=work to scope to one imported machine. See [Cross-Machine History](#cross-machine-history).

Every query, grouped by category with a one-line gloss:

Sessions and Prose

• search: find sessions by keyword
• text-export: dump cleaned prose
• phrase-lift: phrase rate, assistant-vs-user lift
• model-summary: assistant text per model

Tool Use and Friction

• stats: tool usage breakdown
• errors: recent tool errors
• permissions: tool calls the user rejected
• sandbox: sandbox-bypassing Bash calls
• sandbox-bypass-effective-command: normalized bypass verbs

Hooks

• hooks: hook activity and performance
• hook-blocks: hook overfiring analysis
• hook-block-then-retry-success: blocks retried away
• hook-config-vs-observed: configured vs observed hooks

Skills

• skills: skill invocation counts
• skill-activity: work attributed per skill
• skill-auto-vs-explicit: auto vs explicit invocations

Files, Tokens, Activity

• files: most-read and edited file hotspots
• repeat-read-waste: repeat-Read context tax
• activity: session interaction profile
• diagnostics: recurring type/lint diagnostics
• usage-timeline: one session's token burn per time bucket (estimated cost, cache-miss ratio)
• usage-spikes: ranked (session, bucket) burn windows across the corpus

Planning and Outcomes

• plans: sessions using plan mode
• plan-iterations: per-plan growth and carry-over
• outcomes: session terminal states
• delegation: subagent spawn model mix against the parent's main model, generic vs pinned-agent split

Schema and Index

• schema: list every column
• keys: sample raw JSON keys
• fields: infer JSON keys at a path
• index-health: the index auditing itself

Full params and descriptions in [references/catalog.md](references/catalog.md). Load it before running a query you have not used.

Markdown and YAML on Disk

Two queries read files on disk through community extensions instead of the index. They need markdown/yaml loaded, so run them with -init resources/extensions.sql, which loads both in the same process before the piped query and runs under -readonly. The common-path queries above omit -init and pay nothing.

duckdb -readonly -init ${CLAUDE_SKILL_DIR}/resources/extensions.sql ${CLAUDE_PLUGIN_DATA}/session.duckdb \
  < ${CLAUDE_SKILL_DIR}/resources/queries/plan-sections.sql

Three queries use this path: plan-sections, frontmatter, and skill-config-vs-observed. Params and per-query notes are in [`