Premier League Predictor: Astro — Chapter 5, Exercise 2
====================================================

TASK
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.

SOLUTION

Case 1 — a second "user" prediction on the same fixture:

The partial unique index (fixture_id, source) WHERE source != 'guest'
covers the "user" source directly, since "user" is not "guest". A
plain INSERT attempting to add a second "user" row for a fixture that
already has one would collide with that index and SQLite would reject
it, throwing a real SqliteError with code === 'SQLITE_CONSTRAINT_UNIQUE'.
Left uncaught, that becomes a generic, unhelpful 500 response — the
user gets no explanation, and worse, has no way to actually correct
their own earlier prediction, since every attempt to resubmit would
simply fail the same way. The route's own real find-then-write logic
is what turns this into an UPDATE instead, letting the same request
that would otherwise fail actually change the stored scoreline.

Case 2 — a second "guest" prediction from a guest with the same name:

The partial index explicitly excludes "guest" rows from its own
uniqueness check (WHERE source != 'guest'), so nothing at the database
level would stop a second INSERT for the same fixture_id, source =
'guest', and even the same guest_name from succeeding. The insert
would go through without error, and the predictions table would end
up holding two separate rows for what was clearly meant to be one
guest correcting their own earlier prediction — the exact same guest
name and fixture, two different scorelines, both stored. Later,
Chapter 6's own scoring logic and Chapter 8's own prediction league
table would have no reliable way to know which of the two rows
reflects that guest's real, final prediction, since the database
itself never enforced that a given (fixture, guest name) pair should
be unique.

In both cases, the find-then-write step is what actually prevents the
real-world problem — a hard failure with no recovery path in Case 1,
and a silent, ambiguous duplicate in Case 2 — since the database's own
guarantees only cover the first case and stay deliberately silent on
the second.

WHY THIS WORKS AS AN ANSWER
----------------------------
It treats the two cases separately, since they fail in genuinely
different ways (a real thrown error vs. a silently accepted
duplicate), and explains the real downstream consequence of each
rather than only stating that "it would break."