# REF: Supabase Row Level Security Patterns

## CRITICAL: This is a REFERENCE BCM. ALL user-facing tables MUST have RLS enabled.
## A table without RLS exposes ALL data to ANY authenticated user.

## 1. Enable RLS (REQUIRED on every table)
```sql
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;
```

## 2. Pattern: User Owns Their Data (most common)
Use for: profiles, user_settings, user_documents, invoices, etc.
```sql
-- Users can only see their own rows
CREATE POLICY "Users read own data"
  ON user_documents FOR SELECT
  USING (auth.uid() = user_id);

-- Users can only insert rows they own
CREATE POLICY "Users insert own data"
  ON user_documents FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Users can only update their own rows
CREATE POLICY "Users update own data"
  ON user_documents FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Users can only delete their own rows
CREATE POLICY "Users delete own data"
  ON user_documents FOR DELETE
  USING (auth.uid() = user_id);
```

## 3. Pattern: Public Read, Authenticated Write
Use for: blog posts, published content, public profiles, product listings
```sql
-- Anyone can read (including anonymous)
CREATE POLICY "Public read access"
  ON published_content FOR SELECT
  USING (true);

-- Only the author can insert
CREATE POLICY "Authors insert own content"
  ON published_content FOR INSERT
  WITH CHECK (auth.uid() = author_id);

-- Only the author can update
CREATE POLICY "Authors update own content"
  ON published_content FOR UPDATE
  USING (auth.uid() = author_id);
```

## 4. Pattern: Subscription-Gated Access
Use for: premium content, pro features, gated resources
```sql
-- Free users see free content, pro users see everything
CREATE POLICY "Subscription-gated read"
  ON premium_content FOR SELECT
  USING (
    is_free = true
    OR EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.subscription_status = 'active'
    )
  );
```

## 5. Pattern: Admin Full Access
Use for: admin dashboards, moderation tools
```sql
-- Check admin role via profiles or auth metadata
CREATE POLICY "Admin full access"
  ON any_table FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );
```

## 6. Pattern: Profiles Table (special — auto-create on signup)
```sql
-- Create profiles table
CREATE TABLE profiles (
  id uuid REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email text,
  full_name text,
  avatar_url text,
  stripe_customer_id text,
  subscription_status text DEFAULT 'free',
  subscription_id text,
  role text DEFAULT 'user',
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Users read and update their own profile
CREATE POLICY "Users read own profile" ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users update own profile" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Auto-create profile on signup via trigger
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    new.id,
    new.email,
    new.raw_user_meta_data->>'full_name',
    new.raw_user_meta_data->>'avatar_url'
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
```

## 7. Pattern: Service Role Bypass (for webhooks, background jobs)
```typescript
// Service role client ignores RLS — use ONLY server-side
import { createClient } from "@supabase/supabase-js"

const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // NEVER expose to client
)
```

## COMMON MISTAKES TO AVOID
1. NEVER forget to enable RLS — disabled = all data exposed
2. NEVER use service_role key on the client — it bypasses all security
3. ALWAYS test policies by signing in as a regular user and trying to access other users data
4. ALWAYS add INSERT policy WITH CHECK — or users can insert rows claiming to be other users
5. ALWAYS use auth.uid() not a user-provided ID for policy checks
6. NEVER create a SELECT policy with USING (true) on tables with private data

## Required ENV Variables
```
NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...  (safe for client)
SUPABASE_SERVICE_ROLE_KEY=eyJ...     (server ONLY, never expose)
```