TwoMore

Appearance

B4 — Matches + Rating (Backend) Adversarial Audit ​

Status: Archived Date: 2026-07-11 Scope: matches/match_score_calls schema + RLS (00007, 00057, 00011, 00013, 00067, 00149, 00162, 00219, 00324), participant_ids recompute (00057, 00313), score consensus (call_match/agree_to_call/reject_call, 00149/00150/00152/00154/00197/00236/00240/00242/00317, latest defs verified), score corrections (00308), apply_match_ratings (00068→00084→00147→00236→ 00323→00385, latest def verified), tier bands (00235/00282/00286), dual ELO (00017/00236), referee transfer (00154/00218/00241), start_match (00312), settle-before-play (00384, cross-ref only), disputed-state write sites (independently re-grepped), signal wiring for the match domain (00109/00240/ 00385).

Method: read every migration that CREATE OR REPLACEs a match-domain function and traced each RPC/trigger to its terminal (latest) definition before evaluating it — several functions have 3-6 historical redefinitions (apply_match_ratings: 00236→00323→00385; submit_match_score: 00068→ 00084→00147→00236→00317→00324; agree_to_call: 00149→00152→00154→00236→ 00317) and grading an early version would misreport fixed bugs as open or vice versa. Independently re-ran the score_status = write-site grep across all 385+ migrations (not just trusting U4's claim) and independently traced GRANT/REVOKE ACL history for 9 match RPCs to confirm a codebase-wide SECDEF sweep (00209) actually closed what looked like per-function gaps. Cross- referenced docs/audits/blocks/U4-matches.md (UI/UX audit, same date) and docs/audits/blocks/B3-payments-backend.md (payments backend, same date, already filed the settle-before-play/swap-gate CRITICALs) — this audit does not re-derive their findings, only verifies the fix surface is complete. No code modified.

Summary table ​

#FindingSeverityVerified
1submit_match_score has no status guard — an admin/match_director can silently rewrite a COMPLETED match's score with zero consent, bypassing 00308's all-agree correction flow, while apply_match_ratings' idempotency guard silently skips re-rating, permanently desyncing the displayed score from elo_history/player_matchupsCRITICALTraced through terminal RPC definitions
2matches.score_status is never written to 'disputed' by any migration — independently re-confirmed at the SQL layer (cross-ref U4 finding #1, same root cause, backend fix owned here)CRITICAL (cross-ref, not re-derived)Independent grep of all migrations
3Two independent unprotected paths let a completed/live match's roster or score be rewritten server-side with no status re-check: (a) matches_update RLS has no status predicate — team-slot swap (U4 #2 / B3 #1); (b) submit_match_score RPC itself (finding #1) bypasses RLS via SECURITY DEFINER and has no status check either — the wiring plan must close bothCRITICAL (fix-surface synthesis)Traced RLS + RPC independently
4RSVP tier-eligibility gate (rsvps_enforce_tier) uses the cross-pool combined profiles.elo_rating ("latest rating in the last-played pool" per 00236), not a format-aware rating matching the session being joinedMEDIUMConfirmed by migration read
5No server-side membership/RSVP validation on match participant slots at INSERT or swap time — team*_player*_id only requires a valid profiles(id) FK, not club membership or a confirmed RSVPMEDIUMConfirmed by migration read
6No server-side invariant prevents the same player appearing in two matches in the same round — only (session_id, round_number, court_number) uniqueness is enforced (00162); round-level player uniqueness is a client-only (rotation.rules.ts) guaranteeLOW-MEDIUMConfirmed by migration read
7Legacy verify_match_score RPC + verifyScore port/adapter method is orphaned dead code (0 hook consumers) — superseded by the match_score_calls consensus system but never removed; its incremental verified_by-append semantics now conflict with the consensus path's single-final-agreer writeLOW-MEDIUMConfirmed by grep, 0 consumers
8signals_on_match_insert / signals_on_match_update use SET search_path = public instead of the canon-mandated SET search_path = ''LOWConfirmed by migration read
9propose_referee_transfer has no match-status gate (referee_id is never cleared on completion) — moot today since the whole referee-transfer feature is unreachable from the UI (U4 #3)LOW (moot pending U4 #3's owner call)Confirmed by migration read
10Tiebreak-aware ELO winner bug (flagged in the task brief as "deferred, still open?") — confirmed CLOSED, not open. 00323 fixed it and the fix is verbatim-preserved through 00385, the current live definitionINFO (answers an explicit audit question)Traced 00236→00323→00385 diff
11Concurrent admin-submit vs consensus-agree finalization race — analyzed, confirmed safe (Postgres row-locking serializes it), not a live bugINFOAnalytical trace of lock semantics
12match_score_calls RLS + consensus RPCs — fully closed surface, "agree for others" impossible, auto-call sweep/trigger dedup race-freeINFO (going well)Confirmed by migration read
13SECDEF GRANT hygiene — a blanket 00209 sweep revoked PUBLIC/anon EXECUTE from every SECDEF function in public, closing what would otherwise look like 9+ individual missing-REVOKE gapsINFO (going well, initially looked like a gap)Confirmed by ACL/signature trace
14Guest/cross-club rating — any valid profiles(id) gets rated with no membership check; confirmed intentional (global cross-club ELO + per-club club_elo_ratings is the documented 00017/00236 design), not a bugINFO (design note under #5)Confirmed by migration read

1. CRITICAL — submit_match_score has no status guard, silently bypassing the 00308 correction-consent flow ​

The RPC (terminal definition, 00324_audit_batch15_two_sided_tiebreak_submit.sql:26-82):

sql
CREATE OR REPLACE FUNCTION private.submit_match_score(
  p_match_id UUID, p_team1_score INT, p_team2_score INT,
  p_team1_tiebreak_score INT DEFAULT NULL, p_team2_tiebreak_score INT DEFAULT NULL,
  p_outcome TEXT DEFAULT 'decisive'
) ... AS $$
BEGIN
  ...
  -- auth check: caller must be owner/admin/match_director of the club
  ...
  UPDATE public.matches
  SET team1_score = p_team1_score, team2_score = p_team2_score,
      team1_tiebreak_score = p_team1_tiebreak_score,
      team2_tiebreak_score = p_team2_tiebreak_score,
      outcome = p_outcome, status = 'completed', completed_at = now()
  WHERE id = p_match_id;              -- NO "AND status IN ('scheduled','in_progress')"
  DELETE FROM public.match_score_calls WHERE match_id = p_match_id;
  RETURN private.apply_match_ratings(p_match_id, p_outcome);
END;
$$;

The UPDATE has no status predicate. It authorizes on club role only (owner/admin/match_director), then unconditionally overwrites team1_score/team2_score/tiebreak/outcome/completed_at — including on a match that is already completed.

Why this matters — it's a live backdoor around the sanctioned correction path:

Migration 00308_completed_match_score_correction.sql built exactly one sanctioned way to change a completed match's score: propose_score_correction → every participant must agree_to_correction → the corrected score is written and all ratings are re-derived via recompute_all_ratings() (00308's own header: "Today a completed match's score is LOCKED... This adds the sanctioned path... grounded in the USTA rule 'correct only to a score both sides agree on'"). submit_match_score is a parallel, unrestricted door into the exact same column set, requiring zero participant consent, and reachable by the same role (owner/admin/match_director) that already has propose_score_correction available.

The rating-drift compounder: apply_match_ratings's idempotency guard (IF EXISTS (SELECT 1 FROM elo_history WHERE match_id = p_match_id AND reason = 'match')) fires on the SECOND submit_match_score call and no-ops the entire rating body — elo_history, profiles.singles_elo/ doubles_elo/elo_rating, club_elo_ratings, and player_matchups all stay frozen at the FIRST score's values. The matches.team1_score/ team2_score columns, however, already changed unconditionally in step 3 above (this happens before the idempotency-guarded call). Net result: the match's displayed score and its rating history permanently disagree, with no error, no signal (the match_completed AFTER UPDATE OF status trigger, 00109, only fires on an OLD.status IS DISTINCT FROM 'completed' transition — a second completed→completed write is invisible to it, so players are never re-notified their score changed), and no audit trail (unlike 00308's path, which is fully consent-gated and re-derives the whole rating chain).

Reachability: the sanctioned UI only mounts score-entry when match.status === 'scheduled' (packages/features/sessions/src/match-card.tsx:193, confirmed by grep), so no normal tap reaches this. But per the same standing framing this audit block's sibling findings use (U4 #2, B3 #1): the RPC is callable directly by any authenticated club admin/match_director via a stale client state, a race, or a direct API call — it is not RLS-gated at all (SECURITY DEFINER bypasses RLS entirely), so there is no defense layer below the client's own status check.

Fix shape: add AND status IN ('scheduled', 'in_progress') to submit_match_score's UPDATE, raising a friendly error (mirroring propose_score_correction's own 'Only a completed match can be corrected' guard) when the match is already completed — directing the caller to the 00308 correction flow instead. One migration, no client changes (the client never legitimately hits this path).

2. CRITICAL (cross-ref, not re-derived) — score_status never reaches 'disputed' ​

U4 (docs/audits/blocks/U4-matches.md finding #1) found this from the UI/query side (findDisputedByUser always returns [], HomeDisputedAlertBanner can never render). I independently re-ran the verification at the SQL layer rather than trusting the claim:

grep -rn "score_status" supabase/migrations/*.sql | grep -v "IN\|TEXT\|CHECK\|--\|COMMENT"

confirms every write site (00068, 00149, 00152, 00154, 00236, 00317) sets score_status = 'verified' — never 'disputed', including in reject_call's terminal definition (00385_alert_model_wiring.sql:404-462, "3. match_score_disputed"), which emits the match_score_disputed signal but never touches the matches row.

Backend fix surface for the wiring plan (this block owns the DB side; U4 owns the UI consumers, which are already correctly built and just need the column to actually change):

  • reject_call should UPDATE public.matches SET score_status = 'disputed' WHERE id = p_match_id before/alongside deleting the pending call.
  • A fresh call_match/propose_score_correction on a disputed match should clear it back to 'unverified' (both already INSERT a new match_score_calls row; add the score_status reset there).
  • No RLS change needed — reject_call and call_match/ propose_score_correction are already SECURITY DEFINER and already the sole legitimate writers.

3. CRITICAL (fix-surface synthesis for the wiring plan) — two independent unprotected paths into a finalized match ​

The task brief asks this audit to "verify the full fix surface... don't re-derive" the swap-gate criticals U4/B3 already filed. Confirmed: there are two structurally different gaps, both closing on a completed/live match, and a wiring plan needs both:

(a) RLS-level (already filed, U4 #2 / B3 #1): matches_update (00013_payment_history_and_match_director_policies.sql:76-89, the terminal policy — confirmed no later redefinition exists) has:

sql
USING (
  EXISTS (SELECT 1 FROM sessions s JOIN club_members cm ON cm.club_id = s.club_id
          WHERE s.id = matches.session_id AND cm.user_id = auth.uid()
            AND cm.role IN ('owner','admin','match_director') AND cm.is_active = true)
)

— no status predicate, no WITH CHECK (Postgres reuses USING — same pattern B3 flagged on session_payments). This is the RLS surface a raw .update({team1_player1_id, ...}) client call rides through (updateMatchTeams, confirmed by U4/B3). Independently confirmed one more detail neither prior audit called out: the ORIGINAL 00011 policy also had a participant self-update branch (auth.uid() IN (team1_player1_id, ...)), which 00013dropped when it added the match_director role — so today's RLS surface is admin-tier-only, which narrows exposure to admin-tier accounts but does not add a status check.

(b) RPC-level (new, filed here — finding #1): submit_match_score is SECURITY DEFINER and bypasses RLS entirely, so fixing (a) alone does not close the score-tampering surface — an admin/match_director can still silently rewrite a completed match's score via the RPC itself, independent of any RLS predicate. The 00384 enforce_settled_match_participants trigger (B3 #1) is also UPDATE OF participant_ids-scoped and does not fire on either the team-slot RLS path or a submit_match_score re-call (which never touches participant_ids at all).

Consolidated fix shape for the wiring plan: (1) add a status predicate to matches_update's USING/WITH CHECK (closes (a) — U4/B3's ask), (2) add participant_ids to enforce_settled_match_participants's UPDATE OF list (closes B3's settle-before-play bypass on the same write shape), (3) add a status guard to submit_match_score (closes (b) — finding #1, new). All three are independent, small, additive migrations; none require client changes since the sanctioned UI never exercises the guarded paths.

4. MEDIUM — RSVP tier gate uses cross-pool elo_rating, not a format-aware rating ​

private.rsvps_enforce_tier (terminal definition, 00286_rating_gate_hardening.sql:37-90) reads:

sql
SELECT elo_rating, rating_state, rating_source
INTO v_elo, v_state, v_source
FROM public.profiles WHERE id = NEW.user_id;
...
v_user_idx := private.tier_index_from_elo(v_elo);

profiles.elo_rating is explicitly documented (00236: "the singles branch now ALSO writes elo_rating... elo_rating = 'latest rating in the last-played pool'") as a cross-pool value that reflects whichever of singles_elo/doubles_elo the player most recently played, not the rating relevant to the session's actual format. A player whose last match was singles gets gated into a min_tier/max_tier-restricted doubles session using their singles rating (which can differ substantially from their doubles skill), and vice versa. apply_match_ratings itself correctly uses the pool-specific rating for its own tier-change signal (v_ratings[v_i] is singles_elo/doubles_elo per v_is_doubles) — only the RSVP gate has this drift.

Fix shape: pass the session's format into rsvps_enforce_tier (already available via sessions.format, joined in the same query) and select singles_elo/doubles_elo accordingly, mirroring apply_match_ratings's own branch.

5. MEDIUM — no server-side membership/RSVP check on match participant slots ​

matches.team1_player1_id/team2_player1_id are NOT NULL REFERENCES profiles(id); the _player2_id slots are nullable FKs to the same table (00007_matches.sql:9-12, FK constraints re-affirmed in 00219_fk_cascade_cleanup.sql:105-132). Neither matches_insert RLS (00013: is_club_admin(get_match_club_id(session_id))) nor any trigger checks that the four player IDs are actual members of the session's club, or confirmed RSVPs of the session. The only server-side gate on participant identity at all is 00384's settle-before-play payment check (fee sessions only, and — per B3's finding — bypassed on the swap write shape anyway).

Design note (not a bug): this is consistent with rating being an intentionally global, cross-club stat — profiles.elo_rating/ singles_elo/doubles_elo are per-user, not per-club, and club_elo_ratings is the separate per-club-scoped table (00017/00236) — so "a non-member gets rated" is expected by design for a guest/cross-pool match. The gap is narrower: nothing stops an admin-authored match from including a player who was never even offered a seat in the session (no membership, no RSVP, confirmed or otherwise) — a data-integrity gap in match composition, not a rating-model gap.

Fix shape: add a lightweight EXISTS check (RSVP status='confirmed' for the match's session) to matches_insert RLS and to the swap/update path being hardened for finding #3 — same authorization surface, one additional predicate.

6. LOW-MEDIUM — no round-level player-uniqueness invariant ​

uniq_matches_court_assignment (00162_match_court_assignment_unique.sql) enforces (session_id, round_number, court_number) uniqueness (prevents duplicate-tap double-round-generation) but nothing enforces that a given player appears in at most one match per (session_id, round_number). The generator (rotation.rules.ts, confirmed pure client-side, zero I/O imports — matches U4's finding) is the only thing guaranteeing this today. A bug in a future generator refactor, or a direct API insert, could place the same player on two courts in the same round with no server rejection — downstream this doesn't double-count ratings (each match is rated independently and correctly on its own merits) but it does produce an impossible "playing two matches at once" state with no live-scoring/court UI accounting for it.

Fix shape: a CHECK-friendly approach is awkward across 4 nullable columns; the practical fix is a BEFORE INSERT trigger that raises if any of the 4 new player IDs already appears in another matches row with the same (session_id, round_number).

7. LOW-MEDIUM — orphaned verify_match_score RPC + verifyScore adapter method ​

public.verify_match_score (00135_public_rpc_wrappers.sql:40-61, wrapping private.verify_match_score from 00068) does an incremental array_append(verified_by, p_user_id) per participant, flipping score_status = 'verified' only once every participant has individually called it. This entire mechanism predates and is now superseded by the atomic match_score_calls all-agree consensus (00149+), where agree_to_call's final agreer writes verified_by = array_append(..., auth.uid()) — a single UUID, not an incrementally-built full array.

  • packages/app/src/adapters/supabase/match.supabase.ts:154-169 (verifyScore) still calls supabase.rpc('verify_match_score', ...).
  • packages/app/src/ports/repositories/match.repository.port.ts:49 still declares the port method.
  • grep -rn "verifyScore\b" packages/app packages/features (excluding the port/adapter/mapper/generated-types definitions themselves) → 0 consumers. No hook wraps it; nothing in packages/features calls it.

Same shape as U4's referee-transfer finding (#3: backend fully wired, zero UI consumer) but on the backend side of this audit — a legacy verification subsystem left live after its successor shipped. Not itself exploitable (SECDEF-gated, participant-only), just dead weight with a semantically stale column-population story if it were ever accidentally re-wired.

Fix shape: delete verify_match_score (both RPC layers), verifyScore port method + adapter implementation, and the dead private.verify_match_score from 00068 — a single follow-up migration + 3-file client cleanup — OR, if there's a reason to keep an individual-acknowledgment path distinct from the all-agree consensus, document why both coexist.

8. LOW — inconsistent search_path on two match-domain signal triggers ​

signals_on_match_insert (00385_alert_model_wiring.sql:834-878) and signals_on_match_update (00109_signals_remaining_triggers.sql:275-322) both declare SET search_path = public rather than the canon-mandated SET search_path = ''. Not currently exploitable — every table reference in both bodies is already schema-qualified (public.sessions, public.matches) and the only unqualified identifiers are signal_category/signal_severity enum casts, which public search*path resolves safely — but it's a hygiene deviation from the rest of the match RPC surface (every RPC/trigger graded in this audit otherwise uses SET search_path = '' correctly) and from the codebase's own stated hard rule. Likely a broader pattern across other signals_on*\* triggers site-wide (out of this block's scope to audit exhaustively).

Fix shape: re-declare both with SET search_path = '' + fully qualify the two enum casts ('matchup'::public.signal_category, etc.) — a one-line change per function, zero behavior change.

9. LOW (moot pending U4 #3) — propose_referee_transfer has no status gate ​

propose_referee_transfer (terminal def, 00241_referee_transfer_signal_rewire.sql:13-88) only checks that the caller currently holds referee_id and the target has a confirmed RSVP — no v_match.status check. Since referee_id is never cleared on match completion (only release_referee/accept_referee_transfer touch it), a referee could technically propose a handoff on an already- completed match. Practically moot: U4 (finding #3) confirmed the entire referee-transfer feature is deliberately de-rendered from the UI (0 consumers of any of the 5 hooks) — this is a latent landmine in dead code, worth folding into whichever direction that feature's "finish or delete" decision goes, not an independent fix.

10. INFO — tiebreak-aware ELO winner bug: CONFIRMED CLOSED (task brief asked to verify) ​

The task brief flagged this as "deferred from club-lifecycle audit — still open?" Traced the full history:

  • The bug (pre-00323): apply_match_ratings (00236) determined the winner from v_t1 > v_t2 (games only), never reading team1_tiebreak_score/team2_tiebreak_score — a tiebreak-decided level set (e.g. 6-6, tiebreak 7-5) fell to the ELSE branch and rated the tiebreak winner as a full loss.
  • The fix (00323_audit_batch14_tiebreak_elo_winner.sql:143-147): introduced v_t1_wins/v_t2_wins, tiebreak-aware: (v_t1 > v_t2) OR (v_t1 = v_t2 AND COALESCE(v_tb1,0) > COALESCE(v_tb2,0)) — applied consistently at all four winner-dependent sites (decisive/ incomplete actual-score branches, upset detection, player_matchups W/L).
  • Verified NOT regressed in the current live definition: read apply_match_ratings's terminal body (00385_alert_model_wiring.sql:1193-1629, "reproduced verbatim from 00323... with ONE addition" per its own header, adding the elo_changed/tier_promoted/tier_demoted signals) line-by-line against 00323 — v_t1_wins/v_t2_wins and every downstream use of them (lines 1320-1324, 1400-1412, 1428-1430) are byte-identical to 00323. This is the opposite of what B3 found for the same migration's expire_unpaid_session_holds (a genuine silent regression from a stale "verbatim" copy) — here the "verbatim" claim checks out.
  • A companion write-path bug (00317: the games-only submit_match_score tiebreak re-derivation always hardcoded team2 as tiebreak winner on tied games) was separately fixed by 00324, which also added a matches_tiebreak_symmetry CHECK constraint backstopping all three score-write paths (submit_match_score/agree_to_call/ update_live_match_score) against asymmetric one-sided tiebreak input.

Conclusion: closed, not open. No further action needed here.

11. INFO — concurrent admin-submit vs consensus-agree race: analyzed, safe ​

Considered whether a submit_match_score call racing an agree_to_call finalization on the same match could double-apply ratings (both read apply_match_ratings's idempotency guard as false before either commits). Traced the locking: agree_to_call takes an explicit SELECT ... FOR UPDATE on the matches row before finalizing (00154, closing an earlier documented race); submit_match_score's own UPDATE public.matches SET ... WHERE id = p_match_id acquires the same row-level lock implicitly. Under Postgres read-committed semantics, the second transaction's UPDATE blocks until the first commits, then re-evaluates against the now-current row before proceeding to its own (blocking, sequential) call into apply_match_ratings — by which point the first transaction's elo_history insert is already visible. No double-rating race exists here. (This does NOT protect against finding #1's sequential-not-concurrent scenario — that's a logic gap, not a race, and locking can't fix a missing status check.)

12. INFO (going well) — match_score_calls consensus surface is fully closed ​

  • RLS is airtight: match_score_calls_no_direct_{insert,update,delete} (00149) are WITH CHECK (false)/USING (false) — the table is writable only through the 6 SECDEF RPCs (call_match, agree_to_call, reject_call, propose_score_correction, agree_to_correction, and the auto-call trigger/cron). Confirmed no other write path exists.
  • "Agree for others" is structurally impossible: every consensus RPC (agree_to_call, reject_call, agree_to_correction) uses auth.uid() directly for both the authorization check and the array mutation — none accept or trust a client-supplied user-id parameter.
  • Auto-call dedup is race-free: the instant AFTER UPDATE OF score columns trigger (00150) and the */5 cron sweep (00197) both guard with IF EXISTS (SELECT 1 FROM match_score_calls WHERE match_id = ...) THEN RETURN and the INSERT itself is ON CONFLICT (match_id) DO NOTHING against the table's own UNIQUE(match_id) — even a genuine concurrent double-fire cannot produce two call rows for one match.
  • Sudden-death / time-cap branches (check_auto_call section B) are correctly gated on started_at + rules_snapshot/session match_rules, cleanly falling through to sudden_death notification, draw auto-call, or current_score_stands auto-call per the configured onTimeUp action — no dead branch, no unreachable state found.

13. INFO (going well, initially looked like a gap) — SECDEF GRANT hygiene ​

Reading each function's own migration in isolation, 9 match-domain RPCs (call_match, agree_to_call, reject_call, update_live_match_score, update_session_rules, update_match_rules_snapshot, and the 5 referee RPCs before their own dedicated fix) show only a bare GRANT EXECUTE ... TO authenticated with no REVOKE EXECUTE FROM PUBLIC — which would normally read as 9 individual missing-hardening findings (Postgres grants EXECUTE to PUBLIC by default on function creation).

Traced forward and found this is not a live gap: 00209_revoke_anon_execute_secdef.sql runs a DO $$ ... FOR r IN SELECT p.oid::regprocedure FROM pg_proc p JOIN pg_namespace n ... WHERE n.nspname = 'public' AND p.prosecdef = true LOOP EXECUTE format('REVOKE EXECUTE ON FUNCTION %s FROM PUBLIC, anon;', r.sig) END LOOP $$ — a blanket sweep across every SECURITY DEFINER function in the public schema in one migration, closing all of them at once (plus ALTER DEFAULT PRIVILEGES ... REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC so new functions are safe-by-default going forward). The 5 referee RPCs got an explicit redundant follow-up (00218) because 00209 predates their 00154 re-hardening pass; 00241's later CREATE OR REPLACE of 3 of them re-declares REVOKE/GRANT explicitly too (defensive, not required since the signature didn't change).

Verified no later signature change silently reopened any of these: checked every CREATE OR REPLACE/DROP + CREATE across this block's scope for an argument-list change (which resets ACL to default) — only private.submit_match_score and public.submit_match_score changed signature (00324, adding two tiebreak params), and both explicitly re-issue REVOKE ALL ... FROM PUBLIC + GRANT ... TO authenticated immediately after. Clean.

14. INFO (design note, folded under #5) — guest/cross-club rating is intentional ​

No membership gate blocks a non-member profiles row from being rated via apply_match_ratings — any valid profiles(id) slotted into a match's team columns gets a full ELO update. Confirmed this is the documented design (00017: dual-ELO with club_elo_ratings as the club-scoped table alongside a global profiles.elo_rating/singles_elo/doubles_elo; 00236's cross-pool detection via private.get_home_club and the is_cross_pool confidence-bonus logic exist specifically to handle a player from outside the match's home club) — not a bug. The narrower gap is finding #5 (no check that the player was ever offered a seat in the session at all).

Adversarial probes run (explicit list) ​

  1. Traced apply_match_ratings through all 3 historical redefinitions (00236→00323→00385) diffing line-by-line for regressions — found none (contrast B3's finding of a genuine regression in a different function in the same 00385 migration).
  2. Double-finalization (admin-submit after consensus-agree already completed a match, and vice versa) → found the CRITICAL (#1): no status guard on submit_match_score, confirmed by reading its terminal definition directly (not inferring from an earlier version).
  3. Concurrent-finalization race (both paths racing, not sequential) → analyzed Postgres row-lock semantics, confirmed safe (#11).
  4. score_status = 'disputed' write-site search → independently re-ran (not trusting U4's claim) across all 385+ migrations, confirmed zero write sites (#2).
  5. K-factor / provisional / high-rated / upset / seed-deviation-strike / peak-floor logic in apply_match_ratings → read in full, singles and doubles branches structurally mirror each other with no asymmetry found; no defect identified.
  6. Tier-band RSVP gate vs. session format → found the pool-mismatch (#4).
  7. Match participant slots vs. club membership/RSVP → found no server check exists (#5); confirmed the gap is about seat legitimacy, not cross-club rating (which is by design, #14).
  8. Round-level player double-booking → confirmed no DB invariant exists beyond the court-assignment uniqueness index (#6).
  9. verify_match_score reachability → grepped for consumers beyond the port/adapter definitions themselves; confirmed 0 (#7).
  10. SECDEF search_path + GRANT hygiene swept across all ~20 match-domain functions in scope → found 2 search_path=public deviations (#8) and confirmed the apparent GRANT gaps are closed by 00209 (#13, initially misread as a gap until traced forward).
  11. Referee-transfer state-machine gate → confirmed no status check on propose (#9), folded into U4's existing dead-feature finding rather than filed as independent since the feature is unreachable.
  12. Auto-call / sudden-death timer races → confirmed dedup via UNIQUE(match_id) + ON CONFLICT DO NOTHING on both the instant trigger and the cron sweep paths (#12).

SECDEF hygiene sweep (full table, this block's scope) ​

Functionsearch_path=''REVOKE PUBLIC/anonGRANT
private.apply_match_ratings✓n/a (private schema)n/a
private.submit_match_score✓✓ (00324 explicit)n/a (private schema)
public.submit_match_score✓✓ (00324 explicit)authenticated
public.call_match✓✓ (00209 sweep)authenticated
public.agree_to_call✓✓ (00209 sweep)authenticated
public.reject_call✓✓ (00209 sweep)authenticated
public.update_live_match_score✓✓ (00209 sweep)authenticated
public.propose_score_correction✓✓ (explicit, 00308)authenticated
public.agree_to_correction✓✓ (explicit, 00308)authenticated
public.start_match✓✓ (explicit, 00312)authenticated
public.assign_referee / release_referee✓✓ (00218 explicit)authenticated
public.propose/accept/decline_referee_transfer✓✓ (00218 + 00241)authenticated
private.match_participants✓n/a (private schema)n/a
private.check_auto_call / trigger✓n/a (private schema)n/a
private.tier_from_elo / tier_index_from_elo✓n/a (private schema)n/a
private.rsvps_enforce_tier✓n/a (private schema)n/a
public.signals_on_match_insert✗ (public, not '')n/a (trigger-only)n/a
public.signals_on_match_update✗ (public, not '')n/a (trigger-only)n/a
public.signals_on_match_score_call_insert/delete✓n/a (trigger-only)n/a
public.verify_match_score (legacy, dead)not verified — dead code, out of scope——

No missing search_path='' on any function that actually needs it (trigger-only functions with no client-callable surface are lower stakes, but #8 flags the 2 deviations for consistency). No live PUBLIC/anon EXECUTE gap found anywhere in this block's scope (13, contrary to first appearance).

Cross-reference: U4 / B3 ​

  • U4 (docs/audits/blocks/U4-matches.md) — this audit's #2 (disputed state) is the same root cause as U4 #1; this audit's #3(a) is the same root cause as U4 #2 (team-slot swap RLS gap). Neither is re-derived here; this audit adds the backend fix shape and, for #3, the additional RPC-level gap (#1/#3(b)) neither U4 nor B3 identified. U4 #3 (orphaned referee-transfer UI) directly explains why this audit's #9 is graded LOW rather than independently actionable.
  • B3 (docs/audits/blocks/B3-payments-backend.md) — this audit's #3(a) is the identical bug B3 #1 found from the settle-before-play angle (same updateMatchTeams write shape, same missing-predicate root cause) — B3's fix (enforce_settled_match_participants's UPDATE OF list) and this audit's fix (matches_update RLS status predicate) are complementary, not duplicative, and the wiring plan should land both together since they share a root cause. B3 also independently found a genuine regression in 00385 (the payment-hold TOCTOU fix) from the same "verbatim from an outdated migration" pattern this audit checked apply_match_ratings against (#10/#1 in "Going well") — that pattern did not repeat in the rating engine; worth noting for calibration (00385 is not uniformly risky, just risky in the one place B3 found).
  1. Fix #1 (submit_match_score status guard) and #3(a)+(b) (matches_update RLS status predicate + enforce_settled_match_participantsUPDATE OF list, bundling B3's fix in the same migration) — the highest-leverage, most surprising findings; all three are small additive SQL changes with zero client changes required (no sanctioned UI path exercises any of the three).
  2. Fix #2 (reject_call writes score_status = 'disputed', cleared on a fresh call) — makes U4's already-built banner/query/badge chain work with zero client changes; bundle with #1/#3 in the same migration pass since all four touch matches-adjacent SECDEF functions.
  3. #4 (format-aware tier gate) and #5 (participant membership/RSVP check on match slots) — one migration each, no urgency but real integrity value; #5 pairs naturally with #3(a)'s RLS predicate work.
  4. #6 (round-level player-uniqueness trigger) and #7 (delete verify_match_score + verifyScore) — housekeeping, bundle whenever convenient.
  5. #8 (2-function search_path fix) — trivial, ride along with any other migration touching those files.
  6. #9 — no independent action; resolves automatically whichever way U4 #3's referee-transfer decision goes.

Markdown remains the source of truth. Run yarn docs:check before handoff.