-- Polymarket Screener — Database Schema
CREATE TABLE IF NOT EXISTS screener_markets (
id TEXT PRIMARY KEY, -- conditionId
question TEXT NOT NULL,
slug TEXT,
event_id TEXT,
event_title TEXT,
event_slug TEXT,
category TEXT, -- primary category (Politics, Sports, Crypto, etc.)
tags TEXT, -- JSON array of all tag labels
image_url TEXT,
yes_price REAL DEFAULT 0,
no_price REAL DEFAULT 0,
spread REAL DEFAULT 0,
volume_24h REAL DEFAULT 0,
volume_total REAL DEFAULT 0,
liquidity REAL DEFAULT 0,
end_date TEXT,
neg_risk INTEGER DEFAULT 0,
clob_token_ids TEXT, -- JSON array [yes_token_id, no_token_id]
group_item_title TEXT, -- for multi-outcome events (e.g. "Spain")
active INTEGER DEFAULT 1,
resolved INTEGER DEFAULT 0, -- 1 once we've captured the resolved outcome
winning_outcome TEXT, -- outcome label that won (CLOB token winner=true), NULL until resolved
resolve_checked_at TEXT, -- last time the resolve cron polled CLOB for this market (backoff/round-robin)
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_markets_category ON screener_markets(category);
CREATE INDEX IF NOT EXISTS idx_markets_volume ON screener_markets(volume_24h DESC);
CREATE INDEX IF NOT EXISTS idx_markets_active ON screener_markets(active);
CREATE INDEX IF NOT EXISTS idx_markets_end_date ON screener_markets(end_date);
CREATE INDEX IF NOT EXISTS idx_markets_event ON screener_markets(event_id);
CREATE TABLE IF NOT EXISTS screener_stats (
id INTEGER PRIMARY KEY DEFAULT 1,
total_markets INTEGER DEFAULT 0,
total_events INTEGER DEFAULT 0,
total_volume_24h REAL DEFAULT 0,
top_mover_question TEXT,
top_mover_change REAL DEFAULT 0,
avg_spread REAL DEFAULT 0,
categories TEXT, -- JSON {name: count}
fetched_at TEXT DEFAULT (datetime('now'))
);
-- Ensure stats row exists
INSERT OR IGNORE INTO screener_stats (id) VALUES (1);
-- Price alerts
CREATE TABLE IF NOT EXISTS alerts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
market_id TEXT NOT NULL,
side TEXT NOT NULL DEFAULT 'yes', -- 'yes' or 'no'
direction TEXT NOT NULL DEFAULT 'above', -- 'above' or 'below'
threshold REAL NOT NULL,
triggered INTEGER DEFAULT 0,
triggered_at TEXT,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_alerts_market ON alerts(market_id);
CREATE INDEX IF NOT EXISTS idx_alerts_active ON alerts(triggered);
-- Whale alerts: subscribe to a whale's FUTURE trades. last_trade_ts is the
-- newest trade seen at subscribe/last-check time; the whale cron bumps
-- pending_count when newer trades arrive for a subscribed address.
CREATE TABLE IF NOT EXISTS whale_alerts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
whale_address TEXT NOT NULL UNIQUE,
last_trade_ts TEXT,
pending_count INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_whale_alerts_addr ON whale_alerts(whale_address);
-- Volume daily snapshots (one row per market per day for anomaly detection)
CREATE TABLE IF NOT EXISTS volume_daily (
market_id TEXT NOT NULL,
date TEXT NOT NULL, -- YYYY-MM-DD
volume_24h REAL DEFAULT 0,
liquidity REAL DEFAULT 0,
PRIMARY KEY (market_id, date)
);
CREATE INDEX IF NOT EXISTS idx_volume_daily_date ON volume_daily(date);
-- Detected volume anomalies
CREATE TABLE IF NOT EXISTS volume_anomalies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
market_id TEXT NOT NULL,
question TEXT,
category TEXT,
score REAL NOT NULL, -- volume_24h / avg_7d (e.g. 5.0 = 500%)
volume_24h REAL DEFAULT 0,
avg_volume REAL DEFAULT 0, -- 7-day average
liquidity REAL DEFAULT 0,
detected_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_anomalies_score ON volume_anomalies(score DESC);
CREATE INDEX IF NOT EXISTS idx_anomalies_detected ON volume_anomalies(detected_at);
-- Trade history cache (from Polymarket Data API /activity)
CREATE TABLE IF NOT EXISTS trade_history (
id TEXT PRIMARY KEY, -- unique trade id
address TEXT NOT NULL, -- wallet address (lowercase)
market_id TEXT,
market_question TEXT,
market_slug TEXT,
side TEXT, -- 'BUY' or 'SELL'
outcome TEXT, -- 'Yes' or 'No'
size REAL DEFAULT 0, -- number of shares
price REAL DEFAULT 0, -- price per share
amount REAL DEFAULT 0, -- total USDC amount
fee REAL DEFAULT 0,
realized_pnl REAL DEFAULT 0,
trade_type TEXT, -- 'TRADE', 'REDEEM', 'DEPOSIT', 'WITHDRAWAL'
timestamp TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_trades_address ON trade_history(address);
CREATE INDEX IF NOT EXISTS idx_trades_timestamp ON trade_history(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_trades_market ON trade_history(market_id);
-- Whale trades (large trades detected from Data API)
CREATE TABLE IF NOT EXISTS whale_trades (
id TEXT PRIMARY KEY, -- unique trade id from Data API
address TEXT NOT NULL, -- trader wallet address (lowercase)
market_id TEXT NOT NULL,
market_question TEXT,
market_slug TEXT,
side TEXT NOT NULL, -- 'BUY' or 'SELL'
outcome TEXT NOT NULL, -- 'Yes' or 'No'
size REAL NOT NULL DEFAULT 0, -- number of shares
price REAL NOT NULL DEFAULT 0, -- price per share
amount REAL NOT NULL DEFAULT 0, -- total USDC amount
timestamp TEXT NOT NULL,
detected_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_whale_trades_address ON whale_trades(address);
CREATE INDEX IF NOT EXISTS idx_whale_trades_amount ON whale_trades(amount DESC);
CREATE INDEX IF NOT EXISTS idx_whale_trades_timestamp ON whale_trades(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_whale_trades_market ON whale_trades(market_id);
-- Whale wallets (aggregated profiles of large traders)
CREATE TABLE IF NOT EXISTS whale_wallets (
address TEXT PRIMARY KEY, -- wallet address (lowercase)
label TEXT, -- auto-generated label ("Whale #1", "Political Shark", etc.)
total_trades INTEGER DEFAULT 0,
total_volume REAL DEFAULT 0, -- lifetime USDC volume
avg_trade_size REAL DEFAULT 0,
largest_trade REAL DEFAULT 0,
win_rate REAL, -- 0.0-1.0, NULL if unknown
top_category TEXT, -- most traded category
first_seen TEXT,
last_seen TEXT,
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_whale_wallets_volume ON whale_wallets(total_volume DESC);
CREATE INDEX IF NOT EXISTS idx_whale_wallets_last_seen ON whale_wallets(last_seen DESC);
-- Edge scanner scores (heuristic model)
CREATE TABLE IF NOT EXISTS edge_scores (
market_id TEXT PRIMARY KEY,
question TEXT,
category TEXT,
image_url TEXT,
market_price REAL NOT NULL, -- current YES price
edge_score REAL NOT NULL, -- composite 0.0-1.0
signal TEXT NOT NULL DEFAULT 'NEUTRAL', -- BUY_YES, BUY_NO, NEUTRAL
confidence TEXT NOT NULL DEFAULT 'LOW', -- HIGH, MED, LOW
spread_factor REAL DEFAULT 0,
volume_factor REAL DEFAULT 0,
whale_factor REAL DEFAULT 0,
momentum_factor REAL DEFAULT 0,
price_factor REAL DEFAULT 0,
whale_yes_pct REAL DEFAULT 0, -- % of whale volume on YES side
volume_ratio REAL DEFAULT 0, -- volume_24h / liquidity
whale_count INTEGER DEFAULT 0, -- # of qualifying whale trades behind signal
whale_volume_usd REAL DEFAULT 0, -- total $ whale volume behind signal
scored_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_edge_score ON edge_scores(edge_score DESC);
CREATE INDEX IF NOT EXISTS idx_edge_signal ON edge_scores(signal);
-- ============================================================
-- Copy-trading (model B). User identity = wallet address (SIWE auth).
-- ============================================================
-- Users: wallet-based identity (no email/password). address = user_id.
CREATE TABLE IF NOT EXISTS users (
address TEXT PRIMARY KEY, -- lowercase EOA = user_id (SIWE)
vault_address TEXT, -- their vault (NULL until custody setup)
email TEXT, -- optional, notifications only
created_at TEXT DEFAULT (datetime('now')),
last_login TEXT
);
-- Per-user custom vault (model B: holds funds, delegate may trade not withdraw).
CREATE TABLE IF NOT EXISTS vaults (
vault_address TEXT PRIMARY KEY, -- deployed vault contract
owner_address TEXT NOT NULL, -- user EOA (= users.address)
delegate_address TEXT, -- our server signer enrolled in vault
chain_id INTEGER NOT NULL DEFAULT 137,
status TEXT NOT NULL DEFAULT 'pending', -- pending | active | revoked
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_vaults_owner ON vaults(owner_address);
-- Copy subscriptions: who copies whom + config (JSON CopyConfig from the UI).
CREATE TABLE IF NOT EXISTS copy_subscriptions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_address TEXT NOT NULL, -- follower (= users.address)
leader_address TEXT NOT NULL, -- whale being copied
config TEXT NOT NULL, -- JSON: alloc/limits/categories/price/mirror/drawdown
status TEXT NOT NULL DEFAULT 'active', -- active | paused
created_at TEXT DEFAULT (datetime('now')),
UNIQUE(user_address, leader_address)
);
CREATE INDEX IF NOT EXISTS idx_copysub_user ON copy_subscriptions(user_address);
CREATE INDEX IF NOT EXISTS idx_copysub_leader ON copy_subscriptions(leader_address);
-- Per-user deposit-wallet: where the copy demon trades on the user's behalf.
-- Keyed by the same identity as copy_subscriptions.user_address (Privy DID or SIWE addr).
-- Reported by the client after onboarding (deterministic from the embedded EOA).
CREATE TABLE IF NOT EXISTS user_wallets (
user_address TEXT PRIMARY KEY,
deposit_wallet TEXT NOT NULL, -- lowercase deposit-wallet address
owner_eoa TEXT, -- lowercase embedded EOA (signs CLOB orders via Privy delegate)
updated_at TEXT DEFAULT (datetime('now'))
);
-- Copy positions: trades mirrored on behalf of a user. One row per (user, token_id).
-- Multi-user ledger replacing the daemon's single-user positions.json. The daemon runs
-- off-box (Malaysia VPS) so it reads/writes this over the service API, not direct SQLite.
CREATE TABLE IF NOT EXISTS copy_positions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_address TEXT NOT NULL,
leader_address TEXT NOT NULL,
market_id TEXT NOT NULL,
token_id TEXT, -- CLOB outcome tokenID (asset) — ledger key with user_address
outcome TEXT,
title TEXT,
event_slug TEXT,
side TEXT, -- BUY | SELL
size REAL,
entry_price REAL, -- OUR actual fill (honest, not leader's)
leader_price REAL, -- leader's price for reference
status TEXT NOT NULL DEFAULT 'open', -- pending | open | closed
cost REAL, -- notional spent entering ($)
mark_pnl REAL, -- last live mark-to-market P&L
realized_pnl REAL, -- banked P&L once closed
pnl REAL, -- legacy/compat
leader_exit_seen INTEGER DEFAULT 0, -- consecutive reconcile cycles leader absent (mirror-exit confirm)
opened_at INTEGER, -- epoch seconds of entry (reconcile age math)
last_order_id TEXT, -- resting BUY order id (cancel if pending times out)
close_order_id TEXT, -- resting SELL/close order id
closed_reason TEXT, -- why closed (mirror-leader-exit / resolved-onchain-absent / ...)
source_trade_id TEXT, -- whale_trades row we copied
close_requested_at INTEGER, -- epoch secs of manual-close request (status→'closing'); daemon executes
created_at TEXT DEFAULT (datetime('now')),
closed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_copypos_user ON copy_positions(user_address);
-- NOTE: the (user_address, token_id) unique index is created in db.rs AFTER the idempotent
-- ALTER that adds token_id — putting it here would panic on existing DBs (the no-op CREATE
-- TABLE leaves the column absent when execute_batch(SCHEMA) reaches this index).