supabase-export.sql

-- DeepDiver Supabase Schema
-- Run this in Supabase SQL Editor

-- 1. CANSLIM Scans
CREATE TABLE scans (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  scan_time TIMESTAMPTZ NOT NULL,
  market_regime TEXT NOT NULL,
  dist_days TEXT,
  buy_ok TEXT,
  account_balance NUMERIC,
  risk_per_trade NUMERIC,
  actionable_count INT,
  metadata JSONB
);

-- 2. Stock Candidates
CREATE TABLE scan_stocks (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  scan_id BIGINT REFERENCES scans(id) ON DELETE CASCADE,
  ticker TEXT NOT NULL,
  pivot NUMERIC,
  stop NUMERIC,
  rs_rating NUMERIC,
  comp_rating NUMERIC,
  eps_rating NUMERIC,
  setup_type TEXT,
  notes TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_scan_stocks_scan_id ON scan_stocks(scan_id);
CREATE INDEX idx_scan_stocks_ticker ON scan_stocks(ticker);

-- 3. Settings
CREATE TABLE settings (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO settings (key, value) VALUES
  ('account_equity', '100000'),
  ('risk_pct', '0.01'),
  ('max_positions', '6');

-- 4. Alerts
CREATE TABLE alerts (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ticker TEXT NOT NULL,
  condition TEXT NOT NULL,
  price NUMERIC NOT NULL,
  triggered BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 5. Earnings
CREATE TABLE earnings (
  ticker TEXT PRIMARY KEY,
  earnings_date DATE NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 6. Positions
CREATE TABLE positions (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ticker TEXT NOT NULL,
  account TEXT DEFAULT 'default',
  trade_type TEXT DEFAULT 'long',
  entry_date DATE NOT NULL,
  entry_price NUMERIC NOT NULL,
  shares INT NOT NULL,
  cost_basis NUMERIC NOT NULL,
  stop_price NUMERIC,
  target_price NUMERIC,
  setup_type TEXT,
  status TEXT DEFAULT 'open',
  close_date DATE,
  close_price NUMERIC,
  pnl NUMERIC,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_positions_status ON positions(status);

-- 7. Covered Calls
CREATE TABLE covered_calls (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ticker TEXT NOT NULL,
  sell_date DATE NOT NULL,
  expiry DATE NOT NULL,
  strike NUMERIC NOT NULL,
  contracts INT NOT NULL,
  premium_per_contract NUMERIC NOT NULL,
  premium_total NUMERIC NOT NULL,
  delta NUMERIC,
  stock_price_at_sell NUMERIC,
  status TEXT DEFAULT 'open',
  close_date DATE,
  close_price NUMERIC,
  pnl NUMERIC,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 8. Routines
CREATE TABLE routines (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  date DATE NOT NULL,
  routine_type TEXT NOT NULL,
  data JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(date, routine_type)
);

-- 9. Watchlist (from project_vision.md)
CREATE TABLE watchlist (
  ticker TEXT PRIMARY KEY,
  status TEXT NOT NULL,
  sentiment_score NUMERIC,
  last_updated TIMESTAMPTZ DEFAULT NOW()
);

-- 10. Bot Config (from project_vision.md)
CREATE TABLE bot_config (
  key TEXT PRIMARY KEY,
  value TEXT,
  description TEXT
);
INSERT INTO bot_config (key, value, description)
VALUES ('trading_enabled', 'true', 'Master switch for trade execution');

-- Note: journal table should already exist from earlier setup
-- If not, create it:
CREATE TABLE IF NOT EXISTS journal (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  agent_name TEXT NOT NULL,
  category TEXT NOT NULL,
  content TEXT NOT NULL,
  meta JSONB
);

-- 11. Trading Universe (AI-focused stock discovery)
CREATE TABLE trading_universe (
  ticker TEXT PRIMARY KEY,
  company_name TEXT,
  sector TEXT,
  category TEXT,             -- 'ai_chip', 'ai_software', 'ai_cloud', 'ai_infrastructure', 'ai_beneficiary'
  score INT,                 -- 0-100 theme relevance score
  is_active BOOLEAN DEFAULT TRUE,
  last_scanned TIMESTAMPTZ,
  last_mention TIMESTAMPTZ,  -- Last theme keyword mention in news
  notes TEXT,                -- Curator's reasoning
  created_at TIMESTAMPTZ DEFAULT NOW(),
  deactivated_at TIMESTAMPTZ,
  involvement_level TEXT CHECK (involvement_level IN ('research_ai', 'build_ai', 'leverage_ai', 'use_ai'))
);

CREATE INDEX idx_trading_universe_active ON trading_universe(is_active);
CREATE INDEX idx_trading_universe_category ON trading_universe(category);
CREATE INDEX idx_trading_universe_score ON trading_universe(score DESC);