johnsturgeon/tgfp-web
Watch
1
0
Fork
You've already forked tgfp-web
 0
Code Issues 30 Pull requests Projects 1 Releases 41 Packages 1 Wiki Activity Actions

Migrate from ESPN api to TheSportsDB #363

New issue
Closed
opened 2026-08-12 21:08:29 +02:00 by johnsturgeon · 0 comments
johnsturgeon commented 2026-08-12 21:08:29 +02:00
Owner
Copy link

I have a membership / pay for access so this should be a more 'proper' long term solution

Phase 3 — TheSportsDB cutover checklist

Phase 2 (this issue) is done: schema is provider-neutral and DataSource.THE_SPORTS_DB
exists as an enum value, but nothing reads or writes it yet. Every job still
hardcodes ESPN. This is what's left.

0. Answer first — this gates everything else

  • Does TheSportsDB give us a point spread / favored team? Game.spread is a
    non-nullable float and today it comes from ESPNNflOdd.favored_team_spread.
    Picks, locks and upsets all hang off spread + favorite. If TheSportsDB has no
    betting lines, the cutover is not a straight swap — we either keep ESPN as an
    odds-only source or add a third provider. Settle this before writing any client.
  • Confirm it covers preseason + postseason, not just regular season.
  • Confirm scores update live at a usable polling cadence (we poll every 5 min).

1. Provider client

  • Add app/thesportsdb/ mirroring app/espn_nfl/'s public surface:
    games(), teams(), standings(), find_game(), find_teams(),
    find_standing_for_team().
  • Game objects must expose what _game_from_nfl_game() consumes: id,
    start_time, week_no, season_type, season, game_status_type, spread,
    favored_team, home_team, away_team, total_home_points,
    total_away_points, extra_info.
  • Same retry/backoff behaviour as the ESPN client.

2. Normalize the two ESPN-specific vocabularies

These leak into the database, so they have to be translated at the provider boundary
or existing rows stop matching:

  • Game status. Game.is_final / Game.is_pregame compare against the literal
    strings "STATUS_FINAL" and "STATUS_SCHEDULED". If TheSportsDB writes its own
    vocabulary into game_status, awards and score updates break silently — no
    exception, games just never look final.
  • Season type ints. 1=Pre, 2=Regular, 3=Post is ESPN's numbering, and it's
    stored on Game.season_type and used by WeekInfo. Map to the same ints.
  • Check week numbering matches too.

3. Dispatch instead of hardcoded ESPN

  • Config value for the active provider (env var, per config/op.env).
  • Factory that returns a client for a given DataSource.
  • create_picks._espn_team() — filters on DataSource.ESPN and the new Game
    sets data_source=DataSource.ESPN. Both become the active provider.
  • update_game._update_one_game() — builds ESPNNfl(...) directly; should pick
    the client from game.data_source so historical rows still resolve.
  • sync_team_records() — same, builds ESPNNfl() directly.

4. Team row cutover (Option 1, overwrite the 32 rows)

  • UPDATE in place, keyed by short_name — do not delete and re-insert.
    game.favorite_team_id, road_team_id and home_team_id are FKs to team.id;
    dropping the rows orphans every historical game. Also discord_emoji and
    logo_url are TGFP-owned with no provider equivalent and would be lost.
  • Set external_team_id + data_source together in one transaction.
  • Verify all 32 matched before committing — a missed short_name means a team that
    silently stops updating.

5. Timing

  • Cut over between weeks, after the last game is final. Games already in the
    table carry ESPN ids with data_source='espn', and update_game polls them by
    that id — swapping team ids mid-week strands in-flight games.
  • Leave historical rows at data_source='espn' permanently. That's what the column
    is for; the composite unique constraint is on (data_source, external_*_id), so
    old ESPN rows and new rows coexist without collision.

6. Verify

  • Autogenerate probe reports 0 ops (no schema drift).
  • Dry-run create_the_picks() against a scratch DB and confirm
    teams resolve, spreads are sane, and no .one() raises NoResultFound.
  • Let one week of live scoring run and confirm games reach is_final and awards
    recalculate.
  • pylint 10.00/10.
