Supabase Rls

RLS policy template (apply to every user-owned table)

-- 002_rls_policies.sql

ALTER TABLE {table} ENABLE ROW LEVEL SECURITY;

CREATE POLICY "{table}_select_own" ON {table}
  FOR SELECT USING (user_id = (SELECT auth.uid()));

CREATE POLICY "{table}_insert_own" ON {table}
  FOR INSERT WITH CHECK (user_id = (SELECT auth.uid()));

CREATE POLICY "{table}_update_own" ON {table}
  FOR UPDATE
  USING (user_id = (SELECT auth.uid()))
  WITH CHECK (user_id = (SELECT auth.uid()));

CREATE POLICY "{table}_delete_own" ON {table}
  FOR DELETE USING (user_id = (SELECT auth.uid()));

Migration naming

supabase/migrations/
  001_initial_schema.sql    ← all tables
  002_rls_policies.sql      ← RLS for every table
  003_indexes.sql           ← performance indexes
  004_add_tags_to_chats.sql ← new feature (never edit 001-003)

Verify RLS (run after every migration)

-- Must return TRUE for every row
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Type generation

# After every migration
pnpm db:gen
# = supabase gen types typescript --local > packages/database/src/types.ts

Local vs Cloud workflow

# Local development
supabase start                    # start Docker containers
supabase db reset                 # reset + re-run all migrations (DESTRUCTIVE)
pnpm db:gen                       # regenerate types

# Cloud staging
supabase db push                  # apply pending migrations to cloud
supabase db push --dry-run        # preview what would run

# Never run db:reset on production

API route — manual user_id filter (defense in depth)

// Even with RLS, always filter explicitly in queries
const { data, error } = await supabase
  .from('chats')
  .select('*')
  .eq('user_id', user.id)   // ← explicit, even though RLS enforces it
  .order('created_at', { ascending: false })

createServerClient vs createBrowserClient

// Server (API routes, Server Components) — uses cookies
import { createServerClient } from '@/lib/supabase/server'
const supabase = await createServerClient()

// Browser (Client Components) — singleton
import { createBrowserClient } from '@/lib/supabase/browser'
const supabase = createBrowserClient()

// Service role (admin operations only, server-side only)
import { createClient } from '@supabase/supabase-js'
const admin = createClient(url, process.env['SUPABASE_SERVICE_ROLE_KEY']!)
// Never expose service role key to client

Anti-patterns (forbidden)

❌ CREATE TABLE chats (...);  -- without RLS policies
❌ auth.uid() = user_id       -- use (SELECT auth.uid()) for performance
❌ Editing existing migration files

❌ supabase.auth.getSession()   // can return stale data, use getUser()
❌ NEXT_PUBLIC_SUPABASE_SERVICE_ROLE_KEY  // exposes secret
❌ supabase.from('chats').select('*')     // without .eq('user_id', user.id)

Verification checklist

• Every new table has RLS migration?
• SELECT tablename, rowsecurity FROM pg_tables — all TRUE?
• pnpm db:gen run after migration?
• packages/database/src/types.ts committed?
• Cross-user isolation tested (User A cannot see User B data)?
• Migration file named correctly and not editing existing ones?