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 awal@2.0.3 status

If the wallet is not authenticated, refer to the

authenticate-wallet

skill.

Executing a Query

npx awal@2.0.3 x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run

-X

POST

-d

'{"sql": ""}'

--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 awal@2.0.3 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 awal@2.0.3 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 awal@2.0.3 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 awal@2.0.3 status to check, see authenticate-wallet skill) Wallet must have sufficient USDC balance ( npx awal@2.0.3 balance to check) Each query costs $0.10 (100000 USDC atomic units) Error Handling "Not authenticated" - Run awal auth login 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

query-onchain-data

安装