I have a membership / pay for access so this should be a more 'proper' long term solution ## Phase 3 — TheSportsDB cutover checklist Phase 2 (this issue) is done: schema is provider-neutral and `DataSource.THE_SPORTS_DB` exists as an enum value, but **nothing reads or writes it yet**. Every job still hardcodes ESPN. This is what's left. ### 0. Answer first — this gates everything else - [x] **Does TheSportsDB give us a point spread / favored team?** `Game.spread` is a non-nullable float and today it comes from `ESPNNflOdd.favored_team_spread`. Picks, locks and upsets all hang off spread + favorite. If TheSportsDB has no betting lines, the cutover is *not* a straight swap — we either keep ESPN as an odds-only source or add a third provider. Settle this before writing any client. - [x] Confirm it covers preseason + postseason, not just regular season. - [ ] Confirm scores update live at a usable polling cadence (we poll every 5 min). ### 1. Provider client - [ ] Add `app/thesportsdb/` mirroring `app/espn_nfl/`'s public surface: `games()`, `teams()`, `standings()`, `find_game()`, `find_teams()`, `find_standing_for_team()`. - [ ] Game objects must expose what `_game_from_nfl_game()` consumes: `id`, `start_time`, `week_no`, `season_type`, `season`, `game_status_type`, `spread`, `favored_team`, `home_team`, `away_team`, `total_home_points`, `total_away_points`, `extra_info`. - [ ] Same retry/backoff behaviour as the ESPN client. ### 2. Normalize the two ESPN-specific vocabularies These leak into the database, so they have to be translated at the provider boundary or existing rows stop matching: - [ ] **Game status.** `Game.is_final` / `Game.is_pregame` compare against the literal strings `"STATUS_FINAL"` and `"STATUS_SCHEDULED"`. If TheSportsDB writes its own vocabulary into `game_status`, awards and score updates break *silently* — no exception, games just never look final. - [ ] **Season type ints.** `1=Pre, 2=Regular, 3=Post` is ESPN's numbering, and it's stored on `Game.season_type` and used by `WeekInfo`. Map to the same ints. - [ ] Check week numbering matches too. ### 3. Dispatch instead of hardcoded ESPN - [ ] Config value for the active provider (env var, per `config/op.env`). - [ ] Factory that returns a client for a given `DataSource`. - [ ] `create_picks._espn_team()` — filters on `DataSource.ESPN` and the new `Game` sets `data_source=DataSource.ESPN`. Both become the active provider. - [ ] `update_game._update_one_game()` — builds `ESPNNfl(...)` directly; should pick the client from `game.data_source` so historical rows still resolve. - [ ] `sync_team_records()` — same, builds `ESPNNfl()` directly. ### 4. Team row cutover (Option 1, overwrite the 32 rows) - [ ] **UPDATE in place, keyed by `short_name` — do not delete and re-insert.** `game.favorite_team_id`, `road_team_id` and `home_team_id` are FKs to `team.id`; dropping the rows orphans every historical game. Also `discord_emoji` and `logo_url` are TGFP-owned with no provider equivalent and would be lost. - [ ] Set `external_team_id` + `data_source` together in one transaction. - [ ] Verify all 32 matched before committing — a missed short_name means a team that silently stops updating. ### 5. Timing - [ ] **Cut over between weeks, after the last game is final.** Games already in the table carry ESPN ids with `data_source='espn'`, and `update_game` polls them by that id — swapping team ids mid-week strands in-flight games. - [ ] Leave historical rows at `data_source='espn'` permanently. That's what the column is for; the composite unique constraint is on `(data_source, external_*_id)`, so old ESPN rows and new rows coexist without collision. ### 6. Verify - [ ] Autogenerate probe reports 0 ops (no schema drift). - [ ] Dry-run `create_the_picks()` against a scratch DB and confirm teams resolve, spreads are sane, and no `.one()` raises `NoResultFound`. - [ ] Let one week of live scoring run and confirm games reach `is_final` and awards recalculate. - [ ] pylint 10.00/10.
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
migrate-the-job-schedule-manager-page-over-to-v3-539
2.36.0
2.35.0
2.34.1
2.34.0
2.33.0
2.32.1
2.32.0
2.31.2
2.31.1
2.31.0
2.30.1
2.30.0
v1.4.8
v1.4.7
v1.4.6
v1.4.5
v1.4.4
v1.4.3
v1.4.2
v1.4.1
v1.4.0
v1.3.17
v1.3.16
v1.3.15
v1.3.14
v1.3.13
v1.3.12
v1.3.11
v1.3.10
v1.3.9
v1.3.8
v1.3.7
v1.3.6
v1.3.5
v1.3.4
v1.3.3
v1.3.2
v1.3.1
v1.3.0
v1.2.0
v1.1.6
v1.1.5
v1.1.4
v1.1.3
v1.1.2
v1.1.1
v1.1.0
v1.0.0
Labels
Clear labels   
Kestra

   
bug
Something isn't working

  
enhancement
New feature or request

  
someday
Someday / Maybe

  
subtask

   
☁️ api

   
🎛️ infrastructure

   
🐞 sentry

   
📆 2025 Season
  
📝 pages
allpicks
 Related to the All Picks page

  
📝 pages
picks

  
📝 pages
standings

  
🚀 performance
No labels
Kestra
bug
enhancement
someday
subtask
☁️ api
🎛️ infrastructure
🐞 sentry
📆 2025 Season
📝 pages
allpicks
📝 pages
picks
📝 pages
standings
🚀 performance
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
johnsturgeon/tgfp-web#363
No description provided.