Data & Hooks Conventions

Status: Active Last reviewed: 2026-10-11

Single source of truth for how TwoMore fetches, mutates, and displays data. Covers formatting utilities, mutation and query hook discipline, the port/adapter/mapper layer, telemetry and performance instrumentation, mock data seeding, API key rotation, tier display, cross-query cache writes, PII/consent/data-collection obligations, the profile-photo (face) rule, and SQL migration footguns (SECDEF discipline, CREATE OR REPLACE traps, idempotency-guard silent skips, secret hygiene, and the no-PII-in-JSONB rule). Rationale and history live in CLAUDE.md; the machine-enforced subset is in AGENTS.md (DATA-1..DATA-27, KR-5).

Formatting & display utilities

• Formatting a date → packages/app/src/presentation/utils/date-format.ts. Never inline getMonth()/getDay() in components. ONE-CLOCK (owner ruling, improvement plan 2026-09-23, W2 F7) — 24-hour HH:MM is the one clock, everywhere. Every time-of-day render reads formatHm (@/config/date-utils, for a Date) or formatTime (the module above, for an "HH:MM[:SS]" string) — deadlines (formatDeadline, whose past form is M/D HH:MM 마감됨, e.g. 4/13 09:00 마감됨), the late-arrival chip, and the wizard's deadline receipt all read through them. The retired 12-hour 오전/오후 house clock (formatTimeOfDayKo12) and its i18n keys are DELETED, not merely unused — don't reintroduce a 12-hour form. Enforced by @twomore/no-inline-date-format, which also catches a hand-rolled getHours()-style value fed to padStart (a sweep with this wider check found three more ad-hoc local-date builders, all moved onto the domain's one local-date formatter, toLocalDateStr, packages/app/src/domain/rules/local-date.ts). Evidence: decision log (ROUND 4 — one clock, empty profiles, rows).
• Formatting an ELO rating → formatElo(elo) from @twomore/app. Never inline elo.toLocaleString('ko-KR').
• Formatting a session/match format label → formatSessionFormat(format) from @twomore/app (reads the long-form t().sessions.format* namespace). Never re-declare a local formatLabel. Space-constrained short-form badges (getFormatLabel in packages/features/sessions/src/match-board-helpers.ts, used by match-board-screen.tsx's fact row and add-round-sheet.tsx's feasibility tile — SessionCardV2 now reads the same long-form formatSessionFormat for its 🎾 fact, not this map) keep their own 혼복-style map — different i18n namespace (t().sessionHeader, retained from the retired SessionHeader component) by design.
• Displaying a session title → sessionTitle(session) from @twomore/app (generated from format + competitive/friendly style, e.g. "혼합복식 경쟁"). Session titles are always metadata-derived, never custom — never read session.title for display (the create/edit flows no longer collect a custom title; the DB column persists only a generated fallback). Use it at every surface: detail header + identity card, SessionCardV2, scorecard headers, share cards, recap/history.
• Grouping list items into "YYYY년 M월" sections → groupByMonth(items, getDate) from @twomore/app (returns { title, data }[] for SectionList). Never re-implement the month-bucketing loop.
• Filtering RSVPs by status → filterConfirmed / filterWaitlisted / confirmedUserIds / isConfirmed from @twomore/app (domain/rules/rsvp-selectors). Never inline rsvps.filter(r => r.status === 'confirmed'). Bespoke partitions (waitlist sorted by position, confirmed-excluding-self, sessionId extraction) stay inline.
• A person's name for display is never blank → profileDisplayName(profile) from @twomore/app is total: a name of only whitespace is no name (신규 멤버), a profile the read did not return (undefined) reads 신규 멤버 too, and 탈퇴한 멤버 is said only of a profile whose anonymizedAt the data sets. Every team line, roster, person row and score sheet takes a name through it; a query that has not answered yet is not a missing profile, so the block waits (draws nothing) rather than name a stand-in. Never byId.get(id)?.displayName ?? '' or a second fallback.
• Building an id→name lookup from profiles → buildNameMap(profiles) (id→displayName) / buildProfileStubMap(profiles) (id→{displayName, avatarUrl}) / getProfileName(map, id) from @twomore/app (presentation/utils/profile-map). Full-Profile maps stay inline new Map(profiles.map(p => [p.id, p])).
• Validating a name → validateName(). Never inline if/else chains
• Reading auth state in presentation → useAuth(). Never supabase.auth.* outside auth adapter
• Reading theme → useThemeColors() / useTheme(). Never useColorScheme() outside theme-provider
• UI strings → t() from packages/app/src/config/i18n/. Code + comments → English
• Option value vs label → in a { value, label } option (chips / selects / PillNav / SegmentedTabs / filters), value is a stable language-invariant key (used for comparison, DB storage, persistence, deep-links, React keys); label is the display string and the only field that may hold Korean (ideally via t()). NEVER put a Korean/localized string in value — it's a value/label confusion that breaks the moment the value meets a stored key or another locale. Canonical example: region uses the english SIDO key ('seoul') as value, Korean ('서울') only as label (REGIONS / PICKUP_REGION_OPTIONS from config/regions.ts, SIDO_OPTIONS for key→label display). Enforced by @twomore/no-localized-option-value. The region filter shipped broken (Korean value compared against the english-key session.region) until this was fixed (2026-05-22).

Club regular meets & match history

• Club regular meets are RRULE-modeled (iCalendar RFC5545 subset). The club_regular_meets table stores rrule TEXT (FREQ/INTERVAL/BYDAY/BYSETPOS + dtstart phase anchor); date-math uses the rrule npm package (nextOccurrence, regularMeetDays from domain/entities/regular-meet.entity.ts) — pure-JS, Hermes-safe, OTA-deployable. clubs.primary_day + clubs.primary_time_slot are DENORMALIZED CACHE synced by the private.sync_club_primary_schedule trigger (AFTER INSERT/UPDATE/DELETE on club_regular_meets) — never write them directly; they exist only for cheap card-rendering queries. The source of truth is always club_regular_meets. club_schedule_slots is RETIRED (dormant since the 2026-05 roadmap; data migrated; do not reference). Korean display lives in presentation/utils/recurrence-format.ts (describeRecurrence / describeRegularMeet — produces "격주 토요일 09:00~11:00") — deliberately i18n-free domain strings (not t() calls), because recurrence descriptions are domain-derived from structured data, not lookup keys. When extending the recurrence editor, stay within the supported RRULE subset (isSupportedRrule); arbitrary RRULE strings from outside callers are not guaranteed to render correctly in Korean. Architecture note: rrule was previously rejected for DST complexity — Korea's no-DST single-timezone neutralizes that entirely; iCalendar strings stored in the DB remain portable should TZ support ever be needed.

• Match history is sourced from PARTICIPATION, not current club membership. useMatchHistory reads rsvps.findPlayedSessions(userId) (every session the user confirmed / no-showed for — INCLUDING guest sessions in clubs they aren't a member of, AND pickups) → sessions.findByIds(...) → matches. NEVER source a player's match history from their current-member clubs (the old sessions.findPast shape) — that silently DROPS guest + pickup matches and retroactively appears/disappears matches as the user joins/leaves clubs. The "played" gate is the match-level filter (status==='completed' && both scores non-null && user-in-match), NOT a session-status pre-filter — so a scored match in a session later cancelled mid-play still appears. Note use-player-stats-detail + use-activity-data still source from findPast (member-club stats are the intended scope there) — only the HISTORY list is participation-sourced.

• Every match-history categorization dimension is DURABLY SNAPSHOTTED at write-time, never derived from mutable CURRENT state (the snapshot-at-write principle — a fact computed from current club_members / current friendships / current ELO bands silently drifts as the user/relationships/bands change). The durable sources per dimension: membership (member/guest/pickup) ← rsvps.joined_as_member (00233 BEFORE INSERT trigger). nature (friendly/competitive/tournament/league) ← matches.nature, snapshotted from the session at the →completed transition (00234 snapshot_match_completion_context trigger — fires on completed because submit_match_score sets completed directly, skipping in_progress); deriveNature(match, session) reads match.nature ?? <session fallback> (fallback only for pre-migration rows). friends-vs-strangers ← matches.friend_pairs JSONB (the accepted-friendship [lo,hi] edges among the players at completion, viewer-agnostic); the records-history friends scope reads entry.hadFriendInMatch ?? <current-friends fallback> — NEVER intersect against the live friends list alone (friendships are hard-deleted, so the live list is unreconstructable history). tier-at-time ← elo_history.tier_before (the player's 5-tier label from the row's own rating_before, set by 00235 snapshot_elo_tier_before BEFORE INSERT trigger); an opponent's tier-at-time is THEIR own elo_history row, so no per-match tier columns. ELO→tier bands have ONE server-side source: private.tier_index_from_elo / private.tier_from_elo (00235) — rsvps_enforce_tier consumes it; never re-inline the 900/1400/2000/2500 thresholds in SQL again (the 3rd inline copy was the 00151/00229 7→5-tier drift). When adding a new dimension: snapshot it at a GUARANTEED transition trigger, forward-only (NULL on old rows = honest "unknown" — never backfill a mutable-derived value, which manufactures a wrong durable fact), and thread it Match-entity → mapper → MatchHistoryEntry.

• Player figures: another person's count 경쟁 only, one's own count every match (batch 66, 2026-11-15). get_player_match_history (00776) reads a match as rated when private.match_rating_is_applied says so (an elo_history row of reason match, the guard the rating step itself uses); a friendly session and a skipped outcome (walkover, suspended) are completed yet not rated. For any viewer but the subject, or with p_rated_only, the record, the rows and truncated are that one rated population; the subject's own read keeps record over every finished match and adds ratedRecord. A surface that prints another person's 총 경기 or 승률 reads it rated only and says 경쟁 (common.competitiveMatches, common.competitiveWinRate); a person's own tiles say 총 경기 / 승률 over the full record, and their 기간 기록 separates 경쟁 and 친선 (record-detail/kind-rows.ts): 경쟁 means "counted for the rating" (ratedRecord), and the 친선 row holds everything else (record minus ratedRecord). A ranking row's W-L-D is rated only for every row (00778). The viewer's own head-to-head, heatmap and share card stay on every match. A new reader of another person's figures goes through this function, never a second count.

Mutations & query hooks

• Creating a mutation → createMutationHook factory. Never raw useMutation (except optimistic updates, which call refuseWriteWhileOffline()). The factory accepts an optional mutationKey: QueryKey config — pass it as [<entity>, <action>] (e.g. ['rsvp', 'upsert']) so TanStack can deduplicate concurrent observers and so the AGENTS.md DATA-2 rule stays satisfied. Required only for mutations whose state needs to be observed cross-component; optional for fire-and-forget admin actions.
• createMutationHook's offline option states the write's offline class (owner decision 16): 'block' (the default) refuses at mutate() while offline with the typed OfflineWriteBlockedError (code OFFLINE_WRITE_BLOCKED, presentation/utils/offline-write-blocked.ts), unreported and with no Toast; 'queue' keeps TanStack's pause-and-replay and is limited to the reviewed read-state marks (useMarkThreadRead, useMarkPostRead, useMarkPostsRead). The durable queue is useQueueableMutation, never this factory. A raw useMutation (optimistic hooks) calls the one shared refuseWriteWhileOffline() in onMutate, before any optimistic patch (a background write such as useRecordClubGrowthEvent is dropped offline: replaying an impression later would count it at the wrong time). Every mutation hook is in exactly one of the three classes (AGENTS.md DATA-28; offline-class.gate.test.ts holds it with a known-positive canary per rule).
• The persist allow-list law (EP-2, 2026-11-12). A query root reaches the device's disk (MMKV, 24 h) only if PERSIST_TABLE in query-persist-policy.ts has a row for it with persist: true, a reason (the viewer's own data, or what every member already sees) and the offline screens that need it; a root without a row, a refused scope of a persisted root (allows) and a malformed key are never written, and an entry an older build wrote under a refused root is never restored and is deleted by the first sweep. query-persist-table.test.ts calls every key factory in query-keys/** and fails when a root has no row (a new query forces the decision) or a row has no root, and lists per offline screen the keys it reads, which must all persist. The hidden class (staff data, a bank account, an ownership claim or a certification, a bearer grant, another person's money, a typed search term) is persist: false and its page withholds it offline (QueryBoundary offline="hidden", or an online gate). Scopes of a persisted root that stay in memory: club-join-requests/club (only mine persists), dues (only member and plans), sessionPayments (only user), attendance (only summary), guestApplications (only the viewer's own application), clubs browse lists and signed media grants, profiles/search, dm/signed-url, and an official's read of a member's trust record (['trustRecord', 'user', userId, clubId] with a club: it holds the member's 사유 인정 category and note, health information, 00754; the member's own record has no club in its key and stays cached, as does the requester's own request state, attendanceExcuse). Both account reads, clubBankAccounts (the club's) and sessionDepositAccount (a 매치's own; read only for the host or a confirmed seat, through useDepositAccount), are persist: false: an account number is never in a signal payload, a push, a log, telemetry, an error message or a persisted draft. What the disk cache is today: beginSessionBootstrap (session-lifecycle.ts → clearQueryCacheMmkvStrict, since 2026-09-07) deletes every persisted query row at each start, so one account's cache can never show to another: the cache is written during a session and does not survive a cold start. Offline display after a restart is not provided; offline display while the app stays in memory is. An account-stamped, encrypted cache that restores for the same account (a key in the secure store, a secure random source: expo-crypto, a native module) is built after the next native build. AGENTS.md STORE-3.
• createMutationHook(...) is MODULE-SCOPE ONLY — never invoke it inside a component/hook body. Always export const useFoo = createMutationHook({...}) at module scope. The factory's returned hook calls useQueryClient + useMutation; calling createMutationHook({...})() during render builds a fresh hook function every render, and the React Compiler (enabled) memoizes/reorders that inline hook-returning call so the inner hooks are skipped between renders → an intermittent, lint-invisible "Should have a queue. You are likely calling Hooks conditionally" crash (the compiler healthcheck + rules-of-hooks both pass — eslint doesn't recognize the factory's return value as a hook). If you need a queryClient-dependent side effect (e.g. invalidating a sibling cache after a board-post write), define the factory at module scope and pass the closure via the per-call onSuccess override: const useFooBase = createMutationHook({...}); export function useFoo() { const qc = useQueryClient(); return useFooBase({ onSuccess: (r, i) => { /* uses qc */ } }); }. Lesson from 2026-05-25 — useUpdateSession + useCreateSession both did the inline-factory pattern and crashed EditSessionSheet/create-session intermittently for days; localized only via recent_client_errors.component_stack_head (the dev_read_telemetry stream drops component_stack).
• A hook that travels as a value (through context, a prop or a registry) is bound to a name that starts with use before it is called; never useContext(X)(). The React Compiler knows a hook by its name, so an unnamed call is cached as an ordinary function and skipped on later renders, which throws "Should have a queue. You are likely calling Hooks conditionally" (the second W5 build, useBottomChromeReport). Write const useProvidedReporter = useContext(X); useProvidedReporter();. packages/ui/src/__tests__/bottom-chrome-report-compiled.test.ts compiles the real file and renders it twice. The same holds for a hook inside a literal or an argument: { email: useRef(null) } is cached as one value and the hook is skipped (the sign-in form, same build), so every hook call is a statement of its own. yarn check:compiled-hooks compiles every shipped file as the phone does and fails on either form.
• Mutations whose target row is already cached → optimistic via applyOptimisticPatches. Wait-for-server feels broken even on fast networks.
• Pure CPU derivations of upstream query data → useMemo, not useQuery. useQuery is for I/O — its cache key churns when wrapping pure compute.
• Don't fire the same useQuery key from two component levels — extra observer subscription cost per render with no caching benefit. Lift state up.
• Fetching all open/locked sessions across user's clubs → useClubUpcomingSessions. RSVP-filtered variant → useRsvpdUpcomingSessions. Never use the old useUpcomingSessions name.
• A form that edits a server value holds it in useServerDraft(server, toDraft) (F11, 2026-09-29). It hydrates on arrival, re-hydrates only while pristine, never lets a refetch replace a dirty field, and reset() adopts what the server holds now; markSaved() makes the saved edit the new baseline. Never key a draft on the query result's identity or hand-roll a "hydrated" flag.
• Several writes of one kind → one batch mutation hook, never a repository call in a loop (F16, 2026-09-29). useCreateRegularMeets is the model: the loop lives in the mutationFn, per-item failures reach captureCriticalError, and the entity's caches are invalidated once. "Hooks can't run in loops" is not a reason to bypass the mutation layer.

