Spaces:
Paused
Paused
| -- frontal-lobe2 canonical schema. | |
| -- Derived from docs/CHART_AUDIT.md (TeamRankings structural audit, 2026-09-24). | |
| -- Design-only: recreates chart shape (columns + splits/dropdowns), not TeamRankings' data. | |
| -- Target engine: DuckDB (schemas as namespaces; PRAGMA lines are SQLite-compatible fallback). | |
| -- Leagues in scope: nfl, ncf (college football), nba, mlb. NHL deferred (docs/NHL_GAP.md). | |
| CREATE SCHEMA IF NOT EXISTS bronze; | |
| CREATE SCHEMA IF NOT EXISTS silver; | |
| CREATE SCHEMA IF NOT EXISTS gold; | |
| -- ============================================================================ | |
| -- BRONZE: ingestion tracking (shared across all reference pulls and live pulls) | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS bronze.ingestion_runs ( | |
| run_id VARCHAR PRIMARY KEY, | |
| source_name VARCHAR NOT NULL, -- e.g. 'teamrankings_reference', 'espn_api', 'cfbd' | |
| source_kind VARCHAR NOT NULL, -- 'html_snapshot' | 'api' | |
| league VARCHAR, -- nfl | ncf | nba | mlb | |
| started_at TIMESTAMP NOT NULL, | |
| completed_at TIMESTAMP, | |
| row_count INTEGER, | |
| status VARCHAR NOT NULL, | |
| error_message VARCHAR | |
| ); | |
| -- ============================================================================ | |
| -- SILVER: chart catalog -- the master list of every chart/stat available per league. | |
| -- Populated from the *_stats.html / *_player-stats.html index pages (~600+ slugs total). | |
| -- This IS the spreadsheet deliverable's source of truth; export this table to build it. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.stat_catalog ( | |
| league VARCHAR NOT NULL, -- nfl | ncf | nba | mlb | |
| stat_slug VARCHAR NOT NULL, -- e.g. 'points-per-game' | |
| display_name VARCHAR NOT NULL, -- e.g. 'Points per Game' | |
| stat_scope VARCHAR NOT NULL, -- 'team' | 'player' | |
| category VARCHAR, -- offense | defense | special_teams | batting | pitching | etc. | |
| source_page VARCHAR, -- which catalog page this was found on | |
| PRIMARY KEY (league, stat_scope, stat_slug) | |
| ); | |
| CREATE TABLE IF NOT EXISTS silver.ranking_catalog ( | |
| league VARCHAR NOT NULL, | |
| rating_name VARCHAR NOT NULL, -- e.g. 'Predictive by other', 'Home Advantage' | |
| best_label VARCHAR, -- label used for #1 in that rating system | |
| worst_label VARCHAR, | |
| PRIMARY KEY (league, rating_name) | |
| ); | |
| -- ============================================================================ | |
| -- SILVER: the universal per-stat chart template. | |
| -- Confirmed identical shape across every sampled stat page (NFL/NBA points-per-game, | |
| -- MLB runs-per-game): Rank | Team | current season | Last 3 | Last 1 | Home | Away | prior season. | |
| -- One normalized fact table covers all ~600 stat_catalog entries -- do not create one | |
| -- physical table per stat. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.team_stat_value ( | |
| league VARCHAR NOT NULL, | |
| stat_slug VARCHAR NOT NULL, -- FK -> stat_catalog.stat_slug (stat_scope='team') | |
| team VARCHAR NOT NULL, | |
| season INTEGER NOT NULL, -- the 'date' dropdown selection | |
| split VARCHAR NOT NULL, -- 'overall' | 'last_3' | 'last_1' | 'home' | 'away' | |
| rank INTEGER, | |
| value DOUBLE, | |
| source_name VARCHAR NOT NULL, -- live source once wired, e.g. 'espn', 'nflverse' | |
| captured_at TIMESTAMP NOT NULL, | |
| PRIMARY KEY (league, stat_slug, team, season, split, captured_at) | |
| ); | |
| CREATE TABLE IF NOT EXISTS silver.player_stat_value ( | |
| league VARCHAR NOT NULL, | |
| stat_slug VARCHAR NOT NULL, -- FK -> stat_catalog.stat_slug (stat_scope='player') | |
| player_name VARCHAR NOT NULL, | |
| team VARCHAR, | |
| position VARCHAR, | |
| season INTEGER NOT NULL, | |
| split VARCHAR NOT NULL, | |
| rank INTEGER, | |
| value DOUBLE, | |
| source_name VARCHAR NOT NULL, | |
| captured_at TIMESTAMP NOT NULL, | |
| PRIMARY KEY (league, stat_slug, player_name, team, season, split, captured_at) | |
| ); | |
| -- ============================================================================ | |
| -- SILVER: standings. Confirmed division/conference-grouped per league; NCF has | |
| -- 11 conference tables (vs. 2 for pro leagues) and a MAC East/West sub-split. | |
| -- group_name carries the division/conference label so all leagues share one table. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.standings ( | |
| league VARCHAR NOT NULL, | |
| season INTEGER NOT NULL, | |
| group_name VARCHAR NOT NULL, -- division/conference label, e.g. 'AFC East', 'MAC East' | |
| rank_in_group INTEGER, | |
| team VARCHAR NOT NULL, | |
| overall_wins INTEGER, | |
| overall_losses INTEGER, | |
| overall_ties INTEGER, -- NCF/NFL can have ties; NULL where not applicable | |
| win_pct DOUBLE, | |
| games_back DOUBLE, | |
| group_wins INTEGER, -- division/conference record | |
| group_losses INTEGER, | |
| home_wins INTEGER, | |
| home_losses INTEGER, | |
| road_wins INTEGER, | |
| road_losses INTEGER, | |
| streak VARCHAR, | |
| source_name VARCHAR NOT NULL, | |
| captured_at TIMESTAMP NOT NULL, | |
| PRIMARY KEY (league, season, group_name, team, captured_at) | |
| ); | |
| -- ============================================================================ | |
| -- SILVER: schedules. Confirmed: date-pivoted headers with Time/Location per league, | |
| -- plus week and group(division/conference/poll) filters as splits. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.schedule ( | |
| league VARCHAR NOT NULL, | |
| season INTEGER NOT NULL, | |
| week VARCHAR, -- NFL/NCF use week labels incl. 'Bowl Week'; NBA/MLB use game date grouping | |
| game_date DATE, | |
| home_team VARCHAR, | |
| away_team VARCHAR, | |
| game_time_utc TIME, | |
| location VARCHAR, | |
| group_filter VARCHAR, -- the 'group' dropdown value this row was captured under (division/conference/poll), if applicable | |
| home_score INTEGER, | |
| away_score INTEGER, | |
| status VARCHAR, | |
| source_name VARCHAR NOT NULL, | |
| captured_at TIMESTAMP NOT NULL, | |
| PRIMARY KEY (league, season, game_date, home_team, away_team, captured_at) | |
| ); | |
| -- ============================================================================ | |
| -- SILVER: projections. Confirmed 2-tier (NFL/NBA/MLB) or 3-tier (NCF, adds ties) | |
| -- structure: Current record / Projected record / Playoff-odds percentages. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.season_projection ( | |
| league VARCHAR NOT NULL, | |
| season INTEGER NOT NULL, | |
| team VARCHAR NOT NULL, | |
| current_wins INTEGER, | |
| current_losses INTEGER, | |
| current_ties INTEGER, -- NCF only; NULL elsewhere | |
| projected_wins DOUBLE, | |
| projected_losses DOUBLE, | |
| projected_ties DOUBLE, -- NCF only | |
| playoff_pct DOUBLE, | |
| win_division_pct DOUBLE, | |
| top_seed_pct DOUBLE, | |
| win_championship_pct DOUBLE, -- Win SB / Champs / WS Champs / Bowl Eligible% depending on league | |
| undefeated_pct DOUBLE, -- NCF only; NULL elsewhere | |
| source_name VARCHAR NOT NULL, | |
| captured_at TIMESTAMP NOT NULL, | |
| PRIMARY KEY (league, season, team, captured_at) | |
| ); | |
| -- ============================================================================ | |
| -- SILVER: odds board. SCHEMA IS PROVISIONAL -- the real column structure was NOT | |
| -- captured (client-side AJAX render, see CHART_AUDIT.md "Confirmed gaps"). This | |
| -- shape is inferred from The Odds API's standard market fields, not from a | |
| -- confirmed TeamRankings sample. Revise once a headless-browser capture or the | |
| -- AJAX endpoint response is inspected directly. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.odds_snapshot ( -- PROVISIONAL | |
| league VARCHAR NOT NULL, | |
| game_id VARCHAR NOT NULL, | |
| book VARCHAR NOT NULL, | |
| market VARCHAR NOT NULL, -- spread | total | moneyline | |
| home_line DOUBLE, | |
| away_line DOUBLE, | |
| home_price DOUBLE, | |
| away_price DOUBLE, | |
| is_closing_line BOOLEAN, | |
| fetched_at_utc TIMESTAMP NOT NULL, | |
| source_name VARCHAR NOT NULL, | |
| PRIMARY KEY (league, game_id, book, market, fetched_at_utc) | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_team_stat_lookup | |
| ON silver.team_stat_value (league, stat_slug, season, split); | |
| CREATE INDEX IF NOT EXISTS idx_player_stat_lookup | |
| ON silver.player_stat_value (league, stat_slug, season, split); | |
| CREATE INDEX IF NOT EXISTS idx_standings_lookup | |
| ON silver.standings (league, season, group_name); | |
| CREATE INDEX IF NOT EXISTS idx_schedule_lookup | |
| ON silver.schedule (league, season, week); | |