Promotion & Relegation: Auto-Calculating the Bottom Three, Manually Entering the Promoted Three

Premier League Predictor: Astro

Chapter 9 · Promotion & Relegation: Auto-Calculating the Bottom Three, Manually Entering the Promoted Three

A real Premier League season ends with three of its 20 teams relegated and three new ones promoted up from the Championship. Because season_teams is scoped per season (Chapter 2), "next season's 20 teams" is really just a brand-new set of season_teams rows — this chapter is about deciding, and inserting, exactly which team_ids those rows should point at.

The Bottom Three Are Auto-Calculated — No Refactor Required

Both sibling courses reach this chapter needing to first refactor their own Chapter 7 league-table logic out of an inline route and into a separate, reusable function before they can call it a second time here. This course never has that step to do: Chapter 7's own getLeagueTable() was written into src/lib/leagueTable.ts from the very start, with a tip-box at the time explicitly naming this exact reuse as the reason. Finding the real bottom three is nothing more than calling the same function again, now with a different question in mind:

// src/lib/rollover.ts
import { db } from './db';
import { getLeagueTable, type LeagueTableRow } from './leagueTable';

const TEAMS_PER_SEASON = 20;
const RELEGATED_COUNT = 3;

export function getRelegated(seasonId: number): LeagueTableRow[] {
  const table = getLeagueTable(seasonId);
  return table.slice(-RELEGATED_COUNT);
}

export function getSurvivors(seasonId: number): LeagueTableRow[] {
  const table = getLeagueTable(seasonId);
  return table.slice(0, -RELEGATED_COUNT);
}

slice(-3) and slice(0, -3) always mean "the last three" and "everything but the last three"

Because getLeagueTable() already returns its rows sorted ORDER BY points DESC, goal_difference DESC, goals_for DESC, the worst-performing team is always the very last element of the returned array, regardless of how many teams are actually in it. Array.prototype.slice(-3) reads as "the last three elements" and slice(0, -3) as "every element except the last three" — for a genuine 20-team table, that's positions 18-20 and positions 1-17 respectively, with no index arithmetic to get wrong by hand.

This assumes the season actually finished with all 20 teams intact

Nothing in getRelegated() or getSurvivors() checks that a season is actually over, or that every one of its 20 teams played a full 38-gameweek schedule. The rollover route below adds exactly one real safeguard — checking the table has exactly 20 rows before proceeding — but doesn't verify anything about gameweeks played. Calling this mid-season would return a real, well-formed answer to the wrong question, not an error.

Rolling the Survivors Forward: An Ordinary Loop, Not a Bulk Insert

The Django & MySQL sibling carries its own 17 survivors forward with a real bulk_create() call, wrapped in transaction.atomic(). better-sqlite3 has no equivalent bulk-insert API at all — there's no method that accepts an array of rows and inserts them all in one call. The identical atomicity guarantee is reached the same way every other multi-write operation in this course has reached it since Chapter 3: wrap a plain, ordinary for loop of individual .run() calls in db.transaction().

// src/pages/api/seasons/[seasonId]/rollover.ts
import type { APIRoute } from 'astro';
import { db } from '../../../../lib/db';
import { json } from '../../../../lib/http';
import { getLeagueTable, getRelegated, getSurvivors } from '../../../../lib/rollover';

