Admin: Managing the 20 Competing Teams Each Season

Premier League Predictor: Astro

Chapter 3 · Admin: Managing the 20 Competing Teams Each Season

Chapter 2 built the schema; this chapter turns seasons and season_teams into real, working Astro API routes — the actual admin tooling used to set up a new season and manage which 20 teams are competing in it.

A Small JSON Response Helper, Reused From Here On

Every route from this chapter onward needs to return JSON with the right Content-Type header and status code. Rather than repeating that boilerplate in every single route, one small helper handles it once:

// src/lib/http.ts
export function json(data: unknown, status = 200): Response {
  return new Response(JSON.stringify(data), {
    status,
    headers: { 'Content-Type': 'application/json' },
  });
}

Bootstrapping a Season

// src/pages/api/seasons/index.ts
import type { APIRoute } from 'astro';
import { db } from '../../../lib/db';
import { json } from '../../../lib/http';

export const GET: APIRoute = async () => {
  const seasons = db.prepare('SELECT * FROM seasons ORDER BY start_date DESC').all();
  return json(seasons);
};

export const POST: APIRoute = async ({ request }) => {
  const { name, start_date, end_date, is_current = false } = await request.json();

  if (is_current) {
    // Only one season is ever "current" — clear the flag on every other one first
    db.prepare('UPDATE seasons SET is_current = 0').run();
  }

  const result = db.prepare(
    'INSERT INTO seasons (name, start_date, end_date, is_current) VALUES (?, ?, ?, ?)'
  ).run(name, start_date, end_date, is_current ? 1 : 0);

  const season = db.prepare('SELECT * FROM seasons WHERE id = ?').get(result.lastInsertRowid);
  return json(season, 201);
};

Another cross-row rule, enforced the same way Chapter 2 already established

"At most one season is marked current" spans every row in seasons, not just the one being inserted — the same category of rule as Chapter 2's own "no team twice in a gameweek," which a single-row constraint can't express on its own. The plain UPDATE seasons SET is_current = 0 above clears every other season's flag before the new one is even inserted — a deliberate, explicit app-level guarantee rather than something left to chance.

Adding a Team to a Season: Reuse or Create?

A promoted club might genuinely be a completely new name to this app, or it might be a club that was in the Premier League three seasons ago, got relegated, and is only now coming back up. Chapter 2's own design decision — teams rows persist forever, independent of any single season — means this route has to check which case it's actually in before deciding what to do:

// src/pages/api/seasons/[seasonId]/teams/index.ts
import type { APIRoute } from 'astro';
import { db } from '../../../../../lib/db';
import { json } from '../../../../../lib/http';

const MAX_TEAMS_PER_SEASON = 20;

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

  const season = db.prepare('SELECT * FROM seasons WHERE id = ?').get(seasonId);
  if (!season) {
    return json({ error: 'Season not found' }, 404);
  }

  const addTeam = db.transaction(() => {
    const { count } = db.prepare(
      'SELECT COUNT(*) AS count FROM season_teams WHERE season_id = ?'
    ).get(seasonId) as { count: number };

    if (count >= MAX_TEAMS_PER_SEASON) {
      throw new Error('SEASON_FULL');
    }

    // Reuse the historical team row if this club has been tracked before
    let team = db.prepare('SELECT * FROM teams WHERE name = ?').get(team_name) as
      { id: number; name: string; short_name: string } | undefined;

    if (!team) {
      const result = db.prepare(
        'INSERT INTO teams (name, short_name) VALUES (?, ?)'
      ).run(team_name, short_name ?? team_name);
      team = db.prepare('SELECT * FROM teams WHERE id = ?').get(result.lastInsertRowid) as typeof team;
    }

    const existing = db.prepare(
      'SELECT * FROM season_teams WHERE season_id = ? AND team_id = ?'
    ).get(seasonId, team!.id);

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

    db.prepare(
      'INSERT INTO season_teams (season_id, team_id) VALUES (?, ?)'
    ).run(seasonId, team!.id);

    return team;
  });

  try {
    const team = addTeam();
    return json(team, 201);
  } catch (err) {
    if ((err as Error).message === 'SEASON_FULL') {
      return json({ error: `Season already has ${MAX_TEAMS_PER_SEASON} teams` }, 400);
    }
    if ((err as Error).message === 'ALREADY_IN_SEASON') {
      return json({ error: 'Team is already in this season' }, 409);
    }
    throw err;
  }
};

db.transaction() replaces flush()-then-commit() here — a genuinely different mechanism for the same guarantee

The FastAPI & PostgreSQL course uses db.flush() to get a new Team row's id assigned without ending the SQLAlchemy session, so it can build the related SeasonTeam row before the final commit() — both rows join or fail together as one real transaction. This stack has no session lifecycle to manage at all, so it reaches the same all-or-nothing guarantee differently: db.transaction(fn) wraps the entire inner function — the count check, the team lookup-or-create, the duplicate check, and the final insert — as one real SQLite transaction. If fn returns normally, everything inside it commits together; if it throws anywhere, SQLite automatically rolls back everything it did, and nothing partial is ever left behind. Same real atomicity, reached through wrapping a whole function rather than manually staging a flush partway through a longer-lived session.

Astro has no automatic exception-to-JSON translation, unlike FastAPI's HTTPException

FastAPI's own HTTPException is caught by the framework itself and turned into a proper JSON error response with the right status code — raising it is enough. Astro's API routes have no equivalent built in: an uncaught thrown error inside a route handler becomes a generic, unstyled 500 response with no useful JSON body at all. That's exactly why the errors above are thrown as plain Error objects carrying a short string code ('SEASON_FULL', 'ALREADY_IN_SEASON') inside the transaction, then caught and explicitly translated into a real json({error: ...}, status) response outside it. Every route in this course follows the same shape: known failure cases are caught and turned into a deliberate response; anything genuinely unexpected is re-thrown and allowed to become a 500, since there's nothing sensible to say about it anyway.

