claude-sql

Ask your Claude Code transcripts anything. Your sessions are already on disk. claude-sql turns them into a searchable, explorable, self-improving record of your work — in place, with zero copy.

v2.0.0. The codebase is a strict hexagonal architecture — domain / application (ports) / infrastructure (adapters) / interfaces — enforced by an import-linter contract. Embeddings are pluggable behind an EmbeddingProvider port: Cohere Embed v4 on Bedrock is the default, with Ollama and a local ONNX BGE (BAAI bge) adapter behind optional [ollama] / [onnx] extras. The v1 evals/ and provenance/ planes (judge panel, kappa, freeze/replay, transcript↔PR binding) were dropped and move to a separate eval project. Design and migration notes live in docs/v2/DESIGN.md and docs/v2/MIGRATION.md.

What you get out of it

Remember what you worked on.

• "What was that thing I did last Tuesday with DuckDB and HNSW?"
• "Show me every conversation I've had about temporal workflows, ranked by relevance."
• "Which week did I finally figure out that memory bug?"

See where your time and money actually go.

• "Which sessions cost me more than $5 on Opus this month — and what was I trying to do?"
• "Which tools am I leaning on most? Which ones fail the most?"
• "Where am I spending hours on prose vs. on tool calls?"

Notice patterns in how you work.

• "When do I hand-hold the agent step-by-step vs. let it run on its own? Has that shifted over time?"
• "What kinds of work am I doing most — coding, strategy, admin, writing?"
• "Which session types actually finish successfully vs. trail off?"
• "Which todos do I create and never close out?"

Surface themes across hundreds of conversations.

• "Group my sessions by what they're about and tell me what moved this month."
• "Show me the biggest themes in my work and what's trending."
• "When I've wrestled with the same problem across multiple sessions, group them together so I can see the arc."

Catch yourself disagreeing with yourself.

• "Find sessions where I took two opposing positions on the same decision — and flag which ones got resolved vs. abandoned."

Spot where the agent left you hanging.

• "Which sessions had me pinging for status the most? What was the agent doing?"
• "Show me every time I asked a one-word question like screenshot? because the agent didn't proactively share one."
• "Rank sessions by how often I had to interrupt or correct the agent."

claude-sql turns every one of those into a SQL query that runs in under a second on the live JSONL corpus — no export, no pipeline.

How it works

flowchart LR
    J["~/.claude/projects/<br/>*.jsonl"] -->|read_json| R[raw views]
    J2["subagents/<br/>agent-*.jsonl"] -->|read_json| R
    R --> V[business views]
    V --> Q[["claude-sql query<br/>claude-sql explain<br/>claude-sql schema"]]
    V --> E["claude-sql embed<br/>(Cohere Embed v4 on Bedrock,<br/>or Ollama / ONNX BGE)"]
    E --> P["LanceDB store<br/>(corpora/&lt;corpus_key&gt;/embeddings_lance/<br/>FLOAT[1024] + IVF_HNSW_SQ index)"]
    P --> S[["claude-sql search"]]
    V --> L["claude-sql classify / trajectory /<br/>conflicts / friction (Sonnet 4.6 +<br/>output_config.format)"]
    L --> PA["session_classifications/, message_trajectory/,<br/>session_conflicts/, user_friction/<br/>(sharded part-*.parquet)"]
    P --> C["claude-sql cluster + terms<br/>(UMAP + HDBSCAN + c-TF-IDF)"]
    C --> PC["clusters + cluster_terms"]
    P --> CM["claude-sql community<br/>(Leiden + CPM over mutual-kNN centroids)"]
    CM --> PM["session_communities<br/>parquet"]
    PA --> AV[analytics views + macros]
    PC --> AV
    PM --> AV
    AV --> Q

Every parquet is cached and rebuilt only on explicit re-run. Views register over whichever parquets exist at connection open — missing ones warn and no-op, never crash.

Caches are corpus-scoped: they live under claude_sql_home()/corpora/<corpus_key>/, keyed by the effective corpus root (team_corpus_root > CLAUDE_CONFIG_DIR > ~/.claude, which maps to the reserved key default). Switching corpus can therefore never read another corpus's analytics parquets; each corpus builds and reads its own. Caches written by earlier versions at the claude_sql_home() root are auto-relocated into corpora/default/ once, on first connect.

Install

As a uv tool (recommended)

claude-sql is published to PyPI. Install the CLI into an isolated, uv-managed venv on your PATH:

