Premier League Predictor: FastAPI & PostgreSQL — Chapter 5, Exercise 2
====================================================

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

SOLUTION

Second user prediction on the same fixture: the partial unique index
from Exercise 1 applies to "user" rows, since "user" isn't "guest" —
the (fixture_id, source) pair already exists from the first submission.
A plain INSERT with no existing-row check would hit that index directly
and PostgreSQL would reject it as a unique constraint violation,
raising an IntegrityError in SQLAlchemy. Left uncaught, that surfaces
to the caller as an unhandled 500 Internal Server Error rather than
either successfully updating the prediction or returning a clear,
specific error — the user simply couldn't correct their own prediction
before kickoff at all.

Second guest prediction from a guest with the same name: the partial
unique index doesn't apply to "guest" rows at all, since the WHERE
clause excludes them — so a plain INSERT would actually succeed here,
with no database error raised. But that's arguably worse in a
different way: it would silently create a second, separate Prediction
row for that same guest and fixture, rather than updating their
existing one. GET /api/fixtures/{id}/predictions would then return two
rows for the same guest on the same fixture, and any later scoring
logic would have no reliable way to know which of the two rows is the
"real" prediction that guest actually intended to stand — a genuine
data-integrity problem the database itself has no way to catch, since
nothing about two same-named guest rows on the same fixture technically
violates any constraint.

The "find an existing row first" step in upsert_prediction handles both
cases correctly: for the user case, it finds the existing row and
updates it, avoiding the IntegrityError entirely; for the guest case,
it filters by guest_name too, so it can find that specific guest's own
prior prediction and update it rather than silently duplicating it.

WHY THIS WORKS AS AN ANSWER
----------------------------
It treats the two cases separately since they fail in genuinely
different ways — one hits a real database constraint and crashes, the
other silently succeeds but creates ambiguous duplicate data — and
explains exactly how the existing-row lookup in upsert_prediction
prevents both outcomes.