TwoMore

Appearance

Data & Hooks Conventions ​

Status: Active Last reviewed: 2026-06-30

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, 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-13, KR-5).

Formatting & display utilities ​

  • Formatting a date → packages/app/src/utils/date-format.ts. Never inline getMonth()/getDay() in components
  • 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 (SessionHeader, home cards) keep their own 혼복-style maps — different i18n namespace 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, SessionCard, 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/utils/rsvp-selectors). Never inline rsvps.filter(r => r.status === 'confirmed'). Bespoke partitions (waitlist sorted by position, confirmed-excluding-self, sessionId extraction) stay inline.
  • 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/utils/recurrence.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.

Mutations & query hooks ​

  • Creating a mutation → createMutationHook factory. Never raw useMutation (except optimistic updates). 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(...) 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).
  • 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.

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).
  • Handling adapter error → handleError(). Never silent catch. Fire-and-forget → logger.warn()
  • 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.)

Telemetry, perf & debugging ​

  • 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
  • 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.
  • 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.
  • 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.
  • 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.

Tier display ​

  • Displaying tier info → tier-display.ts utilities (TIER_ICONS, getTierColor, TIER_BADGE_VARIANT). Single source of truth for tier visual representation
  • 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 dash-separated SessionHeader variant + wizard "all levels" shortcut stay bespoke.
  • 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 — SessionCard calls this internally.

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.
  • 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.
  • 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.
  • 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. Then bump policy_versions.version to a new row + content_hash if the change is material per PIPA §30. Update the privacy policy entrustment table in privacy-screen.tsx if a new vendor is added. 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 PrivacyScreen, (2) cross-border transfer section with the vendor's PI manager contact, (3) a new policy_versions row if the change is 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.

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. anon must never hold EXECUTE on a SECDEF function: it is used only for the auth flow, and no anon-role RLS policy references any SECDEF function (verified + enforced in 00209, which revoked anon/PUBLIC from all 82 public SECDEF functions). 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).
  • 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.
  • 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.

Sharing ​

  • 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/match → useShareSession / useShareMatch hook (or future useShareXxx siblings); never call Share.share() directly from a component.

See also ​

Markdown remains the source of truth. Run yarn docs:check before handoff.