Premier League Predictor: Astro — Chapter 9, Exercise 2 ==================================================== TASK Explain why the rollover route checks each survivor individually for an existing season_teams row inside the loop, rather than checking once, up front, whether the new season already has any season_teams rows at all before starting, and explain separately why a genuine partial-failure retry never needs this guard to "resume" anything. SOLUTION Part 1 — why per-team, not "any rows at all": A single coarse check — "does the new season already have any season_teams rows in it? If so, refuse the whole request" — would misfire against a genuinely realistic real-world workflow. An admin might reasonably add one or more of the three promoted clubs via Chapter 3's own POST /api/seasons/{id}/teams route before ever calling rollover, simply because they already know which teams came up from the Championship and see no reason to wait. Those promoted teams' own season_teams rows for the new season would then already exist at the moment rollover is called — but none of those rows have anything to do with the 17 survivors the rollover loop is actually about to insert; a promoted team's team_id is, by definition, not one of the current season's own top 17. A blanket "any row exists" check would see those unrelated promoted-team rows and incorrectly refuse to run a rollover that hasn't actually happened yet. Checking each survivor specifically, right before its own INSERT, sidesteps that false positive entirely: the promoted teams' own rows are simply never examined by this check at all, since the loop only ever looks up season_teams rows for the 17 specific survivor team_ids it's iterating over. Only a genuine duplicate among those 17 — a survivor that was already rolled over, most likely from an earlier, already-successful call to this exact route — actually trips the guard. Part 2 — why a partial failure needs no resume logic: The entire loop runs inside a single db.transaction() call. If something genuinely unrelated to duplicates goes wrong partway through — a crash, a thrown error from some other part of the app — SQLite rolls back every insert that transaction had attempted, leaving the new season's own survivor rows exactly as empty as they were before the call started. There is no such thing as "half of the 17 survivors got inserted and the other half didn't" surviving past a failed call, because the transaction wrapper guarantees all-or-nothing. A retry after that kind of failure calls rollover() again against a season that genuinely has zero of the 17 survivors' own rows yet, so every one of the per-team existence checks correctly finds nothing and lets all 17 inserts proceed fresh — not because the guard is "resuming" anything, but because there is nothing partial left for it to need to skip over in the first place. WHY THIS WORKS AS AN ANSWER ---------------------------- It gives a concrete, realistic scenario where a coarse whole-season check would produce a false positive (promoted teams already added independently), explains exactly why the per-team check avoids that false positive, and separately and correctly reasons through why db.transaction()'s own atomicity guarantee means a genuine partial failure never actually leaves a half-finished state that would need special resume logic to recover from.