Roundhouse

The data layer

SQL over the index

Send read-only SQL over the whole index, within the limits the database enforces.

Read as markdown

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.

bash
curl -sL -X POST "https://www.roundhouse.studio/api/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
json
{ "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.

LimitValue
StatementsOne. SELECT or WITH only
Semicolons, commentsRejected
Rows returned300, hard cap
Statement timeout8 seconds
Readable tablesThe public data-layer tables only
Rate limit30/min anonymous, 240/min with a trial key, Query Units for org keys

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#

text
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

sql
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

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

Who actually pays a given service

sql
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

sql
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

sql
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

sql
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

sql
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

sql
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

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

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

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

Agent prompt
Answer this from the Roundhouse index using POST /api/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#