spb/crypto-trade-bot

1-- ktrade schema v1 (Phase 1 + Phase 2). Timescale hypertables are created only if the extension exists.2CREATE TABLE IF NOT EXISTS schema_migrations (version TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT now());34CREATE TABLE IF NOT EXISTS pairs (5  pair_id TEXT PRIMARY KEY,6  altname TEXT NOT NULL,7  wsname TEXT NOT NULL UNIQUE,8  base TEXT NOT NULL, quote TEXT NOT NULL,9  status TEXT NOT NULL DEFAULT 'unknown',10  ordermin NUMERIC NOT NULL DEFAULT 0, costmin NUMERIC NOT NULL DEFAULT 0, tick_size NUMERIC NOT NULL DEFAULT 0,11  pair_decimals INT NOT NULL DEFAULT 8, lot_decimals INT NOT NULL DEFAULT 8, cost_decimals INT NOT NULL DEFAULT 8,12  leverage_buy JSONB NOT NULL DEFAULT '[]', leverage_sell JSONB NOT NULL DEFAULT '[]',13  in_universe BOOLEAN NOT NULL DEFAULT false,14  raw JSONB NOT NULL DEFAULT '{}',15  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()16);1718-- Bars keyed by OPEN time (unix seconds). A bar is usable by a strategy only at ts + interval*60.19CREATE TABLE IF NOT EXISTS candles (20  symbol TEXT NOT NULL, interval_min INT NOT NULL, ts BIGINT NOT NULL,21  open DOUBLE PRECISION NOT NULL, high DOUBLE PRECISION NOT NULL, low DOUBLE PRECISION NOT NULL, close DOUBLE PRECISION NOT NULL,22  vwap DOUBLE PRECISION NOT NULL, volume DOUBLE PRECISION NOT NULL, count INT NOT NULL,23  closed BOOLEAN NOT NULL DEFAULT true,24  source TEXT NOT NULL DEFAULT 'rest',25  ingested_at TIMESTAMPTZ NOT NULL DEFAULT now(),26  PRIMARY KEY (symbol, interval_min, ts)27);28CREATE INDEX IF NOT EXISTS candles_sym_iv_ts_desc ON candles (symbol, interval_min, ts DESC);2930CREATE TABLE IF NOT EXISTS trades (31  symbol TEXT NOT NULL, trade_id BIGINT NOT NULL, ts DOUBLE PRECISION NOT NULL,32  price DOUBLE PRECISION NOT NULL, volume DOUBLE PRECISION NOT NULL,33  side TEXT NOT NULL, order_type TEXT NOT NULL,34  received_at DOUBLE PRECISION,35  PRIMARY KEY (symbol, trade_id)36);37CREATE INDEX IF NOT EXISTS trades_sym_ts ON trades (symbol, ts DESC);3839CREATE TABLE IF NOT EXISTS ticker_latest (40  symbol TEXT PRIMARY KEY, payload JSONB NOT NULL, exchange_ts DOUBLE PRECISION, received_at DOUBLE PRECISION NOT NULL41);42CREATE TABLE IF NOT EXISTS book_latest (43  symbol TEXT PRIMARY KEY, payload JSONB NOT NULL, exchange_ts TEXT, received_at DOUBLE PRECISION NOT NULL, valid BOOLEAN NOT NULL DEFAULT true44);45-- 1-second sampled microstructure series (spread, depth, imbalance)46CREATE TABLE IF NOT EXISTS book_samples (47  symbol TEXT NOT NULL, ts BIGINT NOT NULL,48  best_bid DOUBLE PRECISION, best_ask DOUBLE PRECISION, spread_bps DOUBLE PRECISION,49  bid_depth DOUBLE PRECISION, ask_depth DOUBLE PRECISION, imbalance DOUBLE PRECISION,50  PRIMARY KEY (symbol, ts)51);5253CREATE TABLE IF NOT EXISTS ingestion_state (key TEXT PRIMARY KEY, value JSONB NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now());5455CREATE TABLE IF NOT EXISTS data_gaps (56  id BIGSERIAL PRIMARY KEY, symbol TEXT NOT NULL, interval_min INT NOT NULL,57  gap_start BIGINT NOT NULL, gap_end BIGINT NOT NULL, missing_bars INT NOT NULL,58  detected_at TIMESTAMPTZ NOT NULL DEFAULT now(),59  UNIQUE (symbol, interval_min, gap_start)60);6162CREATE TABLE IF NOT EXISTS backtest_runs (63  id UUID PRIMARY KEY,64  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),65  finished_at TIMESTAMPTZ,66  status TEXT NOT NULL DEFAULT 'queued',67  strategy TEXT NOT NULL, symbol TEXT NOT NULL, interval_min INT NOT NULL,68  params JSONB NOT NULL DEFAULT '{}', cost_model JSONB NOT NULL DEFAULT '{}',69  start_ts BIGINT, end_ts BIGINT,70  metrics JSONB, provenance JSONB, artifacts JSONB,71  error TEXT,72  label TEXT73);74CREATE INDEX IF NOT EXISTS backtest_runs_created ON backtest_runs (created_at DESC);7576-- Append-only, hash-chained audit trail (prev_hash → hash). No UPDATE/DELETE is ever issued by the app.77CREATE TABLE IF NOT EXISTS audit_log (78  id BIGSERIAL PRIMARY KEY,79  ts TIMESTAMPTZ NOT NULL DEFAULT now(),80  actor TEXT NOT NULL, action TEXT NOT NULL, entity TEXT, details JSONB NOT NULL DEFAULT '{}',81  cid TEXT, prev_hash TEXT, hash TEXT NOT NULL82);8384CREATE TABLE IF NOT EXISTS risk_state (key TEXT PRIMARY KEY, value JSONB NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now());85INSERT INTO risk_state (key, value) VALUES ('kill_switch', '{"active": false, "reason": null, "since": null}') ON CONFLICT DO NOTHING;86INSERT INTO risk_state (key, value) VALUES ('limits', '{}') ON CONFLICT DO NOTHING;8788-- Paper-trading ledger (Phase 4 uses it; created now so the schema is stable)89CREATE TABLE IF NOT EXISTS paper_orders (90  client_order_id TEXT PRIMARY KEY, created_at DOUBLE PRECISION NOT NULL,91  strategy TEXT, symbol TEXT NOT NULL, side TEXT NOT NULL, order_type TEXT NOT NULL,92  qty NUMERIC NOT NULL, limit_price NUMERIC, status TEXT NOT NULL, filled_qty NUMERIC NOT NULL DEFAULT 0,93  avg_fill_price NUMERIC, fee NUMERIC NOT NULL DEFAULT 0, reason TEXT, risk_decision JSONB, updated_at DOUBLE PRECISION NOT NULL94);95CREATE TABLE IF NOT EXISTS paper_fills (96  id BIGSERIAL PRIMARY KEY, client_order_id TEXT NOT NULL REFERENCES paper_orders(client_order_id),97  ts DOUBLE PRECISION NOT NULL, symbol TEXT NOT NULL, side TEXT NOT NULL, qty NUMERIC NOT NULL, price NUMERIC NOT NULL, fee NUMERIC NOT NULL, liquidity TEXT NOT NULL98);99CREATE TABLE IF NOT EXISTS paper_positions (100  symbol TEXT PRIMARY KEY, qty NUMERIC NOT NULL DEFAULT 0, avg_cost NUMERIC NOT NULL DEFAULT 0, realized_pnl NUMERIC NOT NULL DEFAULT 0, updated_at DOUBLE PRECISION NOT NULL101);102CREATE TABLE IF NOT EXISTS paper_account (key TEXT PRIMARY KEY, value JSONB NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now());103CREATE TABLE IF NOT EXISTS paper_equity (ts BIGINT PRIMARY KEY, equity DOUBLE PRECISION NOT NULL, cash DOUBLE PRECISION NOT NULL, exposure DOUBLE PRECISION NOT NULL);104105DO $$106BEGIN107  IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'timescaledb') THEN108    PERFORM create_hypertable('candles', 'ts', chunk_time_interval => 2592000, if_not_exists => TRUE, migrate_data => TRUE);109    PERFORM create_hypertable('book_samples', 'ts', chunk_time_interval => 604800, if_not_exists => TRUE, migrate_data => TRUE);110  END IF;111END $$;112