vyndr/supabase/migrations/014_pipeline_tables.sql

-- ---------------------------------------------------------------
-- 014 — Data pipeline tables.
--   odds_cache         : last-seen odds per book/prop
--   line_history       : append-only line samples (drives steam detection + CLV)
--   cascade_alerts     : injury / lineup / ump-ref / weather deltas
--   player_stats_cache : enricher payloads from nba_api, pybaseball, etc.
--   grade_history     : every grade issued + result (drives Ledger + CLV)
--   prop_correlations  : measured correlation between stat pairs
--   player_id_map      : cross-source player ID mapping
--
-- RLS posture:
--   Every table here is WRITTEN by the service role only (no policy = no
--   row visible to anon/authenticated for INSERT/UPDATE/DELETE — service
--   role bypasses RLS). Public-readable surfaces (grade_history) get a
--   SELECT policy. Internal-only tables stay locked down.
-- ---------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.odds_cache (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  game_id      TEXT NOT NULL,
  sport        TEXT NOT NULL,
  player_id    TEXT,
  player_name  TEXT,
  stat_type    TEXT,
  book         TEXT NOT NULL,
  line         NUMERIC,
  odds_over    INTEGER,
  odds_under   INTEGER,
  source       TEXT NOT NULL,
  fetched_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (game_id, COALESCE(player_id, ''), COALESCE(stat_type, ''), book)
);
CREATE INDEX IF NOT EXISTS idx_odds_cache_game ON public.odds_cache (game_id, fetched_at DESC);
CREATE INDEX IF NOT EXISTS idx_odds_cache_player ON public.odds_cache (player_id, stat_type);

CREATE TABLE IF NOT EXISTS public.line_history (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  game_id      TEXT NOT NULL,
  player_id    TEXT,
  stat_type    TEXT,
  book         TEXT NOT NULL,
  line         NUMERIC,
  odds         INTEGER,
  recorded_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_line_history_recent
  ON public.line_history (game_id, recorded_at DESC);
CREATE INDEX IF NOT EXISTS idx_line_history_prop
  ON public.line_history (player_id, stat_type, recorded_at DESC);

CREATE TABLE IF NOT EXISTS public.cascade_alerts (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trigger_type    TEXT NOT NULL CHECK (trigger_type IN (
                    'injury', 'lineup', 'weather', 'ref', 'umpire', 'manual'
                  )),
  trigger_detail  JSONB NOT NULL,
  affected_props  JSONB NOT NULL DEFAULT '[]'::jsonb,
  affected_count  INTEGER NOT NULL DEFAULT 0,
  resolved        BOOLEAN NOT NULL DEFAULT FALSE,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_cascade_recent
  ON public.cascade_alerts (created_at DESC) WHERE resolved = FALSE;

CREATE TABLE IF NOT EXISTS public.player_stats_cache (
  player_id   TEXT NOT NULL,
  sport       TEXT NOT NULL,
  bucket      TEXT NOT NULL DEFAULT 'default',
  stats_data  JSONB NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (player_id, sport, bucket)
);

CREATE TABLE IF NOT EXISTS public.grade_history (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id      TEXT NOT NULL,
  player_name    TEXT,
  sport          TEXT NOT NULL,
  stat_type      TEXT,
  line           NUMERIC,
  direction      TEXT CHECK (direction IN ('over', 'under')),
  grade          TEXT,
  projection     NUMERIC,
  modeled_prob   NUMERIC,
  implied_prob   NUMERIC,
  game_date      DATE,
  result         TEXT CHECK (result IN ('hit', 'miss', 'push', 'pending')),
  actual_value   NUMERIC,
  graded_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_grade_history_player
  ON public.grade_history (player_id, game_date DESC);
CREATE INDEX IF NOT EXISTS idx_grade_history_recent
  ON public.grade_history (graded_at DESC);

CREATE TABLE IF NOT EXISTS public.prop_correlations (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  player_id    TEXT NOT NULL,
  stat_a       TEXT NOT NULL,
  stat_b       TEXT NOT NULL,
  correlation  NUMERIC NOT NULL,
  sample_size  INTEGER,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (player_id, stat_a, stat_b)
);

CREATE TABLE IF NOT EXISTS public.player_id_map (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  display_name  TEXT NOT NULL,
  sport         TEXT NOT NULL,
  team          TEXT,
  nba_id        TEXT,
  espn_id       TEXT,
  mlbam_id      TEXT,
  nhl_id        TEXT,
  nfl_gsis_id   TEXT,
  ufc_id        TEXT,
  headshot_url  TEXT,
  active        BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (sport, display_name, team)
);
CREATE INDEX IF NOT EXISTS idx_player_sport ON public.player_id_map (sport, active);
CREATE INDEX IF NOT EXISTS idx_player_name ON public.player_id_map (display_name);

-- ── RLS ────────────────────────────────────────────────────────────────────

ALTER TABLE public.odds_cache         ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.line_history       ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.cascade_alerts     ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.player_stats_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.grade_history      ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.prop_correlations  ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.player_id_map      ENABLE ROW LEVEL SECURITY;

-- Public-readable surfaces. The Ledger is published; correlation data and
-- the player ID map are referenced by the UI for headshots + cross-source
-- IDs. odds_cache / line_history / cascade_alerts / player_stats_cache stay
-- locked to the service role.

DROP POLICY IF EXISTS "Anyone can read grade history" ON public.grade_history;
CREATE POLICY "Anyone can read grade history"
  ON public.grade_history
  FOR SELECT
  USING (TRUE);

DROP POLICY IF EXISTS "Anyone can read prop correlations" ON public.prop_correlations;
CREATE POLICY "Anyone can read prop correlations"
  ON public.prop_correlations
  FOR SELECT
  USING (TRUE);

DROP POLICY IF EXISTS "Anyone can read player id map" ON public.player_id_map;
CREATE POLICY "Anyone can read player id map"
  ON public.player_id_map
  FOR SELECT
  USING (TRUE);

GRANT SELECT ON public.grade_history     TO anon, authenticated;
GRANT SELECT ON public.prop_correlations TO anon, authenticated;
GRANT SELECT ON public.player_id_map     TO anon, authenticated;