On-Chain SQL + RAG Analytics Agent

Ask questions in plain English about live Zora token data or the project's analytics methodology. The agent routes quantitative questions to guarded SQL and conceptual questions to hybrid document retrieval, then returns the answer together with SQL rows and/or cited chunks.

The model provider is swappable: Anthropic and OpenAI API adapters are available, while claude_code uses an authenticated Claude Code Pro/Max subscription without API-token billing.

Tests: 107 tests currently pass, including the SQL attack suite, circuit/cache behavior, MCP protocol handshake, retrieval provenance, and eval scorers.

What this demonstrates

Capability	Implementation
Bounded agentic tool use	run_sql + search_docs, with at most two tool executions in agent/service.py
Defense-in-depth SQL safety	Fail-closed SQLGlot AST policy, allowlisted tables, row/timeout caps, and a SELECT-only role
Hybrid RAG / vector database	pgvector cosine search + PostgreSQL FTS + Reciprocal Rank Fusion in retrieval/
MCP integration	Official MCP Python SDK, FastMCP, stdio + Streamable HTTP in mcp_server/
OpenTelemetry observability	OpenTelemetry-native Langfuse spans, structured stdout, and query_logs
Resilience and cost control	Transient retries, provider circuit breaker, normalized answer cache, stale fallback
Measured quality	A 44-case adversarial SQL harness plus a real 28-case retrieval ablation in eval/
Provider independence	Anthropic, OpenAI, or local Claude Code subscription through the same LLMClient protocol

Architecture

flowchart LR
    Z[Zora explorer] --> I[Scheduled indexer]
    I --> P[(Postgres + pgvector)]
    U[Streamlit / API client] --> A[FastAPI /ask]
    A --> G[Bounded agent]
    G -->|run_sql| S[AST SQL guard]
    S -->|read-only transaction| P
    G -->|search_docs| R[Dense + FTS + RRF]
    R -->|guarded SELECTs| S
    P --> G
    G --> U
    C[MCP client] --> M[FastMCP stdio / HTTP]
    M --> S
    T[OpenTelemetry spans] --> L[Langfuse optional]
    G --> T
    M --> T

Quickstart

Prerequisites: Docker Desktop and Python 3.11+.

cp .env.example .env
python3 -m venv .venv
.venv/bin/pip install -e '.[all,dev]'
docker compose up -d postgres

New database volumes initialize the schema and roles automatically. To upgrade an existing project volume after pulling this version, apply the additive schema once:

docker compose exec -T postgres \
  psql -v ON_ERROR_STOP=1 -U postgres -d zora_analytics -f /opt/zora/schema.sql

Index the bundled Markdown corpus. The first command allows one download of the 67 MB quantized BGE ONNX model; subsequent embedding runs work from the local cache without an API key:

.venv/bin/zora-retrieval-index --allow-model-download

Run with a Claude subscription

Confirm that the host CLI is authenticated:

claude auth status

Set these values in .env, then run the application on the host so it can invoke your logged-in claude binary:

LLM_PROVIDER=claude_code
CLAUDE_CODE_MODEL=sonnet
CLAUDE_CODE_EFFORT_LEVEL=low

The model alias currently selects the latest Sonnet, while the explicit low-effort setting keeps routine Text-to-SQL requests from inheriting a higher global Claude Code effort. To favor maximum speed over SQL reliability, set CLAUDE_CODE_MODEL=haiku; Haiku does not use an effort level.

Run the API:

.venv/bin/uvicorn app.main:app --host 127.0.0.1 --port 8000

In a second terminal:

API_BASE_URL=http://127.0.0.1:8000 \
  .venv/bin/streamlit run ui/streamlit_app.py --server.address 127.0.0.1 --server.port 8501

Open the API docs or the chat UI. Claude Code subscription usage reports cost_usd: null; it does not pretend subscription calls have API-token cost.

Run with an API provider

Set either ANTHROPIC_API_KEY or OPENAI_API_KEY and the matching LLM_PROVIDER. The fully containerized path is then. Compose mounts the host .cache/fastembed directory read-only, so the model cached during the indexing step is reused without another download:

docker compose up --build

Health and direct query examples:

