Backend Wiring Plans — 2026-07 Adversarial Audit Synthesis

Status: Archived

Source: docs/audits/blocks/B1–B8 (backend) + backend-rooted findings from U2-clubs.md, U4-matches.md, U8-dues.md (UI audits whose root cause is server-side). Synthesized 2026-07-11. Every item below is a plan, not a diff — no migration or app code has been written yet. Each plan names the exact function/table/policy to touch, so an IMPLEMENT-phase agent can execute directly without re-deriving the audit.

Numbering: current migration HEAD is 00387. New migrations proposed below start at 00388. Numbers are provisional — assign at commit time in case parallel work has advanced HEAD.

Already shipped — do NOT re-plan

These landed as out-of-band hotfixes during/after the audit. Reference only.

| Migration / commit | What it closed | | ---------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --- | ------ | | 00385 + c6cdfe7d (alert-model wiring + TOCTOU restore) | Restored the 00313 attest-vs-expiry FOR UPDATE locking in private.expire_unpaid_session_holds that 00385's "verbatim from 00301" copy had silently dropped (B3 #2). | | 00386_hotfix_portone_rpc_lockdown.sql | public.mark_session_payment_paid_via_portone was anon+authenticated EXECUTE-able with zero internal auth check (B5 #1). Now REVOKE ALL FROM PUBLIC, anon, authenticated; GRANT TO service_role on the live signature. | | 00387_hotfix_venue_rpc_and_avatar_acl.sql | Part 1: ingest_venues_batch/enrich_venues_batch/apply_venue_coords/record_venue_observations/resolve_venue_facts were anon-EXECUTE-able with zero internal auth check (B7 #3, escalates B6 #1). Locked to service_role. Part 2: avatars insert own/avatars update own storage policies had no ownership predicate (B7 #4) — any signed-in user could deface any other user's avatar. Now scoped to pg_catalog.starts_with(name, auth.uid()::text | | '-'). |

Everything below is still open.

SECURITY / ACL

This is the systemic layer everything else sits on top of — sequence it first (see Migration sequence).

S1 — CRITICAL — `REVOKE ... FROM PUBLIC` is a no-op against `anon`; 29% of public SECDEF functions are anon-executable

Defect: docs/audits/blocks/B7-platform-plumbing.md findings #1/#2. Supabase's platform bootstrap grants anon/authenticated EXECUTE on every new public-schema function via explicit per-role pg_default_acl rows, not via the PUBLIC pseudo-role. REVOKE EXECUTE ... FROM PUBLIC (used 96× across migration history) never touches those rows. 00209's own ALTER DEFAULT PRIVILEGES ... REVOKE ... FROM PUBLIC future-proofing clause has the identical blind spot. Live sweep: 40 of 139 public SECDEF functions are anon-executable today; 5 have zero internal auth guard (ingest_venues_batch, enrich_venues_batch, apply_venue_coords, record_venue_observations, resolve_venue_facts — already fixed by 00387, see above). The remaining 35 are either internally guarded (auth.uid() IS NULL early-exit), trigger-only (unreachable regardless of grant), or intentionally public-shaped discovery/share RPCs.

Canonical fix:

Audit query (run first, re-derive the live list — do not trust the B7 snapshot verbatim, HEAD may have moved):

sql

SELECT p.proname, pg_get_function_identity_arguments(p.oid) AS args,
       p.prosecdef, p.provolatile
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) AS acl
JOIN pg_roles r ON r.oid = acl.grantee
WHERE p.prosecdef = true AND n.nspname = 'public'
  AND r.rolname = 'anon' AND acl.privilege_type = 'EXECUTE'
ORDER BY p.proname;

