Database Schema

This document provides a detailed overview of the ClickHouse database schema used by the indexer.

Overview

The schema is designed to store the raw and processed data from the Solana blockchain in a way that is optimized for both real-time and historical analysis. It uses a combination of MergeTree and ReplacingMergeTree tables, along with materialized views, to achieve this.

Tables

pool_state_updates

• Engine: ReplacingMergeTree(write_version)
• Description: This table stores an append-only log of all pool state changes for CLMM (Concentrated Liquidity Market Maker) pools from Raydium and Orca. The ReplacingMergeTree engine ensures that duplicate updates for the same write_version are automatically removed.
• Key Columns:
  • ts: The timestamp of the update.
  • slot: The Solana slot number in which the update occurred.
  • write_version: The Geyser write version for idempotence.
  • program: The program that generated the update (e.g., raydium_clmm, orca_whirlpool).
  • pool: The public key of the pool.
  • sqrt_price_x64: The Q64.64 fixed-point representation of the square root of the price.
  • liquidity: The total liquidity in the pool.
  • tick_current: The current tick of the pool.

tick_updates

• Engine: ReplacingMergeTree(write_version)
• Description: This table stores an append-only log of all tick array changes for CLMM pools.
• Key Columns:
  • ts: The timestamp of the update.
  • slot: The Solana slot number.
  • program: The program that generated the update.
  • pool: The public key of the pool.
  • tick_index: The index of the tick.
  • liquidity_net: The net liquidity of the tick.
  • liquidity_gross: The gross liquidity of the tick.

pump_swaps

• Engine: MergeTree
• Description: This table stores an append-only log of all swaps from the Pump.fun and PumpSwap programs.
• Key Columns:
  • ts: The timestamp of the swap.
  • slot: The Solana slot number.
  • sig: The transaction signature.
  • program: The program that generated the swap.
  • pool: The public key of the pool.
  • side: The side of the swap (buy or sell).
  • amount_in: The amount of the input token.
  • amount_out: The amount of the output token.
  • price_adj: The price adjusted for token decimals.

pool_state_latest

• Engine: ReplacingMergeTree(ts)
• Description: This is a materialized view that stores the latest state of each pool. It is updated automatically whenever a new row is inserted into pool_state_updates.
• Key Columns:
  • program: The program of the pool.
  • pool: The public key of the pool.
  • sqrt_price_x64: The latest square root price.
  • liquidity: The latest liquidity.
  • tick_current: The latest current tick.
  • ts: The timestamp of the latest update.

pool_price_1s

• Engine: ReplacingMergeTree(bucket)
• Description: This is a materialized view that calculates 1-second candles (open, high, low, close) for each pool. It is updated automatically from the pool_state_updates table.
• Key Columns:
  • bucket: The 1-second time bucket.
  • program: The program of the pool.
  • pool: The public key of the pool.
  • open, high, low, close: The OHLC prices for the 1-second interval.

pool_price_1m and pool_price_5m

• Engine: ReplacingMergeTree(bucket)
• Description: These are materialized views that calculate 1-minute and 5-minute candles, respectively. They are aggregated from the pool_price_1s table.
• Key Columns:
  • bucket: The 1-minute or 5-minute time bucket.
  • program: The program of the pool.
  • pool: The public key of the pool.
  • open, high, low, close: The OHLC prices for the interval.