LAST RESORT — arbitrary READ-ONLY SQL against the Optimism transfers database. The
Bitquery MCP specialized optimism_* tools are the PRIORITY; use this ONLY when none of them
can answer (e.g. an uncovered table). No query optimizer here — naive SQL full-scans
huge tables and JOINs time out.
Runs read-only ClickHouse SQL. SQL reference: https://clickhouse.com/docs/reference/home
FAST-QUERY RULES: filter on the indexed key tables — `optimism_api.transfers_sender` (by
sender / outgoing), `optimism_api.transfers_receiver` (by receiver / incoming),
`optimism_api.transfers_tx` (by tx hash); NEVER JOIN big tables — use `WHERE col IN (SELECT …)`
(use `GLOBAL IN` when the subquery is referenced inside another subquery, otherwise its
result is not available where it is used). A subquery that itself reads a transfers table (`IN (SELECT Transfer_Sender FROM …transfers_… WHERE …)`) is re-run for every shard and rescans that table — run it as its OWN query first, then pass the result back as a literal list (`IN (unhex('a'), unhex('b'))`). To filter transfers by a specific token, read `transfers_currency` (keyed by the token contract + `Block_Time`); `transfers_sender` / `transfers_receiver` are keyed by address, so filtering those by a token contract scans the table.
Addresses are RAW BYTES `FixedString(20)` in `Transfer_Sender` / `Transfer_Receiver`
(there are NO plain string address columns) →
`Transfer_Sender = unhex(substring(lower('0x…'),3))`, output `concat('0x',lower(hex(col)))`.
Tx hash is `Transaction_Hash` `FixedString(32)` (same unhex/hex pattern).
A SET of hashes or addresses must have the column's exact type:
`Transaction_Hash IN (SELECT toFixedString(unhex(h), 32) FROM …)`, `toFixedString(…, 20)` for
an address. A plain `unhex(…)` set, or a JOIN on a String key, silently misses every value
ending in `00`; `=` and a literal list `IN (unhex('a'), unhex('b'))` are safe.
Currency symbol + decimals are INLINE columns (no dictionaries):
amount = `toFloat64(Transfer_Amount) / pow(10, Transfer_Currency_Decimals)`;
symbol = `Transfer_Currency_Symbol`; token contract = `Transfer_Currency_SmartContract` — itself RAW BYTES in a `String` column (not text), so filter it `= unhex('<40 hex, no 0x>')` and read it back `concat('0x',lower(hex(…)))`; a `'0x…'` text literal matches nothing.
Always add `AND Transfer_Success = 1`. `Transfer_Type`: 'token' = a token transfer,
'transaction' = a plain native-coin send, 'call' = native coin moved by a call. A
transaction's OWN value transfer is stored either as 'transaction' or as a 'call' with
`Call_Index = 0`; only a 'call' with `Call_Index != 0` is an INTERNAL transfer (made by
contract code deeper inside the transaction). For top-level transfers filter
`(Transfer_Type IN ('token','transaction') OR (Transfer_Type = 'call' AND Call_Index = 0))` -
`Transfer_Type IN ('token','transaction')` alone drops a large share of ordinary sends. Time =
`Block_Time`. Date-time values come back as ISO-8601 UTC, e.g. 2026-09-21T15:04:18Z (toString() of a date-time gives 2026-09-21 15:04:18). Other `optimism_api.*` tables (calls, transactions, balances) are reachable with
an explicit db prefix. Counterparty labels are NOT in this database — use
labels_for_addresses. Read-only; always add a LIMIT of at most 10,000 — a larger answer fails with an error, so aggregate (GROUP BY, count) instead. 64-bit integers come back as JSON numbers, exact only up to 2^53: return a hash such as cityHash64(...) or any other 64-bit id as toString(x).
Exact table and column names first: chain_list_tables, chain_describe_table and chain_search_columns with database=optimism_api.
ConnectorOAuth