SQL over the index

POST /v0/sql runs one SELECT over the public dataset and returns rows as JSON. Use it for the questions the fixed endpoints do not answer. It is a paid endpoint, two ways in: $0.005 USDC per query over x402 — send the query, get a 402, pay it and resend with X-PAYMENT; one settlement buys one execution — or an organisation API key from the dashboard, metered in Query Units against that organisation's balance on any plan, free included. What a paid plan buys is the longer 60-second tier, not the route. A trial identity key belongs to no organisation, so it has no balance to draw on and takes the pay-per-query path.

If you send both a key and an X-PAYMENT header, the payment wins and no Query Units are charged. Attaching a signed authorization is an explicit choice — the credit lane never issues a challenge for a client library to answer automatically — so it is honoured rather than ignored.

curl -sL -X POST "https://api.roundhouseai.io/v0/sql" \
  -H "authorization: Bearer $ROUNDHOUSE_KEY" \
  -H 'content-type: application/json' \
  -d '{"sql":"select wallet, inbound_usd, inbound_count from mv_entity_rollups order by inbound_usd desc limit 10"}' | jq

{ "rows": [...], "row_count": 10, "duration_ms": 205, "authenticated": true, "tier": "trial" }

The contract#

Enforced in the database, not in the request handler — so these are hard limits, not conventions.

What a query costs#

A query is priced by the time it takes: one Query Unit per started five seconds, with a floor of one. A query that answers in under five seconds costs 1 QU, which is what every query cost before this was metered.

tier buys the ceiling. Every organisation can run SQL at standard and pay for it from its balance; extended needs a paid plan.

The tier's maximum is held when the call starts and the unused part released as soon as the duration is known, so usage.units_charged on the response is what the query actually cost. Two consequences worth knowing: an organisation needs the tier maximum available to start a query at all, and a query that runs to the ceiling it bought is charged the whole tier — including one that hits the timeout, because it used every second it paid for.

A query that fails is charged for the time it used, not for the tier: a syntax error costs the 1 QU floor. A query that never ran — our runner unavailable, or any 5xx — costs nothing.

Knowing before you spend#

{ "sql": "select ...", "explain_only": true }

returns the quote and charges nothing: the tier's ceiling and price bounds, plus the planner's own planner_cost and plan_rows and a suggested_tier. The planner figures are advisory — arbitrary units, and not calibrated against this schema — so they are there to tell you whether a query is in the right shape, never to price it. Settlement is always from measured time.

max_qu refuses the call before anything is held if the tier could cost more than that. get_account lists every tier with whether your plan allows it.

The query runs as a role that can read only the tables below. Platform tables (users, listings, receipts) and Postgres internals (pg_catalog, information_schema, session functions) are unreachable — not filtered, unreachable.

Readable tables#

chains                chain_tokens          settlements
entities              agents                agent_feedback
facilitators          fee_proxies           external_resources
settlement_corrections                      settlement_sync_state
mv_entity_rollups     mv_entity_daily
mv_global_stats       mv_global_daily

Column reference: the data model.

Write for the limits#

Five habits that turn a timeout into a result.

1. Filter on block_time. It is the indexed column. A settlements query without a time bound is a sequential scan over tens of millions of rows and will hit the 8-second timeout.
2. Aggregate, do not paginate. With a 300-row cap you cannot walk the table. Ask for the answer.
3. Read a rollup when one exists. mv_entity_rollups already knows a wallet's lifetime totals.
4. Lowercase your addresses. where payer = lower('0xABC…').
5. Filter verified_x402 when you mean proven. Without it you are counting unexamined rows.

Query shapes to copy#

Busiest merchants this week

select s.payee, e.display_name, count(*) as settlements, sum(s.amount_usd) as usd
from settlements s
left join entities e on e.wallet = s.payee
where s.block_time > now() - interval '7 days'
group by 1, 2
order by settlements desc
limit 25

One wallet's daily trend

