ERC-8004 BigQuery Workshop Cheat Sheet

gistfile1.txt

-- =============================================================================
-- ERC-8004 BigQuery Workshop Cheat Sheet
-- =============================================================================
-- This file contains the 4 reference BigQuery SQL queries used in the workshop
-- slides. Copy and paste these directly into the Google Cloud BigQuery Console.
--
-- Network: Ethereum Mainnet logs (routed via CREATE2 singletons starting with 0x8004)
-- Data Source: `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.logs`
-- =============================================================================


-- =============================================================================
-- QUERY 1: Adoption Curve (Registrations per Day)
-- Slide: Demo 1 / 4 - Adoption curve: registrations per day
-- =============================================================================
-- Measures the growth rate of new agent registrations over time since launch.
-- Uses partition pruning on `block_timestamp` to optimize query speed and cost.

SELECT
  DATE(block_timestamp) AS day,
  COUNT(*)              AS new_agents
FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.logs`
WHERE address = '0x8004a169fb4a3325136eb29fa0ceb6d2e539a432'  -- IdentityRegistry address
  AND block_timestamp >= TIMESTAMP '2026-01-28'  -- Launch date partition pruning
  AND topics[SAFE_OFFSET(0)] = 
    '0xca52e62c367d81bb2e328eb795f7c7ba24afb478408a26c0e201d155c449bc4a'  -- Registered event signature
GROUP BY day
ORDER BY day;


-- =============================================================================
-- QUERY 2: Decode the Registered Event (Raw ABI Decoding in SQL)
-- Slide: Demo 2 / 4 - Decode the Registered event — pure SQL
-- =============================================================================
-- Extracts agent identity and metadata pointers from raw, unparsed event logs.

SELECT
  -- 1. Extract agentId (indexed tokenId) from the second topic element
  --    - Input (topics[1]): '0x0000000000000000000000000000000000000000000000000000000000000016'
  --    - Output: 22 (integer)
  SAFE_CAST(topics[SAFE_OFFSET(1)] AS INT64)       AS agent_id,

  -- 2. Extract and unpad the owner wallet address
  --    - Input (topics[2]): '0x000000000000000000000000742d35cc6634c0532925a3b844bc454e4438f44e'
  --    - Action: SUBSTR(..., 27) strips the leading 24 zeros; CONCAT prepends '0x'
  --    - Output: '0x742d35cc6634c0532925a3b844bc454e4438f44e'
  CONCAT('0x', SUBSTR(topics[SAFE_OFFSET(2)], 27)) AS owner,

  -- 3. Extract and decode the dynamic agent_uri string from hex bytes
  --    - Input (data): '0x00...002e...697066733a2f2f516d596f75724167656e7443617264' (offset + length + string hex)
  --    - Action: Slices the hex payload at character 131 for 'length' characters, then:
  --              FROM_HEX() converts to binary bytes: [0x69, 0x70, 0x66, 0x73, ...]
  --              SAFE_CONVERT_BYTES_TO_STRING() decodes UTF-8 text from bytes
  --    - Output: 'ipfs://QmYourAgentCard'
  SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(SUBSTR(
    data,
    131,
    2 * SAFE_CAST(CONCAT('0x', SUBSTR(data, 67, 64)) AS INT64)
  )))                                              AS agent_uri
FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.logs`
WHERE address = '0x8004a169fb4a3325136eb29fa0ceb6d2e539a432' 
  AND topics[SAFE_OFFSET(0)] = '0xca52e62c367d81bb2e328eb795f7c7ba24afb478408a26c0e201d155c449bc4a'
  AND block_timestamp >= TIMESTAMP '2026-01-28'
ORDER BY block_timestamp DESC 
LIMIT 50;


-- =============================================================================
-- QUERY 3: The Reputation Leaderboard (Aggregating Client Scores)
-- Slide: Demo 3 / 4 - The reputation leaderboard
-- =============================================================================
-- Aggregates ratings for each agent. Requires a threshold of unique client
-- reviews to prevent Sybil feedback manipulation (agent owners rating themselves).

