# Fourth&Data

**When it's fourth and short, ask the data.**

Fourth&Data is a free, public [MCP](https://modelcontextprotocol.io) server that
turns 26 seasons of NFL play-by-play (1999–2025) and 24 of college football
(2002–2025) into analyst answers. Ask your AI assistant a football question in
plain English; it calls curated tools and answers with the verdict first and the
evidence attached — league ranks, trends, sample sizes, and only the caveats
that actually apply.

## Connect

**Endpoint:** `https://fourthanddata.fly.dev/mcp`

**Claude** (claude.ai or Desktop): Settings → Connectors → *Add custom
connector* → paste the endpoint URL. Or with Claude Code:
```bash
claude mcp add --transport http fourthanddata https://fourthanddata.fly.dev/mcp
```

**Cursor**: add to `~/.cursor/mcp.json`:
```json
{ "mcpServers": { "fourthanddata": { "url": "https://fourthanddata.fly.dev/mcp" } } }
```

**ChatGPT**: Settings → Connectors (developer mode) → add the endpoint URL.

Then just ask: *"Lions 4th-and-2 at the 34, down 4, 6:10 left — do they go?"*

**Free tier:** no key needed, ~60 requests/hour per IP. The gateway may
briefly serve cache-only answers under heavy load (it says so honestly when it
does). No accounts, no tracking — client IPs are salted-hashed before they
touch a log.

## Data, attribution, honesty

- NFL data from [nflverse](https://github.com/nflverse) (CC-BY 4.0 — thank
  you). College data from [cfbfastR / sportsdataverse](https://github.com/sportsdataverse)
  and the [CollegeFootballData.com](https://collegefootballdata.com) API.
- College EPA for 2022–2025 comes from our own model (`cfb_ep_v1`, validated
  r = 0.973 against cfbfastR's reference EPA); those seasons are labeled with
  their fidelity in every answer that uses them.
- Win-probability values are **empirical** — the share of games actually won
  from each state since 1999 — not a simulation. Every tool's envelope carries
  a `methodology` field saying exactly how its numbers are built.
- Coverage edges: PROE needs 2006+; NGS/FTN-derived context is 2016+/2022+;
  college player attribution in the API era (2022+) is parsed from play text
  (~87% coverage, labeled).
- **Not affiliated with, endorsed by, or connected to the NFL, the NCAA, any
  team, or any data provider.** For entertainment and analysis; nothing here
  is betting advice.

## Tools — the Fourth&Data launch toolset

Every tool answers in the same envelope: `answer` (analyst read, verdict first),
`headline`, `values`, `context` (rank/percentile/trend), `sample`, `fidelity`,
`season_type`, `methodology` (standing method notes — always true of the tool),
`caveats` (situational only — thin sample, ambiguous name, fidelity seam), `sources`.
The caveat discipline is a calibration ruling: what is always true lives in
methodology; prose carries only what *this* answer tripped.

| Tool | What it answers | Example prompt |
|---|---|---|
| `fourth_down_verdict` | Go, kick, or punt — the signature call | "Lions 4th-and-2 at the 34, down 4, 6:10 left — do they go?" |
| `player_trajectory` | Multi-season arc, NFL/CFB/both | "How has Joe Burrow's efficiency moved year over year?" |
| `explain_production` | Where a season came from (descriptive PDR layer) | "Break down Tua's 2023 — how much was the system?" |
| `schedule_adjusted_epa` | Raw vs opponent-adjusted EPA, gap called out | "Was Goff's 2024 inflated by the schedule?" |
| `prospect_comps` | Statistical college comps + what happened to them | "Who does Jayden Daniels comp to statistically?" |
| `team_identity` | What a team actually does, vs league | "What's the Ravens' identity on offense in 2024?" |
| `matchup_preview` | Unit-collision analysis + head-to-head | "Chiefs–Bills: who has the edge and where?" |
| `breakout_candidates` | Rising usage/efficiency signals, no projections | "Which WRs are trending toward a breakout?" |
| `weird_stats` | Oddities ranked by rarity vs 26 years | "What was weird in week 12 of 2024?" |
| `history_answer` | Deep-history leaders/records/counts, filters done right | "Most 4th-quarter comeback wins since 1999?" |
| `get_qb_epa_leaders` | QB leaderboard by EPA per pass play | "Who led the NFL in EPA per play in 2024?" |
| `compare_teams` | Quick two-team EPA side-by-side | "Chiefs or Bills in 2024 — who was better?" |
| `get_team_tendencies` | Run/pass split and EPA by down | "What do the 49ers call on 2nd down?" |
| `situational_split` | Performance in a named situation, vs league | "How does Mahomes play on third and long?" |
| `player_profile` | Who a player is: college, size, draft, ids | "Where did Puka Nacua go to college?" |
| `injury_report` | Injury designations from the daily snapshot | "Who's hurt on the Chiefs?" |
| `game_conditions` | Weather and venue profile for a team or stadium | "How windy are Bills games?" |
| `standings` | Record, streak, seed — proxied live from ESPN | "What's the Panthers' record?" |
| `live_scores` | Live/scheduled games right now — proxied, uncached | "Did the Eagles win?" |
| `player_career` | One player's career totals + milestone distance | "How many yards does Derrick Henry need for 12,000?" |
| `market_history` | ATS and over/under record vs the closing line | "Do the Bills cover more often at home?" |
| `market_lines` | Consensus spread/total for upcoming games | "What's the Chiefs' spread?" |
| `cache_stats` | Gateway internals | — |

`season_type` accepts `REG` (default), `POST`, or `ALL`. It defaults to regular season
because that is what "the 2024 season" means in almost every football question — letting
playoffs leak in silently inflates play counts and shuffles leaderboards.

Note that `get_qb_epa_leaders` measures EPA per **pass play** (attempts and sacks).
Designed QB runs and scrambles are classified as runs upstream and are excluded, so
mobile quarterbacks are understated relative to an all-plays EPA metric.
`MART_QB_SEASON` (behind `explain_production` and `player_trajectory`) coalesces
scrambles back in via the rusher attribution, so those tools carry the all-dropback number.

### fourth_down_verdict — how the call is made

Each choice is valued by **empirical win probability**: league conversion rates by
distance × field zone, modern-era FG make rates by kick distance, expected punt nets —
each branch resolved against a WP surface built from **actual game outcomes** of every
1st-down state since 1999 (not a model's opinion). Three surfaces: fine
(10-yd × score-diff × 7.5-min buckets), a coarse fallback for thin cells, and an
**endgame surface** (final 5 minutes: 60-second buckets, exact score diff) so the
clock is never erased in states where the clock is the state. For flat early-game
toss-ups, ordering ties break on the state-only WP model — disclosed in
`sample.tiebreak` when used. Every answer carries the coach-behavior line (how often
coaches actually went in this spot, 2015 vs latest), and toss-ups end with the
one-sentence tiebreaker: what would tip the call.

### What it can and cannot answer

`docs/verification/capability-eval.md` scores **797 real-shaped questions**
through the live tools — CORRECT / WRONG / GAP, with a plausible-sounding wrong
answer counted as WRONG rather than partial credit. Current: **76.2% answered,
zero wrong**, up from 47.9% before this phase. Failures are clustered by the
capability they need, which is what turns an eval into a build list.

It earns its keep by finding silent defects: a leaderboard that ranked on its
own rounded display column (so the 2021 EPA "leader" was whatever Snowflake
happened to emit among three QBs tied at 0.185), a rate that reported 84 of 67,
a situational predicate that could not execute, and a bio lookup that answered
"Josh Allen" with a centre who retired in 2016.

### Tests

`pytest tests/` runs against live Snowflake:
- **Sanity** (`test_sanity_fourth_down.py`): ~10 obvious-answer situations —
  must-punt, obvious-go, never-punt-from-scoring-range — that fail loudly on
  regressions like the punt-geometry inversion. Wired into CI (`.github/workflows/ci.yml`;
  runs when `SNOWFLAKE_*` secrets are configured).
- **Smoke** (`test_smoke_tools.py`): every tool with real params, envelope shape checked.

---

# Development & architecture

Everything below is for people running or extending the warehouse and gateway.

## Architecture (v0.1)

```
nflreadpy ─────parquet──▶ @INGEST_STAGE ──COPY INTO──▶ GAMEDAY.RAW      (loader role)
cfbfastR-data ─parquet──▶ @INGEST_STAGE ──COPY INTO──▶ GAMEDAY.RAW_CFB  (loader role)
CFBD API ──────parquet──▶                                   │
                                                   dbt ──▶ GAMEDAY.MARTS
                                                            │
public ◀─streamable HTTP (Fly.io)─▶ FastMCP ──read-only role┘
local  ◀─stdio────────────────────▶ server   │
          ops layer: per-IP rate limit ·     └▶ GAMEDAY.OPS.TOOL_CALLS (audit)
          TTL cache · concurrency cap ·
          query circuit breaker (cache-only mode)
```

The deployed gateway (`MCP_TRANSPORT=http`) adds an ops layer (`server/ops.py`):
per-IP sliding-window rate limiting with an optional env-configured API-key tier
(`API_KEYS="key:limit,..."`), salted-IP-hash audit logging batched to
`GAMEDAY.OPS.TOOL_CALLS` (the reader role's single, deliberate INSERT grant —
see `sql/setup_ops.sql`), a global concurrent-query cap, per-query timeouts,
and a circuit breaker that flips to cache-only mode on anomalous query volume.
Cost stack, outermost first: rate limit → cache → concurrency cap → breaker →
45s query timeout → 60s warehouse statement ceiling → 30-credit monthly
resource monitor (hard suspend).

## Setup

### 1. Snowflake trial
Sign up at signup.snowflake.com (30 days / $400 credits, no card). Pick AWS + a nearby region. Note your **account identifier** (Admin → Accounts, format like `ABC12345.us-east-1`).

### 2. Key-pair auth (Snowflake now requires MFA/keys for programmatic access)
```bash
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out sf_key.p8 -nocrypt
openssl rsa -in sf_key.p8 -pubout -out sf_key.pub
```
In a Snowflake worksheet (paste the pub key contents, minus header/footer lines):
```sql
ALTER USER YOUR_USERNAME SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';
```

### 3. Create the Snowflake objects
Run `sql/setup.sql` in a worksheet as ACCOUNTADMIN (edit `YOUR_USERNAME` at the bottom first). This creates the database, RAW/MARTS schemas, an XSMALL warehouse with 60s auto-suspend and a 60s statement timeout, a 30-credit/month resource monitor, and the loader/reader roles.

### 4. Local environment
```bash
python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
cp .env.example .env   # fill in account, user, key path
set -a; source .env; set +a
```

### 5. Ingest
```bash
python ingest/ingest.py --seasons 2023 2024 --dry-run   # verify data pull works
python ingest/ingest.py --seasons 2023 2024             # full load to Snowflake
python ingest/ingest.py --recreate --skip-export        # rebuild schema from local parquet
```

`--skip-export` reuses whatever is already in `data/*.parquet` instead of re-pulling
nflverse. `--recreate` DROPs each table before rebuilding it — required after any change
to the schema template, since `CREATE TABLE IF NOT EXISTS` silently keeps the old shape.
`--fresh` ignores the checkpoint and re-pulls.

Every `(dataset, season)` pull is checkpointed as a parquet shard under `data/shards/`,
so an interrupted run resumes instead of restarting. Seasons the upstream does not
publish are recorded as unavailable and not retried.

**Season coverage is one constant.** `LATEST_SEASON` in `ingest/ingest.py` drives every
dataset's season list; bumping it for 2026 is a one-line change.

### 5b. College ingest (CFB)
First create the schema — the loader role cannot, so run `sql/setup_cfb.sql` in Snowsight
as ACCOUNTADMIN (it is one `BEGIN...END` block because the worksheet rejects
multi-statement pastes). Then:
```bash
python ingest/cfbd_ingest.py --bulk-only      # zero API calls
python ingest/cfbd_ingest.py --estimate-only  # print planned cost, spend nothing
python ingest/cfbd_ingest.py --with-pbp-api   # include metered play-by-play
python ingest/cfbd_ingest.py --max-calls 60   # tighter ceiling for one run
```
The ingest is bulk-first regardless of tier: anything published by cfbfastR-data is
downloaded from GitHub at zero API cost, and the API covers only what bulk does not.
Calls are counted in `data/cfb/.cfbd_quota.json`, which warns at 25/50/75% and
hard-stops before the monthly limit. **Every run prints its planned call count before
spending anything**, and `--estimate-only` prints it and exits.

Two season ranges, deliberately separate: `BULK_EARLIEST` (2002) and `API_EARLIEST`
(2015). Widening the free bulk history can never silently widen a metered API pull.

Current tier is **Tier 2** ($5/mo, 30,000 calls/month, play-by-play unlocked). Set
`TIER_NAME`/`TIER_MONTHLY` if that changes. Reference costs: recruiting + portal +
returning production for 2015–2025 is 38 calls; play-by-play for 2022–2025 is 89, since
`/plays` is week-scoped (62 regular-season weeks + 27 postseason probes).

### 5c. Entity resolution (crosswalk marts)
Run after both ingests. No API key, no quota — ESPN team lists and the Sleeper
player dump are free and keyless, and both are cached for 24h.
```bash
python ingest/xwalk_teams.py --dry-run        # parquet + report, no Snowflake
python ingest/xwalk_teams.py                  # -> MARTS.MART_TEAM_XWALK/_ALIASES
python ingest/xwalk_players.py                # -> MARTS.MART_PLAYER_XWALK
python ingest/xwalk_players.py --validate-fuzzy   # held-out precision measurement
pytest tests/test_xwalk.py                    # 34 invariant + redteam tests, offline
```
Human corrections go in `data/xwalk/player_overrides.csv` as `link` or `unlink`
assertions; an `unlink` outranks any amount of automatic agreement.

### 5d. The gateway's own identity (read-only, verified by attempting writes)
The public endpoint authenticates as **`GAMEDAY_GATEWAY`**, a user that holds
exactly one role and has `DEFAULT_SECONDARY_ROLES = ()`. It is deliberately not
the user the ingest scripts use — see Lessons learned, "A read-only role is not a
read-only session". Create it with `sql/setup_readonly_hardening.sql` as
ACCOUNTADMIN, give it its **own** key pair, and point the server at it:

```bash
# local (.env)
SNOWFLAKE_GATEWAY_USER=GAMEDAY_GATEWAY
SNOWFLAKE_GATEWAY_PRIVATE_KEY_PATH=/absolute/path/gateway_key.p8

# Fly — pass the key CONTENTS, not a path
fly secrets set SNOWFLAKE_GATEWAY_USER=GAMEDAY_GATEWAY
fly secrets set SNOWFLAKE_GATEWAY_PRIVATE_KEY="$(cat gateway_key.p8)"
```
Both fall back to `SNOWFLAKE_USER` / `SNOWFLAKE_PRIVATE_KEY_PATH` when unset,
which is convenient locally and wrong in production.

Verify the posture the only way that means anything — by attempting what it
forbids, not by reading `SHOW GRANTS`:
```bash
pytest tests/test_readonly_posture.py -v
```
That asserts SELECT works across RAW/RAW_CFB/MARTS and that INSERT, UPDATE,
DELETE, DROP, CREATE, TRUNCATE and DROP DATABASE are all refused.

### 5e. Live layer (ESPN, Sleeper, weather)
First create the schema — the loader role cannot, so run `sql/setup_live.sql` in
Snowsight as ACCOUNTADMIN (one `BEGIN...END` block, same as the CFB schema).

```bash
python ingest/espn.py --probe                  # health-check all 8 endpoints
python ingest/espn.py --snapshot-injuries      # daily -> RAW_LIVE.ESPN_INJURIES
python ingest/sleeper.py --snapshot-trending   # daily -> RAW_LIVE.SLEEPER_TRENDING
python ingest/stadiums.py --validate           # check every coordinate
python ingest/stadiums.py                      # -> MARTS.MART_STADIUMS
python ingest/weather.py --league NFL --estimate-only   # planned call count
python ingest/weather.py --league NFL --backfill        # 868 calls, resumable
python ingest/weather.py --league NFL --validate        # vs nflverse temp/wind
python ingest/weather.py --league NFL --forecast        # next 14 days
```

**Live state is proxied, not mirrored.** A scoreboard is worthless five minutes
later and ESPN grants no licence to archive it, so only two feeds are
snapshotted — the ones whose *history* cannot be reconstructed afterwards:
injury listings ("who was questionable on Thursday") and Sleeper trending
adds/drops (a same-day-only feed). Everything else is fetched on demand through
`ingest/livefetch.py`, which caches to disk with a per-endpoint TTL and degrades
to cache, then to `None`, rather than raising. **A dead upstream must cost an
answer, not the gateway.**

### 6. Wire into Claude Desktop
`claude_desktop_config.json`:
```json
{
  "mcpServers": {
    "gameday": {
      "command": "/absolute/path/.venv/bin/python",
      "args": ["/absolute/path/server/server.py"],
      "env": {
        "SNOWFLAKE_ACCOUNT": "ABC12345.us-east-1",
        "SNOWFLAKE_USER": "YOUR_USERNAME",
        "SNOWFLAKE_PRIVATE_KEY_PATH": "/absolute/path/sf_key.p8"
      }
    }
  }
}
```
Restart Claude Desktop, then ask: *"Who led the NFL in EPA per play in 2024?"*

## Data inventory

**GAMEDAY.RAW — NFL, 30 tables, 5,780,790 rows, 682 MB in Snowflake.**
Every table verified row-for-row against its source parquet.

| Table | Seasons | Rows | Cols |
|---|---|---|---|
| `pbp` | 1999–2025 | 1,279,628 | 372 |
| `depth_charts` | 2001–2025 | 1,423,400 | 26 |
| `rosters_weekly` | 2002–2025 | 906,378 | 36 |
| `participation` | 2016–2025 | 478,989 | 26 |
| `player_stats` | 1999–2025 | 476,156 | 145 |
| `snap_counts` | 2012–2025 | 324,611 | 16 |
| `ftn_charting` | 2022–2025 | 185,215 | 29 |
| `rosters` | 1920–2025 | 139,685 | 36 |
| `ff_opportunity` | 2006–2025 | 112,297 | 159 |
| `injuries` | 2009–2025 | 90,752 | 17 |
| `pfr_def` / `pfr_rec` / `pfr_rush` / `pfr_pass` | 2018–2025 | 62,345 / 35,724 / 18,461 / 5,424 | 16–29 |
| `contracts` | all | 51,803 | 37 |
| `player_stats_season` | 1999–2025 | 49,514 | 143 |
| `players` | all | 25,035 | 39 |
| `officials` | 2015–2025 | 21,900 | 9 |
| `ngs_receiving` / `ngs_rushing` / `ngs_passing` | 2016–2025 | 14,731 / 6,059 / 5,933 | 22–29 |
| `team_stats` | 1999–2025 | 14,531 | 133 |
| `draft_picks` | all | 12,670 | 36 |
| `ff_playerids` | current | 12,470 | 35 |
| `combine` | 2000–2025 | 8,649 | 18 |
| `schedules` | 1999–2025 | 7,276 | 46 |
| `ff_rankings` | current | 5,281 | 25 |
| `trades` | all | 4,975 | 11 |
| `team_stats_season` | 1999–2025 | 862 | 131 |
| `teams` | current | 36 | 16 |

**GAMEDAY.RAW_CFB — college, 10 tables, 5,163,561 rows, 1,009 MB in Snowflake.**
Every table verified row-for-row against its source parquet.

| Table | Seasons | Rows | Cols | Source | API calls |
|---|---|---|---|---|---|
| `cfb_pbp` | 2002–2021 | 2,456,357 | 701 | bulk | 0 |
| `cfb_player_stats` | 2014–2025 | 1,637,447 | 70 | bulk | 0 |
| `cfb_pbp_api` | 2022–2025 | 648,121 | 30 | CFBD API | 89 |
| `cfb_rosters` | 2004–2025 | 282,157 | 18 | bulk | 0 |
| `cfb_recruiting_players` | 2015–2025 | 45,735 | 20 | CFBD API | 11 |
| `cfb_team_info` | 2004–2025 | 39,323 | 29 | bulk | 0 |
| `cfb_schedules` | 2002–2025 | 36,231 | 31 | bulk | 0 |
| `cfb_transfer_portal` | 2021–2025 | 14,422 | 10 | CFBD API | 5 |
| `cfb_recruiting_teams` | 2015–2025 | 2,338 | 5 | CFBD API | 11 |
| `cfb_returning_production` | 2015–2025 | 1,430 | 15 | CFBD API | 11 |

**GAMEDAY.MARTS — entity resolution, 3 tables, 139,012 rows.**
The crosswalk layer every cross-source answer joins through. Built by
`ingest/xwalk_players.py` and `ingest/xwalk_teams.py`; contract, invariants and
validation in `docs/verification/`.

| Table | Rows | What it is |
|---|---|---|
| `mart_player_xwalk` | 131,814 | One row per person, 29 id namespaces wide (gsis, pfr, espn, sleeper, cfbd, cfbref, sportradar, yahoo, pff, esb, smart, oddsjam, kalshi, …) with `MATCH_METHOD`, `MATCH_CONFIDENCE`, `ALT_IDS`, `HAS_CONFLICT` |
| `mart_team_aliases` | 5,279 | Every string that resolves to a franchise — abbreviations, names, nicknames, alt names, slugs — with season validity and an `AMBIGUOUS` flag |
| `mart_team_xwalk` | 1,919 | One row per franchise (32 NFL active + 43 historical, 1,844 CFB) across nflverse / ESPN / CFBD / Sleeper / odds ids |

Two deterministic namespace facts do most of the work, both **verified against
the data rather than assumed**: CFBD's `athlete_id` *is* the ESPN athlete id
(4,609 exact joins, 96.2% surname agreement), and CFBD's `team_id` *is* the ESPN
team id (752 of ESPN's 755 college teams). That turns college↔pro from a fuzzy
name match into an id equality for everyone it covers.

Match rates, scoped to each namespace's era of existence — a 1955 player cannot
have an ESPN id, and quoting a whole-history rate would just be measuring how old
the archive is:

| Population | GSIS → ESPN | → PFR | → CFBD | → Sleeper |
|---|---|---|---|---|
| played 2001+ (n=15,678) | 92.4% | 89.7% | — | — |
| rookie 2006+ (n=11,930) | 94.0% | — | **76.1%** | — |
| active 2015+ (n=8,892) | 99.7% | — | 81.0% | 84.0% |

131,814 identities, **0 unresolved conflicts**, mean confidence 0.945. The
remaining CFBD gap is mostly not a matching failure: of players in the CFBD era
with no college link, 80.9% do not appear in CFBD's roster files under any name.

**Merges are refused, not scored.** A missed link costs a "not found"; a false
link silently attributes one man's career to another inside an answer the user is
told is evidence-backed. So the union-find refuses any merge that would combine
two different league-issued anchor ids, records every refusal with a reason, and
the fuzzy tier is bounded by measurement: **99.82% person-precision at 98.2%
recall** against a held-out set of 4,977 id-proven links
(`docs/verification/xwalk-eval.md`). `pytest tests/test_xwalk.py` — 34 tests —
fails the build if that slips below 99%.

**GAMEDAY.MARTS.CFB_PBP_EPA — unified college EPA, 3,011,211 rows, 2004–2025.**
Our own EP/EPA/WP model (`cfb_ep_v1`) scores both pbp eras into one mart with
`OUR_EP`, `OUR_EPA`, `OUR_WP` and a `GARBAGE_TIME` flag — closing the gap that
2022–2025 API plays carry no EPA. Validated against cfbfastR's reference EPA at
**r = 0.973** across 1.96M bulk-era plays (gate was 0.95); externally
cross-checked at r = 0.83 vs CFBD's independent `ppa` on the API era. Model
details, guards and limitations: `docs/EPA_MODEL.md`.

**Play-by-play spans 2002–2025 across two deliberately separate tables.** Bulk
`cfb_pbp` (2002–2021) is cfbfastR's enriched ~700-column frame — EPA, win probability,
participation. `cfb_pbp_api` (2022–2025) is the raw ~30-field CFBD play record, fetched
week-by-week on Tier 2, with `season`/`season_type`/`week` stamped at fetch time since
`/plays` echoes none of them. They are not unioned: a merged table would be mostly-null
with column meaning depending on era. CFBD files every bowl and playoff game as
postseason week 1.

## Design decisions (read before interviews)

- **No `query(sql)` tool.** The model is an untrusted query author; curated parameterized tools + a read-only role with statement timeouts are the mitigation. Raw SQL access from an LLM is a prompt-injection → data-exfiltration vector.
- **INFER_SCHEMA + COPY INTO** instead of row inserts: the standard Snowflake bulk pattern, and nobody hand-writes a 372-column DDL. The template `UPPER()`s the inferred column names — see Lessons learned.
- **TTL cache in the gateway** so repeat questions never resume the warehouse — compute cost control lives in the app layer *and* in AUTO_SUSPEND.
- **TRUNCATE + full reload** is deliberate v1 simplicity; incremental merge comes with dbt.

## Lessons learned

**Parquet + INFER_SCHEMA gives you case-sensitive columns.** `CREATE TABLE … USING
TEMPLATE (INFER_SCHEMA(…))` copies Parquet field names verbatim — lowercase — and
Snowflake stores them as *quoted* identifiers. Unquoted SQL folds to uppercase, so
every query failed with `invalid identifier 'POSTEAM'`. The load itself succeeded,
because `COPY INTO` used `MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE`, so the break
only surfaced at read time. Fix: build the template explicitly with
`OBJECT_CONSTRUCT('COLUMN_NAME', UPPER(COLUMN_NAME), …)` and keep column order with
`WITHIN GROUP (ORDER BY ORDER_ID)`. Quoting every identifier in the queries also
works, but it pushes the problem onto every future query instead of fixing it once.

**`CREATE TABLE IF NOT EXISTS` hides schema changes.** Fixing the template did
nothing until the tables were dropped — hence `--recreate`. An idempotent DDL
statement is not the same as a migration.

**Snowflake has no role-level statement timeout.** `ALTER ROLE … SET
STATEMENT_TIMEOUT_IN_SECONDS` is not valid; the parameter lives on account,
warehouse, user, or session. The ceiling belongs on `GAMEDAY_WH`, which every
gateway query runs through anyway.

**A tool's default filter is part of its contract.** The tools originally had no
`season_type` filter, so "2024" quietly meant regular season *plus* playoffs —
inflating team play counts by ~10% and reordering the QB leaderboard. Defaulting to
`REG` and making `POST`/`ALL` explicit removed a whole class of wrong answers.

**A cost guardrail and a bulk load are the same knob.** The 60s
`STATEMENT_TIMEOUT_IN_SECONDS` on `GAMEDAY_WH` that protects the gateway also killed the
1.3M-row pbp `COPY`. Raising it per-session does *not* work: Snowflake enforces the lower
of the session and warehouse values, so the warehouse ceiling wins (verified — a session
set to 1800 still died at 60s). The fix is to keep statements short rather than raise the
limit: load anything over 100 MB as one `COPY` per season shard. The guardrail stays at
60s and the load still completes.

**"The file exists" is not a checkpoint.** The first full run reported `schedules` at 570
rows — it had silently reused a two-season parquet left by an earlier run. A cached
artifact is only trustworthy if *this* pipeline recorded producing it, so the cache test
is now "file exists AND the checkpoint has a `pulled_at`", not `path.exists()`.

**Check the upstream's shape before batching it.** `participation` has no `season` column,
so splitting a multi-season pull by season silently packed whole batches into one shard;
`ff_opportunity` types `season` as a *string*, so an `== 2006` filter raised rather than
returning empty. Both were invisible until the per-season shard counts were compared
against the seasons requested.

**UPPER()-ing column names can collide.** The fix for lowercase quoted identifiers has
a failure mode of its own: cfbfastR's pbp contains 40 column pairs differing only by
case (`EPA`/`epa`, `TFL`/`tfl`) because the upstream renamed columns between eras, so
the uppercase template generated duplicate identifiers and `CREATE TABLE` failed with
"Object already exists". Verified across all 40 pairs that no row ever has both
variants non-null — they're the same measure from different seasons — so the combine
step now coalesces case-variants into one column, losslessly.

**An API answers exactly what you asked, and no more.** CFBD `/plays` takes year, week
and seasonType as parameters and returns rows containing none of them. Load those rows
as-is and week attribution is gone forever. Request parameters are data — stamp them
onto the rows at fetch time. (Recoverable here without re-spending 89 calls only
because the shard filenames encoded season/type/week.)

**A column's name is not its contents.** nflverse's `players.gsis_id` holds an ESB
id for 6,093 of 25,035 players — every one of them a player the league never issued
a GSIS id to, and in every case `gsis_id == esb_id`. Nothing errors: the crosswalk
happily carries `ABB498348` as a GSIS id, and every join to `pbp` then returns zero
rows *and reports that as a finding*. A silent zero is worse than a crash, because
it looks like an answer. The fix is to type values by their shape at the single
point every source row enters (`retype_id`), not to remember the quirk at each join.

**A read-only role is not a read-only session.** `role=GAMEDAY_READER` on a
Snowflake connection sets only the session's PRIMARY role. Snowflake defaults
users to `DEFAULT_SECONDARY_ROLES = ALL`, so the session silently also holds every
other role the user has — on this account GAMEDAY_LOADER, ACCOUNTADMIN and
ORGADMIN. `current_role()` still answers `GAMEDAY_READER`, and `SHOW GRANTS TO
ROLE GAMEDAY_READER` still shows SELECT and nothing else, so both places you would
look to check say it is fine. It was found by *trying the write* rather than
reading the grants: the reader could INSERT, DELETE and DROP in `MARTS`. The
gateway now issues `USE SECONDARY ROLES NONE` on connect, and
`sql/setup_readonly_hardening.sql` gives the endpoint its own single-role user.
Verify a posture by attempting the thing it forbids; a grant listing is a claim,
not a test.

**The id you need is the one field they left out.** ESPN's injuries payload
carries each player's name, position and team — and no athlete id. The id exists
only inside the link URLs (`/player/_/id/4240824/joey-blount`). Without it every
row is unjoinable to the crosswalk, so a snapshot that looked complete would have
been decorative: names alone cannot identify a player, which is the entire
premise of `MART_PLAYER_XWALK`. It is now parsed out of the href — a genuinely
brittle dependency on a URL format, so the snapshot measures its own id coverage
every run and warns below 95% rather than silently warehousing junk.

**Validate the curated table against something that did not write it.** No free
source has NFL stadium coordinates, so `data/reference/nfl_stadiums.csv` is
hand-entered — 62 rows of exactly the kind of data that quietly contains a
transposed digit. It is checked against two independent signals: distance to an
independently geocoded city, and Open-Meteo's terrain elevation at that exact
point (Denver must come back near 1,600 m). The first run flagged two stadiums;
both were the *geocoder* being wrong — an unqualified "Santa Clara" resolves to
Cuba and "Carson" to Carson City, Nevada. So the validator now asks for several
candidates and takes the nearest. A check that cannot distinguish its own
failures from the data's is not a check.

**A good correlation can hide a useless variable.** The reconstructed game-site
weather validates at r = 0.973 on temperature and r = 0.680 on wind, with a bias
of under 1 mph — numbers that read as "wind is fine." It is not. Of the 636 games
nflverse calls 15+ mph, the reconstruction flags 219: **34% recall**, and 21% at
the 20 mph threshold. The error is concentrated exactly where the question lives,
because a reanalysis model averages wind over an ~11 km grid cell and smooths away
the local extremes, while nflverse reports a point observation at the stadium.
Aggregate fit said yes; the only cut anyone actually asks about said no. So the
rule is: prefer nflverse's observed wind wherever it exists, never threshold or
rank on the reconstruction, and say which one an answer used
(`docs/verification/weather-eval.md`).

**Rank on the number, not on the number you print.** `get_qb_epa_leaders` ordered
by `ROUND(AVG(epa), 3)` — the display column — so among QBs who round to the same
three decimals the order was whatever Snowflake returned for equal sort keys.
2021 has Stafford, Mahomes and Brady all at 0.185. It was *usually* right, which
is the worst failure mode available: the leaderboard was not reproducible between
identical calls and nothing downstream could detect it. Rank on the full-precision
value, break ties explicitly, and when the lead is smaller than the printed
precision, say "edged" and caveat it rather than crowning a winner.

**Confidence in an identity is not confidence about which identity you meant.**
`player_profile` ordered candidates by `MATCH_CONFIDENCE` and answered "Josh
Allen" with a centre who last played in 2016 — his ids were cleaner than the
Bills quarterback's, so he scored higher. The crosswalk was right; both men
exist. The lesson is that a score answering "is this row one person?" says
nothing about "is this the person asked for", and using it that way is a category
error that reads as a data bug.

**Check whether you already own the data before you buy it.** The market work
started by wiring up The Odds API. Its free tier gates historical odds behind a
paid plan, so a backtesting archive looked impossible — until a look at
`RAW.SCHEDULES` showed nflverse already ships the closing spread and total for
all 7,276 games back to 1999, under CC-BY, redistributable. So market *history*
needed no paid tier and no new vendor, and the paid API is now only responsible
for *current* lines. The convention was verified before use rather than assumed:
league-wide the data yields 49.0% home covers ex-pushes and 48.7% overs, which is
where an efficient market has to sit, and is the check that the sign is not
inverted.

**A licence can constrain a tool's shape, not just its docs.** The Odds API
permits analytical use and forbids "offering our data through your own API...
intended to serve as a source of raw data for others". Fourth&Data *is* a public
API, so a tool returning book-by-book prices would be the prohibited thing no
matter what the docstring said. `market_lines` therefore returns a cross-book
consensus and a disagreement measure and never a per-book table — enforced by a
test that fails if a bookmaker name appears in the envelope at all.

**The image is the deployment, not the repo.** `standings` and `live_scores`
imported `ingest/espn.py`, which needs polars and the Snowflake loader — neither
of which the gateway image installs, and the Dockerfile copies only `server/`.
In production that raised ImportError, the tools' own try/except turned it into a
polite "the upstream is unreachable", and the feature would have been permanently
dead while blaming ESPN. Everything worked locally, where `ingest/` is on the
path. The runtime now has a standard-library-only client, and a test fails the
build if anything under `server/` imports the ingest stack again. **A failure
that disguises itself as someone else's outage is worth a test.**

**Free tiers have shape, not just size.** The CFBD free tier's binding constraint isn't
the 1,000 calls — a full 2015–2025 pull of recruiting, portal and returning production
costs 38. It's that play-by-play is gated to a paid tier entirely, which is why the
ingest is bulk-first and the API is a fallback rather than the default.

**Name the metric you actually compute.** `get_qb_epa_leaders` filters
`play_type = 'pass'`, which includes sacks but excludes scrambles and designed runs.
Calling that "EPA per play" in the docstring would have had the model confidently
report a passing-efficiency stat as total QB value.

## MARTS layer (dbt + one python mart)

`transform/` is a dbt project: `stg_pbp_nfl` staging plus 16 marts — the
fourth-down stack (go conversion, FG make, punt nets, three WP surfaces, coach
behavior), player marts (`mart_qb_season` with opponent adjustment,
`mart_player_usage` with late-season splits and age), team marts
(`mart_team_identity` with PROE, `mart_team_def_season`), history marts
(`mart_game_team` comeback flags, `mart_player_game_extremes` all-time
percentiles), and the college side (`mart_cfb_qb_season_early`,
`mart_cfb_team_season`, `mart_cfb_comps_features` with recruiting + draft
outcomes). Full build: ~12s, well under the 60s statement ceiling.

One mart is python-built on purpose: `MART_CFB_QB_SEASON` (2015–2025) reuses
the PDR Phase-1 playText parser for API-era QB attribution
(`transform/python_marts/build_mart_cfb_qb_season.py`) — validated code the
SQL layer can't replicate.

## Roadmap
- [x] dbt: RAW → MARTS models with tests
- [x] CFBD ingest (college) + cross-league draft-class join
- [x] Fourth&Data launch toolset (14 tools, envelope voice, sanity CI)
- [x] Streamable HTTP transport + API keys + audit log table
- [x] Deploy (Fly.io) as public MCP endpoint
- [x] Scheduled market capture (GitHub Actions, 52 credits/month)
- [x] Public site (`site/`, static, no build step)
- [x] Entity resolution: player + team crosswalk marts (Group C)
- [x] Live layer: ESPN + Sleeper clients, stadium reference, weather (Group A)
- [ ] The Odds API (held: awaiting key + ToS review)
- [x] Capability eval + the cheap wins it ranked (situational, bio, injuries, conditions, standings, live)
- [x] Market layer: odds ingest (quota-gated), ATS history, consensus lines
- [ ] Deploy; re-run the eval in September (see ops/queued_jobs.md)
- [ ] Deferred: rest/travel + officials (the eval ranks them near-zero demand)
- [ ] GitHub Actions scheduled ingest
- [ ] Custom domain (fourthanddata.com) on the Fly endpoint