TwoMore

Appearance

B2 — Sessions/RSVP: Adversarial BACKEND Audit ​

Status: Archived Date: 2026-07-11 Scope: supabase/migrations/* touching sessions/rsvps — status machine (00137 auto-advance, 00312 capacity/autolock, 00319 lifecycle safety), waitlist mechanics (00232, 00312, 00300/00313/00385 hold-expiry cron, 00310 forfeit-auto-substitute), RSVP integrity (RLS in 00011, tier/cooldown gates 00230/00133), settle-before-play completion trigger v3 (00384), seat-release-on-leave (00322), forfeit (00206/00310), capacity races, and SECDEF hygiene. Cross-referenced against docs/audits/blocks/U3-sessions.md (UI side — the stale payment-hold-gate finding). Also read the actual application/adapter/hook code (manage-rsvp.usecase.ts, rsvp.supabase.ts, use-rsvp.ts, session.supabase.ts, use-start-session.ts, use-end-session.ts, public-pickup-preview-screen.tsx) to verify what the client actually calls, not just what the schema intends. No code modified.

Executive-severity ranking ​

#FindingSeverity
1PublicPickupPreviewScreen — the screen built for non-members/unauthenticated users to preview a pickup session — is backed by an RLS-gated query that can never return data for that exact audienceCRITICAL
2Voluntary pre-session RSVP cancellation never promotes the waitlist; the only code that would have done it is dead, and even live it would fail RLSCRITICAL / HIGH
3Capacity gate (assign_waitlist_position) has a real TOCTOU race under concurrent last-seat RSVPs — no row/advisory lockHIGH
4Status machine has no positive transition table — cancelled → completed and any state-skip (open → completed, open → in_progress) are not server-blocked, only client-UI-gatedHIGH
5No trigger/RLS predicate blocks an RSVP-confirm on a locked/cancelled/completed sessionMEDIUM
6max_players decrease doesn't reconcile existing over-quota confirmed seats or re-evaluate open/lockedMEDIUM
7signals_on_rsvp_update (SECURITY DEFINER) uses SET search_path = public instead of ''LOW (hygiene)
8rsvps_delete RLS policy is dead — no client path ever hard-deletes an RSVPINFO

1. CRITICAL — Pickup preview screen can never load for its target audience ​

Evidence:

  • supabase/migrations/00011_rls_indexes_triggers.sql:201-204 — the ONLY sessions SELECT policy ever created:
    sql
    CREATE POLICY sessions_select ON public.sessions
  FOR SELECT TO authenticated
  USING (public.is_club_member(club_id));
    Confirmed via grep across all 385 migrations — sessions_select is never dropped or re-created after 00011. No second permissive SELECT policy exists (Postgres OR's multiple permissive policies together, so a second one covering public_invite_enabled would have worked — there isn't one).
  • packages/app/src/adapters/supabase/session.supabase.ts:63-70 — findById is a plain supabase.from('sessions').select(...).eq('id', id).maybeSingle() — no SECDEF RPC, no view. It is subject to sessions_select verbatim.
  • packages/app/src/index.ts:711 — useSessionQuery (the name imported by feature packages) is a re-export of useSession (packages/app/src/presentation/hooks/queries/use-sessions.ts:63-67), which calls sessions.findById(id).
  • packages/features/sessions/src/public-pickup-preview-screen.tsx:50 — PublicPickupPreviewScreen (routed at apps/mobile/app/sessions/[sessionId]/preview.tsx) uses useSessionQuery for its session data — the same RLS-gated path. Docstring (lines 1-16) is explicit about the audience: "the curated view a non-member / unauthenticated user sees when they tap a pickup card from discovery", with a CTA state machine whose first branch is unauthenticated → "로그인하고 신청하기".
  • public-pickup-preview-screen.tsx:276-277 — {!session ? (<EmptyState icon={Users} title={previewStrings.notFound} variant="full" />) : ...}.
  • is_club_member (00011:26-39) is EXISTS (... club_members WHERE club_id=... AND user_id=auth.uid() AND is_active=true) — false for anyone who hasn't joined yet, and sessions_select is TO authenticated only, so an unauthenticated request (auth.uid() NULL, no session/role) is blocked even before that check.

Impact: the ENTIRE non-member pickup-preview funnel — discovery card tap → /sessions/[id]/preview — renders "존재하지 않는 모임" (session not found) for every visitor who is not already a member of the session's club, i.e. every visitor the screen exists to serve. A logged-in existing member never notices (their own club sessions resolve fine), so this would be invisible in normal internal QA and only surface for a genuinely new user following a shared pickup link or browsing 번개 찾기 discovery — exactly the growth funnel U6 flagged from the UI side ("new-user guest-apply funnel leak") and the audit-plan header's remaining-work item #1 refers to tangentially. This is the root cause one layer deeper than U6's routing finding: even if routing were perfect, the destination screen cannot load data for that user.

Note on discovery RPCs: the summary RPCs (get_club_discovery_summary, get_public_share_preview in 00306, the 00256-00331 family) all correctly bypass this via SECURITY DEFINER

  • an explicit (s.visibility='public' AND s.public_invite_enabled=true) predicate — so the discovery FEED renders pickup cards fine. The gap is specifically the detail/preview hop from that feed into a single session's full record.

Fix shape: add a second permissive sessions_select policy (or extend the existing one) allowing SELECT when visibility = 'public' AND public_invite_enabled = true, mirroring the predicate every discovery RPC already uses — OR route PublicPickupPreviewScreen through a SECDEF RPC (mirrors get_public_share_preview) instead of the raw table read. The former is the smaller/more consistent fix (one source of truth for "is this session publicly visible") and also fixes any other raw-table read a non-member might legitimately need (e.g. findByIds bulk paths hitting a mixed set of own-club + public sessions).

2. CRITICAL/HIGH — Voluntary RSVP cancellation never promotes the waitlist ​

Evidence — the only 2 live promotion paths, both narrow:

  • private.expire_unpaid_session_holds (00300 → 00313 → 00385, cron */5) promotes the next waitlister, but ONLY as a side effect of a fee-session, idle-payment-hold expiry (s.participation_fee > 0 AND p.status='pending' AND p.submitted_at IS NULL). A free session, or a fee session where the canceller had already paid, never enters this path.
  • public.forfeit_session (00206 → 00310) promotes a waitlister, but ONLY as a mid-session substitute (v_session.status <> 'in_progress' raises forfeit_not_live) and only when no benched confirmed player is available for that specific match round — it is not a general seat-freed promotion, it's a roster-gap fill.

