tenant isolation — cross-tenant reads return 0 rows 4 4 17 show cases └ coach A cannot read coach B clients profile12ms coach
└ coach A cannot read coach B clients progress2ms coach
└ client A1 cannot read another clients profile or progress2ms client
└ anon sees nothing (deny-by-default through the public key)1ms anon
positive controls — owners and coaches see what they should 3 3 6 show cases └ coach A sees their own two clients and their three progress rows2ms coach
└ client A1 sees their own profile + their coach, and their own two progress rows2ms coach client
└ client A1 can read their OWN coach but not another coach (own-coach policy)2ms coach client
water_logs (0096) tenancy 2 2 7 show cases └ owner + their coach read the row; other client / other coach are denied3ms coach client
└ anon cannot touch water_logs (owner-private — SELECT not granted to anon)4ms anon
support_tickets (0110) tenancy 4 4 10 show cases └ owner + admin read the ticket; other client, the owner's OWN coach, and anon are denied3ms coach client admin anon
└ the BEFORE-INSERT trigger stamps user_id = auth.uid() (a forged user_id/role is ignored)2ms
└ a client cannot UPDATE a ticket (status/response are service-role-only)1ms client service
└ a service-role resolve mints the owner support_ticket_update notification4ms service
privilege escalation & mass-assignment are rejected 5 5 6 show cases └ client cannot elevate their own role2ms client
└ client cannot reassign their own coach (tenant hop)1ms coach client
└ client cannot insert a progress row owned by someone else1ms client
└ a client can no longer self-insert a profile — creation is server-side (§2)1ms client
└ client cannot insert a profile for another user id1ms client
profile bootstrap — signup creates the profile server-side 2 2 4 show cases └ inserting an auth.users row auto-creates exactly one client profile3ms client
└ reads full_name from signup metadata into the profile (0008)1ms
service_role is the trusted server path 2 2 2 show cases └ service_role reads across all tenants1ms service
└ service_role may reassign a clients coach (the move clients are denied)1ms coach service
schema invariant — every public table has RLS enabled (§2) 1 1 2 show cases └ no public base table ships without row level security2ms
custom access token hook — role is injected server-side from the DB 2 2 1 show cases └ stamps profiles.role into the JWT claims (run as supabase_auth_admin)1ms
└ yields a null role for a user without a profile (→ onboarding)0ms
invitations — coach-scoped, server-side assignment (§2) 5 5 6 show cases └ a coach creates only their own pending invitation1ms coach
└ a coach cannot create an invitation owned by another coach1ms coach
└ a client cannot create an invitation1ms coach client
└ a coach cannot read another coach's invitations2ms coach
└ anon sees no invitations1ms coach anon
assignment functions (0006) — service-side coach_id writes (§2) 9 9 10 show cases └ accept_invitation assigns the coach and consumes the token (single-use)2ms coach service
└ accept_invitation rejects an expired token1ms service
└ accept_invitation rejects a mismatched email (leaked-link defense)1ms service
└ assign_client links an unassigned client to a coach1ms coach client service
└ assign_client refuses to steal an already-assigned client1ms client service
└ assign_client rejects a non-coach actor1ms coach service
└ the assignment functions are not executable by authenticated/anon1ms anon service
└ accept_invitation refuses a client who already has a coach (one-coach lock)1ms coach client service
└ rejects a duplicate PENDING invite to the same (coach, email) — 0008 dedupe1ms coach service
plans v2 (0010) — templates, library, hierarchy, clone (§2) 16 16 32 show cases └ coach A sees their templates + their assigned plans; coach B only theirs1ms coach
└ a client reads only their non-draft assigned plans — never a template1ms client
└ coach B cannot read coach A’s template1ms coach
└ a coach can create a template (no client) but a client cannot2ms coach client
└ client reads children of their published plans only (through the helper chain)4ms client
└ client cannot read another tenant’s plan children1ms client
└ coach writes children only on plans they own; a client cannot write at all2ms coach client
└ coach writes meal items only on plans they own (grandchild helper chain)2ms coach
└ every coach reads global library entries2ms coach
└ a coach reads their own custom entry but not another coach’s2ms coach
└ a coach can create their own custom exercise, but cannot forge a global or another coach’s2ms coach
└ a client cannot create a library entry1ms client
└ assign_plan_to_client deep-copies a template into an independent assigned plan3ms
└ assign_plan_to_client clones a nutrition template’s meals + items2ms
└ assign_plan_to_client rejects a non-client, a non-template, another coach, and a client caller3ms coach client
└ anon sees no plans, library, or plan children3ms anon
coach applications (0011) — client applies, admin reviews, role flip (§2) 10 10 11 show cases └ a client applies for themselves (own pending insert)1ms coach client admin
└ a client cannot apply for another user1ms coach client admin
└ a coach cannot apply (role check)1ms coach client admin
└ rejects a duplicate PENDING application for the same user (dedupe)1ms coach client admin
└ a client reads only their own application; an admin reads all1ms coach client admin
└ review_coach_application is not executable by authenticated/anon1ms coach client admin anon
└ approve flips the applicant to coach, clears coach_id, marks approved1ms coach client admin
└ reject marks the application rejected and leaves the role unchanged2ms coach client admin
└ a non-admin reviewer is rejected1ms coach client admin
└ anon sees no applications1ms coach client admin anon
messages (0012) — coach⇄client DMs, server-set sender, rate limit (§8) 7 7 20 show cases └ a coach messages their own client, and a client their own coach5ms coach client
└ the server forces sender_id = auth.uid() (a forged sender is overwritten)1ms coach client
└ cannot message someone you are not paired with2ms coach client
└ cannot message yourself1ms coach client
└ only the two parties read a message; outsiders see nothing2ms coach client
└ rate-limits a burst of sends from one sender8ms coach client
└ anon sees no messages1ms coach client anon
media (0013) — owner/coach/admin read, writes are service-role-only (§2/§7) 6 6 16 show cases plans v3 (0014/0015) — weeks, system templates, clone, duplicate (§2) 10 10 31 show cases └ every coach can READ a global system template; a client cannot3ms coach client
└ a system template is read-only — a coach cannot add a week to it1ms coach
└ weeks are tenant-scoped: a coach reads/writes only their own plan’s weeks4ms coach
└ the composite FK rejects a day whose week belongs to another plan2ms
└ clone_template copies a GLOBAL template into a new editable coach-owned template10ms coach
└ a client cannot clone a template2ms client
└ duplicate_plan_week copies a week + its days into a new week in the same plan5ms
└ a coach cannot duplicate a week in another coach’s plan1ms coach
└ assign_plan_to_client clones the weeks layer2ms
└ anon sees no weeks1ms anon
completion logging (0016) — sessions, set logs, derived metrics (§2) 11 11 24 show cases └ coach A cannot read coach B’s client sessions; a client cannot read a sibling’s4ms coach client
└ anon sees no sessions and no set logs1ms anon
└ a client logs their own session + set, and reads their own history4ms client
└ coach A reads their two clients’ sessions but never coach B’s client1ms coach client
└ the owner + their coach read a session’s set logs; outsiders see none3ms coach
└ a client cannot insert a session owned by someone else2ms client
└ a client cannot write set logs into another client’s session2ms client
└ the adherence view reports sets done vs planned for the owner, and is tenant-scoped2ms
└ current_streak counts the owner’s consecutive days; a coach sees their client’s, an outsider 02ms coach client
└ coach_leaderboard returns only the caller’s own clients2ms
└ coach_leaderboard rejects a client caller and anon1ms client anon
profiles & goals (0017) — athlete_profile + coach_profile (§2) 6 6 10 show cases └ the owner + their coach read an athlete_profile; outsiders see nothing3ms coach
└ anon sees no athlete or coach profiles2ms coach anon
└ a client upserts their OWN athlete_profile but cannot forge another owner2ms client
└ a coach cannot write an athlete_profile for their client (self-report only)1ms coach client
└ a client reads THEIR coach’s profile but not another coach’s1ms coach client
└ a coach upserts their own coach_profile but cannot forge another coach’s1ms coach
food logging (0019) — diary, targets, daily roll-up, streak (§2) 12 12 22 show cases └ a coach cannot read another coach’s client diary; a client cannot read a sibling’s2ms coach client
└ anon sees no diary entries, targets, or daily roll-ups2ms anon
└ a client logs + reads their own diary; their coach reads it but a stranger cannot2ms coach client
└ an athlete can log a food carrying a serving snapshot (0055 columns)1ms
└ a client adds an off-plan entry (null plan_meal_item_id) owned by themselves1ms client
└ the server forces user_id = auth.uid() (a forged owner is overwritten, not honoured)1ms
└ a coach cannot edit or delete a client’s diary entry (read-only for coaches)1ms coach client
└ the owner + their coach read a target; another coach cannot3ms coach
└ a coach may override their client’s target (set_by forced); another coach may not2ms coach client
└ a client upserts their OWN target but cannot create one for someone else2ms client
└ v_daily_nutrition rolls up the owner’s day and is tenant-scoped2ms
└ nutrition_streak counts the owner’s consecutive logged days; a coach sees it, an outsider 03ms coach
food preferences (0020) — athlete-owned likes/avoids, coach-readable (§2) 6 6 10 show cases └ the global food library is categorised (0020 backfill)1ms coach
└ the owner + their coach read a preference; another coach and a sibling cannot3ms coach
└ a coach’s cohort read returns only their own clients’ preferences1ms coach
└ anon sees no preferences1ms coach anon
└ an athlete sets their own preference; a forged owner is coerced back to the caller2ms coach
└ a coach cannot write preferences (read-only): insert rejected, delete affects nothing2ms coach
workout logging (0021) — exercise PRs + athlete→coach feedback notes (§2) 6 6 12 show cases └ the owner + their coach read a note; another coach and a sibling cannot3ms coach
└ a coach’s cohort read returns only their own clients’ notes1ms coach
└ anon sees no notes0ms coach anon
└ an athlete writes their own note; a forged owner is coerced back to the caller3ms coach
└ a coach cannot author notes (read-only): insert rejected1ms coach
└ v_exercise_prs gives an athlete their own bests; their coach can read them, others cannot4ms coach
exercise tracking modes (0098) — bodyweight snapshot, assisted loads, PR folding 5 5 10 show cases └ rejects a negative load without a bodyweight snapshot, and a total below zero2ms
└ folds the bodyweight snapshot into best_load + e1RM (weighted and assisted)2ms
└ a weighted hold (duration + load) stays out of the lift PRs2ms
└ assign_plan_to_client carries the tracking_mode snapshot3ms
└ the global library is classified and read-only to clients1ms
workout note delete keeps the chat copy (0056) — FK SET NULL is not an edit 1 1 2 show cases └ deleting a note unlinks its mirrored chat message but preserves it (no edit_window, no edited_at)2ms
workout note: hide vs delete-for-everyone (0060) 2 2 5 show cases └ "hide from my log" sets hidden_at but keeps the mirrored chat message linked2ms
└ delete_workout_note_everywhere removes the note AND retracts its mirrored chat message3ms
nutrition system templates (0022) — clonable global starters (§2) 2 2 6 show cases └ a coach sees the seeded nutrition system templates, each with meals + items5ms coach
└ a client cannot see system templates (they are not assigned plans)1ms client
one published plan per type (0024) — publishing supersedes the previous 1 1 2 show cases └ publishing a second nutrition plan archives the previously published one (same type only)2ms
exercise unit prefs (0025) — per-exercise kg/lb, athlete-owned 3 3 6 show cases └ the owner + their coach read a unit pref; another coach and anon cannot3ms coach anon
└ an athlete sets their own pref; a forged owner is coerced back to the caller2ms
└ a coach cannot write a client’s unit pref (read-only): insert rejected1ms coach client
body metrics (0026) — coach-verified, athletes never self-write (§2/§4) 7 7 11 show cases └ the athlete + their coach read metrics; another coach and anon cannot2ms coach anon
└ an athlete CANNOT self-write a metric (anti-cheat): insert rejected1ms coach
└ a coach writes a verified metric for their client; verifier is server-stamped to the caller3ms coach client
└ verification cannot be forged on a non-coach_entered source: verified_* nulled1ms coach
└ a coach cannot write a metric for a client they do not coach: insert rejected1ms coach client
└ coach_body_metrics_board returns only the caller’s own clients2ms coach
└ coach_body_metrics_board rejects a client caller and anon1ms coach client anon
ai usage ledger (0027) — owner-only read, service-role-only write (§9) 2 2 3 show cases └ the owner reads their own AI usage; their coach, another coach, and anon cannot2ms coach anon service
└ a client CANNOT write the ledger (service-role only): insert rejected1ms client service
InBody OCR confirm (0026 + 12b) — coach confirms, athlete cannot self-verify 3 3 3 show cases └ the staged inbody_ocr reading is UNVERIFIED (trigger forces verified_*=null)1ms coach
└ the athlete CANNOT confirm their own OCR row (anti-cheat): RLS hides it from their UPDATE1ms coach
└ the client’s coach confirms it: source→coach_entered server-stamps the verifier1ms coach client
InBody insights + comments (0028) — coach-only analysis, coach→client comments 6 6 14 show cases └ AI insight is COACH-ONLY: the coach reads it; the athlete (owner), another coach, and anon cannot5ms coach client anon
└ a client CANNOT write an insight (service-role only): insert rejected1ms coach client service
└ comments: the reading’s owner + their coach read them; another coach and anon cannot4ms coach client anon
└ the coach comments on their client’s reading; author is server-stamped to the caller2ms coach client
└ the athlete CANNOT comment on their own reading (coach→client only): insert rejected1ms coach client
└ a coach cannot comment on a client they do not coach: insert rejected1ms coach client
coach AI plan_insights (0029) — coach-only nudges, athlete never sees them (§2) 2 2 4 show cases └ the nudge is COACH-ONLY: the client’s coach reads it; the client (owner), another coach, and anon cannot3ms coach client anon
└ a client CANNOT write a nudge (service-role only): insert rejected1ms coach client service
coach analytics (0031, Phase 15) — coach-fenced KPI engine + coach-only summary (§2) 6 6 12 show cases └ the summary is readable by the OWNING coach; another coach, a client, and anon get nothing3ms coach client anon
└ a client CANNOT write a summary (service-role only): insert rejected1ms coach client service
└ coach_adherence_overview returns only the caller’s own clients with their counts2ms coach
└ coach_adherence_overview rejects a client caller and anon2ms coach client anon
└ coach_plan_effectiveness returns only the caller’s published, client-assigned plans2ms coach client
└ coach_plan_effectiveness rejects a client caller and anon2ms coach client anon
notifications (0032, Phase 17) — in-app feed, prefs, event triggers (§2/§8) 11 11 25 show cases └ a recipient reads only their OWN feed; their coach, another tenant, and anon see nothing3ms coach anon
└ a client cannot INSERT or UPDATE notifications (server-side only); they may dismiss their own5ms client
└ mark_all_notifications_read clears the owner’s unread; a stranger cannot mark another’s4ms
└ a client upserts their OWN prefs but cannot create one for another user; anon sees none2ms client anon
└ sound_prefs: owner upserts/reads own row; cross-tenant + anon see none; cannot write for another user3ms anon
└ a new message emits a feed row for the recipient, deep-linked to the sender’s chat1ms
└ coalesces a rapid burst from one sender into a single silent notification (0112)1ms
└ re-emits (re-pushes) a message from the same sender once the 20s window has passed (0112)2ms
└ a per-type opt-out suppresses the emit (smart, not spammy)1ms
└ publishing a plan emits a plan_published feed row for the assigned client1ms client
└ a completed set beating the prior best emits one pr_achieved row (a first log is a baseline, not a PR)2ms
chat safety (0034, Phase 18) — message reports + ban enforcement (§2/§8) 8 8 11 show cases └ the reporter and an admin read a report; the reported user, another tenant, and anon do not2ms admin anon
└ a participant reports a message ADDRESSED TO THEM; reporter + reported are server-set1ms
└ cannot report your OWN message, a message you cannot see, or file a duplicate2ms
└ a client cannot UPDATE or DELETE a report (append-only; status is service-role-only)1ms client service
└ a banned account cannot send messages1ms
└ a client cannot ban anyone (banned_at is immutable from the client)1ms client
└ moderate_message_report (ban) bans the reported user and actions the report1ms
└ moderate_message_report rejects a non-admin reviewer and is not callable by clients2ms admin
chat engagement (0036, Phase 18 Slice 2) — reactions + soft edit (§2/§8) 11 11 14 show cases └ both parties read a reaction on their thread; another tenant and anon do not3ms anon
└ a participant reacts to a message in their thread; user_id is server-set2ms
└ the server forces user_id = auth.uid() (a forged reactor is overwritten)1ms
└ cannot react to a message you cannot see (cross-tenant outsider)1ms
└ a reactor removes their OWN reaction; another party cannot delete it1ms
└ a banned account cannot react1ms
└ the sender edits their OWN recent message; edited_at + original_body are set1ms
└ the recipient cannot edit the sender’s message (RLS update is sender-only)1ms
└ an edit cannot change sender / recipient / created_at1ms
└ a message past the edit window cannot be edited1ms
└ a banned account cannot edit1ms
message replies (0037, Phase 18 Slice 2) — quote a message in your thread (§8) 3 3 3 show cases └ a participant replies quoting a message in their own thread1ms
└ cannot quote a message you cannot see (cross-thread reply is rejected)1ms
└ an edit cannot change reply_to_id (the reply target is immutable)1ms
ban appeals (0038, Phase 18 Slice 3) — a banned user appeals; admin resolves (§2/§8) 6 6 10 show cases └ a banned account files an appeal (user_id + status server-set); a non-banned account cannot4ms admin
└ the appellant and an admin read an appeal; another tenant and anon do not2ms admin anon
└ a client cannot UPDATE or DELETE an appeal (append-only; not granted)1ms client admin
└ resolve_ban_appeal (approve) clears the ban and approves the appeal1ms admin
└ resolve_ban_appeal (reject) keeps the ban and rejects the appeal1ms admin
└ resolve_ban_appeal rejects a non-admin reviewer and is not callable by clients1ms admin
chat acknowledgments (0039, Phase 18 Slice 3) — per-person disclaimer gate (§8) 3 3 4 show cases └ a user records an acknowledgment; user_id is server-set (a forged owner is overwritten)1ms
└ acknowledgments are own-only: the peer and anon cannot read them2ms anon
└ a client cannot UPDATE or DELETE an acknowledgment (append-only; not granted)1ms client
device tokens (0040, Phase 17 Slice 2) — push registry, owner-only, server-forced owner (§2) 9 9 11 show cases └ register_device_token stamps the caller as owner — the client never supplies a user_id1ms client
└ register_device_token rejects an invalid platform / empty token / bad locale2ms
└ anon cannot register a token (execute revoked from anon)0ms anon
└ a client cannot directly INSERT or UPDATE a token (registration is the RPC only)1ms client
└ an owner reads only their own tokens; another tenant, the coach, and anon see nothing2ms coach anon
└ the same owner re-registering a token updates platform/locale1ms
└ re-registering a token owned by another user does NOT reassign it (H-3)2ms
└ an owner can delete their own token; a non-owner delete affects nothing1ms
└ service_role reads every token (the fan-out path)1ms service
app achievements (0073, E1) — minted trophies, owner-only raw read, public RPC (§2) 5 5 8 show cases └ minting is service-role only — clients cannot mint or write the table directly1ms service
└ a user reads only their own raw trophies; another tenant and anon see none2ms anon
└ get_public_app_achievements: public shows non-body trophies; body-derived gated behind the share toggle3ms
└ anon cannot call the public achievements RPC (execute revoked from anon)1ms anon
└ client_goal_progress is INTERNAL — authenticated and anon cannot call it1ms anon
coach transformations (0077, E3) — coach-curated, consent-gated showcase (§2) 4 4 9 show cases transformation submissions (0084) — client→coach, coach-approves (§2) 4 4 16 show cases transformation manager (0087) — multi-card, photo rows, public media fix, scan pick, nudge (§2) 7 7 20 show cases transformation client actions (0090) — request removal + reframe proposal (§2) 5 5 16 show cases voice notes (0043, Phase 18) — audio media readable by chat participants (§2/§7/§8) 4 4 6 show cases public profiles (0044, Phase 19) — opt-in portfolio via field-allowlist RPCs (§2) 13 13 34 show cases └ a public profile does NOT widen the raw coach_profile/athlete_profile RLS5ms
└ any authenticated user reads a PUBLIC coach portfolio (allowlisted columns)3ms coach
└ a PRIVATE coach portfolio returns 0 rows to outsiders, but the owner may preview it2ms coach
└ a PUBLIC athlete profile exposes only the minimal allowlist (no birth_date/height/sex/injuries)3ms
└ a PRIVATE athlete profile returns 0 rows to outsiders2ms
└ list_public_coaches lists the public coach and never a private one4ms coach
└ list_public_coaches filters by specialty (case-insensitive)3ms
└ coach_public_highlights returns aggregate goal stats with NO client ids2ms client
└ coach_public_highlights returns 0 rows for a PRIVATE coach (to an outsider)2ms coach
└ anon cannot execute any public-profile RPC (no execute grant)2ms anon
└ a PUBLIC profile’s avatar media is readable by an unrelated authenticated user2ms
└ a PRIVATE profile’s avatar media is NOT readable by outsiders (but the owner + admin read it)3ms admin
└ a user cannot set avatar_media_id to someone else’s media row1ms
public leaderboards (0045, Phase 20) — opt-in physique/outcomes boards via field-allowlist RPCs (§2) 7 7 10 show cases └ the public board does NOT widen the raw body_metrics / athlete_profile RLS2ms
└ the men’s board returns opted-in public athletes, FFMI-ranked, with the allowlist only2ms
└ the men’s board excludes a private athlete, a not-opted-in athlete, a banned athlete, and women1ms
└ the women’s board returns the opted-in public female athlete and no men1ms
└ the coach board returns opted-in public coaches as AGGREGATE counts (no client ids)2ms coach client
└ the coach board excludes a public-but-not-opted-in coach and a private coach1ms coach
└ anon cannot execute either leaderboard RPC (no execute grant)1ms anon
leaderboard period window + self-rank (0052, Slice G1) 6 6 9 show cases └ the all-time board still includes an athlete whose only verified reading is 200 days old1ms
└ the month and quarter windows drop the 200-day reading but keep the recent ones1ms
└ public_athlete_my_rank gives the caller their exact rank + board size3ms
└ public_athlete_my_rank returns no row when the caller has no in-window reading1ms
└ public_athlete_my_rank returns no row for a verified athlete who has not opted in (0057)2ms
└ anon cannot execute public_athlete_my_rank (no execute grant)1ms anon
leaderboard snapshots (0103) — shared-fn refresh parity + deny-all snapshot (§2) 2 2 6 show cases └ refresh_public_leaderboards populates the snapshot identically to the live ranking fn4ms
└ snapshot tables + refresh are service-role only (authenticated & anon denied)2ms anon service
coach_requests (0053, Slice G2) — request-a-coach funnel (§2) 12 12 14 show cases └ the addressed coach can read the request (with the snapshotted client_name)1ms coach
└ the requesting client can read their own request1ms coach client
└ a DIFFERENT coach cannot read a request addressed to another coach (cross-tenant)1ms coach
└ an unrelated client cannot read the request1ms coach client
└ an admin can read any request1ms coach admin
└ anon cannot read coach requests1ms coach anon
└ the addressed coach cannot flip status to accepted directly (service-role only)1ms coach service
└ the requesting client cannot self-accept their own request (with_check blocks it)1ms coach client
└ the requesting client MAY cancel their own pending request1ms coach client
└ an unassigned client CAN request a PUBLIC coach (trigger server-sets client_id)2ms coach client
└ requesting a PRIVATE coach raises request_invalid2ms coach
└ requesting a PUBLIC ATHLETE (not a coach) raises request_invalid — is_public_coach is stricter than is_public_profile1ms coach
admin console RPCs (0054, Slice G3) — in-function admin fence 5 5 5 show cases └ an admin gets a single counts row with non-negative integers1ms admin
└ a non-admin gets ZERO rows from the counts RPC (the WHERE fence denies)1ms admin
└ admin user search matches by name and returns ONLY the allowlisted columns1ms admin
└ a non-admin gets ZERO rows from user search1ms admin
└ anon cannot execute either admin RPC (no execute grant)1ms admin anon
conversation previews + read-state (0058) — chat list aggregation (§2/§8) 5 5 7 show cases └ list_conversation_previews gives one row per counterpart with the unread count1ms
└ mark_conversation_read clears the unread count for that conversation2ms
└ a user cannot read another user’s conversation_read_state (cross-tenant)2ms
└ mark_conversation_read writes the row as the caller (user_id forced to auth.uid())1ms
└ anon cannot execute the conversation RPCs (0059 — execute revoked from anon)1ms anon
conversation summaries (0102) — O(clients) previews + AFTER DELETE recompute (§2/§8) 2 2 6 show cases └ a user cannot read another user’s conversation_summaries rows (cross-tenant)3ms
└ AFTER DELETE rolls the summary back to the prior message, then removes it when emptied3ms
conversation receipts (0091) — Sent/Delivered/Seen via the peer read-state (§2/§8) 7 7 10 show cases └ a coach reads the client’s marker against them (Seen source, non-null last_read_at)1ms coach client
└ returns 0 rows when the peer has no marker yet (paired but nothing read)1ms
└ cross-tenant: another coach/sibling client cannot read A1’s markers1ms coach client
└ mark_conversation_delivered stamps only the caller’s own row (not read)1ms
└ anon cannot execute the receipt RPCs (execute revoked from anon)1ms anon
└ mark_conversation_delivered_for stamps the RECIPIENT marker, service-role only (0113)2ms service
└ peer-read (0094 realtime): the sender reads the marker made about them, not cross-tenant3ms
presence (0092) — online/last-seen behind a pairing fence (§2) 6 6 10 show cases └ a coach reads their client’s presence (seeded online)3ms coach client
└ a client reads their coach’s presence (seeded 10m ago → offline)2ms coach client
└ cross-tenant: unpaired peers return 0 rows2ms
└ the raw table is pair-readable (0094 realtime) but denies cross-tenant1ms
└ touch_last_seen writes only the caller’s own row1ms
└ anon cannot execute the presence RPCs1ms anon
security hardening (0061–0067) 4 4 8 show cases └ M-5 — accept_invitation refuses a client who already has a coach (atomic no-steal)2ms coach client
└ M-2 — first-contact DM needs a chat acknowledgment (server-enforced disclaimer)4ms
└ H-1 — moderation FKs are ON DELETE SET NULL (audit trail survives user deletion)1ms
└ C-1 — a blocklisted email cannot register (case-insensitive handle_new_user guard)1ms
@handle (0069) 4 4 5 show cases └ rejects a duplicate handle (case-insensitive unique)2ms
└ enforces the 14-day rename cooldown1ms
└ rejects a reserved handle1ms
└ check_handle_available is not callable by anon (execute revoked)1ms anon
calls & meetings (0083) — slots + booking asymmetry + lifecycle (§2) 12 12 14 show cases └ client A1 sees only their coach's OPEN slots (held + other-coach hidden)1ms coach client
└ client B1 cannot read coach A slots or client A1 calls1ms coach client
└ coach B cannot read coach A slots or calls1ms coach
└ anon sees no slots or calls1ms anon
└ coach A sees their own two slots + the completed call1ms coach
└ client A1 sees their own call0ms client
└ client forging coach_adhoc is rejected (origin forced → no slot → invalid)1ms client
└ client cannot book another coach's slot2ms coach client
└ client cannot book a held slot (double-booking guard)1ms client
└ coach cannot mutate a call via a direct RLS update (must use the RPC)1ms coach
└ coach can publish their OWN slot but not one for another coach1ms coach
└ client can create a booking and cancel it, but cannot self-accept3ms client
push queue (0104) — durable enqueue + batch claim + reaper (§2/§9) 3 3 5 show cases └ a notification insert enqueues a push_queue row (trigger)1ms
└ claim_push_batch claims pending rows; the reaper reclaims a stuck one2ms
└ push_queue / push_tickets / claim_push_batch are service-role only2ms service
admin_actions (0108) — append-only audit ledger, admin-read only (§7.4) 4 4 4 show cases └ the seeded ban record is readable by an admin only1ms admin
└ a coach, a client, and anon see 0 rows (no cross-tenant / no self-read)1ms coach client admin anon
└ a client cannot insert into the ledger (service-role-write only)1ms client admin service
└ is append-only — UPDATE and DELETE are blocked even for service_role1ms admin service