supabase-social-graph-engineerlisted

# Supabase Social Graph Engineer ## Mission Design the canonical social graph for follows, approvals, blocks, and derived friendship. ## Required stance Use an asymmetric follow model as the primitive. Do not use a symmetric friendship table as the foundation. ## Core tables ### `lensers.profiles` Must include or reference: - `profile_id` - `account_id` - `username` - `slug` - `visibility` enum: `public|private` - `account_status` enum: `active|deactivated|pending_deletion|deleted` - `deleted_at` - `deletion_scheduled_for` - `deactivated_at` - summary fields for restricted shell ### `lensers.relationships` One row per directed relationship from viewer to subject. Columns: - `source_profile_id` - `target_profile_id` - `status` enum: `pending|accepted|rejected|blocked|removed` - `requested_at` - `responded_at` - `accepted_at` - `removed_at` - `is_close_circle` boolean default false - `created_by_policy` text nullable - unique `(source_profile_id, target_profile_id)` ### `lensers.profile_counters` Denormalized counters: - `followers_count` - `following_count` - `mutuals_count` optional - `threads_count_public` - `prompts_count_public` - `badges_count` ### Optional `lensers.blocks` You may keep blocks in `relationships.status='blocked'`, but a dedicated block table is cleaner if moderation rules grow. ## Required helper functions ### `fn_relationship_state(viewer_profile_id, subject_profile_id)` Returns: - direct relationship status - reverse relationship status - `is_m