Entering Results & Calculating Correct Score vs. Correct Result

Premier League Predictor: Astro

Chapter 6 · Entering Results & Calculating Correct Score vs. Correct Result

Every fixture created back in Chapter 4 still carries home_score and away_score as NULL, and every prediction recorded in Chapter 5 has sat unscored ever since. This chapter closes that loop: a real result gets entered, and every prediction on that fixture is scored against it — using the exact point values the user confirmed directly while working through the FastAPI & PostgreSQL sibling course's own Chapter 6: 40 points for a correct score, 10 points for a correct result.

Two Genuinely Different Kinds of "Correct"

A correct score means the exact scoreline matches — predict 2-1, the result is 2-1. A correct result means only the outcome matches — home win, away win, or draw — even if the scoreline itself is off. Every correct score is automatically a correct result too (2-1 is a home win, and if the real result is also a home win, the outcome trivially matches), but the reverse isn't true: predicting 3-0 for a match that finishes 1-0 gets the outcome right and the score wrong.

// src/lib/scoring.ts
export const POINTS_CORRECT_SCORE = 40;
export const POINTS_CORRECT_RESULT = 10;

type Outcome = 'HOME_WIN' | 'AWAY_WIN' | 'DRAW';

function outcome(homeScore: number, awayScore: number): Outcome {
  if (homeScore > awayScore) return 'HOME_WIN';
  if (awayScore > homeScore) return 'AWAY_WIN';
  return 'DRAW';
}

export function scorePrediction(
  predictedHome: number,
  predictedAway: number,
  actualHome: number,
  actualAway: number
): number {
  // Exact match checked FIRST — see the warn-box below for why order matters here.
  if (predictedHome === actualHome && predictedAway === actualAway) {
    return POINTS_CORRECT_SCORE;
  }
  if (outcome(predictedHome, predictedAway) === outcome(actualHome, actualAway)) {
    return POINTS_CORRECT_RESULT;
  }
  return 0;
}

Checking outcome first would silently under-score every exact prediction

Because an exact scoreline match always implies an outcome match too, checking the outcome before the exact score and returning immediately on a match would mean scorePrediction never reaches the exact-match branch at all — a prediction of 2-1 against an actual result of 2-1 would be awarded 10 points (a correct result) instead of the 40 it actually earned. The exact check has to run first, and has to return before the outcome check ever gets a chance to fire.

A Real Gotcha While Adding the Column

points_awarded needs to live on the predictions table, nullable until a result is entered. The natural instinct is to add it to schema.sql alongside everything else — but every other statement in that file uses CREATE TABLE IF NOT EXISTS, and ALTER TABLE ... ADD COLUMN has no equivalent guard clause in SQLite at all. Since db.ts re-runs the whole schema file on every app startup, a bare ALTER TABLE statement would throw SqliteError: duplicate column name: points_awarded on the second run.

// src/lib/db.ts (addition, after db.exec(schema) runs)
const predictionColumns = db.pragma('table_info(predictions)') as { name: string }[];
const hasPointsAwarded = predictionColumns.some((col) => col.name === 'points_awarded');

if (!hasPointsAwarded) {
  db.exec('ALTER TABLE predictions ADD COLUMN points_awarded INTEGER');
}

CREATE TABLE IF NOT EXISTS has no ADD COLUMN counterpart

Checked directly against SQLite's own documented ALTER TABLE grammar: it supports RENAME TO, RENAME COLUMN, ADD COLUMN, and DROP COLUMN — none of them accept an IF NOT EXISTS clause the way CREATE TABLE does. A genuinely idempotent column addition has to check PRAGMA table_info for the column's own presence first, exactly the pattern above. This is a real, easy trap for anyone assuming every SQLite DDL statement is as forgiving about being run twice as CREATE TABLE IF NOT EXISTS is.

Entering a Result: A Genuine db.transaction() Use Case

Chapter 5's own upsert route needed no transaction wrapper, because better-sqlite3's synchronous calls already ruled out another request interleaving mid-sequence. Entering a result is a different situation: it coordinates one UPDATE on fixtures with potentially several UPDATEs on predictions — one per recorded prediction — and these writes genuinely need to succeed or fail together. If the fixture's own score updated but a crash interrupted the loop halfway through rescoring predictions, the database would be left in a state where some predictions reflect the new result and others still carry stale points from before.

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

export const PATCH: APIRoute = async ({ params, request }) => {
  const fixtureId = Number(params.fixtureId);
  const { home_score, away_score } = await request.json();

  if (!Number.isInteger(home_score) || !Number.isInteger(away_score) || home_score < 0 || away_score < 0) {
    return json({ error: 'home_score and away_score must both be non-negative integers' }, 400);
  }

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

  const applyResult = db.transaction((homeScore: number, awayScore: number) => {
    db.prepare(
      'UPDATE fixtures SET home_score = ?, away_score = ?, status = ? WHERE id = ?'
    ).run(homeScore, awayScore, 'finished', fixtureId);

    // Every prediction on this fixture is rescored from scratch on every call —
    // not just the first time a result is entered. See the tip-box below.
    const predictions = db.prepare('SELECT * FROM predictions WHERE fixture_id = ?').all(fixtureId);
    for (const prediction of predictions as any[]) {
      const points = scorePrediction(
        prediction.predicted_home_score,
        prediction.predicted_away_score,
        homeScore,
        awayScore
      );
      db.prepare('UPDATE predictions SET points_awarded = ? WHERE id = ?').run(points, prediction.id);
    }
  });

  applyResult(home_score, away_score);

  return json(db.prepare('SELECT * FROM fixtures WHERE id = ?').get(fixtureId));
};