uv tool install claude-sql
claude-sql --version

uv tool upgrade claude-sql      # pull the latest release
uv tool uninstall claude-sql    # remove it

Or run it without installing anything persistent:

uvx claude-sql schema

From a local checkout (latest unreleased commits)

To run ahead of the last PyPI release, install from a clone. mise run tool:install wraps uv tool install --from . claude-sql --force --reinstall:

git clone https://github.com/theagenticguy/claude-sql.git
cd claude-sql
mise run tool:install     # → uv tool install --from . claude-sql --force --reinstall
claude-sql --version

Upgrade after pulling new commits by re-running the same task (the tool:upgrade alias runs an identical command with clearer intent):

git pull
mise run tool:upgrade     # reinstall from the checkout, not from PyPI
mise run tool:uninstall   # → uv tool uninstall claude-sql

Project install (for development)

git clone https://github.com/theagenticguy/claude-sql.git
cd claude-sql
mise install              # fetch pinned Python + uv
mise run install          # uv sync --all-extras + install lefthook git hooks
mise run check            # 6 gates: lint + fmt + typecheck + lint:imports + test + proofs

mise auto-activates .venv on cd. Every command below is also available as a mise task — run mise tasks for the full list.

mise run install also installs the lefthook git hooks. Re-run mise run hooks:install any time lefthook.yml changes.

AWS credentials

Semantic search (with the default cohere-bedrock embedding provider) and Sonnet classification require Bedrock access.

export AWS_PROFILE=your-profile

The IAM policy needs bedrock:InvokeModel on:

• inference-profile/global.cohere.embed-v4:0
• inference-profile/global.anthropic.claude-sonnet-4-6

The embedding provider is pluggable: Cohere Embed v4 on Bedrock is the default, alongside a local Ollama server ([ollama] extra) and a local ONNX BGE model ([onnx] extra), so the Cohere inference profile is required only when Bedrock is the active embedding provider (CLAUDE_SQL_EMBEDDING_PROVIDER=cohere-bedrock, the default). Sonnet classification stays on Bedrock.

Reading transcripts from S3

claude-sql reads the local JSONL corpus by default, but any transcript glob can be an s3:// URI instead — point it at sessions mirrored to S3 by the claude-agent-sdk S3SessionStore (layout s3://{bucket}/{prefix}{project}/{session}/part-*.jsonl). DuckDB reads the parts zero-copy over HTTP range requests — no download step — and every view and macro works unchanged.

# Personal corpus on S3 instead of ~/.claude/projects.
export CLAUDE_SQL_DEFAULT_GLOB='s3://my-bucket/transcripts/*/*/part-*.jsonl'
export AWS_PROFILE=your-profile        # credentials via the standard AWS chain
claude-sql schema
claude-sql query "SELECT session_id, started_at FROM sessions ORDER BY started_at DESC LIMIT 10"

claude-sql loads DuckDB's httpfs extension and creates a credential_chain S3 secret automatically when it sees an s3:// glob — no keys are embedded anywhere. For a non-AWS store (MinIO) or a local mock, set CLAUDE_SQL_S3_ENDPOINT, CLAUDE_SQL_S3_URL_STYLE=path, and CLAUDE_SQL_S3_USE_SSL=false. The IAM policy needs s3:GetObject + s3:ListBucket on the prefix.

Quick tour

# Inspect every registered view + macro.
claude-sql schema

# Opus sessions over $5 in the last 30 days.
claude-sql query "
  SELECT session_id, model_used(session_id) AS model,
         cost_estimate(session_id) AS usd
  FROM sessions
  WHERE started_at >= current_timestamp - INTERVAL 30 DAY
    AND model_used(session_id) LIKE '%opus%'
    AND cost_estimate(session_id) > 5.0
  ORDER BY usd DESC
"

# See the EXPLAIN plan (static by default — no execution).
claude-sql explain "SELECT * FROM messages WHERE session_id = '<uuid>' LIMIT 1"

# Drop into the DuckDB REPL with everything pre-registered.
claude-sql shell

# Backfill embeddings (Cohere Embed v4 via global CRIS by default;
# --embedding-provider {cohere-bedrock,ollama,onnx-bge} to switch).
claude-sql embed --since-days 30

# Semantic search.
claude-sql search "temporal workflow determinism" --k 10

