118 lines
5.0 KiB
SQL
118 lines
5.0 KiB
SQL
-- ---------------------------------------------------------------
|
|
-- 017 — Intelligence tables (Session 6b).
|
|
--
|
|
-- historical_props : ParlayAPI bulk archive (3.7M+ rows over time)
|
|
-- line_snapshots : every SharpAPI pull stored for movement tracking
|
|
-- ref_profiles : per-referee tendencies (foul rate, pace, bias)
|
|
-- coach_profiles : per-coach system + pace preferences
|
|
-- game_ref_assignments : game-day ref crew, with derived crew impact
|
|
--
|
|
-- All five tables are service-role-only — no RLS public read policies.
|
|
-- Internal services read/write via getSupabaseServiceClient(); public
|
|
-- consumers (Ledger, Pricing) read from grade_history / resolution_results.
|
|
-- ---------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.historical_props (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport text NOT NULL,
|
|
game_date date NOT NULL,
|
|
player_name text NOT NULL,
|
|
stat_type text NOT NULL,
|
|
line numeric NOT NULL,
|
|
closing_line numeric,
|
|
result text CHECK (result IS NULL OR result IN ('over','under','push')),
|
|
source text NOT NULL DEFAULT 'parlayapi',
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_hist_props_player
|
|
ON public.historical_props (player_name, stat_type);
|
|
CREATE INDEX IF NOT EXISTS idx_hist_props_sport_date
|
|
ON public.historical_props (sport, game_date DESC);
|
|
|
|
ALTER TABLE public.historical_props ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- ---------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.line_snapshots (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
game_id text NOT NULL,
|
|
sport text NOT NULL,
|
|
player_name text,
|
|
player_id text,
|
|
stat_type text NOT NULL,
|
|
line numeric NOT NULL,
|
|
over_odds integer,
|
|
under_odds integer,
|
|
book text,
|
|
consensus_median numeric,
|
|
snapshot_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
-- Partial index on (game_id, stat_type) is the access pattern for movement
|
|
-- queries; full snapshot_at index covers retention sweeps (delete > 90d).
|
|
CREATE INDEX IF NOT EXISTS idx_snapshots_game
|
|
ON public.line_snapshots (game_id, stat_type, snapshot_at);
|
|
CREATE INDEX IF NOT EXISTS idx_snapshots_time
|
|
ON public.line_snapshots (snapshot_at);
|
|
|
|
ALTER TABLE public.line_snapshots ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- ---------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.ref_profiles (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
ref_name text NOT NULL UNIQUE,
|
|
avg_fouls_per_game numeric,
|
|
avg_free_throws_per_game numeric,
|
|
pace_impact numeric, -- + = faster games
|
|
home_whistle_bias numeric, -- + = favors home team
|
|
games_reffed integer,
|
|
last_updated timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ref_profiles_name ON public.ref_profiles (ref_name);
|
|
|
|
ALTER TABLE public.ref_profiles ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- ---------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.coach_profiles (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
coach_name text NOT NULL,
|
|
team text NOT NULL,
|
|
sport text NOT NULL,
|
|
career_avg_pace numeric,
|
|
current_team_pace numeric,
|
|
pace_delta numeric, -- career_avg - current_team
|
|
tenure_games integer NOT NULL DEFAULT 0,
|
|
primary_player text,
|
|
system_style text, -- 'half_court_iso','motion','transition','post_heavy'
|
|
without_primary_style text,
|
|
without_primary_pace_delta numeric,
|
|
last_updated timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (coach_name, team, sport)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_coach_profiles_team
|
|
ON public.coach_profiles (team, sport);
|
|
|
|
ALTER TABLE public.coach_profiles ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- ---------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS public.game_ref_assignments (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
game_id text NOT NULL UNIQUE,
|
|
sport text NOT NULL,
|
|
game_date date NOT NULL,
|
|
ref1_name text,
|
|
ref2_name text,
|
|
ref3_name text,
|
|
ref_crew_avg_fouls numeric, -- precomputed from ref_profiles
|
|
ref_crew_pace_impact numeric,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_game_refs_game
|
|
ON public.game_ref_assignments (game_id);
|
|
CREATE INDEX IF NOT EXISTS idx_game_refs_date
|
|
ON public.game_ref_assignments (game_date);
|
|
|
|
ALTER TABLE public.game_ref_assignments ENABLE ROW LEVEL SECURITY;
|