Database Changes: Pool State

Completeness digest, end-of-block pool state and pool token balances, computed from the same parent modules the live pools package runs. Scope: replacing the pool-state crons' RPC reads (and the TVL crons' reserve source), plus the completeness digest. No stores, no RPC. Design: "Pool-State Substreams Package: Design" in Notion.

Parents are imported, not rebuilt

The manifest imports the deployed uniswap-database-changes-pools-<chain> spkg by URL and takes pools:uniswap_v{2,3,4}:{store_pools,map_events} from it unchanged. Module hashes don't depend on the import alias, so the parents hash exactly like the live ones and the provider serves them from cache. Rebuilding the protocol packages from a newer commit would change their wasm, and therefore their hashes, and the provider would rebuild every store_pools from the protocol start block.

• scripts/build.sh <chain> <version> resolves the pools version the prod sink streams (scripts/resolve-pools-version.py, from infrastructure/config/sinks/prod), reads the db_out initial block and the v4 PoolManager from that spkg, packs, and runs scripts/check-parent-hashes.sh. The check fails the build if any parent hash differs from the deployed spkg.
• Override with POOLS_VERSION=v0.0.x, or POOLS_SPKG=<url or path> to test against an unreleased pools build.
• A pools version bump on a chain means rebuilding pool-state against it. The parent URL and PoolManager are recorded in the db_out / keys_out module docs (substreams info).
• src/ decodes the imported map_events output with today's proto crate. Proto compatibility with the imported pools version (no renumbered fields in proto/evm/uniswap/v{2,3,4}) is checked at review time, not by the build.

db_out is hand-written

Autogen's Rust generator needs log identity (tx_hash, caller, ordinal, log_index, transaction_index, block_index) on every message. These tables are per block, per slot or per pool, so src/ is written by hand. Every table is flat apart from struct columns, which are proto3 JSON in DatabaseChanges and become nested events.proto messages (BigQuery STRUCT / ARRAY<STRUCT>).

The sink maps a table to its events.proto message by name (PascalCase of the table), so the table names are the event contract: block_digest_event_manifest and block_digest_pool_changeset (the #952 block_digest_pool_state and block_digest_pool_token_balance messages are deprecated and never emitted), and every emitted column must exist in that message.

Modules

• db_out (always on): block_digest_event_manifest and block_digest_pool_changeset, one row per pool touched in the block. One emission per block, including empty blocks (the manifest's all row). Param: the v4 PoolManager address.
• digest_out (the completeness tick's stream): the manifest only, so the tick pays for the digest's few KB per block rather than every table.
• keys_out (on demand): event_key, one row per decoded event. Never sunk, so it has no event schema.

Every row carries block_num, block_hash, timestamp, detail_level, index, global_sequence and ordering_key. global_sequence is a row id, (block << 64) | row index zero-padded to 39 digits, unique within the block because the sink's dedupe key has no table in it. ordering_key is the pool (the pool id for v4), or the table name for the manifest. The sink adds chain_id from the instance config, as it does for the pools package. Decoded numbers are decimal strings, a delta balance signed; raw storage words stay hex.

Behaviour notes

• Stateless. Reverted calls and failed transactions are skipped everywhere. Storage is collapsed per (address, slot): the highest-ordinal write wins and the lowest-ordinal old value is kept.
• The manifest covers every map_events list, 44 today. A unit test fails the build if a list is added to a protocol Events message without a digest type. The *_associated_evm_transaction types are transactions: their bitmaps hold the tx begin_ordinal and the tx index. Bitmaps use roaring portable serialization.
• db_out's manifest also has a pool_changeset row every block (0 included): its event_count is the number of block_digest_pool_changeset rows in the block, so a consumer knows when it holds all of them (the pool rows and the manifest have different ordering keys, so they can arrive out of order). It is not in all, which counts decoded events only, and digest_out does not emit it.
• block_digest_pool_changeset is one row per touched pool (any event, a state slot write, or a Transfer in or out). Decoded state columns (sqrt_price_x96, tick, liquidity, reserve0/1, lp_fee, protocol_fee) are set only when observed this block; consumers keep their last value per field. Struct columns: slots (per slot: the winning storage write's key and raw word, compared with eth_getStorageAt in Level B, or source = event on base blocks, plus exact), balances (sorted by token), liquidity_changes (v3 Mint/Burn and v4 ModifyLiquidity tick ranges with signed liquidity, in ordinal order; applied to a per-pool curve they give exact active liquidity and ReservesLens core reserves on base and extended blocks), created (the creation event, in its block), initialized (the starting price: v3 Initialize, v4 Initialize; v2 has none). Activity: swap / add / remove liquidity counts, tx_count, token0/token1 volume in and out of the pool, and swappers, the sorted distinct tx.from of swap transactions (raw addresses rather than an HLL sketch: sketch formats don't merge across BigQuery and ClickHouse, and a pool's per-block set is tiny).
• Also on the changeset row: buyers0 / buyers1 (distinct tx.from of swaps that took token0 / token1 out of the pool; per pool, not netted per transaction), v4 last_swap_fee and fees0 / fees1 (from the Swap event's fee, LP plus protocol: ceil(amount_in * fee / 1e6) per swap, so base blocks too), and v2 total_supply (pair slot 0, extended blocks) and total_supply_delta (LP Transfers from / to the zero address, every block). On base blocks protocol_fee comes from SetFeeProtocol (packed like slot0.feeProtocol) and ProtocolFeeUpdated, as a protocol_fee slot with source = event.
• balances is absolute when the token's balance slot decodes (preimage or probe) and a signed Transfer delta otherwise, and always a delta on base blocks. A running sum of deltas from a known balance ties out to balanceOf unless the token changes balances without a Transfer (rebasing, reflection, interest-bearing tokens such as aTokens).
• Extended blocks decode state from the winning storage write; base blocks from Sync/Swap (v4 price and tick from store_pools), with exact = false only for Swap-derived v3/v4 liquidity. v4 pools are found from this block's v4 events plus any (poolId, 6) keccak preimage at a written slot, which catches updateDynamicLPFee and protocol-fee changes that emit no pool event.
• balances entries match a pool's balance write by preimage decode, then a probe of slots 0..=50 (Solidity and Vyper key orders) run at most once per token per block. A match counts only if the slot's net change over the block equals the pool's net Transfer delta; otherwise the entry is the delta. Base blocks emit Transfer deltas.
• v4 reserves (the lens coreAmount0/1) are not emitted: the consumer keeps the curve from liquidity_changes (or ClickHouse v4_pool_transactions) and runs the lens math.
• Moved out of this package: layout_evidence (token balances work) and the Hook Reputation Service inputs hook_settlement / hook_code_change; see the design doc's appendices.

Run

cd substreams-packages
./build-database-changes-spkg.sh mainnet v0.0.1 pool-state
substreams run -e mainnet.eth.streamingfast.io:443 \
  local/uniswap-labs-database-changes-pool-state-mainnet.spkg db_out -s <block> -t +1

A run that reports a few thousand more "Processed" than "Received" blocks is the parents catching up from the nearest store snapshot, not a rebuild (a rebuild processes from the protocol start block, millions of blocks).

Use the endpoint the chain's live pools sink uses (substreams-sink-bigquery/src/config/chains.ts), since the parent cache is per provider.