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.