Sweep migration — a DO $$ FOR r IN <above query, excluding an explicit allowlist> LOOP EXECUTE format('REVOKE EXECUTE ON FUNCTION %s FROM PUBLIC, anon;', r.sig); END LOOP $$, mirroring 00209's idiom but correcting it to name anon explicitly (not just PUBLIC). Allowlist (do NOT revoke, document why instead): get_club_discovery_summaries, get_public_share_preview, is_public_discoverable_club (B7 2d — intentionally anon-reachable discovery/share surfaces; add a COMMENT ON FUNCTION noting the exemption is deliberate). Trigger-only functions (signals_on_match_score_call_delete/insert, signals_resolve_sudden_death_on_*) should still be revoked for hygiene even though unreachable.
Fix the default-ACL root cause for future functions: ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM anon; (the FOR ROLE postgres clause matters — pg_default_acl is keyed per-creating-role, and migrations run as postgres).
Canon-doc correction (not a migration): update docs/canon/architecture.md / docs/canon/data-and-hooks.md's SECDEF boilerplate snippet from REVOKE ... FROM PUBLIC; to REVOKE ... FROM PUBLIC, anon; — every future author copies this snippet; leaving it uncorrected guarantees this class of bug recurs.
check_rate_limit_for_key (B7 #9) — fully attacker-controlled p_key/p_max_calls/p_window_seconds with no identity binding, letting a caller trip another user's rate-limit key or defeat their own. Bind p_key to a server-derived namespace (auth.uid()::text || ':' || p_action for authenticated-only actions) instead of trusting the caller's raw key, or restrict p_action to a fixed enum server-side. Fold into this same migration (it's a SECDEF ACL/design fix in the same family).

Blast radius: every public SECDEF function (139). Re-running against a fresh db reset after the migration must show anon EXECUTE only on the 3 documented exemptions. Any client code that legitimately calls one of the 35 guarded functions is unaffected (their own internal auth.uid() check was already the real gate; removing the redundant anon grant changes nothing for an authenticated caller).

Verification: re-run the audit query post-migration — expect exactly 3 rows (the allowlist). SET ROLE anon; SELECT <each of the 5 already-fixed + 35 newly-fixed functions>; should error permission denied for function for all of them. Add a pgTAP test (see S2) so this can't regress silently.

S2 — HIGH — No regression guard for SECDEF ACL hygiene (preventive gap)

Defect: B7-platform-plumbing.md #10. Every fix to date (00209, 00218, 00386, 00387) has been a manual, reactive, one-function-at-a-time patch. Nothing in CI catches the next CREATE OR REPLACE that silently re-opens a function (a signature change resets ACL to the broken default).

Canonical fix: add a supabase/tests/*.sql pgTAP test (or a plain SQL assertion runnable via psql in CI) asserting:

sql

SELECT is(
  (SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
   CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) acl
   JOIN pg_roles r ON r.oid = acl.grantee
   WHERE p.prosecdef AND n.nspname = 'public' AND r.rolname = 'anon'
     AND acl.privilege_type = 'EXECUTE'
     AND p.proname NOT IN ('get_club_discovery_summaries', 'get_public_share_preview', 'is_public_discoverable_club')),
  0::bigint,
  'no unexpected anon-EXECUTE SECDEF functions'
);

Wire into .github/workflows/ci.yml alongside the existing RLS test suite under supabase/tests/, and into the pre-push/yarn check local gate if a local Postgres is available there.

Blast radius: CI only; no runtime behavior change.

Verification: intentionally GRANT EXECUTE ... TO anon on a throwaway SECDEF function locally and confirm the test fails; revoke and confirm green.

S3 — HIGH — `app_config` (plaintext `service_role_key` + `cron_secret`) has full default table grants to `anon`/`authenticated`

Defect: B7-platform-plumbing.md #5. public.app_config (00102) is RLS-enabled with zero policies (currently deny-all, verified live-safe) but never received an explicit narrow GRANT — anon/authenticated both hold raw arwdDxtm at the table-ACL level. One future permissive policy or a stray DISABLE ROW LEVEL SECURITY instantly leaks the service-role key.

Canonical fix: REVOKE ALL ON public.app_config FROM PUBLIC, anon, authenticated; GRANT SELECT ON public.app_config TO service_role; — this table should never have been auto-granted to client-facing roles in the first place given its contents.

Blast radius: every net.http_post-based cron job reads it via get_app_config() (already SECURITY DEFINER, runs as its owner regardless of caller grants) — zero functional change for legitimate readers.

Verification: SET ROLE anon; SELECT * FROM public.app_config; must now error permission denied for table app_config (previously returned 0 rows via RLS, not a grant-level denial — this closes the "one policy away from leak" latent risk, not just the currently-safe state).

S4 — HIGH — 82 of 88 public tables (93%) carry the unmodified Supabase default ACL; no explicit data-API GRANT

Defect: B7-platform-plumbing.md #6, extending B1 #7, B5 #5, B8 #9. Only 6 tables (administrative_divisions, profile_play_regions, surface_catalog, venue_courts, venue_media, venues) have an explicit narrowed GRANT. Every other table — including clubs, club_members, invites, guest_applications (B1 #7), attendance_records, dues, manner_tags (B5 #5), profiles, user_consent, pi_access_log (B8 #9), and everything else — relies entirely on the raw Supabase default (anon+ authenticated full arwdDxtm including DELETE/TRUNCATE). Not currently exploitable (RLS is present with ≥1 policy on 86/88 tables — app_config and signals_archive are the deny-all exceptions, S3 covers the first), but this is a one-RLS-bug-away posture across nearly the entire schema, and directly violates the project's own "every new public table needs an explicit data-API GRANT" rule at 7% compliance.

Canonical fix: one bulk migration, generated (not hand-typed) from information_schema.tables, issuing per-table:

sql

REVOKE ALL ON public.<table> FROM PUBLIC, anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.<table> TO authenticated;

for every table where authenticated is the intended data-API role (the common case — RLS does the row-level narrowing). For service_role-only or postgres/cron-only tables (app_config, signals_archive, pi_access_log), narrow the verb set per S3's pattern instead. Exclude the 6 already-compliant tables (idempotent no-op if included, but cleaner to skip). Do not grant TRUNCATE/REFERENCES/TRIGGER to authenticated anywhere — those aren't needed by any legitimate client write path.

Blast radius: all 82 tables, i.e. effectively the whole app. This is the highest-blast-radius single migration in this plan — sequence it carefully (after S1/S2 land and are verified, so the team has fresh confidence in the sweep pattern) and validate against the full yarn check + a local db reset + smoke of core flows (RSVP, dues mark-paid, club join, match score submit) before considering it safe to push. RLS is the real gate today, so no legitimate call should observe any behavior change — this migration should be a pure no-op for every existing client code path. Any regression signals a table where the app was accidentally relying on an anon-level grant it shouldn't have needed (a bug to find, not paper over).

Verification: SELECT count(*) FROM pg_class c WHERE relkind='r' AND relnamespace='public'::regnamespace AND EXISTS (SELECT 1 FROM aclexplode(c.relacl) a JOIN pg_roles r ON r.oid=a.grantee WHERE r.rolname='anon'); should drop from 82 to 0 (or to the small number of tables where anon is genuinely intended, if any are found during the generation pass — audit before assuming zero). Run the full local scenario suite (node scripts/run-scenario.mjs live_showcase) post-migration as a smoke test given the blast radius.

CLUBS

C1 — CRITICAL — Owner account-deletion permanently locks the club; ghost members never removed; account deletion never releases session/dues/payment state

Defect: B1-clubs-backend.md #1/#2 + B8-auth-identity-backend.md #3 (same root design choice, club-domain and session-domain halves of one gap, explicitly meant to be closed together per B8's own cross-reference). delete_account_atomic (00186) and anonymize_withdrawn_profiles (00157/00184) never touch club_members, rsvps, dues, session_payments, elo_history, manner_tags, attendance_records, member_reports, DM tables, or signals. 00322's own header names this exact gap ("account deletion ... is handled separately") and it was never implemented. Concretely: a sole-owner club becomes permanently unmanageable the moment that owner deletes their account (no RPC path lets anyone become owner — transfer_club_ownership requires the caller to currently hold role='owner'); any deleted member's club_members row stays is_active=true forever, permanently consuming max_members capacity; a deleted user's future confirmed RSVPs never release (no waitlist promotion, session can be stuck at locked/full capacity indefinitely); their unpaid dues rows stay owed to a '탈퇴한 회원' sentinel forever.

Canonical fix: one migration, two parts:

New trigger AFTER UPDATE OF deleted_at ON public.profiles (fires when OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL) calling a new private.deactivate_departed_member_state(p_user_id uuid) SECDEF function (SET search_path = '', owned by postgres/trigger-only, no client GRANT needed) that, for every club_members row where user_id = p_user_id AND is_active = true:
- Sets is_active = false (first legitimate writer of this state — audit whether any other code path assumes is_active=false never happens for a live member; B1's own probe #2 confirms removal is currently a hard DELETE, so this is a genuinely new state transition).
- For the sole-owner case specifically: before deactivating, check EXISTS (SELECT 1 FROM club_members WHERE club_id=X AND role='owner' AND user_id=p_user_id AND is_active=true); if true, either (a) promote the longest-tenured active admin to owner (mirrors transfer_club_ownership's effect, UPDATE club_members SET role='owner' WHERE club_id=X AND user_id = <chosen admin>), or (b) if no admin exists, call the existing archive_club-equivalent logic to archive the club (reuse archive_club's notification-loop shape). Recommend (a)-then-fallback-(b) so a club with any surviving admin never needs archiving. This directly reuses B1's proposed fix shape.
- Calls the existing private.release_member_future_seats_on_leave logic (already built for the club_members DELETE path, 00322) for the user's future confirmed/waitlisted RSVPs across all clubs — either by refactoring it into a callable helper both the DELETE trigger and this new UPDATE trigger invoke, or by duplicating its body (prefer the refactor for single-source-of-truth).
- Transitions the user's outstanding dues rows (any club, status IN ('unpaid','partial')) to 'waived' with a system note (mirrors U8 #4's proposed fix, closes the immortal-dues case for the deletion path specifically — the general ex-member case is handled separately by D-series below).
Extend delete_account_atomic itself (rather than relying solely on the trigger, since the trigger fires on the same transaction) — no additional change needed if the trigger approach is used, since delete_account_atomic already sets deleted_at, which fires the new trigger atomically in the same transaction. Prefer the trigger approach over inlining into delete_account_atomic directly, so anonymize_withdrawn_profiles (the 30-day-later hard-anonymize cron, which does NOT re-set deleted_at — it's already set) doesn't need its own duplicate logic.

Blast radius: club_members, rsvps, dues, session_payments (indirectly, via RSVP release cancelling unpaid holds — reuses existing sync_session_payment_on_rsvp trigger behavior), signals (existing notification triggers on role change / RSVP cancel fire naturally as a side effect — verify they don't spam a just-deleted user's own device, since push_token is already nulled by zero_pii_on_soft_delete in the same transaction, so no PII leaks, but confirm no error is raised sending to a null token). Every countActiveMembers/max_members capacity check (private.join_club_member_checked) benefits immediately once ghost rows stop counting.

Verification: local probe — create a sole-owner club, 2 other members, one with a future confirmed RSVP and unpaid dues; call delete_account_atomic() as the owner; assert (a) an admin (if one existed) or the archived state took over ownership, (b) the deleted owner's club_members.is_active = false, (c) the deleted member's future RSVP is released/waitlist-promoted, (d) their dues rows are 'waived'. Re-run with no surviving admin to confirm the archive fallback. Add as a permanent scenario in scripts/run-scenario.mjs (account_deletion_lifecycle chain) given the multi-domain blast radius.

C2 — HIGH — `guest_applications` missing the `club_join_request`-style RPC-only lockdown; no capacity/status gate

Defect: B1-clubs-backend.md #3. guest_applications_admin_update (00017) has a USING clause but no WITH CHECK — an admin/match_director can raw-PATCH status='approved' directly, bypassing decide_guest_application entirely: the applicant gets notified (the AFTER UPDATE signal trigger fires regardless of write path) but no RSVP row is ever created (the RPC's INSERT ... rsvps ... ON CONFLICT never runs), so they're told they're in but don't appear on the roster. Separately, neitherdecide_guest_application nor private.auto_approve_guest_application_if_needed (00225) checks the session's max_participants or status (cancelled/completed) before upserting a confirmed RSVP.

Canonical fix: mirror 00329_audit_batch21_join_request_update_with_check.sql exactly:

sql

DROP POLICY guest_applications_admin_update ON public.guest_applications;
CREATE POLICY guest_applications_admin_update ON public.guest_applications
  FOR UPDATE USING (false);  -- RPC-only, same idiom as club_join_request

Then add capacity/status guards to both decide_guest_application and private.auto_approve_guest_application_if_needed, mirroring private.join_club_member_checked's max_members check: before the RSVP upsert, SELECT status, max_participants, (SELECT count(*) FROM rsvps WHERE session_id=X AND status='confirmed') INTO ... and raise a friendly error (or silently route to waitlisted instead of confirmed, matching assign_waitlist_position's own behavior) if the session is cancelled/completed, or waitlist if at capacity rather than erroring.

Blast radius: guest_applications, rsvps. The sanctioned UI (decide_guest_application RPC call sites) is unaffected since it already goes through the RPC — only a raw-PATCH bypass loses capability, which is the point.

Verification: attempt a raw .from('guest_applications').update({status: 'approved'}) as an admin — expect RLS rejection. Call decide_guest_application on an already-full/cancelled/completed session — expect either a friendly error or a waitlisted RSVP, never an over-capacity confirmed row.

C3 — MEDIUM — Club lifecycle events with no signal: member-left/removed, dues_amount change, unarchive

Defect: B1-clubs-backend.md #5. No signal fires when a member leaves or is removed (club_members DELETE has no AFTER trigger notifying anyone), no signal fires on a clubs.dues_amount change (a real money-affecting event), no signal fires on unarchive_club (members who were told "archived" are never told it came back).

Canonical fix: three small additive triggers/calls, same shape as the existing 00121/00214/00216 signal wiring:

AFTER DELETE ON public.club_members → new trigger (skip on cascade-purge, same IF NOT EXISTS (SELECT 1 FROM clubs WHERE id = OLD.club_id) THEN RETURN OLD guard idiom as 00322) → signals_emit('club_member_removed', ...) targeted at the removed user (only when the delete was admin-initiated removal, not self-leave, if the product wants to distinguish — otherwise fire for both, differentiated by payload).
AFTER UPDATE OF dues_amount ON public.clubs (guard: OLD.dues_amount IS DISTINCT FROM NEW.dues_amount) → signals_emit('club_dues_amount_changed', ...) fanned out to active members (loop pattern matches archive_club).
Extend unarchive_club (same migration/function family as archive_club, 00214) with the same notify-all-members loop archive_club already has.

Blast radius: signals table growth only; no read-path change (existing signal-routing/notification-center machinery consumes any new type automatically once i18n copy + signal-routes.ts branch exist — client-side follow-up, out of this backend plan's scope but flag for the DOC phase).

Verification: remove a member, change dues_amount, unarchive a club — assert exactly one new signals row per event with the correct dedup_key/ recipient set.

C4 — LOW (defer) — `role_configs` has zero server-side backing

Defect: B1-clubs-backend.md #4. clubs.role_configs JSONB is a client-only permission model (ClubPermission[] per role); every server-side money/admin authority check hard-codes owner/admin/session-host, never reads role_configs. Currently dormant (no client mutation ever writes role_configs, so no live club has a non-default config) — already tracked as AGENTS.md ARCH-9, per 00381's own comment. Not re-planned here as an active migration; note only so the synthesis is complete. When a custom-roles settings UI is scoped, its plan must include a server-legible role_configs read at every authority-check site currently hard-coded to is_club_admin/session-host (dues RLS, session_payments host/admin policies, others enumerated in B1 #4).

SESSIONS / RSVP

SS1 — CRITICAL — Public pickup-preview screen can never load for non-members (its entire target audience)

Defect: B2-sessions-backend.md #1. The only sessions SELECT policy (sessions_select, 00011) is TO authenticated USING (public.is_club_member(club_id)). PublicPickupPreviewScreen (/sessions/[id]/preview, explicitly built for non-members/unauthenticated visitors from discovery) reads via useSessionQuery → session.supabase.ts findById, a raw table read subject to this policy verbatim — so it renders "존재하지 않는 모임" for exactly the audience it exists to serve. Discovery summary RPCs (get_club_discovery_summary, get_public_share_preview) already correctly bypass this via SECURITY DEFINER with a visibility='public' AND public_invite_enabled=true predicate — the gap is specifically the feed→detail hop.

Canonical fix: add a second permissive sessions_select policy mirroring the discovery RPCs' own predicate (Postgres ORs multiple permissive policies):

sql

CREATE POLICY sessions_select_public ON public.sessions
  FOR SELECT TO authenticated, anon
  USING (visibility = 'public' AND public_invite_enabled = true);

Decide whether anon (fully unauthenticated) should be included — the screen's own CTA state machine has an unauthenticated → "로그인하고 신청하기" branch, implying it's meant to render for logged-out visitors too; if so, sessions needs the explicit-GRANT treatment (S4) to actually cover anon at the table-ACL layer as well as RLS, since anon is not the default-covered role in most of this schema. If unauthenticated preview is out of scope, keep TO authenticated only and treat the "로그인하고 신청하기" CTA branch as forward-looking UI, not a contradiction to resolve here.

Blast radius: sessions table reads. Any findByIds bulk path that mixes own-club + public sessions also benefits (same predicate now covers both). No write-path change.

Verification: as a non-member test user (or anon, per the scope decision above), SELECT * FROM sessions WHERE id = <public pickup session id> should now return the row; a private/non-invite session should still return zero rows for the same caller.

SS2 — CRITICAL/HIGH — Voluntary RSVP cancellation never promotes the waitlist

Defect: B2-sessions-backend.md #2. The only two live promotion paths are narrow (fee-hold-idle-expiry cron; mid-session forfeit_session substitute). The general-purpose promotion logic (manageRsvpUseCase.handleCancel) has zero consumers — the actual useCancelRsvp mutation calls rsvps.cancel() directly with no waitlist read at all, and even if wired up, the promotion write would fail RLS for a non-admin canceller (writing a different user's RSVP row). Net effect: a voluntary cancel (the overwhelmingly common cancellation shape) reopens the seat to anyone tapping RSVP again, not the next waitlisted member — the waitlist is decorative for this path despite docs/canon/sessions.md and the UI presenting it as ordered/positional.

Canonical fix: add a new AFTER UPDATE OF status ON public.rsvps trigger (or extend the existing autolock_session_on_capacity, 00312, which already fires on this same event) — when a row transitions confirmed → cancelled on a session with status IN ('open','locked') and a non-empty waitlist, atomically promote the lowest waitlist_positionwaitlisted row to confirmed via a SECDEF helper private.promote_next_waitlisted(p_session_id uuid) (bypasses RLS by design, same pattern as forfeit_session's auto-substitute). This closes the gap without any client change — session_waitlist_promoted signal wiring already fires correctly on any waitlisted → confirmed transition (verified working, just rarely triggered today), so no signal-side change needed. Separately: delete the dead manageRsvpUseCase/waitlist.rules.ts promotion code (or leave it and document clearly it's unused) — the DB trigger supersedes it as the actual mechanism.

Blast radius: rsvps, sessions (via the existing autolock interaction — verify promoting a waitlister doesn't re-trigger autolock_session_on_capacity into flipping the session back to locked incorrectly; the promoted row should be counted in the same statement's capacity check). session_payments (if the session is fee-gated, the promoted user needs a hold created — confirm sync_session_payment_on_rsvp fires correctly for a trigger-driven UPDATE the same as a client-driven one; it should, since it's itself a trigger on the same table).

Verification: local probe — session at capacity with 1 waitlisted user (known waitlist_position), cancel a confirmed member's RSVP as that member (not an admin) — assert the waitlisted user's row flips to confirmed in the same transaction, a session_waitlist_promoted signal fires, and (for a fee session) a session_payments hold is created for the promoted user.

SS3 — HIGH — Capacity-gate TOCTOU race under concurrent last-seat RSVPs

Defect: B2-sessions-backend.md #3. private.assign_waitlist_position (00312) reads max_players then COUNT(*) ... status='confirmed' with no lock — two concurrent inserts for different users on the last open seat can both read v_confirmed = max - 1 under READ COMMITTED and both proceed to 'confirmed', oversell by the degree of concurrency. Contrasts with the migration's own stated goal of closing "RSVP OVERBOOKING."

Canonical fix: add SELECT id FROM public.sessions WHERE id = NEW.session_id FOR UPDATE immediately before the count in private.assign_waitlist_position, serializing concurrent RSVP writes on the same session row (cheap — matches the lock-ordering discipline already established in 00313's expire_unpaid_session_holds comment: "codebase-wide order rsvps → session_payments").

Blast radius: rsvps INSERT/UPDATE-of-status path only. Adds row-lock contention on sessions for concurrent RSVP writes to the same session — negligible at amateur-club scale, worth a one-line comment noting the tradeoff was consciously accepted.

Verification: two concurrent transactions (open in separate psql sessions or a scripted pg_advisory test) both attempting to confirm the last seat — assert exactly one lands confirmed, the other waitlisted, never both confirmed.

SS4 — HIGH — Session status machine has no positive transition table (blocklist, not allowlist)

Defect: B2-sessions-backend.md #4. private.guard_session_status_transition (00319) is a two-rule blocklist (draft can't leave draft once set; completed is terminal). cancelled → completed and any state-skip (open → completed, open → in_progress skipping locked) are unblocked — only which button the client renders gates them today. A forced cancelled → completed would re-run the full 00384 completion sweep against a session whose RSVPs/payments were already unwound by the cancel path, polluting attendance/rating history.

Canonical fix: replace the two-rule blocklist with an explicit allowlist keyed on (OLD.status, NEW.status), matching the enum's intended graph: draft→open→locked⇄open→in_progress→completed, plus →cancelled from any non-terminal state. Any transition not in the allowlist (and not a same-state no-op) raises. Same function (private.guard_session_status_transition), same trigger wiring — no new trigger needed.

Blast radius: every legitimate sessions.status write path (useStartSession, useEndSession, cancel_session_as_host, the auto-advance cron) — audit each against the allowlist before shipping to confirm none needs a transition not in the graph (the audit's own read confirms every legitimate transition today is a strict subset).

Verification: attempt each of the two named illegal transitions (cancelled→completed, open→completed direct-skip) via a raw .update({status}) — both must now raise. Every existing scenario-seeded flow (scripts/run-scenario.mjs live_showcase) must still pass unmodified.

SS5 — MEDIUM — No status gate on RSVP-confirm for a locked/cancelled/completed session

Defect: B2-sessions-backend.md #5. None of the three BEFORE triggers gating a confirm (rsvps_enforce_tier, rsvps_enforce_cooldown, assign_waitlist_position) read sessions.status. Masked in practice because a cancelled/completed session usually has 0 confirmed RSVPs, but nothing stops a stale client/race/direct-API call from creating a fresh confirmed RSVP on an already-terminal session.

Canonical fix: extend private.assign_waitlist_position (already the shared BEFORE-trigger seam) with a SELECT status FROM sessions WHERE id = NEW.session_id; IF v_status NOT IN ('open','locked') THEN RAISE EXCEPTION ... HINT ..., mirroring the tier/cooldown guards' error shape so the client can surface a specific toast. Bundle with SS3 (same function, same migration).

Blast radius: rsvps INSERT/UPDATE-of-status. No legitimate path is affected (confirm attempts on open/locked sessions are unaffected).

Verification: attempt an RSVP confirm on a cancelled/completed session via raw API — expect a raised exception with a client-parseable hint.

SS6 — MEDIUM — `max_players` decrease doesn't reconcile existing over-quota seats or re-evaluate lock status

Defect: B2-sessions-backend.md #6. private.autolock_session_on_capacity only fires AFTER INSERT OR UPDATE OF status ON rsvps, never on a sessions UPDATE. Lowering max_players below the current confirmed count via the ordinary session-edit flow leaves the open/locked label stale and the over-quota members silently grandfathered (new RSVPs are correctly gated against the new max; only the existing count is unreconciled).

Canonical fix: add a new AFTER UPDATE OF max_players ON public.sessions trigger that re-runs the same open/locked comparison autolock_session_on_capacity already performs (extract the comparison into a shared helper both triggers call, or duplicate the ~5-line check). Do not retroactively demote existing confirmed members (grandfather them, per the audit's own recommendation) — only fix the status label.

Blast radius: sessions UPDATE path (session-edit flow). No RSVP rows are touched.

Verification: create a session at capacity, edit max_players down below the confirmed count — assert status flips to locked (or whatever the comparison dictates) without touching any rsvps row.

PAYMENTS (session_payments / settle-before-play)

PY1 — CRITICAL — Settle-before-play match gate + finalized-match protection (matches/payments consolidated fix)

Defect: consolidated from B3-payments-backend.md #1, B4-matches-rating-backend.md #1/#2/#3, U4-matches.md #1/#2. Four independently-discovered gaps share one root cause pattern (a "server backstop" keyed to a specific column-list that the actual write path never targets) and must land together:

(B3 #1 / B4 #3(a) / U4 #2) Team-slot swap bypasses settle-before-play.private.enforce_settled_match_participants (00384) is wired BEFORE INSERT OR UPDATE OF participant_ids. private.sync_match_participant_ids (00313) derives participant_ids as a side effect of a team-slot column UPDATE (UPDATE OF team1_player1_id, ...). The app's updateMatchTeams (backing SwapSheet) only ever sets the four team-slot columns, never participant_ids directly — so the settle-before-play trigger never fires on the one write path that most needs it. Empirically reproduced: an unsettled participant can be swapped into a fee-session match with zero rejection.
(B4 #1 / new) submit_match_score has no status guard. The terminal definition (00324) does UPDATE public.matches SET team1_score=..., status='completed' ... WHERE id = p_match_id with no AND status IN ('scheduled','in_progress') predicate — an admin/match_director can silently rewrite an already-completed match's score with zero consent, bypassing the 00308 all-agree correction flow entirely, while apply_match_ratings's idempotency guard silently no-ops the second call — the displayed score and elo_history permanently disagree, with no error and no signal.
(B4 #3(a) / U4 #2, RLS layer) matches_update has no status predicate. The terminal policy (00013) authorizes any owner/admin/match_director UPDATE with no status/WITH CHECK scoping — the RLS surface updateMatchTeams's raw client call rides through, independent of gap #1's trigger-column issue.
(B4 #2 / U4 #1) score_status never reaches 'disputed'.reject_call (00385) deletes the pending call and emits a match_score_disputed signal but never writes matches.score_status = 'disputed' — findDisputedByUser always returns [], so the already-built HomeDisputedAlertBanner/badge branch can never render. Not the same bug class as #1-3 but touches the same function family and should ride the same migration pass.

Canonical fix (single migration, four parts):

sql

-- Part A: close the trigger-column bypass (B3 #1)
DROP TRIGGER trg_matches_settled_participants ON public.matches;
CREATE TRIGGER trg_matches_settled_participants
  BEFORE INSERT OR UPDATE OF
    team1_player1_id, team1_player2_id, team2_player1_id, team2_player2_id,
    participant_ids
  ON public.matches
  FOR EACH ROW EXECUTE FUNCTION private.enforce_settled_match_participants();
  -- name sorts after sync_match_participant_ids alphabetically (already true) —
  -- verify trigger execution order evaluates the RECOMPUTED participant_ids

-- Part B: status guard on submit_match_score (B4 #1)
-- inside private.submit_match_score, change the UPDATE to:
--   UPDATE public.matches SET ... WHERE id = p_match_id
--     AND status IN ('scheduled', 'in_progress');
--   IF NOT FOUND THEN RAISE EXCEPTION 'Only a scheduled or in-progress match
--     can have its score submitted directly — use propose_score_correction
--     for a completed match.' USING ERRCODE = 'check_violation'; END IF;

-- Part C: status predicate on matches_update RLS (B4 #3(a) / U4 #2)
DROP POLICY matches_update ON public.matches;
CREATE POLICY matches_update ON public.matches
  FOR UPDATE
  USING (<existing owner/admin/match_director EXISTS clause> AND status IN ('scheduled', 'in_progress'))
  WITH CHECK (<same clause>);
  -- decide: should a match_director be able to edit an in_progress match's
  -- team slots (mid-session substitute UI) — if yes, keep in_progress in the
  -- allowlist; if team-slot edits should only ever happen pre-start, narrow
  -- to status = 'scheduled' only, matching matches_delete's existing precedent

-- Part D: score_status disputed wiring (B4 #2 / U4 #1)
-- inside reject_call: UPDATE public.matches SET score_status = 'disputed'
--   WHERE id = p_match_id;  (before/alongside the DELETE FROM match_score_calls)
-- inside call_match / propose_score_correction: reset score_status = 'unverified'
--   when inserting a fresh match_score_calls row on a match whose score_status
--   is currently 'disputed'

Blast radius: matches, match_score_calls, rsvps/session_payments (via the settle-before-play trigger's own read of payment status, unchanged). No sanctioned client UI exercises any of the four guarded paths today (score entry only mounts for scheduled matches; swap sheet only mounts for scheduled) — this is a zero-client-change migration. Verify the match-board-screen.tsx eligiblePlayerIds/swap-candidate-pool logic isn't relying on being ABLE to swap into an in_progress match if Part C narrows to scheduled-only.

Verification: re-run B3's exact empirical repro (fee session, one settled + one unsettled RSVP, team-slot-only UPDATE targeting the unsettled player) — expect a raised exception, not a silent 1-row update. Call submit_match_score twice on the same match — second call must raise, not silently no-op with score/rating drift. Attempt updateMatchTeams on a completed match — expect RLS rejection. reject_call → assert matches.score_status = 'disputed' → a fresh call_match → assert it resets to 'unverified'. Confirm HomeDisputedAlertCard now renders in a scenario-seeded dispute (client-side spot check, not just SQL).

PY2 — MEDIUM — `session_payments` UPDATE RLS has no `WITH CHECK`; host/admin can tamper with `amount`/`user_id` via raw update

Defect: B3-payments-backend.md #3. session_payments_updatable_by_admins (00145) and session_payments_updatable_by_host (00303) both omit WITH CHECK, so Postgres reuses USING (which only gates session_id ownership) for the new-row check too — an authorized host/admin can rewrite amount, user_id, portone_payment_id, refund_state via a raw update, not just the fields the app's own markPaid/markWaived send.

Canonical fix: add WITH CHECK to both policies pinning amount/user_id/session_id/portone_payment_id to OLD.<col> (only status/paid_at/method/refund_state/recorded_by/timestamps may move):

sql

CREATE POLICY session_payments_updatable_by_host ON public.session_payments
  FOR UPDATE
  USING (<existing host clause>)
  WITH CHECK (
    <existing host clause>
    AND amount = (SELECT amount FROM public.session_payments sp WHERE sp.id = session_payments.id)
    AND user_id = (SELECT user_id FROM public.session_payments sp WHERE sp.id = session_payments.id)
  );
-- Postgres RLS WITH CHECK doesn't have a direct OLD reference the way triggers
-- do; use a BEFORE UPDATE trigger instead if the subquery-self-join pattern
-- proves awkward — private.protect_columns() (00067's existing helper) may
-- already fit this shape directly: protect_columns('amount', 'user_id',
-- 'session_id', 'portone_payment_id') as a trigger, simpler than RLS WITH CHECK gymnastics.

Prefer the private.protect_columns() trigger route — it's the established codebase pattern (used for profiles, see A2 below) and avoids RLS self-join awkwardness.

Blast radius: session_payments UPDATE path. App's own markPaid/markWaived never send the protected columns — zero functional change for legitimate calls.

Verification: as an authorized host, attempt .update({amount: 999999}) on a payment row they administer — expect revert (if using protect_columns) or rejection (if using WITH CHECK).

PY3 — LOW — No `CHECK (amount >= 0)` on `session_payments`

Defect: B3-payments-backend.md #4. amount INTEGER NOT NULL with no lower bound. Unreachable today via the reserve-then-pay path (always populated from sessions.participation_fee, which does have CHECK >= 0), but combined with PY2 (pre-fix), a negative amount wasn't structurally impossible.

Canonical fix: ALTER TABLE public.session_payments ADD CONSTRAINT session_payments_amount_nonneg CHECK (amount >= 0); — bundle into the same migration as PY2 (small, same table).

Blast radius: none (no legitimate row ever has a negative amount).

Verification: attempt an insert/update with amount = -1 — expect a CHECK violation.

PY4 — MEDIUM — Fee change after holds exist has no reconciliation path

Defect: B3-payments-backend.md #5. sessions.participation_fee has no edit lock and no AFTER UPDATE trigger reconciling already-created session_payments.amount rows. Currently latent (no shipped UI exposes a fee-edit field), but the write path exists end-to-end (adapter→mapper→useUpdateSession), and mark_session_payment_paid_via_portone validates against the current sessions.participation_fee, not the hold's own frozen amount — a future fee-edit UI would create silent audit-trail drift the moment it ships.

Canonical fix: lock participation_fee once any session_payments row exists for the session — add a BEFORE UPDATE OF participation_fee ON public.sessions trigger that raises if EXISTS (SELECT 1 FROM session_payments WHERE session_id = NEW.id), directing the admin to cancel holds first if a fee correction is truly needed. (Alternative: reconcile amount on every existing pending hold when the fee changes — more permissive but riskier for holds already mid-attestation; the lock is the simpler, safer default given no UI needs the edit path today.)

Blast radius: sessions UPDATE path — inert until a fee-edit UI ships, at which point it correctly blocks the edit rather than silently desynchronizing.

Verification: create a session with a fee hold, attempt .update({participationFee: X}) — expect a raised exception once any hold exists; confirm the update succeeds before any hold is created.

PY5 — MEDIUM — Two missing signal emissions in the payment-hold lifecycle

Defect: B3-payments-backend.md #6/#7.

private.session_completed_record_attendance (00384, step (a)) cancels any still-pending hold at session completion (excused, strike 0) but never calls signals_emit — a member who attested/submitted late gets silently RSVP-cancelled with no notification of why.
sync_session_payment_on_rsvp deletes an idle hold on RSVP-cancel but never resolves the session_payment_hold_created:<id> dedup key — self-heals within payment_hold_minutes (≤10 min) via expires_at, so bounded severity, but still a dangling notification for that window.

Canonical fix: add PERFORM public.signals_emit('session_payment_hold_expired', ...) to the 00384 completion-trigger's hold-cancel branch (mirrors the cron path's existing emission); add PERFORM public.signals_resolve_by_dedup_key('session_payment_hold_created:' || id) to sync_session_payment_on_rsvp's cancel-delete branch.

Blast radius: signals table only.

Verification: let a hold survive to session completion (attest late, past idle window, host never confirms) — assert a session_payment_hold_expired signal fires. Cancel an RSVP with an idle hold — assert the corresponding hold_created signal is resolved immediately, not just via natural expiry.

MATCHES / RATING

(PY1 above already covers the three CRITICAL matches findings — B4 #1/#2/#3. Remaining items below are the MEDIUM-and-below matches findings.)

M1 — MEDIUM — RSVP tier gate uses cross-pool `elo_rating`, not a format-aware rating

Defect: B4-matches-rating-backend.md #4. private.rsvps_enforce_tier (00286) reads profiles.elo_rating — documented as "latest rating in the last-played pool," i.e. whichever of singles_elo/doubles_elo the player most recently played, not the rating relevant to the session's actual format. A singles-last player gets gated into a tier-restricted doubles session using their singles rating. apply_match_ratings itself correctly branches on session format for its own tier-change signal — only the RSVP gate has this drift.

Canonical fix: pass sessions.format into rsvps_enforce_tier (already available via a join in the same query) and select singles_elo/doubles_elo accordingly, mirroring apply_match_ratings's existing v_is_doubles branch.

Blast radius: rsvps INSERT/UPDATE path (tier-gated sessions only).

Verification: a player with divergent singles/doubles ELO attempts to join a tier-restricted session of the format their lower rating belongs to — before the fix, gate decision uses the wrong pool; after, it uses the session's actual format.

M2 — MEDIUM — No server-side membership/RSVP check on match participant slots

Defect: B4-matches-rating-backend.md #5. matches.team*_player*_id only requires a valid profiles(id) FK — no check that the four player IDs are actual members of the session's club or confirmed RSVPs of the session. Cross-club/guest rating is intentional by design (#14, not a bug); the gap is narrower — nothing stops an admin-authored match from including a player never even offered a seat in the session.

Canonical fix: add an EXISTS check to matches_insert RLS (and to the team-slot UPDATE path being hardened in PY1 Part C) — EXISTS (SELECT 1 FROM rsvps WHERE session_id = matches.session_id AND user_id IN (team1_player1_id, team1_player2_id, team2_player1_id, team2_player2_id) AND status = 'confirmed') per non-null slot. Bundle into the same migration as PY1 Part C (same policy, same authorization surface).

Blast radius: matches INSERT/UPDATE. Verify no legitimate flow (guest-application-approved players, forfeit substitutes) is excluded — both already produce confirmed RSVP rows before match assignment, so should be covered.

Verification: attempt to create a match including a player with no RSVP on the session — expect rejection.

M3 — LOW-MEDIUM — No round-level player-uniqueness invariant

Defect: B4-matches-rating-backend.md #6. Only (session_id, round_number, court_number) uniqueness is enforced (00162) — nothing prevents the same player appearing in two matches in the same round. The client generator (rotation.rules.ts) is the only guarantee today.

Canonical fix: a BEFORE INSERT OR UPDATE OF team1_player1_id, ... round_number ON public.matches trigger that raises if any of the 4 new player IDs already appears in another matches row with the same (session_id, round_number).

Blast radius: matches INSERT/UPDATE. No legitimate generator output should ever trip this (by construction).

Verification: attempt to insert a second match in the same round reusing a player from an existing match in that round — expect rejection.

M4 — LOW-MEDIUM — Delete orphaned `verify_match_score` legacy RPC

Defect: B4-matches-rating-backend.md #7. public.verify_match_score (00135, wrapping private.verify_match_score from 00068) is superseded by the match_score_calls consensus system, has zero consumers (verifyScore port/adapter method also unused), and its incremental verified_by-append semantics conflict with the consensus path's single-final-agreer write if ever accidentally re-wired.

Canonical fix: DROP FUNCTION public.verify_match_score(...); + DROP FUNCTION private.verify_match_score(...); — one migration. Companion client cleanup (delete verifyScore port method + adapter implementation) is a separate ≤3-file app-code change, not this migration.

Blast radius: none (zero consumers, confirmed by grep).

Verification: yarn typecheck after the companion client cleanup; confirm no remaining references.

M5 — LOW — `search_path=public` instead of `''` on match-domain signal triggers

Defect: B4-matches-rating-backend.md #8. signals_on_match_insert (00385) and signals_on_match_update (00109) both use SET search_path = public. Fold into the broader D5 sweep below (same fix shape, same migration recommended).

ATTENDANCE / TRUST / DUES

D1 — CRITICAL — Attendance strike-ladder notifications resurrect indefinitely; no decay/resolve policy

Defect: B5-attendance-trust-dues-backend.md #2. private.refresh_attendance_state runs on every attendance_records write for a user (including a clean match-derived 'attended' row) and looks up the most recent strike-bearing session ever (not the session that triggered this call), unconditionally re-firing fire_attendance_strike_notifications at thresholds 1/2/3+. signals_emit's dedup-upsert resets acknowledged_at/resolved_at = NULL on every re-fire. Zero resolve call exists anywhere for attendance_warning_cooldown/attendance_flagged_admin. Concrete failure: a member with 2 old strikes plays cleanly for months — every one of those clean sessions re-pops the dismissed warning/flagged alerts back to unread/unresolved.

Canonical fix:

Change refresh_attendance_state's call into fire_attendance_strike_notifications to only fire when the triggering write itself carries strike_value > 0 — pass the actual new record (already available as the trigger's NEW/the calling context), not a fresh "most recent ever" lookup.
Add resolve calls: when a strike ages out of the trailing-20-record window (i.e. the next refresh_attendance_state call for that user no longer finds ≥2/≥3 strikes in the window), call signals_resolve_by_dedup_key for the corresponding attendance_warning_cooldown/attendance_flagged_admin dedup keys.
Reconsider signals_emit's blanket acknowledged_at = NULL, resolved_at = NULL reset on every dedup'd re-fire — for at least these two signal types, a re-fire should bump count without reopening an already-acknowledged/resolved row. This may warrant a p_reopen_on_dedup boolean DEFAULT true parameter on signals_emit itself so callers can opt out per-type rather than a global behavior change.

Blast radius: private.refresh_attendance_state, private.fire_attendance_strike_notifications, signals_emit (if the parameter change is adopted — audit every other signals_emit call site for whether the reopen-on-dedup default should change for them too, or only these two types via the new parameter).

Verification: local probe — user accrues 2 strikes, then 15 clean match-derived attendance writes. Before the fix: each write re-fires/reopens the dismissed signals. After: no re-fire once the trigger no-longer-carries strike_value > 0, and a resolve call fires once the strikes age out of the window.

D2 — CRITICAL — Dues `waived`/`partial` transitions invisible to signals; no audit trail for waive

Defect: B5-attendance-trust-dues-backend.md #3 (server confirmation of U8-dues.md #1/#2). signals_on_dues_update's only branch is IF NEW.status = 'paid' AND OLD.status IS DISTINCT FROM 'paid' — a transition to 'waived'/'partial' never resolves the four dues_* dedup keys and never emits anything (no dues_waived SignalType even exists). Separately (U8 #1, client-confirmed but server-rooted): dues.mapper.ts's toUpdateRow only writes cleared_by/cleared_at if (input.status === 'paid') — a waive carries the admin's identity through the client call but it's silently dropped before the write, even though nothing server-side prevents writing it (this is a client mapper bug, not an RLS/RPC gap — flag for the IMPLEMENT phase to fix packages/app/src/adapters/supabase/dues.mapper.ts alongside the migration, since the column-write capability already exists).

Canonical fix:

Broaden signals_on_dues_update's resolve/emit guard from NEW.status = 'paid' to NEW.status IN ('paid', 'waived', 'partial') for the signals_resolve_by_dedup_key calls (resolving dues_assigned/ dues_due_soon/dues_overdue/dues_severely_overdue on any of the three "no longer actively overdue" transitions).
Add a new dues_waived SignalType (domain entity: packages/app/src/domain/entities/signal.entity.ts) + emit it (medium severity) inside the same trigger's waived branch, alongside the existing dues_cleared_by_admin shape.
(Client-code companion, not a migration) fix dues.mapper.ts:toUpdateRow to write cleared_by/cleared_at whenever clearedByUserId is supplied, regardless of status value; mirror in use-update-dues.ts's optimistic patch.

Blast radius: dues table triggers, signals. Once U2's separate partial-amount schema fix lands (client-only per U2's own framing — no backend action needed here beyond what D2 already does), a partial payment's remaining balance should drive whether dues_overdue re-escalates rather than a hard stop — note this dependency for the future migration that adds dues.paid_amount (see D6 below), don't block D2 on it.

Verification: waive a severely-overdue dues row — assert (a) cleared_by/cleared_at populate, (b) a dues_waived signal fires, (c) the previously-emitted dues_severely_overdue signal resolves (resolved_at non-null). Repeat for partial.

D3 — CRITICAL — Five divergent "overdue" definitions; canonical definition + ex-member exclusion

Defect: B5-attendance-trust-dues-backend.md #6 (extends U8-dues.md #2, which found 4; B5 found a 5th dead one) + B5 #4 / U8 #4 (ex-members' immortal dues). Five surfaces compute "overdue" with genuinely different status sets, time granularity, and scope: get_home_signals (00103, unpaid+partial, strictly-past calendar month), get_club_admin_snapshot (00381, unpaid only, strictly-past calendar month, deliberately not matching the player-side definition per its own comment), signals_tick_dues (00108, unpaid only, dues_day-precise days-past — can fire within the current calendar month), SummaryCard (client, no overdue concept at all), and the dead generate_dues_reminders() cron (unpaid-only, current-month-only, writes to the zero-consumer club_alerts table). Concrete failure: a member can be receiving a critical-severity push (dues_severely_overdue) while the AdminTab's own "start here" attention strip shows "회비 미납 0건" for the exact same row, because the admin-snapshot definition categorically excludes the current calendar month. Separately: no definition anywhere joins club_members.is_active — a departed member's unpaid dues escalate forever.

Canonical fix:

Extract a single SQL helper implementing signals_tick_dues's dues_day-precise formula as ground truth (the only definition already driving real push notifications):

sql

CREATE OR REPLACE FUNCTION private.dues_days_past(p_year int, p_month int, p_dues_day int)
RETURNS int LANGUAGE sql STABLE SET search_path = '' AS $$
  SELECT (CURRENT_DATE - make_date(p_year, p_month,
    LEAST(p_dues_day, EXTRACT(DAY FROM (make_date(p_year, p_month, 1) + interval '1 month - 1 day'))::int)))::int;
$$;

(or a view private.dues_with_status joining dues + clubs.dues_day + club_members.is_active — either shape works; the view is likely cleaner given 3 call sites need the same joined shape).

Rewire get_home_signals and get_club_admin_snapshot's overdue counts to read from the same helper/view instead of their own calendar-month shortcuts — this directly fixes the "AdminTab shows 0 while a critical push already fired" bug.
Join club_members.is_active into the same helper — excludes departed members from every overdue count and from signals_tick_dues's escalation loop (mirrors the 00322 seat-release pattern). Decide product behavior for existing departed-member unpaid rows: either auto-waive on the exclusion-filter migration (one-time backfill, consistent with C1's account-deletion fix) or simply stop escalating them going forward (cheaper, leaves historical debt visible to the admin if they look, just not pushed).
Delete generate_dues_reminders() + its pg_cron job (job 25's first half, 0 21 * * *) — dead output, zero UI consumer (club_alerts), one less divergent definition to maintain.
SummaryCard (client-side, no migration) should ideally read the same canonical definition rather than inventing a sixth — flag for a follow-up client task, out of this backend plan's scope.

Blast radius: get_home_signals, get_club_admin_snapshot, signals_tick_dues, pg_cron job 25. High-visibility surfaces (Home attention badge, AdminTab strip) — verify both against the same live club fixture pre/post migration to confirm they now agree.

Verification: seed a club with dues_day=5, a member unpaid since the 1st, check on the 10th (still same calendar month) — assert get_club_admin_snapshot's overdue_dues_count now includes this row (previously excluded it). Seed a departed member with unpaid dues — assert they no longer appear in any overdue count and signals_tick_dues no longer escalates them.

D4 — MEDIUM — `dues_day` is not snapshotted per period; mid-cycle edits retroactively reinterpret past due dates

Defect: B5-attendance-trust-dues-backend.md #7. dues has no due_date column — every consumer derives it live from the currentclubs.dues_day. A 총무 changing dues_day from 25 to 5 mid-cycle instantly reinterprets every past unpaid row's due date, silently, with no signal or confirmation.

Canonical fix: decide-and-document is acceptable if snapshotting is deemed unnecessary product-wise, but the recommended fix (matching how session_payments.amount is frozen at hold-creation, B3's own precedent): add due_date DATE to dues, populate it at row-generation time (handleGenerateDues's insert / any future auto-generation path) via the D3 helper, and have signals_tick_dues/the D3 helper read the frozen column instead of recomputing from live clubs.dues_day. If the team instead decides retroactive reinterpretation is intentional, add a confirmation step to the dues_day settings-change UI (client-side, not this plan's scope) rather than leaving it fully silent either way.

Blast radius: dues schema (new column, backfill for existing rows using the dues_day value at the time — best-effort, use current value as the backfill default since historical value is unrecoverable), D3's helper (reads the new column instead of a live join once this lands — sequence D4 after D3, or fold together if convenient).

Verification: generate dues, change clubs.dues_day, confirm the already-generated row's due_date/overdue status is unaffected by the change.

D5 — MEDIUM-HIGH — Phantom attendance: `total_sessions_confirmed` bumps unconditionally regardless of tracking-enabled or whether the session played

Defect: B5-attendance-trust-dues-backend.md #8, self-documented as deferred in 00318 and confirmed still open on HEAD. private.session_completed_record_attendance's total_sessions_confirmed increment fires before the attendance_tracking_enabled gate and doesn't consult v_played at all — a club with tracking off, or a session that never actually started (rained out, auto-advanced to completed by the hourly cron with nobody having shown up), still inflates every confirmed member's global trust-tier session count with zero possibility of an offsetting no-show penalty.

Canonical fix: move the total_sessions_confirmed increment inside the same attendance_tracking_enabled gate that already guards the attendance_records write, and additionally gate on v_played (already computed in the same function body: NOT v_has_matches OR EXISTS(...)).

Blast radius: profiles.total_sessions_confirmed (trust-tier input). Verify private.recalculate_trust_tier's v_sessions input still behaves sensibly for clubs with tracking off (their members simply stop accruing this specific counter — confirm this doesn't unintentionally freeze anyone's tier progression who was relying on it, which may need a product decision alongside the migration).

Verification: a club with attendance_tracking_enabled=false, a session reaches completed — assert total_sessions_confirmed no longer increments for its confirmed RSVPs. A tracking-on session that never had any match activity (v_played=false) — same assertion.

D6 — MEDIUM — Bank-account / payment-instructions surface has no backing schema

Defect: U8-dues.md #3 (backend-rooted: the club entity has zero bank-account field; this is a schema gap, not just a missing screen). A member opening 회비 sees "미납 ₩30,000" with zero actionable payment information — no account number, no KakaoPay/Toss link, not even which of the 5 already-modeled PAYMENT_METHODS (bank|cash|kakaopay|toss|other, dues.entity.ts) the club prefers.

Canonical fix: add clubs.dues_payment_info JSONB (or discrete columns — dues_bank_name, dues_account_number, dues_account_holder, dues_payment_method) settable via the existing admin-gated clubs_update RLS policy (no new policy needed — this is an ordinary club-settings field, same authorization surface as dues_amount/dues_day). No PII concern (this is the club's own collection info, not a member's personal data) — does not need JSONB-PII-avoidance treatment. Entity/mapper/settings-screen field/member-view card are client-side follow-up work, out of this migration's scope but should be scoped together per feedback_entity_migration_checklist.md.

Blast radius: clubs schema only (additive column, nullable, no backfill needed — absent means "not configured," client renders the existing "contact your 총무" fallback).

Verification: yarn check:supabase-types after the migration to confirm generated types pick up the new column; a settings-screen write round-trips correctly (client-side, DOC-phase follow-up).

D7 — LOW-MEDIUM — No `AFTER DELETE` recalc trigger on `manner_tags`

Defect: B5-attendance-trust-dues-backend.md #10. manner_tags has an AFTER INSERT trust-tier recalc trigger but no AFTER DELETE — a tagger revoking their own tag within the 24h window doesn't retroactively lower the taggee's cached trust_tier until an unrelated event happens to re-trigger recalculation.

Canonical fix: add a mirroring AFTER DELETE ON public.manner_tags trigger calling private.recalculate_trust_tier(OLD.taggee_id).

Blast radius: manner_tags DELETE path (the existing 24h-self-delete RLS policy) only.

Verification: tag a user, delete the tag within 24h — assert profiles.trust_tier recomputes immediately rather than waiting for an unrelated event.

D8 — MEDIUM — 20 SECDEF functions use `search_path=public` instead of `''`

Defect: B5-attendance-trust-dues-backend.md #9, broader than B2's single-instance flag (signals_on_rsvp_update) and B4's two-instance flag (signals_on_match_insert/update — M5 above). Full live list: get_club_analytics, get_club_discovery_summaries, get_club_growth_analytics, get_season_standings, signal_preferences_seed_defaults, signals_acknowledge_visible_unread, signals_archive_sweep, signals_on_club_alert_delete/insert, signals_on_club_post_insert, signals_on_dues_delete, signals_on_dues_insert, signals_on_match_insert/update, signals_on_rsvp_update, signals_on_session_update, signals_on_weather_warning_activated, signals_purge_sweep, signals_tick_dues, signals_unread_visible_count. Note the internal inconsistency: signals_on_dues_insert/_delete use search_path=public while their sibling signals_on_dues_update correctly uses ''.

Canonical fix: one migration, ALTER FUNCTION <name>(<args>) SET search_path = ''; for all 20 (plus the 2 from M5 — 22 total, bundle together), fully qualifying any previously-bare enum casts each function's body uses (e.g. 'matchup'::public.signal_category). Mirrors the 00152/00194 hardening passes' own pattern.

Blast radius: 22 functions, all already schema-qualify their table references (confirmed by both audits) — this is a pure hygiene change, no behavior difference expected.

Verification: SELECT proname, proconfig FROM pg_proc WHERE proname = ANY(ARRAY[...]) post-migration — every row's proconfig must include search_path="". Re-run each function's existing test coverage (if any) to confirm no behavior regression from the qualified-enum-cast changes.

D9 — Deferred (roadmap, not a migration) — Phase 6 spec vs. reality: 총무 endorsement / manual demotion unimplemented

Defect: B5-attendance-trust-dues-backend.md #11. docs/specifications/attendance-and-reviews.md's "총무 endorsement" (High weight, manual boost) and "총무 manual action" (demotion trigger + dashboard reason surface) have zero implementation — private.recalculate_trust_tier has no admin-boost/admin-demote input path at all.

Not planned as a migration here — this is materially larger feature work (new RPC(s), new UI, dashboard reason surface), not a small additive fix. Flag for a dedicated planning pass when the Phase 6 trust-tier feature is prioritized. Related: D10 below (vestigial signal types this feature would need) should be resolved in the same pass.

D10 — LOW (decision needed, fold into D9 or delete) — 4 vestigial SignalTypes with zero emitters

Defect: B5-attendance-trust-dues-backend.md #12. trust_tier_changed, manner_tag_received, late_cancel_penalty_applied, no_show_reported are declared in signal.entity.ts's SignalType union with zero emitters anywhere (no migration, no i18n, no route).

Canonical fix: either wire real emitters when the corresponding feature work ships (trust_tier_changed naturally pairs with D9; manner_tag_received could emit from the existing manner_tags AFTER INSERT trigger today, independent of D9), or remove the dead type declarations. Not urgent — bundle into whichever of D9/D7 lands first, or a dedicated small cleanup migration if neither is scheduled soon.

VENUES

V1 — CRITICAL — `venue_corrections` RLS/GRANT bypass allows raw-insert fact poisoning + batch-abort DoS

Defect: B6-venues-weather-backend.md #2. venue_corrections_insert_own (00339) only constrains submitted_by = auth.uid() — not status, field, or proposed_value. Any authenticated user can raw-INSERT status='applied' with an arbitrary unvalidated proposed_value, bypassing submit_venue_correction's entire validation stack (rate limit, bounds checks, reputation gate, anti-walk guard, agreement threshold). The "HUMAN CORRECTIONS WIN" step in resolve_venue_facts picks up any status='applied' row unconditionally on the next resolve call for that venue — silent fact poisoning. Separately, an unguarded cast in the resolver ((hv #>> '{}')::int) on a malformed raw-inserted value raises a hard exception with no EXCEPTION handling in the FOREACH vid IN ARRAY p_venue_ids LOOP, aborting the entire batch (real pipeline chunks at 400 venues per call; the --sql migration-generation path is unchunked, larger blast radius).

Canonical fix:

REVOKE INSERT ON public.venue_corrections FROM authenticated; and DROP POLICY venue_corrections_insert_own; — force all writes through submit_venue_correction (already SECURITY DEFINER, does its own INSERT with definer privileges, unaffected by the client-facing revoke).
Wrap each venue's resolution inside resolve_venue_facts's FOREACH vid IN ARRAY p_venue_ids LOOP body with BEGIN ... EXCEPTION WHEN OTHERS THEN CONTINUE; (log via RAISE WARNING before continuing) so one malformed row can never abort an entire batch.

Blast radius: venue_corrections (write path narrows to RPC-only — no legitimate client ever wrote directly, per the audit's own trace of all consumers). resolve_venue_facts (adds exception isolation — verify no currently-passing scrape/enrich test relies on a full-batch abort as "working as intended"; it should not).

Verification: attempt a raw .from('venue_corrections').insert({status: 'applied', ...}) as an authenticated non-privileged user — expect RLS rejection. Seed one malformed venue_corrections row (non-numeric value for a numeric field) among a batch of 10 valid venue IDs, call resolve_venue_facts — assert the other 9 resolve correctly and the bad one is skipped with a warning, not a full-batch rollback.

V2 — HIGH — Owner/host facts via `venue_source_observations(provider='manual')` are not actually top-trust

Defect: B6-venues-weather-backend.md #3. The "HUMAN CORRECTIONS WIN" override reads exclusively from venue_corrections WHERE status='applied' — it never reads venue_source_observations WHERE provider IN ('manual', 'host'). A manual observation (the pattern 00382 used for owner-QA fixes) is aggregated through the same per-fact majority-vote logic as every scraped provider — one vote, zero trust weighting. A future re-scrape adding 2 agreeing (but wrong) automated observations silently outvotes an owner-verified fact with no confidence penalty, no signal, no audit trail.

Canonical fix: extend the human-override block in resolve_venue_facts to also read venue_source_observations WHERE provider IN ('manual', 'host') AND <most-recent-per-field> at top trust (same precedence tier as venue_corrections.status='applied'), or — the cleaner long-term fix — change the operational convention so all future owner/curator corrections route through submit_venue_correction (into the already-protected venue_corrections ledger) instead of a direct venue_source_observations write, and update 00382's own doc claim ("the top-trust source") to match reality until the resolver change lands. Recommend doing both: extend the resolver (protects past manual rows already in the wild) AND fix the convention going forward (protects future ones without depending on the resolver extension being airtight).

Blast radius: resolve_venue_facts's per-field precedence logic — audit every fact field's resolution block (court_count, has_indoor, telephone, capabilities, fee_schedule, etc.) to add the manual/host read consistently, not just one field.

Verification: seed a venue with a manual observation for has_indoor=true, then seed 2 agreeing scraped observations for has_indoor=false, call resolve_venue_facts — assert the resolved value stays true (manual wins) rather than being outvoted.

V3 — HIGH — No reopen/dispute path for a wrongly-closed venue

Defect: B6-venues-weather-backend.md #4. submit_venue_correction's closed branch sets closed_at once 2 distinct users agree — one-directional, no reversal mechanism anywhere in the codebase. Combined with V1 (pre-fix), or even just 2 genuine sockpuppet/mistaken accounts (no real-identity verification behind submitted_by), a real operating court can be permanently removed from discovery with no in-app recourse for anyone.

Canonical fix: add a reopen_venue(p_venue_id uuid, p_reason text) RPC (SECDEF, search_path='', authenticated-gated, owner-verified-or-admin authority model — decide who can call it; likely the same trust tier as venue corrections generally, or a narrower admin-only gate given the higher-stakes reversal), mirroring report_venue_data's (00357) shape for the inverse direction. Clears closed_at/closure_reason.

Blast radius: venues UPDATE path (new narrow RPC, no RLS change needed if RPC-gated).

Verification: close a venue via the existing 2-agreement path, call reopen_venue — assert closed_at clears and the venue reappears in the active-venue directory query.

V4 — MEDIUM — Tennis-fee parser fix isn't wired into the live ingest pipeline

Defect: B6-venues-weather-backend.md #5. private.parse_tennis_fee (00348) fixed a real bug (multi-sport gov fee blobs showing soccer prices for a tennis court) but is only ever called from its own one-time backfill migration — never from ingest_venues_batch, record_venue_observations, or any live ingest source. Any future re-ingest of an already-covered or new region silently reintroduces the exact bug.

Canonical fix: call private.parse_tennis_fee from inside record_venue_observations (or resolve_venue_facts's fee_schedule resolution block) whenever a gov_% provider's payload carries an unparsed note-only fee entry, so the transform runs on every ingest, not just the historical backfill.

Blast radius: record_venue_observations (or resolve_venue_facts, depending on which layer is chosen) — additive parsing logic, no destructive change to existing correctly-parsed rows.

Verification: feed a synthetic multi-sport gov fee blob through the live ingest path (not the backfill migration) — assert the tennis-specific price is correctly extracted, not the soccer price.

V5 — MEDIUM — `adm1_id`/`adm2_id` region linkage has no ingest-time guard

Defect: B6-venues-weather-backend.md #6. ingest_venues_batch never populates region linkage — it's script-only (scripts/backfill-venue-adm.mjs), not systemic. A newly-ingested venue sits with adm1_id/adm2_id = NULL until a human remembers to re-run the backfill; filtering the directory by 시/도 silently drops these venues with no distinguishing UI signal (root cause behind a U6-flagged directory empty-state ambiguity).

Canonical fix: fold an adm-lookup (lat/lng → administrative_divisions polygon/bbox lookup) directly into ingest_venues_batch at write time — a private.lookup_adm_ids(lat, lng) helper called inline during the upsert, rather than a standalone script dependency.

Blast radius: ingest_venues_batch (additive computed columns on write). Existing NULL rows still need the one-time backfill script run once more to close the historical gap — this migration only prevents future drift.

Verification: run ingest_venues_batch with a synthetic new-region venue — assert adm1_id/adm2_id populate without a separate script run.

V6 — MEDIUM — Weather-warning severity escalation doesn't re-notify

Defect: B6-venues-weather-backend.md #7. signals_on_weather_warning_activated only proceeds on an inactive→active transition — a warning escalating severity while staying active (주의보→경보) never re-fires, and the dedup key is keyed by warning_type only, not warning_level.

Canonical fix: change the early-return guard to IF TG_OP = 'UPDATE' AND OLD.is_active = true AND NEW.warning_level = OLD.warning_level THEN RETURN NEW; END IF; (only skip when nothing materially changed) and extend the dedup key with warning_level so an escalation is a distinguishable event.

Blast radius: weather_warning_cache UPDATE trigger only.

Verification: activate a warning at 주의보, then update it to 경보 without deactivating — assert a new/updated signal fires (previously silent).

V7 — MEDIUM — No per-row exception isolation in `enrich_venues_batch`

Defect: B6-venues-weather-backend.md #8. Same class as V1's resolve_venue_facts issue, different trigger — a naver_place_id/kakao_place_id UNIQUE collision (already happened once, 00378/00380, patched at its one diagnosed cause) can still hard-abort an entire enrich_venues_batch call (chunked at 200 rows) with zero partial progress for any future different cause.

Canonical fix: wrap each row's UPDATE in enrich_venues_batch with WHEN unique_violation THEN handling (null out the conflicting identity field and continue, or skip + log via RAISE WARNING), matching the defensive pattern record_venue_observations already uses (IF NOT EXISTS ... THEN CONTINUE).

Blast radius: enrich_venues_batch only.

Verification: seed a synthetic naver_place_id collision mid-batch — assert the rest of the batch still commits.

V8 — LOW — Edge-function code fixes (not migrations)

Defect: B6-venues-weather-backend.md #1 (re-confirmed live in B7-platform-plumbing.md #7 — ingest-venues's token check is fail-open: if (token && provided !== token) skips the whole gate when INGEST_TOKEN is unset, unlike every sibling edge function which fails closed). Also #9 (fetch-weather's failure logs reference grid.name, a field that doesn't exist on ALL_GRIDS entries — every failedGrids log entry is undefined), #10 (shared-secret comparisons use plain !==, not constant-time — low-severity timing side-channel), and B7 #8 (seed-scenario's permissive-by-default Bearer accept when SEED_ALLOWED_UIDS is unset, self-documented dev-tool tradeoff).

Not migrations — these are TypeScript edits to supabase/functions/ingest-venues/index.ts, supabase/functions/fetch-weather/index.ts, and (optionally, low priority) a constant-time-compare helper shared across all secret-gated functions. Flag for a small IMPLEMENT-phase task alongside (not instead of) V1's RLS/RPC fix — the edge-function token gate and the RPC-level lockdown (already shipped, 00387) are independent layers and both should be correct. Fix if (token && ...) → if (!token || ...) (matches enrich-venues's existing correct pattern exactly — no design decision needed, just copy the sibling). Fix grid.name → include nx/ny in the failedGrids array entries directly (no name field exists to reference).

Verification: deploy with INGEST_TOKEN unset in a staging environment — confirm requests are now rejected (previously would have passed through).

AUTH / IDENTITY

A1 — CRITICAL — `profiles_select` RLS enforces nothing; every cross-user PII field is readable by any authenticated user

Defect: B8-auth-identity-backend.md #1. profiles_select (00011) is USING (true) — zero predicate. profile_visibility (00212) is enforced only inside get_public_profile_view, used by exactly one adapter method (findPublicView). Every other cross-user read path (findByIds, findByClub, getPlayerStats, findTopByRating, searchByDisplayName, getRegionalLeaderboard/Ranking) issues a raw .from('profiles').select(...) subject to the no-op RLS — empirically reproduced live: phone, kakao_id, push_token, birth_year, and all 3 PIPA consent timestamps are fully readable by any signed-in user for any other user, independent of profile_visibility settings, via a direct PostgREST call. This backs every club member-list screen, every leaderboard, any bulk profile hydrate — a live PIPA §23/§24 sensitive-information exposure and push-token exfiltration vector.

Canonical fix: narrow profiles_select itself. Recommended approach (matches the codebase's existing get_public_profile_view precedent, avoids Postgres RLS's lack of native column-level masking):

Split into a self-only full-row policy (USING (auth.uid() = id)) plus force all cross-user reads through SECDEF RPCs. Since get_public_profile_view already exists as the single-row form, add bulk siblings for the 6 listed adapter methods per the project's own Protocol A "bulk sibling" convention: get_public_profile_views(uuid[]) (bulk findByIds/findByClub), get_public_profile_leaderboard(...) (bulk rating-sorted reads for findTopByRating/getRegionalLeaderboard/getRegionalRanking), search_public_profiles(p_query text) (for searchByDisplayName). Each applies the same profile_visibility/hardcoded-never-public-field logic get_public_profile_view already correctly implements — reuse its internal masking logic as a shared private. helper both the single and bulk RPCs call, rather than duplicating the CASE/visibility logic 4 times.
Adapter rewrite: the 6 listed methods (packages/app/src/adapters/supabase/profile.supabase.ts) switch from raw .from('profiles').select(...) to .rpc('get_public_profile_views', ...) etc. This is app code, not a migration — flag explicitly for the IMPLEMENT phase as a required companion change; the migration alone does not close the gap if the RLS is narrowed but the RPCs don't exist yet (that would break every listed screen instead of securing it).

Blast radius: the single highest-blast-radius fix in this entire plan — every club roster, leaderboard, and profile-hydrate call site. Sequence carefully: land the new bulk RPCs first (additive, zero risk), verify each of the 6 adapter methods migrated to the RPC and passing (client-side regression pass — every consuming screen), only then narrow profiles_select. Do not narrow the RLS before the adapter migration lands, or every listed screen breaks in production.

Verification: repeat B8's own empirical repro (unrelated authenticated user reading another user's phone/kakao_id/push_token/birth_year/ consent timestamps via the raw-select shape) — must now return null/empty for all PII fields once both the RLS narrowing and adapter migration are live. Full regression pass on every club-roster/leaderboard screen.

A2 — CRITICAL — Self-service forgery of trust tier, rating fields, and Kakao/nationality identity

Defect: B8-auth-identity-backend.md #2. profiles_update allows the row owner to write any column via raw PATCH; protect_profiles_computed (00067) only reverts 7 columns (elo_rating, elo_matches_played, display_tier, display_tier_expires_at, peak_petals, comeback_sessions_remaining, last_session_at). trust_tier, singles_elo/doubles_elo/singles_rd/doubles_rd, rating_confidence, rating_source, kakao_id, nationality_code, total_sessions_confirmed, global_attendance_rate are all self-writable — empirically reproduced live: a user can self-promote to trust_tier='veteran' and set singles_elo=3000 via one raw UPDATE, both persisted exactly as set.

Canonical fix: extend private.protect_columns(...)'s argument list on the existing protect_profiles_computed trigger to also cover trust_tier, singles_elo, singles_rd, doubles_elo, doubles_rd, rating_confidence, rating_source, rating_state, kakao_id, total_sessions_confirmed, global_attendance_rate — mechanical, same existing trigger function, one migration.

Blast radius: profiles UPDATE path. No legitimate self-service write path exists for any of these columns today (all are server-computed or identity-verified) — zero functional change for legitimate app usage.

Verification: repeat B8's own probe — UPDATE profiles SET singles_elo=3000, trust_tier='veteran' WHERE id=<self> as the row owner — assert both revert to their prior values (matching the existing control probe on elo_rating, which already correctly reverts).

A3 — MEDIUM — `can_view_profile_field` takes the viewer as a caller-supplied argument instead of resolving `auth.uid()` internally

Defect: B8-auth-identity-backend.md #6. can_view_profile_field(p_viewer_id, p_target_id, p_field) trusts a client-supplied p_viewer_id rather than resolving it internally like every sibling function in this block. Any logged-in user can probe whether two other unrelated users share a club or friendship (boolean relationship-graph leak, not the underlying field value).

Canonical fix: drop the p_viewer_id parameter; resolve v_viewer := auth.uid() inside the function body. Update the one call site (get_public_profile_view, and any new bulk siblings from A1) to stop passing it.

Blast radius: can_view_profile_field's signature changes — since Postgres resets ACL on a signature-changing CREATE OR REPLACE, re-issue REVOKE ... FROM PUBLIC, anon; GRANT ... TO authenticated; explicitly in the same migration (per S1's corrected idiom). Bundle with A1 since both touch the same function family.

Verification: call the function with an arbitrary p_viewer_id naming a third party — confirm it no longer accepts that parameter at all (signature change) and correctly resolves to the actual caller.

Defect: B8-auth-identity-backend.md #4 (server-side consequence of U7-auth-profile-dm.md #3's client-side finding — the generic "푸시 알림" toggle silently grants/revokes marketing_push consent). Currently dormant (no code path sends an actual marketing-tagged push), but the §50(8) 2-year re-confirmation cron (private.expire_stale_marketing_consents, 00171) is already running against a ledger that may contain mislabeled grants — the moment a real marketing-push feature ships and reads this ledger as its gate, it sends to users who never knowingly opted in.

Canonical fix: this audit's backend scope covers the one-time ledger remediation, not the client toggle fix itself (that's U7's fix, out of this plan's scope). When the toggle fix lands client-side, pair it with a one-time data migration: for every user_consent row where purpose_id = 'marketing_push' (or marketing_push_night) AND collection_method = 'settings_toggle' AND granted_at predates the toggle fix's ship date, insert a new withdrawal row (append-only ledger — never mutate the existing grant row) so the ledger reflects "not actually informed consent" going forward. Do not backdate; this is a forward-looking correction of the current consent state, consistent with the ledger's own append-only design.

Blast radius: user_consent (new withdrawal rows only, no destructive change). Sequence this migration to run after the client toggle fix ships, not before (otherwise new mislabeled grants keep accumulating past the remediation date).

Verification: post-migration, SELECT * FROM user_consent WHERE purpose_id LIKE 'marketing_push%' AND state = 'granted' should only include rows from genuinely-informed grants (post-toggle-fix settings_toggle rows, or any other legitimately-labeled collection method).

A5 — LOW (defer, roadmap-adjacent) — `record_pi_access` dead code; `nationality_code` no write protection

Defect: B8-auth-identity-backend.md #5/#7. public.record_pi_access (PIPA §30 access-audit RPC) has zero call sites outside delete_account_atomic's own single hardcoded insert — the audit-trail infrastructure exists but is wired to exactly one event. nationality_code (00228) has no protect-trigger; inert today (everyone defaults 'KR', no UI writes it), but pre-seeds a forgery vector for the promised verified-IdP path, which has no "immutable once verified-source-populated" pattern the way birth_year correctly got in 00186.

Not planned as an active migration — both are genuinely low-priority given current inertness. Fold nationality_code's immutability trigger into whichever migration first implements the verified-IdP onboarding path (mirrors 00186's reject_under14_birth_year shape exactly — trivial to add at that time, premature now). record_pi_access wiring is a product decision (which PI-viewing flows actually need §30 audit coverage beyond account deletion) — flag for a dedicated small task when PIPA compliance is next reviewed, not bundled here.

Improvement candidates NOT included above (explicitly out of scope)

For completeness, these UI-audit findings were reviewed and correctly excluded from this backend plan because they require no server-side change: U2 #1 (join-request admin UI — B1 #8 confirms the backend is already fully built and correct; pure UI wiring), U2 #3/#4/#5/#6/#7/#8/#9/#10 (tap-to-profile, localeCompare perf, transfer-ownership display, doc staleness, cosmetic — all client-only), U4 #4–#9/#11/#12 (confirm dialogs, badge color palette, CTA-in-card exception, sudden-death visual marker, bye/sit-out visibility, score-entry pattern unification, dead hook — all client-only; U4 #3 referee-transfer orphan needs an owner product decision before any migration, not a backend defect per se), U8 #5–#12 (mid-month generation CTA gating, signal-route month param, currency formatting, bulk mark-paid UI, i18n dead code, 0-dues gating, parse-amount dedup — all client-only except #8's optional batch RPC, noted below).

One item worth a light backend note: U8 #8 (no bulk mark-paid) could be served by a update_dues_status_batch(p_dues_ids uuid[], p_status text, p_cleared_by uuid) SECDEF RPC wrapping the existing single-row RLS-gated update in a loop — small, optional, low-priority; not assigned a migration number in the sequence below since it's a "nice to have" rather than a defect fix. Bundle opportunistically with D2 if a dues migration is already in flight when this is prioritized.

Migration sequence

Ordered by dependency (foundational ACL fixes first, then domain fixes that build on a trustworthy grant baseline, then lower-priority hygiene). Numbers are provisional starting from current HEAD 00387.

#	Migration	Plan items	Why this position
00388	SECDEF anon-EXECUTE sweep + default-privilege fix + `check_rate_limit_for_key` hardening	S1	Root-cause fix; everything downstream assumes the ACL sweep pattern is correct. Also updates the canon-doc boilerplate (non-migration, same PR).
00389	pgTAP SECDEF regression guard	S2	Locks in 00388's invariant immediately, before any further function churn.
00390	`app_config` GRANT lockdown	S3	Small, isolated, highest-value single-table fix — do it standalone before the big sweep.
00391	Explicit data-API GRANT sweep (82 tables)	S4	Depends on confidence from 00388–00390; highest blast radius, run only after the smaller ACL fixes are verified in isolation.
00392	`profiles` PII lockdown: bulk RPC siblings + `profiles_select` narrowing + `can_view_profile_field` fix	A1, A3	Requires a client adapter migration companion before the RLS narrowing half ships — sequence the RPC-creation half of this migration early, hold the RLS-narrowing half until adapter rewrite lands (may need to split into 00392a/00392b in practice).
00393	`profiles` self-forgery lockdown	A2	Independent of A1/A3, but same table/file family — natural to batch.
00394	Account-deletion completeness (club + session + dues domains)	C1	Depends on 00388's ACL sweep being stable (new SECDEF trigger function). High blast radius — needs its own scenario-seeded verification pass.
00395	`guest_applications` RPC lockdown + capacity/status gate	C2	Independent; mirrors an existing pattern (`00329`), low risk.
00396	Club lifecycle signals (member-left/removed, dues_amount change, unarchive)	C3	Independent, additive only.
00397	Sessions: public pickup-preview RLS	SS1	Independent; unblocks a real growth-funnel gap early.
00398	Sessions: waitlist promotion on cancel	SS2	Independent.
00399	Sessions: capacity TOCTOU lock + RSVP-confirm status gate	SS3, SS5	Same function (`assign_waitlist_position`), bundle.
00400	Sessions: `max_players` decrease reconciliation	SS6	Independent, small.
00401	Sessions: status transition allowlist	SS4	Sequence after SS1–SS6 land and are verified (touches the same status machine those interact with).
00402	Matches/Payments: settle-before-play + finalized-match lockdown (4-part)	PY1	The headline matches fix — depends on nothing above but is high-risk/high-value; sequence with full scenario verification.
00403	Matches: format-aware tier gate + membership/RSVP slot check	M1, M2	Depends on PY1 landing first (same RLS policy family touched by PY1 Part C — M2 extends it).
00404	Matches: round-uniqueness trigger + delete `verify_match_score`	M3, M4	Independent cleanup.
00405	Payments: `session_payments` WITH CHECK + amount CHECK + fee-lock	PY2, PY3, PY4	Independent of PY1 (different table).
00406	Payments: missing signal emissions	PY5	Small, independent.
00407	Attendance: strike-ladder resurrection fix	D1	Independent; high user-visible value, do early within this cluster.
00408	Dues: canonical overdue definition + ex-member exclusion + delete dead cron	D3	Depends on nothing above; sequence before D4 (D4 extends the same helper).
00409	Dues: `dues_day` snapshot	D4	Depends on D3's helper existing.
00410	Dues: waived/partial signal + audit-trail fix	D2	Independent of D3/D4, same domain — bundle window.
00411	Dues: bank-account/payment-info schema	D6	Independent, additive.
00412	Attendance: phantom-attendance gate + `manner_tags` AFTER DELETE	D5, D7	Independent, bundle (small, same domain).
00413	Search-path hygiene sweep (22 functions)	D8, M5	Pure hygiene, any time — placed late since it's zero-urgency.
00414	Venues: `venue_corrections` lockdown + resolve/enrich exception isolation	V1, V7	Independent of everything above; venues domain is otherwise untouched by this plan.
00415	Venues: manual-observation trust fix + reopen RPC	V2, V3	Depends on V1 (same resolver function).
00416	Venues: fee-parser wiring + adm-lookup ingest guard	V4, V5	Independent, bundle (both are ingest-time transform additions).
00417	Weather: severity-escalation re-notify	V6	Independent, unrelated table.
—	Auth ledger remediation (A4)	A4	Not sequenced by number — run only after the client marketing-toggle fix ships; append to the sequence whenever that lands.
—	Edge-function fixes (V8)	V8	Not a migration — separate PR to `supabase/functions/ingest-venues`, `fetch-weather`; land alongside 00414 for the venues half.

Deferred, no migration assigned: C4 (role_configs, already tracked as ARCH-9), D9/D10 (Phase 6 trust-tier feature work), A5 (record_pi_access / nationality_code, roadmap-adjacent), U4 #3 referee-transfer (needs an owner product decision first).

Backend Wiring Plans — 2026-07 Adversarial Audit Synthesis ​

Already shipped — do NOT re-plan ​

SECURITY / ACL ​

S1 — CRITICAL — REVOKE ... FROM PUBLIC is a no-op against anon; 29% of public SECDEF functions are anon-executable ​

S2 — HIGH — No regression guard for SECDEF ACL hygiene (preventive gap) ​

S3 — HIGH — app_config (plaintext service_role_key + cron_secret) has full default table grants to anon/authenticated ​

S4 — HIGH — 82 of 88 public tables (93%) carry the unmodified Supabase default ACL; no explicit data-API GRANT ​

CLUBS ​

C1 — CRITICAL — Owner account-deletion permanently locks the club; ghost members never removed; account deletion never releases session/dues/payment state ​

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

C3 — MEDIUM — Club lifecycle events with no signal: member-left/removed, dues_amount change, unarchive ​

C4 — LOW (defer) — role_configs has zero server-side backing ​

SESSIONS / RSVP ​

SS1 — CRITICAL — Public pickup-preview screen can never load for non-members (its entire target audience) ​

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

SS3 — HIGH — Capacity-gate TOCTOU race under concurrent last-seat RSVPs ​

SS4 — HIGH — Session status machine has no positive transition table (blocklist, not allowlist) ​

SS5 — MEDIUM — No status gate on RSVP-confirm for a locked/cancelled/completed session ​

SS6 — MEDIUM — max_players decrease doesn't reconcile existing over-quota seats or re-evaluate lock status ​

PAYMENTS (session_payments / settle-before-play) ​

PY1 — CRITICAL — Settle-before-play match gate + finalized-match protection (matches/payments consolidated fix) ​

PY2 — MEDIUM — session_payments UPDATE RLS has no WITH CHECK; host/admin can tamper with amount/user_id via raw update ​

PY3 — LOW — No CHECK (amount >= 0) on session_payments ​

PY4 — MEDIUM — Fee change after holds exist has no reconciliation path ​

PY5 — MEDIUM — Two missing signal emissions in the payment-hold lifecycle ​

MATCHES / RATING ​

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

M2 — MEDIUM — No server-side membership/RSVP check on match participant slots ​

M3 — LOW-MEDIUM — No round-level player-uniqueness invariant ​

M4 — LOW-MEDIUM — Delete orphaned verify_match_score legacy RPC ​

M5 — LOW — search_path=public instead of '' on match-domain signal triggers ​

ATTENDANCE / TRUST / DUES ​

D1 — CRITICAL — Attendance strike-ladder notifications resurrect indefinitely; no decay/resolve policy ​

D2 — CRITICAL — Dues waived/partial transitions invisible to signals; no audit trail for waive ​

D3 — CRITICAL — Five divergent "overdue" definitions; canonical definition + ex-member exclusion ​

D4 — MEDIUM — dues_day is not snapshotted per period; mid-cycle edits retroactively reinterpret past due dates ​

D5 — MEDIUM-HIGH — Phantom attendance: total_sessions_confirmed bumps unconditionally regardless of tracking-enabled or whether the session played ​

D6 — MEDIUM — Bank-account / payment-instructions surface has no backing schema ​

D7 — LOW-MEDIUM — No AFTER DELETE recalc trigger on manner_tags ​

D8 — MEDIUM — 20 SECDEF functions use search_path=public instead of '' ​

D9 — Deferred (roadmap, not a migration) — Phase 6 spec vs. reality: 총무 endorsement / manual demotion unimplemented ​

D10 — LOW (decision needed, fold into D9 or delete) — 4 vestigial SignalTypes with zero emitters ​

VENUES ​

V1 — CRITICAL — venue_corrections RLS/GRANT bypass allows raw-insert fact poisoning + batch-abort DoS ​

V2 — HIGH — Owner/host facts via venue_source_observations(provider='manual') are not actually top-trust ​

V3 — HIGH — No reopen/dispute path for a wrongly-closed venue ​

V4 — MEDIUM — Tennis-fee parser fix isn't wired into the live ingest pipeline ​

V5 — MEDIUM — adm1_id/adm2_id region linkage has no ingest-time guard ​

V6 — MEDIUM — Weather-warning severity escalation doesn't re-notify ​

V7 — MEDIUM — No per-row exception isolation in enrich_venues_batch ​

V8 — LOW — Edge-function code fixes (not migrations) ​

AUTH / IDENTITY ​

A1 — CRITICAL — profiles_select RLS enforces nothing; every cross-user PII field is readable by any authenticated user ​

A2 — CRITICAL — Self-service forgery of trust tier, rating fields, and Kakao/nationality identity ​

A3 — MEDIUM — can_view_profile_field takes the viewer as a caller-supplied argument instead of resolving auth.uid() internally ​

A4 — MEDIUM — Marketing-consent ledger may already contain grants users never knowingly made ​

A5 — LOW (defer, roadmap-adjacent) — record_pi_access dead code; nationality_code no write protection ​

Improvement candidates NOT included above (explicitly out of scope) ​

Migration sequence ​

Backend Wiring Plans — 2026-07 Adversarial Audit Synthesis

Already shipped — do NOT re-plan

SECURITY / ACL

S1 — CRITICAL — `REVOKE ... FROM PUBLIC` is a no-op against `anon`; 29% of public SECDEF functions are anon-executable

S2 — HIGH — No regression guard for SECDEF ACL hygiene (preventive gap)

S3 — HIGH — `app_config` (plaintext `service_role_key` + `cron_secret`) has full default table grants to `anon`/`authenticated`

S4 — HIGH — 82 of 88 public tables (93%) carry the unmodified Supabase default ACL; no explicit data-API GRANT

CLUBS

C1 — CRITICAL — Owner account-deletion permanently locks the club; ghost members never removed; account deletion never releases session/dues/payment state

C2 — HIGH — `guest_applications` missing the `club_join_request`-style RPC-only lockdown; no capacity/status gate

C3 — MEDIUM — Club lifecycle events with no signal: member-left/removed, dues_amount change, unarchive

C4 — LOW (defer) — `role_configs` has zero server-side backing

SESSIONS / RSVP

SS1 — CRITICAL — Public pickup-preview screen can never load for non-members (its entire target audience)

SS2 — CRITICAL/HIGH — Voluntary RSVP cancellation never promotes the waitlist

SS3 — HIGH — Capacity-gate TOCTOU race under concurrent last-seat RSVPs

SS4 — HIGH — Session status machine has no positive transition table (blocklist, not allowlist)

SS5 — MEDIUM — No status gate on RSVP-confirm for a locked/cancelled/completed session

SS6 — MEDIUM — `max_players` decrease doesn't reconcile existing over-quota seats or re-evaluate lock status

PAYMENTS (session_payments / settle-before-play)

PY1 — CRITICAL — Settle-before-play match gate + finalized-match protection (matches/payments consolidated fix)

PY2 — MEDIUM — `session_payments` UPDATE RLS has no `WITH CHECK`; host/admin can tamper with `amount`/`user_id` via raw update

PY3 — LOW — No `CHECK (amount >= 0)` on `session_payments`

PY4 — MEDIUM — Fee change after holds exist has no reconciliation path

PY5 — MEDIUM — Two missing signal emissions in the payment-hold lifecycle

MATCHES / RATING

M1 — MEDIUM — RSVP tier gate uses cross-pool `elo_rating`, not a format-aware rating

M2 — MEDIUM — No server-side membership/RSVP check on match participant slots

M3 — LOW-MEDIUM — No round-level player-uniqueness invariant

M4 — LOW-MEDIUM — Delete orphaned `verify_match_score` legacy RPC

M5 — LOW — `search_path=public` instead of `''` on match-domain signal triggers

ATTENDANCE / TRUST / DUES

D1 — CRITICAL — Attendance strike-ladder notifications resurrect indefinitely; no decay/resolve policy

D2 — CRITICAL — Dues `waived`/`partial` transitions invisible to signals; no audit trail for waive

D3 — CRITICAL — Five divergent "overdue" definitions; canonical definition + ex-member exclusion

D4 — MEDIUM — `dues_day` is not snapshotted per period; mid-cycle edits retroactively reinterpret past due dates

D5 — MEDIUM-HIGH — Phantom attendance: `total_sessions_confirmed` bumps unconditionally regardless of tracking-enabled or whether the session played

D6 — MEDIUM — Bank-account / payment-instructions surface has no backing schema

D7 — LOW-MEDIUM — No `AFTER DELETE` recalc trigger on `manner_tags`

D8 — MEDIUM — 20 SECDEF functions use `search_path=public` instead of `''`

D9 — Deferred (roadmap, not a migration) — Phase 6 spec vs. reality: 총무 endorsement / manual demotion unimplemented

D10 — LOW (decision needed, fold into D9 or delete) — 4 vestigial SignalTypes with zero emitters

VENUES

V1 — CRITICAL — `venue_corrections` RLS/GRANT bypass allows raw-insert fact poisoning + batch-abort DoS

V2 — HIGH — Owner/host facts via `venue_source_observations(provider='manual')` are not actually top-trust

V3 — HIGH — No reopen/dispute path for a wrongly-closed venue

V4 — MEDIUM — Tennis-fee parser fix isn't wired into the live ingest pipeline

V5 — MEDIUM — `adm1_id`/`adm2_id` region linkage has no ingest-time guard

V6 — MEDIUM — Weather-warning severity escalation doesn't re-notify

V7 — MEDIUM — No per-row exception isolation in `enrich_venues_batch`

V8 — LOW — Edge-function code fixes (not migrations)

AUTH / IDENTITY

A1 — CRITICAL — `profiles_select` RLS enforces nothing; every cross-user PII field is readable by any authenticated user

A2 — CRITICAL — Self-service forgery of trust tier, rating fields, and Kakao/nationality identity

A3 — MEDIUM — `can_view_profile_field` takes the viewer as a caller-supplied argument instead of resolving `auth.uid()` internally

A4 — MEDIUM — Marketing-consent ledger may already contain grants users never knowingly made

A5 — LOW (defer, roadmap-adjacent) — `record_pi_access` dead code; `nationality_code` no write protection

Improvement candidates NOT included above (explicitly out of scope)

Migration sequence