Project Overview & Stack Setup

Premier League Predictor: FastAPI & PostgreSQL

Chapter 1 · Project Overview & Stack Setup

This is the first of four courses building the exact same Premier League prediction tracker in four genuinely different architectures — Premier League Predictor (FastAPI & Redis), Premier League Predictor (Django & MySQL), and Premier League Predictor (Astro) are its siblings, all still outlined rather than generated yet. This course's own answer leans into a real, fully normalized relational schema and a proper backend framework — the "does everything the textbook way" reference implementation the other three variants get compared against once they exist.

What the App Actually Does

The shared spec every course in this set builds toward — a personal, weekly Premier League prediction tracker, not a generic sports app:

Track every fixture across a real 38-gameweek Premier League season, 20 teams, 10 fixtures per gameweek.
Record four real prediction sources per fixture:
- the user's own prediction,
- the BBC's expert prediction (currently Chris Sutton),
- that week's guest predictor(s) — genuinely variable; some weeks have more than one guest, whose scores get averaged into a single "guest" figure for that week,
- and the BBC's own published AI-generated prediction, manually transcribed each week rather than scraped or generated by this app.
Score two different things per prediction once a result is in — a correct score (the exact scoreline matched) and a correct result (the predicted win/draw/loss outcome matched, even if the scoreline itself was wrong).
Maintain the real league table — points (3/1/0), goal difference, wins/draws/losses — alongside a second, parallel prediction league table ranking how each of the four real predictors is actually doing across the season.
Handle promotion and relegation between seasons — the bottom three teams are auto-calculated and removed at season end; the three promoted teams are entered manually, since which teams come up from the Championship isn't something this app tracks on its own.

Fixture entry is deliberately fast to use, not a generic form. Ten fixtures a week, every single week, for 38 weeks — a dropdown-heavy "select home team, select away team" form for every one of them would be genuinely tedious. Chapter 4 builds a real click-to-pair interface instead: 20 clickable team buttons, paired directly into home/away boxes.

One real detail still genuinely undecided

The actual point values for a correct score versus a correct result haven't been settled yet — this course flags that honestly here rather than guessing a number and presenting it as final. Chapter 6, where results and scoring actually get built, is where that number needs to be confirmed before the scoring logic can be written for real.

Why FastAPI + PostgreSQL for This One

Of the four variants in this set, this is the one built around genuine relational integrity and a real, typed API layer — the two things a season-long, multi-source prediction tracker benefits from most directly:

A real normalized schema. Teams, seasons, gameweeks, fixtures, and predictions are all genuinely separate, foreign-key-linked tables — not documents, not key-value pairs — which is exactly the shape this data already has (Chapter 2).
Pydantic-validated, typed request/response models. Four prediction sources per fixture, two different scoring outcomes per prediction, is enough real structure that catching a malformed payload before it ever reaches the database is a genuine, practical win, not just tidiness.
PostgreSQL's own real aggregate and window functions are a genuine fit for both league tables this app maintains — the real league table's own points/goal-difference sort, and the prediction league table's own per-predictor scoring aggregation, both covered in Chapters 7 and 8.

This Course Assumes Real FastAPI/PostgreSQL Groundwork Already Covered Elsewhere

This site's own fastapi1 (Website Rebuild with FastAPI) and fastapi-food-tracker-1 (Food Tracker) courses already cover FastAPI's own project structure, routing, and Pydantic validation in real depth, and postgres1 covers relational schema design and query fundamentals directly. This course doesn't re-teach any of that — it assumes it, and moves straight into this predictor's own real schema and features.

Setting Up: FastAPI, SQLAlchemy & PostgreSQL

A local Python install with pip, and a working local PostgreSQL server, are assumed from here on:

# Create and activate a virtual environment
python -m venv venv
source venv/bin/activate  # venv\Scripts\activate on Windows

# Install FastAPI, an ASGI server, SQLAlchemy, and the PostgreSQL driver
pip install fastapi uvicorn[standard] sqlalchemy psycopg2-binary python-dotenv

Create the database this whole course builds on:

CREATE DATABASE pl_predictor WITH ENCODING 'UTF8';

A minimal project structure, following this site's own established FastAPI project convention rather than inventing a new one:

pl-predictor-fastapi/
├── main.py              # FastAPI app instance, route registration
├── models.py            # SQLAlchemy models (Chapter 2)
├── schemas.py           # Pydantic models (Chapter 2)
├── database.py          # SQLAlchemy engine/session setup
├── routers/
│   ├── teams.py
│   ├── fixtures.py
│   ├── predictions.py
│   └── tables.py
├── static/              # frontend, styled in Chapter 10
└── requirements.txt

A minimal main.py, confirming the connection works before any real feature exists:

# main.py
from fastapi import FastAPI

app = FastAPI(title="Premier League Predictor")

@app.get("/api/health")
async def health():
    return {"status": "ok"}

uvicorn main:app --reload

/docs is a genuine, practical win for a project shaped like this one

Every route this course builds — team management, fixture entry, four separate prediction sources per fixture, results, two league tables — shows up automatically at http://localhost:8000/docs as a real, working interactive explorer, generated straight from the route definitions and Pydantic models this course writes anyway. For a project with this much real internal structure, that's a genuinely useful way to test each piece as it's built, well before any frontend exists to click through.

Where This Course Is Headed

A real normalized schema for teams, seasons, gameweeks, and fixtures (Chapter 2); managing the 20 competing teams each season (Chapter 3); the fast click-to-pair fixture-entry UI (Chapter 4); recording all four prediction sources per fixture (Chapter 5); entering results and settling the still-open correct-score-vs-correct-result point values (Chapter 6); the real league table (Chapter 7); the parallel prediction league table (Chapter 8); promotion and relegation between seasons (Chapter 9); styling and the gameweek/season selector (Chapter 10); deployment (Chapter 11); and a capstone on integrating this predictor into the existing Astro-based site (Chapter 12).

Hands-On Exercises

Exercise 1

Explain the real difference between a "correct score" and a "correct result" prediction, and describe one real scenario where a prediction is a correct result but not a correct score.

📄 View solution

Exercise 2

Explain why this app averages multiple guest predictors into a single "guest" figure for weeks with more than one guest, rather than tracking each guest as a permanently separate predictor across the season.

📄 View solution

Exercise 3

Set up the pl_predictor PostgreSQL database and a working FastAPI project connected to it yourself, confirm /api/health responds, then check /docs in a browser and write one sentence on what it's showing you before any real route exists.

📄 View solution

Chapter 1 Quick Reference

The shared app — a weekly Premier League prediction tracker: 4 real prediction sources per fixture (user, BBC expert, guest(s) averaged, BBC AI), scored by correct score and correct result, across a real 38-gameweek season
Two league tables — the real Premier League table, plus a parallel prediction league table ranking predictor performance
Promotion/relegation — bottom 3 auto-calculated and removed; the 3 promoted teams entered manually
Still open — the real point values for correct score vs. correct result, to be confirmed before Chapter 6
Why this variant — a real normalized relational schema plus a typed, Pydantic-validated API — the full reference implementation this set's other three variants get compared against
Database — PostgreSQL, created as pl_predictor with explicit UTF8 encoding
Assumed groundwork — FastAPI and PostgreSQL fundamentals, both covered in their own dedicated courses on this site
Next chapter: Data Modeling — teams, seasons, gameweeks & fixtures