onchain.sql

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<String> 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<String> 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);