Query Onchain Data on Base
Use the CDP SQL API to query onchain data (events, transactions, blocks, transfers) on Base. Queries are executed via x402 and are charged per query.
Confirm wallet is initialized and authed
npx [email protected] status
If the wallet is not authenticated, refer to the authenticate-wallet skill.
Executing a Query
npx [email protected] x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "<YOUR_QUERY>"}' --json
IMPORTANT: Always single-quote the -d JSON string to prevent bash variable expansion.
Input Validation
Before constructing the command, validate inputs to prevent shell injection:
- SQL query: Always embed the query inside a single-quoted JSON string (
-d '{"sql": "..."}'). Never use double quotes for the outer -d wrapper, as this enables shell expansion of $ and backticks within the query.
- Addresses: Must be valid
0x hex addresses (^0x[0-9a-fA-F]{40}$). Reject any value containing shell metacharacters.
Do not pass unvalidated user input into the command.
CRITICAL: Indexed Fields
Queries against base.events MUST filter on indexed fields to avoid full table scans. The indexed fields are:
| Indexed Field |
Use For |
event_signature |
Filter by event type. Use this instead of event_name for performance. |
address |
Filter by contract address. |
block_timestamp |
Filter by time range. |
Always include at least one indexed field in your WHERE clause. Combining all three gives the best performance.
CoinbaseQL Syntax
CoinbaseQL is a SQL dialect based on ClickHouse. Supported features:
- Clauses: SELECT (DISTINCT), FROM, WHERE, GROUP BY, ORDER BY (ASC/DESC), LIMIT, WITH (CTEs), UNION (ALL/DISTINCT)
- Joins: INNER, LEFT, RIGHT, FULL with ON
- Operators:
=, !=, <>, <, >, <=, >=, +, -, *, /, %, AND, OR, NOT, BETWEEN, IN, IS NULL, LIKE
- Expressions: CASE/WHEN/THEN/ELSE, CAST (both
CAST() and :: syntax), subqueries, array/map indexing with [], dot notation
- Literals: Array
[...], Map {...}, Tuple (...)
- Functions: Standard SQL functions, lambda functions with
-> syntax
Available Tables
base.events
Decoded event logs from smart contract interactions. This is the primary table for most queries.
| Column |
Type |
Description |
| log_id |
String |
Unique log identifier |
| block_number |
UInt64 |
Block number |
| block_hash |
FixedString(66) |
Block hash |
| block_timestamp |
DateTime64(3, 'UTC') |
Block timestamp (INDEXED) |
| transaction_hash |
FixedString(66) |
Transaction hash |
| transaction_to |
FixedString(42) |
Transaction recipient |
| transaction_from |
FixedString(42) |
Transaction sender |
| log_index |
UInt32 |
Log index within block |
| address |
FixedString(42) |
Contract address (INDEXED) |
| topics |
Array(FixedString(66)) |
Event topics |
| event_name |
LowCardinality(String) |
Decoded event name |
| event_signature |
LowCardinality(String) |
Event signature (INDEXED - prefer over event_name) |
| parameters |
Map(String, Variant(Bool, Int256, String, UInt256)) |
Decoded event parameters |
| parameter_types |
Map(String, String) |
ABI types for parameters |
| action |
Enum8('removed' = -1, 'added' = 1) |
Added or removed (reorg) |
base.transactions
Complete transaction data.
| Column |
Type |
Description |
| block_number |
UInt64 |
Block number |
| block_hash |
String |
Block hash |
| transaction_hash |
String |
Transaction hash |
| transaction_index |
UInt64 |
Index in block |
| from_address |
String |
Sender address |
| to_address |
String |
Recipient address |
| value |
String |
Value transferred (wei) |
| gas |
UInt64 |
Gas limit |
| gas_price |
UInt64 |
Gas price |
| input |
String |
Input data |
| nonce |
UInt64 |
Sender nonce |
| type |
UInt64 |
Transaction type |
| max_fee_per_gas |
UInt64 |
EIP-1559 max fee |
| max_priority_fee_per_gas |
UInt64 |
EIP-1559 priority fee |
| chain_id |
UInt64 |
Chain ID |
| v |
String |
Signature v |
| r |
String |
Signature r |
| s |
String |
Signature s |
| is_system_tx |
Bool |
System transaction flag |
| max_fee_per_blob_gas |
String |
Blob gas fee |
| blob_versioned_hashes |
Array(String) |
Blob hashes |
| timestamp |
DateTime |
Block timestamp |
| action |
Int8 |
Added (1) or removed (-1) |
base.blocks
Block-level metadata.
| Column |
Type |
Description |
| block_number |
UInt64 |
Block number |
| block_hash |
String |
Block hash |
| parent_hash |
String |
Parent block hash |
| timestamp |
DateTime |
Block timestamp |
| miner |
String |
Block producer |
| nonce |
UInt64 |
Block nonce |
| sha3_uncles |
String |
Uncles hash |
| transactions_root |
String |
Transactions merkle root |
| state_root |
String |
State merkle root |
| receipts_root |
String |
Receipts merkle root |
| logs_bloom |
String |
Bloom filter |
| gas_limit |
UInt64 |
Block gas limit |
| gas_used |
UInt64 |
Gas used in block |
| base_fee_per_gas |
UInt64 |
Base fee per gas |
| total_difficulty |
String |
Total chain difficulty |
| size |
UInt64 |
Block size in bytes |
| extra_data |
String |
Extra data field |
| mix_hash |
String |
Mix hash |
| withdrawals_root |
String |
Withdrawals root |
| parent_beacon_block_root |
String |
Beacon chain parent root |
| blob_gas_used |
UInt64 |
Blob gas used |
| excess_blob_gas |
UInt64 |
Excess blob gas |
| transaction_count |
UInt64 |
Number of transactions |
| action |
Int8 |
Added (1) or removed (-1) |
Example Queries
Get recent USDC Transfer events with decoded parameters
SELECT
parameters['from'] AS sender,
parameters['to'] AS to,
parameters['value'] AS amount,
address AS token_address
FROM base.events
WHERE
event_signature = 'Transfer(address,address,uint256)'
AND address = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913'
AND block_timestamp >= now() - INTERVAL 7 DAY
LIMIT 10
Get transactions from a specific address
npx [email protected] x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT transaction_hash, to_address, value, gas, timestamp FROM base.transactions WHERE from_address = lower('\''0xYOUR_ADDRESS'\'') AND timestamp >= now() - INTERVAL 1 DAY LIMIT 10"}' --json
Count events by type for a contract in the last hour
npx [email protected] x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT event_signature, count(*) as cnt FROM base.events WHERE address = lower('\''0xCONTRACT_ADDRESS'\'') AND block_timestamp >= now() - INTERVAL 1 HOUR GROUP BY event_signature ORDER BY cnt DESC LIMIT 20"}' --json
Get latest block info
npx [email protected] x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT block_number, timestamp, transaction_count, gas_used FROM base.blocks ORDER BY block_number DESC LIMIT 1"}' --json
Common Contract Addresses (Base)
| Token |
Address |
| USDC |
0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913 |
| WETH |
0x4200000000000000000000000000000000000006 |
Best Practices
- Always filter on indexed fields (
event_signature, address, block_timestamp) in base.events queries.
- Never use
SELECT * - specify only the columns you need.
- Always include a
LIMIT clause to bound result size.
- Use
event_signature instead of event_name for filtering - it is indexed and much faster.
- Use time-bounded queries with
block_timestamp to narrow the scan range.
- Always wrap address values in
lower() - the database stores lowercase addresses but users may provide checksummed (mixed-case) addresses. Use address = lower('0xAbC...') not address = '0xAbC...'.
- Common event signatures:
Transfer(address,address,uint256), Approval(address,address,uint256), Swap(address,uint256,uint256,uint256,uint256,address).
Prerequisites
- Must be authenticated (
npx [email protected] status to check, see authenticate-wallet skill)
- Wallet must have sufficient USDC balance (
npx [email protected] balance to check)
- Each query costs $0.10 (100000 USDC atomic units)
Error Handling
- "Not authenticated" - Run
awal auth login <email> first, or see authenticate-wallet skill
- "Insufficient balance" - Fund wallet with USDC; see
fund skill
- Query timeout or error - Ensure you are filtering on indexed fields and using a LIMIT
