Predictions: Recording the User, Expert, Guest(s) & AI Predictions Per Fixture
Premier League Predictor: FastAPI & PostgreSQL
Chapter 5 · Predictions: Recording the User, Expert, Guest(s) & AI Predictions Per Fixture
Chapter 4 creates fixtures with home_score/away_score both still null. Before either of those gets filled in, four real sources each predict what they think will happen: the user, the BBC's expert, that gameweek's guest(s), and the BBC's own published AI prediction. This chapter builds the one table that records all four.
One Table, Four Sources
Three of the four sources — user, expert, AI — genuinely predict exactly once per fixture. The fourth, guest, is different by design: some weeks have one guest, some have several, and this app deliberately tracks every individual guest prediction rather than forcing them into one row before they've even been recorded.
# models.py (additions)import enum
from sqlalchemy import Enum, Index
class PredictionSource(str, enum.Enum):
USER = "user"
EXPERT = "expert"
GUEST = "guest"
AI = "ai"class Prediction(Base):
__tablename__ = "predictions"
id = Column(Integer, primary_key=True, index=True)
fixture_id = Column(Integer, ForeignKey("fixtures.id"), nullable=False)
source = Column(Enum(PredictionSource), nullable=False)
guest_name = Column(String, nullable=True) # set only when source == GUEST
predicted_home_score = Column(Integer, nullable=False)
predicted_away_score = Column(Integer, nullable=False)
created_at = Column(DateTime, server_default=func.now())
fixture = relationship("Fixture", back_populates="predictions")
# Extend Chapter 2's own Fixture class with the reverse side of this relationship:# predictions = relationship("Prediction", back_populates="fixture")
A Partial Unique Index: Exactly the PostgreSQL Feature Chapter 1 Picked This Stack For
A plain UniqueConstraint("fixture_id", "source") would enforce "one prediction per source per fixture" — but it would apply to every source equally, including guest, breaking the whole point of allowing several distinctly-named guest predictions on the same fixture. What's actually needed is a unique rule that applies to three sources and deliberately doesn't apply to the fourth — a partial unique index, a real PostgreSQL feature (SQLite has no equivalent):
That compiles to a real index PostgreSQL only enforces where the condition holds:
CREATE UNIQUE INDEX uq_prediction_single_source_per_fixture
ON predictions (fixture_id, source)
WHERE source != 'guest';
A single mechanism doing exactly what Chapter 1 pointed at, but never named
Chapter 1's own reasoning for this variant leaned on "a real normalized schema" and "PostgreSQL's own real aggregate and window functions" — this is the same spirit, one level more specific: a genuinely PostgreSQL-native feature (a conditional index) expressing a rule this course's own data model needs — three sources locked to one prediction each, one source deliberately allowed to repeat — that a plain cross-database UniqueConstraint simply can't express on its own.
Recording (and Correcting) a Prediction: an Upsert
A person should be able to change their mind about a prediction right up until kickoff — the route below looks for an existing prediction before deciding whether to update it or create a new one:
# schemas.py (additions)class PredictionCreate(BaseModel):
source: models.PredictionSource
guest_name: Optional[str] = None
predicted_home_score: int
predicted_away_score: intclass PredictionResponse(BaseModel):
id: int
fixture_id: int
source: models.PredictionSource
guest_name: Optional[str]
predicted_home_score: int
predicted_away_score: intclass Config:
from_attributes = True
# routers/predictions.pyfrom fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from database import get_db
import models, schemas
router = APIRouter(prefix="/api", tags=["predictions"])
@router.post("/fixtures/{fixture_id}/predictions", response_model=schemas.PredictionResponse)
def upsert_prediction(fixture_id: int, payload: schemas.PredictionCreate, db: Session = Depends(get_db)):
fixture = db.get(models.Fixture, fixture_id)
if not fixture:
raise HTTPException(status_code=404, detail="Fixture not found")
is_guest = payload.source == models.PredictionSource.GUEST
if is_guest and not payload.guest_name:
raise HTTPException(status_code=400, detail="guest_name is required for a guest prediction")
guest_name = payload.guest_name if is_guest elseNone
query = db.query(models.Prediction).filter(
models.Prediction.fixture_id == fixture_id,
models.Prediction.source == payload.source,
)
if is_guest:
query = query.filter(models.Prediction.guest_name == guest_name)
existing = query.first()
if existing:
existing.predicted_home_score = payload.predicted_home_score
existing.predicted_away_score = payload.predicted_away_score
db.commit()
db.refresh(existing)
return existing
prediction = models.Prediction(
fixture_id=fixture_id,
source=payload.source,
guest_name=guest_name,
predicted_home_score=payload.predicted_home_score,
predicted_away_score=payload.predicted_away_score,
)
db.add(prediction)
db.commit()
db.refresh(prediction)
return prediction
@router.get("/fixtures/{fixture_id}/predictions", response_model=list[schemas.PredictionResponse])
def list_predictions(fixture_id: int, db: Session = Depends(get_db)):
return (
db.query(models.Prediction)
.filter(models.Prediction.fixture_id == fixture_id)
.all()
)
Without the "find first" step, a second submission would fail, not overwrite
If upsert_prediction simply inserted a new Prediction row on every call, submitting a second user prediction for the same fixture would collide directly with the partial unique index above — PostgreSQL would reject it as a real constraint violation, an unhelpful 500 unless caught. Checking for an existing row first, and updating it in place when one's found, is what actually lets someone correct a prediction before kickoff instead of just being told they can't submit again.
Turning several guest scorelines into one comparable figure is Chapter 6's job
This chapter only records what each individual guest actually predicted — a real, separate Prediction row per guest, each with its own guest_name. Averaging multiple real scorelines (2-1 and 1-0 don't average into another valid scoreline) into the single "guest" figure the prediction league table eventually scores is a genuinely separate problem, deliberately left for Chapter 6, where results and scoring actually get built.
A Real Example Response
GET /api/fixtures/42/predictions for a fixture with two guests that week:
A compact form, reusable across all four sources — the guest form adds a name field, and a page can render as many guest rows as that week actually needs:
Every guest row gets its own submitPrediction(fixtureId, 'guest', guestName, home, away) call — each one a genuinely separate upsert, keyed by that specific guest's own name.
No prediction deadline is enforced yet
A real prediction should probably stop being editable once the fixture actually kicks off — nothing in this chapter checks fixture.kickoff_time against the current time before accepting an upsert. That's an honest gap, not an oversight worth expanding this chapter to close; it's flagged here as a real candidate for a later refinement rather than pretended away.
Where This Course Is Headed
Entering real results — filling in the home_score/away_score this course's fixtures have carried as null since Chapter 4, and defining, at last, how a correct score and a correct result actually get calculated against every one of these four prediction sources, including how multiple guest predictions become one comparable figure (Chapter 6); the real league table (Chapter 7); the prediction league table, scoring every source this chapter records (Chapter 8); and promotion/relegation (Chapter 9).
Hands-On Exercises
Exercise 1
Explain why a plain UniqueConstraint("fixture_id", "source") wouldn't work for the Prediction table, and what the postgresql_where=(Prediction.source != PredictionSource.GUEST) clause actually changes about which rows the unique rule applies to.
Explain what would happen if upsert_prediction skipped the "find an existing row first" step and always ran a plain INSERT, for both a second user prediction on the same fixture and a second guest prediction from a guest who already predicted that fixture under the same name.
Record a user, an expert, an AI, and two separately-named guest predictions against a single real fixture, then call GET /api/fixtures/{id}/predictions and confirm all five rows come back with the correct source and guest_name values.
Prediction — one table, four sources (USER/EXPERT/GUEST/AI), keyed by fixture_id + source (+ guest_name for guests)
Partial unique index — a real PostgreSQL feature enforcing "one prediction per source" for user/expert/AI while deliberately allowing multiple guest rows
POST /api/fixtures/{id}/predictions — an upsert: finds an existing row for that source (and guest_name, if a guest) and updates it, or creates a new one
guest_name — required when source is guest, silently nulled for every other source
Still open — no prediction deadline is enforced against kickoff_time yet
Deferred to Chapter 6 — how multiple guest scorelines become the single "guest" figure the prediction league table actually scores
Next chapter: Entering results and calculating correct score vs. correct result