Ports, adapters & mappers

• Presentation hook touching Supabase → go through the registered port via import { <port> } from '@/registry'. Never import { supabase } from '@/adapters/supabase/client' or getSupabase() directly inside presentation/hooks/**. The hex direction is presentation → registry → port → adapter; bypassing breaks the swap-adapter-for-tests contract and hides the SQL from architecture review. If the existing port doesn't expose the read/write you need, add a method to the port + implement in the adapter in the same commit (canonical example: attendanceRecords.findStrikesByMember added in commit d800eb8).
• Auth session storage (adapters/supabase/secure-storage.ts) is expo-secure-store behind an in-memory write-through cache — supabase-js reloads the session from auth.storage on EVERY request (GoTrueClient.__loadSession), so without the cache each PostgREST/Storage/Functions call paid an Android Keystore decrypt (10–50 ms, serialized natively) before its first byte. Never bypass the cache with a direct SecureStore read of the session key; never give supabase-js another uncached async storage.
• Handling adapter error → handleError(). Never silent catch. Fire-and-forget → logger.warn()
• A .in(column, ids) read with a caller-collected id list goes through selectInBatches(ids, (batch) => …) (base.adapter.ts, 2026-09-29). supabase-js sends .in() as a GET query string, so a few hundred UUIDs beside a wide projection pass the gateway's URL limit and fail as a bare "Failed to fetch". Batches of 100, deduped, merged; re-apply any global order/limit after the merge. RPC arguments ride in a POST body and never need it. @twomore/no-unbatched-in-filter (no per-file exemption); a genuinely bounded list (an enum, the viewer's own clubs) takes a justified inline disable.
• Env vars are read as literal process.env.NAME (2026-09-29). Next and Expo's Babel preset inline nothing else: process.env passed whole, destructured, spread or indexed with a variable is empty in the browser and in a release app. Build an object from literal reads (apps/web/lib/sentry-env.ts). @twomore/no-dynamic-process-env; Node-run tool configs and tests are out of scope.
• Coercing a drift-tolerant enum in a mapper (Protocol K) → coerceEnum(schema, value, fallback) from adapters/supabase/mappers/_assert. Never inline schema.safeParse(value).data ?? fallback for enum fields. (Required enums use assertEnum + safeMap to drop the row; JSONB object schemas keep the inline Schema.safeParse(value).data ?? DEFAULT — "enum" would mislead there.)
• A read selects every column its mapper reads, and a mapper never defaults a NOT NULL column (2026-11-14). The gate adapters/supabase/__tests__/select-list-parity.test.ts runs each registered mapper on a recording row and fails for any key its select list leaves out (a new _COLUMNS list not in __test-utils__/select-list-registry.ts fails it too); a key a list omits on purpose must be optional in the mapper's row type (column-revoked or a lighter projection), and a NOT NULL column is read with requireColumn(row.x, 'x') (mappers/_assert.ts), never ?? default, so a read that dropped it is a typed failure (safeMap drops and reports the row) instead of a default shown as the person's data (참가비 내역's won sign, a held seat drawn lapsed). An RPC bundle's explicit column list must carry the same keys: supabase/tests/session_bundle_row_keys.test.sql pins the session bundles.

Telemetry, perf & debugging

• client_perf_events writes are BATCHED (perf-event.supabase.ts, 2026-09-08): reports buffer in memory and flush as one multi-row insert every 5 s / 25 rows / on app background. A perf report is never its own request — every insert used to pay a session read + serialization and queue behind the 6-slot request gate at exactly the moment the slow screen's own queries were waiting (the diagnostic competing with the thing it diagnosed). Keep any new telemetry sink on the same buffer shape.
• Stamping platform / app_version / app_dist on a client_errors / client_perf_events insert → ...getAdapterTelemetryContext() from adapters/supabase/_telemetry-context. Not for lib/sentry (its OTA updateId + release/dist source-map semantics differ).
• Catching an unrecoverable error → captureCriticalError(err, { context }) from @/lib/sentry. Never Sentry.captureException directly. Errors land in client_errors Supabase table + Sentry. See docs/best-practices/debugging-workflow.md
• Telemetry carries no personal data (EP-4, 2026-11-12). One scrubber, redactErrorText / redactErrorForReport in lib/redact-pii.ts, runs once in recordCriticalErrorSharedLayers, so the on-device crash log, client_errors and Sentry (native and web) all receive the same scrubbed error: tokens, email addresses, phone numbers, bank-account-shaped numbers, URL query strings and the row values a database error quotes (Key (email)=(…)) are replaced by markers; an error's code and hint survive. redactEventText does the same for events the Sentry SDK captures by itself, and scrubNetworkBreadcrumb (lib/breadcrumb-scrub.ts) drops the query string of every fetch/XHR breadcrumb, keeping the method, the path and the status. The key list for objects (PII_KEYS, also in the no-pii-keys-in-jsonb-writes rule, parity-tested) covers credentials, transfer accounts, notes and message bodies, addresses and avatar URLs. handleError never copies the database's details text into an error message (it can quote a row value): it rides on DomainError.serverDetails, which nothing renders or reports (isAlreadyAcceptedError reads it). A mapper that drops a row (safeMap, mapPart) reports once per context per session with the mapper's name and the zod issue paths only (mapper-drop-report.ts); a failed secure-storage write is reported once (logger.report, a warning channel the Sentry module registers). The redaction never throws and never blocks the report. The writers keep inside the server's bounds ("Anonymous writes are bounded", in the SQL section below).
• Tracing user actions → addBreadcrumb(category, message, data) from @twomore/app at the start of every mutationFn. Stable categories: rsvp.toggle, score.submit, payment.mark-paid, payment.mark-waived, attendance.confirm, manner-tags.submit. When the next error fires, Sentry shows the 10 most-recent breadcrumbs as context. New mutation needs a new category — add it to the docstring in lib/sentry.ts so the enum stays the single source of truth.
• Debugging a crash OR lag → run yarn debug:logs (signs in as dev, reads the unified dev_read_telemetry stream of errors + slow ops) BEFORE asking the user for screenshots. --since=2h, --kind=perf|error|query|paint|navigation, --route=X, --clear. Falls back to select * from public.recent_client_errors(20) in the Supabase SQL editor.
• Session-start prod-health check — the watchdog only writes to telemetry; NOBODY is paged. At the start of any session that touches prod (deploys, migrations, cron/edge work, or just resuming after days away), run yarn debug:logs --since=24h --kind=error FIRST and treat any CRON HTTP FAILURE watchdog FATAL as a stop-the-line incident before the planned work. An unread alert IS an ongoing outage: on 2026-06-10→11 the push+weather pipeline was down 19 HOURS with the watchdog faithfully writing hourly FATALs that no one read (cause: app_config.service_role_key held a revoked API key → every cron net.http_post 401'd "Unregistered API key" at the gateway). The 00202 watchdog closed the DETECTION gap (the 2026-05-17 outage ran 33h undetected); this rule closes the READING gap.
• Capturing lag → perf.reportSlow(operation, durationMs, { kind }) (threshold-gated + throttled, fire-and-forget to client_perf_events). perf.measure() auto-funnels; query-client auto-times fetches. NEVER put PII in the perf context (counts/ids/query-keys only — redactPII runs defensively). UX lag is captured at the @twomore/ui primitive level via a parallel DI sink (perf-sink.ts setPerfSink/reportUiPerf, bridged to perf.reportSlow in initPerfReporter since ui can't import lib/perf): PillNav auto-times tab switches (interaction:tab), FeedList/GroupedFeedList capture scroll jank when given a perfLabel prop (scroll:<label>), and <PerfProfiler id> (React Profiler) captures render storms (render:<id>) on high-churn subtrees. Coverage is now app-wide — every FeedList/GroupedFeedList site carries a perfLabel, and the two raw (non-virtualized) ScrollView screens (profile, home-feed) wire useScrollPerf('<label>') + scrollEventThrottle={16} + {...handlers} explicitly. A NEW scrollable surface MUST follow suit: FeedList/GroupedFeedList → add a kebab-case perfLabel; a raw ScrollView → spread useScrollPerf('<label>'). High-traffic / list-heavy screens additionally wrap their scroll subtree in <PerfProfiler id="<screen-key>"> so render storms self-attribute via render:<id> events alongside the scroll:<label> jank events — use the same kebab key for both (e.g. perfLabel="clubs-mine" + <PerfProfiler id="clubs-mine">) so the two event streams correlate under one screen identifier in yarn debug:logs. Wrap the inner content YStack / FeedList — never the ScrollView/DetailShell chrome (the Profiler must enclose the rendered subtree, not the scroll machinery). Pair the wrap with the Wave-G playbook: React.memo on rows + useCallback on renderItem/keyExtractor/separators with stable-reference deps + useMemo on object/array props passed to memoized children + module-level constants for inline styles. Without that, the Profiler will faithfully report storms that are caused by missing memoization.
• A rule that exists in BOTH TypeScript and Postgres is a bug waiting to happen — decide which kind it is. TypeScript cannot import a SQL constant and Postgres cannot import a module, so anything stated in both languages is two hand-maintained copies by construction. Three shipped bugs in 2026-09 came from exactly this and none was caught by a type, a test, or a review: the cancellation policy scored members by a window the app never showed them (00639), the push category labels drifted from the app's own vocabulary in production, and the client's pre-match rating preview computed numbers the server would not apply (H2). The rule now:
  1. A policy NUMBER lives in data. A table the database reads and the client receives through an aggregate it already fetches, with a compiled-in fallback. private.rate_limit_policies (00641) is the template — one source, no gate needed, and ops can tune it without a deploy.
  2. A policy ALGORITHM is computed in ONE place and read from the other. Prefer the server: expose a STABLE read-only function built on the SAME helpers the authoritative write path uses, and deliver its result inside an existing bundle so it costs no extra request. 00642_match_rating_preview.sql is the template — it extracted private.match_rating_compute_deltas so apply_match_ratings and the preview share one implementation, and its pgTAP previews a score, really applies it, and asserts the two are equal. That collapse deleted 311 lines of duplicated TypeScript.
  3. A parity gate is the fallback, never the plan. Gates scale linearly with the number of rules and each depends on someone remembering to add one. Where a value genuinely must be written twice (a cold-start seed, a display default), a gate is right — but it must fail when a declaration cannot be FOUND, not only when values differ, or a rename passes it vacuously. Live gates: check:cancel-policy, check:attendance-policy, check:elo-parity, check:cron-budget.
  4. Never keep a constant "in case" behind a gate. When the ELO algorithm left TypeScript, four constants lost every consumer; they were deleted rather than guarded. A gate protecting values nothing reads is a parking lot with a lock on it.
• Wall-clock duration measurements that can span an app background/suspend MUST cap implausible values. Any Date.now() - start (or perf.mark→measure) duration whose start and end can straddle the OS suspending the app — a setInterval drift detector, an in-flight fetch timer, anything timing across an await that may be backgrounded — measures the ENTIRE suspend (minutes) as one event on resume, emitting bogus multi-minute "blocks"/"slow queries" that swamp real signal AND (for the monitor) fire an error-level Sentry breadcrumb on every resume. Cap/skip durations beyond the operation's realistic upper bound. Canonical guards (both 2026-06-03 — the SAME bug found in two paths): packages/app/src/lib/js-thread-monitor.ts MAX_PLAUSIBLE_BLOCK_MS (5s — reset lastTickAt + return when actualDelta exceeds it) and packages/app/src/presentation/providers/query-client.ts MAX_PLAUSIBLE_QUERY_MS (30s — queries are AbortSignal-timeout-bounded, so anything larger is a suspend artifact → skip the reportSlow). Any NEW wall-clock perf timer must add the same guard; not mechanically lintable — reviewer discipline. A plausibility CAP alone is not enough for a repeating monitor (2026-09-09): a SHORT suspend — notification shade, a glance at another app, screen off/on inside the cap — lands under the bound and is reported as a real multi-second block, which then outranks every genuine block in yarn debug:logs (the unattributed 4187 ms venues/[id] block of 2026-09-08 was exactly this: no GC delta, no interaction near it, sitting between two ordinary home-feed scrolls). js-thread-monitor.ts therefore ALSO gates on AppState: a latch set on any transition away from active discards the next measured window even when the app is back to active before the tick runs (a background round trip completing BETWEEN two ticks is invisible to AppState.currentState). Prefer discarding a real block over reporting a phantom one — phantom events cost more than they reveal.
• Marking "screen fully displayed" → render <Sentry.TimeToFullDisplay record /> (import Sentry from @twomore/app) inside the data-ready content branch of a primary screen — AFTER its Protocol-G readiness gate clears (the QueryBoundary content child, the else of a not-found guard, or below an if (isLoading) return …). It measures the true full-display instant for Sentry Mobile Vitals (TTID is auto via the nav integration; TTFD requires this manual marker). It renders an invisible draw marker and is a no-op when no navigation transaction is active, so a tab-switch re-render won't emit a spurious span. NEVER place it in a skeleton/loading branch (would record skeleton paint as "full display") or unconditionally inside a multi-pane renderTab that mounts >1 pane (gate to the default landing pane, e.g. home's slice === 'today'). Wired on home/profile/records/club-detail/spectator-scorecard; add to a new high-traffic data screen the same way.

Mock data & key rotation

• Mock data comes from the scenario builder, NOT static seeds (2026-06-13). supabase/seed.sql is DELETED. config.toml seeds ONLY seed_public_courts.sql (real reference data — court names, addresses, coordinates; never touched by tests or scenarios). ALL user/club/session/meet/DM/payment/review/availability/challenge/feedback/stats mock data is generated RANDOMLY and IDEMPOTENTLY by the seed-scenario edge function. Run yarn seed (base chain) or yarn seed:dense (dense chain) after npx supabase db reset. Adding a new table: (1) add a randomized generator inside supabase/functions/_shared/scenario-builder.ts; (2) add the table to the seed-scenario truncate list so re-seeding is idempotent; (3) wire the new data into the relevant chain. NEVER add a static row to seed.sql — the file is gone; the scenario builder is the single source of truth.
• Seed media is real photos and logos from one catalog (2026-09-25). Every seeded gallery photo, post photo, banner and logo comes from supabase/functions/seed-scenario/lib/media-catalog.ts: photos taken at real tennis courts and clubs (Unsplash and Wikimedia Commons licences, credited per photo) and original club emblems. node scripts/seed-media.mjs --local|--linked builds them to the app's own upload spec (IMAGE_RESIZE_PRESETS, IMAGE_RESIZE_DEFAULT_QUALITY) into the private seed-fixtures bucket, and lib/seed-media.ts copies them server-side into each club's own paths, the same buckets and path shapes the app's upload flows write. A fixture names its photo by catalog key next to the caption that describes it; a club's logo is the emblem of the place it is named after (derived from the generators' own REGIONS). A fixture's content never changes under its key (a redesigned emblem takes the next SEED_LOGO_SERIES): the refresh recognises seeded media as a byte copy of a fixture by ETag. Seeded public URLs carry ?v=<fixture> and are written on the caller's public URL (run-scenario.mjs sends it; the local functions runtime only knows its internal gateway). Never draw placeholder images or point a logo at a random face photo (i.pravatar.cc): both hid how real content looks on the surfaces under test. seed_media_refresh re-dresses an existing world without truncating. Tested by scripts/__tests__/seed-media.test.mjs. Seeds count rows with a GET (count: 'exact' + .limit(1)), never head: true: on the local stack's gateway a write sent right after a HEAD reuses a pooled connection the API has just closed and fails with 502 "An invalid response was received from the upstream server" (three full_qa failures, 2026-09-25); idempotent media writes also retry that transient error (writeWithRetry).
• Seeding perf: batch inserts, reconcile once per chain (2026-07-04). Multi-row generators (matches, match_sets) must accumulate rows and issue ONE .insert(rows).select('id'), mapping returned ids back to their child rows — a per-row insert in a loop is the classic silent-slowdown trap (measured: live_showcase 25.6s → 13.9s after batching matches). dev_reconcile_seeded_world (full-DB rating replay) is expensive and idempotent-safe to defer — a multi-step chain should pass skipReconcile: true on every step except the last so it runs once over the full dataset, not once per step (mirrors the existing skipTruncate flag). Deliberately serial paths (e.g. dense_compute's scoring, which avoids a real ELO race) are the documented exception. When a generator looks "dead" (zero rows silently seeded), verify its query columns and any CHECK-constrained vocab against the actual migration — three real bugs shipped this way: generateSessionPayments queried a column that only exists on a different table (cost_per_person vs the real participation_fee) and wrote a Korean string into a CHECK-constrained enum column (payment_method); guest_applications inserts omitted a NOT NULL FK (club_id) with no backfill. None of the three raised an error — they either silently seeded zero rows or failed every insert. Grep the actual migration for the column/constraint before trusting a generator's output.
• Rotating/regenerating Supabase API keys → app_config.service_role_key must be updated in the SAME operation. The pg_cron jobs build their Authorization: Bearer from public.app_config (key service_role_key), independent of the edge functions' TWOMORE_SECRET_KEY secret — rotating the platform key updates neither automatically, and a revoked key in app_config 401s ALL cron HTTP calls (push, weather, receipts) while cron.job_run_details still shows "succeeded" (dispatch-level). Verify by digest, never by value: select key, length(value), encode(extensions.digest(value,'sha256'),'hex') from public.app_config; and compare against npx supabase secrets list digests. The sanctioned repair shape when the valid value exists only as an edge secret: a ONE-SHOT token-guarded edge function that copies its own env value into app_config server-side and returns only {updatedRows, valueSha256, valueLen} — the secret never transits chat/git/agent context; delete the function (prod + disk) immediately after the digest verifies. Used 2026-06-11 to end the 19h cron outage.
• EXT-9 force-update gate — the minimum runtime is one app_config edit, no deploy. public.get_app_version_policy(p_platform) (migration 00669, SECURITY DEFINER, anon+authenticated EXECUTE — the check runs before login) reads app_min_runtime_<platform> / app_store_url_<platform> (platform = android/ios) straight off public.app_config and returns {minRuntime, storeUrl}. The client compares its own Updates.runtimeVersion (expo-updates) against minRuntime (isRuntimeSupported, packages/app/src/domain/rules/app-version-gate.rules.ts) and swaps in UpdateRequiredScreen when it falls short (useAppVersionGate → AppVersionGate, mounted around <AuthGate /> in apps/mobile/app/_layout.tsx). Fails open everywhere: web, a dev build (runtimeVersion null), offline, a query error, and a missing/blank config value all resolve to 'ok' — never a hardcoded threshold in code. To raise the minimum: UPDATE public.app_config SET value = '<new-version>' WHERE key = 'app_min_runtime_android' (and/or _ios) directly — no migration, no deploy; clients pick it up on their next policy-query refetch (6h staleTime).

Tier display

• Displaying tier info → EloTierBadge (packages/app/src/presentation/components/elo-tier-badge.tsx) / TrustTierBadge (packages/app/src/presentation/components/trust-tier-badge.tsx), both built on the shared TierBadgeChrome (packages/app/src/presentation/components/tier-badge-utils.tsx) — a single flat $primarySubtle pill (no per-tier color/variant any more) plus each badge's own emoji map (ELO_TIER_ICONS, private to elo-tier-badge.tsx; TRUST_TIER_ICONS, exported from domain/entities/trust.entity.ts). tier-display.ts / TIER_ICONS / getTierColor / TIER_BADGE_VARIANT no longer exist — don't cite them.
• Tier labels in UI → tierLabel(tier) from packages/app/src/utils/tier-label.ts. Never inline t().ranking.tier[x].
• Formatting a club tier range → formatTierRange(min, max) from @twomore/app — "실버~골드", a single label when both resolve equal, null when either is absent. Never inline min && max ? `${tierLabel(min)}~${tierLabel(max)}` : null. The wizard's "all levels" shortcut stays bespoke; the dash-separated SessionHeader variant this line used to except is gone (SessionHeader was deleted — no replacement dash-format exists).
• Tier ordering (promotion/demotion comparisons) → TIER_ORDER from @twomore/app (TIER_ORDER.indexOf(tier)), derived from TIER_DEFINITIONS. Never hand-declare ['bronze','silver',...].
• Fetching session weather → useSessionWeather({ region, date }) from packages/app/src/presentation/hooks/queries/use-session-weather.ts. Never fetch weather directly from a component. SessionCardV2 does NOT call this internally (zero-data-hooks projection, per Conventions › Session Surfaces) — it receives a pre-resolved weatherLabel prop from the list's bulk model builder (useSessionCardModels); session-detail-screen.tsx is the current direct caller.

Ratings: replay, score entrant, level mark

• A rating replay never announces. private.recompute_all_ratings (every correction, review resolution, exclusion and reopening) applies the completed matches in order with p_announce = false (00741, on apply_match_ratings and match_rating_write_player_result): the elo_changed and tier notices went out when the match was scored, and re-emitting them would tell a player twice. The live score entry keeps the default true. The replay also leaves the level mark alone. p_announce = false is the only guard: a repeated dedup key resurfaces a resolved notice as unread, so a replay that announced would re-open every old one. 00744_windowed_rating_replay.test.sql asserts the count of trust_tier_changed notices is unchanged across a windowed replay.
• A replay starts each player from their recorded start (00743). player_ratings.seed_source (self_assessed | derived) names where a seed came from. A self-assessed row is reset to exactly what seed_self_assessed_rating wrote (rating = peak = seed, rd 350, 0 matches), never deleted; everything a match or the engine created is wiped and rebuilt, including the mixed-doubles blend seed (derived, re-derived at the player's first mixed match). Display state (confidence, state, source, tier, tier sub, tier shield) is derived and reset by the replay. The two writers of seed_rating must set seed_source; a CHECK refuses a source with no seed; a seed with no source (a foreign writer) is kept as a given start; a new writer of a starting rating that is user input uses self_assessed. After the replay every completed scored match is applied silently, so the result equals the live path and a second replay changes nothing. Known limits, recorded not fixed: a self-assessed mixed seed that had already played before 00743 cannot be told from a derived one and is re-derived (the backfill's stated ambiguity); a player who self-assesses one discipline after already playing another gets a different mixed blend on replay than live (the seed has no timestamp). One replay under its lock is timed in release evidence (S9); the windowed replay (F3c) is designed, not built.
• A correction replays a window, not the world (00744, F3c). private.recompute_ratings_from(p_match_id, p_previous_at) re-applies only the matches at or after the corrected match's replay position (COALESCE(completed_at, created_at), created_at, id; a moved, excluded or reopened match widens the window to the earlier of its old and new position), under the same exclusive rating lock, silent, leaving level_celebrations and bonus event times alone. Its result equals recompute_all_ratings() (the oracle, unchanged, and the repair path; dev_reconcile_seeded_world keeps it) on ratings, history, profiles, club counts, the three ledgers and the match flags (ledger last_played_at and updated_at stamps excepted); a pgTAP parity file holds this. Callers: replace_match_score, apply_match_exclusion, apply_match_reopening, agree_to_correction (closed to signed-in users since 00769; service role only) (and apply_review_resolution, correct_own_score through them). The before-state of each history row lives in private.elo_history_before_state (RLS on, no grants; elo_history is client-readable), written by match_rating_write_player_result: a new writer of elo_history match rows must go through it. The window falls back to the full replay, checked before anything changes, for seven named reasons returned with mode (window or full): history_without_before_state, history_of_uncounted_match, late_confidence_bonus, history_chain_broken, matches_played_gap, mixed_seed_origin_unknown, ledger_underflow. Before-state fills lazily (00744 runs no replay): history older than 00744 has none, so the first correction after it pays one full replay, which writes it; later corrections use the window. The history has a total order (00769): the one order-dependent read, the streak query in match_rating_player_delta, sorts by the replay key (history created_at, the match's created_at, the match id), so equal completion times never decide a rating and never force a full replay. Known limit: a divergence before the window that reaches a window player only through other players' state, with every window player's chain unbroken, is not detected; the next full replay normalises it. get_home_club ties go to the lowest club id.
• A report names its subject, and an official never decides one about themselves (00745, F4). content_reports and member_reports carry subject_kind (post | comment | session | message) and subject_id, both or neither; the ids point at content, no text is copied (P4: a deleted subject has no excerpt). report_content takes them (seven arguments, no defaults); a chat report's subject is its message. An official reads subjects through get_report_subjects(report_ids) (at most 200 ids, hint report_ids_limit), a staff read that is persist-denied and not drawn offline. The club queue policy reports_admin_read excludes a report whose reported_id is the viewer, and the decision refuses it (report_self); such a report waits for the platform queue.
• matches.score_entered_by is the person who settled the match (00741). The first settling write records it; NULL for every older row, seed and service write, so nobody self-corrects those. It decides the 10-minute door 점수 고치기 and nothing else; it reaches a client as a plain matches column (read with the row, and in the session detail bundle's match rows). A host who entered a score for others is the entrant.
• The level mark (00742). A level-up is the NTRP numeral the app prints going up (ntrpLevelNumeral(getSubTier(rating)), doubles basis; a mixed_doubles match moves the mixed rating only and never the doubles Level). public.level_celebrations(user_id, celebrated_level, updated_at) holds one high-water mark per person: own-row SELECT, no client write; acknowledge_level_up(p_level) is the only writer and is monotone (it keeps the higher of the two, refuses a level outside 2.0 to 5.5 in halves). Pending is derived, never stored (pendingLevelUp in domain/rules/level-up.rules.ts: the newest doubles elo_history row whose numeral rose above the mark and is still held by the newest match's rating), so a correction that removes a climb removes the celebration. The mark is the baseline: a backfill gave every profile a row at its current numeral, a new profile gets one at the numeral of the 1000 default, and seed_self_assessed_rating sets it to the seed's numeral when it inserts the doubles row (a 2.5 seed reaching 3.0 is pending; a 4.0 seed marks 4.0), so no baseline row means nothing celebrates. The seed is clamped to 600 to 1600, so a seeded start is at most 3.5: a strong new player's first celebration is 4.0, reached by play.
• One SQL mirror of the numeral. private.ntrp_numeral_from_rating(int) (IMMUTABLE, no client grant) mirrors the client's table for the backfill, the new-profile trigger and the seed; domain/rules/__tests__/ntrp-numeral-parity.test.ts reads the latest migration defining it and fails if a boundary differs. Any other SQL that needs a numeral calls it; never a second CASE.

Queueable write ordering

Original calls and durable replay use the same account/entity lane in presentation/cache/mutation-entity-sequence.ts. A mutation reserves its turn before asynchronous optimistic work, waits for durable same-entity predecessors and their persistence flush, and releases its physical lane only when the repository promise settles. Queue/account generations revoke authority but do not prove that an earlier server transaction rolled back. Add a natural entity mapping when introducing a queue key whose writes can conflict.

createQueueableCall(reader, replay, requestId, { entity, lifetime }) takes one options object: entity reserves the ordering lane turn, lifetime is an owner abort signal. A lifetime abort stops the call like an account change and releases the reserved turn (waitForTurn rejects on the combined signal; dispose() finishes the turn).

Same-account offline interruption runs rollback, error and reconciliation callbacks, but the interruption is not an error report: the global MutationCache.onError leaves an offline interruption breadcrumb and never calls captureCriticalError (query-client.ts). Stale-account/generation callbacks remain suppressed. Preserve the original request ID and never blindly replay an uncertain online commit. Client sequencing fixes demonstrated ordering failures, but global guarantees across an aborted server transaction require server-side intent versions or a ledger. Overlapping optimistic exact/prefix patches snapshot each canonical query only once, so rollback restores the state before the mutation.

Caching & cross-query writes

• Persistent Supabase Realtime channels are retired. Live surfaces use focus-gated polling, DM uses native refetchInterval, signals use push/cache-bust, and ordinary surfaces rely on staleTime + mutation invalidation. If a future realtime hook is introduced, it must live under presentation/hooks/realtime/**, use useFocusedEffect, and patch/invalidate only specific keys.

• Cross-query entity writes → use writeX / writeXBackfill from @/presentation/cache. Manual setQueryData scattered across hooks is forbidden.

• One logical fact lives in ONE consolidation helper, not N hand-synced pipelines. When an entity's rows are cached under multiple keys (e.g. RSVP lives in bySession + byUser + allByUser + bySessionIds), a mutation/realtime handler MUST NOT hand-enumerate that key set — it WILL forget one and desync (the 2026-05-27 bug: cancel updated the detail screen's byUser but left the summary cards' allByUser/bySessionIds stale). Instead route through the entity's single consolidation surface in packages/app/src/presentation/cache/index.ts: an optimistic/realtime writeX(client, entity) that fans the row across every cache shape (upsert by id-or-natural-key), and an xInvalidationKeys({…}): QueryKey[] builder that returns EVERY shape to reconcile (spread into createMutationHook's invalidateKeys or a raw onSettled loop). Today: writeRsvp + rsvpInvalidationKeys, sessionPaymentInvalidationKeys, attendanceInvalidationKeys, plus the existing writeSession/writeProfile/writeClub. Adding a key to an entity → update its helper, and every call site is correct for free. Audit query: an entity with 3+ overlapping keys whose mutations enumerate keys inline is the desync smell.

• A DB trigger that derives entity X's fields from entity Y's writes needs Y's own mutation hooks to invalidate X's key too — the trigger existing server-side doesn't make the client cache aware of it (PF-04, perf-data-integrity-audit-2026-09-17.md: migration 00650's trg_sync_club_regions recomputes a club's adm1_slug/adm2_slug/club_play_regions as a side effect of every court_venues write, so useCreateVenue/useUpdateVenue/useDeleteVenue also invalidate clubDetailBundleKeys.detail(clubId) + clubKeys.detail(clubId), not just their own venueKeys). This is the cross-entity sibling of the "one logical fact, one consolidation helper" rule above — that rule catches a hand-enumerated key SET for one entity; this one catches a hand-enumerated key set that spans two.

• Future realtime row events → applyRealtimeListChange / applyRealtimePaginatedChange from @/presentation/cache. invalidateQueries fallback only on mapping failure.

• Future realtime invalidation must target the SPECIFIC keys an event affects, not the *.all root prefix.

• A query-key factory omits an absent optional argument; it never fills it (?? 'all'). A key made with fewer arguments is then the PREFIX of the key made with more, so invalidateQueries on the short form refreshes every variant; a filler makes the short form a sibling that matches nothing (the friends list stayed empty after an accept). A filler is legal only where the factory builds one query's own key and is never used to invalidate; query-key-filler-tails.test.ts lists every filler and fails on a new one.

• Per-event hot-path work must be O(1), never O(total state). Any subscriber to a high-frequency source — queryCache.subscribe, realtime callbacks, scroll/onScroll, store subscriptions — runs on the JS thread, often inside a React commit. Work there must be constant-cost: keep aggregate state in memory and mutate it incrementally; NEVER re-read + re-serialize the whole collection per event (JSON.parse/JSON.stringify of a growing structure), and flush persistence on a debounce (ideally off the frame-critical path via requestIdleCallback). Canonical bug (2026-05-22): the lazy-persister re-readManifest() + writeManifest() (parse+stringify the WHOLE manifest) on every query success; with gcTime=30min the manifest grew all session, so JS-thread blocks grew 79→551ms as the user navigated. Fixed by holding the manifest key-set in memory + a debounced flush that only fires on a genuinely-new key. When you add a cache/realtime subscriber, ask: "does this do work proportional to total cached state per event?" — if yes, it's a progressive-slowdown bug.

• A hot-path zustand action must bail BEFORE set() when the write is a no-op. set({...}) with unchanged VALUES still creates a new state object and notifies every subscriber — with useSyncExternalStore that is a forced re-render (forceStoreRerender in the stack is this signature). Any render-adjacent caller (a draft-sync wrapper, an onLayout reporter, an effect) that writes unchanged values then echoes: write → notify → re-render → write → maximum-update-depth (on-device 2026-08-24, wizard updateFormData). Guard shape: compare incoming keys with Object.is against current state and return (or return state from the functional form — zustand bails on identical references) before setting. Applies to updateFormData in both wizard stores and setBottomChromeHeight; apply it to every new store action a render path can reach.

PII, consent & data collection

• New data-collection feature → in the SAME migration, add a public.purpose_catalog row for (purpose_key, jurisdiction) with legal_basis + retention_days + min_age + is_sensitive. Update the privacy policy entrustment table in packages/app/src/config/legal/documents/privacy.ko.ts if a new vendor is added — any text change there bumps the document's version and needs a new policy_versions migration row carrying the hash yarn check:policy-hashes --print prints (is_material=true when material per PIPA §30 → forced re-consent); the gate fails the build until the row exists. Then update getRequiredConsentsForJurisdiction(j) in consent-matrix.ts to include the new purpose in the relevant jurisdictions. The lint:pi-schema script enforces the migration-side discipline; the policy-text update is reviewer-checked. Never collect personal data without a corresponding active grant in user_consent.
• Reading personal data → before SELECTing a PI column (birth_year, phone_e164, region, gender, location coordinates), verify the user has an active grant for the relevant purpose. Helper getActiveConsent(userId, purposeKey) will live in @twomore/app when multiple call sites need it; until then, document the consent gate inline in the adapter.
• Consent revocation surface → every consent MUST be revocable from SettingsConsentManagementScreen WITHOUT losing service access. Required consents (terms, privacy, cross_border_transfer) are revocable only via account deletion — that is intentional per PIPA §37; the withdrawal mechanism for required consents is the delete-account flow.
• Cross-border vendors → if you add a new US-hosted service (Sentry replacement, payment processor, analytics SaaS), update three places: (1) entrustment table in documents/privacy.ko.ts, (2) its cross-border transfer section with the vendor's PI manager contact, (3) the new policy_versions row the hash gate demands (material). The Jan-2025 KakaoPay/Apple KRW 8.3B fine establishes §28-8 disclosure as the top PIPC enforcement target.
• No PII in JSONB columns → signals.payload / signals.context / notification_events.data / client_errors.context / user_consent.evidence / matches.match_rules are the project's 6 JSONB writeable surfaces. Each is genuinely heterogeneous so JSONB is the right shape (signals have 13 categories with different fields; client_errors carry arbitrary call-site context; ISO/IEC TS 27560:2023 explicitly specifies user_consent.evidence as free-form). But: never write PII values into these columns. PII = name / displayName / display_name / phone* / email / kakao*Id / lat/lng/latitude/longitude / birth_year / birthYear / commenterName / commenter_name. Write the user's userId instead, and resolve the display name at read time via profiles.findByIds(...). Migration 00192 codifies this with: (a) trigger functions rewritten to write actorUserId only, (b) backfill cleanup UPDATE stripping PII keys from existing rows, (c) CHECK constraint signals_payload_no_pii_keys blocking the named PII keys at DB level. The PII key set matches packages/app/src/lib/redact-pii.ts's SENSITIVE_KEYS so client + DB stay in sync. New JSONB writers (RPCs / triggers / adapters) must follow the same pattern; if a denormalized name is needed for UX, do read-side enrichment.

Faces (profile photos)

Owner ruling 2026-10-11: non-members see no faces. Inventory and decisions: faces-members-only-inventory-2026-10-11.md.

• The rule is one predicate: public.can_view_face(p_viewer, p_target) (migration 00691). A face reaches V only if V is T, V and T share an active club (club_members.is_active), V and T are accepted friends, or T opted in to public and V is signed in. An anonymous viewer never gets one. profile_visibility->>'avatar' picks the audience; an ABSENT key means club_members = a shared club or a friend, friends = friends only, private = nobody else, public = any signed-in viewer (an explicit opt-in, never a default). The four conditions are necessary; an explicit narrowing is honored. A block in either direction (private.is_blocked) closes every level, public included (00768, PRIV-4): the first clause after the self check, so a public profile does not leak past it; the block is silent (the value is NULL, as for a stranger, and neither person is told).
• Every SQL projection of avatar_url calls it and nothing re-derives the rule. Today: private.mask_profile_row (behind get_public_profile_view(s), search_public_profiles, get_rating_leaderboard, get_profile_view and the club, session and home bundles), get_club_discovery_summaries, get_public_share_preview and private.venue_owner_restricted. get_own_profile() is the only self read. A new function that returns a face writes CASE WHEN public.can_view_face(<viewer>, <target>) THEN avatar_url END. Enforced by supabase/tests/00691_faces_members_only.test.sql, a catalog scan (with canaries) that fails when a function mentions avatar_url without the predicate, or emits a whole profiles row as JSON.
• The predicate is not client-executable. A caller-chosen viewer would be a friendship/clubmate oracle (the reason can_view_profile_field is revoked too); callers are SECDEF and pass auth.uid(). service_role keeps EXECUTE.
• The column is closed: SELECT (avatar_url) on profiles is revoked from anon and authenticated (same shape as 00539 / 00680: table-level revoke, then an explicit column allow-list). A client never selects it or embeds it; packages/app/src/adapters/supabase/__tests__/profile-face-reads.test.ts fails the build on .from('profiles').select('…avatar_url…' | '*') or a profiles(…avatar_url…) embed. Writes (update, insert … do nothing) need no SELECT.
• Storage: the avatars SELECT policy lists only the caller's own objects (<uid>-…, and the legacy avatars/<uid>-…). Rendering uses public URLs, which never consult RLS: a URL that was shown works until the object is deleted, so a new surface must never expose a URL outside this predicate and the client deletes the replaced object.
• The client treats NULL as "show the initials". Profile.avatarUrl: null means none uploaded OR outside the audience; the card seam shows real photos only, so a non-member's discovery card has no stack. The settings preview (settings-privacy-screen.tsx) restates the audience rule for display only; the server decides.
• The default in the app: 설정 → 프라이버시 shows the photo row at "클럽 멤버와 친구" until the user picks otherwise.

Profile activity fields (migration 00767) and the server-health reader

• The audience rule is one SQL function: private.profile_audience_allows(viewer, target, key). public.can_view_face is that rule with key = 'avatar'; the profile's primary club, attendance rate, session count and last session use key = 'activity'. A new gated field reuses it with its own key; never a second spelling of the rule. Absent key = club_members (self, a shared active club or an accepted friend); friends = friends only; private = self only; public = any signed-in viewer; an anonymous viewer never. A block in either direction answers false at every level (00768).
• Enforcement: private.mask_profile_row (behind every profile projection) returns those four fields as null outside the audience, get_public_share_preview returns a session count of 0, and SELECT (primary_club_id, global_attendance_rate, total_sessions_confirmed, last_session_at) on profiles is revoked from authenticated (select_hidden in invariant_profile_column_grants). The owner's own copy comes through get_own_profile. patch_profile_visibility accepts the key activity; the privacy settings page has its row (주 클럽과 출석 현황), default 같은 클럽 (reaches friends too).
• The client rule: a hidden value draws nothing: no number, no label, no "아직 클럽이 없어요" for another person (the page cannot tell "no club" from "hidden from you"), no chip. Only the viewer's own page says "no club".
• The block clause lives in both spellings of the audience (00768). private.profile_audience_allows (face, activity) and public.can_view_profile_field (region, district, play regions, gender, birth year) each answer false for a blocked pair right after the self check; private.mask_profile_row probes the pair once per row and skips the gated reads, so a list pays one probe per person. Still visible to a blocked pair, by decision (the fields no audience gates): display name, ratings and tier, nationality, 구력, kata division, elite background; and the surfaces that never ask an audience rule: club posts and comments, the club roster, session participant lists, group chat threads and existing direct-thread history. A new gated field reuses a rule; a new surface that shows a person decides about the block on purpose.
• The leaderboard keeps a blocked pair; search does not (00772). private.rating_pool has no block clause, so a blocked pair read the same rows and ranks as a stranger (a block is silent; the face and gated fields stay masked by the rule above). search_public_profiles still hides a blocked pair from each other (00713): it is the door to a friend request and a chat, and closing that door is what a block is for.
• Set scores and reactions follow their match (00772). match_sets and reactions are readable only through a match the caller can see (EXISTS (SELECT 1 FROM matches ...) under the caller's own RLS), never to every signed-in person; a new table that hangs off a match reads the same way.
• A signed-out caller reads nothing from sessions (00772). anon holds no column grant on the table. A signed-out page reads through the SECURITY DEFINER previews (get_public_share_preview, get_club_invite_preview), and the signed-out request wrapper (adapters/supabase/request-access.ts) refuses every other read; a column granted to anon later needs a signed-out page that reads it, named in the migration.
• Club media lists to the club's officials (00772). The club-media bucket is public (a public URL still works); listing and signed URLs need an owner or admin of the folder's club, the test the write policies use. An unknown invite code is a refusal (P0001, hint join_invalid_invite_code), not an HTTP 500 (P0002); the other P0002 sites are a pre-launch item (docs/release-evidence.md).
• Required consents come from the catalog (batch 63). 동의 관리 puts a purpose under 필수 when the consent matrix (getRequiredConsentsForJurisdiction, the source sign-up reads) marks it required, never by legal_basis; a required row shows no 철회 and says it ends only with 회원 탈퇴. A required consent is withdrawn only by leaving the service: revoke_consent raises P0001, hint consent_required, for a purpose the catalog marks required (00774), and the page words that refusal as the 필수 line on its row; delete_account_atomic writes the ledger itself and never calls it.
• Trust is unaffected: the trust record reads none of these profiles columns.
• public.dev_read_server_health(p_swallowed_limit) (developer account AND the allow_dev_rpcs switch; refused for anyone else; SECDEF, search_path = '', PUBLIC and anon revoked): the swallowed-error sink (source, SQLSTATE, schema-object names only, no user id or value) and the watchdog's own verdict per cron job. Read by yarn qa:health (local by default; --hosted alone prints what it would call; --hosted --run makes the read-only call) and by the dev panel's Server tab. It never takes a service key.

Invite codes (members-only)

Migration 00711 (2026-11-08). A club's invite code is the join gate of an invite_only club: join_club_by_invite_code and its target-bound sibling admit a code holder without approval. Before 00711 any signed-in user could read the code of every discoverable club, raw or through the club page bundle.

• One read, for members: public.get_club_invite_code(club) answers an active member of that club (public.is_club_member); anyone else, and a club that does not exist, gets 42501 with HINT club_invite_code_members_only. The client reads it through ClubRepositoryPort.getInviteCode (useClubInviteCode, clubInviteCodeQuery); its cache root clubInviteCode stays in memory (query-persist-policy). useShareClubInvite reads the code when the member shares and says so when it cannot.
• No club row carries it: SELECT (invite_code) on clubs is revoked from anon and authenticated (the faces shape), CLUB_COLUMNS and the club, home and session bundles name their columns without it, and Club has no inviteCode. A select('*') on clubs fails for every signed-in user. Enforced by supabase/tests/club_invite_code_members_only.test.sql and packages/app/src/adapters/supabase/__tests__/club-invite-code-reads.test.ts.
• A new clubs column needs its own grant (§ SQL footguns below). The joins and lookup_club_by_invite_code are SECDEF and keep matching on the column; the lookup returns the row only to someone who typed the code.
• A signed-out visitor sees which club a link is (00759, batch 52). public.get_club_invite_preview(p_code) is granted to anon and authenticated on purpose (named, with this reason, in the anon allow-list of 00026_secdef_acl_hygiene.test.sql). It returns exactly six keys, the ones a discoverable club already shows publicly: clubName, region, district, levelBand, joinMethod, activeMemberCount. Never an id, logo, description, member, the code itself or dues; an invite-only club shows no more than a discoverable one. SQL NULL for an unknown code, an archived club, a non-club row and a code outside 4 to 10 trimmed characters (the last is answered before the cap is charged). It is charged against a global cap of 2,000 calls per Korea day (private.public_ingress_policies row club-invite-preview, both ingress CHECK lists widened) and fails closed: a spent cap raises P0001 with hint invite_preview_capped and reveals nothing, so the page falls back to exactly the page without a preview. At 32-bit codes a prober at the cap finds any club with probability 2,000 x clubs / 2^32 per day (0.047% at 1,000 clubs). One abuser can spend the cap and turn the preview off for the day (accepted); per-IP fairness is a later step. The app asks it signed out only, never while auth loads, once per settled code of 4 to 10 characters (400 ms debounce, never on a partial keystroke), and a failed read, the cap, offline and an unknown code all draw no preview and no error line.
• Guessing is throttled for signed-in accounts (00759). lookup_club_by_invite_code allows 15 calls per user per hour (hint rate_limited_invite_lookup); join_club_by_invite_code and join_club_by_invite_code_for_club share 5 completed joins per user per hour (hint rate_limited_invite_join); both are private.rate_limit_policies rows, separate counters. An honest mistyper needs 2 to 4 lookups and one join, far below either; probing is capped at 360 lookups a day per account. Signatures and return shapes are unchanged (installed builds). A raising call rolls back its own charge, so the join counter bounds completed joins only; that is why a join by code succeeds only for a club the caller found through the lookup in the last 30 days: the lookup writes a receipt (private.invite_lookup_receipts, one row per user and club, cascading from auth.users and clubs, no client grant, PI-EXEMPT; the caller's receipts older than 30 days are deleted in the same call). Without a receipt, both joins raise the one wrong-code refusal (private.raise_invite_code_not_found: P0002, 'Club not found', no detail, hint join_invalid_invite_code), byte for byte what an unknown code or a non-club row raises, before any approval, capacity or archive refusal, so a guess through the join gives no signal. The app already looks up first (the join page, and the visitor home's join runs the same cached lookup). A seed or script that joins by code must look the code up first or plant a receipt in a fixture. Receipts of a withdrawn person stay until a hard delete (the 30-day anonymization does not remove them; recorded for the engineering pass).
• A code never reaches a log. Both code-keyed queries (preview, lookup) sit under the memory-only cache root clubInviteCode and carry SECRET_QUERY_KEY_META: the query cache's error hook logs and reports only the key's root, never the key. Any new query whose key holds a secret (a code, a token) carries the same meta.

Public share preview (anonymous read)

public.get_public_share_preview(kind, id) (anon and authenticated, one function, one set of arguments) never returns stored free text or a position (00758, batch 52): a session or match preview carries title / sessionTitle as the derived title only (private.derive_session_title; installed builds reject a preview without one, so the keys stay), venueName is the linked court venue's name or null, and the stored title, the typed place (location) and the coordinates are not in the payload. A session with a typed place and no linked venue shows its derived title. Held by supabase/tests/share_preview_derived_title.test.sql (a canary session titled SECRET-TITLE at SECRET-PLACE yields neither string anywhere in the payload, for the session and its match, as anon and as a signed-in caller). The app's parsers (public-share-preview.ts) read both the new shape and an old-shaped payload and keep none of the dropped keys.

SQL: SECDEF & migration footguns

• RETURNS TABLE in plpgsql SECDEF functions → every column reference inside CTE bodies (especially GROUP BY + SELECT lists) MUST be qualified with table aliases when the return column name matches an underlying column name. PostgreSQL raises 42702 "column reference is ambiguous" at function-call time, NOT at CREATE OR REPLACE FUNCTION time — so the error only surfaces in production. Codified in migration 00183 — signals_pick_pushable is the canonical fix shape (all internal CTE refs qualified as s.recipient_user_id etc.). The ESLint plugin cannot catch this; it requires reviewer discipline on SQL migrations.
• SECDEF function discipline → every SECURITY DEFINER function MUST (a) set search_path = '' to prevent search-path injection attacks, (b) REVOKE EXECUTE FROM PUBLIC and explicitly GRANT only to the principals that need it (service_role, authenticated, etc.), (c) gate on auth.uid() IS NOT NULL at the top of the body if the function reads any user-keyed data. Default PUBLIC EXECUTE on a SECDEF function that reads sensitive config (like 00101's get_app_config which returned the service-role key) is a catastrophic data-exfil vector — authenticated users can call it via PostgREST rpc() regardless of RLS. Fixed in 00184. The lookup_club_by_invite_code pattern (migration 00146) is the canonical REVOKE + GRANT shape. As of 00209, public no longer auto-grants EXECUTE to PUBLIC for newly-created functions (ALTER DEFAULT PRIVILEGES … REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC), so a new function migration that omits an explicit GRANT EXECUTE … TO authenticated[, service_role] is UN-callable by the client — and silently so (the failure surfaces at runtime, not apply time). Always add the explicit GRANT in the same migration. Cron/edge-only functions GRANT service_role only (cron runs as the postgres superuser, bypassing grants; edge functions use service_role) — never authenticated, never anon. Anonymous EXECUTE is denied except for the intentional public projections and RLS predicates explicitly covered by 00026_secdef_acl_hygiene.test.sql. The ownership addition permits only named anon on get_venue_owner_certifications(uuid[]): one to one hundred requested venue IDs return booleans and a check time, without claimant identity or proof. This exact-signature exception does not permit PUBLIC execution, other overloads, raw ownership tables or private helpers. Run the Supabase Security Advisor periodically — the four categories it flags here (rls_policy_always_true, function_search_path_mutable, anon_security_definer_function_executable, public_bucket_allows_listing) were all closed in 00207–00210 (2026-05-27).
• A SECDEF function that checks nothing about its caller is not callable by authenticated (00760, batch 52). Three were: recent_client_errors (every user's client error rows), increment_naver_api_budget (spends the shared map and search budget) and signals_resolve_by_dedup_key (resolves anyone's notice). EXECUTE is revoked from PUBLIC, anon and authenticated; service_role and the owner keep it. Every SQL caller runs as the owner (the resolver is called by SECURITY DEFINER functions owned by postgres: signals_on_dues_update, signals_on_dues_delete, sync_session_payment_on_rsvp, acknowledge_session_refund; the budget by an edge function on the service key, naver-cost-guard.ts; the error read by nothing in the app, scripts read the table). supabase/tests/00760_close_three_open_definer_functions.test.sql asserts a signed-in user is refused each and the internal paths still work. get_club_discovery_summaries also pins search_path = '' (ALTER only, output unchanged).
• Trigger functions and helpers are not callable by clients (00761, wave 1). EXECUTE is revoked from PUBLIC, anon and authenticated on the 27 public trigger functions (a trigger function is checked for EXECUTE when the trigger is created, never when it fires, so a member's own write still fires it) and on get_session_club_id, dev_inspect_cron_status, affected_region_keys, sido_key_from_label and signals_is_in_quiet_window, whose only callers are owner-run definer functions. Policy helpers (is_club_*, dm_thread_messageable, get_match_club_id, can_react_to_match, has_pending_join_request) keep their grant: RLS evaluates them as the caller. Invariant tests: invariant_secdef_authenticated_caller (I-1: every public definer function open to authenticated names its caller, calls private.*, or is on an allow-list with a reason per entry; four entries are open items for wave 3), invariant_secdef_hygiene_canary (I-8: a known-positive for the PUBLIC-EXECUTE and search_path gates) and invariant_anon_write_surface (I-9: anon may INSERT into exactly three telemetry tables, each with its ingress guard).
• Anonymous writes are bounded (00761). client_errors and club_growth_events take the ingress guard of client_perf_events (private.guard_*_ingress: size bounds, a per-source rate through rate_limit_policies, a daily global cap through public_ingress_policies; actions client-errors 60 a minute and 50,000 a day, club-growth-events 120 a minute and 100,000 a day). Server bounds: message 1..8,000 characters; stack and component stack 16,000; route 500; app version and build 64; context a JSON object of at most 32,768 octets; growth metadata an object of at most 2,048 octets. platform = 'cron-watchdog' is refused to anon and authenticated (42501): only the database itself and the service role write it. The app's writers stay under half of every bound (client-error-row.ts: message 4,000, stack 8,000, route 500, context 16,384 octets with the largest values replaced by a marker; club-growth-metadata.ts: 1,024 octets), and telemetry-bounds-parity.test.ts reads the migration and fails when either side moves alone. A row over a bound is refused (22023) and the report is lost, so a new telemetry field gets a writer bound in the same change.
• One developer gate (00761). private.is_dev_caller() is true only for the developer account AND while private.dev_rpcs_enabled() (app_config.allow_dev_rpcs) is on; it is the only place the account is named. private.assert_dev_caller(fn) raises {fn} is dev-account only (42501, HINT dev_account_only); the nine developer functions (dev_read_telemetry, dev_clear_telemetry, dev_emit_test_push_signal, dev_inspect_cron_status, dev_mock_pay_session, dev_read_content_reports, dev_resolve_content_report, support_get_user_errors, get_user_consents's cross-user path) all use it. With the switch off every one refuses, even for the developer account. A new developer function calls the gate and never names the account. The production project runs with the switch off and the developer account disabled (a launch-day item, docs/release-evidence.md).
• A pgTAP test reads and writes only its own fixture. Every row it counts or deletes is scoped to the fixture (its ids, or created_at >= now(), which inside the test's transaction means rows this test created); a whole-table DELETE/TRUNCATE or an unscoped count(*) fails check:pgtap-hermetic (exempt only by a property the script verifies: a permission probe inside throws_ok/lives_ok, a listed reference table, or a count passed as an argument). A global result the test then filters must be complete: a picker or feed that returns the oldest N rows (signals_pick_pushable(100)) let a seeded database's older rows crowd the fixture out, so pass a limit that covers every row. Why: tests that read global state passed on a fresh CI database and failed on any seeded local stack, and a whole-table wipe locks every row, so two suite runs at once deadlocked. Fixture ids are fixed by design: run one suite at a time against a database. One stated exception: where the RPC under test owns a bucket the test cannot redirect (00689: the ingress RPCs stamp today's KST day, and the prune helper is table-wide over expired days), the test sets that footprint aside with a scoped DELETE inside its transaction, which ROLLBACK restores. It never refuses to run on rows a used local stack left there: 00689 did, and one day of local app use failed it on that day, six days later, and the next morning until the 04:23 prune (2026-10-28).
• A trigger function that writes a table other than its own runs as SECURITY DEFINER, and a denormalized count belongs to its trigger alone. An invoker trigger runs as whoever fired it, so its write passes through the OTHER table's RLS as that user: the post counters (club_posts.comment_count/reaction_count/read_count) updated 0 rows whenever a plain member commented, reacted or read someone else's post, and the count never moved (fixed in 00671). A count column is zeroed on INSERT for every writer and reverted on any client UPDATE (author and admin included), so neither a seed nor a PATCH can put a number there that its rows don't back; seeds insert the rows and let the triggers count. Enforced by supabase/tests/trigger_cross_table_writes_definer.test.sql: a catalog scan that also follows calls into invoker helpers (three levels), with a canary for the direct and the delegated write; it is textual, so dynamic EXECUTE is out of its reach.
• CREATE OR REPLACE FUNCTION is two distinct footguns — both surface only at call time, never at migration-apply time, so supabase db reset passing does NOT clear them. (1) Changed arg list spawns a new overload. CREATE OR REPLACE only replaces a function with the EXACT same signature; adding/removing/retyping a parameter creates a SEPARATE overload and leaves the old one live. When every param is defaulted, the two then collide for the lower-arity call (42725 "function … is not unique"); worse, the orphan can keep a deprecated body reachable. When you change a function's signature, DROP the prior signature explicitly in the same migration (DROP FUNCTION IF EXISTS schema.fn(<old arg types>);). Found 2026-05-24: recent_client_errors(int) orphaned by 00191's 2-arg form, and private.submit_match_score(uuid,int,int,int) orphaned by 00084's p_outcome addition (the orphan still carried the pre-v2 ELO body) — both dropped in 00201. A pg_proc scan grouping by (nspname, proname) HAVING count(*) > 1 over public+private is the audit query; it must return 0 rows. (2) Re-deriving from a stale base silently reverts a later fix. When a hardening/refactor migration re-issues CREATE OR REPLACE for a function, it must start from the function's CURRENT (latest-migration) body, not the original. 00194's search_path pass re-derived signals_enforce_immutability from the 00107 original (service_role-only bypass) instead of the 00109 fix (auth.uid() IS NULL OR service_role), silently re-breaking signal dedup re-emits (signals_emit's ON CONFLICT DO UPDATE runs with auth.uid() NULL — the headline emits are clean INSERTs so it shipped silently; only re-emitted/aggregating signals from cron broke). Restored in 00200. Before re-issuing a function, grep -rl "FUNCTION <schema>.<name>" supabase/migrations/ and diff against the HIGHEST-numbered prior definition.
• The current definition of any database object is in the committed snapshot, offline (EP-1, wave 5). docs/schema/** is generated from the local database (public and private as pg_dump --schema-only entries, plus the cron job list and the storage buckets and policies; never row data) by yarn db:snapshot; migration files are history, the snapshot is the answer to "what does X do now".
• Look it up with yarn db:def <name> [--type function|table|trigger|policy|…] [--schema public|private]: it prints the function (with its grants and comment), or the table with its constraints, indexes, policies and triggers. Start every CREATE OR REPLACE from this output, never from the last migration that touched the function.
• yarn check:schema-snapshot fails when the snapshot differs from a fresh dump of the local database (so a migration lands with a refreshed docs/schema in the same commit); like check:supabase-types it runs only with SUPABASE_USE_LOCAL=1 and skips with a message otherwise. Invariants I-4 (invariant_raise_contract: a refusal names its ERRCODE, and a HINT when it is P0001) and I-6 (invariant_trigger_order: the pinned trigger order on rsvps, matches, sessions, club_members, session_payments) are pgTAP ratchets.
• profiles and clubs carry column allow-lists, not table grants (00691, 00711). The migration that adds a column to either writes GRANT SELECT (<column>) ON public.<table> TO authenticated, or every client read of the new column fails with 42501; club_invite_code_members_only.test.sql fails until a new clubs column has its grant. A SECURITY INVOKER function that reads either table names its columns: SELECT * or c.* needs every column, so it fails for everyone once one column is closed (00711 rewrote the club, home and session bundles for this).
• member_reports carries a column allow-list too (00736): who filed a club report (reporter_id) and the link to the platform report (content_report_id) are read only by SECURITY DEFINER code, never by a client, so a club's officials cannot learn who reported a member. A row policy alone does not hide a column: when a table's rows are readable by a role but one column must not be, close the column in the grant, and assert it in pgTAP (one_report_pipeline.test.sql lists exactly the closed columns, so a new column there fails until it is granted or named as closed).
• Extension functions are schema-qualified in migrations (extensions.gen_salt(...), extensions.crypt(...), extensions.digest(...), extensions.uuid_generate_v4(...)). supabase db push applies each file with a search_path that does NOT include extensions, while db reset and migration up --local resolve them — so an unqualified call passes every local gate and then fails on the linked project mid-chain, leaving the earlier prefix applied (2026-09-05: 00590's fixture gen_salt('bf') failed after 00585–00589 had landed). Enforced by yarn lint:migration-extension-calls (DATA-14, in yarn check).
• An idempotency guard (IF NOT EXISTS, or a DO-block IF NOT EXISTS (SELECT FROM pg_constraint WHERE conname=…)) silently SKIPS a constraint/table replacement, not just a re-create. A "fix" migration that intends to TIGHTEN or replace an object whose name already exists becomes a NO-OP under such a guard — it leaves the OLD definition live, and supabase db reset (fresh DB) PASSES because the guard takes the create-branch there, so the bug only exists in environments that predate the object (i.e. prod). To replace a constraint, ALTER TABLE … DROP CONSTRAINT IF EXISTS then ADD it unconditionally (normalize offending rows FIRST so the new CHECK validates). Found 2026-06-03: 00151 intended to re-pin clubs_min_tier_check/max_tier_check to the 5-tier enum but its IF NOT EXISTS guard left the original 00093 7-tier check live in PROD for months (allowed platinum/diamond, blocked grandmaster) — only a runtime pg_get_constraintdef probe caught it; fixed unconditionally in 00229. Same class as the CREATE TABLE IF NOT EXISTS that silently skipped over a pre-existing v1 table and broke 00124. Verify a constraint/table "fix" actually took by probing the LIVE definition (pg_get_constraintdef(oid) / \d), never by trusting that db reset passed.
• Secret-value verification in chat / docs → NEVER paste the literal value. Use SELECT length(value) FROM <table> WHERE key = '<x>'; or digest(value, 'sha256') to verify identity. Chat transcripts are durable; an exposed secret stays exposed until revoked. Lesson from 2026-05-19 rotation pause: a RETURNING length(value) AS value_len mismatch triggered a debug paste of an sb_secret in chat, which then required regenerating the key from scratch. The canonical safe pattern is RETURNING key, length(value) AS value_len; and watch for trailing-newline traps that bump value_len by 1 (some SQL editors append \n on paste).
• Edge function service-key reads → use Deno.env.get('TWOMORE_SECRET_KEY'). Direct Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') reads are forbidden by @twomore/no-legacy-service-role-key-in-edge-functions; the legacy reserved name was retired after the Path B rotation. New edge functions should also gate Bearer-token auth guards on the project-prefixed var (see simulate-activity/index.ts + seed-scenario/index.ts for the auth-guard pattern). SUPABASE_* names are reserved by Supabase and can't be set via the CLI — always pick a project-prefixed name for custom secrets.
• Soft-delete contract → setting deleted_at on a row is ONLY half the work. Every read of that row's table MUST add .is('deleted_at', null) (Supabase adapter pattern) — omitting it leaks soft-deleted profiles onto every leaderboard, member list, match-history opponent name, and search result. Every write that creates the soft-delete MUST also (a) clear any sensitive columns that drive ongoing behavior (push_token, etc.) and (b) revoke any consent grants if PIPA-relevant. Easy to add the soft-delete and forget either read or write side. Use the canonical delete_account_atomic SECDEF RPC pattern (migration 00186) for full atomicity: deleted_at + push_token clear + consent revocations + pi_access_log audit write in a single transaction.
• A CHECK ... NOT VALID still refuses every later UPDATE of an old offending row, so a migration cannot leave one behind (a status cron over such a row would abort its batch). The money checks (00764) go through private.validate_constraint_or_record: it validates, and when an old row offends it DROPS the constraint and records the table, the constraint and the count of rows (never a value) in private.swallowed_errors; a later migration adds the constraint after the rows are repaired. The migration's ADD CONSTRAINT holds its ACCESS EXCLUSIVE lock to commit, so the validation scan runs under it too.

Write serialization

Transaction advisory locks, one key per domain, defined once in migrations 00681 and 00684 (private.lock_*, never ad-hoc pg_advisory_xact_lock calls). Every key is (classid, objid); unrelated domains and unrelated sessions never share a key.

Helper	Key	Exclusive	Shared
lock_rating_replay(excl)	(2147483000, 1)	recompute_all_ratings, agree_to_correction, dev_reconcile_seeded_world, seed maintenance	score submit/verify/agree_to_call/corrections, rating application, player_ratings and matches.verified_by writes
lock_session_roster(session, excl)	(2147483001, hashtext(session))	RSVP and guest-application writes (row trigger), match slot/round writes, sessions.status writes, complete_session/forfeit/cancels, waitlist admit	score work on that session's matches and its completion cascade
lock_departures(excl)	(2147483002, 1)	club_members deactivate/delete, profiles delete/anonymize, clubs purge, purge_archived_clubs	score submit
lock_dm_thread(thread)	(2147483003, hashtext(thread))	dm_messages INSERT/UPDATE/DELETE (BEFORE ROW trigger), every messaging RPC (read receipt, participants add/remove, rename, leave); also takes departures shared first	—
lock_push_claims()	(2147483004, 1)	signals_claim_pushable, signals_mark_pushed, signals_record_push_receipts (receipts also hold replay and departures shared)	—
lock_refund_tick()	(2147483005, 1)	tick_session_refund_obligations (also departures shared first, then each overdue session's roster key)	—

Rules:

• Order: rating replay, then departures, then refund tick, then session roster, then row locks (UUID order). The DM-thread and push-claim keys are leaves: nothing holding one takes a roster, rating or exclusive departures key, so they may sit under a roster key but never above one. Score entry points call private.lock_match_score_write(match), which takes the three shared keys in that order.
• Upgrade: only lock_rating_replay forbids shared to exclusive (P0001 rating_gate_upgrade_forbidden); take exclusive first. lock_session_roster allows the score-owned completion cascade (a write from inside another trigger) to reuse its shared session key.
• Row triggers are a backstop: BEFORE ROW fires after the tuple lock on UPDATE/DELETE, so RPCs that write rosters take lock_session_roster(p_session_id, true) as their first statement.
• A new write path that touches rosters, ratings or departures joins an existing key; a new domain gets a new classid and a row here. Never reuse (2147483000, 1) for unrelated work (one key for everything was the 00682 draft; it serialized unrelated sessions).
• Payments are roster writes: a session_payments INSERT/status UPDATE (BEFORE ROW trigger) and settle_manual_session_payment / acknowledge_session_refund take lock_session_roster(session, true) before the parent/payment tuples, so cancellation, confirmation and acknowledgement of one session queue on one key while two sessions never do.
• A DELETE retraction on dm_messages takes its thread key in a BEFORE ROW trigger (after the message tuple lock), so the one key holder that could wait on a message tuple, the read-receipt UPDATE, uses FOR UPDATE SKIP LOCKED and never waits on a retraction.
• pgTAP 00681_write_serialization.test.sql and 00684_write_serialization.test.sql assert the held keys per entry point through pg_locks; scripts/db-concurrency/** proves the interleavings on a disposable database (see disposable_guard.py: loopback AND the CI-only private.disposable_database() marker, else it refuses).

Sharing

• Handing a file to the person (the 장부's CSV) → the FileSharePort (fileShare in @/registry, ports/services/file-share.service.port.ts): the web adapter downloads a Blob, the native adapter (file-share.native.ts: expo-file-system + expo-sharing) writes the file to the cache directory and opens the OS share sheet. Features use the hooks (useDownloadClubLedger, useCanShareLedgerFile), never the registry, and the native adapter is reached only on native (the web build resolves file-share.native.web.ts and stubs both modules). The native path needs a build that holds the modules and has not run on a device (docs/release-evidence.md, S18).
• Building a share-card → use buildSessionShareCard / buildMatchShareCard (and future buildXxxShareCard siblings) from @/presentation/utils/share-cards.ts. Configuration-driven — the card pulls fields from the entity. Never compose share text inline.
• Sharing a session → the useShareSession hook (or a future useShareXxx sibling; useShareMatch retired with the old match board, W4 batch 12 — buildMatchShareCard waits in share-cards.ts for the live pages); never call Share.share() directly from a component.
• Sharing a club → useShareClubInvite (a member: the code, read when the member shares) or useShareClubPage (a visitor: the page link, never the code).

No direct write to a challenge (follow-up batch C, 2026-11-12)

inter_club_challenges has a members' SELECT policy and no INSERT, UPDATE or DELETE grant for authenticated or anon: a challenge is written by propose_challenge, respond_to_challenge, cancel_challenge and private.settle_challenge (the settle job) alone, and the app's repository exposes propose, respond, cancel, standings and no create. The two helpers a policy calls (private.is_challenge_counterpart_member, private.holds_tagged_seat, private.owes_tagged_session_fee) return a boolean about the caller only and are the narrow exceptions on the SECDEF ACL list. A new write path to the table is a new function on the pgTAP list in 00755_challenge_lifecycle.test.sql, never a policy.

Failures stay visible: session renewal, keys, the server's sink, the withdrawal walk (engineering pass wave 2, 2026-11-12)

• Session renewal. A failed renewal is classified in the auth adapter from the installed auth library's own error classes (@supabase/auth-js 2.117.2: AuthRetryableFetchError for no network, a timeout and a 5xx, fetch.js handleError / _request; AuthApiError 429; AuthRefreshDiscardedError when a sign-out raced it; AuthUnknownError, an answer that was not the auth service's own JSON) and leaves the port as a DomainError coded AUTH_REFRESH_UNAVAILABLE (isAuthRefreshUnavailable, ports/services/auth-refresh-error.ts). Only a refusal the service decided (a 400 or 401 such as invalid_grant, refresh_token_not_found, AuthSessionMissingError) counts toward sign-out. An unavailable answer keeps the session, is retried on the minute tick, never counts, and is reported at warning severity once and then at most every 10 minutes. A session already expired at start signs out on the first refusal, whichever tick brings it. Never match a failure by its message text.
• Idempotency keys. One helper, newIdempotencyKey() (lib/idempotency-key.ts, expo-crypto on a device, the platform's Web Crypto on the web). A key that guards a write never comes from Math.random(); a key already queued on a device is replayed as stored. Math.random remains only for ids that guard nothing (a toast id, an optimistic post id, an upload file name, a crash-log id, the mock payment service).
• The confirm Sheet closes, then awaits the button's action and does not catch its rejection. An action that returns a promise presents its own typed failure (a mutation's errorSurface, a typed catch) and lets a defect reject, which reaches the app's unhandled-rejection report (Sentry's React Native integration enables Hermes' rejection tracker).
• Server: WHEN OTHERS. No WHEN OTHERS THEN NULL. A block that must continue (a trigger that must not abort its statement, a batch job) calls private.record_swallowed(where, SQLSTATE, SQLERRM) first: the sink private.swallowed_errors stores the place, the SQLSTATE and only schema-object names quoted by the message (constraint, function, trigger, relation, column, index), never message text or a value, and is purged after 30 days by prune_telemetry. A validator of untrusted input narrows to raise_exception OR data_exception. A permanently failing item is recorded at most once an hour per item. invariant_no_silent_when_others lists the one reviewed exception (seed_score_matches). Nobody sees the sink unless the operator reads it: it is a private table with no screen, read with SQL (SELECT source, count(*) FROM private.swallowed_errors GROUP BY 1, release-evidence S32), so a failure recorded there is not a failure a person is told. That is deliberate where the operation must not fail (the withdrawal ends the person's sign-in sessions through end_sign_in_sessions_of_user, which records an insufficient_privilege and goes on, because a withdrawal must not fail on that), and it makes the read after each push part of the closure (S32).
• Server: the cron watchdog counts failed runs (alerting after the job's threshold in private.cron_expected_intervals: 1 for a job running daily or less, 3 for the rest), reports a job whose last success is older than 1.5 times its interval, and reports a job with no interval row. A new cron job registers its interval in its own migration (invariant_cron_expected_intervals). Known gap: a run stuck in running is not detected.
• Server: the withdrawal walk. At day 30 anonymize_withdrawn_profiles anonymizes the profile, scrubs the auth.users row in place (placeholder withdrawn-<id>@withdrawn.invalid, no phone, no password, empty metadata, still banned), and deletes the person's rows in every auth table with a user_id column (private.delete_auth_rows_of_user finds them at run time, so a later auth version needs no change; a table the owner may not delete from is recorded and skipped) and the audit-log entries naming the person. It also deletes the person's private match notes and invite lookup receipts, nulls feedback notes, invited phones and the club nickname. Posts, comments, messages and reviews stay under the anonymized id until the legal check (S24) says otherwise. A new table with a key to profiles or auth.users decides its row in this walk in the same migration.

Trust at the boundary, the membership predicate, lookup limits, and who words a failure (engineering pass waves 3 and 4, 2026-11-12)

• A bundle result is validated, reporting first. The four bundle RPC results (club detail, home snapshot, session card, session detail) pass through parseBundle(name, schema, data) (adapters/supabase/bundle-wire.ts) against bundle-schemas.ts: every key the RPC emits is required, rows are checked to be objects (the mappers still check columns), the one tolerant field is thumbhashes. A mismatch is reported once per bundle per session with issue paths only (ids and indexes shown as *) and the value is returned as before; enforcing it is owed (see docs/release-evidence.md, S11). A new bundle adds its schema, not a cast. The data client RETURNS a failed query's error: a read that wraps it in try/catch never sees it, so a failed read must go through handleError (the weather adapter did not, and a failed read read as "no rows").
• Membership has one predicate. A policy that asks "is this person an active member (or admin) of the club" calls public.is_club_member / public.is_club_admin, which test is_active; it never repeats the club_members subquery, so a deactivated member loses access on its own, not because another table's policy hides the row. Every policy reads the caller as (SELECT auth.uid()) so the planner evaluates it once. Enforced by supabase/tests/policies_use_the_membership_helpers.test.sql and invariant_policy_shape.test.sql (with canaries).
• Enumerable lookups are limited. search_public_profiles needs two characters, caps its limit at 50 and is charged to profile_search (40 per 10 minutes, hint rate_limited_profile_search); count_clubs_by_name is charged to club_name_probe (30 an hour, hint rate_limited_club_name_probe, and it raises: a probe that is refused is never read as "name available"); has_pending_join_request answers only for oneself or an official of the club; get_season_standings only for an active member; guest_rsvp_gate_context only for the caller's own id or a member of the session's club. A new function that answers a question about someone else's data carries a per-person limit or a membership gate, held by lookup_limits_and_gates.test.sql and invariant_rate_limit_liveness.test.sql.
• The app words a failed write; the server's sentence is never shown. failureMessage(error, { hints, codes, otherwise }) (presentation/utils/failure-message.ts) is the one reader: a line by the refusal's HINT, then its CODE, then otherwise, then the app's own lines for the failure classes every write meets (UNAUTHORIZED, FORBIDDEN, the network codes, OFFLINE_WRITE_BLOCKED), then common.genericError. It never returns error.message (a test pins it). The factory's default message is failureMessage(err); a feature's table of refusals (clubRefusalWords, sessionRefusalWords, dm.refusal) is a thin table over it.
• Every mutation hook names its errorSurface (required by the type): helper (a line under the field or on the CTA band), dialog (the app's one WriteFailureDialog, @twomore/app, over the page), notice (a Notice on the page, or nothing) or toast. A Toast is for a write whose failure can land after the person left the page (queueable writes, a venue adopt from a closed overlay) or that no page presses yet, each with a one-line reason in the hook. A background write the person cannot redo (a read mark, a delivery audit line, an unmount cleanup) shows nothing and is reported: errorSurface: 'notice' with hapticOnError: false; the factory still reports it. check:error-toast-growth ratchets errorSurface: 'toast' and error.message read in packages/features/** down, never up.

Who may write: the authority closure (security pass, 2026-11-13, migration 00765)

• A membership row comes only from a join function. club_members has no INSERT grant and no INSERT policy for a client; handle_new_club (the founder), join_club_member_checked (join_open_club, join_club_by_invite_code[_for_club], apply_to_club and decide_join_request for a person's own application) and answer_club_nomination write it. A person becomes a member only by their own act (N13): a nomination (nominate_for_membership) and a guest conversion (confirm_guest_conversion) create a pending offer (club_join_request, source admin_nominated); decide_join_request refuses to accept one (hint decide_nominee_must_accept); the offered person answers through answer_club_nomination(request, accept). One rule for an offer that ends without a yes: when an admin_nominated request leaves pending as rejected or cancelled (the person declines, an official withdraws, the club stops asking for approval), the trigger release_guest_when_offer_ends returns the guest row from proposed to active; remove_club_guest cancels the guest's waiting offer in the same transaction, so a removed guest cannot accept afterwards; the officials' counts (pending_join_requests_count, pendingApplications, signals_open_requests) leave offers out (they wait for the person, not the official); confirm_guest_conversion answers nominated, self_request_pending (the guest's own request blocks the offer) or converted. A client UPDATE of club_members is a column grant for auto_rsvp alone (the notify level and the group have their own functions).
• A key column never moves under a client's hand. A trigger, z_forbid_key_columns (private.forbid_key_column_change), refuses a changed session_id, club_id, user_id, event_id or profile_id on every table in the key-column table (supabase/tests/invariant_key_columns.test.sql builds it from the catalog and fails on a new unguarded pair). It is a trigger and not a column REVOKE because PostgREST's upsert names every payload column in ON CONFLICT DO UPDATE SET: a revoked column breaks the upsert even when the value is unchanged, while the trigger lets an equal value pass. Its discriminator is the existing one (CURRENT_USER in authenticated, anon, authenticator), so a SECURITY DEFINER function, which runs as its owner, can still move a key. A new table that a client may UPDATE and that carries such a column needs the trigger (or a reasoned row in the test's allow-list).
• Entitlements, contributions and scores are a function's. subscriptions, club_challenge_contributions, match_sets and match_score_calls have no client INSERT, UPDATE or DELETE (SELECT stays); increment_challenge, the score functions and the billing path write them. invariant_fk_index_and_write_grants pins the tables a client may still write by name and command, and ratchets the grants that no policy backs at one (dues INSERT, reviewed). A post reaction is switched in place and only a member reacts: club_post_reactions has INSERT and UPDATE policies that ask for the person's own row in a post of a club they belong to (the upsert is ON CONFLICT (post_id, user_id) DO UPDATE), the count trigger fires on INSERT and DELETE only, and the key guard holds post_id and user_id so a switch cannot move a reaction to another post.
• The anonymous role reads two columns lists, nothing more. anon holds SELECT on 16 named columns of sessions and 16 of club_media (no host, rules or money column, no uploader); the signed-out pages call SECURITY DEFINER functions (get_public_share_preview, get_club_invite_preview) and read neither table. A policy expression runs with the caller's column rights, so hiding a column from anon breaks a policy that names it: the club_media metadata read and the gallery bucket's read are split into an authenticated policy (unchanged) and an anonymous one (the public clause only). invariant_anon_columns pins the lists and proves what anon and a non-member read.
• profiles UPDATE is a column list. deleted_at and anonymized_at are not client-writable: withdrawal is delete_account_atomic, which also ends the person's sign-in sessions (private.end_sign_in_sessions_of_user). A new profiles column needs its own GRANT UPDATE (<column>) as well as its GRANT SELECT (<column>), or a client write of it fails with 42501. invariant_profile_column_grants pins every profiles column as client-writable or server-only (the client-writable list equals the live UPDATE grant), the columns authenticated cannot read (the SELECT list), and that anon reads none; a canary adds a column and shows the test names it. The same rule for clubs is club_invite_code_members_only (every column but the invite code).
• A push token belongs to one profile. A trigger clears the same token from every other profile when one registers it, and clear_my_push_token() is the signed-in person's own clear (sign-out calls it first while online; offline it is skipped and the next registration clears it).
• A rate-limited replay refunds its charge once. correct_own_score refuses cheaply, charges the limit, takes the replay lock, re-reads, and gives the charge back (private.refund_rate_limit_charge, one charge, never below zero) only when that locked re-read refuses.

See also

• AGENTS.md — DATA-1..DATA-27, KR-5 (machine-enforced data and consent constraints).
• Best Practices › Security — PIPA compliance, RLS, consent, and SECDEF audit.
• Best Practices › State & Architecture — Zustand, TanStack Query, caching strategy.
• CLAUDE.md — orchestration core + the pointer index back to this doc.