Trading & Charting

​

Who it’s for

​

The recipe bundle

​

How the pieces fit

                     ┌──►  Swaps by Pair    (trade tape, by pool)
trade fills  ────────┼──►  Swaps by Token   (trade tape, by token)
                     └──►  Pair OHLCV        (candles, by pool)

pool post-balances ──►  Pair Reserves       (live depth, by pool)
price updates      ──►  Token Prices        (USD/native marks, by token)

• The swap recipes all draw from the same normalized in-block trade events, direct pool fills (tokenSwaps) and aggregator-routed trades (aggregateTokenSwaps), so every fill lands exactly once. Key by pool for a pool’s tape, by token for a token’s tape.
• Pair OHLCV is a downstream aggregation of that ingest: the same trades land, then a candle surface buckets them per (pool, hour). “Price” per fill is the bought-leg in-block USD mark; volume is summed notional; trades is the count.
• Pair Reserves reads the pool’s absolute token0PostBalance / token1PostBalance from each swap, so current reserves are simply the latest observation per pair: live depth without reconstructing a running sum.
• Token Prices is the connective tissue for valuation: join any trade, balance, or position to the token’s mark, and use the latest-mark dictionary to value quiet positions with no fresh update.

​

Building a pool overview

SELECT  event_ts, side, source_kind,
        token0_address, token1_address,
        amount0, amount1, notional_usd, protocol
FROM    recipe_swaps_by_pair.fact_swaps_by_pair FINAL
WHERE   chain_id = 1
  AND   pool_address = lower('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')
ORDER BY event_ts DESC
LIMIT 50;

SELECT  bucket_start, open, high, low, close, volume, trades
FROM    recipe_pair_ohlcv.candles
WHERE   chain_id = 1
  AND   pool_address = lower('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')
ORDER BY bucket_start DESC
LIMIT 24;

SELECT  pair_address,
        argMax(reserve0, (block_number, log_index)) AS reserve0,
        argMax(reserve1, (block_number, log_index)) AS reserve1
FROM    recipe_pair_reserves.fact_pair_reserves FINAL
WHERE   chain_id = 1
  AND   pair_address = lower('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')
GROUP BY pair_address;

SELECT dictGetOrDefault(
         'recipe_token_price_history.latest_token_price_dict', 'usd_price',
         tuple(1, lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')), '0'
       ) AS usd_price;

​

Notes and considerations

• EVM + Solana. The trade, candle, reserve, and price recipes are chain-parametrized. On Solana, repeated logIndex within one instruction means the pool-fill vendor_event_id should be widened (e.g. with the token addresses/amounts) to keep rows distinct; the candle math is chain-agnostic.
• One trade, two pool addresses. A fill observed both as a direct pool fill (pairAddress) and as part of an aggregator route (aggregatorAddress) appears under both; they are distinct events by design. source_kind (pool_fill vs aggregator) tells them apart.
• USD is in-block only. Trades, candles, and notional are enriched from same-block tokenPriceUpdates. No cross-block carry-forward. Illiquid or brand-new tokens with no in-block mark leave price_usd / notional_usd NULL (and produce candle gaps for that hour). For valuing quiet positions, use the Token Prices latest-mark dictionary, which carries the last on-chain mark forward.
• OHLCV is fixed 1-hour and trade-reconstructed. Candles are built from on-chain swap fills, so they can differ slightly from a DEX’s own reported OHLC. For other intervals, re-bucket the trade ingest. Use candles for the fast accelerator and candles_exact (over fact_swaps FINAL) when post-reorg precision on high/low matters; volume and trades are sign-weighted and always exact.
• Reserves need an in-window swap. Current reserves come from swap post-balances, so a pool appears only if it had at least one swap with resolved post-balances in the ingested window. Quiet pools have unchanged reserves by definition. Widen the window to pick them up.
• Run ClickHouse in hybrid for live terminals. It backfills history then hands off to a seamless, reorg-safe realtime tail at the chain head. Postgres/MySQL are first-class for historical backfill; the trade recipes expand many rows per block, so live/reorg-safe ingestion is ClickHouse-only.
• Pair with other bundles. Add Swaps by Wallet for per-user trade history, or see Token Analytics and Portfolio Tracking for adjacent recipe bundles.

​

Get started