The problem Arkiv SQL solves

Arkiv stores data onchain as entities with attributes, owners and expiry. Reading that data back at scale is hard. arkiv_query returns at most 200 results per page. It cannot JOIN entities, GROUP BY an attribute or ORDER BY an attribute. To answer "how many support tickets per agent, sorted by age", an app has to page through everything, pull it into memory and aggregate it by hand. The public RPC also rate limits you while you do that.

Arkiv SQL Indexer turns all of Arkiv into data you can query with plain SQL in milliseconds.

What people can use it for

• Analytics and dashboards. Run JOIN, GROUP BY, ORDER BY, window functions and aggregates over every live entity on Tiramisu. The showcase page has chart cards, and each one is a single SQL query that arkiv_query cannot express.
• A ready made data API for apps and AI agents. One docker compose up gives you a read only HTTP API. It serves versioned Parquet snapshots plus a JSON delta, so any tool that reads Parquet can use it: DuckDB, Python, pandas, Polars or an LLM agent with a SQL tool. You need no API key and no SDK, and you never page through results.
• Exploring data in the browser. The SQL playground runs DuckDB WASM in your browser tab. It has schema aware autocomplete, query history and CSV export. Each row has Payload and Verify actions that check the row against the chain.
• Live data, not a stale export. The browser tails new blocks from the public RPC every 4 seconds and merges the delta every 2 seconds. Creates, patches, expiry extensions, ownership transfers and deletes appear almost at once.

How it makes existing tasks easier

• Questions that used to need custom paging code and in-memory aggregation become one SQL query.
• The indexer replays native operation logs from block zero over the public RPC and needs no credentials. It saves a checkpoint and resumes after a restart.
• Immutable Parquet files support HTTP Range requests and ETag caching. Clients download only what changed, and the load moves off the Arkiv RPC.

How it makes them safer

• Expiry is enforced everywhere. A row disappears as soon as its expiry block passes. Every public view filters on expires_at > live_head, so expired data never shows up in results.
• Creator scoping as a trust boundary. Set INDEX_EXPRESSION to a $creator filter and the index contains only entities written by wallets you trust. Third party entities cannot get into your results. The indexer refuses to start if you reuse a data store that was built with a different expression.
• Read only by design. The public API cannot write. The indexer never holds a private key. The optional demo writer runs separately and never sees the RPC API key.
• Verifiable. Any row can be checked against the chain from the browser, so users do not have to trust the indexer.

Challenges I ran into

1. One query used up the entire anonymous budget

My first test was a broad arkiv_query ($expiresAt > u64(0), limit 5). That single call used up the whole anonymous budget of 10,000 units. After that every query returned HTTP 429. The response body was also a plain object ({"error":"ANON_COST_LIMITED", ...}), not a JSON RPC error envelope, so my client crashed while trying to read error.code.

How I got over it: I changed the whole design so it no longer depends on arkiv_query. The indexer now rebuilds the live entity set by replaying native operation logs with eth_getLogs. That works from block zero with no API key and no query cost. The RPC client accepts both error shapes and respects Retry-After. On a 429, only attribute fetching pauses. Everything else keeps running.

2. Keeping up with live data would have used up the paid quota in days

I measured Tiramisu and saw about 4,000 entity events per hour, touching about 2,800 distinct keys. An EntityPatched log carries no attribute values, so every patch needs a query. At that rate, keyed live sync would have used up the monthly API quota in three to four days.

How I got over it: Live sync is now keyless by default. Creates, patches, extensions, ownership transfers and deletes are applied directly from public logs. A patched row is marked attrs_pending. A background filler then fetches attribute values newest first, in batches of 50 keys, within the anonymous budget. The API key is optional and used only if you opt in.

3. An undocumented limit on query length

To batch key lookups I OR'd $key terms together. 50 keys (about 4,100 characters) worked. 100 keys failed with -32004 query is too long. This limit is not documented, and a failed query still costs units.

How I got over it: I tested to find working bounds, then capped batches at 50 keys. The config rejects any FILL_CHUNK above 50.

4. Log queries over large ranges fail

Above 20,000 results, eth_getLogs returns an error. The suggested retry range appears only inside the error message text (retry with the range A-B).

How I got over it: fetchLogsAdaptive parses that range out of the message, retries exactly that range and continues from the end of it. If the message has no range, it halves the window.

5. Expiry emits no event

When an entity expires, nothing is logged. A mirror driven only by logs would keep expired rows forever. For a project whose main promise is "expired data never shows up", that is a serious problem.

How I got over it: I enforce expiry at three layers. Every indexer batch removes rows where expires_at <= batch end and records tombstones. Snapshots leave out expired rows. Browser SQL views filter on expires_at > live_head, using eth_blockNumber read directly from the chain. A row therefore disappears in the browser as soon as its expiry block passes, even before the next snapshot.

6. A race between the filler and live updates

Suppose an entity is patched, transferred or extended while the filler is fetching its attributes. The older query result could overwrite the newer state.

How I got over it: Every fill is pinned to a block. Before the indexer writes a fill, it checks whether the key changed after that block. If so, it discards the fill and retries.

I documented every one of these with reproduction steps and suggested fixes in arkiv/friction.md.

Link to a twitter post explaining your project (or post with the video)

https://x.com/_anshudhar_/status/2111944515716358592