Paulnsorensen Vaudeville Vaudeville Session Analytics

session-analytics

Query Claude Code session logs. DuckDB is the database, SQL is the interface.

How it works

Session logs live as JSONL files in ~/.claude/projects/. Each conversation turn is one JSON line. This skill materializes them into a DuckDB database with pre-flattened tables so you can answer questions with single SQL queries.

Prerequisites

Requires the duckdb CLI on PATH (brew install duckdb on macOS). The ingest script shells out to duckdb — it will error if the binary isn't found. Never use python3 for querying — always use the duckdb CLI directly.

Step 1: Ensure the database exists

Run the ingestion script. It has a 1-hour TTL — it skips work if the database is fresh. Always run this first.

python3 <skill-dir>/scripts/ingest.py

Use --force to re-ingest regardless of TTL (e.g., if the user wants the latest data from the current session).

The database lives at ~/.claude/analytics/sessions.duckdb.

Step 2: Query via DuckDB CLI

All queries go through the CLI. Use -json for structured output you can reason about, or omit it for human-readable tables.

duckdb ~/.claude/analytics/sessions.duckdb -json -c "SELECT ..."

Schema

tool_uses

Flattened from assistant message content[] blocks where type = 'tool_use'.

tool_results

Flattened from user message content[] blocks where type = 'tool_result'.

stop_events

Assistant messages where Claude stopped generating.

agent_spawns

Subset of tool_uses for Agent calls.

skill_invocations

Subset of tool_uses for Skill calls.

mcp_calls

Subset of tool_uses for MCP server calls (tool_name starts with mcp__).

Same columns as tool_uses. The tool_name encodes the server and method: mcp__<server>__<method> (e.g., mcp__context7__query-docs).

sessions

One row per unique (sessionId, cwd, branch) combination.

stop_hooks

System entries with subtype stop_hook_summary.

permission_denials

Pre-filtered tool_results for permission-related failures.

raw_entries

The full unflattened JSONL data. Use only when the materialized tables don't have what you need.

Query Catalog

Organized by investigation type. Use as starting points — modify for your question.

Tool usage frequency

SELECT tool_name, count(*) AS uses
FROM tool_uses
GROUP BY tool_name
ORDER BY uses DESC;

Error rate by tool

SELECT
    tu.tool_name,
    count(*) AS total,
    sum(CASE WHEN tr.is_error = 'true' THEN 1 ELSE 0 END) AS errors,
    round(sum(CASE WHEN tr.is_error = 'true' THEN 1 ELSE 0 END) * 100.0 / count(*), 1) AS error_pct
FROM tool_uses tu
JOIN tool_results tr ON tu.tool_use_id = tr.tool_use_id
GROUP BY tu.tool_name
ORDER BY errors DESC;

MCP server usage breakdown

SELECT
    split_part(tool_name, '__', 2) AS server,
    split_part(tool_name, '__', 3) AS method,
    count(*) AS calls
FROM mcp_calls
GROUP BY server, method
ORDER BY calls DESC;

Skill usage over time

SELECT
    skill_name,
    timestamp::DATE AS day,
    count(*) AS uses
FROM skill_invocations
GROUP BY skill_name, day
ORDER BY day DESC, uses DESC;

Busiest sessions

SELECT
    s.sessionId,
    s.project,
    s.branch,
    s.first_seen,
    s.last_seen,
    (SELECT count(*) FROM tool_uses tu WHERE tu.sessionId = s.sessionId) AS tool_calls
FROM sessions s
ORDER BY tool_calls DESC
LIMIT 10;

Most common Bash commands

SELECT
    substr(bash_cmd, 1, 80) AS cmd,
    count(*) AS uses
FROM tool_uses
WHERE tool_name = 'Bash' AND bash_cmd IS NOT NULL
GROUP BY cmd
ORDER BY uses DESC
LIMIT 20;

Permission friction audit

SELECT
    CASE
        WHEN bash_cmd LIKE '%python3%' THEN 'python3 inline'
        WHEN bash_cmd LIKE '%cat %' AND bash_cmd LIKE '%>%' THEN 'cat redirect (use Write)'
        WHEN bash_cmd LIKE 'find %' OR bash_cmd LIKE '% find %' THEN 'find (use Glob)'
        WHEN bash_cmd LIKE 'grep %' OR bash_cmd LIKE 'egrep %' THEN 'grep (use Grep)'
        WHEN bash_cmd LIKE 'sed %' OR bash_cmd LIKE '%sed -i%' THEN 'sed (use Edit)'
        WHEN bash_cmd LIKE 'cd %' AND bash_cmd LIKE '%git%' THEN 'cd+git compound'
        WHEN bash_cmd LIKE 'cd %' AND bash_cmd LIKE '%gh %' THEN 'cd+gh compound'
        ELSE 'other: ' || substr(bash_cmd, 1, 50)
    END AS category,
    count(*) AS denials
FROM tool_uses tu
JOIN tool_results tr ON tu.tool_use_id = tr.tool_use_id
WHERE tu.tool_name = 'Bash'
AND tr.is_error = 'true'
AND tr.content LIKE 'Permission to use Bash%'
GROUP BY category
ORDER BY denials DESC;

Hook impact analysis

SELECT
    level,
    preventedContinuation,
    substr(stopReason, 1, 100) AS reason,
    count(*) AS cnt
FROM stop_hooks
GROUP BY level, preventedContinuation, reason
ORDER BY cnt DESC
LIMIT 20;

Hook duration breakdown

