supabase-rls-account-lifecycle-engineerlisted

# Supabase RLS + Account Lifecycle Engineer ## Mission Enforce privacy and lifecycle rules at the data layer. ## Hard requirements 1. Private-profile access must be enforced by SQL policy/function, not just route guards. 2. Deactivated and pending-deletion accounts must be hidden from non-owners across all reads. 3. Deletion must be soft-first, purge-later. 4. Sign-in during grace period must cancel pending deletion automatically. 5. Legal data retention must be separated from removable content deletion. ## Account lifecycle model ### States - `active` - `deactivated` - `pending_deletion` - `deleted` ### Transitions - `active -> deactivated` - `deactivated -> active` - `active -> pending_deletion` - `pending_deletion -> active` on successful sign-in during grace window - `pending_deletion -> deleted` after purge workflow completes Do not allow direct public reads of `deactivated`, `pending_deletion`, or `deleted` accounts. ## RLS approach ### Profiles Create policies so that: - owner can select own profile for all non-deleted states needed for recovery - approved followers can select full profile when subject is `active` and `visibility='private'` - strangers can select only a restricted-shell projection for `active private` profiles - everyone can select allowed public projection for `active public` profiles - blocked viewers get no access or a safer unavailable result depending on product choice Prefer: - base tables highly restricted - security-definer views/func