BlockDB Liquidity Pools Details & Fees | Ethereum & EVM Chains | Historical, EOD, Real-Time | ...

Dataset Overview
Canonical registry of all DEX liquidity pools, normalized into the BlockDB schema for deterministic cross-chain analysis.
Fees are modeled separately as versioned terms, so fee changes over time are tracked precisely and are joinable back to the pool.

Chains and Coverage
ETH, BSC, Base, Arbitrum, Unichain, Avalanche, Polygon, Celo, Linea, Optimism (others on request).
Full history from chain genesis; reorg-aware real-time ingestion and updates.
Coverage includes:
• Uniswap V2, V3, V4
• Balancer V2, PancakeSwap, Solidly, Maverick, Aerodrome, and others

Schema
List the columns exactly as delivered.

liquidity_pools (base registry)
• pool_uid BYTEA NOT NULL - stable pool identifier (derived from address or pool_id)
• exchange_id INTEGER NOT NULL - exchange identifier (e.g. 1 Uniswap, 2 Sushiswap)
• type_id INTEGER NOT NULL - pool type FK (constant-product, concentrated, stable/weighted, etc.)
• block_number BIGINT NOT NULL - first block where the pool was recognized
• block_time TIMESTAMPTZ NOT NULL - UTC timestamp when the block was mined
• tx_index INTEGER NOT NULL - transaction index for that event
• log_index INTEGER NOT NULL - log index for that event
• factory BYTEA NOT NULL - DEX factory / pool manager address
• tokens BYTEA[] NOT NULL - array of 20-byte token addresses (order matches protocol convention)
• contract_address BYTEA NULL - 20-byte pool address (v2/v3-style)
• pool_id BYTEA NULL - 32-byte pool id (v4-style / manager-based)
• pairnum NUMERIC(6) NULL - optional pair ordinal/descriptor
• asset_managers BYTEA[] NULL - per-token managers (e.g. Balancer)
• amp NUMERIC(6) NULL - amplification for stable/weighted math
• weights NUMERIC(6,5)[] NULL - per-token weights in 0..1 (5 dp)
• tick_spacing SMALLINT NULL - grid size for concentrated liquidity
• _tracing_id BYTEA NOT NULL - deterministic row-level hash
• _genesis_tracing_ids BYTEA[] NOT NULL - hash(es) of original sources (genesis of the derivation path)
• _parent_tracing_ids BYTEA[] NOT NULL - hash(es) of immediate parent rows in the derivation graph
• _created_at TIMESTAMPTZ NOT NULL - record creation timestamp
• _updated_at TIMESTAMPTZ NOT NULL - record last update timestamp

liquidity_pool_fee_terms (versioned, non-overlapping)
• pool_uid BYTEA NOT NULL - FK → blockdb0203_liquidity_pools_v1(pool_uid)
• exchange_id INTEGER NOT NULL - exchange identifier (e.g. 1 Uniswap, 2 Sushiswap)
• type_id INTEGER NOT NULL - pool type FK (constant-product, concentrated, stable/weighted, etc.)
• block_number BIGINT NOT NULL - block where this fee term starts
• block_time TIMESTAMPTZ NOT NULL - UTC timestamp when the block was mined
• tx_index INTEGER NOT NULL - transaction index for that event
• log_index INTEGER NOT NULL - log index for that event
• total_fee NUMERIC(18,18) NULL - total fee fraction (e.g. 0.003 = 0.30%)
• user_fee NUMERIC(18,18) NULL - user/LP fee fraction (same units as total_fee)
• protocol_fee NUMERIC(18,18) NULL - protocol fee fraction (same units as total_fee)
• extra_fee NUMERIC(18,18) NULL - extra fee fraction (burn/staking/etc.; same units as total_fee)
• _tracing_id BYTEA NOT NULL - deterministic row-level hash
• _genesis_tracing_ids BYTEA[] NOT NULL - hash(es) of original sources (genesis of the derivation path)
• _parent_tracing_ids BYTEA[] NOT NULL - hash(es) of immediate parent rows in the derivation graph
• _created_at TIMESTAMPTZ NOT NULL - record creation timestamp
• _updated_at TIMESTAMPTZ NOT NULL - record last update timestamp

Checks
• At least one identifier present (contract_address or pool_id) and lengths enforced (20B/32B).

Notes
• Fee terms are non-overlapping; each record defines a valid block-range.
• Use liquidity_pool_fee_terms for historical fee reconstruction or to obtain the active fee at a given block.

Lineage
Every row has a verifiable path back to the originating raw events via the lineage triple and tracing graph:
• _tracing_id - this row’s identity
• _parent_tracing_ids - immediate sources
• _genesis_tracing_ids - original on-chain sources
This supports audits and exact reprocessing to source transactions/logs/function calls.

Common Use Cases
• Building the complete DEX pool registry for routing and analytics
• Filtering pools by fee, type, or token pair
• Integrating with reserves, price, and swap datasets for liquidity intelligence
• MEV routing, arbitrage path optimization, and chain-wide pool analytics
• Constructing pool-level AI or quantitative features

Quality
• Each row includes a cryptographic hash linking back to raw on-chain events for auditability.
• Tick-level resolution for precision.
• Reorg-aware ingestion ensuring data integrity.
• Complete backfills to chain genesis for consistency.