chainquery

Ask questions about Hedera mainnet in English. Get back a number and the SQL that produced it.

The query is the citation. Anyone who doesn't believe the number can read the SQL that produced it — a stronger guarantee than "the model said so", and the reason this is a text-to-SQL agent rather than a RAG bot.

Built around three things most LLM side-projects skip: an eval harness whose cases are proven to reject careless queries, a parser-level safety guard that fails closed, and measured cost per question.

---

See it working in two minutes — no API key required

The core of the project needs no Anthropic key. Clone it, ingest real mainnet data, and query it:

git clone https://github.com/codejutsu04/chainquery && cd chainquery
uv venv --python 3.12 && uv pip install -e '.[dev]'
source .venv/bin/activate

chainquery ingest --days 7 --per-day 900    # ~90s, public mirror node
chainquery info                             # what landed, and what it does NOT cover
chainquery sql "SELECT type, count(*) n FROM transactions GROUP BY 1 ORDER BY n DESC LIMIT 5"

Real output, captured from a live warehouse:

$ chainquery sql "SELECT CAST(consensus_at AS DATE) AS day, count(*) AS txns,
                  count(*) FILTER (WHERE result = 'SUCCESS') AS ok
                  FROM transactions GROUP BY 1 ORDER BY 1 DESC"
┏━━━━━━━━━━━━┳━━━━━━┳━━━━━┓
┃ day        ┃ txns ┃ ok  ┃
┡━━━━━━━━━━━━╇━━━━━━╇━━━━━┩
│ 2026-08-02 │ 900  │ 846 │
│ 2026-08-01 │ 900  │ 789 │
│ 2026-07-31 │ 900  │ 822 │
│ 2026-07-30 │ 900  │ 845 │
└────────────┴──────┴─────┘

And the guard, refusing things:

$ chainquery sql "DROP TABLE accounts"
refused: DROP is not a read-only query

$ chainquery sql "SELECT * FROM read_csv('/etc/passwd')"
refused: function read_csv() can reach outside the database

$ chainquery sql "SELECT * FROM duckdb_settings"
refused: unknown table 'duckdb_settings'. Allowed tables: accounts, contracts,
crypto_transfers, ingest_runs, nft_transfers, token_transfers, tokens, transactions

More real captured output — MCP session, guard refusals, test run: docs/DEMO.md.

With an ANTHROPIC_API_KEY set, chainquery ask adds the natural-language layer on top of exactly the same guard and warehouse:

$ chainquery ask "Which 5 accounts hold the most HBAR?"

Prints the generated SQL, the result table, the model's rationale, any assumptions it had to make, then attempts · elapsed · cost · cached tokens. (Shape described rather than pasted — this repo does not show output it hasn't captured.)

---

Data source, and what this repo does not touch

Everything comes from the public Hedera mainnet mirror node at mainnet-public.mirrornode.hedera.com — free, unauthenticated, no key. No private endpoint, no employer infrastructure, no credentials in this repo.

The mirror node is a REST API rather than a bulk export, so the warehouse holds a slice of mainnet, never all of it. Two ingest modes:

• Contiguous (--transactions N) — the most recent N transactions. Covers minutes of wall-clock time, which makes day-over-day questions meaningless.
• Day-stratified (--days 7 --per-day 900) — samples a window from each of the last seven days. Real date buckets, still ~90 seconds to ingest.

Sampling has an obvious failure mode: a per-day count from the warehouse is not a mainnet volume. So the data profile handed to the model states the exact span held per day and says so outright:

Transaction coverage — these are the only periods with data:
  2026-08-02: 900 transactions, 04:01:08–04:06:21 UTC (313s of that day)
  2026-08-01: 900 transactions, 04:01:46–04:06:22 UTC (276s of that day)
  ...
Transactions were SAMPLED from each day above, not collected exhaustively.
Per-day counts are counts within this warehouse, NOT mainnet totals.

A model reporting "Hedera did 900 transactions on August 1st" would be wrong. This is what stops it.

---

Commands

Command	What it does	Needs a key
chainquery ingest	Backfill from the mirror node; --days N --per-day M samples across days	no
chainquery info	Row counts, coverage windows, live data profile	no
chainquery sql "…"	Run your own SQL through the agent's guard	no
chainquery schema	Print the warehouse schema	no
chainquery mcp	Serve the warehouse to any MCP host over stdio	no
chainquery ask "…"	Natural language → SQL → guarded execution → answer	yes
chainquery eval	Run the golden set; write reports to runs/	yes

---

Use it from Claude Desktop, Claude Code, or Cursor

chainquery is also an MCP server.

uv pip install -e '.[mcp]'
chainquery mcp --config     # prints the JSON block to paste into your host

