Yahoo ADP Arbitrage Overlay¶

Status: AS-BUILT (2026-07-26) — shipped end-to-end in PRs #367–#376 (prod image 20260726v1). The spec below is preserved as written; the implementation deviates in the four places listed in Deviations from spec. Date: 2026-07-26 (spec authored 2026-07-25) Format assumption at spec time: H-Town 2026 (470.l.563684), 11-team H2H, half-PPR (0.5/rec). As built, league size and scoring format derive live per focused league (league_settings, migration 043) — currently 12-team half-PPR; nothing is hardcoded.

Deviations from spec (as-built)¶

Where the shipped implementation differs from the spec sections below, the implementation wins:

1. Runner CLI — runners/yahoo_adp_runner.py takes --dry-run, --depth, --league-key (yahoo_adp_runner.py:662–671). There is no --manual/--daemon pair; Section 6's "follow the daily_adp_runner.py pattern" flag list does not apply.
2. Value-rank basis is two-mode, not fixed half-PPR — ml_pipeline/adp_edge.py:38–47 computes value_rank under a value_rank_basis of league_format (live-derived from the focused league's league_settings) or pigpro_interim, falling back to interim when format-specific board coverage is below MIN_FORMAT_COVERAGE = 100 players. Section 3's fixed "half-PPR VOR, 11-team baselines" input is the spec-time framing only.
3. Badge hygiene constants — edges are only surfaced within a draftable window: DRAFTABLE_ADP_RANK = 120 and edge rounds are capped at EDGE_ROUND_CAP = 3 (ml_pipeline/adp_edge.py, PR #372). These are in addition to Section 3's gating rules.
4. K/DEF handling is richer than "exclude from edge scoring" — K and DEF are excluded from both rank pools (value rank and Yahoo ADP rank) in adp_edge.py, and the War Room additionally need-schedules K/DEF recommendations on its own track (PR #376).

1. Goal & rationale¶

The draft board's ADP column is currently populated from FFC and Sleeper via _adp_from_v1() — a blend across all scoring formats. H-Town 2026 is half-PPR; Yahoo's platform ADP is a global pool blended across every scoring variant Yahoo supports site-wide. That mismatch is exploitable: receivers are priced at roughly PPR value by the market while our half-PPR VOR shades them 2–3 rounds lower, and TEs/QBs are systematically late relative to VOR replacement math.

The overlay makes this gap visible at pick time. It computes a per-player edge score (how many draft slots the market is wrong, in our favor or against us), badges it on the Value Map, injects it into the War Room board, and feeds it into the draft recommender's pick-availability survival estimates. The alpha is real: at edge >= +10 the BUY list runs RB 9 / TE 8 / QB 7 / WR 0; the FADE list at edge <= -10 is WR 21 / RB 5 / QB 4 — exactly the half-PPR vs PPR shape you would predict.

Confidence note: July projection noise dominates individual edges below ~8 ranks; confidence on individual player names is medium; confidence on the positional pattern (WR overpriced, TE/QB underpriced in Yahoo ADP vs half-PPR VOR) is high based on a 106-player join.

2. Verified Yahoo API contract¶

Verified: live pulls from Yahoo Fantasy API on 2026-07-25, league 470.l.563684, game 470 (NFL 2026). Proof artifacts (ephemeral, session-scoped): scratchpad yahoo_adp_raw.json, yahoo_adp_sorted.json (149 rows), arbitrage_join.json.

Base URL¶

https://fantasysports.yahooapis.com/fantasy/v2/

The existing YahooFantasyConnector in data_sources/yahoo_connector.py prepends this; all requests append format=json.

Endpoint to use¶

league/470.l.563684/players;start={N};count=25;sort=OR/draft_analysis

• sort=OR = Yahoo Overall Rank. Use this for pagination. Do not use sort=AD — it returns HTTP 200 but is silently ignored; rows come back in player_id order (see Edge Case 7).
• count=25 is the max per page.
• Top-200 by Yahoo OR requires 8 calls (start=0,25,50,...,175).
• In the 2026-07-25 pull, all 100+ players in the half-PPR value top-60 appeared within OR top-150; recommend depth 200 (8 calls) for margin.

draft_analysis fields¶

All fields are JSON strings. "-" when absent (player not yet drafted or no data).

Sample (Aaron Rodgers): average_pick="117.8", average_round="12.4", average_cost="1.2", percent_drafted="0.02".

Player key / yahoo_id¶

player_key format: 470.p.{yahoo_player_id} (e.g. 470.p.7200). Extract yahoo_player_id via regex \.p\.(\d+). Note: nflverse_player_ids.yahoo_id stores these float-style ("32711.0"); strip .0 before comparing.

Format specificity (critical)¶

Yahoo ADP is one global pool. The 2026-07-25 proof pull compared league-context vs. game-context draft_analysis values for 15 overlapping sample players — they were byte-identical for every player including "-" gaps. This is not a limitation to work around; it is the reason the overlay has alpha.

Auth path¶

The app-level token (fantasy-yahoo-secret, ns fantasyfootball) is currently dead. Use the user-token connector pattern:

connector = web_dashboard._get_user_connector('08b64b98-dc03-4618-ba8d-2f847cf67eef')

The on_token_refresh persist callback must be wired (the 2026-07-24 rotation-persist fix is working; do not construct a connector without on_token_refresh — that is what killed the app token). The yahoo_api_cache in data_sources/yahoo_connector.py has a 30-min TTL on /players endpoints (_ttl_for_endpoint, line ~370).

3. Edge-score formula & gating rules¶

Inputs¶

• value_rank(p): overall rank ordered by half-PPR VOR descending, using 11-team baselines (QB12 / RB28 / WR28 / TE12). Derived from pigpro_score_history at current-season snapshot. Must be format-aware: derive scoring format from user_leagues.is_active → league_settings, not a global constant (see pigskin#364).
• yahoo_adp_rank(p): rank ordered by draft_analysis.average_pick ascending, computed client-side from the paginated pull (do not trust Yahoo server-side sort).

Primary formula¶

edge_rank(p)  = yahoo_adp_rank(p) − value_rank(p)

Positive = market undervalues the player relative to half-PPR VOR = BUY. Negative = market overvalues = FADE.

Display formula¶

edge_round(p) = round(average_pick / league_size) − round(value_rank / league_size)

Where round(x) means floor((x - 1) / league_size) + 1. For 11-team: Rd1=1–11, Rd2=12–22, Rd3=23–33, Rd4=34–44, Rd5=45–55, Rd6=56–66, Rd7=67–77, Rd8=78–88, Rd9=89–99, Rd10=100–110, Rd11=111–121, Rd12=122–132.

Gating rules (all validated on 2026-07-25 sample)¶

1. BUY gate: suppress BUY badge when VOR <= 0. Without this gate, below-replacement players like Tyler Shough (+31), Isaiah Likely (+23), Jake Ferguson (+18), Bo Nix (+16) would appear as top buys — they are not.
2. Materiality threshold: surface edge badge only when |edge_rank| >= 8 OR |edge_round| >= 1.
3. Per-position display: show positional rank edge alongside overall edge (e.g. "TE: +3 rounds" next to "+31 overall"). Without this, BUY list degenerates to all-TE/QB (1-starter positions with compressed Yahoo ADP); positional context makes it actionable.
4. K/DEF exclusion: DEF and K are outside VOR baseline — exclude from edge scoring entirely.
5. percent_drafted < 0.05 flag: treat ADP as low-confidence and add a visual qualifier; 7/149 sampled players fell here.

4. Data model & persistence¶

Target table: adp_records (existing)¶

No schema migration required. The existing table already holds ADP data from FFC and Sleeper. Upsert new Yahoo rows with:

Constraint: existing adp_value > 0 check on the table. Skip rows where average_pick == "-" or float(average_pick) <= 0; do not zero-fill.

Existing rows use: source IN ('ffc', 'sleeper'), scoring_format IN ('half', 'ppr', 'standard'). The ('yahoo', 'global') pair is unambiguous and clean.

Secondary upsert: player_sources (existing)¶

Upsert one row per successfully resolved player:

INSERT INTO player_sources (source, source_player_id, player_id, ...)
VALUES ('yahoo', '<yahoo_player_id_as_string>', '<players.id>', ...)
ON CONFLICT (source, source_player_id) DO UPDATE SET player_id = EXCLUDED.player_id;

Current row counts: sleeper 12,201 / ffc 237 / gsis 48 / yahoo 0. These rows enable future fast lookups without re-resolving.

Derived view / computed fields¶

Do not persist edge_rank or edge_round — compute them at query time from adp_records + pigpro_score_history. Persisting them would require re-materializing on every scoring run; at 8 calls/run and daily cadence, real-time join is cheaper.

5. Identity resolution / join strategy¶

Authoritative chain (existing — do not reimplement)¶

The resolution chain is already implemented in runners/yahoo_draft_sync.py:

1. yahoo_player_id (from player_key regex \.p\.(\d+)) → nflverse_player_ids.yahoo_id → nflverse_player_ids.gsis_id → players.gsis_id → players.id
2. Fallback: _RESOLVE_BY_NAME (name + position normalization)
3. DEF fallback: _RESOLVE_DEF_BY_NICK

Note: nflverse_player_ids.yahoo_id is stored float-style ("32711.0"). Strip .0 when matching against the integer extracted from player_key.

Coverage facts (prod DB, 2026-07-25)¶

• nflverse_player_ids: 7,989 rows; 5,356 have yahoo_id (67%).
• Latest pigpro_score_history snapshot: 1,096 players; 767 reachable via yahoo crosswalk (70%); 95 board rows have no gsis_id (name-fallback territory, includes 2026 rookies with synthetic IDs).
• Name+position join on the 2026-07-25 ADP pull: 147/149 matched (98.7%). Failures: Texans DEF (by design — excluded from VOR board), Stefon Diggs (no 2026 projection).

Name normalization requirements¶

The resolver must handle these confirmed problem patterns from the 2026-07-25 pull:

These are already handled by the existing data_normalizer.py fuzzy match or within _RESOLVE_BY_NAME. Verify they still resolve correctly against 2026 season data before shipping.

6. Refresh cadence & cron design¶

Schedule¶

CronJob manifest¶

Model after k8s/cronjob-ffc-adp-persist.yaml and k8s/cronjob-sleeper-adp-persist.yaml. Name: k8s/cronjob-yahoo-adp-persist.yaml. Suggested spec:

# excerpt — fill in image, env refs, namespace per project convention
metadata:
  name: yahoo-adp-persist
spec:
  schedule: "0 11 * * *"   # 06:00 CT = 11:00 UTC
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: yahoo-adp-persist
            command: ["python", "runners/yahoo_adp_runner.py"]

As-built: the shipped manifest (k8s/cronjob-yahoo-adp-persist.yaml) uses schedule: "45 11 * * *" — 06:45 CT daily, deliberately offset from the 06:00 CT collection window and the :23 league-sync-draft-watch minute — not the 0 11 * * * proposed above. The excerpt is preserved as the spec-time proposal.

Draft-week 2x/day and single draft-day pull can be handled by a separate CronJob with a date-gated schedule or a manual trigger via the scheduler UI at /scheduler.

Per-run call budget¶

8 calls per refresh (start=0,25,...,175; count=25; sort=OR; /draft_analysis). The yahoo_api_cache will absorb repeat reads within 30 min. The connector's base_connector self-limits at 100 req/min; 8 calls is negligible. Critical constraint: sustained heavy calling triggered app-level 403s on 2026-07-24 (~17 calls in a single analysis session pushed toward threshold). The daily 8-call ADP refresh is safe; do not add additional Yahoo calls in the same run window without deliberate accounting.

Runner file¶

Create runners/yahoo_adp_runner.py following the runners/daily_adp_runner.py pattern: --manual, --daemon, --help CLI flags; centralized logging; cron_runs heartbeat at start and completion.

7. UI & API integration points¶

7a. Value Map — /analytics/positions¶

Route: web_dashboard.py:3427 (draft_value_map or similar; search for analytics/positions). Data API: /api/analytics/positions/value-map, web_dashboard.py:7261. Board loader: _load_board_for_value_map(), web_dashboard.py:7144. Template: templates/analytics_positions.html, section id="draft-value-map".

Current state: each player dict in the board response carries an adp field populated from _adp_from_v1() (ml_pipeline/draft_recommender.py:129) — an FFC/Sleeper-blend artifact that is neither Yahoo nor half-PPR-specific.

Change: add yahoo_adp, edge_rank, edge_round, edge_label ('BUY'/'FADE'/None) fields to each player dict in _load_board_for_value_map(). Join from adp_records WHERE source='yahoo' per player_id. Tiers stay VOR pos_rank_range — do not alter tier boundaries.

UI treatment: badge on the player card within the tier grid. BUY = green chip with +{N} rds or +{N} picks; FADE = red chip. Only render when |edge_round| >= 1 or |edge_rank| >= 8. Show positional rank edge in tooltip or secondary line.

7b. Draft War Room — /draft/live¶

Route: draft_live_page, web_dashboard.py:6089 (/draft-room 301s here). Template: templates/draft_live.html. Board API: /api/draft-room/full-board, web_dashboard.py:6268. Yahoo pick ingestion: /api/draft-room/session/<id>/yahoo-sync, web_dashboard.py:6332.

Current state: full-board also uses _adp_from_v1 for ADP display and for the draft recommender's pick-availability survival math (ml_pipeline/draft_recommender.py).

Change: when the active session is a Yahoo-platform draft (detectable from session metadata), replace or augment _adp_from_v1 ADP in full-board with adp_records WHERE source='yahoo' per player_id. Surface edge_rank and edge_label on the board row. The recommender's pick-availability survival math should use Yahoo ADP as the prior for Yahoo-draft sessions — this is the highest-leverage integration point and can be done as a follow-on to the simpler badge display.

7c. PigPro ADP Value component¶

File: ml_pipeline/value_scorer.py, ValueScorer.WEIGHTS. Currently adp_value_score weight = 0.230, sourced from FFC/Sleeper blend.

Decision for Tony before implementing: should Yahoo ADP replace or blend with existing ADP sources in the adp_value_score input? Options: (a) replace when source='yahoo' row exists (cleanest); (b) blend yahoo + ffc + sleeper weighted average; (c) keep PigPro score unchanged and surface edge purely as UI overlay without touching the formula. Option (c) is safest for the scoring run; (a) is most correct for a Yahoo league. This decision is out of scope of the overlay spec but must be resolved before touching value_scorer.py.

8. Edge cases¶

1. Undrafted player (average_pick == "-"): skip — do not insert to adp_records, do not compute edge. If VOR > 0, surface as "not yet drafted in Yahoo" in tooltip only.

2. Low-confidence ADP (percent_drafted < 0.05): insert the row but add a low_confidence=true flag. In UI, render pick number in lighter weight / with asterisk. 7/149 sampled rows fell here.

3. Rookie with synthetic gsis_id: name+position fallback is mandatory. Jeremiyah Love (gsis_id='LOV121782', players.pigpro_score=7.29) has Yahoo ADP 26.6 (Rd3) but was absent from the latest pigpro_score_history snapshot on 2026-07-25 — a live #323 failure mode. The ADP runner must not silently drop him; log a warning and attempt insert with whatever player_id the resolver returns. If the resolver returns null, insert to a yahoo_adp_unresolved staging table (create if it does not exist) so the data is not lost.

4. Stale players rows: Tyreek Hill | WR | LAC and Stefon Diggs | WR | '' (blank team, no projection) exist in players on 2026-07-25. Gate: do not assign a BUY edge to any player with no current-season projection row in pigpro_score_history. FADE edges on stale rows are fine to display (the market still has ADP on them).

5. DEF by nickname: Yahoo names team defenses (e.g. "Texans"). _RESOLVE_DEF_BY_NICK in yahoo_draft_sync.py handles this. Exclude all DEF from edge scoring regardless of resolution status.

6. Multi-position display_position ("WR,TE"): take the first token before the comma for position matching and edge grouping.

7. sort=AD regression trap: if someone changes the endpoint to sort=AD, Yahoo returns HTTP 200 with rows in player_id order — not ADP order — while appearing correct. Add an assertion in the runner after client-side sort: page 0 (OR top 25) must contain >= 20 rows with percent_drafted >= 0.95 when sampled mid-season or in draft window. Fail loudly if not, rather than silently ingesting junk rankings.

8. adp_value > 0 DB constraint: existing check constraint on adp_records. Do not attempt to insert zero or negative values; skip or log.

9. Prerequisites & blockers¶

These two issues must be addressed before this feature can be considered production-reliable. They are not blockers to building the overlay, but they will cause silent failures if unresolved.

BLOCKER 1 — player-reconciliation cron failure (pigskin#323) [HIGH]¶

Status: failing as of 2026-07-25. Evidence: K8s CronJob player-reconciliation (k8s/player-reconciliation-cronjob.yaml, schedule 35 11 * * *) has zero rows in cron_runs under any reconcile-like job_name. The job is dying before its heartbeat write. Jeremiyah Love | RB | ARI has a synthetic gsis_id (LOV121782), has pigpro_score=7.29 in players, but is absent from the latest pigpro_score_history snapshot — a top-30 Yahoo ADP pick invisible on the draft board.

Why it matters for this feature: the overlay's edge scores are only as complete as the board. Unreconciled players produce edge=null + a "not on board" flag in the UI (by design — see Edge Case 3 above), but the root cause is #323, not the overlay. Fix #323 first or in parallel.

Required before merge: confirm player-reconciliation CronJob completes at least one successful run (heartbeat row appears in cron_runs). Verify Jeremiyah Love appears in pigpro_score_history with a non-synthetic resolution path.

BLOCKER 2 — yahoo_api_call_log observability gap [MEDIUM]¶

Status: yahoo_api_call_log has 0 rows in the last 24h as of 2026-07-25, despite league-sync-draft-watch running hourly and ~17 analysis calls having been made earlier that day. _log_db_call in data_sources/yahoo_connector.py is not recording.

Why it matters: without call log data, there is no way to know how close a session is to the app-level throttle threshold (observed on 2026-07-24: sustained calling → 403s on all calls for ~4–5h). The daily 8-call ADP refresh is safe in isolation, but if the cron fires while another Yahoo-heavy process is running, there is no instrumentation to detect or back off.

Required before merge: fix _log_db_call so calls are recorded. Verify by running the ADP runner manually and confirming rows appear in yahoo_api_call_log.

Auth dependency¶

The app-level Yahoo token (fantasy-yahoo-secret, ns fantasyfootball) is dead. The ADP runner must use _get_user_connector with a user token that has on_token_refresh wired. See runners/yahoo_draft_sync.py for the working pattern. Do not reseed the app-level token solely for this feature — use the user-token path.

10. Acceptance criteria¶

The following criteria are directly derived from verified findings and are testable against the live system.

1. Pull coverage: a full ADP refresh run (start=0 through start=175, 8 pages, count=25, sort=OR) completes and inserts >= 140 rows into adp_records WHERE source='yahoo'. (149/149 parseable rows in the 2026-07-25 pull; 147/149 joined successfully.)

2. Call count: the runner makes exactly 8 Yahoo API calls per scheduled refresh for the top-200 pull. Assert in tests and confirm via yahoo_api_call_log post-fix.

3. Sort=OR guard assertion: after client-side sort by average_pick, page 0 (OR top 25) must contain >= 20 rows with percent_drafted >= 0.95. Runner fails loudly (non-zero exit, logged error) rather than silently ingesting if assertion fails. (Guards against sort=AD silent-no-op regression — Edge Case 7.)

4. Jeremiyah Love renders with edge: Love (Yahoo ADP ~26.6 at snapshot time, synthetic gsis_id) appears on the Value Map and War Room board with an edge badge (or an explicit "not on board" flag if #323 is still unresolved). He must not be silently dropped.

5. BUY gate fires: Tyler Shough, Isaiah Likely, Jake Ferguson, Bo Nix — all identified as below-replacement (VOR <= 0) on 2026-07-25 — produce no BUY badge despite having positive edge_rank.

6. scoring_format='global' in DB: SELECT DISTINCT scoring_format FROM adp_records WHERE source='yahoo' returns exactly {'global'}. No 'half', 'ppr', or 'standard' rows for source yahoo.

7. player_sources populated: after a successful run, SELECT count(*) FROM player_sources WHERE source='yahoo' is > 100.

8. Stale-player gate: Tyreek Hill (LAC, stale row with no current-season projection in pigpro_score_history) does not receive a BUY badge in the Value Map even if edge_rank > 0.

9. Low-confidence ADP flag: players with percent_drafted < 0.05 render with a visual qualifier in the UI (lighter weight, asterisk, or tooltip note). At least 5 such players in the top-200 pull (7 observed on 2026-07-25).

10. yahoo_api_call_log populated: after fixing _log_db_call, a manual test run of the ADP runner produces >= 8 rows in yahoo_api_call_log within the session window.

11. Edge directional sanity check (manual QA): on the Value Map, at least 3 of the top 5 BUY badges are TE or RB, and at least 3 of the top 5 FADE badges are WR. This reflects the structural PPR vs. half-PPR market dislocation confirmed in the 2026-07-25 sample.

11. Out of scope / open questions¶

Out of scope for this spec:

• Replacing _adp_from_v1 within the PigPro adp_value_score component (see Section 7c — decision required from Tony first).
• Live ADP polling during the draft. War Room picks come from yahoo-sync; ADP is a pre-draft prior only.
• Yahoo ADP for non-Yahoo leagues (the overlay applies only when user_leagues.platform='yahoo' or as a general market-signal view — scope TBD with Tony).
• Reseeding the dead app-level Yahoo token. Use user-token path.
• preseason_average_pick display (data is available in draft_analysis; decision whether to surface it alongside in-season ADP is deferred).

Open questions requiring Tony's decision before implementation:

1. Should Yahoo ADP feed into the adp_value_score component of PigPro (replacing or blending with FFC/Sleeper), or should it remain a UI-only overlay without touching the formula weights? (See Section 7c.)
2. Is the overlay global (any league, using Yahoo ADP as a general market benchmark) or scoped to active Yahoo-platform leagues only?
3. percent_drafted < 0.05 low-confidence threshold — confirm 5% or adjust.
4. Draft-week 2x/day schedule — confirm second fire time (18:00 CT proposed) or adjust.
5. Once #323 is fixed: should yahoo_adp_unresolved staging rows be auto-retried on the next reconciliation pass, or require manual review?