select day, settlements, usd
from mv_entity_daily
where wallet = lower('0xTheirWallet')
order by day desc
limit 60

Who actually pays a given service

select e.service_name, e.resource, count(*) as calls, count(distinct s.payer) as payers
from external_resources e
join settlements s on s.payee = e.pay_to
where e.resource ilike '%example.com%'
  and s.block_time > now() - interval '30 days'
group by 1, 2
order by calls desc

Listed price versus what is actually paid

select e.service_name,
       e.price_usdc                          as listed,
       round(avg(s.amount_usd)::numeric, 4)  as avg_paid,
       count(*)                              as settlements
from external_resources e
join settlements s on s.payee = e.pay_to
where s.block_time > now() - interval '30 days'
group by 1, 2
having count(*) >= 3
order by settlements desc
limit 40

Repeat-customer rate for one merchant

select count(*) as payers,
       count(*) filter (where n > 1) as repeat_payers,
       round(100.0 * count(*) filter (where n > 1) / nullif(count(*), 0), 1) as repeat_pct
from (
  select payer, count(*) as n
  from settlements
  where payee = lower('0xTheirWallet')
    and block_time > now() - interval '90 days'
  group by payer
) t

Price distribution across the market

select width_bucket(amount_usd, 0, 1, 20) as bucket,
       min(amount_usd) as low, max(amount_usd) as high, count(*) as settlements
from settlements
where block_time > now() - interval '7 days'
  and amount_usd is not null and amount_usd <= 1
group by 1
order by 1

Facilitator share of relayed volume

select via_facilitator, count(*) as settlements, count(distinct payer) as payers
from settlements
where block_time > now() - interval '7 days'
  and via_facilitator not in ('self', 'unattributed')
group by 1
order by settlements desc

Proven versus unexamined, for one counterparty

select count(*) as all_rows,
       count(*) filter (where verified_x402) as proven,
       count(*) filter (where verified_x402 is null) as unexamined,
       count(*) filter (where verified_x402 = false) as disproven
from settlements
where payee = lower('0xTheirWallet')
  and block_time > now() - interval '90 days'

Agents with feedback and real payments

select a.agent_id, a.display_name, a.score, r.inbound_usd, r.inbound_count
from agents a
join mv_entity_rollups r on r.wallet = a.wallet
where a.score is not null and r.inbound_count > 0
order by a.score desc
limit 25

New merchants this month — nobody had paid them before, someone has now.

select payee, min(block_time) as first_paid, count(*) as settlements
from settlements
where block_time > now() - interval '30 days'
group by payee
having min(block_time) > now() - interval '30 days'
order by settlements desc
limit 25

Errors#

400 invalid_query    failed the sandbox checks (detail explains why)
400 query_failed     the query ran but errored — timeout, bad column
401 invalid_api_key  key not found, revoked, or expired
402 insufficient_qu  organization out of Query Units
429 rate_limited     too many requests this minute

query_failed with no detail is almost always the timeout. Add a block_time filter.

Natural language instead#

The AI playground generates SQL from a question and runs it through the same sandbox. It is metered in Query Units, and it is the fastest way to find the query shape you actually wanted — copy the SQL out and run it against the endpoint from then on.

Hand it to an agent#

Answer this from the Roundhouse index using POST /v0/sql:

<your question>

Constraints the runner enforces — write for them rather than discovering them:
- One statement, SELECT or WITH only. No semicolons, no comments.
- 300 rows maximum, 8-second timeout.
- Always filter settlements on block_time; it is the indexed column.
- Addresses are stored lowercase.
- verified_x402 is three-valued: true (proven), null (unexamined), false
  (disproven). Filter on true when the answer needs to mean "proven".
- mv_entity_rollups has no display_name — join entities for names.

Show me the SQL before running it, then the result, then what the result does
not tell me.

Next steps#

• The data model — every column
• Coverage and confidence — before you quote a number
• API reference — keys, tiers, Query Units