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.

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)"]
    E --> P["LanceDB store<br/>(~/.claude/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.

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            # ruff + fmt + ty + pytest

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 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

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).
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

# Provenance: resolve a merged commit back to the transcript that wrote it.
claude-sql resolve "$(git rev-parse HEAD)"
claude-sql review-sheet "$(git rev-parse HEAD)" --no-dry-run

# Eval gym: pre-register a study, run the judge panel, gate on agreement.
claude-sql judges
claude-sql freeze rubric.yaml --panel kimi-k2.5,deepseek-v3.2,glm-5
claude-sql judge <manifest_sha> --sessions-parquet sessions.parquet \
  --output-parquet scores.parquet --no-dry-run
claude-sql kappa scores.parquet --floor 0.6

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, judge, review-sheet) 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)

Embeddings + structure

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

Skills catalog + cache maintenance

Eval gym (cross-provider judge panels + agreement gates)

Transcript ↔ PR provenance (RFC 0001)

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 (also reused when resolve / review-sheet find no binding). Two command-specific codes: kappa exits 66 when an agreement floor or delta-gate is tripped, and 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 config.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 config.py for the full set.

Development

mise run check           # lint + fmt-check + typecheck + tests
mise run fmt:write       # auto-apply ruff formatting
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 src/ tests/ across the whole tree (strict mode), 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)
mise run changelog       # regenerate CHANGELOG.md without bumping

[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 + test) 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.).
• Global CRIS for Cohere. The global.cohere.embed-v4:0 profile sustains the highest throughput with no throttling in testing; direct and US CRIS both saturate at low TPM.
• 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.
• Pre-registered eval gym. The judges / freeze / judge / kappa family grades transcripts with a cross-provider Bedrock judge panel (8 non-Anthropic/non-Amazon primaries + a within-family Anthropic holdout to measure self-preference bias), then scores inter-rater agreement with Cohen's + Fleiss' kappa and bootstrapped 95% CIs. freeze writes an immutable study manifest under ~/.claude/studies/<sha>/ so a run is reproducible (replay); blind-handover strips identity markers before grading; kappa exits 66 below the agreement floor. No DuckDB views or list-cache parquets — output goes to caller-named parquets stamped with the freeze SHA.
• Transcript ↔ PR provenance. bind writes a host-neutral binding between a merged commit and the transcript that produced it, encoded as three git-interpret-trailers trailers plus a JSON note under refs/notes/transcripts. resolve reads it back (trailer-first, note-fallback, loud on digest disagreement); review-sheet compresses the bound transcript into a Markdown/JSON PR review sheet via Sonnet 4.6. Pure-stdlib, no new git infrastructure — see docs/rfc/0001-transcript-pr-binding.md.

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.
• RFC 0002 — vision + roadmap — the v2 data model and production direction.

License

Apache 2.0. See LICENSE.