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)

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.