# The data model

The tables behind every surface, and what each column actually means.

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

---

Four tables carry almost everything. Learn these and the [SQL endpoint](https://www.roundhouse.studio/docs/data/sql) becomes
obvious.

## `settlements`

One row per payment. The spine.

| Column | Meaning |
| --- | --- |
| `chain_id` | CAIP-2 chain, e.g. `eip155:8453` |
| `tx_hash`, `log_index` | The on-chain location. Unique with `chain_id` |
| `block_time` | When it settled. **Filter on this** — it is the indexed column |
| `payer`, `payee` | Lowercased addresses. `payee` is the merchant, not an intermediary |
| `amount_raw` | Atomic token units |
| `amount_usd` | USD value, or `NULL` when the token had no rate — never guessed |
| `token_address` | Which token moved |
| `verified_x402` | Three-valued confidence. See below |
| `via_facilitator` | Who relayed it: an operator id, a raw address, `self`, or `unattributed` |
| `resource_url` | What was paid for, where known |
| `routed_via` | Fee-proxy route. `{}` for a direct settlement |
| `gross_amount_raw` | Pre-fee amount when a proxy was involved |

Three things that will bite you if you assume otherwise:

- **Addresses are lowercase.** Compare with `lower('0x…')`.
- **`amount_usd` is nullable.** A count and a sum are therefore different populations, and every
  volume figure is a **floor**.
- **`verified_x402` is not a boolean.** `true` = proven by an EIP-3009 marker, `null` = unexamined,
  `false` = examined and **disproven**. Summing all three and calling it verified volume is the most
  common mistake made with this table.

`settlements` has tens of millions of rows. Never aggregate it unbounded — filter on `block_time`, or
read a rollup.

## `entities`

One row per wallet the index has seen, with whatever has been resolved about it.

| Column | Meaning |
| --- | --- |
| `wallet` | Lowercased address. The primary key |
| `display_name` | Resolved name: agent card, ENS, or a fallback |
| `agent_id` | ERC-8004 id, when linked |
| `first_seen`, `last_seen` | Activity bounds |

An entity is not an account anyone created. It is an address observed transacting, annotated after the
fact. The same wallet can be a payer and a payee — `/agents/<w>` and `/merchant/<w>` are two views of
one row.

## `agents`

ERC-8004 identities, read from the on-chain registry.

| Column | Meaning |
| --- | --- |
| `agent_id`, `chain_id` | The registry key. **Composite** — the same id can exist on two chains |
| `wallet` | The declared payment wallet, from `setAgentWallet` |
| `agent_uri` | Where the card lives |
| `display_name`, `description`, `icon` | Read from the card |
| `score` | ReputationRegistry aggregate. `NULL` means no feedback, not bad |

`agent_feedback` holds the individual feedback rows behind `score`.

## `external_resources`

The x402 service catalog, crawled and enriched.

| Column | Meaning |
| --- | --- |
| `source`, `resource` | Where the listing came from, and the URL you pay. Unique together |
| `service_name`, `description` | As published |
| `pay_to` | The join key into `settlements` |
| `price_usdc`, `network`, `asset`, `scheme` | The claimed terms |
| `is_live`, `http_status`, `enriched_at` | Our external liveness probe |
| `geo_*` | Nearest serving location of the endpoint's IP — a CDN edge for most hosts |
| `last_indexed_at` | Last seen upstream. A full crawl cycle is about forty minutes |

Everything before `is_live` is a **claim**. Everything after it is an **observation**. That line is the
most useful thing in the table.

> [!NOTE]
> Stale rows are not pruned — the table holds more entries than are currently
> listed upstream. `last_indexed_at` much older than a crawl cycle means the
> listing is probably gone from the source.

## Supporting tables

| Table | Holds |
| --- | --- |
| `chains` | Supported chains, finality depth, RPC configuration |
| `chain_tokens` | Per-chain token metadata and USD rates |
| `facilitators` | Known relayers, per chain, with observed counts |
| `fee_proxies` | Contracts that forward payments, so their legs are not double-counted |
| `settlement_corrections` | An audit trail of in-place corrections |
| `settlement_sync_state` | Capture cursor, last error, row counts per chain |

## The rollups

Pre-computed aggregates, because the raw table cannot be aggregated on demand.

| View | Grain | Note |
| --- | --- | --- |
| `mv_entity_rollups` | wallet | Lifetime inbound/outbound. **No `display_name`** — join `entities` |
| `mv_entity_daily` | wallet × day | Only days with activity — densify before charting |
| `mv_global_daily` | day | The site-wide series behind [`/stats`](https://www.roundhouse.studio/stats) |
| `mv_global_stats` | one row | Site-wide totals |

Two properties to hold in mind:

- **They refresh on a schedule** and trail the chain by up to an hour. Read paths fold in newer
  settlements, so a page can be more current than the view behind it.
- **A rollup row exists only for days that saw activity.** A raw series therefore silently compresses
  quiet stretches, and a chart drawn from it reads as continuous activity. Fill the gaps.

## The aggregation rule

Worth knowing because it explains an absence you might otherwise report as a bug.

**Site-wide aggregates exist in exactly one place: [`/stats`](https://www.roundhouse.studio/stats).** Everywhere else — profiles,
feeds, the graph, every `/v0` endpoint — the only aggregate numbers are per-agent or per-merchant.

There is no volume leaderboard, no live total ticker, and no global figure on the home page, by
design. A data layer that ranks its subjects starts shaping them.

## Next steps

- [SQL over the index](https://www.roundhouse.studio/docs/data/sql) — query it
- [Coverage and confidence](https://www.roundhouse.studio/docs/data/coverage-and-confidence) — how much of it is real
- [Data surfaces](https://www.roundhouse.studio/docs/data/surfaces) — where each table is rendered

---

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