analyze-usage

AI Coding Usage

Unified usage analyzer for Claude Code, Codex, and Cursor. Loads logs into DuckDB for SQL analysis. The skill also provisions a canonical cross-harness reference schema from references/canonical-agent-schema.duckdb.sql during database bootstrap.

Quick Start

# Run the script (loads data on first run, incremental updates after)
scripts/analyze-usage

# Show database schema and example queries
scripts/analyze-usage --schema

# Query your data
scripts/analyze-usage query "SELECT * FROM tool_summary"

Commands

Command	Description
(default)	Auto-detect changes, incremental update, show summary
update	Explicit incremental update
reload	Force reload all data (with backup)
query "SQL"	Execute SQL query
search "query"	Search conversation content
shell	Interactive DuckDB shell
--schema	Database schema with example queries
--help	Full help documentation

Search

# ILIKE search on conversation content (default)
scripts/analyze-usage search "memory"

# BM25 full-text search (covers content + thinking)
scripts/analyze-usage search "memory" --fts

# Search reasoning traces
scripts/analyze-usage search "memory" --thinking

# Search both content and thinking
scripts/analyze-usage search "memory" --all

# Filter by role, repo, time
scripts/analyze-usage search "refactor" --user --repo bertram-chat --since 7d

# Limit results
scripts/analyze-usage search "deploy" -n 20

Common Queries

-- Most used tools
SELECT * FROM tool_summary;

-- Daily usage (last 2 weeks)
SELECT * FROM daily_summary ORDER BY date DESC LIMIT 14;

-- Compare harness usage
SELECT source, COUNT(*) AS interactions
FROM interactions
GROUP BY source ORDER BY interactions DESC;

-- Skill popularity
SELECT regexp_extract(context, '"skill":"([^"]+)"', 1) as skill, COUNT(*) as uses
FROM claude_tools WHERE tool_name = 'Skill'
GROUP BY skill ORDER BY uses DESC;

-- Peak coding hours
SELECT hour_of_day, SUM(interactions) as total
FROM peak_hours GROUP BY hour_of_day ORDER BY total DESC LIMIT 5;

-- Activity by repository (aggregates worktrees)
SELECT repo_name, SUM(interactions) as total, SUM(worktrees) as branches
FROM repo_activity GROUP BY repo_name ORDER BY total DESC LIMIT 10;

-- Turn durations
SELECT * FROM turn_durations ORDER BY duration_ms DESC LIMIT 10;

-- Session overview with summaries
SELECT session_id, repo_name, summary FROM session_overview
WHERE summary IS NOT NULL ORDER BY started_at DESC LIMIT 10;

-- API errors
SELECT * FROM api_errors ORDER BY timestamp DESC LIMIT 10;

-- PR links
SELECT * FROM pr_links;

-- Cost by repo
SELECT repo_name, ROUND(SUM(cost_usd), 2) as cost
FROM usage_with_cost
WHERE CAST(timestamp AS TIMESTAMP) >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY repo_name ORDER BY cost DESC;

-- Full cost summary by repo and model
SELECT * FROM cost_summary ORDER BY cost_usd DESC;

Cost Calculation

The script tracks tokens and calculates API costs automatically:

Token columns in claude_tools:

• input_tokens, output_tokens - Direct tokens
• cache_write_tokens, cache_read_tokens - Prompt caching tokens
• model - Model used (opus/sonnet/haiku)

Cost views:

• model_pricing - API rates per million tokens (update when prices change)
• usage_with_cost - Each row has pre-calculated cost_usd
• cost_summary - Pre-aggregated by repo/model

Key Tables/Views

Core Tables

• claude_tools - Tool invocations (with model, tokens, repo/branch, source_file)
• claude_sessions - Session metadata
• codex_tools - Codex tool invocations
• codex_sessions - Codex session metadata
• codex_token_counts - Codex per-turn token snapshots
• codex_developer_messages - Codex developer-role instruction payloads
• messages - Conversation content (user text, assistant text + thinking)
• system_events - System records (turn_duration, api_error, stop_hook_summary)
• queue_operations - User inputs queued during assistant response
• pr_links - Session-to-PR mappings
• _sessions_index - Session metadata from sessions-index.json (summary, first_prompt)
• _loaded_files - File mtime tracking for incremental loading

Canonical Reference Tables

• agent_sessions - Canonical sessions with id primary key and external_session_id
• agent_raw_events - Raw imported events keyed by agent_session_id
• agent_contexts - Context changes keyed by agent_session_id / agent_raw_event_id
• agent_events - Normalized events keyed by agent_session_id, agent_context_id, agent_raw_event_id
• agent_parts - Structured event parts keyed by agent_event_id
• agent_tool_calls - Tool invocations keyed by agent_event_id
• agent_tool_results - Tool outputs keyed by agent_event_id / agent_tool_call_id
• agent_tokens - Token and cost summaries keyed by agent_event_id

Views

• turn_durations - Response timing from system events
• api_errors - API error events
• session_overview - Sessions joined with index metadata
• interactions - Unified view (Claude + Codex + Cursor)
• conversation_search - Messages with content/thinking previews
• session_messages - Per-session aggregation with topic
• recent_conversations - Last 50 sessions
• conversation_pairs - User/assistant turns joined on parent_uuid
• message_stats - Daily message volume by harness/role
• repo_activity - Repository-level summary (aggregates worktrees)
• project_activity - Project-level with worktree info
• usage_with_cost - Tool invocations with pre-calculated cost_usd
• cost_summary - Pre-aggregated costs by repo/model
• model_pricing - API rates (editable)

Run --schema for complete documentation. When the script is installed standalone, copy the same canonical schema file to ~/.local/share/analyze-usage/ so bootstrap still uses the checked-in DDL.

Testing

uv run skills/analyze-usage/tests/test_analyze_usage.py

This regression harness covers fresh bootstrap, canonical schema discovery, and legacy upgrade behavior for update.