export const POST: APIRoute = async ({ params, request }) => {
  const oldSeasonId = Number(params.seasonId);
  const { new_season_id } = await request.json();

  const oldSeason = db.prepare('SELECT * FROM seasons WHERE id = ?').get(oldSeasonId);
  const newSeason = db.prepare('SELECT * FROM seasons WHERE id = ?').get(new_season_id);
  if (!oldSeason || !newSeason) {
    return json({ error: 'Old or new season not found' }, 404);
  }

  const table = getLeagueTable(oldSeasonId);
  if (table.length !== 20) {
    return json(
      { error: `Expected 20 teams in the old season's table, found ${table.length}` },
      400
    );
  }

  const survivors = getSurvivors(oldSeasonId);
  const relegated = getRelegated(oldSeasonId);

  const rollover = db.transaction(() => {
    for (const team of survivors) {
      const existing = db.prepare(
        'SELECT * FROM season_teams WHERE season_id = ? AND team_id = ?'
      ).get(new_season_id, team.team_id);

      if (existing) {
        throw new Error('ALREADY_ROLLED_OVER');
      }

      db.prepare(
        'INSERT INTO season_teams (season_id, team_id) VALUES (?, ?)'
      ).run(new_season_id, team.team_id);
    }
  });

  try {
    rollover();
  } catch (err) {
    if ((err as Error).message === 'ALREADY_ROLLED_OVER') {
      return json(
        { error: 'One or more surviving teams are already in the new season — rollover may already have run' },
        409
      );
    }
    throw err;
  }

  return json(
    {
      survivors: survivors.map((t) => t.name),
      relegated: relegated.map((t) => t.name),
    },
    201
  );
};

Same atomicity, reached by wrapping a loop instead of calling a dedicated bulk API

17 separate INSERT statements run inside db.transaction() either all commit together or, if any one of them throws — including the deliberate 'ALREADY_ROLLED_OVER' guard above — none of them do. That's the identical all-or-nothing guarantee bulk_create() gives the Django sibling, reached through a genuinely different, plainer mechanism: there's no special bulk-insert code path here at all, just the same db.transaction(fn) wrapper this course has reused since Chapter 3, wrapped around an ordinary loop instead of a single statement.

The double-rollover guard checks one row at a time, on purpose

Checking each survivor individually for an existing season_teams row — rather than a single coarse check for "does the new season have any rows in it at all" — matters for a genuinely realistic reason: an admin might reasonably add one or more of the three promoted teams via Chapter 3's own route before ever calling rollover, simply because they already know which clubs came up. A blanket "any row exists yet" check would see those promoted-team rows and refuse to run the rollover at all, even though none of them collide with any of the 17 survivor team_ids the loop is actually about to insert. Checking each survivor specifically means the promoted teams' own already-added rows are simply irrelevant to this check — only a genuine duplicate among the 17 survivors themselves ever trips the guard.

A genuine partial failure never needs this guard at all

Because the entire loop runs inside one db.transaction(), a real failure partway through — a crash, an unrelated thrown error — rolls back every insert that transaction attempted, leaving the new season's own survivor rows exactly as empty as before the call started. A retry after that kind of failure simply reinserts all 17 from scratch with no conflicts to detect; the per-team check above isn't a resume mechanism for a half-finished transaction, since the transaction wrapper already guarantees there's no such thing as "half-finished" to resume.

The Three Promoted Teams: Reusing Chapter 3's Own Route, Not Rebuilding It

A promoted club needs exactly the same handling Chapter 3 already built: check whether this club has a historical teams row already (a team coming back up after a previous relegation) or genuinely needs a new one, then add a season_teams row for the new season. That's precisely what POST /api/seasons/{seasonId}/teams already does — so rather than building a second, parallel route here that duplicates that exact reuse-or-create logic, the three promoted teams are simply added to the new season with three ordinary calls to the route Chapter 3 already wrote:

// Three plain calls to Chapter 3's own route — no new endpoint needed
await fetch(`/api/seasons/${newSeasonId}/teams`, {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ team_name: 'Leeds United', short_name: 'Leeds' }),
});
// ...and again for the second and third promoted clubs

A deliberate architectural choice, not an oversight

Which three teams actually get promoted is a genuine, real-world piece of knowledge — it's decided by the Championship's own final table, something no query against this app's own database could ever determine, since this app doesn't track any division below the Premier League. That's exactly the kind of decision Chapter 3's route already exists to handle by hand: an admin who knows the real, current promoted clubs types their names in, the route reuses or creates their teams row as appropriate, and adds them to the new season. The rollover route above only automates the part that genuinely is mechanical — carrying forward the 17 teams this app's own data already knows survived.

