B5 — Attendance/Trust + Dues (Backend) Adversarial Audit

Status: Archived Date: 2026-07-11 Scope: attendance_records + private.refresh_attendance_state + strike ladder (00127/00132/00318), match-derived attendance v3 (00384session_completed_record_attendance), excused semantics (00206/00215/ 00307/00310/00322/00325), manner tags (00124/00125), trust tiers (private.recalculate_trust_tier, spec docs/specifications/attendance-and-reviews.md), dues engine (00009, 00108 signals_tick_dues, 00120 cleared_by, 00381 admin snapshot, 00385 alert-model dues emitters).

Method: Full read of every migration touching attendance_records/ dues/manner_tags, cross-checked against live source pulled from a freshly-reset local Postgres (npx supabase db reset, HEAD = 00385) via docker exec supabase_db_twomore-v2 psql \sf <function> and direct pg_proc/pg_default_acl/information_schema.role_table_grants probes — not static migration reading alone (the dead-theme-file lesson: several findings below only surfaced because the live grant/search_path state diverges from what the migration text implies). Cross-referenced docs/audits/blocks/U8-dues.md (dues UI criticals) and docs/audits/blocks/B3-payments-backend.md (sibling payments audit — one of its SECDEF-hygiene "verified clean" claims is corrected below with live evidence).

---

Summary table

