Phase 3 — `Game.week_id` foreign key #409

New issue

Closed

opened 2026-08-21 17:39:57 +02:00 by johnsturgeon · 0 comments

johnsturgeon commented

2026-08-21 17:39:57 +02:00

Owner

Parent: #406 · Tech debt: Game has no week_id back-pointer

Revised 2026-08-22. The week-picker filter changes from game_state to
odds_state (added in #408), and one task moves out to #414.

Why

Week is authoritative for what week it is, but nothing references it. Game
still carries season / season_type / week_no as three loose columns, so a
game agrees with its week only by convention, and every week-scoped query
hand-assembles a three-column WHERE.

This gets materially harder the longer it waits. game,
playergamepick, and playeraward are all empty in production right now, so
the column can be created NOT NULL in a single revision with no
nullable-then-backfill-then-alter dance. The first time prod holds real games,
this becomes a three-step migration against live data.

Needs Phase 1 (a Week row must exist for create_the_picks to point at).

Tasks

Add Game.week_id as a NOT NULL FK plus migration. One revision,
no backfill, while the tables are still empty.
create_the_picks sets week_id via get-or-create on Week, the
same way it already resolves teams.
Switch Game.games_for_week and Game.get_first_game_of_the_week
to filter on week_id.
Switch PlayerGamePick.find_picks_for_week and
upsert_award_with_args to the FK. Keep this separate from the task
above — different tables, different blast radius.
~~Game.current_season_distinct_week_infos queries week directly.~~
~~The DISTINCT over game disappears entirely.~~
Moved to #414, which is the same change described twice. Do it
there — it also has to move the method onto Week, and it depends on
odds_state existing, which this issue does not.

Watch out

~~If week rows exist for weeks that have no games yet, the week picker will~~
~~render links to empty pages. Filter on game_state != PENDING — that is what~~
~~the Phase 2 column is for, and it is why no separate picks_created_at column~~
~~is needed.~~

If week rows exist for weeks whose picks page has not been released, the week
picker will render links to pages nobody should see yet. Filter on
odds_state == LOCKED.

The struck version had this backwards, and the reasoning is worth keeping
visible because it is the same mistake that shaped create_the_picks:
game_state != PENDING asks "do game rows exist", when the question is "is
this week viewable". Those coincide today only because the picks page is a
side effect of rows being inserted — which is the accident this epic is
unwinding, not a property to build on. odds_state is the released flag, and
it is why a separate picks_created_at column is still not needed.

The DISTINCT over game is currently serving as this filter by accident,
which is why nothing has gone wrong yet. Removing it (in #414) without
odds_state in place is what would break the picker.

Done when

game.week_id is NOT NULL with no orphans.
No query in app/models filters on the season / season_type / week_no
triple to identify a week.
~~The week picker renders from week alone.~~ The week picker renders from
week alone, filtered on odds_state == LOCKED — see #414.

Parent: #406 · Tech debt: *`Game` has no `week_id` back-pointer* > **Revised 2026-08-22.** The week-picker filter changes from `game_state` to > `odds_state` (added in #408), and one task moves out to #414. ## Why `Week` is authoritative for what week it is, but nothing references it. `Game` still carries `season` / `season_type` / `week_no` as three loose columns, so a game agrees with its week only by convention, and every week-scoped query hand-assembles a three-column `WHERE`. **This gets materially harder the longer it waits.** `game`, `playergamepick`, and `playeraward` are all empty in production right now, so the column can be created NOT NULL in a single revision with no nullable-then-backfill-then-alter dance. The first time prod holds real games, this becomes a three-step migration against live data. Needs Phase 1 (a `Week` row must exist for `create_the_picks` to point at). ## Tasks - [x] **Add `Game.week_id` as a NOT NULL FK** plus migration. One revision, no backfill, while the tables are still empty. - [x] **`create_the_picks` sets `week_id`** via get-or-create on `Week`, the same way it already resolves teams. - [x] **Switch `Game.games_for_week` and `Game.get_first_game_of_the_week`** to filter on `week_id`. - [x] **Switch `PlayerGamePick.find_picks_for_week` and `upsert_award_with_args`** to the FK. Keep this separate from the task above — different tables, different blast radius. - [x] ~~**`Game.current_season_distinct_week_infos` queries `week` directly.**~~ ~~The `DISTINCT` over `game` disappears entirely.~~ **Moved to #414**, which is the same change described twice. Do it there — it also has to move the method onto `Week`, and it depends on `odds_state` existing, which this issue does not. ## Watch out ~~If week rows exist for weeks that have no games yet, the week picker will~~ ~~render links to empty pages. Filter on `game_state != PENDING` — that is what~~ ~~the Phase 2 column is for, and it is why no separate `picks_created_at` column~~ ~~is needed.~~ If week rows exist for weeks whose picks page has not been released, the week picker will render links to pages nobody should see yet. **Filter on `odds_state == LOCKED`.** The struck version had this backwards, and the reasoning is worth keeping visible because it is the same mistake that shaped `create_the_picks`: `game_state != PENDING` asks "do game rows exist", when the question is "is this week viewable". Those coincide today only because the picks page is a side effect of rows being inserted — which is the accident this epic is unwinding, not a property to build on. `odds_state` is the released flag, and it is why a separate `picks_created_at` column is still not needed. The `DISTINCT` over `game` is currently serving as this filter by accident, which is why nothing has gone wrong yet. Removing it (in #414) without `odds_state` in place is what would break the picker. ## Done when - `game.week_id` is NOT NULL with no orphans. - No query in `app/models` filters on the `season` / `season_type` / `week_no` triple to identify a week. - ~~The week picker renders from `week` alone.~~ The week picker renders from `week` alone, filtered on `odds_state == LOCKED` — see #414.

johnsturgeon added a new dependency

2026-08-21 17:41:38 +02:00

#406 Resolve the technical debt issues that remain for the week table issue

johnsturgeon referenced this issue

2026-08-21 17:42:36 +02:00

Resolve the technical debt issues that remain for the week table issue #406

johnsturgeon referenced this issue

2026-08-22 11:24:24 +02:00

Phase 2 — Week state machine #408

johnsturgeon referenced this issue

2026-08-22 11:24:35 +02:00

Move the method current_season_distinct_week_infos to the 'week' model #414