SELECT
    json_extract_string(hook_info, '$.command') AS hook_command,
    count(*) AS executions,
    round(avg(CAST(json_extract_string(hook_info, '$.durationMs') AS DOUBLE)), 0) AS avg_ms,
    max(CAST(json_extract_string(hook_info, '$.durationMs') AS DOUBLE)) AS max_ms
FROM stop_hooks,
     unnest(json_extract(hookInfos, '$[*]')) AS t(hook_info)
WHERE hookInfos IS NOT NULL
GROUP BY hook_command
ORDER BY executions DESC;

Agent spawn patterns by skill

SELECT
    si.skill_name,
    asp.agent_type,
    substr(asp.description, 1, 60) AS agent_desc,
    count(*) AS spawns
FROM skill_invocations si
JOIN agent_spawns asp
    ON si.sessionId = asp.sessionId
    AND asp.timestamp::TIMESTAMP BETWEEN si.timestamp::TIMESTAMP
        AND si.timestamp::TIMESTAMP + INTERVAL '10' MINUTE
GROUP BY si.skill_name, asp.agent_type, agent_desc
ORDER BY si.skill_name, spawns DESC;

Daily activity heatmap

SELECT
    timestamp::DATE AS day,
    extract(hour FROM timestamp::TIMESTAMP) AS hour,
    count(*) AS calls
FROM tool_uses
WHERE timestamp::DATE >= CURRENT_DATE - INTERVAL '14' DAY
GROUP BY day, hour
ORDER BY day DESC, hour;

Filter by project cwd

Restrict any query to a single project by matching cwd LIKE '%<keyword>%'. Substitute <keyword> with a fragment of the project's working directory (e.g. vaudeville, port-louis). This is the same pattern vaudeville.analytics.query_session_patterns(project_filter=...) uses.

-- Top bash commands scoped to one project
SELECT bash_cmd, count(*) AS uses
FROM tool_uses
WHERE tool_name = 'Bash'
  AND bash_cmd IS NOT NULL
  AND cwd LIKE '%vaudeville%'
GROUP BY bash_cmd
ORDER BY uses DESC
LIMIT 10;

-- Top tool uses scoped to one project
SELECT tool_name, count(*) AS uses
FROM tool_uses
WHERE cwd LIKE '%vaudeville%'
GROUP BY tool_name
ORDER BY uses DESC
LIMIT 10;

-- Permission denials scoped to one project (join through sessions.project)
SELECT pd.content, count(*) AS cnt
FROM permission_denials pd
JOIN sessions s ON pd.sessionId = s.sessionId
WHERE s.project LIKE '%vaudeville%'
GROUP BY pd.content
ORDER BY cnt DESC
LIMIT 5;

Pre-built query scripts

For common analytics questions, use these scripts instead of writing inline SQL or Python. All scripts live in <skill-dir>/scripts/queries/. Most accept --days N, --limit N, and --json flags; hook_stats.py accepts --days and --json but uses a fixed error-pattern limit of 10.

# Denied tool names ranked by frequency (replaces inline Python regex extraction)
python3 <skill-dir>/scripts/queries/denied_tools.py [--days 14] [--json]

# Top tools by usage count
python3 <skill-dir>/scripts/queries/tool_usage.py [--days 14] [--json]

# Tools ranked by error rate
python3 <skill-dir>/scripts/queries/error_rates.py [--days 14] [--min-uses 5] [--json]

# Most-repeated bash commands (add --dangerous for risky ones only)
python3 <skill-dir>/scripts/queries/bash_patterns.py [--days 14] [--dangerous] [--json]

# Bash commands that should use dedicated tools (cat→Read, grep→Grep, etc.)
python3 <skill-dir>/scripts/queries/tool_misuse.py [--days 14] [--json]

# Hook coverage ratio and error patterns
python3 <skill-dir>/scripts/queries/hook_stats.py [--days 14] [--json]

Use --json for structured output you can pipe to jq. Without it, most scripts output tab-separated values; hook_stats.py prints a multi-line human-formatted summary instead.

Query patterns

When answering user questions:

1. Run python3 <skill-dir>/scripts/ingest.py first (fast if cached)
2. Translate the question into SQL against the schema above
3. Use duckdb ~/.claude/analytics/sessions.duckdb -json -c "..." to execute
4. Present results as markdown tables when there are 3+ rows. For single-value answers, state the number directly. Lead with the answer, then show the query if the user might want to modify it.
5. For follow-up questions, skip ingestion — the database persists

For complex analysis, chain multiple queries. Aim to answer within 5-8 queries. If the question needs more, present intermediate findings and ask if deeper analysis is needed.

When filtering by project, use LIKE '%keyword%' on the cwd column since full paths are verbose.

Timestamps are ISO 8601 strings stored as VARCHAR. Cast to TIMESTAMP for date math: timestamp::TIMESTAMP, timestamp::DATE, or use extract().

Gotchas

• Empty DuckDB CLI JSON results return [{] not [] — handle both
• is_error in tool_results is VARCHAR 'true'/'false', not boolean
• Timestamps are VARCHAR — cast explicitly for date math
• The input column in tool_uses is full JSON — use json_extract_string() to pull specific fields not already materialized as columns
• DuckDB CLI -json mode returns all values as strings

What This Skill Doesn't Do

• Read or reconstruct individual session transcripts
• Debug issues in the current session
• Modify or delete session log files
• Query external data sources
• Write ad-hoc Python scripts — all queries go through duckdb CLI

Source: paulnsorensen/vaudeville — distributed by TomeVault.