Pool TVL

Overview

The pool_tvl table provides time-series tracking of token balances in liquidity pools, enabling TVL (Total Value Locked) calculations and liquidity analysis. Each row represents a single token's balance within a pool at a specific time.

Schema

Field	Type	Mode	Description
id	BYTES	REQUIRED	Client-generated deterministic ID using MD5 hash of (liquidity_pool_id, token_contract_id, datetime). 128-bit hash stored as BYTES
liquidity_pool_id	STRING	REQUIRED	Foreign key to liquidity_pool table
token_contract_id	STRING	REQUIRED	Foreign key to token_contract table
block_number	INTEGER	NULLABLE	Block number when this snapshot was taken
datetime	TIMESTAMP	REQUIRED	UTC timestamp for this TVL measurement
raw_amount	BIGNUMERIC	REQUIRED	Token balance in the pool (in smallest unit, e.g., wei) - uses BIGNUMERIC to handle all token decimals without overflow
value_usd	FLOAT	NULLABLE	USD value of the token balance at measurement time
ingested_at	TIMESTAMP	REQUIRED	Timestamp when this record was inserted into BigQuery

Primary Key

• id: A deterministic ID generated using MD5 hash of liquidity_pool_id, token_contract_id, and datetime

Foreign Keys

• liquidity_pool_id: References the liquidity_pool table
• token_contract_id: References the token_contract table

Usage Examples

-- Calculate total TVL by DEX over time (daily)
SELECT 
  DATE(pt.datetime) AS date,
  di.name AS dex_name,
  SUM(pt.value_usd) AS total_tvl_usd
FROM `bitpulse.dex.pool_tvl` pt
JOIN `bitpulse.dex.liquidity_pool` lp ON pt.liquidity_pool_id = lp.id
JOIN `bitpulse.dex.dex_instance` di ON lp.dex_instance_id = di.id
WHERE pt.datetime >= '2023-01-01'
GROUP BY date, di.name
ORDER BY date DESC, total_tvl_usd DESC;

-- Track TVL for a specific pool over time
SELECT 
  pt.datetime,
  tc.symbol,
  pt.raw_amount / POWER(10, tc.decimals) AS token_amount,
  pt.value_usd
FROM `bitpulse.dex.pool_tvl` pt
JOIN `bitpulse.dex.token_contract` tc ON pt.token_contract_id = tc.id
WHERE pt.liquidity_pool_id = 123
ORDER BY pt.datetime DESC, tc.symbol;

Notes

• TVL snapshots are typically taken at regular intervals (e.g., hourly).
• The raw_amount field stores token amounts in their smallest unit (e.g., wei for ETH) and should be divided by 10^decimals to get the human-readable amount.
• The value_usd field may be NULL if price data was not available at the time of ingestion.
• To calculate the total TVL of a pool, sum the value_usd of all tokens in the pool for a given timestamp.