builtbykev
2366660f5e
Line movement system: - Baseline capture on first odds fetch of the day - Movement detection >= 0.5 points with direction (up/down) - Sharp money heuristic (sharp_action/public_action/unknown) - GET /api/movements with player, stat_type, min_movement filters - Movements included in GET /api/odds/nba live responses Cascade detection system: - Scratch detection: player props disappear from 2+ books - Affected user lookup via scan_sessions + picks - Parlay re-grade without scratched legs - cascade_alerts created for affected users - GET /api/alerts (Analyst/Desk only), PATCH /api/alerts/:id/read Zero extra Odds API credits — all detection piggybacks on existing fetches. Migration 002: line_baselines, line_movements, cascade_alerts tables. 30 new tests, 188 total (161 Node.js + 27 Python), all passing. Phase 2 Core Product COMPLETE. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
82 lines
3.1 KiB
PL/PgSQL
82 lines
3.1 KiB
PL/PgSQL
-- Feature 2.2 — Line Movement + Cascade Detection
|
|
-- New tables: line_baselines, line_movements, cascade_alerts
|
|
|
|
-- ============================================================
|
|
-- TABLE: line_baselines
|
|
-- Morning baseline lines for movement detection
|
|
-- ============================================================
|
|
CREATE TABLE public.line_baselines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport TEXT NOT NULL,
|
|
game_date DATE NOT NULL,
|
|
player TEXT NOT NULL,
|
|
stat_type TEXT NOT NULL,
|
|
book TEXT NOT NULL,
|
|
baseline_line NUMERIC(5,1) NOT NULL,
|
|
captured_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_baseline_unique ON public.line_baselines(game_date, player, stat_type, book);
|
|
CREATE INDEX idx_baseline_date ON public.line_baselines(game_date);
|
|
|
|
-- No RLS needed — server-only table, not user-facing
|
|
|
|
-- ============================================================
|
|
-- TABLE: line_movements
|
|
-- Detected line movements throughout the day
|
|
-- ============================================================
|
|
CREATE TABLE public.line_movements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
sport TEXT NOT NULL,
|
|
game_date DATE NOT NULL,
|
|
player TEXT NOT NULL,
|
|
stat_type TEXT NOT NULL,
|
|
book TEXT NOT NULL,
|
|
baseline_line NUMERIC(5,1) NOT NULL,
|
|
current_line NUMERIC(5,1) NOT NULL,
|
|
movement NUMERIC(4,1) NOT NULL,
|
|
direction TEXT NOT NULL CHECK (direction IN ('up', 'down')),
|
|
sharp_indicator TEXT CHECK (sharp_indicator IN ('sharp_action', 'public_action', 'unknown')),
|
|
detected_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_movements_date ON public.line_movements(game_date);
|
|
CREATE INDEX idx_movements_player ON public.line_movements(player);
|
|
|
|
-- No RLS needed — server-only table
|
|
|
|
-- ============================================================
|
|
-- TABLE: cascade_alerts
|
|
-- Alerts for users whose scans are affected
|
|
-- ============================================================
|
|
CREATE TABLE public.cascade_alerts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
|
alert_type TEXT NOT NULL CHECK (alert_type IN ('player_scratched', 'line_moved', 'parlay_regraded')),
|
|
scan_session_id UUID REFERENCES public.scan_sessions(id),
|
|
player TEXT,
|
|
detail TEXT NOT NULL,
|
|
is_read BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_alerts_user ON public.cascade_alerts(user_id, is_read);
|
|
|
|
ALTER TABLE public.cascade_alerts ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "alerts_select_own" ON public.cascade_alerts
|
|
FOR SELECT USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "alerts_update_own" ON public.cascade_alerts
|
|
FOR UPDATE USING (auth.uid() = user_id)
|
|
WITH CHECK (auth.uid() = user_id);
|
|
|
|
-- Cleanup: delete baselines older than 2 days (call periodically)
|
|
CREATE OR REPLACE FUNCTION public.cleanup_old_baselines()
|
|
RETURNS void AS $$
|
|
BEGIN
|
|
DELETE FROM public.line_baselines WHERE game_date < (CURRENT_DATE - INTERVAL '2 days');
|
|
DELETE FROM public.line_movements WHERE game_date < (CURRENT_DATE - INTERVAL '2 days');
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|