# Classify every recent session (dry-run prints a cost estimate first).
claude-sql classify --dry-run --since-days 30
claude-sql classify --no-dry-run --since-days 30

# Friction classifier — status pings, unmet expectations, interruptions.
claude-sql friction --dry-run --since-days 14
claude-sql friction --no-dry-run --since-days 14
claude-sql query "SELECT * FROM friction_counts(14)"
claude-sql query "SELECT * FROM friction_examples('unmet_expectation', 10)"

# Seed the Skills catalog from ~/.claude/skills + ~/.claude/plugins/cache.
claude-sql skills sync
claude-sql skills ls --kind plugin-skill | head
claude-sql query "SELECT * FROM skill_rank(30) LIMIT 15"
claude-sql query "SELECT * FROM skill_source_mix(30) WHERE skill_id LIKE '%erpaval%'"
claude-sql query "SELECT * FROM unused_skills(30) LIMIT 20"

# Full analytics pipeline (includes a zero-cost `skills sync` at step 0).
claude-sql analyze --since-days 30 --no-dry-run

More recipes in docs/cookbook.md (v1: sessions, messages, tools, todos, subagents, semantic search) and docs/analytics_cookbook.md (v2: clusters, communities, classifications, trajectory, conflicts, friction).

CLI surface

Every subcommand shares the top-level flags: --verbose / --quiet, --glob, --subagent-glob, and --format {auto,table,json,ndjson,csv}. The LLM-classification commands (classify, trajectory, conflicts, friction, analyze) and the cache-rewriting ones (cache compact, cache migrate, ingest) default to --dry-run. embed and search are the exceptions — they spend Bedrock the moment you call them (an embed backfill and a single query-vector call, respectively), so they have no dry-run gate.

Query + introspection (zero cost)

watch — near-live freshness

The raw readers materialize as DuckDB TEMP TABLEs, so freshness is a property of a process, not of the corpus: any long-lived reader answers from the instant it registered. watch holds a connection and re-reads only the transcript files whose mtime moved — measured 0.22 s per refresh versus 6.2 s for a full re-registration over 9,082 files.

claude-sql watch                       # OS events (needs the `watch` extra), free
claude-sql watch --embed-limit 512     # also keep semantic search fresh (spends)
claude-sql watch --force-polling       # network mounts where inotify never fires

Changes are coalesced per source file and flushed once the file has been quiet for --quiet-period seconds — the "turn finished writing" signal, observed rather than announced by a hook. --max-wait bounds how long a continuously-appended transcript can go unrefreshed, so an active session still lands on a fixed cadence. The event stream decides when to refresh, never what: each flush rescans the watermark, so a dropped OS event self-heals.

There is deliberately no one-shot mode. A fresh process has no prior snapshot to advance, so it pays the full registration regardless; incrementality only pays inside a resident process. snapshot_as_of(con) is the accessor for an embedding consumer that needs to know what instant its answers cover.

Embeddings + structure

LLM analytics (Sonnet 4.6 — default --dry-run)

Skills catalog + cache maintenance

The v1 eval-gym commands (judges, freeze, replay, blind-handover, judge, ungrounded-claim, kappa) and provenance commands (bind, resolve, review-sheet) were removed in v2.0.0; they move to a separate eval project. The last release carrying them is v1.2.1.

Agent-friendly defaults

• --format auto emits a human table on a TTY and JSON when stdout is piped, so agents calling claude-sql via subprocess get JSON for free. json, ndjson, and csv are always available explicitly.
• Classified exit codes for DuckDB errors — 64 for invalid input (malformed flags or unparseable SQL), 65 for unknown view / column / macro, 70 for other runtime errors, and 2 when search is called before embed has run. One command-specific code: shell exits 127 when the system duckdb binary isn't on PATH. On non-TTY stdout the error also comes back as {"error": {"kind", "message", "hint"}} on stderr, so agents don't have to scrape tracebacks.
• list-cache reports every parquet (embeddings, classifications, trajectory, conflicts, clusters, cluster terms, communities, friction) with its {exists, bytes, mtime, rows}, so an agent can decide whether to run a prerequisite stage before issuing a search or query.
• explain is a static plan by default (no query execution); pass --analyze for EXPLAIN ANALYZE when you want real timings.
• --quiet drops INFO / WARNING logs to ERROR-only. View registration happens at DEBUG level, so the default query stderr is already empty unless something actually warrants attention.

Views

Macros

Environment variables

