Solana Indexer Database Schema Reference

This reference describes the database schema used by our Solana transaction indexer. Below you'll find comprehensive documentation of all tables, their columns, and relationships. The indexer maintains the following core tables:

solana_mints - New Solana tokens (excluding Pump.fun tokens)
raydium_all_swaps - Raydium transactions
pumpfun_token_creation - New Solana tokens on Pump.fun
pumpfun_all_swaps - Pump.fun transactions
pumpswap_all_swaps - PumpSwap transactions
pfamm_migrations - PumpFun AMM migration transactions
meteora_swaps - Meteora DLMM DEX swap transactions
raydium_launchpad_swaps - Raydium Launchpad transactions
raydium_launchpad_migrations - graduations from the Raydium launchpad.

Shared Column Definitions

Several tables utilize a consistent set of columns, especially for identifying transactions:

Temporal and Transaction Fields

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-Related Fields

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)

Compute and Fee Details

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

Storage Engine Settings

The majority of tables are configured with identical storage parameters:

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)

Indexing Strategy

Tables employ a unified indexing approach for optimal query performance:

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

Individual Table Specifications

PumpSwap All Swaps

This table captures comprehensive swap data from the PumpSwap protocol on Solana.

Trade Direction and Token Addresses

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

Swap Quantities

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

Trader Account Details

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

Liquidity Pool Reserves

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 Structure

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

Key Implementation Details

The direction column encodes buy operations as "B" and sell operations as "S"
For multi-instruction transactions, the signing wallet and fee payer may be different accounts
Swap data is extracted via the PumpSwapAnySwapDetector, which scans for PumpSwap program instructions
The parser handles both top-level and nested (inner) instructions

PumpFun AMM Migrations

This table records token migration events occurring within the PumpFun AMM ecosystem.

Field Reference

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 Ingestion Process

The PumpFunAmmMigrationDetector populates this table by:

Monitoring Solana transactions for PumpFun program calls
Detecting migration instructions via their discriminator (9beae792ec9ea21e)
Matching associated migration log events (e445a52e51cb9a1d)
Parsing and storing structured migration metadata

Sample Queries — PumpFun AMM Migrations

Retrieve migrations for a particular wallet:

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

Aggregate daily migration statistics:

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

Identify tokens with the highest migration activity:

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

This table contains swap transaction records from the Meteora DLMM (Dynamic Liquidity Market Maker) exchange on Solana.

Field Reference

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

Handling Raw Token Amounts

Monetary values are stored as raw integers without decimal normalization. To obtain human-readable amounts, divide by 10^(token_decimals). For instance, a token with 9 decimals requires division by 10^9.

Sample Queries — Meteora Swaps

Daily trading volume grouped 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

Fee breakdown by pool:

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

Top traders by activity:

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

Liquidity bin utilization metrics:

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

Protocol Background

Meteora implements a concentrated liquidity model on Solana, conceptually similar to Uniswap v3 but with distinct characteristics:

Liquidity is allocated across discrete price bins instead of continuous tick ranges
The start_bin_id and end_bin_id columns reveal which bins participated in the swap
The swap_for_y flag denotes swap direction (X→Y when true, Y→X when false)

CPMM Pool 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).

Query Optimization Guidelines

Follow these best practices when querying the indexed data:

Include block_date or block_date_utc filters whenever possible to take advantage of partition pruning
For extended time-series analysis, leverage the minmax index on block_time
Queries filtering by token or wallet addresses benefit from bloom filter indexes
When joining across tables, use signature as the join key since it uniquely identifies each transaction
Remember to apply decimal conversion when working with raw token amounts

Data Lifecycle

All tables enforce a 1-year TTL policy; records are automatically purged once they exceed this retention period.