AI Coding Usage

Use scripts/analyze-usage to load local Claude Code, Codex, Cursor, and Pi logs into DuckDB, then answer usage questions with explicit scope and freshness. Treat the database as an activity ledger—not a productivity score or authoritative provider bill.

Required workflow

1. Run scripts/analyze-usage update before current-state analysis. Stop if it fails; updates are atomic, so a failure leaves the prior database intact.
2. Run scripts/analyze-usage --schema when constructing unfamiliar queries.
3. Choose a metric whose unit matches the question. Read references/analysis-guide.md for query patterns and interpretation boundaries.
4. Report the database refresh time, time window, included harnesses, and any unknown-priced models with the result.
5. For shareable or repeatable reporting, use report with explicit UTC boundaries. Verify pricingCoverage, reconcile the provider/model totals, and keep the generated aggregate JSON private unless the user chooses a publication surface.

Do not compare raw interactions counts across harnesses as if they were equivalent: Claude Code, Codex, and Pi rows are tool calls, while Cursor rows are prompts. Use messages, sessions, active days, or per-source trends for cross-harness comparisons.

Commands

scripts/analyze-usage update
scripts/analyze-usage --schema
scripts/analyze-usage query "SELECT * FROM tool_summary"
scripts/analyze-usage report --from 2026-06-13T17:00:00Z --to 2026-07-12T17:00:00Z --output report.json
scripts/analyze-usage search "memory"
scripts/analyze-usage search "don't panic" --fts
scripts/analyze-usage shell
scripts/analyze-usage reload

update detects changed and deleted files with nanosecond mtime plus file size, then publishes the new database atomically. reload rebuilds atomically and keeps a timestamped backup of the prior database.

report emits the versioned analyze-usage-report/v1 aggregate contract. Its default window is the full observed archive; --from is inclusive and --to is exclusive. The report includes overall and per-harness archive coverage beside report-window activity, provider/model/repository token and cost ledgers, cache effects, and priced/unpriced coverage. Cursor activity is present even when the focus window is empty and even though its source does not expose token accounting. Report output excludes prompts, responses, reasoning, tool arguments, paths, and session identifiers.

Analysis surfaces

• messages, sessionmessages, sessionoverview, and message_stats support conversation and session analysis across harnesses.
• claudetools, codextools, pitools, and toolsummary support tool-call analysis.
• interactions, repoactivity, projectactivity, and time views support activity trends; preserve their per-source measurement units. interactions.timestamp is UTC and local_timestamp drives calendar views in DuckDB's system timezone.
• codextokencounts stores Codex token snapshots; pi_usage stores Pi per-message tokens with harness-recorded dollars.
• usagewithcost calculates Claude API-equivalent cost once per assistant turn; codexusagewithcost applies verified OpenAI Standard API rates and long-context rules to Codex token snapshots; piusagewithcost exposes Pi's harness-recorded dollars (pricing_status = 'native', not an estimate).
• providerusagewithcost normalizes Claude, Codex, and Pi tokens and cost components (plus Claude/Codex no-cache baselines and cache savings; Pi has no local rates, so its baseline columns are NULL). Aggregate with providercostsummary or cacheefficiency_summary.
• conversation_search and search cover user text, assistant text, and optional reasoning traces.

Unknown models have pricingstatus = 'unknownmodel' and costusd = NULL; never substitute a guessed rate. modelpricing and codexmodelpricing are user-editable and built-in defaults are only inserted when missing. Claude/Codex dollar values are API-equivalent workload estimates and Pi dollar values are harness-recorded, but none are subscription invoices or observed purchased-credit spend.

Data boundaries

The database contains sensitive local material: prompts, assistant text, reasoning traces, developer instructions, tool arguments, paths, and PR metadata. Keep the database and backups private and avoid returning raw conversation content unless the user asks for it.

The agent_* tables from references/canonical-agent-schema.duckdb.sql are an empty reference schema. Current loaders populate the harness-specific tables and views, not the canonical tables.

Verification

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

The regression suite covers bootstrap, legacy migration, sparse and incremental Claude/Codex ingestion, real Cursor SQLite ingestion, deletion and same-second change detection, atomic failure handling, quoted paths and search, turn-level provider costs, long-context pricing, cache savings, editable pricing, repository provenance, local calendar views, report-window boundaries, cross-harness reconciliation, deterministic output, and aggregate-only privacy.