Implementation Plan — 2026-07-11 Whole-App Audit Remediation
Superseded (2026-07): folded into Implementation Roadmap — 2026-07 Audit Remediation. Kept for reference.
Status: Superseded
Master plan synthesizing the 16-block adversarial audit (docs/audits/blocks/*, index docs/audits/2026-07-11-semantic-audit-plan.md). Companion docs: wiring-plans-2026-07-backend.md (server fix detail), design-specs-2026-07-ui.md (UI fix detail + wireframes).
Scope of the audit: every UI block (U1-U8) and backend block (B1-B8), adversarially probed against canon + live DB. ~130 findings; this plan sequences the ones worth acting on. Pure-doc-drift and INFO items stay in the block files.
Already shipped during the audit (no further action)
| Ref | What | Status |
|---|---|---|
| c6cdfe7d | 00385 TOCTOU restore — expire_unpaid_session_holds lost 00313's FOR UPDATE re-checks in the alert rewrite | pushed to main; in the owner-gated prod-push bundle |
| 891cb33b | 00386 — mark_session_payment_paid_via_portone anon/authenticated EXECUTE revoked (was unauth payment-marking) | pushed; prod-push bundle |
| 2f13a5f6 | 00387 — 5 venue-write RPCs anon-locked + avatar storage ownership scoping | pushed; prod-push bundle |
| 5dec3455 | Alert model wiring (9 types, 3-band center, emitters) + OTA 9f3c940b | shipped |
OWNER-BLOCKED (must run — the classifier denies prod DB deploys)
npx supabase db pushfor00385+00386+00387. Two of these (00386, 00387) close live, unauthenticated production holes. This is the single most urgent action in this document. All three are validated against a clean local full-sequence reset.
P0 — Security (live prod exposure, remaining after hotfixes)
Ordered by exploitability. Full fix detail in the backend wiring-plan doc.
| # | Defect | Block | Fix shape |
|---|---|---|---|
| S1 | Systemic anon-EXECUTE: REVOKE FROM PUBLIC never touches anon's separate pg_default_acl grant → 40/139 SECDEF fns anon-executable. 5 venue RPCs already fixed (00387); ~35 remain (most fail-safe via auth.uid() IS NULL guards, but the set must be enumerated + swept). | B7 | Migration: enumerate via aclexplode, REVOKE ... FROM anon on all that should be locked; add a pgTAP/CI guard so it can't regress. |
| S2 | profiles_select is USING(true) — any authenticated user reads any user's phone, kakao_id, push_token, birth_year, consent timestamps. profile_visibility is enforced only inside one RPC; every other read bypasses it. | B8 | Re-scope profiles_select to a column/row policy or route all reads through a visibility-enforcing view. High blast radius — leaderboards/stats read profiles; needs a designed column split (public-safe vs private columns), not a reflexive lock. |
| S3 | profiles_update column-forge — any user can self-set trust_tier, singles_elo, doubles_elo, rating_confidence, kakao_id, nationality_code via raw PATCH. | B8 | Extend the existing column-protection trigger (already guards elo_rating) to the full server-authoritative column set. |
| S4 | ingest-venues edge fn fails OPEN when INGEST_TOKEN unset (+ exposes ?action=reset corpus wipe). | B6 | Fail-closed guard (match the 6 sibling fns). RPC front door already closed by 00387. |
| S5 | guest_applications no RPC-only lockdown (unlike club_join_request in 00329) — raw admin PATCH "approves" without creating the RSVP, no capacity/status check. | B1 | Mirror 00329: RLS to RPC-only + decide_guest_application capacity/status guard. |
| S6 | app_config (holds plaintext service-role key + cron secret) has anon/authenticated table grants (inert only because RLS has no policies). | B7 | Explicit REVOKE + a deny-all RLS policy. |
P1 — Correctness (data integrity, money, no live-exploit but wrong)
| # | Defect | Block | Fix shape |
|---|---|---|---|
| C1 | Match finalize + team-swap have no status gate (2 independent paths): submit_match_score re-callable on completed matches (silent score/ELO desync); updateMatchTeams RLS has no status predicate → post-completion roster edits corrupt ELO attribution. | B4/U4/B3 | RPC status guard on submit_match_score; RLS status predicate on matches team-slot columns (extend 00067). |
| C2 | Voluntary RSVP cancel never promotes the waitlist — the only general promotion path is dead code; live paths cover only fee-hold-expiry + forfeit. | B2 | Wire promotion into the live cancel path (RLS-correct, SECDEF); reconcile with the 2 existing promoters to avoid double-promotion. |
| C3 | Attendance strike alerts resurrect forever — every attendance write re-fires attendance_warning_cooldown/flagged_admin via stale ref; dedup-upsert resets acknowledged_at; no resolve path. | B5 | Gate emission to the transition edge; add resolve. |
| C4 | Dues has 5 divergent "overdue" definitions (home-signals, admin-snapshot, tick-dues cron, screen summary, a dead cron) → admin sees "0 미납" while a critical push already fired. | B5/U8 | Define ONE canonical dues_day-precise overdue; route all surfaces through it. |
| C5 | Dues waive/partial writes no audit trail + no signal resolution — waiving a severely-overdue member leaves a permanent unresolvable critical push; partial discards the amount entirely (no amount field in the domain model). | U8/B5 | Add cleared_by/at on waive; dues_waived resolution; partial-payment amount column + reconciliation. |
| C6 | Account deletion is incomplete — never touches club_members (sole-owner club lockout + capacity leak), rsvps, dues, session_payments, elo_history. | B1/B8 | Extend delete_account_atomic to deactivate membership (with ownership-transfer-or-archive), cancel holds/RSVPs, tombstone dues. |
| C7 | Capacity TOCTOU on last-seat RSVP (assign_waitlist_position no row lock) + no server block on RSVP to locked/cancelled/completed sessions. | B2 | Advisory/row lock on the capacity check; status guard trigger on rsvp insert. |
| C8 | Session status machine is a blocklist not allowlist — cancelled→completed + state-skips only client-gated. | B2 | Allowlist transition guard trigger. |
P2 — Dead pipelines (built, unwired — high user value, low risk)
| # | Gap | Block | Action |
|---|---|---|---|
| D1 | Club join-request approval UI missing entirely — useClubJoinRequests/useDecideJoinRequest built + unexported; server fully hardened (00213/00329). Zero migration needed. | U2/B1 | Wire the admin approve/reject surface (design spec) — pure client. |
| D2 | Push-notification tap dead-ends for ~12/13 categories — edge fn nests IDs under data.context.*, handler reads top-level. | U1 | Align the key path; add coverage. |
| D3 | Disputed-match escalation is dead — nothing writes matches.score_status='disputed'; reject_call only deletes + signals. | U4/B4 | reject_call sets the status; the existing banner/badge then live. |
| D4 | club join-request-received / member-left / member-removed emit no signal; admin rows stay "virtual"/polled. | B1 | Signal emitters → the alert model's 할 일 band handles the rest. |
| D5 | signal_preferences has zero settings UI (route + i18n scaffolded, unwired) — Alert plan Phase D. | U7 | Preferences screen (design spec). |
| D6 | Empty-DM inbox has no 1:1 CTA (header + = groups only); guest-apply funnel leak (pickup preview has no onboarding-intent rule). | U7/U6 | CTA + intent rule (design spec). |
P3 — Canon/consistency + latent
ICU localeCompare re-introductions (U2 club-card, U5 records, U6 club-discovery — the freeze anti-pattern); 3-way admin-count inconsistency (U1); tap-to-profile missing (U2 members, U5 podium); CTA-in-Card drift x4 (U3); tier-info-sheet raw RN Views (U5); ELO sparkline plots backwards (U5); solo-region leaderboard renders zero rows (U5); marketing-consent conflation in the generic push toggle (U7/B8); explicit-GRANT convention 7% compliant (B7 — 82 tables). Batch these as a mechanical sweep + a lint/pgTAP ratchet where possible.
P4 — Deferred / external (documented, not now)
OAuth provider UI (EXT-11 Kakao creds); verified-identity 본인인증; PortOne verify-swap (Phase B); trust-tier compute (Phase 6 spec vs empty impl); recurrence; PIPA privacy text (owner-owned); naver_place deep data (scraped, unread — surface when venue-detail gets its redesign).
Execution sequencing
- Owner prod-push 00385/86/87 (unblocks the shipped security fixes).
- Security sweep migration (S1 + S6) + pgTAP ACL guard — one migration, highest leverage.
- profiles RLS redesign (S2+S3) — needs the public/private column split; design first.
- Money/integrity batch (C1 C4 C5 C6) — payments + dues + deletion, each its own migration, each locally probe-verified.
- Session integrity batch (C2 C7 C8) + signal emitters (D3 D4).
- Dead-pipeline UI (D1 D2 D6) — client, OTA-shippable, high user value.
- Canon sweep (P3) — mechanical, ratchet-guarded.
- Design-driven UI (D5 + the design-spec rethinks) — wireframe-first per canon.
Each backend slice: migration → local db reset + psql/synthetic probe → yarn check → commit → owner prod push. Each client slice: yarn check → commit → OTA → baseline. Docs updated per slice.