Supabase Production Checklist

-- 1. Verify RLS is enabled on EVERY table
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public';
-- rowsecurity should be TRUE for every table!

-- 2. Check for tables without any RLS policies
SELECT t.tablename
FROM pg_tables t
LEFT JOIN pg_policies p ON t.tablename = p.tablename
WHERE t.schemaname = 'public'
  AND t.rowsecurity = true
GROUP BY t.tablename
HAVING COUNT(p.policyname) = 0;
-- These tables have RLS enabled but no policies (blocks all access!)

-- 3. Review all existing policies
SELECT tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename;

-- 4. Ensure no sensitive data is exposed
-- Check that service_role key is NEVER in client code
-- Check that API keys are in environment variables
-- Check that .env files are in .gitignore

-- 1. Find missing indexes (tables with sequential scans)
SELECT
  schemaname, relname AS table_name,
  seq_scan, idx_scan,
  seq_scan - idx_scan AS too_many_seqscans,
  n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY too_many_seqscans DESC;

-- 2. Find unused indexes (wasting write performance)
SELECT
  indexrelname AS index_name,
  relname AS table_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 3. Find slow queries
SELECT
  query,
  calls,
  mean_exec_time::numeric(10,2) AS avg_ms,
  max_exec_time::numeric(10,2) AS max_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 4. Check table bloat
SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_dead_tup AS dead_rows,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

// Security configuration checklist

// 1. Use getUser() on the server, NOT getSession()
// getUser() validates the JWT, getSession() does not
const { data: { user } } = await supabase.auth.getUser()

// 2. Validate and sanitize all user inputs
// Never trust client-side data in server actions
export async function createPost(formData: FormData) {
  const title = formData.get('title') as string
  if (!title || title.length > 200) {
    throw new Error('Invalid title')
  }

  // 3. Always check auth before mutations
  const supabase = await createClient()
  const { data: { user } } = await supabase.auth.getUser()
  if (!user) throw new Error('Not authenticated')

  // 4. Let RLS handle authorization — don't rely on app logic alone
  const { error } = await supabase.from('posts').insert({
    title: title.trim(),
    user_id: user.id,  // RLS will verify this matches auth.uid()
  })
}

// 5. Configure auth settings in Supabase Dashboard:
// - Enable email confirmation
// - Set password minimum length (8+)
// - Configure rate limiting
// - Restrict OAuth redirect URLs
// - Enable CAPTCHA for sign-ups if needed

-- Set up monitoring queries (run periodically with pg_cron)

-- Monitor connection count
SELECT count(*) AS active_connections,
  max_conn AS max_connections,
  count(*)::float / max_conn::float * 100 AS utilization_pct
FROM pg_stat_activity
CROSS JOIN (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') s
WHERE state IS NOT NULL;

-- Monitor database size growth
SELECT
  pg_size_pretty(pg_database_size('postgres')) AS db_size;

-- Monitor RLS policy performance
-- If policies use functions, ensure they're STABLE or IMMUTABLE
-- Avoid subqueries in policies — use security definer functions instead

# Use separate Supabase projects for each environment

# Development: local Supabase
npx supabase start

# Staging: separate Supabase project
npx supabase link --project-ref staging-project-id
npx supabase db push

# Production: production Supabase project
npx supabase link --project-ref production-project-id
npx supabase db push

# Environment variable management
# .env.local          → local development
# .env.staging        → staging
# .env.production     → production
# NEVER commit .env files to git