curl http://127.0.0.1:8000/health
curl -X POST http://127.0.0.1:8000/ask \
  -H 'Content-Type: application/json' \
  -d '{"question":"Who are the top 10 current holders?"}'

Guardrails

Every model- or MCP-supplied query goes through one SQLGuard and one SQLExecutor. The guard:

• accepts exactly one read-only SELECT;
• allowlists tokens, holders, transfers, and retrieval embeddings;
• blocks writes, DDL, system schemas, cross-database references, locks, and dangerous functions;
• preserves pgvector's <=> cosine operator only on embeddings.embedding;
• injects or clamps LIMIT, while the executor sets TRANSACTION READ ONLY and a statement timeout.

The database reader role is a separate safety boundary. A parser regression still cannot turn the reader credential into a writer.

Resilience and cache

Provider calls receive transient-only exponential-backoff retries. After four consecutive failures by default, the process-wide provider/model circuit opens for 30 seconds. These values are controlled by LLM_PROVIDER_RETRY_ATTEMPTS, LLM_CIRCUIT_FAILURE_THRESHOLD, and LLM_CIRCUIT_RESET_SECONDS.

Successful responses are cached in answer_cache by a hash of:

1. case-folded, punctuation/whitespace-normalized question;
2. tracked token address;
3. current introspected schema hash.

A fresh hit skips the model. If the provider is unavailable, an expired last-good response can be served explicitly as stale. Provider and guard failures return HTTP 200 with a machine-readable contract instead of a 5xx:

{
  "status": "degraded",
  "answer": "I couldn't answer that confidently.",
  "reason": "provider_unavailable",
  "last_error": "...",
  "served_from_cache": false
}

GET /health includes answer-cache lookups, hits, stale hits, and process-local hit rate.

Observability

observability/tracing.py is a deliberately thin span API. One trace covers every /ask or MCP call, with nested llm.generate, tool.run_sql, tool.search_docs, guard.validate, db.execute, and retrieval.* spans. Provider/model, tokens, nullable cost, latency, retries, guard decision, and row counts use the same values returned by the API and written to query_logs.

Langfuse is optional. If any of LANGFUSE_PUBLIC_KEY, LANGFUSE_SECRET_KEY, or LANGFUSE_HOST is missing, tracing runs as a no-op and cannot break a request. For Langfuse Cloud, create a project, put its three values in .env, and restart the API.

For local Langfuse v3:

docker compose --profile observability up -d

Open localhost:3000, create the first project/key pair, place the keys in .env, and set LANGFUSE_HOST=http://localhost:3000 for a host-run API. For the Compose API, set LANGFUSE_DOCKER_HOST=http://langfuse-web:3000. The optional LANGFUSE_INIT_* variables perform the same organization/project/user setup headlessly on first startup. Change all LANGFUSE_* local secrets before exposing that profile. OTEL_SERVICE_NAME defaults to zora-analytics-agent so new traces have a stable service identity instead of an anonymous OpenTelemetry resource.

A local smoke run on 2026-07-18 sent a real MCP describe_schema call through this instrumentation: Langfuse stored one mcp.call trace with a nested db.execute span. The local project uses a non-personal demo identity; no developer account information is attached to the trace.

Langfuse's current Python SDK is OpenTelemetry-native, so the instrumentation is not coupled to a custom agent framework and can coexist with another OTel backend.

MCP server

The MCP server is a parallel interface to the same guarded executor. It exposes:

• run_sql(query) — structured rows or a structured guard rejection;
• describe_schema() — the runtime-introspected analytics schema;
• data_freshness() — token watermark plus latest sync run;
• top_holders(limit=10) — a curated query, still passed through the guard.

Claude Desktop claude_desktop_config.json:

{
  "mcpServers": {
    "zora-analytics": {
      "command": "/absolute/path/to/zoraHoldersAI/.venv/bin/zora-mcp",
      "args": ["--transport", "stdio"],
      "env": {
        "DATABASE_URL": "postgresql://zora_app:zora_app@localhost:55432/zora_analytics",
        "READ_ONLY_DATABASE_URL": "postgresql://zora_reader:zora_reader@localhost:55432/zora_analytics"
      }
    }
  }
}

