session-analyzer

name	session-analyzer
description	Read and interpret captured Claude Code session events from a project's local sana SQLite database. Use when the user asks to analyze a session, summarize what happened, list tools used, find slow tool calls, review prompts, or otherwise inspect the events recorded by sana hooks. Also use when the user mentions "sana", ".claude/analysis/sessions.db", or session/hook telemetry.

session-analyzer

This skill reads the SQLite database that sana populates from Claude Code hooks. The DB lives at $CLAUDE_PROJECT_DIR/.claude/analysis/sessions.db (i.e. .claude/analysis/sessions.db relative to the project root).

If that file does not exist, sana has not been installed in this project. Tell the user to run sana's install.sh from the project root.

How to query

Use the Bash tool with sqlite3. Always pass -readonly when you don't intend to write — this is a read-only analysis skill. Use -json or -header -separator $'\t' for parseable output.

sqlite3 -readonly -json .claude/analysis/sessions.db "SELECT ..."

For multi-line queries or long output, write the query to a heredoc:

sqlite3 -readonly -json .claude/analysis/sessions.db <<'SQL'
SELECT event_type, COUNT(*) FROM events GROUP BY event_type;
SQL

Schema

One table, events, with one row per hook invocation:

column	type	notes
id	INTEGER	autoincrement primary key
session_id	TEXT	UUID assigned by Claude Code per session
event_type	TEXT	hook_event_name from payload (PreToolUse, Stop, ...)
ts	REAL	unix epoch seconds (fractional) when hook fired
cwd	TEXT	working directory at hook time (NULL if missing)
tool_name	TEXT	tool name for PreToolUse/PostToolUse/PostToolUseFailure; NULL otherwise
transcript_path	TEXT	path to the session's .jsonl transcript
payload_json	TEXT	full original JSON payload (use json_extract to drill in)

Indexes: session_id, event_type, ts.

Captured events: SessionStart, SessionEnd, UserPromptSubmit, PreToolUse, PostToolUse, PostToolUseFailure, Stop, StopFailure, SubagentStart, SubagentStop, PreCompact, PostCompact, Notification.

Useful query patterns

Latest session ID (for "what just happened?" questions):

SELECT session_id FROM events ORDER BY ts DESC LIMIT 1;

Sessions overview — start/end/duration/event count, newest first:

SELECT
  session_id,
  datetime(MIN(ts), 'unixepoch', 'localtime') AS started,
  datetime(MAX(ts), 'unixepoch', 'localtime') AS ended,
  ROUND(MAX(ts) - MIN(ts), 1) AS duration_s,
  COUNT(*) AS events
FROM events
GROUP BY session_id
ORDER BY MIN(ts) DESC;

Tool histogram for a session (most-called first):

SELECT tool_name, COUNT(*) AS n
FROM events
WHERE session_id = :sid AND event_type = 'PreToolUse'
GROUP BY tool_name
ORDER BY n DESC;

User prompts in a session, oldest first:

SELECT datetime(ts, 'unixepoch', 'localtime') AS at,
       json_extract(payload_json, '$.prompt') AS prompt
FROM events
WHERE session_id = :sid AND event_type = 'UserPromptSubmit'
ORDER BY ts;

Slowest tool calls — time between PreToolUse and the matching Post* event, by tool_use_id:

WITH pre AS (
  SELECT session_id,
         json_extract(payload_json, '$.tool_use_id') AS tid,
         tool_name, ts AS pre_ts
  FROM events WHERE event_type = 'PreToolUse'
),
post AS (
  SELECT session_id,
         json_extract(payload_json, '$.tool_use_id') AS tid,
         ts AS post_ts, event_type AS post_kind
  FROM events WHERE event_type IN ('PostToolUse','PostToolUseFailure')
)
SELECT pre.session_id, pre.tool_name,
       ROUND(post.post_ts - pre.pre_ts, 2) AS dur_s,
       post.post_kind
FROM pre JOIN post USING (session_id, tid)
ORDER BY dur_s DESC
LIMIT 20;

Tool failures with error messages:

SELECT datetime(ts, 'unixepoch', 'localtime') AS at,
       tool_name,
       json_extract(payload_json, '$.error') AS error
FROM events
WHERE event_type = 'PostToolUseFailure'
ORDER BY ts DESC;

Bash commands run in a session, in order:

SELECT datetime(ts, 'unixepoch', 'localtime') AS at,
       json_extract(payload_json, '$.tool_input.command') AS cmd
FROM events
WHERE session_id = :sid
  AND event_type = 'PreToolUse'
  AND tool_name = 'Bash'
ORDER BY ts;

Files written/edited in a session:

SELECT DISTINCT tool_name,
       COALESCE(
         json_extract(payload_json, '$.tool_input.file_path'),
         json_extract(payload_json, '$.tool_input.path')
       ) AS path
FROM events
WHERE session_id = :sid
  AND event_type = 'PreToolUse'
  AND tool_name IN ('Write','Edit','NotebookEdit');

Workflow

1. Confirm the DB exists: ls .claude/analysis/sessions.db. If missing, stop and tell the user.
2. If the user didn't specify a session, default to the latest one (query above) and tell them which you picked.
3. Run the relevant query from the patterns above (or compose a new one against the schema).
4. Summarize the results in plain prose — don't dump raw JSON unless asked. Cite specific tool names, prompts, and timestamps.
5. If a question requires data that isn't in events (e.g., the actual model output), the transcript_path column points to the JSONL transcript file you can read with Read.