Relegating a Team Deletes Nothing

It's worth being precise about what "relegated" actually means here, since nothing in this chapter runs a single DELETE statement. The old season's own season_teams rows — including the three relegated teams' own rows — are never touched by any of the code above. Relegation is really just an omission: the rollover loop only ever inserts rows for the 17 survivors, so a relegated team simply never gets a row in the new season's own season_teams at all. Its historical membership in every season it actually played in, including the one it just got relegated from, stays exactly as recorded, permanently — the same "teams persist forever, season_teams tracks membership per season" split Chapter 2 established from the very beginning of this course.

A Read-Only Relegation Preview

Deciding to run the rollover route is a real, one-way step for a season — a lightweight, read-only route lets an admin check who's actually getting relegated first, with no risk of accidentally triggering anything:

// src/pages/api/seasons/[seasonId]/relegation-preview.ts
import type { APIRoute } from 'astro';
import { getRelegated, getSurvivors } from '../../../../lib/rollover';
import { json } from '../../../../lib/http';

export const GET: APIRoute = async ({ params }) => {
  const seasonId = Number(params.seasonId);
  return json({
    relegated: getRelegated(seasonId),
    survivors: getSurvivors(seasonId),
  });
};

Where This Course Is Headed

Astro Islands and interactivity — replacing the hardcoded seasonId/gameweekId/fixtureId values every chapter since Chapter 4 has deliberately left in place with a real, working selector (Chapter 10); deployment (Chapter 11); and the capstone, mounting this app directly onto the real, live Astro site (Chapter 12).

Hands-On Exercises

Exercise 1

Explain why this chapter needs no refactoring step to reuse Chapter 7's own league-table logic, and contrast that directly with what the FastAPI and Django sibling courses had to do at this exact point in their own Chapter 9s.

📄 View solution

Exercise 2

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.

📄 View solution

Exercise 3

Set up a complete 20-team season with a full league table, call GET /api/seasons/{seasonId}/relegation-preview to confirm the correct bottom three, then call POST /api/seasons/{seasonId}/rollover against a newly created next season and confirm the new season's season_teams contains exactly the 17 survivors — followed by three calls to Chapter 3's own POST /api/seasons/{id}/teams route to add three promoted clubs, bringing the new season back up to 20.

📄 View solution

Chapter 9 Quick Reference

No refactor needed — getLeagueTable() has lived in its own module since Chapter 7 specifically for this reuse, unlike both sibling courses' own inline-query-to-function refactor at this point
getRelegated()/getSurvivors() — table.slice(-3) and table.slice(0, -3) against the already-sorted league table; correct regardless of table size
POST /api/seasons/{id}/rollover — verifies 20 teams exist, then inserts the 17 survivors' own season_teams rows for the new season, wrapped in db.transaction()
Plain loop, not bulk_create() — better-sqlite3 has no bulk-insert API; the identical atomicity guarantee comes from wrapping an ordinary for loop in the same db.transaction() this course has used since Chapter 3
Double-rollover guard checks per-team — a coarse "does the new season have any rows yet" check would wrongly block a legitimate rollover after promoted teams were already added via Chapter 3's own route; a genuine partial failure needs no such guard at all, since db.transaction() never leaves a half-finished result to resume
Promoted teams reuse Chapter 3's own route — no second add-team endpoint is built; which clubs are promoted is real-world knowledge no query here could ever determine
Relegation deletes nothing — the old season's own season_teams rows are untouched forever; a relegated team simply never gets a row in the new season
GET /api/seasons/{id}/relegation-preview — a read-only check before the one-way rollover step is actually triggered
Next chapter: Astro Islands & interactivity — a real gameweek/season selector, replacing every hardcoded ID since Chapter 4