# Database tables

## 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:

```sql
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
```

2. Calculate daily migration volume:

```sql
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
```

3. Find most migrated tokens:

```sql
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:

```sql
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
```

2. Fee Analysis:

```sql
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
```

3. Active Traders Analysis:

```sql
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
```

4. Bin Range Analysis:

```sql
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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://web3-engineering.gitbook.io/web3-engineering-docs/overview/solana-indexer/database-tables.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.