← back to Ken

kalshi-dash/schema.sql

152 lines

-- Ken (Kalshi dashboard) reconstructed schema for local boot on Mac3 (2026-07-10).
-- Derived from the INSERT/UPDATE/SELECT column usage in server.js — the original
-- ken_* migration lived on the prod host and was never in this repo.
-- Columns are permissive (nullable) so no pipeline INSERT fails on a NOT NULL.
-- Portfolio/tournament tables are created EMPTY (their rows need the original dump
-- or an explicit reseed from PORTFOLIO_STRATEGIES).

CREATE TABLE IF NOT EXISTS ken_config (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_sentiment (
  id BIGSERIAL PRIMARY KEY,
  source TEXT, subreddit TEXT, keyword TEXT, post_title TEXT, post_url TEXT,
  score BIGINT, num_comments BIGINT, sentiment_score DOUBLE PRECISION,
  relevance_ticker TEXT, raw_text TEXT,
  captured_at TIMESTAMPTZ DEFAULT NOW(), created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_event_sentiment (
  id BIGSERIAL PRIMARY KEY,
  article_id TEXT, event_tag TEXT, market_ticker TEXT,
  sentiment DOUBLE PRECISION, confidence DOUBLE PRECISION, magnitude DOUBLE PRECISION,
  headline TEXT, source TEXT, created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_probability_shifts (
  id BIGSERIAL PRIMARY KEY,
  market_id TEXT, prior_probability DOUBLE PRECISION, sentiment_impact DOUBLE PRECISION,
  adjusted_probability DOUBLE PRECISION, market_probability DOUBLE PRECISION,
  edge DOUBLE PRECISION, num_articles BIGINT, created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_strategy_signals (
  id BIGSERIAL PRIMARY KEY,
  market_id TEXT, direction TEXT, expected_edge DOUBLE PRECISION,
  size_recommendation DOUBLE PRECISION, confidence DOUBLE PRECISION,
  reasoning TEXT, sources JSONB, risk_approved BOOLEAN,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_market_mappings (
  id BIGSERIAL PRIMARY KEY,
  market_id TEXT, keywords JSONB, category TEXT, contract_type TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_market_snapshots (
  id BIGSERIAL PRIMARY KEY,
  ticker TEXT, event_ticker TEXT, title TEXT, subtitle TEXT, category TEXT,
  yes_bid BIGINT, yes_ask BIGINT, no_bid BIGINT, no_ask BIGINT, last_price BIGINT,
  volume BIGINT, volume_24h BIGINT, open_interest BIGINT, spread BIGINT,
  captured_at TIMESTAMPTZ DEFAULT NOW(), created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE ken_market_snapshots ADD COLUMN IF NOT EXISTS captured_at TIMESTAMPTZ DEFAULT NOW();

CREATE TABLE IF NOT EXISTS ken_predictions (
  id BIGSERIAL PRIMARY KEY,
  ticker TEXT, event_ticker TEXT, title TEXT, signal_type TEXT, side TEXT,
  confidence DOUBLE PRECISION, entry_price DOUBLE PRECISION, edge_bps DOUBLE PRECISION,
  reasoning TEXT, sources JSONB,
  outcome TEXT, exit_price DOUBLE PRECISION, pnl_cents BIGINT, resolved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_performance (
  id BIGSERIAL PRIMARY KEY,
  total_markets BIGINT, active_markets BIGINT, predictions_made BIGINT,
  long_signals BIGINT, short_signals BIGINT, sentiment_items BIGINT,
  scan_duration_ms BIGINT, slack_sent BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_trades (
  id BIGSERIAL PRIMARY KEY,
  ticker TEXT, event_ticker TEXT, title TEXT, side TEXT, action TEXT,
  price_cents BIGINT, count BIGINT, cost_cents BIGINT, order_id TEXT,
  signal_type TEXT, confidence DOUBLE PRECISION, reasoning TEXT, status TEXT,
  exit_price DOUBLE PRECISION, pnl_cents BIGINT, closed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_learning (
  id BIGSERIAL PRIMARY KEY,
  category TEXT, signal_type TEXT, win_rate DOUBLE PRECISION,
  avg_pnl_cents DOUBLE PRECISION, total_trades BIGINT,
  last_updated TIMESTAMPTZ DEFAULT NOW(), created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_reddit_tracking (
  id BIGSERIAL PRIMARY KEY,
  source TEXT, subreddit TEXT, keyword TEXT, enabled BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_portfolios (
  id BIGINT PRIMARY KEY,
  name TEXT, strategy TEXT,
  balance_cents BIGINT, daily_budget_cents BIGINT,
  total_invested_cents BIGINT DEFAULT 0, total_returned_cents BIGINT DEFAULT 0,
  starting_balance_cents BIGINT, color TEXT,
  trades_won BIGINT DEFAULT 0, trades_lost BIGINT DEFAULT 0, trades_expired BIGINT DEFAULT 0,
  streak BIGINT DEFAULT 0, best_trade_cents BIGINT DEFAULT 0, worst_trade_cents BIGINT DEFAULT 0,
  last_trade_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT NOW()
);
-- backfill columns on an already-created ken_portfolios (idempotent)
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS total_returned_cents BIGINT DEFAULT 0;
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS trades_won BIGINT DEFAULT 0;
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS trades_lost BIGINT DEFAULT 0;
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS trades_expired BIGINT DEFAULT 0;
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS streak BIGINT DEFAULT 0;
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS best_trade_cents BIGINT DEFAULT 0;
ALTER TABLE ken_portfolios ADD COLUMN IF NOT EXISTS worst_trade_cents BIGINT DEFAULT 0;

CREATE TABLE IF NOT EXISTS ken_portfolio_trades (
  id BIGSERIAL PRIMARY KEY,
  portfolio_id BIGINT, signal_id BIGINT, market_id TEXT, market_title TEXT,
  direction TEXT, entry_price_cents BIGINT, contracts BIGINT, cost_cents BIGINT,
  reasoning TEXT, confidence DOUBLE PRECISION, ai_enhanced BOOLEAN, mc_consistent BOOLEAN,
  status TEXT, exit_price_cents BIGINT, pnl_cents BIGINT, resolved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_portfolio_snapshots (
  id BIGSERIAL PRIMARY KEY,
  portfolio_id BIGINT, nav_cents BIGINT, open_positions BIGINT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_daily_pnl (
  id BIGSERIAL PRIMARY KEY,
  portfolio_id BIGINT, date DATE, closed_trades BIGINT, wins BIGINT, losses BIGINT,
  daily_pnl_cents BIGINT, cumulative_pnl_cents BIGINT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (portfolio_id, date)
);

CREATE TABLE IF NOT EXISTS ken_tournament_entries (
  id BIGSERIAL PRIMARY KEY,
  tournament_id BIGINT, portfolio_id BIGINT, start_balance_cents BIGINT,
  end_balance_cents BIGINT, pnl_cents BIGINT, trades_count BIGINT,
  status TEXT, elimination_reason TEXT, created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ken_weekly_tournaments (
  id BIGSERIAL PRIMARY KEY,
  week_start DATE, week_end DATE, budget_per_portfolio_cents BIGINT,
  notes TEXT, status TEXT DEFAULT 'active', created_at TIMESTAMPTZ DEFAULT NOW()
);