---
title: Scope the team forecast join to one league and season
type: task
status: planned
description: >-
  The teams route picks each team's odds from a max(generated_at) subquery with no lid filter and a
  join that re-filters neither season_year nor week, so a multi-row match is resolved arbitrarily by
  a JavaScript find().
base_uri: user:task/league/scope-the-team-forecast-join-to-one-league-and-season.md
created_at: '2026-09-15T20:31:57.721Z'
entity_id: 0749b8e0-44de-4944-9dcb-9f7fb5a8adbd
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-15T20:31:57.721Z'
visibility_analyzed_at: '2026-11-06T00:18:04.799Z'
---

## Problem

The teams route builds each team's odds from a latest-forecast subquery that is under-constrained in three ways (`api/routes/leagues/teams.mjs`, the `sub_query` and its `innerJoin`):

- the subquery groups `max(generated_at)` by `tid` with **no `lid` filter**, so it scans every league's forecasts to answer about one league's teams
- the join back to `████████████████████` re-filters neither `season_year` nor `week`, matching on `teamid = tid AND generated_at = maxtime` alone
- `████████████████████` is keyed `(tid, season_year, week, day)`, so several rows per team per season are normal, and the route resolves a multi-row match with a JavaScript `forecasts.find(...)` — first row wins, with no `ORDER BY` behind it

Currently harmless, and measured so rather than assumed: against the production database on 2026-09-15 the query returns exactly one row per `tid` for season 2026, because a simulation run stamps one `generated_at` across the teams it writes. The defect is that nothing enforces that — a second run writing a different `week` under the same timestamp, or a season sharing a timestamp with another, silently selects an arbitrary forecast row and the route reports it as the current odds.

Found while diagnosing the league home odds table showing 0.0%, which turned out to be a separate client-side defect (fixed in `03523f832`). This half was deliberately not bundled into that fix.

## Plan

- [ ] Scope the subquery to the league: add `lid` to the `where` alongside `season_year`
- [ ] Re-filter the join back on `season_year` (and `lid`), so a timestamp collision across seasons cannot cross-match
- [ ] Decide what "latest" means when a season holds several weeks at one `generated_at` — most likely `week` should join on the forecast week the route is asking about rather than being left free
- [ ] Replace the `.find()` resolution with a query that cannot return more than one row per team, so the ambiguity fails loudly instead of resolving arbitrarily
- [ ] Assert the shape in a spec: two forecast rows for one team at the same `generated_at`, and the route returns the right one rather than whichever the planner emits first