Guest Reputation System

Public reputation for Guest Network profiles: host-authored, guest-approved endorsements, a private host→guest rating corpus feeding a consent-gated aggregate + badges, and a moderation surface. Design charter: docs/planning/plans/2026-07-12-guest-reputation.md.

Migrations: 20260717100000_guest_reputation.sql (schema, triggers, RPCs, search recreate), 20260717110000_guest_reputation_moderation.sql (admin moderation RPCs), 20260718090000_guest_reputation_mean_two_dp.sql (mean displayed at 2dp), and 20260718100000_guest_reputation_review_fixes.sql (review round 1: badge recompute across roster/podcast cascade deletes via a new podcast_guests AFTER DELETE trigger, NULL-safe respond-action guard, purge clears resolved_by, and the single-source reputation_rating_aggregate / reputation_rating_bucket / reputation_assert_pair_eligible helpers). pgTAP: supabase/tests/guest_reputation.test.sql (117 assertions).

Data Model

Table	Purpose
network_ratings	1–5 per (podcast_id, podcast_guest_id, direction). direction is host_rates_guest (live) or guest_rates_host (schema-ready, dormant: no write path, no display, no client access). submitted_by_user_id is nullable ON DELETE SET NULL: a host rating is the podcast's voice and survives staff turnover.
guest_endorsements	≤280-char attributed prose. Status machine pending / approved / hidden / declined / retracted / removed; only approved displays. Partial unique index (podcast_id, podcast_guest_id) WHERE status <> 'retracted' gives one active slot per pair. declined_at is stamped once and survives retraction (notification-suppression memory). author_user_id is ON DELETE CASCADE: attributed prose dies with its author's account.
guest_reputation_state	Materialised badge state per guest (highly_rated, distinct_podcasts, mean_score). Exists because hysteresis needs memory. No client policies.
guest_claim_notices	One-shot claim-email dedup keyed on lowercased email. No client policies.
guest_endorsement_reports	First content-report surface in the platform. No client policies.

Enforcement Model (three layers)

The Hono API runs on the service-role client (requireAuth() injects createSupabaseAdmin), so RLS never gates API queries. Enforcement is honest about that:

1. Route layer (src/api/routes/reputation/index.ts): middleware chain + explicit scoping; identity columns are set from the middleware-verified user, never the body. Author-only edit and target-excluded retract live here.
2. Row-content triggers (fire for every role, including service_role): INSERT-only eligibility (published-appearance join keyed on episode_guests.podcast_guest_id, which works for account-less escrow targets), direction-aware authorship, self-dealing from column values. Triggers never read auth.uid() (NULL under service role).
3. RLS (the direct user-JWT browser path): SELECT-only client policies, no client write policies on either table. Podcast admins can read their own podcast's endorsements and its host_rates_guest rows only; guest_rates_host is never client-readable. This is what keeps the dormant direction unforgeable.

Rating writes use an explicit UPDATE when the pair row exists, never INSERT … ON CONFLICT: Postgres fires BEFORE INSERT row triggers on the proposed row even when the conflict path resolves to an update, which would re-arm the INSERT-only eligibility check.

Identity-Link Transitions (continuous enforcement)

podcast_guests.user_id is email-keyed auto-link, nullable, and orthogonal to membership, so reputation rules re-fire on transitions (charter §6.5):

Consent Chain

Display requires every link of: user_profiles.is_discoverable → profile_privacy_settings.show_endorsements (default true; per-item approval is the consent, the flag is a kill-switch) or show_ratings (default false; being publicly scored is its own opt-in) → status/threshold predicates. All enforced in SQL inside the SECURITY DEFINER read RPCs; search_guest_network's returned reputation columns are consent-masked inside the function body (a returned column on an anon-granted RPC is a display surface). A pg_get_functiondef regression guard asserts the predicates survive redefinition.

Thresholds: aggregate (mean 2dp + coarse bucket 3+/5+/10+/25+) displays at ≥3 distinct rater podcasts. Highly rated badge: grant at mean ≥4.25 across ≥3 podcasts; revoke only below 3.75 or under 3 podcasts (hysteresis, materialised state). Invited back: computed on read, ≥2 published appearances on one podcast.

RPC Surface

RPC	Grant	Notes
get_guest_reputation(p_user_id)	anon, authenticated	Public read: badges, masked aggregate, approved endorsements.
get_my_reputation()	authenticated	auth.uid()-based; RAISES 42501 under NULL uid (service-role misuse fails loudly). Called from /settings/reputation load via locals.supabase.
guest_respond_endorsement(id, action)	authenticated	approve / decline / hide / unhide; P0002 for foreign ids (no existence oracle). Called from form actions.
admin_purge_reputation_subject(user, admin)	service_role	GDPR erasure, both roles (received deleted; authored host-rating provenance NULLed), atomic with admin_action_log.
admin_remove_endorsement / admin_delete_network_rating / admin_resolve_endorsement_report	service_role	Moderation, acting-admin parameter, atomic action-log append (20260717110000).

Surfaces

• Host write path: GuestReputationSection.svelte on /p/[slug]/guests/[guestId] (admin+), calling /api/reputation/* (rate limits api.reputation.rate|endorse|report). The composer opens in a SidePanel, not inline in the card.
• Report path: the profile's Report button opens a SidePanel too, replacing the old dialog. Same endpoint, same rate limit.

GET /reputation/summary carries two timestamps, not one

The card dates an approved endorsement by approved_at and a pending one by created_at. Both are in the payload precisely because they are different events: updated_at moves when the HOST edits the row, so dating "Approved …" from it would show the host's own edit as the guest's act of approval. approvedAt is null until the guest approves, and the card drops the date rather than the sentence when it is missing. Asserted in src/api/routes/reputation/__tests__/index.test.ts (GET /summary, "what the card is allowed to say").

The eligibility flag canRate now removes the rating and endorsement controls rather than rendering them disabled. Locked means there is nothing to press, and the card says what unlocks it.

• Guest consent path: /settings/reputation (+ two flags in the settings Guest Network section). Its +page.server.ts awaits parent() and uses locals.supabase (user JWT) exclusively.
• Public display: guest-network profile + GuestCard badges + search filters (filter_highly_rated, filter_has_endorsements RPC params).
• Moderation: /admin/reputation behind the GATE-6 perimeter, src/api/routes/admin/reputation.ts. Every report also sends an ops email to [email protected] (sendEndorsementReportEmail, metered against the reported endorsement's podcast account) linking to the queue.
• Escrow: endorsing an account-less guest sends one metered claim email (sendEndorsementClaimEmail, deduped by guest_claim_notices); on signup the auto-link triggers attach user_id and re-validation runs before anything counts.

Notifications

network.endorsement_received (suppressed when any prior row for the pair carries declined_at) and network.endorsement_approved, registered in src/lib/notifications/types.ts + policies.ts, published from the endorsement-create/respond paths as non-blocking side effects.

Related

• Guest Network invitation flow
• Public docs: /guests/guest-reputation, /for-guests/your-reputation on the help center