Database tables

In the following section, we’ll outline what the database tables consist of, detailing the key components and data structures that power our Solana transaction indexer.

Solana Transaction Indexer Database Documentation

Overview

This document outlines the database tables that power our Solana transaction indexer, detailing the key components and data structures. The system consists of the following tables:

  1. solana_mints - New Solana tokens (excluding Pump.fun tokens)

  2. raydium_all_swaps - Raydium transactions

  3. pumpfun_token_creation - New Solana tokens on Pump.fun

  4. pumpfun_all_swaps - Pump.fun transactions

  5. pumpswap_all_swaps - PumpSwap transactions

  6. pfamm_migrations - PumpFun AMM migration transactions

  7. meteora_swaps - Meteora DLMM DEX swap transactions

  8. raydium_launchpad_swaps - Raydium Launchpad transactions

  9. raydium_launchpad_migrations - graduations from the Raydium launchpad.

Common Column Structure

Many tables share similar fields, particularly for transaction identification:

Time and Transaction Identifiers

Column Name
Data Type
Description

block_time

DateTime

Timestamp when the block containing the transaction was processed

block_date_utc / block_date

Date

Date of the block in UTC timezone (truncated to day)

slot

UInt32/UInt64

Solana blockchain slot number

tx_idx

UInt16/UInt32

Index of the transaction within the block

signature

String

Unique Solana transaction signature

Wallet Information

Column Name
Data Type
Description

signing_wallet / user

String

The wallet that signed the transaction

fee_payer

String

The wallet that paid for the transaction fees

from_wallet

String

Original wallet that initiated a transaction (when different from signing wallet)

Gas and Execution Information

Column Name
Data Type
Description

provided_gas_fee

UInt64

Gas price set by the user for the transaction

provided_gas_limit

UInt64

Gas limit set by the user for the transaction

fee / fee_paid

UInt64

Actual transaction fee paid

consumed_gas

UInt64

Actual compute units consumed by the transaction

Common Table Configuration

Most tables share similar storage configuration:

  • Engine: MergeTree

  • Partition By: block_date_utc or block_date (daily partitions)

  • Order By: (slot, tx_idx) as primary key

  • TTL: Data is retained for 1 year

  • Settings:

    • index_granularity = 8192

    • min_bytes_for_wide_part = 10485760 (10MB)

Common Indexes

Most tables implement similar indexing strategies:

  • Bloom filter indexes on wallet addresses (signing_wallet, from_wallet, etc.)

  • MinMax index on block_time for efficient time-range queries

  • Bloom filter indexes on token addresses and pool identifiers

Table Details

PumpSwap All Swaps

The pumpswap_all_swaps table stores detailed information about PumpSwap transactions on the Solana blockchain.

Swap Direction and Tokens

Column Name
Data Type
Description

direction

LowCardinality(String)

Swap direction - "B" for buy or "S" for sell

base_token

String

Solana address of the base token in the swap

quote_token

String

Solana address of the quote token in the swap

Token Amounts

Column Name
Data Type
Description

base_token_amount

UInt64

Amount of base tokens involved in the swap

quote_token_amount

UInt64

Amount of quote tokens involved in the swap

quote_token_amount_limit

UInt64

Maximum amount of quote tokens the user is willing to swap

quote_token_amount_without_lp_fee

UInt64

Quote token amount before LP fees are applied

User Account Information

Column Name
Data Type
Description

user_base_token_account

String

Solana address of the user's base token account

user_quote_token_account

String

Solana address of the user's quote token account

user_base_token_reserves

UInt64

User's base token balance

user_quote_token_reserves

UInt64

User's quote token balance

Pool State Information

Column Name
Data Type
Description

pool_id

String

Pool ID

pool_base_token_reserves_before

UInt64

Pool's base token reserves before the swap

pool_quote_token_reserves_before

UInt64

Pool's quote token reserves before the swap

pool_base_token_reserves_after

UInt64

Pool's base token reserves after the swap

pool_quote_token_reserves_after

UInt64

Pool's quote token reserves after the swap

Fee Information

Column Name
Data Type
Description

lp_fee_basis_points

UInt16

LP fee in basis points (1 basis point = 0.01%, 1/10000)

lp_fee

UInt64

LP fee amount in token units

protocol_fee_basis_points

UInt16

Protocol fee in basis points

protocol_fee

UInt64

Protocol fee amount in token units

Important Notes

  • The direction field uses "B" for buys and "S" for sells

  • In complex transactions, the signing wallet may differ from the fee payer

  • Data is extracted through the PumpSwapAnySwapDetector which identifies PumpSwap instructions

  • Instructions are parsed for both top-level and inner (nested) instructions

PumpFun AMM Migrations

The pfamm_migrations table stores data about token migrations within the PumpFun AMM protocol.

Column Descriptions

Column Name
Data Type
Description

user

String

Base58-encoded address of the user who initiated the migration

mint

String

Base58-encoded address of the token mint being migrated

