TwoMore

Appearance

B1 — Clubs Domain: Adversarial BACKEND Audit ​

Status: Archived Date: 2026-07-11 Scope: supabase/migrations/*.sql — club tables (clubs, club_members, invites, club_join_request, guest_applications, club_elo_ratings, club_posts/board), RLS, SECDEF RPCs, triggers, GRANTs. Cross-referenced against docs/audits/blocks/U2-clubs.md (UI-side findings) — this doc audits the SERVER side of the same flows (join-request pipeline, dues authority). Method: direct migration read (00003→00385, ~70 files touching club tables) + targeted greps for GRANT/SECDEF/is_active/deleted_at patterns + cross-reference against client adapters (club.supabase.ts) and domain entities (club.entity.ts, use-club-role.ts) to verify which server guarantees the client code actually depends on. No code modified.

Executive-severity ranking ​

#FindingSeverity
1Deleting your account while sole club owner permanently locks the club — no RPC path lets anyone become owner againCRITICAL
2Deleted/anonymized accounts are never removed or deactivated from club_members — permanent ghost members, forever counted against max_members capacityCRITICAL (root cause of #1, independently harmful)
3guest_applications never received the raw-UPDATE lockdown that club_join_request got in 00329 — an admin's raw PATCH can "approve" without creating the RSVP, and neither decide_guest_application nor the auto-approve trigger checks session capacity/statusHIGH
4role_configs (custom per-club permissions incl. manage_dues) is a schema/domain concept with zero server-side backing — RLS and RPCs hard-code owner/admin/session-host, never read role_configsMEDIUM (currently dormant — no write UI exists yet)
5Club lifecycle silence: no signal ever fires for member-left / member-removed / club settings changed (incl. dues_amount changes)MEDIUM
6invites table (direct phone invite flow) is fully dead code server-and-client — adapter registered, zero consumersLOW (dead code, not a bug)
7clubs, club_members, invites, guest_applications have never received an explicit data-API GRANT — rely entirely on Supabase default-privilege auto-grant, violating the project's own new-table ruleLOW (hygiene, not currently exploitable — RLS scopes to authenticated)
8Server side of the U2-flagged "dead" join-request pipeline is fully built, correct, and adversarially hardened (00213/00320/00321/00328/00329) — confirms U2's "hooks built but unconsumed" framing preciselyINFO (positive finding)

1. CRITICAL — Owner account-deletion permanently locks the club ​

Evidence:

  • public.delete_account_atomic() (00186_soft_delete_anonymize_complete.sql:54-127) only touches profiles (deleted_at, push_token), user_consent, and pi_access_log. It never reads or writes club_members.
  • private.anonymize_withdrawn_profiles() (00157_pipa_hard_delete_cron.sql:33-90, daily cron after the 30-day grace window) scrubs PII on profiles and deletes user_preferences/notification_events/signal_consent_log — again, never touches club_members. The design is explicitly documented as "anonymize, don't drop… deleting profile rows would cascade into matches/RSVPs/scores" (00157:8-13) — i.e. profiles rows are permanent by design, which means every FK-referencing club_members row is permanent too.
  • transfer_club_ownership(club_id, new_owner_id) (00214) requires the caller to currently hold role='owner' AND is_active=true — but an account-deleted owner can never authenticate again to call it.
  • private.update_member_role (00121) only lets an existing owner promote someone else to owner (p_new_role='owner' AND v_caller_role != 'owner' → reject, 00121:47-49); a non-owner admin can never self-promote past a ghost owner.
  • prevent_last_owner_removal (00311, BEFORE DELETE on club_members) never fires here because nothing ever deletes or deactivates the ghost owner's row — the owner-count check only guards the DELETE/role-change paths, not the "owner's account no longer exists" path, which isn't a distinct case the 09319-00329 audit-batch series (which is otherwise extremely thorough on lifecycle races) ever covers.
  • Cross-checked: no migration sets club_members.is_active = false anywhere in the repo (grep -rn "club_members" supabase/migrations/*.sql | grep -i "is_active = false" → 0 hits) and no migration reads profiles.deleted_at in any club-role function (grep -rn "deleted_at" supabase/migrations/*.sql | grep -iE "club_member|is_club_|update_member_role|transfer_club_ ownership" → 0 hits).

Impact: a solo-owner club (the common case for a small pickup club with one 총무) becomes permanently unmanageable the moment that owner deletes their account without first running transfer_club_ownership. No admin can be promoted to owner, no owner-only action (archive_club, unarchive_club, transfer_club_ownership, promoting a new owner) can ever run again through any client-reachable path. The only escape is a manual service_role SQL fix — an operator hand-intervention, not a product feature. This is exactly the "owner leaves without transfer" adversarial probe this audit was asked to run, and it reproduces cleanly against the shipped schema.

Fix shape: either (a) delete_account_atomic auto-transfers ownership to the longest-tenured active admin (or archives the club if no admin exists) before soft-deleting a sole-owner account, or (b) block delete_account_atomic when the caller is the sole owner of any active club and surface a "transfer ownership first" flow client-side (mirrors the existing useConfirm-gated destructive-action pattern already used for archive/leave/transfer elsewhere in this package per U2's audit). (a) is safer for UX (never blocks account deletion, a PIPA §37 right); (b) is safer for club continuity. Needs a migration + docs/canon note either way.

2. CRITICAL — Ghost members are never removed from club_members ​

Evidence: same trace as #1. Beyond the owner case, ANY member (not just owner) who deletes their account keeps an is_active=true row forever:

  • countActiveMembers (club.supabase.ts:404-416) and every max_members capacity check (private.join_club_member_checked, 00304:52-65) count this ghost row, permanently consuming a membership slot in capacity-limited clubs.
  • is_club_member/is_club_admin/is_club_owner (00011) all key off is_active = true with no profiles.deleted_at IS NULL join — so a deleted admin's is_club_admin() continues to return true for RLS purposes indefinitely (though in practice they can never authenticate to exploit it — the risk is entirely the capacity/roster-integrity one, not an auth bypass).
  • Member lists / rosters will show display_name = '탈퇴한 회원' (post anonymization) forever occupying a seat, with no admin-facing affordance to clean it up (removeMember requires an admin action per member — no bulk "purge departed accounts" tool exists).

Impact: independent of the owner case, this is a slow capacity/roster leak. A club with max_members set will eventually be unable to accept new members even though its real active headcount is lower — degrading exactly the club-growth flows this codebase is currently prioritizing (00255+ club-growth work, most recent commits).

Fix shape: a lightweight AFTER UPDATE trigger on profiles.deleted_at (NULL → value) that deactivates (not deletes — preserve history) the user's club_members rows, mirroring the release_member_future_seats_on_leave (00322) RSVP-release pattern for future sessions. Needs a real is_active=false semantics decision since today the column is otherwise never set false (see finding below) — this would be the first legitimate writer of that state.

3. HIGH — guest_applications missing the club_join_request-style RPC-only lockdown; no capacity/status gate ​

Evidence:

  • guest_applications_admin_update policy (00017_pickup_games_guest_invites_dual_elo.sql:91-102) has a USING clause (owner/admin/match_director of the club) but no WITH CHECK — Postgres reuses USING for both, so any admin/ match_director can raw-PATCH a guest_applications row via the data API directly to status='approved', bypassing decide_guest_application (00215→00225) entirely.
  • This is the exact same class of bug 00329_audit_batch21_join_request_ update_with_check.sql closed for club_join_request (raw admin PATCH bypassing the RPC's side effects) — but the fix was never applied to the sibling guest_applications table, which has an identical request/decide/RPC shape.
  • Concretely, a raw admin .update({status:'approved'}) on guest_applications:
    • Still fires signal_guest_application_decided_emit (00216, AFTER UPDATE trigger — fires regardless of write path), telling the applicant "you're approved!"
    • Never runs the INSERT ... rsvps ... ON CONFLICT DO UPDATE that decide_guest_application performs (00225:113-121) — so the applicant is told they're in, but holds no RSVP row and does not appear on the session roster.
    • Never stamps approval_method='manual'/approved_at (00225 columns) — UI that branches on those (approval-method badge) sees stale nulls.
    • Never checks v_app.status <> 'pending' (only the RPC does) — an admin could re-"approve" an already-rejected application via raw PATCH.
  • Separately, neither decide_guest_application (00225) nor the auto-approve trigger private.auto_approve_guest_application_if_needed (00225:154-197) checks the session's capacity (max_participants) or status (cancelled/completed) before upserting a confirmed RSVP — contrast with private.join_club_member_checked (00304), which explicitly checks max_members before inserting. A guest can be approved (auto or manual) into an already-full, cancelled, or completed session.

Impact: the roster-integrity + notify-without-membership gap is a real UX/data-integrity defect reachable by any club admin/match_director doing a manual data-API call (accidental via a debugging tool, or a future admin screen that writes the table directly instead of calling the RPC — nothing in the schema prevents it). The capacity/status gap is a session-lifecycle bug (closer to B2 scope) but originates in this table's guarded RPCs, flagged here for visibility to the B2 auditor.

Fix shape: mirror 00329 exactly — DROP POLICY guest_applications_admin_ update; CREATE POLICY ... USING (false) (RPC-only), plus add max_participants/status checks to both decide_guest_application and the auto-approve trigger (mirroring join_club_member_checked's capacity guard).

4. MEDIUM (dormant) — role_configs custom permissions have zero server-side backing ​

Evidence:

  • clubs.role_configs JSONB DEFAULT NULL (00013) backs a ClubRoleConfig[] domain type (club.entity.ts:44-81) where each config maps a key: ClubRole (owner|admin|match_director|member) to an arbitrary permissions: ClubPermission[] array — i.e. a club could in principle grant manage_dues to the match_director role (or even member), and hasPermission()/checkPermission('manage_dues') (use-club-role.ts:120) would return true for that member client-side.
  • But every server-side authority check for money/admin actions is hard-coded to the DB role enum, never role_configs:
    • dues table RLS (00011:367-395) — is_club_admin(club_id) only (role IN ('owner','admin')), no dues-manager carve-out at all.
    • session_payments host/admin policies (00059/00145/00303) — despite 00303's own commit message and CLAUDE.md's canon doc both calling the non-admin authority path "club dues-manager," the actual SQL only checks sessions.created_by = auth.uid() (session host, unrelated to any role-config) OR is_club_admin. There is no code path anywhere in supabase/migrations that reads clubs.role_configs to authorize a write.
    • get_club_admin_snapshot (00381:19-32) explicitly documents this gap in its own header comment: "match_director / dues-manager custom roleConfigs … are intentionally NOT admitted here: those are CLIENT-side permission grants … A narrower dues-manager-only … grant is deferred until roleConfigs gets a server-legible representation."
  • Currently dormant because no client mutation ever writes clubs.role_configs (grep -rln "role_configs" packages/features → 0 hits outside read-only mapper/entity code) — every club today has role_configs = NULL, which resolves to DEFAULT_ROLE_CONFIGS (club.entity.ts:54-81), and under those defaults only owner/admin ever carry manage_dues, which already matches the server's hard-coded is_club_admin check. So today there is no live mismatch.

Impact: the moment a "custom role permissions" settings screen is built (a natural next step given the domain model already exists end-to-end on the client), granting manage_dues (or any permission) to a non-admin role will silently fail server-side for every write it's supposed to authorize — the club settings screen will render as if the grant worked (the JSONB write itself would succeed against clubs via the normal admin clubs_update policy) while every downstream action 403s. This is a structural client/server drift, not an active bug — flagged as a design debt to close before or alongside any future custom-roles UI work (docs/architecture/agentic-project-foundation.md ARCH-9 is the named tracking item per 00381's own comment).

5. MEDIUM — Club lifecycle events with no signal at all ​

Evidence: signal coverage confirmed for: role changes (club_role_ changed, 00121), ownership transfer (club_ownership_transferred, 00214), archive (club_archived, 00214), join-request decisions in both directions (club_application_received/approved/rejected 00216, join_request_ decided 00328). Confirmed absent (grep across all migrations for plausible signal type strings, plus reading every trigger on club_members and clubs):

  • No signal on a member leaving or being removed/kicked — the club_members DELETE path (RLS club_members_delete, 00011) has no AFTER DELETE trigger that emits anything to the removed member or to the remaining admins. Compare to the DM domain's member_removed system message (00293_dm_system_messages.sql), which the clubs domain has no equivalent of.
  • No signal on club settings changes — clubs_update (00011, admin- gated) is a bare RLS-permitted column update with no AFTER UPDATE trigger; a dues_amount change (a real money-affecting event for every member) fires nothing. join_policy changes DO trigger a side effect (00321's stranded-request cancellation) but still emit no member-facing signal.
  • No signal on unarchive — archive_club notifies all members (00214), but unarchive_club (same migration) does not, so members who were told "your club was archived" are never told it came back.

Impact: all three are informational gaps, not correctness bugs — a kicked member finds out by the app simply no longer showing the club (a "silent removal" UX, matching the "half-built, no confirmation" complaint pattern flagged elsewhere in this codebase's audits), and members never learn dues went up until the next 회비 cycle surfaces it.

Fix shape: three small AFTER-trigger additions, same shape as 00121/ 00214/00216: club_members AFTER DELETE (skip on cascade-purge, same idiom as 00322) → notify the removed user; clubs AFTER UPDATE OF dues_amount → notify active members; unarchive_club → PERFORM signals_emit mirroring archive_club's loop.

6. LOW — invites table is dead code end-to-end ​

Evidence: public.invites (00010) — direct phone/user invite flow, fully RLS-covered (00011) and FK-hardened (00219) — has a registered adapter (registry.ts:167, createSupabaseInviteRepository) but zero presentation-layer consumers: no InvitePort type is imported anywhere under packages/app/src outside the adapter itself, and no packages/features file references invite creation/lookup. The product's actual join surface is exclusively invite_code (on clubs) + join_policy (00213) + join_open_club/join_club_by_invite_code (00304).

Impact: none today (dead weight, not a bug) — but it's a second positional example (alongside U2's useClubJoinRequests/useDecideJoinRequest dead hooks) of a fully-built vertical slice with no client wiring, worth folding into the same cleanup-or-finish decision U2 already raised for the join-request UI.

7. LOW — Core club tables predate the explicit-GRANT convention ​

Evidence: clubs, club_members, invites, guest_applications have no GRANT SELECT/INSERT/UPDATE/DELETE ... TO authenticated statement anywhere in the migration history (grep -rn "GRANT.*public\. (clubs|club_members|invites|guest_applications)\b" supabase/migrations/*.sql → 0 hits), unlike club_join_request (00213:145, GRANT SELECT, INSERT) which was created after CLAUDE.md's "every new public table needs an explicit data-API GRANT" rule existed. These older tables rely entirely on Supabase's default-privilege auto-grant to anon/authenticated.

Impact: not currently exploitable — every policy on these tables is scoped TO authenticated (never TO anon/public), so even though anon likely has a table-level default GRANT, RLS still returns zero rows for an anon caller (no matching policy applies). This is pure hygiene/consistency debt: if the project's Supabase instance is ever reconfigured with ALTER DEFAULT PRIVILEGES REVOKE ... FROM authenticated (a real hardening step some teams take), these four tables would silently break with no migration recording the intended grant.

Fix shape: a single hygiene migration adding explicit GRANT SELECT, INSERT, UPDATE, DELETE ON public.clubs, public.club_members TO authenticated; (and the narrower correct verb sets for invites/ guest_applications) — mechanical, no behavior change, closes the drift between documented rule and shipped schema.

8. INFO — Server side of U2's "dead join-request pipeline" is solid ​

U2 (UI audit) found useClubJoinRequests/useDecideJoinRequest built but unconsumed by any screen — CRITICAL finding on the UI side. This audit confirms the server side that UI would call is fully built and well hardened:

  • apply_to_club/decide_join_request (00213) — auth-gated, ownership/role checked, FOR UPDATE-locked against double-decide races (status≠'pending' guard makes re-decide impossible — verified no idempotency gap).
  • Archived-club gates added later (00320): apply_to_club rejects applying to an archived club; decide_join_request blocks ACCEPT (not reject) into an archived club, with a FOR SHARE lock closing the TOCTOU window against a concurrent archive_club.
  • Stranded-request cleanup (00321): an AFTER UPDATE OF join_policy trigger auto-cancels pending requests the moment a club leaves approval policy, plus a one-time backfill — closes a real "permanently blocked from re-applying" self-inflicted bug.
  • Applicant notification (00328): decide_join_request now emits join_request_decided to the applicant on both accept and reject (closing the "apply into silence" gap the inbound side, 00216, didn't have).
  • Raw-write lockdown (00329): the original USING-without-WITH-CHECK UPDATE policy (which let any admin raw-PATCH a request, bypassing the archived-club gate and the applicant signal, or reassign user_id to a victim) was closed with USING (false) — RPC-only writes enforced.

Conclusion: the "dead pipeline" is entirely a UI-wiring gap, not a backend one. The wiring-plan for U2 finding #1 needs zero new migrations — just useClubJoinRequests + the export of useDecideJoinRequest from @twomore/app, consumed by a new admin review screen. (Finding #3 above recommends the sibling guest_applications table receive the same 00329- style hardening the review screen work would otherwise not surface.)

Adversarial probes run (explicit list) ​

  1. Non-member reads member list? club_members_select — is_club_member(club_id) only. No escape hatch found; confirmed clubs_select (post-00069/00214) correctly splits member-full-access vs. discoverable-only for non-members.
  2. Removed member still writes? club_members_delete is a hard DELETE (removeMember/leave both call raw .delete()); no soft-deactivate path exists, so a removed member has no row left to write through at all. Not exploitable.
  3. Join request approved twice (idempotency)? decide_join_request locks the row FOR UPDATE and rejects non-pending status — a second decide raises decide_not_pending. Safe.
  4. Invite to archived club? join_club_member_checked (00304) and apply_to_club/decide_join_request (00320) all reject on archived_at IS NOT NULL. The dead invites table (finding #6) has no such check, but it's unreachable from any client path.
  5. Member removed mid-session with active RSVP/holds? Handled by 00322 (release_member_future_seats_on_leave) — future confirmed/waitlisted RSVPs are excused-then-cancelled, releasing the seat and dropping unpaid holds; paid/submitted holds and past history are deliberately left intact (documented tradeoff, reasonable).
  6. Owner leaves without transfer? REPRODUCED — finding #1 (CRITICAL).
  7. Last admin demotes self? Only "last owner" is protected (00311 DELETE trigger + 00121 role-change check); an admin role has no equivalent "last admin" protection, but this is not a genuine gap — a club can have zero admins and still be governed by its owner (owner always retains full authority), so there's no lockout case here, unlike the owner scenario.
  8. Club deleted/archived → orphan chains? purge_archived_clubs (00214, 30-day cron) relies on ON DELETE CASCADE for sessions/club_members/dues/etc.; the 00319/00322 batches specifically retrofitted cascade-skip guards onto BEFORE DELETE triggers that would otherwise abort this cron (block_unsafe_session_delete, block_delete_session_with_pending_refunds, release_member_future_seats_on_leave) — verified all three correctly check EXISTS (SELECT 1 FROM clubs WHERE id = OLD.club_id) before applying their guards. No abort-the-purge regression found.
  9. Guest application to cancelled/full session? REPRODUCED — finding #3 (capacity/status gap, flagged for B2 cross-reference).
  10. SECDEF hygiene sweep: scripted regex sweep of every CREATE [OR REPLACE] FUNCTION matching club/invite/dues/guest_application in name across all 384 migrations for SECURITY DEFINER without search_path in the signature block — 1 hit (increment_club_challenge, 00044), confirmed superseded/dropped by 00068_security_rpcs.sql:11 (DROP FUNCTION IF EXISTS public.increment_club_challenge). No live SECDEF club function is missing search_path=''. REVOKE/GRANT hygiene spot-checked across every function read in this audit (apply_to_club, decide_join_request, transfer_club_ownership, archive_club, unarchive_club, join_open_club, join_club_by_invite_code, update_member_role, purge_archived_clubs, is_club_active) — all correctly REVOKE ... FROM PUBLIC (several also explicitly FROM PUBLIC, anon) + explicit GRANT ... TO authenticated (or service_role/postgres for cron/trigger-only functions).

Data integrity checks ​

  • club_role enum: owner | admin | member (00001) + match_director (00012, ALTER TYPE ... ADD VALUE) — DB-enforced, no unchecked TEXT role column anywhere in this domain.
  • UNIQUE(club_id, user_id) on club_members (00004) — one membership row per user per club, enforced at the DB level (backs every join RPC's ON CONFLICT upsert).
  • dues_manager is not a DB role or column — it exists only as a ClubPermission string ('manage_dues') inside the client-only role_configs JSONB (see finding #4). There is no dues_manager boolean anywhere in the schema; CLAUDE.md's phrase "club dues-manager (canManageDues)" in the session-payments section refers to the client permission check, which for session-payment confirmation is actually backed server-side by session host, not by any role/permission at all (00303) — a naming mismatch between the doc and the actual authority model, worth a doc correction alongside any fix to finding #4.

Canon / rule cross-references ​

  • CLAUDE.md "every new public table needs an explicit data-API GRANT… never rely on the Supabase default-privilege auto-grant" — violated by 4 pre-dating tables (finding #7); all tables created after this rule existed (club_join_request, 00213) comply.
  • CLAUDE.md SECDEF hygiene ("SET search_path = ''` + REVOKE EXECUTE FROM PUBLIC + explicit GRANT") — fully complied with across the live club domain (probe #10).
  • "BEFORE DELETE guards need a cascade-skip" (project memory lesson) — correctly applied in every relevant club-adjacent trigger checked (probe #8); no regression found.
  • AGENTS.md ARCH-9 (named tracking item, per 00381's own comment) — this is the pre-existing tracking item for finding #4's root cause (role_configs needs a server-legible representation).

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