src/db/schema.sql

-- schema.sql
-- Run this script to initialize the TimescaleDB schema

-- First, ensure the TimescaleDB extension is enabled (requires superuser)
-- CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE IF NOT EXISTS tick_data (
    timestamp TIMESTAMPTZ NOT NULL,
    market_id VARCHAR(100),
    platform VARCHAR(20),  -- 'polymarket' or 'kalshi'
    event_name TEXT,
    outcome VARCHAR(50),   -- 'YES', 'NO', or other specific outcomes
    bid_price DECIMAL(10,8),
    bid_size DECIMAL(15,2),
    ask_price DECIMAL(10,8),
    ask_size DECIMAL(15,2),
    mid_price DECIMAL(10,8),
    volume_24h DECIMAL(15,2),
    CONSTRAINT pk_tick PRIMARY KEY (timestamp, market_id, platform, outcome)
);

-- Convert tick_data to a hypertable if TimescaleDB is installed
-- SELECT create_hypertable('tick_data', 'timestamp', if_not_exists => TRUE);

CREATE TABLE IF NOT EXISTS trades (
    trade_id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    strategy VARCHAR(50),
    market_id VARCHAR(100),
    platform VARCHAR(20),
    side VARCHAR(10),
    price DECIMAL(10,8),
    size DECIMAL(15,2),
    fees DECIMAL(15,2),
    pnl DECIMAL(15,2),
    execution_latency_ms INTEGER
);