Spaces:
Paused
Paused
| -- ============================================================================= | |
| -- frontal-lobe2 : NFL sports report room | |
| -- ============================================================================= | |
| -- Purpose: give a beat writer everything needed to write a matchup preview from | |
| -- the lake alone -- injuries, inactives-track, current news, transactions -- no | |
| -- browser tab open. This is the data layer only; narrative generation (an agent | |
| -- reading this and writing the article) is a later phase, per the user. | |
| -- | |
| -- Sources, both confirmed live with real 2026 data on 2026-09-25: | |
| -- 1. nflverse injuries_2026.csv -- OFFICIAL weekly injury report (practice | |
| -- status, game status, injury type). This is the closest thing to a | |
| -- league-mandated inactives signal available pre-gameday. See caveat in | |
| -- docs/REPORT_ROOM.md: the FINAL inactives list is only released ~90 min | |
| -- before kickoff and is not covered here -- this is the last official | |
| -- report before that point (usually Fri "Questionable/Doubtful/Out"). | |
| -- 2. ESPN (site.web.api.espn.com -- confirmed reachable; the more commonly | |
| -- documented site.api.espn.com returned HTTP 403 in this environment). | |
| -- Same "unofficial/undocumented, can change without notice" risk profile | |
| -- already flagged for ESPN elsewhere in this project -- just a different, | |
| -- currently-reachable host. Three endpoints used: | |
| -- /injuries -- narrative injury context per player (why it | |
| -- matters, who benefits if they sit) | |
| -- /news, /news?team=<abbr> -- headlines + summary, tagged with | |
| -- structured athleteId/teamId categories (no fuzzy | |
| -- text matching needed to link a story to a player) | |
| -- /transactions -- roster moves (signings, releases, IR moves) | |
| -- | |
| -- Engine: DuckDB. | |
| -- ============================================================================= | |
| CREATE SCHEMA IF NOT EXISTS silver; | |
| CREATE SCHEMA IF NOT EXISTS gold; | |
| -- ============================================================================ | |
| -- Official weekly injury report (nflverse). One row per player per week. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.nfl_injury_report ( | |
| season INTEGER NOT NULL, | |
| week INTEGER NOT NULL, | |
| season_type VARCHAR, | |
| game_type VARCHAR, | |
| team VARCHAR NOT NULL, | |
| gsis_id VARCHAR, -- nflverse player id, joins to nfl_player | |
| position VARCHAR, | |
| full_name VARCHAR, | |
| report_primary_injury VARCHAR, | |
| report_secondary_injury VARCHAR, | |
| report_status VARCHAR, -- Questionable | Doubtful | Out | |
| practice_primary_injury VARCHAR, | |
| practice_secondary_injury VARCHAR, | |
| practice_status VARCHAR, -- Did Not Participate | Limited | Full | |
| ingest_run_id VARCHAR NOT NULL, | |
| PRIMARY KEY (season, week, team, gsis_id, full_name) | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_injury_team_week | |
| ON silver.nfl_injury_report (season, week, team); | |
| -- ============================================================================ | |
| -- ESPN narrative injury context. One row per player, current snapshot | |
| -- (ESPN's injuries endpoint is "current state", not a per-week history -- | |
| -- snapshot it repeatedly if a history is wanted later). | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.nfl_injury_narrative ( | |
| espn_athlete_id VARCHAR NOT NULL, | |
| player_name VARCHAR, | |
| team VARCHAR, | |
| status VARCHAR, -- Questionable | Doubtful | Out | IR | |
| injury_type VARCHAR, -- e.g. 'Toe', 'Achilles' | |
| injury_location VARCHAR, -- e.g. 'Leg' | |
| injury_side VARCHAR, -- Left | Right | |
| return_date DATE, | |
| short_comment VARCHAR, -- one-line practice-report style note | |
| long_comment VARCHAR, -- full narrative paragraph, matchup context | |
| reported_at TIMESTAMP, | |
| fetched_at_utc TIMESTAMP NOT NULL, | |
| source_name VARCHAR NOT NULL DEFAULT 'espn', | |
| PRIMARY KEY (espn_athlete_id, fetched_at_utc) | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_injury_narr_team | |
| ON silver.nfl_injury_narrative (team, fetched_at_utc); | |
| -- ============================================================================ | |
| -- News articles. League-wide + team-scoped pulls land here; category tags | |
| -- (below) are what actually link a story to a specific player/team. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.nfl_news_article ( | |
| espn_article_id VARCHAR PRIMARY KEY, | |
| headline VARCHAR, | |
| description VARCHAR, -- ESPN's own summary, not just a title | |
| article_type VARCHAR, -- e.g. 'Media', 'HeadlineNews', 'Story' | |
| published_at TIMESTAMP, | |
| last_modified_at TIMESTAMP, | |
| is_premium BOOLEAN, -- ESPN+ gated; still has headline/description | |
| fetched_at_utc TIMESTAMP NOT NULL, | |
| source_name VARCHAR NOT NULL DEFAULT 'espn' | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_news_published | |
| ON silver.nfl_news_article (published_at); | |
| -- Many-to-many: one article can tag multiple players/teams (a trade story tags | |
| -- two teams and the player involved, for instance). | |
| -- No composite PK here on purpose: espn_athlete_id/team are legitimately NULL | |
| -- for non-athlete/non-team tags (e.g. a 'league' tag has neither), and DuckDB | |
| -- (like most SQL engines) rejects NULL in a PRIMARY KEY column. A tag_id | |
| -- surrogate key keeps this table de-duplicable without that constraint. | |
| CREATE TABLE IF NOT EXISTS silver.nfl_news_article_tag ( | |
| tag_id VARCHAR PRIMARY KEY, -- article_id + tag_type + subject, hashed | |
| espn_article_id VARCHAR NOT NULL, | |
| tag_type VARCHAR NOT NULL, -- 'athlete' | 'team' | 'league' | |
| espn_athlete_id VARCHAR, | |
| team VARCHAR, -- abbreviation, when tag_type='team' | |
| tag_description VARCHAR -- display name as ESPN tagged it | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_newstag_athlete | |
| ON silver.nfl_news_article_tag (espn_athlete_id); | |
| CREATE INDEX IF NOT EXISTS ix_newstag_team | |
| ON silver.nfl_news_article_tag (team); | |
| -- ============================================================================ | |
| -- Transactions (signings, releases, IR moves, trades). | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS silver.nfl_transaction ( | |
| transaction_id VARCHAR NOT NULL, | |
| team VARCHAR, | |
| description VARCHAR NOT NULL, -- ESPN's own transaction text | |
| transaction_date DATE, | |
| fetched_at_utc TIMESTAMP NOT NULL, | |
| source_name VARCHAR NOT NULL DEFAULT 'espn', | |
| PRIMARY KEY (transaction_id, fetched_at_utc) | |
| ); | |
| CREATE INDEX IF NOT EXISTS ix_txn_team_date | |
| ON silver.nfl_transaction (team, transaction_date); | |
| -- ============================================================================ | |
| -- GOLD: espn_athlete_id <-> nflverse gsis_id bridge. ESPN and nflverse use | |
| -- different player id schemes; name-matched as a best effort (case-insensitive, | |
| -- suffix-stripped). Anything unresolved stays NULL rather than a wrong guess -- | |
| -- check gold.v_nfl_player_id_bridge_unmatched before trusting a join silently. | |
| -- ============================================================================ | |
| CREATE TABLE IF NOT EXISTS gold.nfl_player_id_bridge ( | |
| gsis_id VARCHAR, | |
| espn_athlete_id VARCHAR, | |
| match_method VARCHAR NOT NULL, -- 'exact_name' | 'normalized_name' | 'manual' | |
| PRIMARY KEY (gsis_id, espn_athlete_id) | |
| ); | |
| -- ============================================================================ | |
| -- GOLD: the matchup bundle. Parameterized by two team abbreviations -- run as | |
| -- a query template (see scripts/matchup_report.py), not a static materialized | |
| -- view, since "which two teams" changes every call. | |
| -- | |
| -- This view returns injuries + news + transactions for ONE team; the report | |
| -- script calls it twice (once per side) and stitches the bundle together, so | |
| -- the underlying SQL stays simple and reusable rather than a single giant | |
| -- cross-joined query. | |
| -- ============================================================================ | |
| CREATE OR REPLACE VIEW gold.v_nfl_team_report_card AS | |
| SELECT 'injury_official' AS section, r.team, r.full_name AS subject, r.report_status AS headline, | |
| r.report_primary_injury AS detail, NULL AS narrative, | |
| NULL::TIMESTAMP AS occurred_at | |
| FROM silver.nfl_injury_report r | |
| JOIN (SELECT max(season) AS season, max(week) AS week FROM silver.nfl_injury_report) latest | |
| ON r.season = latest.season AND r.week = latest.week | |
| UNION ALL | |
| SELECT 'injury_narrative', team, player_name, status, | |
| injury_type || COALESCE(' (' || injury_location || ')', ''), long_comment, | |
| reported_at | |
| FROM silver.nfl_injury_narrative | |
| WHERE fetched_at_utc = (SELECT max(fetched_at_utc) FROM silver.nfl_injury_narrative) | |
| UNION ALL | |
| SELECT 'news', t.team, a.headline, a.description, NULL, a.description, a.published_at | |
| FROM silver.nfl_news_article a | |
| JOIN silver.nfl_news_article_tag t ON t.espn_article_id = a.espn_article_id | |
| WHERE t.tag_type = 'team' | |
| UNION ALL | |
| SELECT 'transaction', team, description, NULL, NULL, NULL, transaction_date::TIMESTAMP | |
| FROM silver.nfl_transaction; | |
| CREATE INDEX IF NOT EXISTS ix_bridge_gsis ON gold.nfl_player_id_bridge (gsis_id); | |