Evidence — the general-purpose promotion code is dead:

  • packages/app/src/application/session/manage-rsvp.usecase.ts:83-105 (handleCancel) DOES contain the intended logic — cancel, then promoteFromWaitlist(allRsvps, maxPlayers), then rsvps.upsert({ userId: promoted.userId, status: 'confirmed', ... }). But grep -rl "manageRsvpUseCase" across packages/ finds only its own file and the barrel re-export in packages/app/src/application/index.ts — zero consumers. Confirmed by reading the actual production path: packages/app/src/presentation/hooks/mutations/use-rsvp.ts:342 (useCancelRsvp's mutationFn) calls rsvps.cancel(sessionId, userId) directly and nothing else — no waitlist read, no promotion call.
  • Even if manageRsvpUseCase were wired up, the promotion write would fail for the common case: rsvps.upsert({ userId: promoted.userId, status: 'confirmed' }) is an UPDATE-on-conflict against a DIFFERENT user's row. rsvps_update RLS (00011:264-274) is USING/WITH CHECK ((select auth.uid()) = user_id OR is_club_admin(...)) — a regular member cancelling their own RSVP is neither the promoted user nor (in the common pickup-host case) a club admin, so the write would be RLS-rejected. This would only work when the canceller happens to be a club admin.
  • packages/features/sessions/src/participants-screen.tsx — grepped for any host-facing "promote" action on the waitlist section (waitlisted, lines 124-128, 210-211): none exists, only onRemove. grep -rl "promote" across packages/app/src and packages/features turns up zero UI/mutation surfaces for manual promotion either.

What actually happens today: a member cancels a confirmed RSVP before the session starts (the overwhelmingly common cancellation shape). The rsvps row flips to cancelled (sync_session_payment_on_rsvp drops an unpaid hold, autolock_session_on_capacity re-opens locked → open if the session had been at capacity — 00312). Nothing touches the waitlist. The session shows as open again with a free seat; existing waitlisted members still show status='waitlisted' with their waitlist_position intact but functionally meaningless — ANY member (not necessarily them, not necessarily first-in-line) can grab the seat by tapping RSVP again, since the capacity gate (assign_waitlist_position) only checks the current confirmed count, not who is "next."

Cross-check against the signal system: session_waitlist_promoted (00109:332-371) DOES fire correctly on any waitlisted → confirmed transition regardless of cause — so the alert-model plumbing itself is not the gap; it is simply rarely triggered because the underlying promotion almost never happens for the common cancellation shape. This directly answers the audit-plan's dimension-6 question ("rsvp_confirmed/ waitlist_join still silent — gap or correct?") for the promoted-side signal: the wiring is correct, the trigger condition upstream is the problem.

Fix shape: either (a) add a general promote_next_waitlisted(session_id) SECDEF RPC (mirrors start_match/forfeit_session shape: auth gate + host-or-admin check, or fire it automatically from a lightweight AFTER trigger on rsvps when a confirmed → cancelled transition frees a seat on an open/locked session with a non-empty waitlist — mirroring autolock_session_on_capacity's shape exactly, just promoting instead of only re-labelling the session), or (b) delete the dead manageRsvpUseCase/waitlist.rules.ts promotion code as aspirational and document the "waitlist is advisory, not FIFO-enforced" behavior explicitly if that is the intended product shape. Given docs/canon/sessions.md and the UI (participants-screen.tsx) both present the waitlist as ordered/positional, (a) matches stated intent.

3. HIGH — Capacity-gate TOCTOU race under real concurrency ​

Evidence: supabase/migrations/00312_audit_batch2_match_start_and_rsvp_capacity.sql:108-149 (private.assign_waitlist_position, BEFORE INSERT/UPDATE OF status on rsvps):

sql
SELECT max_players INTO v_max FROM public.sessions WHERE id = NEW.session_id;
IF v_max > 0 THEN
  SELECT COUNT(*) INTO v_confirmed FROM public.rsvps
   WHERE session_id = NEW.session_id AND status = 'confirmed'
     AND id IS DISTINCT FROM NEW.id;
  IF v_confirmed >= v_max THEN NEW.status := 'waitlisted'; END IF;
END IF;

No SELECT ... FOR UPDATE on the sessions row, no advisory lock keyed by session_id, no serializable isolation. Two concurrent transactions inserting/upserting DIFFERENT users' RSVPs for the same last open seat (different user_id, so the UNIQUE(session_id, user_id) constraint never engages to serialize them) each run SELECT COUNT(*) against the pre-commit snapshot under the default READ COMMITTED isolation. If both statements start before either commits, both see v_confirmed = max - 1 and both proceed to 'confirmed' — one seat is oversold by exactly the degree of concurrency.

Contrast with the codebase's own acknowledged pattern: 00232's assign_waitlist_position predecessor explicitly documents an analogous race in its own comment block (lines 16-19: "two simultaneous joins on the SAME locked session could read the same MAX and collide on a position — acceptable for an amateur-club waitlist") and calls it an accepted, named trade-off. 00312's capacity extension — whose entire stated purpose was closing "RSVP OVERBOOKING" (migration header, lines 18-21) — carries no equivalent acknowledgment, which reads as an unintentional gap rather than a documented trade-off: the fix closes the sequential overbooking case (a session already full correctly demotes a new confirm) but not the concurrent last-seat case.

Fix shape: SELECT ... FROM public.sessions WHERE id = NEW.session_id FOR UPDATE before the count (serializes on the session row — cheap, matches the lock-ordering discipline already established in 00313's expire_unpaid_session_holds comment, "codebase-wide order rsvps → session_payments"). Given amateur-club scale this is genuinely low-severity in practice (two people tapping RSVP within the same statement-execution window on the literal last seat is rare), but it directly contradicts the migration's own stated goal and is worth a documented decision either way.

4. HIGH — Status machine has no positive transition table ​

Evidence: private.guard_session_status_transition (00319_audit_batch10_lifecycle_safety.sql:72-97) is a two-rule BLOCKLIST, not an allowlist:

sql
IF NEW.status = 'draft' AND OLD.status <> 'draft' THEN NEW.status := OLD.status; END IF;
IF OLD.status = 'completed' THEN NEW.status := OLD.status; END IF;

That is the entirety of server-side transition enforcement. Combined with:

  • sessions_update RLS (00011:216-220) — is_club_admin(club_id), no status scoping at all.
  • packages/app/src/adapters/supabase/session.supabase.ts:273-284 (updateStatus) — a raw .update({ status }), no RPC, no transition-graph check client-side either beyond what the calling hook passes.
  • packages/app/src/presentation/hooks/mutations/use-end-session.ts:14 — useEndSession calls sessions.updateStatus(sessionId, 'completed') unconditionally (the safety is entirely "the button only renders for an in_progress session" in BottomCtaBand, per U3's audit — client-only).
  • packages/app/src/presentation/hooks/mutations/use-start-session.ts:124 — same shape for 'in_progress'.

Concretely unblocked, reachable via this raw path, both real gaps:

  • cancelled → completed — not listed in either blocklist rule. sessions_after_completed_record_attendance (00127_attendance_strike_triggers.sql:277-282, function body replaced by 00384) fires on WHEN (OLD.status IS DISTINCT FROM 'completed' AND NEW.status = 'completed') — i.e. it does not care what OLD was. A cancelled session forced to completed would re-run the full v3 completion sweep (settle-before-play hold cancellation + match-derived attendance) against a session whose RSVPs/payments were already unwound by whichever cancel path ran (cancel_session_as_host / 00307, cancel_pickup_session_without_strike / 00215) — producing a session that is simultaneously "완료" in status but was never actually played, polluting total_sessions_confirmed/attendance history for anyone whose RSVP survived the cancel un-cancelled (edge case, but structurally possible if a cancel path partially failed).
  • State-skip (open → completed directly, open → in_progress skipping locked) — likewise only prevented by which button the client renders, not by the DB.

Cross-reference: 00319's own commentary (lines 122-130) already names an adjacent gap in the SAME migration — an admin can still raw-mutate another member's RSVP to launder the delete-safety check, tracked as "a separate rsvps.status SECDEF guard... tracked as a follow-up batch, not folded here." This audit's finding is the sibling gap on sessions.status itself (not rsvps.status) — same shape (a defense-in- depth backstop was scoped to the two most dangerous transitions and never extended to a full transition table), consistent with the project's stated incremental-hardening pattern rather than a design oversight.

Fix shape: replace the two-rule blocklist with an explicit allowlist keyed on (OLD.status, NEW.status) pairs — mirrors the enum's own 6-state shape (draft→open→locked⇄open→in_progress→completed, plus →cancelled from any non-terminal state). Small, mechanical, and closes both holes at once without touching any legitimate caller (every legitimate transition in the codebase today is a strict subset of the enum's intended graph).

5. MEDIUM — No trigger blocks RSVP-confirm on a locked/cancelled/completed session ​

Evidence: every BEFORE trigger that gates a transition into 'confirmed' was read in full:

  • private.rsvps_enforce_tier (00230) — checks ELO tier band only.
  • private.rsvps_enforce_cooldown (00133) — checks strike count only.
  • private.assign_waitlist_position (00312) — checks max_players vs confirmed count only.

None of the three reads sessions.status. RLS (rsvps_insert/ rsvps_update, 00011:255-274) has no status predicate either. In practice this is largely masked because a cancelled/completed session typically has 0 confirmed RSVPs after its cancel/completion sweep runs (so a NEW confirm would pass the capacity check trivially and just succeed) — but nothing server-side stops a stale client, a race with the auto-advance cron, or a direct API call from creating a fresh 'confirmed' RSVP on a session that has already been cancelled or completed. sessions_select (finding #1) still gates whether the client can even see the session to attempt this, which narrows the practical exposure to existing members of the session's own club (who already had a live RSVP row and are re-tapping after a status flip they haven't refreshed).

Fix shape: extend assign_waitlist_position (already the single BEFORE-trigger seam every other RSVP-integrity rule uses) with SELECT status FROM sessions ... ; IF v_status NOT IN ('open','locked') THEN RAISE EXCEPTION, mirroring the tier/cooldown RAISE ... HINT shape so the client can surface a specific toast.

6. MEDIUM — max_players decrease doesn't reconcile capacity ​

Evidence: private.autolock_session_on_capacity (00312_audit_batch2...sql:160-200) is AFTER INSERT OR UPDATE OF status ON public.rsvps — it never fires on a sessions row UPDATE. If a club admin edits an existing session and lowers max_players below the current confirmed count (e.g. 16 → 8 with 12 already confirmed via the ordinary session-edit flow, use-update-session.ts → sessions.update(), a plain authenticated RLS write with no capacity-aware trigger on that column), nothing re-evaluates: the session's open/locked flag stays whatever it was until the NEXT independent rsvps write incidentally fires the trigger, and the 12 now-over-quota confirmed members are never trimmed, flagged, or waitlisted retroactively — they are silently grandfathered. New RSVP attempts ARE correctly gated (the assign_waitlist_position count check reads the live max_players), so no further overbooking accrues, but the existing overbook is invisible to the status machine.

Fix shape: add OR UPDATE OF max_players to a trigger on sessions that re-runs the same open/locked comparison autolock_session_on_capacity already does — cheap, and closes the display-staleness gap. Retroactively demoting existing confirmed members is a product decision (probably: don't — grandfather, but at least flip the status label correctly).

7. LOW (hygiene) — signals_on_rsvp_update search_path not pinned to '' ​

Evidence: supabase/migrations/00109_signals_remaining_triggers.sql:334-338

sql
CREATE OR REPLACE FUNCTION public.signals_on_rsvp_update()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public
AS $$ ...

grep across every later migration confirms signals_on_rsvp_update is never CREATE OR REPLACE'd again (unlike its neighbor signals_on_club_member_insert, which WAS re-hardened to search_path = '' in 00192:50-54), and it is not in 00208's blanket ALTER FUNCTION ... SET search_path = public, private, extensions remediation list either — it fell through both hardening passes. This is a live, currently-deployed violation of CLAUDE.md's explicit rule ("every SECURITY DEFINER function MUST set search_path = ''"). Practical exploitability is low — the function body only references public.sessions and public.signals_emit, both schema-qualified — but it is a real, confirmed gap in the mechanical hygiene sweep, not a hypothetical.

Fix shape: one-line ALTER FUNCTION public.signals_on_rsvp_update() SET search_path = '' (or a CREATE OR REPLACE matching the sibling functions' shape) in a follow-up migration.

8. INFO — rsvps_delete RLS policy is dead code ​

rsvps_delete (00011:277-282, admin-only) has zero client callers — grep -rn "from('rsvps').delete" across packages/app/src returns nothing. All cancellation flows exclusively write status='cancelled'. Not a bug (a hard delete would lose the attendance/payment trail every other migration in this block goes out of its way to preserve), just confirming the policy is unused, not a live gate on anything.

What works (verified, not just assumed) ​

  • UNIQUE(session_id, user_id) (00006:10) structurally prevents duplicate RSVP rows — no trigger needed, no gap found.
  • RSVP forging is correctly blocked for regular members. rsvps_insert WITH CHECK requires auth.uid() = user_id; rsvps_update USING/WITH CHECK requires self-or-admin. A non-admin cannot write another member's RSVP row in either direction.
  • 00384's completion trigger is well-guarded against double-fire. The WHEN (OLD.status IS DISTINCT FROM 'completed' AND NEW.status = 'completed') clause (unchanged since 00127) means a completed → completed re-save never re-fires. The genuine race between the hourly cron's cascade rule and the per-match cascade_match_completion_ to_session trigger is correctly serialized: both UPDATE ... WHERE status = 'in_progress' statements re-check their predicate after acquiring the row lock (Postgres READ COMMITTED re-evaluation semantics), so whichever commits first wins and the second is a guaranteed no-op, not a double-run.
  • 00313's hold-expiry cron correctly closes a real attest-vs-expiry TOCTOU — FOR UPDATE re-check on both the rsvps row and the session_payments row inside the cursor loop, in the documented lock order (rsvps → session_payments, matching the trigger-fired order elsewhere) to avoid an ABBA deadlock. Read the full function; the re-check logic is correct.
  • 00322 (seat-release-on-leave) and 00319 (session-delete safety) are unusually rigorous, self-auditing migrations. Both explicitly name their own residual scope gaps in the migration comment (the admin raw-RSVP path, the scheduled-match-roster-not-rebalanced-on-leave case) rather than silently leaving them — this audit independently rediscovered both named gaps rather than finding anything new there, which is a good sign of the prior audit batch's thoroughness.
  • forfeit_session (00206 → 00310) correctly differentiates host-vs-participant and its auto-substitute loop is per-round-scoped (WHERE m2.round_number = v_match.round_number) so a bench player can never be double-booked into two courts in the same round.
  • session_waitlist_promoted signal wiring is correct — fires on any waitlisted → confirmed transition regardless of which of the (narrow) promotion paths caused it. See finding #2 for why it rarely fires.
  • Cascade-skip idiom is applied consistently across 00311 (club-owner removal), 00319 (session delete backstop, refund-block retrofit), 00322 (seat-release) — every BEFORE-DELETE/AFTER-DELETE guard checks IF NOT EXISTS (SELECT 1 FROM public.clubs WHERE id = OLD.club_id) THEN RETURN OLD/NEW before running its guard logic, so the purge_archived_clubs cascade-purge cron is never aborted by any of them. No missed cascade-skip found in the sessions/rsvps blast radius.

Adversarial probes run (that came back clean) ​

  • Checked whether guard_session_status_transition's CURRENT_USER exemption could be spoofed by a client — no; CURRENT_USER reflects the actual connection role (authenticated/anon/authenticator for every PostgREST request), not a spoofable session variable, and every SECDEF RPC in this blast radius (forfeit_session, cancel_pickup_session_ without_strike, decide_guest_application, start_match) is owned by a role outside that list, so the exemption is correctly scoped. Consistent with 00311's established pattern for prevent_last_owner_ removal.
  • Checked whether matches.participant_ids guest coverage is complete for the 00384 completion sweep's match-derived attendance — a guest's approved application (decide_guest_application, 00215) inserts a normal rsvps row (status='confirmed', no joined_as_member distinction at the row-status level), so guests flow through the SAME rsvps WHERE status='confirmed' loop as members — no guest-specific gap found in the completion trigger.
  • Checked 00385's re-definition of expire_unpaid_session_holds against the live 00313 version for behavior drift beyond the added signal — byte-for-byte identical control flow (verified line-by-line); the only delta is the signals_emit call. Consistent with the audit-plan header's note that 00385 is still blocked on db push and hasn't landed.
  • Checked whether pickup sessions ever have a nullable/absent club_id (several comments, e.g. 00230:24 and 00312-era code, casually say "pickup session (no club)") — sessions.club_id is NOT NULL from 00005 onward and never altered; grepped for any nullable migration or a synthetic personal-club pattern and found none. Read as informal short-hand for "a club with no meaningful tier/member config," not a literal null — not a bug, but the comment itself is imprecise language worth a doc nit if anyone re-reads 00230 cold.

Canon / consistency notes ​

  1. Finding #2 (dead waitlist-promotion code + the live gap it was meant to cover) is the backend root cause underlying part of U3's UI finding #1 (the stale payment-hold gate) — U3 flagged the RSVP cancel/promotion interplay from the client-cache-staleness angle; this audit shows the promotion event that cache staleness was worried about essentially never happens for a voluntary cancel in the first place.
  2. Finding #1 is the backend layer beneath U6's "new-user guest-apply funnel leak" (onboarding-intent routing) — even a perfectly-routed visitor hits a dead end at the data layer.
  3. Every SECDEF function graded here follows the search_path='' + REVOKE PUBLIC + explicit GRANT discipline correctly EXCEPT the one named in finding #7 — the hygiene rule is well-enforced as a pattern, just missed one function across two hardening passes.

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