{
  "mcpServers": {
    "chainquery": {
      "command": "/path/to/.venv/bin/chainquery-mcp",
      "env": { "CHAINQUERY_DB": "/path/to/data/hedera.duckdb" }
    }
  }
}

Four tools, all marked readOnlyHint so hosts can skip approval prompts:

Tool	Purpose
describe_schema	CREATE TABLE statements plus the Hedera domain notes
run_sql	Execute a SELECT behind the guard; returns rows or the real DB error
warehouse_profile	Row counts and the periods the slice actually covers
ask_hedera	Let chainquery's own agent write the SQL (needs a key)

There are deliberately two paths, and the interesting one is free. The host already is a language model, so it can call describe_schema, write the SQL itself, and submit it to run_sql — no Anthropic key, no second model call. The guard still applies, and a rejected query comes back with the parser's reason so the host can correct itself.

That split is the point. Most data MCP servers expose a fixed menu of endpoints, so coverage stops where the tool author stopped. Here the tool surface is a schema and a safety boundary, and the questions are open-ended.

---

How it works

question
   │
   ▼
┌─────────────────────────────────────────────────────────┐
│ prompt: [cached prefix: role + schema + domain notes]   │  ← stable, cache_control
│         [volatile: live row counts + coverage windows]  │  ← after the breakpoint
└─────────────────────────────────────────────────────────┘
   │  structured output → {sql, rationale, assumptions}
   ▼
┌─────────────────────────────────────────────────────────┐
│ guard (sqlglot): single statement? read-only? known     │
│ tables? no table-valued functions? LIMIT enforced?      │
└─────────────────────────────────────────────────────────┘
   │  refused → self-repair with the error in context (≤2 rounds)
   ▼
┌─────────────────────────────────────────────────────────┐
│ DuckDB: read-only connection, row cap, query timeout,   │
│ scan-size approval gate                                 │
└─────────────────────────────────────────────────────────┘
   │
   ▼
answer + SQL + usage

The guard

agent/guard.py is the security boundary and it fails closed. It parses the statement rather than pattern-matching it, then requires: exactly one statement, a read-only query (a leading WITH is fine), only allowlisted tables, no table-valued functions, and a LIMIT — injected or tightened if the model omitted one. The connection itself is opened read-only, so the guard is defence in depth rather than the only line.

Table-valued functions are refused by shape, not by a name blocklist, so an extension function nobody has heard of yet is blocked too.

Prompt caching, done deliberately

Caching is a prefix match: any byte that changes early in the prompt invalidates everything after it. So the system prompt is split — the stable block (role, rules, schema DDL, domain notes, worked examples) carries the cache_control breakpoint, and the volatile block (row counts, coverage windows) is appended after it.

Interpolating live row counts into the stable block would silently drop the hit rate to zero on every re-ingest. tests/test_prompt.py asserts the split holds.

Domain knowledge that earns its place

Hedera has traps that produce wrong-but-plausible answers, and they live in the prompt and in the golden set:

• crypto_transfers is double-entry, so summing every leg gives exactly 0.
• Amounts are tinybars, not HBAR (1 HBAR = 1e8).
• Fees are paid as transfer legs to node/network accounts, so they pollute any naive "value moved" figure.
• Token amounts are in each token's smallest unit — you must join for decimals.
• NFT movement lives in nft_transfers, not token_transfers.
• Entity IDs are strings (0.0.1234); sorting them lexically is wrong.

---

The eval harness

This is the part that matters, and it was built before the agent worked.

Each case in evals/cases.yaml is a hand-written question plus hand-written reference SQL. At eval time the reference query runs against the same warehouse the agent queries, and its result is the expected answer. Freezing literal values would make the suite stale the moment the warehouse is re-ingested; what's frozen is the intent.

mode	used for	behaviour
scalar	"how many", "what is the total"	first cell, optional numeric tolerance
ordered	"top 5", "most recent"	full result, row order significant
set	"one row per type"	full result, order ignored
assumptions	questions the schema cannot answer	passes only if the agent says so instead of inventing a column

Only the first N columns are compared, where N is the reference's column count, so an agent returning a helpful extra column isn't punished.

Cases are proven to discriminate, not assumed to

A case that both a correct and a careless query pass is not a test. tests/test_traps.py builds a synthetic warehouse where each trap has distinguishable answers, runs the real reference SQL from cases.yaml, then runs the plausible-but-wrong query and asserts the harness scores it as a failure.

case	the wrong answer it catches
hbar_volume	Summing every transfer leg — double-entry makes the naive answer 0
hbar_volume_excluding_fees	Counting fee legs as user-to-user value
token_volume_scaled_by_decimals	Assuming a fixed decimal scale instead of joining tokens
nft_vs_fungible_tokens	Looking for NFT movement in token_transfers
count_successful_transactions	Forgetting to filter result = 'SUCCESS'
unanswerable_hbar_price	Inventing a price column — there is no price data here