Replace only the absolute repository path and, if necessary, the mapped PostgreSQL port. Any MCP-compatible client can use the same stdio command. Streamable HTTP is one line:

.venv/bin/zora-mcp --transport streamable-http --host 127.0.0.1 --port 8001

Clients connect to http://127.0.0.1:8001/mcp.

Hybrid retrieval

The curated corpus in retrieval/corpus/ covers protocol scope, on-chain terminology, timestamp semantics, safety, and system methodology. Heading-aware chunks keep stable provenance such as methodology#meaning-of-first-seen.

For a query, the retriever runs two guarded SELECTs over the same PostgreSQL instance:

1. BGE-small dense cosine search through pgvector;
2. English full-text search through a generated tsvector + GIN index;
3. deterministic RRF fusion; optionally, a local FastEmbed cross-encoder reranker.

No paid embedding API is required. EMBEDDINGS_PROVIDER=openai remains an explicit option, while fastembed is the default. With FASTEMBED_LOCAL_FILES_ONLY=true, a cached BGE model is used without network; if it is not cached, a deterministic local hash embedding keeps the feature fail-soft and is clearly labeled as a fallback rather than BGE benchmark output.

Retrieval eval results

The table below came from a real local run on 2026-07-18 against 28 golden questions and 33 indexed chunks. It was generated with python -m eval.run_retrieval --allow-model-download --judge; no values were hand-waved.

<!-- RETRIEVAL_RESULTS_START --> Real retrieval run using BAAI/bge-small-en-v1.5; ranking cutoff k=5.

Configuration	Precision@5	Recall@5	MRR	Groundedness	Status
Dense only	22.14%	89.29%	0.8810	96.43%	completed
Dense + FTS (RRF)	22.86%	92.86%	0.9315	92.86%	completed
Hybrid + reranker	23.57%	94.64%	0.9643	96.43%	completed

An unavailable reranker is reported explicitly and is never relabeled as a reranked run. <!-- RETRIEVAL_RESULTS_END -->

Run it again with:

.venv/bin/zora-retrieval-eval --allow-model-download

Add --judge to generate evidence-only answers and reuse the provider-neutral groundedness judge. The default gate fails if hybrid MRR drops below dense-only MRR. Raw per-question results live in eval/results/retrieval-*.json.

SQL eval results

<!-- EVAL_RESULTS_START -->

Metric	Value
Execution accuracy	100.00%
Valid-SQL rate	100.00%
Clarification accuracy	100.00%
Answer groundedness	88.64%
Mean latency	7.01 s
p95 latency	10.60 s
Mean cost / query	n/a

Cases: 44 total / 37 executable.

<details> <summary>Per-case results</summary>

