offchain.sql

-- Table for Twitter/X posts with Unix timestamps
CREATE TABLE IF NOT EXISTS default.x_posts
(
    `timestamp` DateTime('UTC'),
    `id` String,
    `type` String,
    `author_handle` String,
    `body_text` String,
    `urls_list` Array(String),
    `mentions_list` Array(String),
    `images` Array(String),
    `is_quote_tweet` UInt8,
    `subtweet_author_handle` Nullable(String),
    `subtweet_text` Nullable(String),
    `subtweet_images` Array(String),
    `raw_data_compressed` String
)
ENGINE = ReplacingMergeTree(timestamp)
ORDER BY (id, timestamp);

-- Table for follows, using handles instead of IDs
CREATE TABLE IF NOT EXISTS default.x_follows
(
    `timestamp` DateTime('UTC'),
    `event_id` String,
    `author_handle` String,
    `followed_author_handle` String,
    `raw_data_compressed` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, author_handle, followed_author_handle);

-- Table for specific profile actions, using handles
CREATE TABLE IF NOT EXISTS default.x_profile_actions
(
    `timestamp` DateTime('UTC'),
    `event_id` String,
    `author_handle` String,
    `action_type` String,
    `raw_data_compressed` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, author_handle);

-- Table for DexScreener trending snapshots with Unix timestamps
CREATE TABLE IF NOT EXISTS default.dextrending_snapshots
(
    `timestamp` DateTime('UTC'),
    `timeframe` String,
    `trending_tokens` Nested(
        -- Core Identifiers
        token_address String,
        token_name String,
        ticker String,
        token_image String,
        protocol String,
        created_at UInt32,

        -- Financial Metrics
        market_cap Float64,
        volume_sol Float64,
        liquidity_sol Float64,

        -- Activity Metrics
        buy_count UInt32,
        sell_count UInt32,
        
        -- Holder & Tokenomics Metrics
        top_10_holders_pct Float32,
        lp_burned_pct Nullable(Float32),
        total_supply Float64,
        
        -- Social Links
        website Nullable(String),
        twitter Nullable(String),
        telegram Nullable(String)
    )
)
ENGINE = MergeTree()
ORDER BY (timestamp, timeframe);

-- Table for Lighthouse protocol stats (wide format) with Unix timestamps
CREATE TABLE IF NOT EXISTS default.protocol_stats_snapshots
(
    `timestamp` DateTime('UTC'),
    `timeframe` String, -- '5m', '1h', '6h', '24h'
    
    -- Protocol Specific Stats
    `protocol_name` String, -- e.g., 'All', 'Pump V1', 'Meteora DLMM'
    `total_volume` Float64,
    `total_transactions` UInt64,
    `total_traders` UInt64,
    `total_tokens_created` UInt32,
    `total_migrations` UInt32,

    -- Percentage Change Metrics
    `volume_pct_change` Float32,
    `transactions_pct_change` Float32,
    `traders_pct_change` Float32,
    `tokens_created_pct_change` Float32,
    `migrations_pct_change` Float32
)
ENGINE = MergeTree()
ORDER BY (timestamp, timeframe, protocol_name);

CREATE TABLE IF NOT EXISTS default.phantomtrending_snapshots
(
    `timestamp` UInt64,
    `timeframe` String,
    `trending_tokens` Nested(
        `token_address` String,
        `token_name` String,
        `ticker` String,
        `token_image` String,
        `market_cap` Float64,
        `volume` Float64,
        `price` Float64,
        `price_change_pct` Float32,
        `volume_change_pct` Float32
    )
)
ENGINE = MergeTree()
ORDER BY (timestamp, timeframe);

-- Table for tokens that have paid for a profile (one-time event per token)
CREATE TABLE IF NOT EXISTS default.dex_paid_tokens
(
    `timestamp` UInt64,
    `token_address` String,
    `chain_id` String,
    `description` Nullable(String),
    `icon_url` Nullable(String),
    `header_url` Nullable(String),
    
    -- Structured Social Links
    `website` Nullable(String),
    `twitter` Nullable(String),
    `telegram` Nullable(String),
    `discord` Nullable(String)
)
ENGINE = ReplacingMergeTree(timestamp)
PRIMARY KEY (token_address)
ORDER BY (token_address);

