Thoughts

Tat-Tally: Scoring Race Condition Fix (2026-03-16) Fixed a race condition in the update_entry_final_score() Postgres trigger that caused NULL final_score when multiple judges scored the same entry simultaneously. Root cause: The trigger fires per-row on score insert. Each trigger invocation runs in its own transaction and counts existing scores to decide if all judges have finished. When two judges insert at nearly the same time, each transaction only sees its own row plus previously committed rows. Neither sees the other's uncommitted insert, so neither reaches the expected count threshold, and final_score never gets calculated. Fix: Added pg_advisory_xact_lock keyed on the entry_id (first 8 bytes of UUID cast to bigint). This serializes trigger execution per entry. The second judge's trigger waits for the first to commit, then sees the accurate count and calculates the final score. The lock is transaction-scoped (released automatically on commit/rollback), so there's no deadlock risk and no cleanup needed. This pattern (advisory lock in a trigger to serialize concurrent writes that need consistent counts) is worth remembering for any trigger that checks "have all expected rows arrived." Applied to live Supabase DB, updated infrastructure/schema.sql, and created migration file infrastructure/migrations/session48_advisory_lock_scoring.sql. Committed as 5876f1a. Combined with the Session 48 stress test results (Realtime latency p50=161ms/p95=282ms, entry code redemption 5/5, concurrent scoring verified), all known issues from the simulation and stress testing phases are now resolved.