ID	Difficulty	Check	Correct	Valid SQL	Latency	Cost
q001	easy	result_set_match	✓	✓	6.77 s	n/a
q002	easy	numeric_match	✓	✓	5.91 s	n/a
q003	easy	result_set_match	✓	✓	7.32 s	n/a
q004	easy	numeric_match	✓	✓	6.73 s	n/a
q005	easy	numeric_match	✓	✓	5.87 s	n/a
q006	easy	numeric_match	✓	✓	5.59 s	n/a
q007	easy	numeric_match	✓	✓	5.01 s	n/a
q008	easy	numeric_match	✓	✓	5.67 s	n/a
q009	easy	numeric_match	✓	✓	5.23 s	n/a
q010	easy	result_set_match	✓	✓	7.92 s	n/a
q011	easy	numeric_match	✓	✓	7.90 s	n/a
q012	easy	result_set_match	✓	✓	6.40 s	n/a
q013	easy	numeric_match	✓	✓	5.14 s	n/a
q014	medium	numeric_match	✓	✓	7.70 s	n/a
q015	medium	result_set_match	✓	✓	6.29 s	n/a
q016	hard	numeric_match	✓	✓	6.74 s	n/a
q017	medium	numeric_match	✓	✓	7.48 s	n/a
q018	easy	numeric_match	✓	✓	5.26 s	n/a
q019	medium	result_set_match	✓	✓	9.64 s	n/a
q020	medium	numeric_match	✓	✓	5.86 s	n/a
q021	medium	numeric_match	✓	✓	7.33 s	n/a
q022	medium	numeric_match	✓	✓	5.89 s	n/a
q023	medium	numeric_match	✓	✓	10.15 s	n/a
q024	medium	numeric_match	✓	✓	10.60 s	n/a
q025	medium	result_set_match	✓	✓	6.52 s	n/a
q026	medium	result_set_match	✓	✓	8.19 s	n/a
q027	medium	numeric_match	✓	✓	5.85 s	n/a
q028	medium	numeric_match	✓	✓	7.21 s	n/a
q029	hard	result_set_match	✓	✓	8.62 s	n/a
q030	hard	result_set_match	✓	✓	9.81 s	n/a
q031	hard	numeric_match	✓	✓	9.40 s	n/a
q032	hard	numeric_match	✓	✓	7.88 s	n/a
q033	medium	result_set_match	✓	✓	5.90 s	n/a
q034	medium	numeric_match	✓	✓	6.63 s	n/a
q035	ambiguous	clarification	✓	n/a	4.12 s	n/a
q036	ambiguous	clarification	✓	n/a	3.46 s	n/a
q037	ambiguous	clarification	✓	n/a	3.60 s	n/a
q038	ambiguous	clarification	✓	n/a	4.27 s	n/a
q039	hard	result_set_match	✓	✓	12.75 s	n/a
q040	hard	numeric_match	✓	✓	10.36 s	n/a
q041	hard	result_set_match	✓	✓	15.44 s	n/a
q042	unsupported	clarification	✓	n/a	4.46 s	n/a
q043	unsupported	clarification	✓	n/a	6.10 s	n/a
q044	ambiguous	clarification	✓	n/a	3.38 s	n/a

</details> <!-- EVAL_RESULTS_END -->

This is a bounded regression benchmark over one indexed token snapshot, not a claim of universal Text-to-SQL accuracy. The set deliberately includes multi-relation joins, unavailable-data rejections, and ambiguous questions rather than only straightforward aggregate queries. The published run used the sonnet alias at high effort; the application now defaults to low effort for subsequent interactive queries. Changing inference settings does not alter historical results.

Evaluation-engineering highlights:

• Execution accuracy is based on database result comparison, not generated-SQL string similarity.
• A broken or timed-out reference query invalidates the run instead of counting as an agent error.
• Result-set matching remains strict, while numeric comparisons use narrow rounding tolerance.
• The groundedness judge measures faithfulness separately from SQL correctness; calibration and answer-grounding work raised the measured score from 26.32% to 88.64%.
• The independent retrieval ablation improved Recall@5 from 89.29% to 94.64% (+5.35 percentage points), with sparse retrieval changing 26 of 28 rankings.

SQL execution accuracy compares each generated query's actual result set with a hand-written reference query. It is order- and alias-insensitive and tolerance-aware for numeric answers. Clarification cases accept either a direct clarification or a cited, row-free, SQL-free rejection; an unsupported metric answered with guessed SQL still fails.

Repository structure

.github/         CI for lint, formatting, tests, and both Compose configurations
agent/           bounded tool loop, cache, circuit breaker, prompts
app/             FastAPI: /ask, /admin/sync, /health
db/              Postgres/pgvector schema, roles, schema introspection
eval/            SQL golden eval + retrieval golden set/ablation
indexer/         scheduled Zora holder/transfer synchronization
llm/             Anthropic, OpenAI, and Claude Code adapters
mcp_server/      official FastMCP server and CLI
observability/   Langfuse/Otel spans + structured/query-table logging
retrieval/       corpus, chunking, embeddings, indexing, dense/FTS/RRF
sql_guard/       fail-closed AST guard + read-only executor
ui/              Streamlit chat
tests/           package-mirrored unit and protocol tests

Development

.venv/bin/pip install -e '.[all,dev]'
.venv/bin/pytest -q
.venv/bin/ruff check .
docker compose config --quiet

The optional dependency groups are observability, mcp, and retrieval; all is used by the runtime Docker image. No LangChain or LlamaIndex dependency is used.

License

MIT. See LICENSE.