observability:session
/observability:session
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.
- Author
- bendrucker ↗
- Repository
- claude ↗
- Licence
- MIT
- Last repository change
- 23.10.2026
Assessment and security
- Files in this package
- 114
- Scripts
- 25
- HTML comments
- 2
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 23.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
- 25 scripts in this package. Review them before installing, as Claude may execute them.
- SKILL.md zawiera komentarze HTML (2). Nie widać ich w podglądzie Markdown, a model je czyta.
Scripts: scripts/catalog.test.ts, scripts/catalog.ts, scripts/db.test.ts, scripts/db.ts, scripts/events.test.ts, scripts/events.ts, scripts/extensions.integration.ts, scripts/file-catalog.ts
What it does and when to use it
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.
Triggers from the description
Arguments
/observability:session [--refresh] [--host label] [--since date]
Original author 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.
How it works
- Arguments
- Database
- Named Queries
- Cross-Machine History
- Tables, Views, and Macros
- Known Blind Spots
- Discovery
- Source Lookup
- Session File Structure
SKILL.md preview
---
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
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: the incremental refresh described in [Refresh](#refresh).
- `--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 `~/.local/share/claude-sessions/session.duckdb`, under `$XDG_DATA_HOME` when that is set. 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.
```bash
bun ${CLAUDE_SKILL_DIR}/scripts/refresh.ts
```
### 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. Add `-init ${CLAUDE_SKILL_DIR}/resources/limits.sql`, which caps the process at 60% of RAM (at most 4 GiB) and 4 threads. DuckDB's own defaults (80% of RAM, every core) apply per process, so a few concurrent readers over the index can freeze the machine. Add `-json` too: the default box format returns only 40 rows however many matched, and `-csv` emits the newlines embedded in `command` and `text` raw, so a row stops being one line. 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 either way.
```bash
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 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. Checked against the 62 sessions that carry a real `cost-state.totalCostUSD`, it comes in at 0.97 of billed spend ($1,582 against $1,631), so treat it as a close approximation. That record appears in only 66 of 1,645 recent files, which is why the estimate is the primary surface.
### 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)
For a fan-out of agents investigating the corpus, the orchestrator runs `refresh.ts --refresh` once up front, then every agent opens `~/.local/share/claude-sessions/session.duckdb` read-only with `-init limits.sql`. Never let a fanned-out agent call `refresh.ts`: past the stamp's `--max-age` it opens read-write and collides with every reader. Queries read a shared file, so the agents need no worktree. Worked example (param scoping via `SET VARIABLE`, breadth-first survey surfaces) in [`references/workflows.md`](references/workflows.md).
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.
## 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 to span every machine, `host=work` to scope to one imported machine). See [Cross-Machine History](#cross-machine-history).
The eleven that carry most of the measured usage:
- `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` before writing SQL against `text_content`. Put the topic's synonyms, command names and API names in one `query`, since a session scores on every term it contains:
```bash
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, so a command or a path it printed finds the session. Each row's `matched_surfaces` names where the terms hit. Fall back to `ILIKE` only for text `search` leaves out: Read output, file contents in Write and Edit calls, tool text past 2,000 characters per call, subagent turns, skill bodies, and hook output.
The rest, by name:
<!-- generated:queries -->
`already-allowed-still-prompting`, `bare-refs`, `catalog-reinjection-thrash-sessions`, `classifier`, `cli-health`, `field-drift`, `hook-block-then-retry-success`, `hook-config-vs-observed`, `hook-self-timing`, `model-summary`, `mods`, `permissions`, `plan-iterations`, `plan-sections`, `plan-sizes`, `sandbox-bypass-scaffold`, `sandbox-path-deny-recurrence`, `search`, `skill-activity`, `stats`, `stop-hook-noop-detector`, `text-export`, `tool-failure-rates`, `top-sessions`, `usage-spikes`
<!-- /generated:queries -->
[`references/catalog.md`](references/catalog.md) gives each one's purpose and params in a line. Before running a query you have not used, read the YAML header at the top of its `resources/queries/<name>.sql` for what it returns, its caveats, and its params' defaults.
### Queries That Read Disk
Four queries read files on disk instead of the index. Two of them (`plan-sections`, `skill-config-vs-observed`) parse structure through the `markdown`/`yaml` community extensions, so run those with `-cmd ".read resources/extensions.sql"` after `-init resources/limits.sql`, which loads both extensions in the same process before the piped query. `hook-self-timing` and `hook-config-vs-observed` read JSON and need no extension. Each one's header names its glob param and default.
```bash
duckdb -readonly -json -init ${CLAUDE_SKILL_DIR}/resources/limits.sql \
-cmd ".read ${CLAUDE_SKILL_DIR}/resources/extensions.sql" ~/.local/share/claude-sessions/session.duckdb \
< ${CLAUDE_SKILL_DIR}/resources/queries/plan-sections.sql
```
## Cross-Machine History
Session history copied from another machine is queryable alongside this machine's. Each machine is a `host`: this one is always `local`, and every imported machine gets a label you choose. With nothing imported, the index behaves exactly as the single-machine case.
`${CLAUDE_SKILL_DIR}/scripts/hosts.ts` lists every imported host. The listing, import, re-sync, and forget procedures live in [`references/cross-machine.md`](references/cross-machine.md). Read it when the user asks to list, import, re-sync, or remove a machine.
### Privacy
Importing another machine's history is a data-ownership decision: raise it once, at import. The egress policy records the answer. A host imported without `--egress` is marked `block_egress`, meaning its rows must be excluded from any output that leaves this machine (PR descriptions, Slack, email, web requests, uploads) by adding `host != '<label>'` (or scoping to `local`). `hosts.ts` prints each host's policy so that filter is easy to build. Pass `--egress` at import only when the source machine's history may leave this machine.
Imported corpora are a hot place for secrets in tool output and pasted text. Patterns worth watching before anything leaves the machine: `sk_live_`, `xoxb-`, `ghp_`, `AKIA`, `eyJhbGciOi`. This is a signal to review, not a redactor.
## Tables, Views, and Macros
Every table and view carries a `host` column (`local` for this machine, the label for imported ones). The `sessions` view adds `project_id` (`host || ':' || project_path`) for cross-host project identity. [`references/catalog.md`](references/catalog.md) documents every table, view, and filter macro, and says what each one holds. Load it before writing SQL against a surface you have not used.
Columns of the ten surfaces ad-hoc SQL reads most. `DESCRIBE <name>` for anything else.
!`bun ${CLAUDE_SKILL_DIR}/scripts/schema.ts`
## Known Blind Spots
The `index-health` query detects drift the corpus can show. These absences are structural, so no query can surface them. State them when an analysis depends on what they hide. Full elaboration in [`references/blind-spots.md`](references/blind-spots.md).
- **Thinking text**: persisted as signature-only stubs, unsearchable from transcripts.
- **Retention floor**: `cleanupPeriodDays` deletes old sessions, so the corpus floor ratchets forward. `~/.claude/history.jsonl` goes further back but isn't ingested.
- **Cloud and mobile sessions**: claude.ai web/mobile and cloud routines write no local JSONL.
- **Approved permission prompts before 2026-10-07**: only rejections left a trace. Later approvals are in `permission_requests`.
- **Offloaded tool results**: large outputs truncate to a sidecar preview. The full output never enters the index.
- **Other machines**: only imported hosts exist, so a never-imported or stale host is invisible rather than empty.
## Discovery
For a surface outside the map above, `DESCRIBE <table>` or `information_schema.columns`. For anything not pinned to a column, reach into `data` with JSON path operators. Wrap `data->>'$.path'` in parens before any comparison: DuckDB parses `data->>'$.x' = 'y'` as `data->>('$.x' = 'y')` and fails. Worked examples in [`references/schema-discovery.md`](references/schema-discovery.md).
## Source Lookup
To retrieve the full JSONL line for a message:
```bash
sed -n '<source_line>p' <source_file>
```
`source_line` is 1-based and per-file, with two caveats. It reflects a single-file scan's row order, which DuckDB preserves in practice but does not formally guarantee for window functions. And unparseable lines are skipped at import (`ignore_errors`), so in a file containing malformed lines it can trail the physical line number. When exactness matters, verify the fetched line's `uuid` or `timestamp` against the row.
## Session File Structure
Session logs live in `~/.claude/projects/<encoded-path>/<session-id>.jsonl` where the encoded path replaces `/` with `-`.
Files in this package (114)
- CLAUDE.md 20 KB
- README.md 2 KB
- SKILL.md 13 KB
- fixtures/debug/denials-session.txt 2 KB
- fixtures/hook-metrics/guard.jsonl 1 KB
- fixtures/mod-events/denials-session/auto-mode.1.0.jsonl 2 KB
- fixtures/plans/feature-plan.md 1 KB
- fixtures/plans/quick-plan.md 1 KB
- fixtures/sessions/-Users-test-health/health.jsonl 3 KB
- fixtures/sessions/-Users-test-project/attribution.jsonl 1 KB
- fixtures/sessions/-Users-test-project/basic.jsonl 1 KB
- fixtures/sessions/-Users-test-project/delegation.jsonl 5 KB
- fixtures/sessions/-Users-test-project/denials.jsonl 3 KB
- fixtures/sessions/-Users-test-project/hooks.jsonl 20 KB
- fixtures/sessions/-Users-test-project/labels.jsonl 2 KB
- fixtures/sessions/-Users-test-project/malformed.jsonl 1 KB
- fixtures/sessions/-Users-test-project/multiple.jsonl 1 KB
- fixtures/sessions/-Users-test-project/no-container.jsonl 1 KB
- fixtures/sessions/-Users-test-project/plan-iterations.jsonl 3 KB
- fixtures/sessions/-Users-test-project/plan-outcomes.jsonl 4 KB
- fixtures/sessions/-Users-test-project/plans.jsonl 2 KB
- fixtures/sessions/-Users-test-project/replay.jsonl 2 KB
- fixtures/sessions/-Users-test-project/review.jsonl 4 KB
- fixtures/sessions/-Users-test-project/search-surfaces.jsonl 5 KB
- fixtures/sessions/-Users-test-project/ship.jsonl 1 KB
- fixtures/sessions/-Users-test-project/skills.jsonl 5 KB
- fixtures/sessions/-Users-test-project/tool-failures.jsonl 3 KB
- fixtures/sessions/-Users-test-project/tools.jsonl 3 KB
- fixtures/sessions/-Users-test-project/subagents/agent-failures.jsonl 1 KB
- fixtures/sessions/-Users-test-project/subagents/agent-inherit1.jsonl 4 KB
- fixtures/sessions/-Users-test-project/subagents/agent-mine-hooks.jsonl 1 KB
- fixtures/sessions/-Users-test-project/subagents/agent-override2.jsonl 4 KB
- fixtures/sessions/-Users-test-project/subagents/agent-pinned3.jsonl 4 KB
- fixtures/sessions/-Users-test-project/subagents/agent-search-surfaces.jsonl 1 KB
- fixtures/sessions/-Users-test-project/subagents/workflows/wf_attribution/agent-nested.jsonl 1 KB
- fixtures/sessions/-Users-test-project-feature/feature.jsonl 1 KB
- fixtures/sessions/-Users-test-refs/refs.jsonl 3 KB
- fixtures/sessions/-Users-test-trope/trope.jsonl 2 KB
- fixtures/sessions/-Users-test-webapp/webapp.jsonl 1 KB
- references/blind-spots.md 2 KB
- references/catalog.md 23 KB
- references/cross-machine.md 3 KB
- references/discovery.md 12 KB
- references/schema-discovery.md 1 KB
- references/workflows.md 1 KB
- resources/extensions.sql 1 KB
- resources/import.sql 2 KB
- resources/limits.sql 1 KB
- resources/schema-map.txt 2 KB
- resources/views.sql 47 KB
- resources/queries/activity.sql 4 KB
- resources/queries/already-allowed-still-prompting.sql 1 KB
- resources/queries/bare-refs.sql 4 KB
- resources/queries/catalog-reinjection-thrash-sessions.sql 2 KB
- resources/queries/classifier.sql 3 KB
- resources/queries/cli-health.sql 4 KB
- resources/queries/delegation.sql 7 KB
- resources/queries/field-drift.sql 1 KB
- resources/queries/hook-block-then-retry-success.sql 2 KB
- resources/queries/hook-blocks.sql 4 KB
- resources/queries/hook-config-vs-observed.sql 5 KB
- resources/queries/hook-self-timing.sql 3 KB
- resources/queries/hooks.sql 5 KB
- resources/queries/index-health.sql 16 KB
- resources/queries/model-summary.sql 1 KB
- resources/queries/mods.sql 2 KB
- resources/queries/outcomes.sql 3 KB
- resources/queries/permissions.sql 1 KB
- resources/queries/plan-iterations.sql 5 KB
- resources/queries/plan-sections.sql 2 KB
- resources/queries/plan-sizes.sql 2 KB
- resources/queries/repeat-read-waste.sql 5 KB
- resources/queries/sandbox-bypass-effective-command.sql 2 KB
- resources/queries/sandbox-bypass-scaffold.sql 2 KB
- resources/queries/sandbox-path-deny-recurrence.sql 2 KB
- resources/queries/search.sql 2 KB
- resources/queries/skill-activity.sql 1 KB
- resources/queries/skill-auto-vs-explicit.sql 4 KB
- resources/queries/skill-config-vs-observed.sql 8 KB
- resources/queries/stats.sql 1 KB
- resources/queries/stop-hook-noop-detector.sql 4 KB
- resources/queries/text-export.sql 1 KB
- resources/queries/tool-failure-rates.sql 3 KB
- resources/queries/top-sessions.sql 2 KB
- resources/queries/usage-spikes.sql 3 KB
- resources/queries/usage-timeline.sql 2 KB
- resources/schema/00_pinned.sql 3 KB
- resources/schema/01_tables.sql 4 KB
- resources/schema/03_macros.sql 5 KB
- scripts/catalog.test.ts 7 KB
- scripts/catalog.ts 5 KB
- scripts/db.test.ts 131 KB
- scripts/db.ts 29 KB
- scripts/events.test.ts 2 KB
- scripts/events.ts 3 KB
- scripts/extensions.integration.ts 2 KB
- scripts/file-catalog.ts 5 KB
- scripts/forget.ts 1 KB
- scripts/hosts.ts 2 KB
- scripts/import.ts 3 KB
- scripts/migrate.test.ts 2 KB
- scripts/pull.test.ts 1 KB
- scripts/pull.ts 4 KB
- scripts/query-header.ts 3 KB
- scripts/query.test.ts 6 KB
- scripts/query.ts 5 KB
- scripts/refresh.integration.ts 6 KB
- scripts/refresh.ts 5 KB
- scripts/retention.ts 1 KB
- scripts/schema.ts 3 KB
- scripts/telemetry.test.ts 17 KB
- scripts/telemetry.ts 7 KB
- scripts/usage.test.ts 5 KB
- scripts/usage.ts 6 KB
Versions
The database has no other skills named observability:session.
Database snapshot: 13.11.2026 · package version c76816048bd9 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100search
Use when the user wants to find something across their Claude Code history — e.g. 'find where I discussed X', 'search for Y', 'when did I work on Z - 100dig
Mine Claude Code sessions — timeline, gaps, repo attribution, session history. Use when user says "dig", "sessions", "past sessions", "timeline", "what did I work on", or wants to see session history. Do NOT trigger for finding code/projects (use /trace), exploring repos (use… - 84observability
When the bundled explain-usage skill resolves in this session, prefer it for a plain-language breakdown of this session's tokens; this skill for cross-session reports from the local telemetry stores (OTEL DuckDB, hook event log, ccusage): token burn, cost, hook latency,… - 100cq
Query past Claude Code sessions using the cq CLI (SQL over session transcripts via DuckDB). You MUST use this skill whenever the user asks about their Claude session history, tool usage, errors, or past commands. This includes questions like "what tools have I used", "how many… - 100today
Show enriched daily activity from Claude Code sessions — branches, PRs, Linear issues, and what you worked on. Use when the user wants to see what they did today, review their daily work, or get a summary of their sessions. - 100session-history
Search and summarize Claude Code, Codex, and opencode session history. Use when the user wants to look at past conversations, find sessions about a topic, or reconstruct a debugging narrative.