mint_amount

UInt64

Amount of tokens being migrated (in smallest denomination)

sol_amount

UInt64

Amount of SOL involved in the migration (in lamports)

pool_migration_fee

UInt64

Fee charged by the protocol for the migration (in lamports)

bonding_curve

String

Base58-encoded address of the bonding curve used in the migration

timestamp

UInt32

Unix timestamp of when the migration occurred

pool

String

Base58-encoded address of the destination pool for the migration

Data Population

This table is populated by the PumpFunAmmMigrationDetector which:

  • Scans Solana transactions for PumpFun program interactions

  • Identifies migration instructions by their instruction discriminator (9beae792ec9ea21e)

  • Looks for corresponding migration log events (e445a52e51cb9a1d)

  • Extracts structured data about the migration

Example Queries (PF AMM migrations)

  1. Find migrations by a specific user:

SELECT 
    block_time,
    mint,
    mint_amount,
    sol_amount,
    pool_migration_fee,
    pool
FROM pfamm_migrations
WHERE user = '5tGTGzHcqnEaFP7Vp3SxwAyTJJhLSKY9Yx5hUVEwuuQQ'
ORDER BY block_time DESC
LIMIT 100
  1. Calculate daily migration volume:

SELECT 
    block_date_utc,
    count() AS migration_count,
    sum(sol_amount) / 1000000000 AS total_sol_volume,
    sum(pool_migration_fee) / 1000000000 AS total_fees_sol
FROM pfamm_migrations
WHERE block_date_utc BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY block_date_utc
ORDER BY block_date_utc
  1. Find most migrated tokens:

SELECT 
    mint,
    count() AS migration_count,
    sum(mint_amount) AS total_tokens_migrated,
    sum(sol_amount) / 1000000000 AS total_sol_volume
FROM pfamm_migrations
WHERE block_date_utc >= dateAdd(day, -30, today())
GROUP BY mint
ORDER BY migration_count DESC
LIMIT 20

Meteora Swaps

The meteora_swaps table stores detailed information about swap transactions executed on the Meteora LB (Liquidity Book) DEX on Solana.

Column Descriptions

Column Name
Data Type
Description

base_coin

String

Solana address of the base token in the trading pair

quote_coin

String

Solana address of the quote token in the trading pair

base_coin_amount

UInt64

Amount of base tokens involved in the swap (raw value)

quote_coin_amount

UInt64

Amount of quote tokens involved in the swap (raw value)

start_bin_id

Int32

ID of the starting liquidity bin used in the swap

end_bin_id

Int32

ID of the ending liquidity bin used in the swap

fee

UInt64

Total swap fee collected (raw value)

protocol_fee

UInt64

Portion of the fee paid to the protocol (raw value)

fee_bps_low

UInt64

Lower bound of the fee rate in basis points

fee_bps_high

UInt64

Upper bound of the fee rate in basis points

host_fee

UInt64

Portion of the fee paid to the host/referrer (raw value)

lb_pair

String

Solana address of the liquidity book pair contract

swap_for_y

UInt8

Boolean flag (0/1) indicating swap direction

Working with Raw Values

Several monetary amounts in this table are stored as raw integer values without decimal conversion. To convert these to actual token amounts, you need to divide by 10^(token decimals). For example, if a token has 9 decimals, divide the raw amount by 10^9.

Example Queries (Meteora swaps)

  1. Daily Swap Volume by Token Pair:

SELECT 
    block_date,
    base_coin,
    quote_coin,
    COUNT(*) AS num_swaps,
    SUM(base_coin_amount) AS total_base_volume_raw,
    SUM(quote_coin_amount) AS total_quote_volume_raw
FROM default.meteora_swaps
WHERE block_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY block_date, base_coin, quote_coin
ORDER BY block_date, num_swaps DESC
  1. Fee Analysis:

SELECT 
    block_date,
    lb_pair,
    COUNT(*) AS num_swaps,
    SUM(fee) AS total_fees_raw,
    SUM(protocol_fee) AS total_protocol_fees_raw,
    SUM(host_fee) AS total_host_fees_raw
FROM default.meteora_swaps
WHERE block_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY block_date, lb_pair
ORDER BY total_fees_raw DESC
  1. Active Traders Analysis:

SELECT 
    signing_wallet,
    COUNT(*) AS num_swaps,
    COUNT(DISTINCT lb_pair) AS num_pools_used,
    COUNT(DISTINCT block_date) AS num_active_days
FROM default.meteora_swaps
WHERE block_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY signing_wallet
ORDER BY num_swaps DESC
LIMIT 100
  1. Bin Range Analysis:

SELECT 
    lb_pair,
    AVG(end_bin_id - start_bin_id) AS avg_bin_range,
    MAX(end_bin_id - start_bin_id) AS max_bin_range,
    MIN(end_bin_id - start_bin_id) AS min_bin_range
FROM default.meteora_swaps
WHERE block_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY lb_pair
ORDER BY avg_bin_range DESC

