Spaces:
Paused
Paused
File size: 9,799 Bytes
367f592 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 | -- =============================================================================
-- 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);
|