No refresh() needed — the just-inserted row is simply read back

SQLAlchemy's db.refresh(db_season) re-populates a Python object's fields from the database after an insert. better-sqlite3 has no such object to refresh in the first place — result.lastInsertRowid gives the new row's real id directly off the .run() result, and a plain follow-up SELECT ... WHERE id = ? reads the row back exactly as it was actually stored, including any column defaults SQLite itself applied.

Real names must match exactly, and there's no fuzzy matching

WHERE name = ? only reuses an existing team if the name matches character-for-character. Typing "Nottingham Forest" one season and "Nott'm Forest" the next creates a genuine duplicate teams row rather than reusing the real one — this route trusts the admin to type the name consistently rather than trying to guess a match. A dropdown of already-known team names (built from Chapter 4's own "20 clickable team buttons" pattern) would be a real, worthwhile fix, but isn't built in this chapter.

Removing a Team From a Season

// src/pages/api/seasons/[seasonId]/teams/[teamId].ts
import type { APIRoute } from 'astro';
import { db } from '../../../../../lib/db';
import { json } from '../../../../../lib/http';

export const DELETE: APIRoute = async ({ params }) => {
  const seasonId = Number(params.seasonId);
  const teamId = Number(params.teamId);

  const result = db.prepare(
    'DELETE FROM season_teams WHERE season_id = ? AND team_id = ?'
  ).run(seasonId, teamId);

  if (result.changes === 0) {
    return json({ error: 'Team is not in this season' }, 404);
  }

  return new Response(null, { status: 204 });
};

.changes stands in for a separate existence check

The FastAPI sibling has to SELECT the SeasonTeam row first specifically to know whether it existed at all, before deciding whether to delete it or return a 404. better-sqlite3's own .run() result reports changes — the real number of rows the statement actually affected — so a single DELETE already answers both questions at once: if changes is 0, nothing matched, and the 404 is returned with no separate lookup query needed first.

This deletes the season_teams row, not the team itself — exactly the point of splitting the two tables back in Chapter 2. A relegated club's own historical teams row is untouched; only the fact "competing in this particular season" goes away.

Listing a Season's Teams

// src/pages/api/seasons/[seasonId]/teams/index.ts (GET, added alongside POST above)
export const GET: APIRoute = async ({ params }) => {
  const seasonId = Number(params.seasonId);
  const teams = db.prepare(`
    SELECT teams.id, teams.name, teams.short_name
    FROM season_teams
    JOIN teams ON teams.id = season_teams.team_id
    WHERE season_teams.season_id = ?
    ORDER BY teams.name
  `).all(seasonId);

  return json(teams);
};

No nested-model serialization step exists to compare against

The FastAPI sibling needs a nested SeasonTeamResponse.team: TeamResponse Pydantic model to shape a joined query result into the right JSON structure. There's no equivalent step here at all — the raw SQL JOIN above already selects exactly the columns the response needs, in exactly the shape it needs them, and db.prepare(...).all() hands back plain JavaScript objects that json() serializes directly. Nothing is being reshaped between the query and the response, because the query was written to already produce the response's own shape.

These routes have no access control

Every route in this chapter is reachable by anyone who can reach the deployed site — there's no login, no admin check, nothing gating who can add or remove a team. That's a deliberate scope decision for a personal, single-operator tool, not an oversight: this course has no dedicated authentication chapter, since the real, honest question of whether this app is ever meant to be used by more than one person is still genuinely open.

Where This Course Is Headed

The fast click-to-pair fixture-entry UI, built directly on top of this chapter's own GET /api/seasons/{seasonId}/teams route to populate its 20 clickable team buttons (Chapter 4); recording predictions per fixture (Chapter 5); entering results (Chapter 6); both league tables (Chapters 7-8); and promotion/relegation, which reuses this chapter's own add/remove routes directly at the season boundary (Chapter 9).

Hands-On Exercises

Exercise 1

Explain why the POST /api/seasons route clears is_current on every other season with a plain UPDATE before inserting the new one, and what would go wrong if that step were skipped.

📄 View solution

Exercise 2

Explain why the add-team route looks up an existing team by name before creating a new one, and describe a real scenario where skipping that lookup would create a duplicate teams row for the same real club.

📄 View solution

Exercise 3

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.

📄 View solution

Chapter 3 Quick Reference

json() helper — one small function wrapping every route's own JSON response and status code, reused from here on
POST /api/seasons — creates a season; clears is_current on every other season first if the new one is marked current
POST /api/seasons/{id}/teams — reuses an existing team by name if one matches, otherwise creates one; enforces a 20-team cap via a COUNT check, all wrapped in db.transaction()
db.transaction() vs. flush()/commit() — a whole function committed or rolled back together, replacing SQLAlchemy's own mid-session flush pattern
No HTTPException equivalent — known errors are thrown as plain Error objects inside the transaction, then explicitly caught and translated into json({error}, status) responses, since an uncaught error would otherwise become a bare 500
DELETE /api/seasons/{id}/teams/{teamId} — removes the season_teams row only; result.changes === 0 replaces a separate existence-check query
GET /api/seasons/{id}/teams — a real JOIN, already shaped to match the response with no serialization step needed
Real limit — team-name matching is exact, no fuzzy matching; no access control on any route in this chapter
Next chapter: The fast click-to-pair fixture-entry UI