# SQL over the index

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

Section: The data layer
Source: https://www.roundhouse.studio/docs/data/sql

---

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

| Limit | Value |
| --- | --- |
| Statements | One. `SELECT` or `WITH` only |
| Semicolons, comments | Rejected |
| Rows returned | 300, hard cap |
| Statement timeout | 8 seconds |
| Readable tables | The public data-layer tables only |
| Rate limit | 30/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](https://www.roundhouse.studio/docs/data/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](https://www.roundhouse.studio/dashboard/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

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

- [The data model](https://www.roundhouse.studio/docs/data/data-model) — every column
- [Coverage and confidence](https://www.roundhouse.studio/docs/data/coverage-and-confidence) — before you quote a number
- [API reference](https://www.roundhouse.studio/docs/api) — keys, tiers, Query Units

---

Every page in these docs is available as markdown at its own URL plus `.md`.
Full index: https://www.roundhouse.studio/docs.md