Overview
- Dataset ID:
0301 - Tables:
blockdb0301_liquidity_pools_reserves_v1— Time-series of pool reserve/state snapshots; one row per pool per lineage event.blockdb0301_liquidity_pools_reserves_details_v1— Locator-level liquidity/tick/bin detail keyed to each snapshot.
Snapshot Table Columns (blockdb0301_liquidity_pools_reserves_v1)
| Column | Type | Description |
|---|---|---|
id | BIGINT | Surrogate primary key for the table, used for joins and change tracking. |
pool_uid | BIGINT | Foreign key referencing blockdb0203_liquidity_pools_v1.uid for the pool in question. |
exchange_id | BIGINT | Identifier linking to the DigitalExchange enumeration for centralized venues. |
block_number | BIGINT | Canonical block height within the originating chain. |
block_time | TIMESTAMPTZ | UTC timestamp for the block that produced this record. |
tx_index | INTEGER | Zero-based transaction index within the block, used for deterministic ordering. |
log_index | INTEGER | Zero-based log index within the transaction, uniquely identifying the emitted event. |
reserves | NUMERIC(78,0)[] | Array of current reserves (per token) for even-style pools, raw units. |
current_tick | INTEGER | Current tick for concentrated-liquidity pools (e.g., Uniswap v3). |
current_sqrt_price | NUMERIC(49,0) | Q64.96 sqrt price as integer; decode via (value / 2^96)^2 for mid-price (adjust for decimals). |
current_bin | INTEGER | Current bin id for bin-based AMMs (e.g., TraderJoe v2*). |
_tracing_id | BYTEA | BlockDB lineage identifier that links this record to lineage APIs. |
_genesis_tracing_ids | BYTEA[] | Tracing identifiers for the raw artifacts (blocks, logs, proofs) that seeded this record. |
_parent_tracing_ids | BYTEA[] | Tracing identifiers for upstream derived records referenced during computation. |
_created_at | TIMESTAMPTZ | Record creation timestamp. |
_updated_at | TIMESTAMPTZ | Record last update timestamp. |
Constraints: Each snapshot enforcespool_uid, block_number, tx_index, log_indexuniqueness so only one lineage event is stored per pool per block/log triple. A payload check ensures at least one ofreserves,current_tick, orcurrent_binis present. Hot-path indexes cover(pool_uid, block_number DESC, tx_index DESC, log_index DESC)plus block-time ordering for time-series pulls.
Details Table Columns (blockdb0301_liquidity_pools_reserves_details_v1)
| Column | Type | Description |
|---|---|---|
snapshot_id | BIGINT | Foreign key to blockdb0301_liquidity_pools_reserves_v1.id; cascades on delete. |
pool_uid | BIGINT | Denormalized pool identifier to accelerate pool-scoped scans. |
tick | INTEGER | Single-tick locator for concentrated liquidity pools (e.g., Uniswap v3). |
lower_tick | INTEGER | Lower bound for range-style liquidity slices. |
upper_tick | INTEGER | Upper bound for range-style liquidity slices. |
bin_id | INTEGER | Bin identifier for bin-based AMMs (e.g., TraderJoe). |
liquidity | NUMERIC(38,0) | Engine-native packed liquidity metric (e.g., Uniswap v3 L). |
amount0 | NUMERIC(78,0) | Token0 amount associated with the locator (decimals-adjusted). |
amount1 | NUMERIC(78,0) | Token1 amount associated with the locator (decimals-adjusted). |
Constraints: Each detail row must include a locator (tick,bin_id, or bothlower_tick/upper_tick) and a payload (liquidity,amount0, oramount1). Uniqueness constraints enforce one tick/bin/range per snapshot, and indexes exist onsnapshot_id,pool_uid,tick,bin_id, and(lower_tick, upper_tick)to support fast fan-out queries.
Use Cases
- Backtesting and AI modeling with verifiable pool states
- Market-structure analytics and liquidity regime detection
- Monitoring dashboards for protocol health, volatility, and liquidity shifts
- Price, TVL, and slippage calculations across pools and chains
- Routing and arbitrage research (depth, fee tiers, fragmentation)