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": "<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 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