Admin: Managing the 20 Competing Teams Each Season

Premier League Predictor: FastAPI & PostgreSQL

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

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

Bootstrapping a Season

Extending schemas.py with the Season shapes:

# schemas.py (additions)
from datetime import date

class SeasonCreate(BaseModel):
    name: str
    start_date: date
    end_date: date
    is_current: bool = False

class SeasonResponse(BaseModel):
    id: int
    name: str
    start_date: date
    end_date: date
    is_current: bool

    class Config:
        from_attributes = True

# routers/seasons.py
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from database import get_db
import models, schemas

router = APIRouter(prefix="/api", tags=["seasons"])


@router.post("/seasons", response_model=schemas.SeasonResponse)
def create_season(season: schemas.SeasonCreate, db: Session = Depends(get_db)):
    if season.is_current:
        # Only one season is ever "current" — clear the flag on every other one first
        db.query(models.Season).update({models.Season.is_current: False})

    db_season = models.Season(**season.model_dump())
    db.add(db_season)
    db.commit()
    db.refresh(db_season)
    return db_season

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

"At most one season is marked current" spans every row in the seasons table, 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 database constraint can't express on its own. The bulk update() above clears every other season's flag inside the same request, before the new season is even added — 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 — Team 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:

# schemas.py (additions)
class SeasonTeamAdd(BaseModel):
    team_name: str
    short_name: Optional[str] = None   # only used if the team doesn't already exist

class SeasonTeamResponse(BaseModel):
    id: int
    season_id: int
    team: TeamResponse

    class Config:
        from_attributes = True

# routers/seasons.py (additions)
MAX_TEAMS_PER_SEASON = 20


@router.post("/seasons/{season_id}/teams", response_model=schemas.SeasonTeamResponse)
def add_team_to_season(
    season_id: int, payload: schemas.SeasonTeamAdd, db: Session = Depends(get_db)
):
    season = db.get(models.Season, season_id)
    if not season:
        raise HTTPException(status_code=404, detail="Season not found")

    current_count = (
        db.query(models.SeasonTeam)
        .filter(models.SeasonTeam.season_id == season_id)
        .count()
    )
    if current_count >= MAX_TEAMS_PER_SEASON:
        raise HTTPException(
            status_code=400,
            detail=f"Season already has {MAX_TEAMS_PER_SEASON} teams",
        )

    # Reuse the historical Team row if this club has been tracked before
    team = db.query(models.Team).filter(models.Team.name == payload.team_name).first()
    if not team:
        team = models.Team(name=payload.team_name, short_name=payload.short_name or payload.team_name)
        db.add(team)
        db.flush()   # assigns team.id without committing yet

    already_in = (
        db.query(models.SeasonTeam)
        .filter(
            models.SeasonTeam.season_id == season_id,
            models.SeasonTeam.team_id == team.id,
        )
        .first()
    )
    if already_in:
        raise HTTPException(
            status_code=409, detail=f"{team.name} is already in this season"
        )

    season_team = models.SeasonTeam(season_id=season_id, team_id=team.id)
    db.add(season_team)
    db.commit()
    db.refresh(season_team)
    return season_team

Why the new Team is db.flush()'d, not db.commit()'d, before creating the SeasonTeam row

flush() sends the pending INSERT to PostgreSQL and lets the database assign team.id — needed here, since SeasonTeam.team_id can't be set until an id actually exists — without ending the transaction. Both the new Team row and the new SeasonTeam row stay part of the same transaction until the final commit(), so if anything after the flush goes wrong, both roll back together rather than leaving an orphaned Team row with no SeasonTeam to go with it.

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

db.query(models.Team).filter(models.Team.name == payload.team_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 Team 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

@router.delete("/seasons/{season_id}/teams/{team_id}", status_code=204)
def remove_team_from_season(season_id: int, team_id: int, db: Session = Depends(get_db)):
    season_team = (
        db.query(models.SeasonTeam)
        .filter(
            models.SeasonTeam.season_id == season_id,
            models.SeasonTeam.team_id == team_id,
        )
        .first()
    )
    if not season_team:
        raise HTTPException(status_code=404, detail="Team is not in this season")

    db.delete(season_team)
    db.commit()

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

Listing a Season's Teams

@router.get("/seasons/{season_id}/teams", response_model=list[schemas.TeamResponse])
def list_season_teams(season_id: int, db: Session = Depends(get_db)):
    return (
        db.query(models.Team)
        .join(models.SeasonTeam, models.SeasonTeam.team_id == models.Team.id)
        .filter(models.SeasonTeam.season_id == season_id)
        .order_by(models.Team.name)
        .all()
    )

A nested Pydantic model reads a joined result directly

SeasonTeamResponse.team: TeamResponse, back up in the schema for the "add team" route, is a real Pydantic model nested inside another one. It works because SeasonTeam.team — the relationship("Team") defined in Chapter 2 — already gives a real Team object to read from; from_attributes = True lets Pydantic walk that relationship automatically and serialize the related team's own fields as a nested object in the JSON response, with no manual re-shaping of the query result required.

These routes have no access control

Every route in this chapter is reachable by anyone who can reach the API — 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/{season_id}/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 create_season clears is_current on every other season with a bulk update before inserting the new one, and what would go wrong if that step were skipped.

📄 View solution

Exercise 2

Explain why add_team_to_season 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 Team row for the same real club.

📄 View solution

Exercise 3

Explain why db.flush() is used instead of db.commit() when creating a brand-new Team inside add_team_to_season, and what real guarantee would be lost if commit() were used at that point instead.

📄 View solution

Chapter 3 Quick Reference

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
db.flush() vs. db.commit() — flush assigns an id without ending the transaction, keeping a new Team and its SeasonTeam row atomic together
DELETE /api/seasons/{id}/teams/{team_id} — removes the SeasonTeam row only; the historical Team row is untouched
GET /api/seasons/{id}/teams — a real join, returned through a nested SeasonTeamResponse/TeamResponse Pydantic model
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