Every setting in infrastructure/settings.py is overridable via a CLAUDE_SQL_* env var (prefix + the upper-cased field name) or a .env in the working directory. The common knobs are below; the per-stage tuning fields (CLAUDE_SQL_UMAP_*, CLAUDE_SQL_HDBSCAN_*, CLAUDE_SQL_TFIDF_*, the *_THINKING modes, and each analytics parquet path) follow the same prefix convention — see src/claude_sql/infrastructure/settings.py for the full set.

Development

mise run check           # lint + fmt-check + typecheck + lint:imports + tests + proofs
mise run fmt:write       # auto-apply ruff formatting
mise run proofs          # Lean 4 proof layer: lake build under proofs/
mise run upgrade         # uv lock --upgrade && uv sync
mise run build           # uv build → dist/*.whl + *.tar.gz
mise run tool:install    # install claude-sql as a uv tool (global)
mise run cli -- schema   # run the CLI in the project venv
mise tasks               # list every mise task

Git hooks + conventional commits

mise run install installs lefthook git hooks:

• pre-commit — parallel ruff check --fix + ruff format on staged Python files (auto-staged), ty check across the whole tree (strict mode), lint-imports (the hexagonal layers contract), and uv lock --check when pyproject.toml or uv.lock is staged.
• commit-msg — validates the message via cz check --allow-abort against the conventional-commits schema.
• pre-push — runs the full pytest suite before the push lands.

Config lives in lefthook.yml. Reinstall any time that file changes:

mise run hooks:install
mise run hooks:uninstall   # if you need to opt out

Version bumps + changelog (commitizen)

Commit messages follow Conventional Commits. Supported types: feat, fix, docs, style, refactor, perf, test, build, ci, chore, revert. Use mise run commit for the interactive wizard, or write the message yourself — either way the commit-msg hook validates it.

Version bumps are driven by cz bump, which reads commit history and decides MAJOR / MINOR / PATCH from the conventional-commits types:

mise run bump:dry-run    # preview next version + tag
mise run bump            # bump + changelog + annotated tag (vX.Y.Z)

CHANGELOG.md is write-only via cz bump — never hand-edit it. Between releases, query commit history directly (git log --oneline vX.Y.Z..HEAD).

[tool.commitizen] is wired to version_provider = "uv", so every bump keeps pyproject.toml[project.version] and uv.lock in sync atomically.

Quality gates

Local mise run check (lint + fmt + typecheck + lint:imports + test + proofs) and GitHub Actions run the same commands against the same pinned tool versions (via jdx/mise-action), so contributors who pass the hooks locally can trust CI agrees. On top of the local gate, CI layers in:

• Semgrep — p/auto + p/owasp-top-ten rulesets, SARIF uploaded to GitHub code scanning.
• Bandit — Python SAST as defense-in-depth alongside ruff's flake8-bandit (S) selectors. Principled skips align 1:1 with the ruff S-ignores in pyproject.toml. SARIF to code scanning.
• CodeQL — security-and-quality query pack, weekly cron.
• OSV-Scanner — known-CVE scan of uv.lock, fails on findings.
• Betterleaks — secrets sweep over full git history (gitleaks successor). SARIF to code scanning.
• OpenSSF Scorecard — weekly, SARIF to code scanning.
• Codecov — coverage.xml uploaded via tokenless OIDC.
• CycloneDX SBOM — generated + attached on every release.

Local security sweep

mise run security runs all four SAST/SCA/secrets scanners in parallel against the working tree:

mise run security              # bandit + semgrep + osv + leaks → .sarif/
mise run security:bandit       # individual scanners also runnable
mise run security:semgrep
mise run security:osv
mise run security:leaks

Each scanner emits SARIF under .sarif/ (gitignored). The local sweep mirrors what CI uploads to GitHub code scanning, so contributors can reproduce findings without waiting on CI. All four use the "report, don't gate" pattern (--exit-zero / --exit-code=0) — gating happens through code-scanning branch protection, not the scanner exit. See .erpaval/solutions/best-practices/sarif-scanner-report-vs-gate.md for the rationale.

See docs/adr/0015-stack-modernization.md and docs/adr/0016-ci-hardening.md for the full rationale.

Design notes

