-- --------------------------------------------------------------- -- 016 — Resolution infrastructure. -- -- closing_lines : Pinnacle closing reference, captured at tip-off -- (CLV vs our graded line) -- player_id_map : ESPN ↔ MLB Stats API ↔ NBA API cross-references -- resolution_results : append-only per-prop outcome with full audit trail -- (DNP, corrections, closing line link, CLV) -- -- Also retrofits grade_history for the resolution pipeline: -- + game_id (resolution route queries by this) -- + resolved_at (idempotency: resolved_at IS NULL = unresolved) -- + margin (actual_value - line for grading-loop analytics) -- + correction_* (audit trail for morning correction sweeps) -- + player_minutes / was_starter (DNP detection inputs) -- + result CHECK (relaxed to include 'void' for postponed games) -- -- RLS posture: all three new tables are service-role-only (no public read). -- The Ledger queries grade_history directly; resolution_results is an -- internal audit table. -- --------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.closing_lines ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), game_id text NOT NULL, sport text NOT NULL, player_name text NOT NULL, player_espn_id text, stat_type text NOT NULL, pinnacle_line numeric, pinnacle_over_odds integer, pinnacle_under_odds integer, fair_over_probability numeric, fair_under_probability numeric, captured_at timestamptz NOT NULL DEFAULT now(), UNIQUE (game_id, player_espn_id, stat_type) ); CREATE INDEX IF NOT EXISTS idx_closing_lines_game ON public.closing_lines(game_id); CREATE INDEX IF NOT EXISTS idx_closing_lines_sport_date ON public.closing_lines(sport, captured_at); ALTER TABLE public.closing_lines ENABLE ROW LEVEL SECURITY; -- --------------------------------------------------------------- -- player_id_map: first introduced in migration 014 with a wider but -- differently-shaped column set. CREATE TABLE IF NOT EXISTS would no-op -- if 014 already created the table, leaving 016's columns missing — -- which would break populate-player-ids.js and oddsPapiAdapter.js. -- -- Strategy: create the table if absent, then ALTER ADD COLUMN IF NOT -- EXISTS to bring 014's schema forward. The columns 014 has that 016 -- doesn't (nba_id, nhl_id, nfl_gsis_id, ufc_id, headshot_url) stay -- intact — harmless extras. CREATE TABLE IF NOT EXISTS public.player_id_map ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), display_name text NOT NULL, normalized_name text NOT NULL, espn_id text NOT NULL UNIQUE, mlbam_id text, nba_api_id text, sport text NOT NULL, team_abbr text, active boolean NOT NULL DEFAULT true, updated_at timestamptz NOT NULL DEFAULT now() ); -- Bring forward 016's columns onto whatever shape 014 left behind. -- These are no-ops when 016 created the table from scratch. ALTER TABLE public.player_id_map ADD COLUMN IF NOT EXISTS normalized_name text, ADD COLUMN IF NOT EXISTS nba_api_id text, ADD COLUMN IF NOT EXISTS team_abbr text, ADD COLUMN IF NOT EXISTS espn_id text, ADD COLUMN IF NOT EXISTS mlbam_id text; -- 014's UNIQUE(sport, display_name, team) does NOT cover espn_id alone, -- which the new code's upsert relies on. Add it idempotently via a -- unique index — does nothing if espn_id is already unique-enforced. CREATE UNIQUE INDEX IF NOT EXISTS idx_player_id_espn_unique ON public.player_id_map(espn_id) WHERE espn_id IS NOT NULL; CREATE INDEX IF NOT EXISTS idx_player_id_sport ON public.player_id_map(sport); CREATE INDEX IF NOT EXISTS idx_player_id_name ON public.player_id_map(normalized_name); CREATE INDEX IF NOT EXISTS idx_player_id_mlbam ON public.player_id_map(mlbam_id) WHERE mlbam_id IS NOT NULL; ALTER TABLE public.player_id_map ENABLE ROW LEVEL SECURITY; -- --------------------------------------------------------------- CREATE TABLE IF NOT EXISTS public.resolution_results ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), grade_id uuid NOT NULL, game_id text NOT NULL, sport text NOT NULL, player_espn_id text NOT NULL, player_name text NOT NULL, stat_type text NOT NULL, line numeric NOT NULL, direction text NOT NULL CHECK (direction IN ('over', 'under')), actual_value numeric NOT NULL, result text NOT NULL CHECK (result IN ('hit', 'miss', 'push', 'void')), margin numeric, player_minutes numeric, was_starter boolean, closing_line_id uuid REFERENCES public.closing_lines(id), clv numeric, resolved_at timestamptz NOT NULL DEFAULT now(), correction_note text, correction_original_value numeric, correction_original_result text ); CREATE INDEX IF NOT EXISTS idx_resolution_game ON public.resolution_results(game_id); CREATE INDEX IF NOT EXISTS idx_resolution_sport_date ON public.resolution_results(sport, resolved_at); CREATE INDEX IF NOT EXISTS idx_resolution_player ON public.resolution_results(player_espn_id); CREATE INDEX IF NOT EXISTS idx_resolution_result ON public.resolution_results(result); ALTER TABLE public.resolution_results ENABLE ROW LEVEL SECURITY; -- --------------------------------------------------------------- -- grade_history retrofit. Columns confirmed missing by Session 6a audit. -- --------------------------------------------------------------- ALTER TABLE public.grade_history ADD COLUMN IF NOT EXISTS game_id text, ADD COLUMN IF NOT EXISTS resolved_at timestamptz, ADD COLUMN IF NOT EXISTS margin numeric, ADD COLUMN IF NOT EXISTS player_minutes numeric, ADD COLUMN IF NOT EXISTS was_starter boolean, ADD COLUMN IF NOT EXISTS closing_line_id uuid REFERENCES public.closing_lines(id), ADD COLUMN IF NOT EXISTS clv numeric, ADD COLUMN IF NOT EXISTS correction_note text, ADD COLUMN IF NOT EXISTS correction_original_value numeric, ADD COLUMN IF NOT EXISTS correction_original_result text; CREATE INDEX IF NOT EXISTS idx_grade_history_game ON public.grade_history(game_id); CREATE INDEX IF NOT EXISTS idx_grade_history_unresolved ON public.grade_history(game_id) WHERE resolved_at IS NULL; -- Relax the result CHECK to include 'void' for postponed/canceled games. -- Drop-and-recreate is atomic inside the migration; production data with -- any of the legacy values (hit/miss/push/pending) revalidates cleanly. ALTER TABLE public.grade_history DROP CONSTRAINT IF EXISTS grade_history_result_check; ALTER TABLE public.grade_history ADD CONSTRAINT grade_history_result_check CHECK (result IS NULL OR result IN ('hit', 'miss', 'push', 'pending', 'void'));