-- Table to log every boost event over time
CREATE TABLE IF NOT EXISTS default.dex_boost_events
(
    `timestamp` UInt64,
    `token_address` String,
    `chain_id` String,
    `amount` Float64,
    `total_amount` Float64,
    `description` Nullable(String),
    `icon_url` Nullable(String),
    `header_url` Nullable(String),

    -- Structured Social Links
    `website` Nullable(String),
    `twitter` Nullable(String),
    `telegram` Nullable(String),
    `discord` Nullable(String)
)
ENGINE = MergeTree()
ORDER BY (timestamp);

CREATE TABLE IF NOT EXISTS default.dex_top_boost_snapshots
(
    `timestamp` UInt64,
    `top_boosted_tokens` Nested(
        `token_address` String,
        `chain_id` String,
        `total_amount` Float64,
        `description` Nullable(String),
        `icon_url` Nullable(String),
        `header_url` Nullable(String),
        
        -- Structured Social Links
        `website` Nullable(String),
        `twitter` Nullable(String),
        `telegram` Nullable(String),
        `discord` Nullable(String)
    )
)
ENGINE = MergeTree()
ORDER BY timestamp;

CREATE TABLE IF NOT EXISTS default.x_trending_hashtags_snapshots
(
    `timestamp` DateTime('UTC'),
    `country_code` String,
    `trends` Nested(
        `name` String,
        `tweet_count` Nullable(UInt64)
    )
)
ENGINE = MergeTree()
ORDER BY (country_code, timestamp);

CREATE TABLE IF NOT EXISTS default.pump_replies
(
    `timestamp` DateTime('UTC'),
    `id` UInt64,
    `mint` String,
    `user` String,
    `username` Nullable(String),
    `text` String,
    `total_likes` UInt32,
    `file_uri` Nullable(String)
)
ENGINE = MergeTree()
ORDER BY (mint, timestamp);

CREATE TABLE IF NOT EXISTS default.wallet_socials
(
    `wallet_address` String,
    `pumpfun_username` Nullable(String),
    `pumpfun_image` Nullable(String),
    `bio` Nullable(String),
    `pumpfun_followers` Nullable(UInt32),
    `pumpfun_following` Array(String),
    `kolscan_name` Nullable(String),
    `twitter_username` Nullable(String),
    `telegram_channel` Nullable(String),
    `profile_image` Nullable(String),
    `cabalspy_name` Nullable(String),
    `updated_at` DateTime('UTC'),
    `axiom_kol_name` Nullable(String)
)
ENGINE = ReplacingMergeTree(updated_at)
PRIMARY KEY (wallet_address)
ORDER BY (wallet_address);

CREATE TABLE IF NOT EXISTS default.leaderboard_snapshots
(
    `timestamp` DateTime('UTC'),
    `source` String, -- 'kolscan', 'cabalspy', 'axiom_vision'
    `wallets` Array(String) -- An array of wallet addresses, ordered by rank (index 0 = rank 1)
)
ENGINE = MergeTree()
ORDER BY (source, timestamp);

CREATE TABLE IF NOT EXISTS default.alpha_groups
(
    `group_id` String,
    `name` String,
    `short_name` Nullable(String),
    `image_url` Nullable(String),
    `source` Enum8('discord' = 1, 'telegram' = 2, 'telegram_call' = 3),
    `updated_at` DateTime('UTC')
)
ENGINE = MergeTree()
PRIMARY KEY (group_id)
ORDER BY (group_id);

CREATE TABLE IF NOT EXISTS default.alpha_mentions
(
    `timestamp` DateTime('UTC'),
    `group_id` String,
    `channel_id` String,
    `message_id` String,
    `chain` Nullable(String),
    `token_address` String
)
ENGINE = MergeTree()
ORDER BY (message_id, token_address, timestamp);

CREATE TABLE IF NOT EXISTS default.chain_stats_snapshots
(
    `timestamp` DateTime('UTC'),
    `sol_price_usd` Float64,
    `jito_tip_fee` Float64
)
ENGINE = MergeTree()
ORDER BY (timestamp);

CREATE TABLE IF NOT EXISTS default.cex_listings
(
    `timestamp` DateTime('UTC'),
    `exchange_name` String,
    `token_name` String,
    `ticker` Nullable(String),
    `token_address` Nullable(String),
    `chain_id` Nullable(String),
    `source_tweet_id` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, exchange_name);

CREATE TABLE IF NOT EXISTS default.tiktok_trending_hashtags_snapshots
(
    `timestamp` DateTime('UTC'),
    `country_code` String,
    `trends` Nested(
        `hashtag_name` String,
        `rank` UInt16,
        `publish_count` UInt32,
        `video_views` UInt64,
        `creator_nicknames` Array(String)
    )
)
ENGINE = MergeTree()
ORDER BY (country_code, timestamp);