Claude Sessions Duckdb — Data skill for Claude Code

claude-sessions-duckdb

Query your [Claude Code](https://claude.com/claude-code) session transcripts with SQL. One DuckDB script, five views, no dependencies beyond the `duckdb` CLI.

Claude Code writes every session to JSONL files under `~/.claude/projects/`. DuckDB can read them directly — but the transcript format has several traps that silently corrupt your numbers (a naive token sum overcounts roughly 2–3x). This repo is a reference implementation that encodes those traps so you don't have to rediscover them. Everything runs locally; nothing leaves your machine.

Quickstart

# build/refresh the database (full import of a multi-GB corpus: tens of seconds)
duckdb claude.duckdb -c ".read claude_sessions.sql"

# then query — milliseconds
duckdb claude.duckdb

-- output tokens by model, correctly deduplicated
SELECT model, count(*) AS msgs, sum(output_tokens) AS out_tok
FROM claude_token_usage GROUP BY 1 ORDER BY 3 DESC;

-- tool call volume and error rates
SELECT tool_name, count(*) AS calls,
       round(100.0 * count(*) FILTER (is_error) / count(*), 1) AS err_pct
FROM claude_tool_calls GROUP BY 1 ORDER BY 2 DESC;

-- which sessions talked about a topic
SELECT session_id, project, ts, text
FROM claude_messages
WHERE role = 'user' AND text ILIKE '%migration%'
ORDER BY ts DESC;

-- sessions per project, human messages only
SELECT project, count(*) AS sessions, sum(user_msgs) AS human_msgs
FROM claude_sessions GROUP BY 1 ORDER BY 2 DESC;

Views: `claude_sessions` (one row per session), `claude_messages` (text assembled from content blocks), `claude_tool_calls` (every `tool_use` joined with its `tool_result`), `claude_token_usage` (deduplicated usage), plus the raw `claude_events` table for everything else. Column-level docs are in [`claude_sessions.sql`](claude_sessions.sql).

The traps this encodes

These are the things that will quietly give you wrong numbers (or hard errors) if you query the JSONL naively. All verified against Claude C