PS5 — Dues & Attendance
Status: Archived Date: 2026-07-11 Draws from: B5-attendance-trust-dues-backend.md, U8-dues.md, U2-clubs.md (finding #2, partial-payment amount discarded). Canonical companion: wiring-plans-2026-07-backend.md §ATTENDANCE/TRUST/DUES (D1–D10). This doc does not repeat those plans' mechanics — it supplies the research grounding, resolves two design decisions the wiring plan left open (the refresh_attendance_state signature widening mechanics, and the partial-payment schema shape), and surfaces one finding the wiring plan didn't have: an orphaned payment_history table that changes the correct fix shape for D2/D6/U2 #2 from "add a column" to "wire up dead infra." Boundary with PS6/PS7: account-deletion dues tombstoning (C1 in the wiring plan) is owned by whichever PS designs club-membership-lifecycle + identity deletion (PS6/PS7) — this doc's D3 only closes the ongoing ex-member case (a member who left through the normal path, club_members row hard-deleted or is_active=false), not the deletion-cascade case. See §5 boundary note. Already shipped, cited only: 00385_alert_model_wiring.sql (current baseline signal-severity tuning for the dues/attendance signal types this doc extends — not itself a fix for anything below).
1. Problem statement
The dues and attendance subsystems (migrations 00009–00385, live-verified 2026-07-11 against a fresh local reset at HEAD 00387) are functionally mature — strike accounting has no double-counting vector, excused-state preservation is consistent across all 7 attendance writers, and the manner-tag abuse surface is well-designed (§"Going well" in both source audits). But eight independently-confirmed defects erode the two systems' trustworthiness precisely where a 총무 or member would notice — money and standing:
- CRITICAL — five, not four, mutually-inconsistent definitions of "overdue" dues live concurrently (
B5#6, extendsU8#2).get_home_signals(unpaid+partial, strictly-past calendar month),get_club_admin_snapshot(unpaidonly, same calendar-month boundary, deliberately not matching the player-side definition per its own code comment),signals_tick_dues(unpaidonly,dues_day-precise days-past — can fire within the current calendar month), the clientSummaryCard(no overdue concept, raw status buckets for the selected month), and the deadgenerate_dues_reminders()cron (unpaid + current-month-only, writes to a table with zero UI consumers). Concrete failure: a member can be receiving a critical-severitydues_severely_overduepush 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. - CRITICAL — 면제(waive)/부분납부(partial) dues transitions leave a permanently-stuck audit and notification trail (
B5#3, confirmsU8#1/#2).signals_on_dues_update's only branch isIF NEW.status = 'paid'— waiving a severely-overdue member's dues never resolves the critical signal already on their device (no expiry, no future re-check — it is permanent), never notifies them anything happened, and the mapper dropscleared_by/cleared_atfor any non-'paid'status even though the client already threads the admin's identity through. - CRITICAL — the partial-payment feature is decorative (
U2#2, extends intoB5/U8). The admin types a real 부분납부 amount on a number pad; it is parsed, then passed to a underscore-prefixed unused parameter and discarded.UpdateDuesStatusInputhas no amount field. The aggregate "부분납부" bucket sums the full face amount for every partially-paid row, not what was actually received. This is compounded by finding #2 above — a partial payment can't even silence the signal it's supposed to partially resolve. - CRITICAL — attendance strike-ladder notifications resurrect indefinitely (
B5#2).private.refresh_attendance_stateruns on everyattendance_recordswrite for a user (including a completely clean match-derived'attended'row) and looks up the most recent strike-bearing session ever — not the session that triggered this specific call — then unconditionally re-firesfire_attendance_strike_notifications.signals_emit's dedup-upsert resetsacknowledged_at/resolved_attoNULLon every re-fire, and zero resolve call exists anywhere forattendance_warning_cooldown/attendance_flagged_admin. Concrete failure: a member who hit 2 strikes in April and plays cleanly for the next 15 sessions has their dismissed cooldown warning (and the admins' dismissed "flagged member" alert) pop back to unread/unresolved on every single one of those clean sessions. - HIGH — ex-members' unpaid dues are immortal (
B5#4, confirmsU8#4). No dues consumer — notsignals_tick_dues, notget_club_admin_snapshot— joinsclub_members. A departed member keeps escalating to critical-severity pushes for a club they can no longer act in, forever. - MEDIUM —
dues_dayis not snapshotted per period (B5#7). Every consumer derivesdue_datelive from the currentclubs.dues_day. A 총무 changing the setting mid-cycle silently reinterprets every past unpaid row's due date with no notice. - MEDIUM-HIGH — phantom attendance (
B5#8, self-documented as deferred in00318, confirmed still open).total_sessions_confirmed(a trust-tier input) bumps unconditionally on session completion, independent ofattendance_tracking_enabledand independent of whether the session ever actually had match activity. - HIGH — no bank-account/payment-instructions surface (
U8#3). A member opening 회비 sees "미납 ₩30,000" and zero actionable information — not even which of the 5 already-modeledPAYMENT_METHODSthe club prefers. Every payment forces an out-of-app KakaoTalk exchange, on the app's core money loop.
Two smaller, independently-fixable defects ride along with the above because they share files/ triggers this pass already touches: LOW-MEDIUM — manner_tags has an AFTER INSERT trust-tier recalc trigger but no AFTER DELETE (B5 #10), so a tagger revoking a tag within the 24h window doesn't retroactively lower the taggee's cached tier. MEDIUM — 3 of the 20 search_path=public SECDEF functions flagged in B5 #9 (signals_tick_dues, signals_on_dues_insert/delete) sit directly inside this pass's blast radius; the other 17 are cross-domain hygiene tracked at wiring- plan D8, not this doc's scope (see §5).
Finally, B5 #11/#12 catalog a spec-vs-reality gap: the Phase 6 trust-tier spec's "총무 endorsement"/"총무 manual demotion" inputs have zero implementation, and 4 SignalType union members (trust_tier_changed, manner_tag_received, late_cancel_penalty_applied, no_show_reported) have zero emitters. §4.6 makes an explicit scope call on these.
2. Research
2.1 The canonical "overdue" definition — five formulas pulled live, one adopted as ground truth
Reading all five side by side (live source, not migration-text-only, per this doc's own verification pass against a fresh db reset at HEAD 00387):
| Surface | Status set | Time granularity | Scope |
|---|---|---|---|
get_home_signals (00103) | unpaid + partial | strictly-past calendar month (year<v_year OR (year=v_year AND month<v_month)) | all clubs, player-scoped |
get_club_admin_snapshot (00381) | unpaid only (comment: "deliberately NOT replicated … per the task spec") | strictly-past calendar month, same formula | one club, admin-scoped |
signals_tick_dues (00108) | unpaid only | dues_day-precise days-past (due_date = make_date(year, month, LEAST(dues_day, last_day)); escalates from day +1, i.e. within the current calendar month) | one row at a time, per-member push |
SummaryCard (client) | paid/unpaid/partial buckets | no "overdue" concept — whatever status the selected month's row carries | one club, one month |
generate_dues_reminders() (dead) | unpaid only | current calendar month only | one club, zero-consumer output table |
signals_tick_dues's formula is the only one already driving a real user-facing consequence (the push notification a member actually receives), and the only one that is per-club configurable via a real setting rather than a hardcoded calendar boundary. It is adopted as ground truth. Its own day-clamp defense (LEAST(dues_day, last_day_of_month), guarding a hypothetical dues_day=30-in-February) is empirically dead code today: clubs_dues_day_check (live, pg_get_constraintdef) is CHECK (dues_day >= 1 AND dues_day <= 28) — dues_day can never exceed the shortest month's length, so the clamp never actually activates. Kept anyway as defense-in-depth (cheap, and the CHECK constraint could theoretically be loosened later without this code needing a second look).
Concrete failure case, reproduced from B5's own worked example: club has dues_day=5. On the 10th of the current month, a member is unpaid. signals_tick_dues already computed days_past=5 on day 10 (≥1 → dues_overdue, high) and will hit days_past≥7 on the 17th (→ dues_severely_overdue, critical) — all within the same calendar month. get_club_admin_snapshot's filter (month < v_month) excludes the current month categorically — the AdminTab strip, the product's designed "start here" surface, shows "회비 미납 0건" the entire time.
2.2 Period-boundary handling — snapshot-at-generation, not live-recompute
Every current consumer derives due_date live via a JOIN clubs c ON c.id = d.club_id, reading clubs.dues_day as of query time, not as of dues-row-generation time (B5 #7). This repo already has a settled precedent for the opposite choice: session_payments.amount is frozen at hold-creation time (PS2 §2b/§3, 00298–00303) specifically so a later fee edit can't retroactively reinterpret an existing hold. The same reasoning applies here — an invoice's due date is conventionally fixed at issuance in every accounting system this schema takes inspiration from (the duesSchema's own amount/year/month are already immutable-by-convention once generated). Design choice: snapshot due_date onto each dues row at INSERT time, via a BEFORE INSERT trigger reading clubs.dues_day only when the row is created — mirroring the protect_columns- adjacent "compute-once, protect-after" pattern already used for session_payments. This was empirically verified in a rolled-back local transaction (§3.2): a row generated while dues_day=28 keeps due_date=2026-02-28 even after clubs.dues_day is subsequently changed to 5 within the same transaction.
Why a trigger over a client-side compute-and-insert: handleGenerateDues (club-dues-screen.tsx: 238-268) does a plain client-side .insert(rows) with no RPC — a trigger requires zero client change to correctly backfill due_date on every future generation path (including any future mid-month "add missing member" affordance from U8 #5, which is out of this doc's scope but benefits automatically).
2.3 Partial-payment accounting — an owed/paid/balance ledger already exists, unused
UpdateDuesStatusInput (packages/app/src/domain/entities/dues.entity.ts:51-63) genuinely has no amount field — U2's finding is accurate. But researching "how should a partial-payment ledger be shaped" surfaced something neither source audit found: public.payment_history — a fully schema'd, fully RLS'd table (00013_payment_history_and_match_director_policies.sql, live-verified \d public.payment_history) with exactly the shape a partial-payment audit trail needs: dues_id, club_id, user_id, amount, payment_method, recorded_by, note, created_at, RLS scoped to club owner/admin for INSERT/UPDATE/DELETE and self-or-admin for SELECT. Its domain-layer mirror (PaymentRecord, CreatePaymentRecordInput, paymentRecordSchema) is fully written and exported from packages/app/src/domain/index.ts:198-206. Neither has a single consumer: grep -rn "payment_history" across packages/app/src/{ports,adapters} and packages/features → zero hits beyond the generated-types file and the domain entity declaration itself. This table has sat dead since migration 00013 — before dues even had a partial status.
This directly changes the correct fix shape. The standard "owed / paid / balance" ledger pattern (every payments system from Stripe's PaymentIntent captures to a plain accounting ledger) keeps a running sum of receipts as the source of truth, with any "amount paid so far" field on the parent record being a derived rollup, not a raw writable column — because a single scalar column invites exactly the "two admins mark two different partial payments, second overwrites first" race a real ledger avoids. Design: payment_history becomes the INSERT-only receipt ledger; dues.paid_amount becomes a denormalized rollup kept in sync by a trigger — the same denormalize-with-trigger shape this schema already uses for profiles.total_sessions_confirmed and club_members.no_show_count. This is materially better than U2's own proposed fix shape ("add a paidAmount field") because it also closes a gap neither audit flagged: today there is no audit trail at all for which payment (bank transfer on which date, cash, etc.) makes up a partial balance — only a single opaque status flag.
2.4 Waive/partial signal blind spot — resolve broadly, let the next cron cycle re-escalate honestly
B5/U8 converge on the same fix shape: broaden signals_on_dues_update's resolve guard from NEW.status = 'paid' to NEW.status IN ('paid', 'waived', 'partial'). The open design question is what happens to a 'partial' row that is still substantively unpaid (e.g. ₩5,000 of ₩30,000) — should resolving dues_severely_overdue on that transition let the debt go dark? No — the correct model, confirmed against signals_tick_dues's daily cron cadence, is: resolve the existing stuck signal immediately (closes the "already partially acknowledged, still shouting critical" bug), and let the next day's cron run re-evaluate the row against the canonical overdue definition (§2.1, extended in §4.2 to treat status='partial' AND paid_amount < amount as still-overdue) and emit a fresh signal with a fresh dedup_key if genuinely still owed. This is not a resurrection bug (that class is entirely about a stale dedup key resurfacing an already-acted-on signal) — it's the system correctly re-flagging a debt that is, in fact, still outstanding, exactly the same way a real collections system would re-notify after a partial payment doesn't clear the balance.
2.5 Strike-notification resurrection — decouple "detect decay" from "emit new"
Live-traced private.refresh_attendance_state(p_user_id UUID) — its signature takes onlyp_user_id, called from 15 sites across 12 migrations (00127, 00128, 00129, 00131, 00136, 00154, 00206, 00287, 00288 [dev-only], 00310, 00318, 00325, 00384). Every call site is itself a trigger or RPC that just wrote (or deleted) an attendance_records row and therefore already has that row's session_id, club_id, and strike_value in scope (verified by reading 00127's classify_rsvp_cancel/session_completed_record_attendance in full: both compose an explicit strike_value local variable before the INSERT ... ON CONFLICT and could trivially pass it through). The resurrection bug is entirely caused by refresh_attendance_state re-deriving "the most recent strike-bearing session" from scratch on every call instead of being told what just happened.
Two behaviors need to be separated, because they have different correctness requirements:
- Emit a new strike notification — must only happen when this specific write carries
strike_value > 0. Requires the caller to pass the triggering record's context. - Resolve a stale notification once strikes age out of the trailing-20 window — this is a pure function of the user's current
strikesInWindowcount, needs no trigger context at all, and can therefore run unconditionally on everyrefresh_attendance_statecall regardless of whether the call sites are updated. This makes the decay-resolve half of the fix safe to ship even if an obscure 16th call site is missed — a materially lower-risk rollout than "touch all 15 call sites correctly or the fix silently doesn't work everywhere."
Verified live (§3.3, rolled-back transaction): a plain UPDATE public.signals SET resolved_at = now() WHERE type = <t> AND recipient_user_id = <u> AND resolved_at IS NULL correctly bulk-resolves multiple dedup'd signals for one recipient in one statement — no new generic primitive is required beyond the existing signals_resolve_by_dedup_key, just a second, broader resolve call scoped by (type, recipient) instead of exact dedup_key. For attendance_flagged_admin specifically, the recipient is the admin, not the flagged member, so the equivalent resolve must match on payload->>'flaggedUserId' instead of recipient_user_id — both are one-line UPDATEs, no schema change needed since payload already carries flaggedUserId (00132).
2.6 Ex-member exclusion — the hard-delete leave path already does half the work
club_members_delete's live RLS policy (USING (is_club_admin(club_id) OR auth.uid() = user_id)) and the absence of any leave_club/remove_member RPC in pg_proc confirm B5's framing: today's leave/remove path is a raw client-side hard DELETE on club_members, not a soft-deactivate — club_members.is_active is never flipped to false anywhere in the current migration history (grep "is_active = false" supabase/migrations/*.sql matches only unrelated DM-thread-participant triggers). This matters for the fix shape: a plain INNER JOIN club_members cm ON cm.club_id = d.club_id AND cm.user_id = d.user_id AND cm.is_active = true correctly excludes both the current hard-delete-on-leave case (no matching row at all → excluded by the join) and any future soft-deactivation path (e.g. the account-deletion trigger PS6/PS7 designs for C1) in the same predicate — no membership-mechanism-specific branching needed in the dues helper itself.
2.7 Trust-tier Phase 6 — scope recommendation
private.recalculate_trust_tier (live source, full read) consumes exactly five inputs: total_sessions_confirmed, COUNT(DISTINCT (tagger_id, tag_type)) manner tags, account age, global_attendance_rate, and unresolved member_reports count. The spec's "총무 endorsement" (admin manual boost) and "총무 manual demotion" (admin manual action + dashboard reason surface) have zero implementation path — not a stub, not a half-built RPC, nothing. This is materially larger than an additive migration: it needs a new authority model (who can endorse, rate limits, reversibility), new RPC(s), a dashboard surface for "why did this member's tier change," and product decisions this audit pass has no mandate to make unilaterally. Recommendation: out of scope for this pass, flagged for a dedicated Phase 6 planning pass (matches wiring-plan D9). The two small, independently-valuable pieces adjacent to this gap ARE in scope here because they cost almost nothing standalone: manner_tags' missing AFTER DELETE recalc trigger (§4.7) and wiring the already-declared, zero-cost manner_tag_received signal type off the existing AFTER INSERT trigger (§4.8) — both ship independently of the endorsement/demotion feature. The other 3 vestigial SignalTypes (trust_tier_changed, late_cancel_penalty_applied, no_show_reported) stay declared-but-dead, explicitly deferred to whichever pass ships Phase 6 proper (deleting them now would just require re-adding them later for no interim benefit).
3. Empirical verification (rolled-back local Postgres, HEAD 00387)
Per this project's research methodology (verify what actually runs, don't trust migration text alone), three of this doc's non-obvious mechanics were executed — not just read — against a fresh local reset, inside BEGIN … ROLLBACK transactions (no committed state):
3.1 — clubs_dues_day_check bound. pg_get_constraintdef confirms CHECK (dues_day >= 1 AND dues_day <= 28) — corroborates §2.1's "the Feb-30 clamp is defensive dead code" claim precisely, not just by inspection.
3.2 — due_date snapshot survives a mid-transaction dues_day change. Seeded a club with dues_day=28, inserted a dues row via a prototype BEFORE INSERT trigger implementing §2.2's design (due_date := make_date(year, month, LEAST(dues_day, last_day_of_month))), confirmed due_date = 2026-02-28; then updated the same club's dues_day to 5 in the same transaction and re-selected the row — due_date was unchanged at 2026-02-28. Confirms the snapshot design actually decouples past rows from live setting changes, not just in theory.
3.3 — bulk resolve-by-type-for-recipient. Emitted two distinct attendance_warning_cooldown signals (different dedup_keys) for one recipient, ran UPDATE public.signals SET resolved_at = now() WHERE type = 'attendance_warning_cooldown' AND recipient_user_id = <u> AND resolved_at IS NULL, confirmed unresolved count dropped from 2 to 0 in one statement — validates §2.5's decay- resolve primitive needs no new generic infrastructure.
3.4 — paid_amount bound CHECK. Added a prototype CHECK (paid_amount >= 0 AND paid_amount <= amount), attempted UPDATE dues SET paid_amount = 999999 against a ₩30,000 row inside a nested EXCEPTION WHEN check_violation block — correctly rejected. Confirms the ledger-rollup bound (§4.3) is enforceable at the DB layer, not just an application-level convention.
All four probes were rolled back; no schema or data changes persist from this research pass.
4. Solution design
4.1 private.dues_with_overdue_state — the one view every "overdue" surface reads
CREATE VIEW private.dues_with_overdue_state AS
SELECT
d.id, d.club_id, d.user_id, d.year, d.month, d.amount, d.paid_amount,
d.status, d.due_date,
(CURRENT_DATE - d.due_date)::int AS days_past,
cm.is_active AS member_is_active,
-- canonical "still substantively owed" predicate — folds in the partial-
-- amount ledger from §4.3 per §2.4's re-escalation design
(d.status = 'unpaid' OR (d.status = 'partial' AND d.paid_amount < d.amount)) AS is_owed
FROM public.dues d
JOIN public.club_members cm
ON cm.club_id = d.club_id AND cm.user_id = d.user_id AND cm.is_active = true;get_home_signals (00103), get_club_admin_snapshot (00381), and signals_tick_dues (00108) are rewired to SELECT ... FROM private.dues_with_overdue_state WHERE is_owed AND days_past >= <threshold> instead of each re-deriving calendar-month/unpaid-only shortcuts — closing the 5-way divergence (§1 #1) and the ex-member leak (§1 #5) in the same view, since the club_members join is unconditional. SummaryCard (client, no migration) should read the same predicate in a later client-side follow-up — flagged, not blocked on this migration (matches wiring-plan D3's own framing).
4.2 dues.due_date — snapshot at generation, never re-derived live
ALTER TABLE public.dues ADD COLUMN due_date DATE;
CREATE OR REPLACE FUNCTION private.snapshot_dues_due_date()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
DECLARE v_dues_day int;
BEGIN
IF NEW.due_date IS NULL THEN
SELECT dues_day INTO v_dues_day FROM public.clubs WHERE id = NEW.club_id;
NEW.due_date := make_date(NEW.year, NEW.month,
LEAST(COALESCE(v_dues_day, 1),
EXTRACT(DAY FROM (make_date(NEW.year, NEW.month, 1) + INTERVAL '1 month - 1 day'))::int));
END IF;
RETURN NEW;
END;
$$;
REVOKE ALL ON FUNCTION private.snapshot_dues_due_date() FROM PUBLIC;
CREATE TRIGGER dues_snapshot_due_date BEFORE INSERT ON public.dues
FOR EACH ROW EXECUTE FUNCTION private.snapshot_dues_due_date();
-- Backfill existing rows with the CURRENT clubs.dues_day (best-effort — the
-- historical dues_day at each row's actual generation time is unrecoverable).
UPDATE public.dues d SET due_date = make_date(d.year, d.month,
LEAST(c.dues_day, EXTRACT(DAY FROM (make_date(d.year, d.month, 1) + INTERVAL '1 month - 1 day'))::int))
FROM public.clubs c WHERE c.id = d.club_id AND d.due_date IS NULL;
ALTER TABLE public.dues ALTER COLUMN due_date SET NOT NULL;NULL-guard on the trigger (rather than unconditional overwrite) leaves room for a future explicit override (e.g. a manual due-date correction RPC) without fighting the trigger — consistent with how protect_columns elsewhere distinguishes "server-computed" from "server-computed-once."
4.3 payment_history ledger + dues.paid_amount rollup — wiring up dead infrastructure
ALTER TABLE public.dues ADD COLUMN paid_amount integer NOT NULL DEFAULT 0;
ALTER TABLE public.dues ADD CONSTRAINT dues_paid_amount_bounds
CHECK (paid_amount >= 0 AND paid_amount <= amount);
ALTER TABLE public.dues ADD CONSTRAINT dues_partial_requires_balance
CHECK (status <> 'partial' OR (paid_amount > 0 AND paid_amount < amount));
CREATE OR REPLACE FUNCTION private.sync_dues_paid_amount()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
UPDATE public.dues SET paid_amount = (
SELECT COALESCE(SUM(amount), 0) FROM public.payment_history WHERE dues_id = NEW.dues_id
) WHERE id = NEW.dues_id;
RETURN NEW;
END;
$$;
REVOKE ALL ON FUNCTION private.sync_dues_paid_amount() FROM PUBLIC;
CREATE TRIGGER payment_history_sync_dues_paid_amount
AFTER INSERT ON public.payment_history
FOR EACH ROW EXECUTE FUNCTION private.sync_dues_paid_amount();
-- Reject a receipt that would push the cumulative ledger past the dues row's
-- face amount (the money-adjacent invariant this feature currently has none
-- of at all).
CREATE OR REPLACE FUNCTION private.guard_payment_history_overpay()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
DECLARE v_amount int; v_paid_so_far int;
BEGIN
SELECT amount INTO v_amount FROM public.dues WHERE id = NEW.dues_id;
SELECT COALESCE(SUM(amount), 0) INTO v_paid_so_far
FROM public.payment_history WHERE dues_id = NEW.dues_id;
IF v_paid_so_far + NEW.amount > v_amount THEN
RAISE EXCEPTION 'payment_history: cumulative receipts (%) would exceed dues amount (%)',
v_paid_so_far + NEW.amount, v_amount USING ERRCODE = 'check_violation';
END IF;
RETURN NEW;
END;
$$;
REVOKE ALL ON FUNCTION private.guard_payment_history_overpay() FROM PUBLIC;
CREATE TRIGGER payment_history_guard_overpay
BEFORE INSERT ON public.payment_history
FOR EACH ROW EXECUTE FUNCTION private.guard_payment_history_overpay();
CREATE TRIGGER protect_dues_ledger_columns
BEFORE UPDATE ON public.dues
FOR EACH ROW EXECUTE FUNCTION private.protect_columns('paid_amount', 'due_date');paid_amount/due_date join the established protect_columns idiom (PS2 §2c precedent, 00067/00319) — both are server-derived, never directly client-writable; paid_amount is only ever moved by the payment_history trigger, due_date only ever set once by the BEFORE INSERT snapshot.
Client companion (not a migration):
packages/app/src/domain/entities/dues.entity.ts— addpaidAmount: numbertoDues+duesSchema; addpaidAmount?: numberto a newCreatePaymentRecordInput-adjacent flow (the existingPaymentRecord/CreatePaymentRecordInput/paymentRecordSchematypes already exist and need no entity change — only the missing port/adapter/mapper/hook layer).- New port method on
dues.repository.port.ts:recordPayment(input: CreatePaymentRecordInput): Promise<PaymentRecord>→ adapterdues.supabase.tsinserts intopayment_history; newpayment-history.mapper.ts(toDomain/toRow) mirroringdues.mapper.ts's shape exactly. club-dues-screen.tsx:183-196(handlePartialSubmit) — stop discarding_amount; call the newrecordPaymentmutation with the entered amount and the existingupdateDuesStatus.mutate({ status: 'partial', ... })— two calls from one handler, matching the codebase's existing non-RPC dues-write pattern (no new RPC introduced for consistency withmarkPaid/handleWaive).handleMarkPaid's existing call site — also insert apayment_historyrow for the fullamount(or remaining balance if previously'partial') sopaid_amountstays accurate for every path, not just partial.dues.mapper.ts:toUpdateRow— fix the D2-flagged bug in the same pass: writecleared_by/cleared_atwheneverclearedByUserIdis supplied, regardless ofstatus; mirror inuse-update-dues.ts'smergeDuesPatchoptimistic patch.dues-row.tsx— whenstatus === 'partial', render"₩10,000 / ₩30,000"(paidAmount/amount) instead of the current bare face amount.dues-summary-card.tsx:19—partialSumsumsd.paidAmount, notd.amount— this is U2 #2's aggregate-lie fix, now backed by a real column.
4.4 Waive/partial signal + audit-trail broadening
-- signals_on_dues_update, extending the existing trigger (00385 baseline):
IF NEW.status IN ('paid', 'waived', 'partial') AND OLD.status IS DISTINCT FROM NEW.status 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);
IF NEW.status = 'waived' THEN
PERFORM public.signals_emit(
p_type := 'dues_waived', p_category := 'dues'::public.signal_category,
p_severity := 'medium'::public.signal_severity, p_recipient_user_id := NEW.user_id,
p_dedup_key := 'dues_waived:' || NEW.id,
p_title_key := 'signal.dues.waived.title', p_body_key := 'signal.dues.waived.body',
p_context := jsonb_build_object('clubId', NEW.club_id, 'duesId', NEW.id),
p_payload := jsonb_build_object('amount', NEW.amount, 'year', NEW.year, 'month', NEW.month),
p_expires_at := pg_catalog.now() + INTERVAL '24 hours'
);
ELSIF NEW.status = 'paid' THEN
-- existing dues_cleared_by_admin / dues_paid branch, unchanged
...
END IF;
-- 'partial' intentionally emits nothing new here — the resolve calls above
-- already close the stale critical alert (§2.4); signals_tick_dues's next
-- daily run re-escalates honestly via dues_with_overdue_state.is_owed if
-- the row is still substantively unpaid.
END IF;Also fold in search_path = '' for this function's two search_path=public siblings touched by this migration (signals_on_dues_insert, signals_on_dues_delete — B5 #9's dues-domain subset; the other 17 cross-domain instances stay at wiring-plan D8, out of this doc's scope per §1).
Domain change: add 'dues_waived' to SignalType (signal.entity.ts:73-79) alongside the existing dues_* members; add signal.dues.waived.title/.body i18n keys mirroring signal.dues.clearedByAdmin.*'s shape.
4.5 Strike-ladder resurrection fix — widened signature, decay-resolve unconditional
CREATE OR REPLACE FUNCTION private.refresh_attendance_state(
p_user_id UUID,
p_trigger_session_id UUID DEFAULT NULL,
p_trigger_club_id UUID DEFAULT NULL,
p_trigger_strike_value NUMERIC DEFAULT NULL
) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
DECLARE v_rate REAL; v_strikes_in_window INT;
BEGIN
-- (unchanged) recompute global_attendance_rate + trust tier
...
PERFORM private.recalculate_trust_tier(p_user_id);
SELECT COUNT(*) INTO v_strikes_in_window FROM (
SELECT strike_value FROM public.attendance_records
WHERE user_id = p_user_id ORDER BY created_at DESC LIMIT 20
) recent WHERE strike_value > 0;
-- Decay-resolve: runs on EVERY call, independent of trigger args, so this
-- half of the fix is correct even for a call site not yet updated to pass
-- trigger context (§2.5's risk-reduction property).
IF v_strikes_in_window < 2 THEN
UPDATE public.signals SET resolved_at = pg_catalog.now()
WHERE type = 'attendance_warning_cooldown' AND recipient_user_id = p_user_id
AND resolved_at IS NULL;
END IF;
IF v_strikes_in_window < 3 THEN
UPDATE public.signals SET resolved_at = pg_catalog.now()
WHERE type = 'attendance_flagged_admin' AND payload->>'flaggedUserId' = p_user_id::text
AND resolved_at IS NULL;
END IF;
-- Emit-new: only when THIS write carries a real strike, using the caller's
-- own context — never a fresh "most recent ever" lookup.
IF p_trigger_strike_value IS NOT NULL AND p_trigger_strike_value > 0
AND p_trigger_session_id IS NOT NULL THEN
PERFORM private.fire_attendance_strike_notifications(
p_user_id, p_trigger_session_id, COALESCE(p_trigger_club_id, p_user_id), v_strikes_in_window
);
END IF;
END;
$$;fire_attendance_strike_notifications gains a 4th parameter (p_strikes_in_window INT) so it no longer recomputes the window count itself (single source of truth with the caller) — its emit branches (1/2/3+ thresholds) are otherwise unchanged. All 15 call sites (§2.5's enumerated list) are updated to pass their already-in-scope session_id/club_id/strike_value instead of bare p_user_id — mechanical, one line each, safe to delegate at IMPLEMENT time given exact file:line references are already enumerated in §2.5.
4.6 Phantom attendance gate
-- Inside session_completed_record_attendance's existing FOR v_rsvp loop:
-- move the total_sessions_confirmed bump inside the same tracking-enabled +
-- v_played gate that already guards the attendance_records write, instead
-- of firing unconditionally before it.Full body per wiring-plan D5 — no new mechanism, just reordering an existing guard to also cover the counter increment.
4.7 manner_tags AFTER DELETE recalc
CREATE TRIGGER manner_tags_after_delete_recalc
AFTER DELETE ON public.manner_tags
FOR EACH ROW EXECUTE FUNCTION private.recalculate_trust_tier_from_delete();
-- thin wrapper calling private.recalculate_trust_tier(OLD.taggee_id) — the
-- existing function returns text (the new tier), fine to PERFORM-discard.4.8 Bonus: wire manner_tag_received off the existing AFTER INSERT trigger
Zero-cost given §2.7's scope call — manner_tags_after_insert_recalc already has NEW.tagger_id, NEW.taggee_id, NEW.tag_type in scope; add one signals_emit('manner_tag_received', 'trust', 'low', NEW.taggee_id, 'manner_tag:' || NEW.id, ...) call. Closes one of the four B5 #12 vestigial types independent of the deferred Phase 6 endorsement work.
4.9 Dead cron removal
DROP FUNCTION IF EXISTS public.generate_dues_reminders();
SELECT cron.alter_job(
(SELECT jobid FROM cron.job WHERE jobname = 'generate-reminders-daily'),
command => 'SELECT generate_session_reminders();'
);generate-reminders-daily's cron command currently runs both generate_dues_reminders() and generate_session_reminders() in one command string (live-verified via cron.job) — the fix edits the job's command to drop only the dues half, not the whole job (generate_session_reminders is a live, unrelated function).
4.10 Bank-account / payment-instructions schema
ALTER TABLE public.clubs ADD COLUMN dues_payment_info jsonb;
-- shape: { bankName?, accountNumber?, accountHolder?, preferredMethod: PaymentMethod }No new RLS policy needed — ordinary club-settings field under the existing admin-gated clubs_update authorization surface, same as dues_amount/dues_day. No PII concern (club's own collection info). Client entity/mapper/settings-field/member-view-card work is a scoped follow-up per feedback_entity_migration_checklist.md, out of this migration's own blast radius.
5. Slices
| Slice | Migration | Type | Files | Blast radius | Depends on |
|---|---|---|---|---|---|
| 5.1 Canonical overdue view + ex-member exclusion + dead cron removal | 00408 | migration | private.dues_with_overdue_state, get_home_signals, get_club_admin_snapshot, signals_tick_dues, generate_dues_reminders (drop), generate-reminders-daily cron command | High-visibility read surfaces (Home badge, AdminTab strip); no write-path change | needs due_date/paid_amount columns from 5.2/5.3 to exist first if bundled in one file, else sequence after |
5.2 due_date snapshot | 00409 | migration | dues (new column + trigger + backfill) | dues INSERT path only | none |
5.3 payment_history ledger wiring + paid_amount rollup | 00410 (migration) + client-OTA companion | migration + client | payment_history triggers, dues.paid_amount/CHECKs; new port method + adapter + payment-history.mapper.ts; club-dues-screen.tsx, dues-row.tsx, dues-summary-card.tsx, dues.mapper.ts, use-update-dues.ts | dues/payment_history write paths; UI money-display sites | 5.2 (protect_columns list references due_date) |
| 5.4 Waive/partial signal + audit-trail broadening | 00411 | migration | signals_on_dues_update, signals_on_dues_insert/_delete (search_path fix), signal.entity.ts (dues_waived), i18n | dues UPDATE-of-status trigger, signals | 5.1 (dues_with_overdue_state.is_owed referenced by re-escalation reasoning, not a hard code dependency) |
| 5.5 Bank-account/payment-info schema | 00412 | migration + client follow-up | clubs.dues_payment_info | additive, nullable | none |
| 5.6 Attendance strike-ladder resurrection fix | 00407 | migration | refresh_attendance_state, fire_attendance_strike_notifications, 15 call sites | every attendance_records writer | none (highest standalone user-visible value — sequence early per wiring plan) |
5.7 Phantom attendance gate + manner_tags AFTER DELETE + manner_tag_received wiring | 00413 | migration | session_completed_record_attendance, new manner_tags_after_delete_recalc trigger, manner_tags_after_insert_recalc (add one emit call) | profiles.total_sessions_confirmed, profiles.trust_tier | none |
Verification per slice
- 5.1: seed a club with
dues_day=5, a memberunpaidsince the 1st, check on the 10th (same calendar month) — assertget_club_admin_snapshot's count now includes the row (previously excluded). Seed a departed member (hard-deletedclub_membersrow) with unpaid dues — assert they no longer appear in any overdue count andsignals_tick_duesstops escalating them. Confirmgenerate-reminders-dailystill firesgenerate_session_reminders()post-edit. - 5.2: generate dues, change
clubs.dues_day, confirm the already-generated row'sdue_dateis unaffected (this doc's own §3.2 probe, promoted to a permanent migration test). - 5.3: insert a
payment_historyrow for ₩10,000 against a ₩30,000 dues row — assertdues.paid_amountbecomes 10000 via the trigger. Attempt a second receipt that would push the sum past 30000 — assert rejection. Attempt a direct clientUPDATE dues SET paid_amount = ...— assert silent revert (protect_columnsbehavior). Client:handlePartialSubmitround-trips a real amount end-to-end toDuesRow's "₩X / ₩Y" display;SummaryCard's partial bucket sums actual receipts. - 5.4: waive a severely-overdue dues row — assert
cleared_by/cleared_atpopulate, adues_waivedsignal fires, the previously-emitteddues_severely_overdueresolves. Repeat forpartialwith a real receipt — assert the same resolve, and assertsignals_tick_dues's next run re-escalates (fresh dedup key) only ifpaid_amount < amountstill holds. - 5.5:
yarn check:supabase-typespicks up the new column; a settings-screen write round-trips (client-side follow-up, DOC-phase). - 5.6: local probe — user accrues 2 strikes, then 15 clean match-derived attendance writes. Before: each write re-fires/reopens the dismissed signals. After: no re-fire (no
strike_value>0on the triggering write), and the corresponding signal resolves once strikes age out of the trailing-20 window on a subsequent call, independent of that call's own trigger context. - 5.7: a tracking-off club's completed session — assert
total_sessions_confirmedno longer increments. Tag a user, delete the tag within 24h — asserttrust_tierrecomputes immediately. Tag a user — assert amanner_tag_receivedsignal fires.
6. Boundary notes
PS5 / PS6 / PS7 (account deletion, C1). C1's "transition the departed user's outstanding dues rows to 'waived' with a system note" is part of the account-deletion atomic transaction (AFTER UPDATE OF deleted_at ON profiles), owned by whichever PS designs club-membership-lifecycle and identity deletion. This doc's §4.1 dues_with_overdue_state view already stops escalating a departed member's dues the moment their club_members row disappears or deactivates — regardless of whether C1 additionally waives the row outright — so there is no hard ordering dependency between the two; either can land first without the other regressing.
PS5 / PS1 (search-path hygiene, D8). Only the 3 dues-signal-trigger functions this pass already touches (signals_on_dues_insert/_delete, folded into slice 5.4) get their search_path='' fix here. The remaining 17 cross-domain instances (get_club_analytics, signals_on_match_insert, etc.) are wiring-plan D8's full sweep — not owned by any single PS doc today; flagged for the roadmap to assign (candidate: PS11 guardrails, or a standalone hygiene migration, since it's zero-urgency pure hygiene per the wiring plan's own framing).
Deferred, no migration assigned: D9 (Phase 6 trust-tier endorsement/demotion feature — §2.7), 3 of 4 vestigial SignalTypes (trust_tier_changed, late_cancel_penalty_applied, no_show_reported — the 4th, manner_tag_received, ships in slice 5.7).