Schema

Column Name	Data Type
chain_id	long
txn_hash	string
block_number	long
global_counter	bigDecimal
contract_address	string
variable_name	string
variable_slot	string
entry_slot	string
key_1_value	string
key_1_type	string
key_2_value	string
key_2_type	string
key_3_value	string
key_3_type	string
value_before	string
value_after	string
value_type	string

Coverage

Ethereum (1), Base (8453), and Arbitrum (42161)

Source

The table is built within the Mapping storage writes canvas.

Usage notes

This table indexes the solidity mapping write updates up to a depth of 3 keys for nested mappings.

For verified contracts, mapping storage variables are retrieved from the contract source code. For unverified contracts, mapping variables are inferred using our Storage Layout Extractor tool.

Newly verified contracts will only have decoded mapping values using the source code after the source code verification date. Before, the data will be available using the inferred storage layout.

The column global_counter represents a custom execution clock. It is monotonically increasing and can be used to order events.

Sample queries

1. Number of unique addresses that ever had a USDC balance

SELECT DISTINCTCOUNT(key_1_value) AS unique_addresses
  FROM @sim.mapping_storage_writes
 WHERE variable_name = 'balanceAndBlacklistStates'
   AND contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

2. Number of unique addresses with active USDC balance

WITH latest_writes AS (
  SELECT key_1_value,
         MAX(CAST(global_counter AS double)) AS max_global_counter
    FROM @sim.mapping_storage_writes
   WHERE variable_name = 'balanceAndBlacklistStates'
     AND contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
   GROUP BY 1
)

SELECT DISTINCTCOUNT(sw.key_1_value) AS unique_active_addresses
  FROM @sim.mapping_storage_writes sw
  JOIN latest_writes lw ON CAST(sw.global_counter AS double) = lw.max_global_counter
   AND sw.key_1_value = lw.key_1_value
 WHERE true
   AND sw.variable_name = 'balanceAndBlacklistStates'
   AND sw.contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

3. Transactions updating mappings containing a given address in the keys

SELECT  txn_hash,
        block_number,
        contract_address,
        variable_name,
        variable_slot,
        value_after,
        value_before,
        key_1_value,
        key_2_value,
        key_3_value
FROM @sim.mapping_storage_writes 
WHERE key_1_value = '0xd8da6bf26964af9d7eed9e03e53415d37aa96045'
   OR key_2_value = '0xd8da6bf26964af9d7eed9e03e53415d37aa96045'
   OR key_3_value = '0xd8da6bf26964af9d7eed9e03e53415d37aa96045'