• Zero-copy reads. read_json(..., filename=true, union_by_name=true, sample_size=-1, ignore_errors=true) so the corpus is queried in place.
• Lazy content blocks. Nested message.content[] stays as JSON and flattens via UNNEST + json_extract_string, not eagerly shredded — resilient to new block types (thinking, MCP shapes, etc.).
• Pluggable embeddings, global CRIS default. Embedding calls go through the EmbeddingProvider port (domain/ports.py) with three adapters: Cohere-on-Bedrock (default), Ollama, and local ONNX BGE. The default global.cohere.embed-v4:0 profile sustains the highest throughput with no throttling in testing; direct and US CRIS both saturate at low TPM. Switching providers changes the vector dimension (Cohere 1024, bge-small 384, bge-base and nomic 768), and even at matching dims the vector spaces are incompatible, so a provider switch requires a full re-embed — a fail-loud guard (domain/embedding_guard.py) refuses to mix vector spaces in one store. See docs/v2/MIGRATION.md.
• LanceDB embeddings store. Vectors and the cosine-metric IVF_HNSW_SQ (scalar-quantized HNSW) index live together in one versioned LanceDB dataset at ~/.claude/embeddings_lance/; DuckDB reads it back via the lance core extension (INSTALL lance; LOAD lance; ATTACH … (TYPE LANCE)). On a fresh install with no Lance table yet, register_vss binds an empty message_embeddings stub so semantic_search still resolves. This replaces the old embeddings/part-*.parquet + ~/.claude/hnsw.duckdb rebuild-at-open combo; the legacy shards migrate over automatically on first connect. Recovery: rm -rf ~/.claude/embeddings_lance/ then claude-sql embed.
• Structured output, GA path. Sonnet 4.6 classification uses Bedrock's GA output_config.format (not tool_use / tool_choice) with adaptive thinking on. Pydantic v2 schemas are flattened (inline $ref, inject additionalProperties: false, strip the numeric / string constraints the validator rejects from Draft 2020-12).
• Determinism. UMAP, HDBSCAN, and Leiden all seed from CLAUDE_SQL_SEED=42 (default) so cluster IDs and community IDs are stable across reruns. The Leiden seed flows into both leidenalg.find_partition(seed=...) and Optimiser.set_rng_seed(...) for the resolution-profile bisection — same seed + same input ⇒ byte-equal parquets across runs.
• Communities = leidenalg + CPM. Reference Leiden implementation (leidenalg>=0.11.0) over an igraph>=1.0 mutual-kNN cosine graph (k=15, edge floor 0.3) of session centroids. CPM γ has direct cosine semantics (internal density ≥ γ, external ≤ γ); auto-γ via Optimiser.resolution_profile + longest-plateau picker; warn-only connectivity check. Output is signal-rich: is_medoid, coherence, gamma_used per row, plus a community_profile sidecar with one row per γ tested, so an LLM agent can ask "what γ would yield 50 communities?" without rerunning Leiden. Top terms per community come from the live community_top_topics(cid, n) macro composed from cluster_terms, not a frozen column. See docs/research_notes.md for the Louvain → Leiden+CPM swap rationale.
• Hybrid friction pipeline. A hand-curated regex bank catches the unambiguous status_ping / interruption / correction cases at zero Bedrock cost; the ambiguous class — especially unmet_expectation (one-word questions like screenshot? that imply the agent missed a proactive step) — falls through to Sonnet 4.6 structured output. Scoped to user-role messages under 300 characters by default; longer turns are almost always genuine task instructions.
• Hexagonal layering, machine-enforced. domain (pure logic, no boto3/duckdb/lancedb) < infrastructure (all adapters) < application (use-cases + ports) < interfaces (the cyclopts CLI). The layers DAG is an import-linter contract in pyproject.toml, checked by the lint:imports gate on every commit. Formal invariants (backoff, Hamming distance, turn ordering) are machine-checked in Lean 4 under proofs/ via the proofs gate.

See docs/research_notes.md for deeper design rationale, and docs/rfc/0002-vision-and-roadmap.md for the data-model + roadmap direction.

Links

• Cookbook (v1) — sessions, messages, tools, todos, subagents, semantic search.
• Analytics cookbook (v2) — clusters, communities, classifications, trajectory, conflicts, friction.
• Research notes — design decisions and tuning knobs.
• JSONL schema reference — column listings for every registered view.
• RFC 0001 — transcript ↔ PR binding — the bind / resolve / review-sheet convention (commands removed in v2.0.0; kept as the design record).
• RFC 0002 — vision + roadmap — the v2 data model and production direction.

License

Apache 2.0. See LICENSE.