WITH feedback AS (
  SELECT
    -- 1. Extract agentId (indexed uint256)
    SAFE_CAST(topics[SAFE_OFFSET(1)] AS INT64)              AS agent_id,

    -- 2. Extract and unpad client wallet address (skips leading padding zeros)
    CONCAT('0x', SUBSTR(topics[SAFE_OFFSET(2)], 27))        AS client,

    -- 3. Extract raw rating integer from Slot 1 of data payload (chars 67 to 130)
    --    - Input: '0x00000000000000000000000000000000000000000000000000000000000003d9'
    --    - Output: 985 (integer)
    SAFE_CAST(CONCAT('0x', SUBSTR(data,  67, 64)) AS INT64) AS raw_value,

    -- 4. Extract decimal precision scaler from Slot 2 of data payload (chars 131 to 194)
    --    - Input: '0x0000000000000000000000000000000000000000000000000000000000000001'
    --    - Output: 1 (integer)
    SAFE_CAST(CONCAT('0x', SUBSTR(data, 131, 64)) AS INT64) AS value_decimals
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.logs`
  WHERE address = '0x8004baa17c55a88189ae136b182e5fda19de9b63'                 -- ReputationRegistry address
    AND topics[SAFE_OFFSET(0)] = '0x6a4a61743519c9d648a14e6493f47dbe3ff1aa29e7785c96c8326a205e58febc'    -- NewFeedback event
    AND block_timestamp >= TIMESTAMP '2026-01-28'
    AND SUBSTR(data, 67, 1) != 'f'               -- Skip negative ratings
)
SELECT 
  agent_id, 
  COUNT(*) AS feedback_count,
  COUNT(DISTINCT client)                             AS unique_clients, -- Sybil protection barrier
  
  -- Calculate true floating-point score: raw_value / 10^decimals
  --   - Input raw_value = 985, decimals = 1
  --   - Math: 985 / 10^1 = 98.50
  --   - Output: 98.50
  ROUND(AVG(raw_value / POW(10, value_decimals)), 2) AS avg_score
FROM feedback 
GROUP BY agent_id
HAVING unique_clients >= 3                       -- Sybil barrier: requires reviews from at least 3 distinct wallets
ORDER BY avg_score DESC, unique_clients DESC 
LIMIT 20;


-- =============================================================================
-- QUERY 4: Trustworthy & Payable (JOIN + Base64 Decoding)
-- Slide: Demo 4 / 4 - Trustworthy AND payable: the x402 join
-- =============================================================================
-- Joins Identity and Reputation datasets and extracts the 'x402Support' property
-- directly from base64-encoded on-chain payloads without using off-chain helpers.

WITH agents AS (
  SELECT
    SAFE_CAST(topics[SAFE_OFFSET(1)] AS INT64)       AS agent_id,
    CONCAT('0x', SUBSTR(topics[SAFE_OFFSET(2)], 27)) AS owner,
    SAFE_CONVERT_BYTES_TO_STRING(FROM_HEX(SUBSTR(
      data,
      131,
      2 * SAFE_CAST(CONCAT('0x', SUBSTR(data, 67, 64)) AS INT64)
    )))                                              AS agent_uri
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.logs`
  WHERE address = '0x8004a169fb4a3325136eb29fa0ceb6d2e539a432' 
    AND topics[SAFE_OFFSET(0)] = '0xca52e62c367d81bb2e328eb795f7c7ba24afb478408a26c0e201d155c449bc4a'
    AND block_timestamp >= TIMESTAMP '2026-01-28'
),
scores AS (
  SELECT
    SAFE_CAST(topics[SAFE_OFFSET(1)] AS INT64) AS agent_id,
    COUNT(DISTINCT CONCAT('0x', SUBSTR(topics[SAFE_OFFSET(2)], 27))) AS unique_clients,
    ROUND(AVG(
      SAFE_CAST(CONCAT('0x', SUBSTR(data,  67, 64)) AS INT64) / 
      POW(10, SAFE_CAST(CONCAT('0x53', SUBSTR(data, 131, 64)) AS INT64))
    ), 2) AS avg_score
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.logs`
  WHERE address = '0x8004baa17c55a88189ae136b182e5fda19de9b63'
    AND topics[SAFE_OFFSET(0)] = '0x6a4a61743519c9d648a14e6493f47dbe3ff1aa29e7785c96c8326a205e58febc'
    AND block_timestamp >= TIMESTAMP '2026-01-28'
    AND SUBSTR(data, 67, 1) != 'f'
  GROUP BY 1
)
SELECT 
  a.agent_id, 
  a.agent_uri, 
  s.avg_score, 
  s.unique_clients,
  STARTS_WITH(a.agent_uri, 'data:application/json;base64,') AS fully_onchain,
  
  -- Extract properties from on-chain Base64 JSON strings inside SQL
  --   - Input (a.agent_uri): 'data:application/json;base64,eyJuYW1lIjoicGF5YWJsZSIsIng0MDJTdXBwb3J0Ijp0cnVlfQ=='
  --   - Action: 
  --       1. SUBSTR strips the prefix metadata string 'data:application/json;base64,'
  --       2. SAFE.FROM_BASE64 decodes the string to bytes: [123, 34, 110, 97, 109, ...]
  --       3. SAFE_CONVERT_BYTES_TO_STRING converts bytes to JSON text: '{"name":"payable","x402Support":true}'
  --       4. JSON_VALUE extracts the 'x402Support' property value from the JSON string
  --   - Output: 'true'
  IF(
    STARTS_WITH(a.agent_uri, 'data:application/json;base64,'),
    JSON_VALUE(SAFE_CONVERT_BYTES_TO_STRING(SAFE.FROM_BASE64(
      SUBSTR(a.agent_uri, LENGTH('data:application/json;base64,') + 1)
    )), '$.x402Support'),
    NULL
  ) AS x402_support
FROM agents a 
JOIN scores s USING (agent_id)
ORDER BY s.avg_score DESC;