A direct contrast with Chapter 5's own no-transaction finding

Chapter 5 needed no db.transaction() because it was a single find-then-write sequence with no other request able to interleave inside it — the synchronous nature of better-sqlite3 already made it atomic in every way that mattered. This route is different: it's coordinating multiple, separate writes — one fixture update plus one prediction update per recorded prediction — that together represent a single logical operation. db.transaction() here isn't guarding against another request racing in; it's guarding against a crash or thrown error leaving the fixture's own score updated while only some of its predictions have been rescored. Wrapping the whole callback means either every one of those writes commits, or none of them do.

Re-scoring is deliberate, not wasted work

The PATCH route above re-reads and re-scores every prediction on the fixture each time it's called — even predictions that were already scored against a previous, since-corrected result. That's intentional: a result entered in error and then corrected needs every affected prediction's points_awarded to reflect the corrected result, not the original mistake. Scoring only newly-added predictions would leave stale points sitting on rows from before the correction.

A Real Result Applied to Chapter 5's Own Example

Chapter 5 closed with five real predictions recorded against fixture 42: a user prediction of 2-1, an expert prediction of 1-1, two guest predictions (Micah Richards at 3-0, Jamie Carragher at 1-0), and an AI prediction of 2-0. A real PATCH /api/fixtures/42/result call with { "home_score": 2, "away_score": 1 } — the match actually finishing 2-1 — scores all five:

Source	Predicted	Actual	Match type	Points
user	2-1	2-1	Exact score	40
expert	1-1	2-1	Draw predicted, home win actual — neither	0
guest — Micah Richards	3-0	2-1	Home win predicted, home win actual	10
guest — Jamie Carragher	1-0	2-1	Home win predicted, home win actual	10
ai	2-0	2-1	Home win predicted, home win actual	10

Resolving Chapter 5's Own Deferred Question: Averaging Guest Points

Chapter 5 deliberately left one question open: with a variable number of guests each recording their own scoreline, how does the prediction league table turn several guest rows into a single comparable "guest" figure for that fixture? Averaging the raw scorelines themselves doesn't work — 3-0 and 1-0 don't average into another valid scoreline, and even if they did, a fractional goal count is meaningless. What genuinely averages is the points each guest earned, since a point value is always a plain number regardless of how many guests predicted or what they each guessed.

In this fixture's own example, both guests happened to earn 10 points each, so the averaged guest figure for fixture 42 is also 10. That's not a coincidence worth reading too much into — it's just what this particular result produced. A fixture where one guest predicted the exact score (40 points) and a second guest got only the outcome right (10 points) would average to (40 + 10) / 2 = 25 — a number that never appears as anyone's actual points_awarded, but is exactly the figure the prediction league table needs to compare "guest" against "user," "expert," and "ai" on equal footing.

The actual aggregation query lives in Chapter 8

This chapter establishes the principle — average points, not scorelines — and the real per-prediction points_awarded values this route calculates are exactly what that later average will be computed from. The SQL that actually groups every guest prediction by fixture and season to build the full prediction league table is Chapter 8's own job, once there's a real season's worth of scored fixtures to aggregate across rather than one single example.

Where This Course Is Headed

The real league table, built from every fixture's own actual result rather than any prediction (Chapter 7); the prediction league table, finally aggregating the points_awarded values this chapter calculates — including the guest-averaging principle established above — across a full season (Chapter 8); promotion and relegation (Chapter 9); Astro Islands and interactivity (Chapter 10); deployment (Chapter 11); and the capstone (Chapter 12).

Hands-On Exercises

Exercise 1

Explain, with a concrete example, why scorePrediction() checks for an exact score match before checking for a correct-result match, and what would go wrong if the outcome check ran first and returned immediately on a match.

📄 View solution

Exercise 2

A fixture has three guest predictions, scored (after a result is entered) at 40, 10, and 0 points respectively. Explain why the prediction league table should average these point values rather than averaging the guests' raw scorelines, and compute the correct averaged guest figure for this fixture.

📄 View solution

Exercise 3

Enter an incorrect result for a fixture with at least two recorded predictions, confirm both predictions' points_awarded values, then call PATCH /api/fixtures/{id}/result again with the corrected result and confirm that every prediction's points_awarded actually changes to reflect the correction — not just predictions added after the fix.

📄 View solution

Chapter 6 Quick Reference

POINTS_CORRECT_SCORE = 40, POINTS_CORRECT_RESULT = 10 — the real values confirmed by the user during the FastAPI & PostgreSQL sibling course's own Chapter 6
scorePrediction() — checks exact score match first (returns 40), then outcome match (returns 10), else 0 — the order matters, since an exact match always implies an outcome match too
points_awarded — a nullable INTEGER column added to predictions via a guarded ALTER TABLE, since SQLite's own ADD COLUMN has no IF NOT EXISTS clause the way CREATE TABLE does
PATCH /api/fixtures/{id}/result — updates the fixture's score/status and rescores every prediction on it, wrapped in db.transaction() since it's coordinating multiple writes as one logical operation
A genuine db.transaction() use case — contrasted directly against Chapter 5's own no-transaction upsert: this route needs atomicity across several writes, not protection from request interleaving
Re-scoring is total, not incremental — every prediction is recalculated on every PATCH call, so a corrected result also corrects every prediction's own points
Guest averaging resolved — average each guest's own points, never their raw scorelines, since points are always a plain comparable number
Next chapter: The real league table — points, goal difference, won/drawn/lost