PS1 Profiles PII Split — Implementation Spec (research-grounded, 2026-07-12)

Status: Archived

Detailed build spec for PS1 slices 1–3 (the high-blast-radius profiles PII lockdown), grounded in live-DB + adapter research. Read alongside ps1-access-control.md §2.3/§3.1. The orchestrator did the research; this is the exact contract so the implementation can't drift.

The bug (worse than the audit stated)

profiles_select is USING(true) — any authenticated user can raw-select any column of any profile. Beyond crafted queries, the app's own read methods leak PII today:

• PUBLIC_PROFILE_COLUMNS (profile.supabase.ts:17) includes kakao_id and unmasked region/district/gender → findByIds/findByClub/ findTopByRating/getPlayerStats ship kakao_id + others' region/gender to every caller, bypassing each user's visibility settings.
• searchByDisplayName selects PROFILE_COLUMNS = OWN_PROFILE_COLUMNS (the FULL set incl. phone/kakao_id/push_token/consent timestamps) for every matched OTHER user — a severe leak.

The masking RPC get_public_profile_view already nulls phone/kakao/push and gates region/etc. correctly — but only findPublicView (1 of 7 cross-user read paths) uses it.

The mapper contract (authoritative — mask_profile_row MUST return all of these)

profileMapper.toDomain reads exactly these 38 snake_case fields. Every RPC that feeds the mapper must return a jsonb with ALL of them or rosters silently lose data (the current masking RPC is missing 11, incl. singles_matches_played, last_session_at, peak_petals, comeback_sessions_remaining, onboarding_completed_at, rating_state, rating_source, display_tier_sub, and the 3 consent timestamps).

Bucket	Fields
always-visible	id, display_name, avatar_url, nationality_code, elo_rating, elo_matches_played, singles_elo, singles_rd, singles_matches_played, doubles_elo, doubles_rd, rating_confidence, rating_source, rating_state, display_tier, display_tier_expires_at, display_tier_sub, trust_tier, total_sessions_confirmed, global_attendance_rate, last_session_at, comeback_sessions_remaining, peak_petals, onboarding_completed_at, created_at, updated_at, profile_visibility
visibility-gated (mask via can_view_profile_field per field)	region, district, play_regions, gender, birth_year
never public → NULL	phone, kakao_id, push_token, marketing_consent_at, privacy_accepted_at, terms_accepted_at

Slice 1 — migration (additive, ZERO behavior change until slice 2)

1. private.mask_profile_row(v_viewer uuid, v_target public.profiles) RETURNS jsonb — the single masking source of truth. Build the jsonb from the table above: always-visible passthrough; gated via public.can_view_profile_field(v_viewer, v_target.id, '<field>') (keep the existing 3-arg gate, pass v_viewer); never-public → NULL. STABLE, no explicit auth (helper — callers resolve auth.uid()).
2. Refactor get_public_profile_view(p_target_id) to v_viewer:=auth.uid() (unchanged), load the row, RETURN mask_profile_row(v_viewer, v_profile). This is a strict superset (gains the 11 missing fields) — safe for the existing findPublicView caller.
3. public.get_public_profile_views(p_target_ids uuid[]) RETURNS SETOF jsonb — v_viewer:=auth.uid(); if NULL return no rows; RETURN QUERY SELECT mask_profile_row(v_viewer, p) FROM public.profiles p WHERE p.id = ANY(p_target_ids) AND p.deleted_at IS NULL. Backs findByIds, findByClub, getPlayerStats(profile fetch).
4. public.get_public_profile_leaderboard(p_region text DEFAULT NULL, p_min_matches int DEFAULT 0, p_limit int DEFAULT 100) RETURNS SETOF jsonb — masked rows ordered by elo_rating DESC. Filter deleted_at IS NULL; if p_region not null AND region = p_region; if p_min_matches>0 AND (elo_matches_played >= p_min_matches OR singles_matches_played >= p_min_matches). Backs findTopByRating (region NULL, min_matches 1) + getRegionalLeaderboard (region set, min_matches 0). NOTE: preserves current behavior that region leaderboards group by the region column regardless of a user's region-visibility setting — do NOT change that here (a leaderboard privacy redesign is out of scope; flag as follow-up).
5. public.search_public_profiles(p_query text, p_limit int DEFAULT 20) RETURNS SETOF jsonb — masked rows, display_name ILIKE '%'||p_query||'%', deleted_at IS NULL, order display_name, limit. Backs searchByDisplayName.
6. public.get_regional_ranking(p_region text) RETURNS jsonb — v_viewer:=auth.uid(); read v_viewer's own elo; count region players + count region players with elo <= viewer's; return jsonb_build_object( 'totalPlayers', ..., 'rankPosition', ...). Backs getRegionalRanking (drop its p_user_id param — always self via auth.uid()).
7. P9 close: REVOKE EXECUTE ON FUNCTION public.can_view_profile_field(uuid, uuid,text) FROM authenticated; — it's only called by SECDEF definer-internal code now (verify no client .rpc('can_view_profile_field') first). This closes the p_viewer spoofing without a signature change (simpler than PS1 §3.1's rewrite; achieves the same). If a client caller exists, fall back to the 2-arg auth.uid() rewrite.
8. SECDEF hygiene on every new function: SECURITY DEFINER SET search_path='', REVOKE ALL ... FROM PUBLIC, anon; GRANT EXECUTE ... TO authenticated; (the corrected idiom). These are NOT anon (authenticated read paths) — so they won't trip the 00026 pgTAP anon-EXECUTE guard.

Slice 2 — adapter rewrite (client OTA), profile.supabase.ts

Route each method to its RPC, mapping the returned jsonb exactly as findPublicView already does (profileMapper.toDomain(row as ProfileRow)):

method	RPC	mapping
findByIds	get_public_profile_views(p_target_ids)	SETOF → map each
findByClub	fetch active member ids from club_members, then get_public_profile_views(ids)	keep pagination on club_members; map profile jsonb
getPlayerStats	member ids from club_members → get_public_profile_views(ids) for the profileMap; matches query unchanged	build stats as today
findTopByRating	get_public_profile_leaderboard(null, 1, limit)	SETOF → Profile[]
getRegionalLeaderboard	get_public_profile_leaderboard(region, 0, limit)	extract id/display_name/elo → lean shape + rank = idx+1
searchByDisplayName	search_public_profiles(query, limit)	SETOF → Profile[]
getRegionalRanking	get_regional_ranking(region)	jsonb →

findById (self) + findPublicView (already RPC) unchanged. Update any @freshness/mutation-key contracts if the generator requires. Regenerate supabase types if the RPCs change the typed surface (yarn check:supabase-types guidance in CLAUDE.md — pipe through prettier).

Slice 3 — migration, AFTER slice 2 OTA bakes (HARD gate)

profiles_select → USING (auth.uid() = id) (self-row only). Do NOT ship until slice 2 is live+baked — narrowing first breaks every roster/leaderboard in prod.

Verification (E2E, per slice)

• Slice 1: db reset; as an UNRELATED authenticated viewer, SELECT get_public_profile_views(ARRAY[other_id]) → phone/kakao_id/push_token/consent timestamps are NULL, region/gender NULL (not club-mate), but display_name/elo present; as the SAME user (self) → gated fields present. Field-count parity: the returned jsonb has all 38 mapper keys. can_view_profile_field no longer authenticated-executable.
• Slice 2: yarn check; on-device/OTA smoke — club roster, 기록 leaderboards, profile hydrate, search all still render.
• Slice 3: repeat §2.5's live repro — unrelated user's raw .select('phone,...') returns 0 rows.