Underlying Protocol Context

Meteora is a concentrated liquidity DEX on Solana that uses a bin model similar to Uniswap v3, but with some key differences:

  • Liquidity is organized in discrete bins rather than continuous ranges

  • The start_bin_id and end_bin_id fields indicate which bins were used in the swap

  • The swap_for_y field indicates the direction of the swap (X→Y or Y→X)

CPMM migrations

Column name
Data type
Description

payer

String

Fee payer pubkey (base58) for the transaction.

base_mint

String

Mint pubkey (base58) of the base token.

quote_mint

String

Mint pubkey (base58) of the quote token.

platform_config

String

Platform configuration account pubkey (global launchpad settings).

cpswap_program

String

cpSwap program id (pubkey) deployed on-chain.

cpswap_pool

String

cpSwap pool account pubkey (pool instance).

cpswap_authority

String

Authority pubkey (often a PDA) controlling the cpSwap pool.

cpswap_lp_mint

String

LP token mint pubkey for the cpSwap pool.

cpswap_base_vault

String

Token account pubkey holding the pool's base token (cpSwap-specific).

cpswap_quote_vault

String

Token account pubkey holding the pool's quote token (cpSwap-specific).

cpswap_config

String

cpSwap-specific config/account pubkey for pool parameters.

cpswap_create_pool_fee

String

Account pubkey that receives pool-creation fees (fee receiver).

cpswap_observation

String

Observation/oracle account pubkey (e.g., TWAP or metrics) used by cpSwap.

lock_program

String

Locking/vesting program id (pubkey) used to lock tokens/LPs.

lock_authority

String

Authority pubkey (PDA or owner) of the lock program instance.

lock_lp_vault

String

Token account pubkey where LP tokens are locked under the lock program.

authority

String

General administrative/owner pubkey for the pool or migration.

pool_state

String

Account pubkey storing serialized pool state (account data) or a state identifier.

global_config

String

Global configuration account pubkey for the launchpad platform.

base_vault

String

General vault token account pubkey for base token (non-cpSwap/general pool).

quote_vault

String

General vault token account pubkey for quote token (non-cpSwap/general pool).

pool_lp_token

String

Token account pubkey that holds LP tokens for the liquidity pool (distinct from LP mint).

Raydium Launchpad Token Creation

creator

FixedString(48)

Creator account pubkey (base58) that initiated the token creation.

name

FixedString(20)

Token name (fixed-length string metadata).

symbol

FixedString(16)

Token symbol/ticker (fixed-length string metadata).

url

FixedString(256)

Metadata URL associated with the token (fixed-length string).

mint

FixedString(48)

Mint account pubkey (base58) for the created token.

bundle_size

UInt8

Number of items/operations included in the creation bundle.

gas_used

UInt64

Compute units consumed by the creation (implementation-specific runtime units).

amount_of_instructions

Int32

Number of instructions executed in the transaction.

amount_of_lookup_reads

Int32

Number of address lookup table reads used by the transaction.

amount_of_lookup_writes

Int32

Number of address lookup table writes used by the transaction.

bundle_structure

String

Serialized structure of the bundle (e.g., JSON describing bundled ops).

bundled_buys

UInt64

Total amount (smallest token units) purchased across bundled buy operations.

bundled_buys_count

UInt64

Count of individual buy operations included in the bundle.

dev_balance

UInt64

Developer/team balance related to the creation (value in smallest token units).

creation_ix_index

Int32

Index of the creation instruction within the transaction (instruction position).

pool_state

FixedString(48)

Pool state account pubkey (base58) associated with the token/pool.

base_vault

FixedString(48)

Token account pubkey holding base token reserves for the pool.

quote_vault

FixedString(48)

Token account pubkey holding quote token reserves for the pool.

raydium_program_index

UInt8

Identifier/index of the Raydium program variant used (implementation-specific).

direct_raydium_invocation

Bool

Whether Raydium program was invoked directly (true/false).

decimals

UInt8

Token decimal places (number of fractional digits).

cpmm_type

UInt8

CPMM pool type identifier/enum (implementation-specific).

supply

UInt64

Token supply at creation (amount in smallest token units).

bonding_curve_sell_amount

UInt64

Amount (smallest units) designated for bonding-curve sell operations.

bonding_curve_raise_amount

UInt64

Amount (smallest units) expected/raised via the bonding curve.

migrate_type

UInt8

Migration type identifier/enum (implementation-specific).

Performance Considerations

When working with these tables, consider the following best practices:

  1. Always filter by block_date or block_date_utc when possible to leverage partitioning

  2. For time-series analysis over longer periods, use block_time with the minmax index

  3. The tables are optimized for queries filtering by token addresses and wallet addresses

  4. When joining tables, try to join on signature as it uniquely identifies transactions

  5. Use appropriate data conversion for raw token amounts based on token decimals

Data Retention

Data in all tables is automatically deleted after 1 year based on the TTL setting.

Last updated