Premier League Predictor: Astro — Chapter 3, Exercise 3
====================================================

TASK
Explain why the team-lookup-or-create step and the season_teams
insert are both wrapped inside a single db.transaction() call rather
than run as two separate, un-wrapped statements, and what real
guarantee would be lost if the wrapper were removed.

SOLUTION

Adding a team to a season is really two separate writes that both
need to succeed together: (1) possibly inserting a brand-new teams
row, if the club has never been tracked before, and (2) inserting the
season_teams row that actually links that team to the current season.
Between those two writes, the route also does two checks — the
20-team cap and the duplicate-membership check — either of which can
fail and needs to stop the whole operation.

Without db.transaction() wrapping all of this, each individual
db.prepare(...).run() call commits its own effect to the database the
instant it executes, completely independently of whatever runs next.
If the code created a new teams row, then the duplicate-membership
check happened to throw (or the season_teams insert itself failed for
some other reason), the new teams row would already be permanently
saved in the database — a real, orphaned team with no season_teams
row pointing to it at all, created by a request that, from the API
caller's point of view, ultimately failed.

Wrapping the whole sequence in db.transaction(() => { ... }) makes it
genuinely all-or-nothing: SQLite only commits any of the changes made
inside the wrapped function once that function returns normally. If
it throws at any point — the season-full check, the duplicate check,
or anything else — every write already made inside that same call is
rolled back automatically, as if none of it had ever run. The route's
own outer try/catch only has to worry about turning a caught error
into the right JSON response; it doesn't have to manually undo any
half-finished database writes, because SQLite already guarantees none
were kept in the first place.

WHY THIS WORKS AS AN ANSWER
----------------------------
It identifies the real two-write, two-check sequence that needs
atomicity, describes the concrete orphaned-row failure mode that
would result from removing the transaction wrapper, and explains the
actual mechanism (automatic rollback on a thrown error) that
db.transaction() provides instead.