query-onchain-data

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.10.0 status

If the wallet is not authenticated, refer to the authenticate-wallet skill.

Executing a Query

npx awal@2.10.0 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:

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.

base.transactions

Complete transaction data.

base.blocks

Block-level metadata.

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.10.0 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.10.0 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.10.0 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)

Best Practices

1. Always filter on indexed fields (event_signature, address, block_timestamp) in base.events queries.
2. *Never use SELECT ** - specify only the columns you need.
3. Always include a LIMIT clause to bound result size.
4. Use event_signature instead of event_name for filtering - it is indexed and much faster.
5. Use time-bounded queries with block_timestamp to narrow the scan range.
6. 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...'.
7. 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.10.0 status to check, see authenticate-wallet skill)
• Wallet must have sufficient USDC balance (npx awal@2.10.0 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