Best architecture for conditional file visibility (Public vs. Friends-Only) using Supabase RLS and JSONB?

# Postmortem: Performance Degradation and Access Flaws in User Gallery Visibility System

## Summary
An RLS policy implementation for conditional file visibility caused severe performance degradation during peak traffic and allowed unauthorized access to private galleries. The policy relied on real-time JSONB settings parsing and complex friend-check subqueries.

## Root Cause
- The RLS policy performed nested subqueries against `user_settings` (JSONB) and `friendships` tables for every storage object request
- No materialized friend lists resulted in full table scans for friend validation
- JSONB field (`settings->'privacy'->>'isGalleryPrivate'`) lacked expression indexes
- Policy didn't properly isolate friend relationships due to improper JOIN conditions

## Why This Happens in Real Systems
- Developers underestimate RLS policy performance impact at scale
- JSONB fields are convenient but rarely optimized for security policies
- Complex access rules tempt engineers to handle logic purely in DB layer
- Permission checks often require multiple conditional joins that aren't cacheable
- Storage access paths typically bypass application-layer caching mechanisms

## Real-World Impact
- **Performance**: 95th percentile response time increased from 200ms to 3200ms during peak
- **Scaling**: Concurrent user capacity dropped by 60% due to DB connection saturation
- **Data Exposure**: 4.2% of private galleries were accessible to non-friends
- **User Experience**: Gallery load failures increased to 18% of requests

## Example or Code
Flawed Policy:
```sql
CREATE POLICY gallery_visibility
ON storage.objects FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM user_settings
    WHERE 
      user_settings.user_id = (storage.objects.owner).id
      AND (
        NOT (user_settings.settings->'privacy'->>'isGalleryPrivate')::boolean OR
        EXISTS (
          SELECT 1 FROM friendships
          WHERE 
            (friendships.user_id = (storage.objects.owner).id AND friendships.friend_id = auth.uid())
            OR
            (friendships.friend_id = (storage.objects.owner).id AND friendships.user_id = auth.uid())
        ) OR 
        (storage.objects.owner).id = auth.uid()
      )
  )
);

Optimized Implementation:

-- Materialized friend graph
CREATE MATERIALIZED VIEW user_friends AS
SELECT user_id, friend_id FROM friendships
WHERE status = 'confirmed'
UNION
SELECT friend_id AS user_id, user_id AS friend_id FROM friendships
WHERE status = 'confirmed';

-- Optimized policy
CREATE POLICY gallery_visibility_optimized
ON storage.objects FOR SELECT USING (
  (storage.objects.owner).id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM user_settings
    WHERE
      user_id = (storage.objects.owner).id
      AND (settings->'privacy'->>'isGalleryPrivate')::boolean = false
  )
  OR (
    EXISTS (
      SELECT 1 
      FROM user_settings
      WHERE 
        user_id = (storage.objects.owner).id
        AND (settings->'privacy'->>'isGalleryPrivate')::boolean = true
    )
    AND EXISTS (
      SELECT 1 FROM user_friends
      WHERE 
        user_id = (storage.objects.owner).id
        AND friend_id = auth.uid()
    )
  )
);

How Senior Engineers Fix It

Precompute friend relationships:
- Materialize confirmed friendships in a view with reflexive pairs
- Refresh using real-time database triggers
Optimize JSONB access:
- Create expression index: CREATE INDEX idx_settings_privacy ON user_settings USING GIN ((settings->'privacy'))
- Extract isGalleryPrivate to dedicated boolean column
Implement tiered caching:
- Cache public gallery paths in Redis with 5-minute TTL
- Pre-signed URLs with JWT-encoded permissions for private content
Hybrid architecture:
- Move friend-validation to application layer using Supabase middleware
- Generate ephemeral access tokens via Auth service
Monitoring:
- Added Prometheus metrics for RLS policy execution time
- Alert on JSONB parse operations >100ms