Predictions: Recording the User, Expert, Guest(s) & AI Predictions Per Fixture

Premier League Predictor: Astro

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.

-- src/lib/schema.sql (additions)
CREATE TABLE IF NOT EXISTS predictions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  fixture_id INTEGER NOT NULL REFERENCES fixtures(id),
  source TEXT NOT NULL CHECK (source IN ('user', 'expert', 'guest', 'ai')),
  guest_name TEXT,                    -- set only when source = 'guest'
  predicted_home_score INTEGER NOT NULL,
  predicted_away_score INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

No native ENUM type either — a CHECK constraint does the job instead

SQLAlchemy's own Enum(PredictionSource) column type has no direct SQLite equivalent, since SQLite genuinely has no dedicated enum column type at all — every column is fundamentally text, integer, real, or blob. CHECK (source IN ('user', 'expert', 'guest', 'ai')) gives an equivalent real guarantee at the database level: an attempt to insert any other value throws a real SqliteError with code === 'SQLITE_CONSTRAINT_CHECK', exactly like the fixture chapter's own team-can't-play-itself rule.

A Partial Unique Index: Genuinely Supported by SQLite Too

A plain UNIQUE (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:

-- src/lib/schema.sql (after the predictions table)
CREATE UNIQUE INDEX IF NOT EXISTS uq_prediction_single_source_per_fixture
ON predictions (fixture_id, source)
WHERE source != 'guest';

Worth correcting directly: this isn't actually PostgreSQL-only

The FastAPI & PostgreSQL course frames its own equivalent index as "a real PostgreSQL feature (SQLite has no equivalent)." Checked directly against SQLite's own official documentation, that's not accurate — partial indexes have been fully supported in SQLite since version 3.8.0, released in 2013, using nearly identical syntax: CREATE UNIQUE INDEX ... ON table(columns) WHERE expr, enforcing uniqueness only among the rows that satisfy the condition. The SQL above is, in fact, close to a direct, verbatim port of the PostgreSQL version, not a workaround for a missing capability. It's a useful reminder that "feature X belongs to database Y" claims are worth checking against the other database's own real documentation before repeating them, rather than assuming a more familiar system's own feature list is the complete picture.

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:

// src/pages/api/fixtures/[fixtureId]/predictions/index.ts
import type { APIRoute } from 'astro';
import { db } from '../../../../../lib/db';
import { json } from '../../../../../lib/http';

const VALID_SOURCES = ['user', 'expert', 'guest', 'ai'];

export const POST: APIRoute = async ({ params, request }) => {
  const fixtureId = Number(params.fixtureId);
  const { source, guest_name = null, predicted_home_score, predicted_away_score } = await request.json();

  const fixture = db.prepare('SELECT * FROM fixtures WHERE id = ?').get(fixtureId);
  if (!fixture) {
    return json({ error: 'Fixture not found' }, 404);
  }

  if (!VALID_SOURCES.includes(source)) {
    return json({ error: `source must be one of: ${VALID_SOURCES.join(', ')}` }, 400);
  }

  const isGuest = source === 'guest';
  if (isGuest && !guest_name) {
    return json({ error: 'guest_name is required for a guest prediction' }, 400);
  }
  const resolvedGuestName = isGuest ? guest_name : null;

  const existing = isGuest
    ? db.prepare(
        'SELECT * FROM predictions WHERE fixture_id = ? AND source = ? AND guest_name = ?'
      ).get(fixtureId, source, resolvedGuestName)
    : db.prepare(
        'SELECT * FROM predictions WHERE fixture_id = ? AND source = ?'
      ).get(fixtureId, source);

  if (existing) {
    const id = (existing as any).id;
    db.prepare(
      'UPDATE predictions SET predicted_home_score = ?, predicted_away_score = ? WHERE id = ?'
    ).run(predicted_home_score, predicted_away_score, id);
    return json(db.prepare('SELECT * FROM predictions WHERE id = ?').get(id));
  }

  const result = db.prepare(
    'INSERT INTO predictions (fixture_id, source, guest_name, predicted_home_score, predicted_away_score) VALUES (?, ?, ?, ?, ?)'
  ).run(fixtureId, source, resolvedGuestName, predicted_home_score, predicted_away_score);

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

export const GET: APIRoute = async ({ params }) => {
  const fixtureId = Number(params.fixtureId);
  const predictions = db.prepare('SELECT * FROM predictions WHERE fixture_id = ?').all(fixtureId);
  return json(predictions);
};

A direct payoff of Chapter 2's own synchronous-database finding

Between the SELECT that finds existing and the UPDATE/INSERT that follows it, there is no await anywhere — better-sqlite3's own calls are genuinely synchronous, so this entire find-then-write sequence runs to completion in one uninterrupted step, with no possibility of another request's own handler interleaving in the middle of it. That's a real, structural difference from an async database driver, where an awaited query genuinely yields control back to the event loop, letting a second concurrent request's own find-then-write race in before the first one finishes. No transaction wrapper is needed around this upsert for exactly that reason — not because upserts never need one in general, but because this specific stack's own synchronous calls already rule out the very race Chapter 3's own db.transaction() exists to guard against.

Without the "find first" step, a second submission would fail, not overwrite

If this route simply inserted a new row on every call, submitting a second user prediction for the same fixture would collide directly with the partial unique index above — SQLite would reject it with a real SqliteError, code === 'SQLITE_CONSTRAINT_UNIQUE', 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 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 — the row from the database and the JSON response are already the same shape, with no reshaping step in between:

[
  { "id": 1, "fixture_id": 42, "source": "user", "guest_name": null, "predicted_home_score": 2, "predicted_away_score": 1 },
  { "id": 2, "fixture_id": 42, "source": "expert", "guest_name": null, "predicted_home_score": 1, "predicted_away_score": 1 },
  { "id": 3, "fixture_id": 42, "source": "guest", "guest_name": "Micah Richards", "predicted_home_score": 3, "predicted_away_score": 0 },
  { "id": 4, "fixture_id": 42, "source": "guest", "guest_name": "Jamie Carragher", "predicted_home_score": 1, "predicted_away_score": 0 },
  { "id": 5, "fixture_id": 42, "source": "ai", "guest_name": null, "predicted_home_score": 2, "predicted_away_score": 0 }
]

Recording Predictions From the Frontend

A compact page, reusable across all four sources — the guest form adds a name field, and as many guest rows as that week actually needs, with every interaction wired up via addEventListener exactly the way Chapter 4 established, not inline onclick attributes:

---
// src/pages/admin/predictions.astro
---
<html lang="en">
  <body>
    <div>
      <label>Your prediction:
        <input type="number" id="user-home"> - <input type="number" id="user-away">
      </label>
      <button id="save-user">Save</button>
    </div>

    <div id="guest-rows"></div>
    <button id="add-guest-btn">Add Guest</button>

    <script>
      // In the real app, fixtureId comes from Chapter 10's own gameweek/season
      // selector — hardcoded here to keep this example focused.
      const fixtureId = 42;

      async function submitPrediction(
        source: string,
        guestName: string | null,
        homeScore: string,
        awayScore: string
      ) {
        const res = await fetch(`/api/fixtures/${fixtureId}/predictions`, {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({
            source,
            guest_name: guestName || null,
            predicted_home_score: Number(homeScore),
            predicted_away_score: Number(awayScore),
          }),
        });

        if (!res.ok) {
          const error = await res.json();
          alert(error.error);
          return;
        }
        alert('Prediction saved.');
      }

      document.getElementById('save-user')!.addEventListener('click', () => {
        const home = (document.getElementById('user-home') as HTMLInputElement).value;
        const away = (document.getElementById('user-away') as HTMLInputElement).value;
        submitPrediction('user', null, home, away);
      });

      let guestRowCount = 0;

      function addGuestRow() {
        guestRowCount += 1;
        const n = guestRowCount;
        const row = document.createElement('div');
        row.innerHTML = `
          <input type="text" placeholder="Guest name" id="guest-name-${n}">
          <input type="number" placeholder="Home" id="guest-home-${n}">
          <input type="number" placeholder="Away" id="guest-away-${n}">
          <button>Save</button>
        `;
        row.querySelector('button')!.addEventListener('click', () => {
          const name = (document.getElementById(`guest-name-${n}`) as HTMLInputElement).value;
          const home = (document.getElementById(`guest-home-${n}`) as HTMLInputElement).value;
          const away = (document.getElementById(`guest-away-${n}`) as HTMLInputElement).value;
          submitPrediction('guest', name, home, away);
        });
        document.getElementById('guest-rows')!.appendChild(row);
      }

      document.getElementById('add-guest-btn')!.addEventListener('click', addGuestRow);
    </script>
  </body>
</html>

Every guest row gets its own submitPrediction('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 UNIQUE (fixture_id, source) index wouldn't work for the predictions table, what the WHERE source != 'guest' clause actually changes about which rows the unique rule applies to, and correct the common assumption that a conditional partial index like this is a PostgreSQL-only feature.

📄 View solution

Exercise 2

Explain what would happen if the predictions route 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.

📄 View solution

Exercise 3

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.

📄 View solution

Chapter 5 Quick Reference

predictions — one table, four sources (user/expert/guest/ai) validated by a CHECK constraint standing in for SQLite's missing ENUM type, keyed by fixture_id + source (+ guest_name for guests)
Partial unique index — genuinely supported by SQLite since version 3.8.0 (2013), with syntax nearly identical to PostgreSQL's, correcting the sibling course's own "PostgreSQL-only" framing
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
No transaction needed here — better-sqlite3's synchronous calls mean the find-then-write sequence can't be interleaved by another request, unlike an async driver
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