CREATE TABLE IF NOT EXISTS trades ( timestamp DateTime('UTC'), signature String, slot UInt64, transaction_index UInt32, instruction_index UInt16, success Boolean, error Nullable(String), -- Fee Structure priority_fee Float64, bribe_fee Float64, coin_creator_fee Float64, mev_protection UInt8, -- Parties maker String, -- Balances (Pre & Post) base_balance Float64, quote_balance Float64, -- Trade Semantics trade_type UInt8, protocol UInt8, platform UInt8, -- Asset Info pool_address String, base_address String, quote_address String, -- Trade Details slippage Float32, price_impact Float32, base_amount UInt64, quote_amount UInt64, price Float64, price_usd Float64, total Float64, total_usd Float64 ) ENGINE = MergeTree() ORDER BY (base_address, timestamp, maker, signature); --- mint CREATE TABLE IF NOT EXISTS mints ( -- === Transaction Details === -- Solana signature is usually 88 characters, but we use String for flexibility. signature String, -- Converted to DateTime for easier time-based operations in ClickHouse. timestamp DateTime('UTC'), slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- === Protocol & Platform === -- Protocol codes: 0=Unknown, 1=PumpFunLaunchpad, 2=RaydiumLaunchpad, -- 3=PumpFunAMM, 4=RaydiumCPMM, 5=MeteoraBonding protocol UInt8, -- === Mint & Pool Details === mint_address String, creator_address String, pool_address String, -- === Liquidity Details === initial_base_liquidity UInt64, initial_quote_liquidity UInt64, -- === Token Metadata === token_name Nullable(String), token_symbol Nullable(String), token_uri Nullable(String), token_decimals UInt8, total_supply UInt64, is_mutable Boolean, update_authority Nullable(String), mint_authority Nullable(String), freeze_authority Nullable(String), ) ENGINE = MergeTree() ORDER BY (timestamp, creator_address, mint_address); CREATE TABLE IF NOT EXISTS migrations ( -- Transaction Details timestamp DateTime('UTC'), signature String, slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- Protocol & Platform protocol UInt8, -- Migration Details mint_address String, virtual_pool_address String, pool_address String, -- Liquidity Details migrated_base_liquidity Nullable(UInt64), migrated_quote_liquidity Nullable(UInt64) ) ENGINE = MergeTree() ORDER BY (mint_address, virtual_pool_address, pool_address, timestamp); CREATE TABLE IF NOT EXISTS fee_collections ( -- Transaction Details timestamp DateTime('UTC'), signature String, slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- Protocol & Platform protocol UInt8, -- Fee Details vault_address String, recipient_address String, -- Collected Amounts token_0_mint_address String, token_0_amount Float64, token_1_mint_address Nullable(String), token_1_amount Nullable(Float64) ) ENGINE = MergeTree() ORDER BY (vault_address, recipient_address, timestamp); CREATE TABLE IF NOT EXISTS liquidity ( -- Transaction Details -- signature String, timestamp DateTime('UTC'), slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- Protocol Info -- protocol UInt8, -- LP Action Details -- change_type UInt8, lp_provider String, pool_address String, -- Token Amounts -- base_amount UInt64, quote_amount UInt64 ) ENGINE = MergeTree() ORDER BY (timestamp, pool_address, lp_provider); CREATE TABLE IF NOT EXISTS pool_creations ( -- Transaction Details -- signature String, timestamp Datetime('UTC'), slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- Protocol Info -- protocol UInt8, -- Pool & Token Details -- creator_address String, pool_address String, base_address String, quote_address String, lp_token_address String, -- Optional Initial State -- initial_base_liquidity Nullable(UInt64), initial_quote_liquidity Nullable(UInt64), base_decimals Nullable(UInt8), quote_decimals Nullable(UInt8) ) ENGINE = MergeTree() ORDER BY (base_address, creator_address); CREATE TABLE IF NOT EXISTS transfers ( -- Transaction Details timestamp DateTime('UTC'), signature String, slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- Transfer Details source String, destination String, -- Amount & Mint Details mint_address String, amount UInt64, amount_decimal Float64, -- Balance Context === source_balance Float64, destination_balance Float64 ) ENGINE = MergeTree() ORDER BY (source, destination, mint_address, timestamp); CREATE TABLE IF NOT EXISTS supply_locks ( -- === Transaction Details === timestamp DateTime('UTC'), signature String, slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- === Protocol Info === protocol UInt8, -- === Vesting Details === contract_address String, sender String, recipient String, mint_address String, total_locked_amount Float64, final_unlock_timestamp UInt64 ) ENGINE = MergeTree() ORDER BY (timestamp, mint_address, sender, recipient); CREATE TABLE IF NOT EXISTS supply_lock_actions ( -- === Transaction Details === signature String, timestamp DateTime('UTC'), slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- === Protocol Info === protocol UInt8, -- === Action Details === action_type UInt8, -- e.g., 0 for Withdraw, 1 for Topup contract_address String, user String, mint_address String, amount Float64 ) ENGINE = MergeTree() ORDER BY (timestamp, mint_address, user); CREATE TABLE IF NOT EXISTS burns ( -- Transaction Details timestamp DateTime('UTC'), signature String, slot UInt64, success Boolean, error Nullable(String), priority_fee Float64, -- Burn Details mint_address String, source String, amount UInt64, amount_decimal Float64, source_balance Float64 ) ENGINE = MergeTree() ORDER BY (mint_address, source, timestamp); -------- Wallet schema CREATE TABLE IF NOT EXISTS wallet_profiles ( updated_at DateTime('UTC'), first_seen_ts DateTime('UTC'), last_seen_ts DateTime('UTC'), wallet_address String, tags Array(String), deployed_tokens Array(String), funded_from String, funded_timestamp UInt32, funded_signature String, funded_amount Float64 ) ENGINE = ReplacingMergeTree(updated_at) PRIMARY KEY (wallet_address) ORDER BY (wallet_address); CREATE TABLE IF NOT EXISTS wallet_profile_metrics ( updated_at DateTime('UTC'), wallet_address String, balance Float64, transfers_in_count UInt32, transfers_out_count UInt32, spl_transfers_in_count UInt32, spl_transfers_out_count UInt32, total_buys_count UInt32, total_sells_count UInt32, total_winrate Float32, stats_1d_realized_profit_sol Float64, stats_1d_realized_profit_usd Float64, stats_1d_realized_profit_pnl Float32, stats_1d_buy_count UInt32, stats_1d_sell_count UInt32, stats_1d_transfer_in_count UInt32, stats_1d_transfer_out_count UInt32, stats_1d_avg_holding_period Float32, stats_1d_total_bought_cost_sol Float64, stats_1d_total_bought_cost_usd Float64, stats_1d_total_sold_income_sol Float64, stats_1d_total_sold_income_usd Float64, stats_1d_total_fee Float64, stats_1d_winrate Float32, stats_1d_tokens_traded UInt32, stats_7d_realized_profit_sol Float64, stats_7d_realized_profit_usd Float64, stats_7d_realized_profit_pnl Float32, stats_7d_buy_count UInt32, stats_7d_sell_count UInt32, stats_7d_transfer_in_count UInt32, stats_7d_transfer_out_count UInt32, stats_7d_avg_holding_period Float32, stats_7d_total_bought_cost_sol Float64, stats_7d_total_bought_cost_usd Float64, stats_7d_total_sold_income_sol Float64, stats_7d_total_sold_income_usd Float64, stats_7d_total_fee Float64, stats_7d_winrate Float32, stats_7d_tokens_traded UInt32, stats_30d_realized_profit_sol Float64, stats_30d_realized_profit_usd Float64, stats_30d_realized_profit_pnl Float32, stats_30d_buy_count UInt32, stats_30d_sell_count UInt32, stats_30d_transfer_in_count UInt32, stats_30d_transfer_out_count UInt32, stats_30d_avg_holding_period Float32, stats_30d_total_bought_cost_sol Float64, stats_30d_total_bought_cost_usd Float64, stats_30d_total_sold_income_sol Float64, stats_30d_total_sold_income_usd Float64, stats_30d_total_fee Float64, stats_30d_winrate Float32, stats_30d_tokens_traded UInt32 ) ENGINE = MergeTree ORDER BY (wallet_address, updated_at); CREATE TABLE IF NOT EXISTS wallet_holdings ( updated_at DateTime('UTC'), start_holding_at DateTime('UTC'), wallet_address String, mint_address String, current_balance Float64, realized_profit_pnl Float32, realized_profit_sol Float64, realized_profit_usd Float64, history_transfer_in UInt32, history_transfer_out UInt32, history_bought_amount Float64, history_bought_cost_sol Float64, history_sold_amount Float64, history_sold_income_sol Float64 ) ENGINE = MergeTree ORDER BY (wallet_address, mint_address, updated_at); CREATE TABLE IF NOT EXISTS tokens ( updated_at DateTime('UTC'), created_at DateTime('UTC'), -- Core Identifiers token_address String, name String, symbol String, token_uri String, -- Token Metadata decimals UInt8, creator_address String, pool_addresses Array(String), -- Map Vec to Array(String) -- Protocol/Launchpad launchpad UInt8, protocol UInt8, total_supply UInt64, -- Authorities/Flags is_mutable Boolean, -- Alias for UInt8, but Boolean is clearer/modern update_authority Nullable(String), -- Map Option to Nullable(String) mint_authority Nullable(String), freeze_authority Nullable(String) ) ENGINE = ReplacingMergeTree(updated_at) PRIMARY KEY (token_address) ORDER BY (token_address, updated_at); -- Latest tokens (one row per token_address) CREATE TABLE IF NOT EXISTS tokens_latest ( updated_at DateTime('UTC'), created_at DateTime('UTC'), token_address String, name String, symbol String, token_uri String, decimals UInt8, creator_address String, pool_addresses Array(String), launchpad UInt8, protocol UInt8, total_supply UInt64, is_mutable Boolean, update_authority Nullable(String), mint_authority Nullable(String), freeze_authority Nullable(String) ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY (token_address); CREATE TABLE IF NOT EXISTS token_metrics ( updated_at DateTime('UTC'), token_address String, total_volume_usd Float64, total_buys UInt32, total_sells UInt32, unique_holders UInt32, ath_price_usd Float64 ) ENGINE = MergeTree ORDER BY (token_address, updated_at); -- ========= Latest snapshot helper tables ========= -- Keep full history in the base tables above, but read fast from these ReplacingMergeTree snapshots. -- Latest wallet profile metrics (one row per wallet_address) CREATE TABLE IF NOT EXISTS wallet_profile_metrics_latest ( updated_at DateTime('UTC'), wallet_address String, balance Float64, transfers_in_count UInt32, transfers_out_count UInt32, spl_transfers_in_count UInt32, spl_transfers_out_count UInt32, total_buys_count UInt32, total_sells_count UInt32, total_winrate Float32, stats_1d_realized_profit_sol Float64, stats_1d_realized_profit_usd Float64, stats_1d_realized_profit_pnl Float32, stats_1d_buy_count UInt32, stats_1d_sell_count UInt32, stats_1d_transfer_in_count UInt32, stats_1d_transfer_out_count UInt32, stats_1d_avg_holding_period Float32, stats_1d_total_bought_cost_sol Float64, stats_1d_total_bought_cost_usd Float64, stats_1d_total_sold_income_sol Float64, stats_1d_total_sold_income_usd Float64, stats_1d_total_fee Float64, stats_1d_winrate Float32, stats_1d_tokens_traded UInt32, stats_7d_realized_profit_sol Float64, stats_7d_realized_profit_usd Float64, stats_7d_realized_profit_pnl Float32, stats_7d_buy_count UInt32, stats_7d_sell_count UInt32, stats_7d_transfer_in_count UInt32, stats_7d_transfer_out_count UInt32, stats_7d_avg_holding_period Float32, stats_7d_total_bought_cost_sol Float64, stats_7d_total_bought_cost_usd Float64, stats_7d_total_sold_income_sol Float64, stats_7d_total_sold_income_usd Float64, stats_7d_total_fee Float64, stats_7d_winrate Float32, stats_7d_tokens_traded UInt32, stats_30d_realized_profit_sol Float64, stats_30d_realized_profit_usd Float64, stats_30d_realized_profit_pnl Float32, stats_30d_buy_count UInt32, stats_30d_sell_count UInt32, stats_30d_transfer_in_count UInt32, stats_30d_transfer_out_count UInt32, stats_30d_avg_holding_period Float32, stats_30d_total_bought_cost_sol Float64, stats_30d_total_bought_cost_usd Float64, stats_30d_total_sold_income_sol Float64, stats_30d_total_sold_income_usd Float64, stats_30d_total_fee Float64, stats_30d_winrate Float32, stats_30d_tokens_traded UInt32 ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY (wallet_address); -- Latest wallet holdings (one row per wallet_address + mint_address) CREATE TABLE IF NOT EXISTS wallet_holdings_latest ( updated_at DateTime('UTC'), start_holding_at DateTime('UTC'), wallet_address String, mint_address String, current_balance Float64, realized_profit_pnl Float32, realized_profit_sol Float64, realized_profit_usd Float64, history_transfer_in UInt32, history_transfer_out UInt32, history_bought_amount Float64, history_bought_cost_sol Float64, history_sold_amount Float64, history_sold_income_sol Float64 ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY (wallet_address, mint_address); -- Latest token metrics (one row per token_address) CREATE TABLE IF NOT EXISTS token_metrics_latest ( updated_at DateTime('UTC'), token_address String, total_volume_usd Float64, total_buys UInt32, total_sells UInt32, unique_holders UInt32, ath_price_usd Float64 ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY (token_address); CREATE TABLE IF NOT EXISTS known_wallets ( `wallet_address` String, `name` String, -- e.g., "Pump.fun Fee Vault", "Raydium CPMM Authority V4", "KOL - Ansem" `tag` String, -- e.g., "fee_vault", "dex_authority", "kol", "exchange" ) ENGINE = ReplacingMergeTree() ORDER BY (wallet_address);