That test found a flaw in its own first fixture: amounts of a few hundred tinybars put the correct answer below hbar_volume's 0.01 HBAR tolerance, so it passed for the wrong reason. Which is also why the fee-leg case is asked in integer tinybars with zero tolerance — on real data the fee legs are 3,038 tinybars against 69.5 trillion, so in HBAR any float tolerance swallows the difference and the case would pass either way.

Ablations — measuring what each ingredient is worth

Claiming "the domain notes are what make this work" is worth nothing without a run that removes them.

chainquery eval                     # full
chainquery eval --no-examples       # drop the few-shot worked examples
chainquery eval --no-domain-notes   # drop the Hedera domain notes
chainquery eval --no-repair         # disable the self-repair loop

The variant is recorded in the written report, so a number in runs/ can always be traced back to the configuration that produced it. The schema survives every ablation — without it nothing works, so it is not a variable.

Regression gate

chainquery eval --compare runs/latest.json --fail-on-regression

Reports accuracy and cost deltas plus per-case regressions (passed before, fails now) and fixes. A newly added failing case is not a regression, and comparing two different variants is flagged as an ablation comparison rather than failing the build — an ablation scoring worse is the expected result.

Adding a golden case

1. Add an entry to cases.yaml with an id, a question phrased the way a user would ask it, a compare mode, and hand-written reference_sql (omit it for compare: assumptions).
2. Check the reference actually runs and returns something meaningful: chainquery sql "<your reference sql>".
3. If the case exists to catch a specific mistake, add a discrimination test to tests/test_traps.py proving the careless query fails it.
4. pytest — test_golden_cases_load_and_are_well_formed validates the shape, and duplicate ids are rejected.

---

Project status

Feature-complete for its scope, and the scope is deliberate: a working text-to-SQL agent over public Hedera data, with the measurement infrastructure to prove whether it's right.

Done and tested — 131 tests, ruff clean, CI green:

• Mirror-node client with cursor pagination and backoff
• DuckDB warehouse, contiguous and day-stratified ingest
• Parser-level read-only SQL guard
• Agent with structured output, prompt caching, self-repair
• Eval harness: 31 golden cases, 4 compare modes, proven-discriminating traps
• Ablation flags, self-describing reports, regression gate
• MCP server, 4 read-only tools
• Query timeout, row caps, scan-size approval gate

Needs an ANTHROPIC_API_KEY to exercise — the harness is built and tested, but these numbers are not published here because they have not been run, and this repo does not print numbers it hasn't measured:

• The baseline accuracy figure and the ablation curve (chainquery eval)
• The CI eval job, which skips cleanly when the secret is absent rather than failing the build

Ideas deliberately left out of scope: a cost/quality model router, tracing dashboard, and a web UI. All three are interesting; none are needed to demonstrate the thing this project is about.

---

Two bugs worth reading the diff for

Both were found against live mainnet data, and both were silent — no exception, just fewer rows than requested.

1. float64 nanosecond collapse. Hedera consensus timestamps are nanosecond-precision, but at epoch scale a float64 only resolves ~480ns. Parsed with float(), distinct transactions collided on the primary key: 100 distinct timestamps became 76 distinct floats, and 4,000 requested transactions landed as 87 rows. Fixed by parsing to exact integer nanoseconds with string arithmetic.

2. httpx cursor wipe. Passing params={} makes httpx replace a URL's existing query string. The mirror node's pagination cursor carries limit and order, so those were silently stripped, the cursor stopped advancing, and pagination looped the same 25 rows forever. Fixed, plus a repeating-cursor guard.

Both have regression tests (tests/test_mirror.py).

---

Layout

src/chainquery/
  mirror/client.py       public mirror node REST client (retry + cursor pagination)
  warehouse/
    schema.sql           the warehouse, with the column comments the model reads
    ingest.py            mirror node payloads → DuckDB rows; day-stratified sampling
    catalog.py           stable schema context vs. volatile coverage profile
    db.py                connections, query timeout watchdog
  agent/
    prompt.py            system prompt assembly, cache breakpoint, ablations
    generate.py          Messages API call, structured output, token accounting
    guard.py             read-only SQL enforcement
    run.py               ask() pipeline: generate → guard → execute → repair
  evals/
    cases.yaml           the golden set (31 cases)
    score.py             exact-value comparison
    runner.py            harness, reporting, ablations, regression diff
  mcp_server.py          MCP tools over the same guard and warehouse
  cli.py
tests/                   guard, scoring, warehouse mapping, prompt-cache stability,
                         pipeline, mirror-node regressions, MCP, ablations,
                         trap discrimination
docs/DEMO.md             real captured terminal output

License

MIT — see LICENSE.