Row-Level Security (RLS)

All user-facing tables in Supabase have Row-Level Security enabled. RLS ensures users can only access their own data, even if an API bug exposes a direct database query.

Standard Pattern

Every user table follows the same RLS pattern:

-- Enable RLS
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

-- Users can only see their own rows
CREATE POLICY "select_own" ON table_name
  FOR SELECT USING (auth.uid() = user_id);

-- Users can only insert rows for themselves
CREATE POLICY "insert_own" ON table_name
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Users can only update their own rows
CREATE POLICY "update_own" ON table_name
  FOR UPDATE USING (auth.uid() = user_id);

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

Tables with RLS

RLS is enabled on all tables. Representative policies:

Table	RLS Policy
user_profiles	auth.uid() = user_id
token_balances	auth.uid() = user_id
token_transactions	auth.uid() = user_id
learning_progress	auth.uid() = user_id
referral_codes	auth.uid() = user_id
workspace_emails	Owner or shared access via SECURITY DEFINER helpers
workspace_projects	Owner or shared access via SECURITY DEFINER helpers
workspace_brands	Owner or is_brand_member(id)
workspace_products	auth.uid() = user_id; shared-brand reads via is_brand_member(brand_id)
rag_documents, eval_runs/eval_results, email_suppressions, email_delivery_events	RLS enabled, no client policies — service role only

Shared Resources: Avoid Policy Recursion

Policies for shared resources (brand sharing, project sharing) must route membership checks through SECURITY DEFINER helper functions such as is_brand_member() and get_user_active_project_ids(), which bypass RLS internally.

Never subquery a table inside its own RLS policy USING clause. Postgres detects the self-reference at query time and fails every read with error 42P17 (infinite recursion) — a brand-sharing migration once took the whole site down this way. Migration database/87-brand-sharing-fix-recursion.sql is the reference fix.

SECURITY DEFINER Functions

Some operations require modifying tables that users can't directly write to (e.g., token_balances). These use SECURITY DEFINER functions:

CREATE OR REPLACE FUNCTION spend_tokens(p_action_name TEXT, p_description TEXT)
RETURNS TABLE(success BOOLEAN, new_balance INTEGER, cost INTEGER, error_message TEXT)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- auth.uid() still resolves to the calling user
  -- But the function runs with the function owner's privileges
  UPDATE token_balances
  SET balance = balance - v_cost
  WHERE user_id = auth.uid()
  AND balance >= v_cost;
  ...
END;
$$;

Key points:

• SECURITY DEFINER runs with elevated privileges (table owner)
• auth.uid() still resolves to the calling user's ID
• The atomic WHERE balance >= cost check prevents negative balances
• These functions are the only way to modify token_balances — users cannot UPDATE the table directly

Service Role Bypass

Server operations that need cross-user access or service-role-only tables use the SUPABASE_SERVICE_ROLE_KEY, which bypasses RLS entirely. This key is:

• Only used server-side (never exposed to frontend)
• Used by serverless functions for admin flows, webhooks (delivery events, suppressions), tracking, and RAG document fetches
• Stored as an environment variable in Vercel

Note: token spending (spend_tokens RPC) deliberately does not use the service role — spendTokensServer() creates a client with the anon key + the user's Bearer token so auth.uid() resolves to the calling user and RLS is respected.