The data layer
The data model
The tables behind every surface, and what each column actually means.
Four tables carry almost everything. Learn these and the SQL endpoint 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_usdis nullable. A count and a sum are therefore different populations, and every volume figure is a floor.verified_x402is 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 |
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. 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 — query it
- Coverage and confidence — how much of it is real
- Data surfaces — where each table is rendered