#	Finding	Severity	Verified
1	mark_session_payment_paid_via_portone is EXECUTE-able by anon (no login) — the function has zero internal caller-identity check, so any anon API-key caller can mark any pending fee hold paid. Corrects B3's "verified clean" claim.	CRITICAL	Empirically confirmed on a fresh db reset
2	Attendance strike-ladder notifications (attendance_warning_cooldown, attendance_flagged_admin) resurrect indefinitely — every attendance write for a user re-fires them via a stale "last strike session," and signals_emit's dedup-upsert resets acknowledged_at/resolved_at to NULL. Zero resolve call exists anywhere for these 3 signal types. No decay/reset policy.	CRITICAL	Live source + call-graph traced
3	Dues waived/partial transitions are invisible to the signal system (server-side confirmation of U8 #1/#2)	CRITICAL (already filed by U8; re-verified live here)	Live \sf signals_on_dues_update
4	Systemic post-00209 SECDEF anon-EXECUTE leak: 41 functions app-wide, incl. B5's cancel_session_as_host, are anon-executable because ALTER DEFAULT PRIVILEGES … REVOKE … FROM PUBLIC never touches anon's own separate default-ACL grant. Root cause of #1.	HIGH	pg_default_acl + reproduced twice
5	attendance_records, dues, manner_tags carry zero explicit data-API GRANT — 100% reliant on the Supabase default-privilege auto-grant (full CRUD to anon+authenticated), the exact pattern AGENTS.md forbids. Safe today only because RLS policies happen to be correctly scoped.	HIGH	information_schema.role_table_grants
6	Five, not four, mutually-inconsistent "overdue" definitions live in the codebase (U8 found 4; a 5th, dead one found here) — canonical definition proposed below.	CRITICAL (extends U8 #2)	Live source, all 5 pulled
7	dues_day is club-level, mutable, and not snapshotted per period — signals_tick_dues/get_club_admin_snapshot recompute due_date live from the current clubs.dues_day, so an admin's mid-cycle edit retroactively reinterprets every past month's due date.	MEDIUM	Live source
8	Phantom attendance still open: a session that reaches completed unconditionally bumps total_sessions_confirmed for every confirmed RSVP, independent of attendance_tracking_enabled and independent of whether the session ever actually started/played. Self-documented as deferred in 00318, confirmed still true on HEAD.	MEDIUM-HIGH	Live source
9	20 SECDEF functions app-wide carry search_path=public instead of the mandated ''; dues-specific subset (signals_tick_dues, signals_on_dues_insert, signals_on_dues_delete) inconsistent with their own sibling signals_on_dues_update (which correctly uses ''). Broader than B2's single-instance flag on signals_on_rsvp_update.	MEDIUM	pg_proc.proconfig sweep
10	manner_tags has an AFTER INSERT trust-tier recalc trigger but no AFTER DELETE trigger — a tagger revoking their own tag within the 24h window doesn't retroactively lower the taggee's cached trust_tier until an unrelated event re-triggers recalculation.	LOW-MEDIUM	Live trigger list
11	Phase 6 spec (docs/specifications/attendance-and-reviews.md) vs. reality: "총무 endorsement" (High-weight input) and "총무 manual action" (demotion trigger) are entirely unimplemented — recalculate_trust_tier has no admin-boost/admin-demote path at all; no dashboard surfaces demotion reasons.	MEDIUM	Live source vs. spec
12	4 SignalTypes — trust_tier_changed, manner_tag_received, late_cancel_penalty_applied, no_show_reported — are declared in the domain entity's union with zero emitters anywhere (0 migrations reference them) and zero other codebase references (no i18n, no routes). Fully vestigial.	MEDIUM	Grep across migrations + app
13	Dead cron generate_dues_reminders() still runs daily, writing to club_alerts — a table with zero live UI consumers (per the codebase's own comment) — computing yet another (the 5th) divergent overdue definition nobody ever sees.	LOW (dead-code / wasted-cron)	Live source + grep
14	No server-side guard blocks RSVP cancel while a session is in_progress and the user is embedded in a live match's participant_ids; only the purpose-built forfeit_session RPC correctly pre-seeds excused + resyncs participant_ids. Client UI gates this (only "기권하기" shows at in_progress), so unreachable via the shipped app — defense-in-depth gap only.	LOW (defense-in-depth)	Client + server cross-check

---

1. CRITICAL — mark_session_payment_paid_via_portone is anon-executable with zero internal auth check (corrects B3)

The function (00301/00302_portone_payment_confirm.sql:56-57, current live source):

CREATE OR REPLACE FUNCTION public.mark_session_payment_paid_via_portone(
  p_session_id uuid, p_user_id uuid, p_payment_id text, p_paid_amount integer)
...
SECURITY DEFINER SET search_path TO ''
AS $function$
DECLARE v_fee INTEGER;
BEGIN
  SELECT participation_fee INTO v_fee FROM public.sessions WHERE id = p_session_id;
  IF v_fee IS NULL OR v_fee <= 0 THEN RAISE EXCEPTION ...; END IF;
  IF p_paid_amount <> v_fee THEN RAISE EXCEPTION ...; END IF;
  UPDATE public.session_payments
    SET status='paid', paid_at=now(), method='portone',
        portone_payment_id=p_payment_id, submitted_at=COALESCE(submitted_at, now())
    WHERE session_id=p_session_id AND user_id=p_user_id AND status='pending';
  RETURN FOUND;
END;
$function$

No auth.uid() check, no role check, nothing. The function's entire security model is "only service_role can call this," enforced purely by the GRANT. 00302_portone_payment_confirm.sql:56-57:

REVOKE ALL ON FUNCTION public.mark_session_payment_paid_via_portone(UUID, UUID, TEXT, INTEGER) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.mark_session_payment_paid_via_portone(UUID, UUID, TEXT, INTEGER) TO service_role;

Live proof (fresh db reset, no manual DB edits):

proname                                | proacl
mark_session_payment_paid_via_portone  | {postgres=X/postgres,anon=X/postgres,authenticated=X/postgres,service_role=X/postgres}

anon and authenticated both retain EXECUTE. REVOKE ALL … FROM PUBLIC does not remove anon's grant — see finding #4 for the root cause. Because this function has no internal identity check, this is directly, trivially exploitable: any caller with the public anon API key — unauthenticated, no login required — can POST /rest/v1/rpc/mark_session_payment_paid_via_portone with any (session_id, user_id, payment_id, paid_amount) where paid_amount equals the session's (publicly-visible) participation_fee, and flip that member's fee hold to paid with a fabricated portone_payment_id, with no money ever changing hands.

Corrects B3: docs/audits/blocks/B3-payments-backend.md finding #10 states "SECDEF hygiene sweep clean — mark_session_payment_paid_via_portone correctly service_role-only." That claim was based on the migration's REVOKE/GRANT text, not the live ACL — exactly the static-vs-runtime gap CLAUDE.md's research methodology warns against. The live database disagrees with the migration's stated intent.

Fix shape (documentation only per audit scope — not applied): either add an internal IF (SELECT auth.role()) <> 'service_role' THEN RAISE EXCEPTION … END IF; guard (defense-in-depth, matches the pattern other edge-fn-only RPCs should use), or — better — explicitly REVOKE EXECUTE … FROM anon, authenticated; in a follow-up migration. Given this function is already on HEAD (post-00302, well before the currently-unpushed 00385), this is very likely already live in production and should be treated as an urgent out-of-band fix, not bundled with routine B5 remediation.

---

2. CRITICAL — Attendance strike-ladder notifications resurrect indefinitely; no decay policy exists

The mechanism. private.refresh_attendance_state(p_user_id) (live source, 00127/00132) runs on every attendance_records write for a user — including a clean 'attended' / strike_value=0 row from normal match-derived completion, an excused withdrawal, anything:

SELECT session_id, club_id INTO v_latest_strike
FROM public.attendance_records
WHERE user_id = p_user_id AND strike_value > 0
ORDER BY created_at DESC LIMIT 1;

IF v_latest_strike.session_id IS NOT NULL THEN
  PERFORM private.fire_attendance_strike_notifications(
    p_user_id, v_latest_strike.session_id, v_latest_strike.club_id);
END IF;

This looks up the most recent strike-bearing session (not the session that triggered this specific call) and unconditionally re-fires fire_attendance_strike_notifications, which recomputes strikesInWindow from "the last 20 attendance records, any strike_value > 0" and re-emits at thresholds 1/2/3+:

ELSIF v_strikes_in_window = 2 THEN
  PERFORM public.signals_emit(
    p_type := 'attendance_warning_cooldown', p_severity := 'high'::signal_severity,
    p_dedup_key := 'attendance_warning:' || p_user_id::text || ':' || p_session_id::text,
    ...);

p_session_id here is the stale last-strike session — so as long as ≥2 strikes remain inside the trailing-20-record window (realistically months, at weekly play), the dedup key stays constant across every subsequent attendance write.

signals_emit's upsert (live source, 00107/hardened since) resurrects on every re-fire:

INSERT INTO public.signals (...)
VALUES (...)
ON CONFLICT (dedup_key) DO UPDATE SET
  payload = jsonb_set(..., '{count}', to_jsonb(COALESCE((signals.payload->>'count')::int,1)+1)),
  created_at = now(),
  acknowledged_at = NULL,
  resolved_at = NULL,
  expires_at = COALESCE(EXCLUDED.expires_at, signals.expires_at)
RETURNING id INTO new_id;

acknowledged_at = NULL, resolved_at = NULL — every re-fire un-does whatever the member/admin already did with the notification.

Confirmed no resolve path exists:grep -rn "attendance_nudge\|attendance_warning\|attendance_flagged" supabase/migrations/*.sql | grep -i resolve → 0 hits, anywhere.

Concrete failure case: a member hits 2 strikes in April (cooldown warning fires, admin sees "flagged" alert at 3+). They then play cleanly every week through August. Every one of those ~15 clean sessions calls refresh_attendance_state → finds the April strike still inside the trailing-20 window → re-fires the SAME dedup key → the member's dismissed "cooldown warning" and the club admins' dismissed "flagged member" alerts pop back to unread/unresolved, every single week, for a member who has since been a model citizen. There is no decay, no expiry re-check, and no mechanism that ever marks these three signal types resolved.

Fix shape: (a) only call fire_attendance_strike_notifications when the triggering write itself carries strike_value > 0 (pass the actual new record, not "most recent ever"); (b) add resolve calls when a strike ages out of the trailing window or the member's global_attendance_rate recovers past a threshold; (c) consider whether signals_emit's unconditional acknowledged_at/resolved_at = NULL reset is the right default for any dedup'd signal, or whether some categories should only bump count without reopening an already-resolved/acknowledged row.

---

3. CRITICAL — Dues waived/partial signal blind spot (server-side confirmation of U8 #1/#2)

U8 (docs/audits/blocks/U8-dues.md #1/#2) documented this from the client side. Confirmed here against the live signals_on_dues_update trigger function (post-00385):

IF NEW.status = 'paid' AND OLD.status IS DISTINCT FROM 'paid' THEN
  PERFORM public.signals_resolve_by_dedup_key('dues_assigned:' || NEW.id);
  PERFORM public.signals_resolve_by_dedup_key('dues_due_soon:' || NEW.id);
  PERFORM public.signals_resolve_by_dedup_key('dues_overdue:' || NEW.id);
  PERFORM public.signals_resolve_by_dedup_key('dues_severely_overdue:' || NEW.id);
  ...
END IF;

The gate is NEW.status = 'paid' only — a transition to 'waived' or 'partial' never enters this block. Server-side, this is exactly as U8 described: no dues_waived signal type exists in packages/app/src/domain/entities/signal.entity.ts at all, and no resolution of previously-emitted dues_overdue/dues_severely_overdue happens on waive/partial. There is also no DB-level backstop for U2/U8's client-side "waive doesn't write cleared_by" bug — dues status writes go through a raw dues_update RLS-gated table UPDATE (no RPC), so nothing server-side normalizes the audit-trail gap either. See U8 for the full fix shape (broaden the resolve guard to IN ('paid','waived','partial'), add a dues_waived signal type).

---

4. HIGH — Systemic post-00209 SECDEF anon-EXECUTE leak (root cause of #1)

00209_revoke_anon_execute_secdef.sql did two things: (a) an explicit one-time sweep — REVOKE EXECUTE ON FUNCTION %s FROM PUBLIC, anon; for every existing SECDEF function at the time — and (b) a "future-proofing" statement:

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

This only revokes the implicit PUBLIC grant for future functions. It does not touch anon's own separate, direct default-ACL entry, which Supabase's platform bootstrap sets independently of PUBLIC. Live proof:

select defaclrole::regrole, defaclobjtype, defaclacl
from pg_default_acl where defaclnamespace = 'public'::regnamespace;
-- postgres | f | {postgres=X/postgres,anon=X/postgres,authenticated=X/postgres,service_role=X/postgres}

Every SECDEF function created after 00209 (i.e. every migration 00210+) is anon-executable by default unless its migration explicitly adds REVOKE EXECUTE … FROM anon — which the established boilerplate (REVOKE … FROM PUBLIC; GRANT … TO authenticated;, copied everywhere, including cancel_session_as_host at 00307:237-238 and mark_session_payment_paid_via_portone at 00302:56-57) does not do.

Blast radius (live, fresh reset): 41 SECDEF functions app-wide currently carry anon EXECUTE, including B5's cancel_session_as_host. Full list captured during this audit (available on request / re-derivable via the query below); most are non-exploitable because they internally check auth.uid() IS NULL and raise (e.g. cancel_session_as_host, get_club_admin_snapshot) — but mark_session_payment_paid_via_portone (#1) has no such internal check, which is what makes it the one live critical instance.

select p.proname from pg_proc p join pg_namespace n on n.oid=p.pronamespace
where n.nspname='public' and p.prosecdef=true
  and has_function_privilege('anon', p.oid, 'EXECUTE') = true;

Fix shape: either (a) fix the default-ACL at the role level — ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM anon; — so all future SECDEF functions are safe by default, or (b) re-run 00209's sweep pattern (REVOKE EXECUTE ON FUNCTION %s FROM PUBLIC, anon for every SECDEF function currently open to anon) as a new migration, and update the project's own boilerplate comment/snippet so future authors write REVOKE EXECUTE … FROM PUBLIC, anon; instead of FROM PUBLIC; alone.

---

5. HIGH — attendance_records/dues/manner_tags have zero explicit data-API GRANT

AGENTS.md/CLAUDE.md: "every new public table needs an explicit data-API GRANT … never rely on the Supabase default-privilege auto-grant."grep -n "GRANT.*attendance_records\|GRANT.*ON.*dues\|GRANT.*manner_tags" supabase/migrations/*.sql → 0 hits across all three tables' entire migration history (00009, 00055, 00124). Live grant check:

table_name          grantee         privs
attendance_records  anon            TRIGGER,REFERENCES,TRUNCATE,DELETE,UPDATE,SELECT,INSERT
attendance_records  authenticated   (same full set)
dues                anon            INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER
dues                authenticated   (same full set)
manner_tags         anon            DELETE,TRIGGER,REFERENCES,TRUNCATE,UPDATE,SELECT,INSERT
manner_tags         authenticated   (same full set)

anon holds full raw CRUD grants on all three money/trust-adjacent tables — entirely via the Supabase default-privilege auto-grant the project's own rule explicitly forbids relying on. Not independently exploitable today: RLS is enabled on all three (relrowsecurity=true) and the live policy sets correctly scope INSERT/UPDATE to service_role (attendance_records) or gate on is_club_admin()/tagger_id=auth.uid() (dues/manner_tags), both of which evaluate false for anon (auth.uid() is NULL). But this is the identical structural gap B1 already flagged for clubs/club_members/invites/guest_applications ("predate the explicit-GRANT convention") — now confirmed to also cover all 3 of B5's tables. It is a single unreviewed permissive RLS policy away (a failure mode this codebase has hit before, e.g. B1's guest_applications finding) from becoming directly exploitable, with zero grant-layer defense-in-depth in the meantime.

Fix shape: add explicit GRANT SELECT, INSERT, UPDATE, DELETE ON public.attendance_records/dues/manner_tags TO authenticated; (no anon) in a follow-up migration, matching the convention new tables already follow.

---

6. CRITICAL (extends U8 #2) — Five, not four, divergent "overdue" definitions; canonical definition proposed

U8 documented 4 live, disagreeing "overdue" computations. A 5th was found here: the dead generate_dues_reminders() cron (still scheduled daily at 21:00 KST via generate-reminders-daily, 00101/00108):

FOR v_club IN
  SELECT DISTINCT d.club_id, (SELECT cm.user_id FROM club_members cm
    WHERE cm.club_id=d.club_id AND cm.role='owner' LIMIT 1) AS admin_id
  FROM public.dues d
  WHERE d.status = 'unpaid'
    AND d.year = EXTRACT(YEAR FROM now())::INT
    AND d.month = EXTRACT(MONTH FROM now())::INT
  LOOP ...
    INSERT INTO public.club_alerts (...) VALUES (..., 'dues_reminder', ...);

This is unpaid-only, current calendar month only (no dues_day precision, no strictly-past-month exclusion either), recipient = first owner found (LIMIT 1, arbitrary under multi-owner). It writes to public.club_alerts — a table with zero live UI consumers, per packages/app/src/presentation/hooks/index.ts:65-67: "Club Alerts: hooks removed in Round 14 — zero UI consumers since v2 inception." So this cron computes a genuine 5th divergent definition, daily, forever, for a notification literally nobody can ever see. Recommend deleting the cron + function, not reconciling it (it predates and is unrelated to the signals system that superseded it).

The 5 definitions, side by side

Surface	Status set	Time granularity	Scope
get_home_signals (00103)	unpaid + partial	strictly-past calendar month	all clubs, player-scoped
get_club_admin_snapshot (00381)	unpaid only (comment: "deliberately NOT replicated … per the task spec")	strictly-past calendar month	one club, admin-scoped
signals_tick_dues (00108)	unpaid only	dues_day-precise days-past (can fire within the current calendar month)	one row, per-member push
SummaryCard (client)	paid/unpaid/partial buckets	no "overdue" concept — just the selected month's status	one club, one month
generate_dues_reminders() (dead)	unpaid only	current calendar month only, no due-date precision	one club, dead output

Live corroboration of get_club_admin_snapshot's comment (00381):

-- Monthly dues overdue. MIRRORS dues.supabase.ts findOverdue (lines 58-77) EXACTLY:
-- status = 'unpaid' only (NOT 'partial' — that's a broader definition used
-- by get_home_signals' player-side overdueDuesCount, deliberately NOT
-- replicated here per the task spec)
'overdue_dues_count', (
  SELECT count(*) FROM public.dues d
  WHERE d.club_id = p_club_id AND d.status = 'unpaid'
    AND (d.year < v_year OR (d.year = v_year AND d.month < v_month))
),

This confirms the divergence is not accidental drift — it was a deliberate, task-scoped decision ("NOT replicated per the task spec") that was never reconciled afterward. That's the exact shape of debt this audit exists to surface.

Proposed canonical definition

Adopt signals_tick_dues's dues_day-precise formula as ground truth — it's the only one that's (a) per-club configurable via a real setting, (b) already the basis for the push notifications members actually receive, and (c) the most accurate model of "was this actually due and unpaid as of today":

due_date(dues_row) := make_date(year, month,
  LEAST(clubs.dues_day, last_day_of_month(year, month)))
days_past(dues_row) := CURRENT_DATE - due_date(dues_row)
overdue(dues_row)   := status IN ('unpaid')  -- extend to 'partial' with remaining balance > 0
                         once U2/U8's partial-amount schema fix lands
                       AND days_past(dues_row) >= 1
severely_overdue     := overdue AND days_past >= 7

Wiring plan: extract this into a single SQL helper (a STABLE function or a view dues_with_status joining dues + clubs.dues_day + club_members.is_active) and make get_home_signals, get_club_admin_snapshot, and (optionally) SummaryCard's badge all read from it instead of re-deriving their own calendar-month-boundary shortcuts. This closes #6 (unify overdue) and U8 #4 (ex-members' immortal dues, via the same helper's club_members.is_active join) in one migration.

---

7. MEDIUM — dues_day is not snapshotted per period

dues (00009_dues.sql) has no due_date column — every consumer derives it live from clubs.dues_day (current value) + the row's year/month. signals_tick_dues (live source, section above) does make_date(row_rec.year, row_rec.month, LEAST(row_rec.dues_day, …)) where row_rec.dues_day comes from a live JOIN clubs c ON c.id = d.club_id — not a value captured at generation time. If a 총무 changes clubs.dues_day from, say, 25 to 5 mid-cycle, every unpaid row for every past month instantly reinterprets its due date using the new day — a row that was 3 days from due yesterday could become 20 days overdue (or vice versa) the moment the setting changes, with no notice to anyone.

Fix shape: either snapshot due_day/due_date onto each dues row at generation time (immutable per period, matches how session_payments.amount is frozen at hold-creation per B3), or explicitly document/accept that dues_day changes are retroactive-by-design (current behavior is silent either way — no signal, no confirmation dialog on the settings change).

---

8. MEDIUM-HIGH — Phantom attendance still open (confirmed still-live, self-documented gap)

00318_audit_batch8_attendance_integrity.sql's own header explicitly defers this:

"Deferred (deeper): auto_advance auto-completing a NEVER-STARTED session still awards 'attended' + bumps total_sessions_confirmed for its confirmed members (phantom attendance) — that needs a 'did the session actually run' signal, a later attendance pass."

Confirmed still true on HEAD via live session_completed_record_attendance:

FOR v_rsvp IN SELECT user_id FROM public.rsvps WHERE session_id = NEW.id AND status = 'confirmed'
LOOP
  UPDATE public.profiles
  SET total_sessions_confirmed = COALESCE(total_sessions_confirmed, 0) + 1
  WHERE id = v_rsvp.user_id;
  -- (no attendance_tracking_enabled gate on the line above)
  v_played := (NOT v_has_matches) OR EXISTS (...);
  INSERT INTO public.attendance_records (...)
    SELECT ... WHERE EXISTS (SELECT 1 FROM public.clubs WHERE id=NEW.club_id AND attendance_tracking_enabled=true)
    ON CONFLICT (...) DO UPDATE ...;
  PERFORM private.refresh_attendance_state(v_rsvp.user_id);
END LOOP;

The total_sessions_confirmed increment is unconditional — it fires before the attendance_tracking_enabled gate that governs the attendance_records write, and it doesn't consult v_played at all. Two compounding effects:

1. A club with attendance tracking off still has every confirmed RSVP on a completed session count toward that player's global trust-tier session total (recalculate_trust_tier's v_sessions input) — with no possibility of a no-show penalty ever offsetting a no-show, because the tracking-off club never writes an attendance_records row at all.
2. A session that never actually started (rained out, nobody showed, but nobody cancelled it either) still gets auto-advanced to completed by the hourly auto-advance-session-statuses cron and still bumps everyone's global session count — inflating trust-tier eligibility with zero verification the session happened.

This is a genuine gap in the trust-tier input pipeline's integrity, not new territory the codebase hasn't already flagged for itself — but it remains unfixed on HEAD, three migrations after 00318 explicitly named it.

---

9. MEDIUM — search_path=public instead of '' on 20 SECDEF functions (broader than B2's single flag)

B2 (docs/audits/blocks/B2-sessions-backend.md) flagged one instance (signals_on_rsvp_update) as a standalone LOW. Live sweep here shows it's part of a 20-function pattern app-wide:

select p.proname, p.proconfig from pg_proc p
join pg_namespace n on n.oid=p.pronamespace
where n.nspname='public' and p.prosecdef=true
  and NOT (p.proconfig IS NOT NULL AND array_to_string(p.proconfig,',') LIKE '%search_path=""%');

→ 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, but their sibling signals_on_dues_update (finding #3, quoted above) correctly uses SET search_path TO ''. Same trigger family, same migration era, split compliance. signals_tick_dues (the escalation cron, finding #6) is also on this list.

Impact: lower-severity than a fully-unset search_path (these functions mostly use schema-qualified public.foo references already, and public is not attacker-writable under current grants), but it's a live, mechanically-checkable violation of CLAUDE.md's own stated invariant ("every SECURITY DEFINER function MUST set search_path = ''"), and — per this audit's cross-checks — a hardening pass that's been run at least twice (00152/00194) without fully closing.

Fix shape: one migration, ALTER FUNCTION <name> SET search_path = ''; for all 20, mirroring 00152/00194's own pattern. Cheap and mechanical.

---

10. LOW-MEDIUM — manner_tags has no AFTER DELETE recalc trigger

manner_tags_after_insert_recalc (AFTER INSERT) is the only trigger on manner_tags. manner_tags_delete_own_recent RLS policy lets a tagger delete their own tag within 24h:

POLICY "manner_tags_delete_own_recent" FOR DELETE
  USING ((tagger_id = auth.uid()) AND (created_at > now() - '24:00:00'::interval))

But there is no AFTER DELETE trigger calling private.recalculate_trust_tier(taggee_id). recalculate_trust_tier's v_tags input is a live COUNT(DISTINCT (tagger_id, tag_type)) query against manner_tags, so the next recalc would correctly reflect the deletion — but nothing schedules that next recalc. A revoked tag leaves the taggee's cached profiles.trust_tier stale (potentially still showing a tier the live count no longer supports) until some unrelated event (another attendance write, another manner tag) happens to re-trigger recalculate_trust_tier for that specific user.

Fix shape: add a mirroring AFTER DELETE ON manner_tags trigger calling private.recalculate_trust_tier(OLD.taggee_id).

---

11. MEDIUM — Phase 6 spec vs. reality: 총무 endorsement + manual demotion unimplemented; no dashboard reason surface

docs/specifications/attendance-and-reviews.md ("Trust & Reputation System — Specification," Draft v2) lists in its "Inputs to Tier Calculation" table:

Signal	Weight	Source
총무 endorsement	High	Manual boost by admin

And under Demotion: "Triggered by: 3+ no-shows in rolling window, reported by 3+ unique members, 총무 manual action" … "총무 sees demotion reason in dashboard."

Live private.recalculate_trust_tier (full source pulled above) only consumes: total_sessions_confirmed, COUNT(DISTINCT (tagger_id, tag_type)) manner tags, account age, global_attendance_rate, and member_reports unresolved count. There is no admin-endorsement input, no admin-manual-boost path, and no admin-manual-demote path anywhere in the function or in any RPC that calls it. "3+ no-shows" demotion is achieved only indirectly (a falling global_attendance_rate fails the trusted-tier gate on the next recompute) — functionally similar in outcome but not the explicit rolling-window strike mechanism the spec describes. There is also no dashboard surface anywhere showing 총무 why a member's tier changed (consistent with finding #12 — trust_tier_changed is a declared-but-dead signal type, so there's no notification path either).

Not a "feature is invisible" gap — trust tiers and manner-tag counts are genuinely rendered client-side (profile-hero-card.tsx, player-identity-card.tsx, club-members-screen.tsx, manner-tags-section.tsx all consume trustTier/manner-tag data). The gap is specifically the admin-authority half of the spec (endorse/demote + visibility into why), which the codebase's own "총무 authority preserved" design principle explicitly calls out as a requirement.

---

12. MEDIUM — 4 declared SignalTypes have zero emitters anywhere

packages/app/src/domain/entities/signal.entity.ts:122-125,330-333 declares trust_tier_changed, manner_tag_received, late_cancel_penalty_applied, no_show_reported as valid SignalType values. grep -rn "'trust_tier_changed'\|'manner_tag_received'\|'late_cancel_penalty_applied'\|'no_show_reported'" supabase/migrations/*.sql → 0 hits — no signals_emit call anywhere ever constructs one of these. Further grep across packages/ for these 4 strings outside the one entity file → 0 additional hits: no i18n copy, no signal-routes.ts branch, nothing. These are pure vestigial type declarations — either scaffolding for planned work that never got wired, or leftovers from an earlier design. This directly matches the audit's dimension-5 request ("signal gaps") and is a distinct issue from U8's already-filed dues_waived gap (that one doesn't even have a declared type; these 4 do, which makes them easier to wire — the type contract already exists).

---

13. LOW — Dead generate_dues_reminders() cron (see #6 for detail)

Runs daily (generate-reminders-daily, 0 21 * * *), computes a 5th overdue definition, writes to club_alerts — a table with zero live UI consumers per the codebase's own comment (packages/app/src/presentation/hooks/index.ts:65-67). Pure waste; should be dropped, not reconciled into the canonical definition.

---

14. LOW (defense-in-depth) — No server guard on RSVP-cancel-while-in-a-live-match

No trigger on rsvps blocks a status transition to cancelled while session.status = 'in_progress' and the user is embedded in a match's participant_ids. Only the purpose-built forfeit_session RPC (verified clean below) correctly pre-seeds an excused attendance row before cancelling the RSVP and re-syncs participant_ids (including enforce_settled_match_participants-safe, since it sets participant_ids in the same SET clause). The generic cancel path (useCancelRsvp → raw rsvps UPDATE) has no equivalent cleanup, but the shipped client UI only ever shows "기권하기" (which calls forfeit_session) once session.status === 'in_progress' (session-detail-screen.tsx:820-850) — the plain cancel CTA is client-gated out at that point, so this is unreachable via the app today, consistent with the "client gates it, server doesn't" pattern B2/B3 already flagged elsewhere.

---

Going well (checked and found correct)

• Strike integrity has no double-counting vector. attendance_records carries UNIQUE(user_id, session_id) and every writer (classify_rsvp_cancel, session_completed_record_attendance, confirm_attendance, excuse_attendance_record, forfeit_session, cancel_session_as_host, GPS check-in) uses INSERT … ON CONFLICT …DO UPDATE (overwrite semantics), never an increment. global_attendance_rate is a fresh COUNT(...)/(COUNT...) aggregate recomputed from scratch on every refresh_attendance_state call — structurally idempotent, no accumulator to double-fire.
• Excused-state preservation is consistent and well-composed. Every admin-initiated cancel path (cancel_session_as_host, forfeit_session, the 00301 fee-hold-expiry pre-seed in session_completed_record_attendance, 00325's admin-cancel-another's-RSVP branch in classify_rsvp_cancel) pre-seeds 'excused'/strike-0 before the cancel happens, and every downstream writer's ON CONFLICT clause explicitly preserves 'excused'/'no_show' from being clobbered by a later match-derived or timing-based verdict. 00318's fix (preserve no_show through GPS self-checkin and session completion) is intact on HEAD.
• Guest-substitute / forfeit path is correctly handled end-to-end — traced forfeit_session's auto-substitute logic (benched-confirmed preference → waitlist-promotion fallback → graceful no-sub cleanup); the forfeiter's RSVP flips to cancelled beforesession_completed_record_attendance's confirmed-RSVP loop runs, so they're correctly excluded from match-derived re-scoring, and their pre-seeded 'excused' row survives.
• total_sessions_confirmed increment race (audit round 7 #11) is correctly fixed — 00144 rewrote the read-modify-write into an atomic SET total_sessions_confirmed = COALESCE(…,0)+1, confirmed intact in the current session_completed_record_attendance source.
• Manner-tag abuse surface is well-designed. RLS requires tagger_id = auth.uid(), a CHECK (tagger_id <> taggee_id) blocks self-tagging, both tagger and taggee must have been confirmed RSVPs on a completed session within a 14-day window, and UNIQUE(session_id, tagger_id, taggee_id, tag_type) plus recalculate_trust_tier's COUNT(DISTINCT (tagger_id, tag_type)) (not per-session) caps any single tagger's lifetime contribution to a taggee's trust-tier count at 5 (one per tag type) — repeat-session collusion between two players cannot farm the tier count.
• session_completed_record_attendance idempotency guard is correct — wired as AFTER UPDATE … WHEN (OLD.status IS DISTINCT FROM 'completed' AND NEW.status = 'completed'), and guard_session_status_transition blocks any client-driven completed → * transition; no code path in the current migration set moves a session back out of completed, so no re-fire risk was found.
• fire_attendance_strike_notifications's severity tuning is current — 00385 correctly bumped attendance_warning_cooldown from medium to high per the alert-model severity audit (the escalation tiers are sound; the bug is purely the resurrection/no-resolve issue in #2).

---

Adversarial probes run (explicit list)

1. Double-strike-counting via manual admin + auto trigger for the same session → traced UNIQUE(user_id,session_id) + upsert-overwrite pattern across all 7 writers — structurally impossible (going-well #1).
2. refresh_attendance_state re-entrancy / idempotency → confirmed pure recompute-from-aggregate, no accumulator.
3. Strike decay/reset policy → none exists; worse, actively resurrects dismissed notifications (finding #2).
4. Match-derived attendance: player in match but RSVP cancelled (guest sub) → traced forfeit_session's pre-seed + participantids resync — clean. Traced the _non-forfeit generic-cancel path for the same scenario — client-gated only (finding #14).
5. Attendance for non-tracking clubs → confirmed total_sessions_confirmed still increments unconditionally even when attendance_tracking_enabled is false (finding #8).
6. Preservation-guard bypass paths → traced every ON CONFLICT clause on attendance_records across all 7 writers; all correctly preserve excused/no_show. One residual: a GPS/self 'attended' row IS overwritable by a later match-derived verdict at session completion (by design — not flagged as a bug, since match-derived is the authoritative source and GPS check-in was explicitly documented as spoofable in 00318's own comment).
7. Dues generation idempotency → DB-level UNIQUE(club_id,user_id,year,month) backstops the client-side filter (confirmed, matches U8).
8. Dues for inactive/left members → confirmed via 3 independent live sources (signals_tick_dues, get_club_admin_snapshot, and here) — none join club_members; corroborates U8 #4 server-side.
9. Year rollover → JS Date overflow (client) already verified by U8; server-side make_date/EXTRACT calls have no year-boundary special case needed (Postgres date arithmetic handles it natively) — no bug.
10. dues_day changes mid-cycle → new finding, not previously covered by U8 — traced signals_tick_dues/get_club_admin_snapshot's live clubs.dues_day join; no snapshot exists (finding #7).
11. What actually computes trust tiers → traced private.recalculate_trust_tier end-to-end against the Phase 6 spec doc line by line (finding #11).
12. Manner-tag write-path abuse (self-tagging, spam) → RLS + CHECK + UNIQUE + DISTINCT-count design all verified sound (going-well #5). Found the AFTER-DELETE recalc gap instead (finding #10).
13. SECDEF hygiene sweep across the entire public+private schema (not just B5's named functions) → found the systemic anon-EXECUTE leak (#4), the live financially-exploitable instance (#1), the zero-explicit-GRANT tables (#5), and the search_path=public sweep (#9) — all via live pg_proc/pg_default_acl/ information_schema.role_table_grants probes on a freshly-reset DB, not static migration reading.
14. Signal gaps: manner_tag_received, trust_tier_changed, no_show_reported, late_cancel_penalty_applied, dues_waived absence → confirmed all dead/absent (findings #3, #12).

---

Canon violations summary

• SECDEF discipline (AGENTS.md/CLAUDE.md hard rule) — violated live by #1 (exploitable), #4 (systemic root cause), #9 (search_path).
• Explicit data-API GRANT rule — violated by all 3 B5 tables (#5), same class B1 already flagged for club tables.
• Single source of truth — 5 divergent overdue definitions (#6), one of them dead code computing a value nobody reads (#13).
• No half-built screen / dead-end declaration — 4 vestigial SignalType declarations with zero emitters (#12), mirroring U8's own "orphaned i18n subtree" finding shape at the domain-entity layer instead.
• "총무 authority preserved" (Phase 6 design principle) — partially unmet: no endorsement/manual-demote path, no dashboard reason surface (#11).
• Everything else audited (strike upsert integrity, excused-state preservation, forfeit/guest-sub handling, manner-tag abuse surface, session-completion idempotency guard) was found compliant and well-composed — this is a mature, mostly-correct subsystem with a small number of sharp, concrete gaps rather than broad rot.

---

Canonical overdue-definition wiring plan (for the synthesis pass)

1. Delete generate_dues_reminders() + its cron job — dead output, zero UI consumer, one less divergent definition to reconcile.
2. Extract a single dues_days_past(dues_row, clubs.dues_day) helper (SQL function or view) implementing the dues_day-precise formula from signals_tick_dues — the only definition already driving real user notifications.
3. Rewire get_home_signals and get_club_admin_snapshot's overdue counts to the same helper, closing the "AdminTab strip shows 0 overdue while the cron already pushed critical" bug U8 found and this audit corroborated.
4. Join club_members.is_active into the same helper — closes U8 #4 (ex-members' immortal dues) and this audit's confirmation of it in signals_tick_dues in the same migration.
5. Decide on dues_day snapshotting (#7) — either freeze due_date per row at generation time, or explicitly accept + document the retroactive-reinterpretation behavior.
6. Broaden signals_on_dues_update's resolve/emit guard to NEW.status IN ('paid','waived','partial') + add a dues_waived SignalType (U8 fix shape, re-confirmed live here).
7. Fix the strike-notification resurrection bug (#2) independently — it's not part of the dues-overdue unification but is the single most user-visible bug in this block (dismissed alerts reappearing forever).
8. Urgent, out-of-band: close #1 (mark_session_payment_paid_via_portone anon access) and re-run/extend 00209's anon-revoke sweep (#4) — both are live security gaps, not design debt, and shouldn't wait for the dues-unification migration.