{"slug":"supabase-rls-and-migrations","title":"Supabase RLS, Migrations, Backup, and Production Patterns","tags":["supabase","postgres","rls","migrations","database","security"],"agent_summary":"Supabase production patterns — Row Level Security policies, migration workflow (always create rollback), backup strategies, point-in-time recovery, storage setup, edge functions, and common pitfalls.","trigger_phrases":["Supabase RLS","Supabase migrations","Supabase backup","Row Level Security","Supabase production","Supabase policy","supabase service role","Supabase edge function"],"runnable":false,"markdown":"\n## Overview\n\nSupabase wraps PostgreSQL with auth, storage, realtime, and edge functions. The primary gotcha in production: RLS is disabled by default on new tables. Always enable it.\n\n## Project Reference\n\n```\nProject ref: gmgxxiqgshbbgzhqzngq\nSupabase URL: https://gmgxxiqgshbbgzhqzngq.supabase.co\n```\n\n## RLS: Row Level Security\n\nEnable on every table. Without RLS, any authenticated user can read/write all rows.\n\n```sql\n-- Enable RLS\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\n-- Users can only see their own rows\nCREATE POLICY \"owner_select\" ON projects\n FOR SELECT USING (auth.uid() = user_id);\n\n-- Users can only insert their own rows\nCREATE POLICY \"owner_insert\" ON projects\n FOR INSERT WITH CHECK (auth.uid() = user_id);\n\n-- Users can only update their own rows\nCREATE POLICY \"owner_update\" ON projects\n FOR UPDATE USING (auth.uid() = user_id)\n WITH CHECK (auth.uid() = user_id);\n\n-- Users can only delete their own rows\nCREATE POLICY \"owner_delete\" ON projects\n FOR DELETE USING (auth.uid() = user_id);\n```\n\n### Service Role Bypass\n\nThe service role key bypasses RLS. Use it only in server-side code, never in client-side or frontend bundles.\n\n```typescript\n// Server-side only (API routes, edge functions)\nconst supabase = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!);\n\n// Client-side (respects RLS)\nconst supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!);\n```\n\n## Migration Workflow\n\nAlways pair every migration with a rollback:\n\n```bash\n# Create migration\nsupabase migration new add_projects_table\n\n# Edit the generated file\n# supabase/migrations/20260512_add_projects_table.sql\n```\n\n```sql\n-- Migration\nCREATE TABLE projects (\n id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,\n name TEXT NOT NULL,\n status TEXT NOT NULL DEFAULT 'active',\n created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n);\n\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\nCREATE POLICY \"owner_all\" ON projects\n USING (auth.uid() = user_id)\n WITH CHECK (auth.uid() = user_id);\n\nCREATE INDEX projects_user_id_idx ON projects(user_id);\n```\n\n```sql\n-- Rollback (always create this before applying migration)\nDROP TABLE IF EXISTS projects CASCADE;\n```\n\n```bash\n# Apply locally\nsupabase db push\n\n# Apply to remote\nsupabase db push --linked\n```\n\n## TypeScript Types Generation\n\n```bash\nsupabase gen types typescript --project-id gmgxxiqgshbbgzhqzngq > src/types/database.ts\n```\n\nUse the generated types throughout the app:\n\n```typescript\nimport { Database } from \"@/types/database\";\n\ntype Project = Database[\"public\"][\"Tables\"][\"projects\"][\"Row\"];\ntype ProjectInsert = Database[\"public\"][\"Tables\"][\"projects\"][\"Insert\"];\n```\n\n## Common Queries\n\n```typescript\nconst supabase = createClient(url, anonKey);\n\n// Select with filter\nconst { data, error } = await supabase\n .from(\"projects\")\n .select(\"id, name, status, created_at\")\n .eq(\"status\", \"active\")\n .order(\"created_at\", { ascending: false });\n\n// Insert\nconst { data, error } = await supabase\n .from(\"projects\")\n .insert({ name: \"New Project\", user_id: userId })\n .select()\n .single();\n\n// Update\nconst { error } = await supabase\n .from(\"projects\")\n .update({ status: \"archived\" })\n .eq(\"id\", projectId);\n\n// Delete\nconst { error } = await supabase\n .from(\"projects\")\n .delete()\n .eq(\"id\", projectId);\n```\n\n## Storage\n\n```typescript\n// Upload file\nconst { data, error } = await supabase.storage\n .from(\"avatars\")\n .upload(`${userId}/avatar.png`, file, { upsert: true });\n\n// Get public URL\nconst { data } = supabase.storage\n .from(\"avatars\")\n .getPublicUrl(`${userId}/avatar.png`);\n\n// Download\nconst { data, error } = await supabase.storage\n .from(\"private-docs\")\n .download(`${userId}/report.pdf`);\n```\n\nStorage bucket policies use the same RLS syntax as tables.\n\n## Edge Functions\n\n```typescript\n// supabase/functions/send-email/index.ts\nimport { serve } from \"https://deno.land/std@0.168.0/http/server.ts\";\n\nserve(async (req) => {\n const { email, subject, body } = await req.json();\n\n // Call external service\n const res = await fetch(\"https://api.resend.com/emails\", {\n method: \"POST\",\n headers: {\n Authorization: `Bearer ${Deno.env.get(\"RESEND_API_KEY\")}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({ from: \"noreply@example.com\", to: email, subject, html: body }),\n });\n\n return new Response(JSON.stringify({ success: res.ok }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n});\n```\n\n```bash\n# Deploy\nsupabase functions deploy send-email\n\n# Invoke\nsupabase functions invoke send-email --body '{\"email\":\"user@example.com\"}'\n```\n\n## Backup Strategy\n\nFree tier: no automated backups (tables can be exported manually).\nPro tier ($25/mo): daily automated backups + point-in-time recovery.\n\n```bash\n# Manual export (any tier)\npg_dump \"postgresql://postgres:[password]@db.gmgxxiqgshbbgzhqzngq.supabase.co:5432/postgres\" \\\n --schema=public \\\n --data-only \\\n > backup_$(date +%Y%m%d).sql\n\n# Restore\npsql \"postgresql://...\" < backup_20260512.sql\n```\n\n## Common Pitfalls\n\n| Pitfall | Fix |\n|---------|-----|\n| RLS not enabled | `ALTER TABLE t ENABLE ROW LEVEL SECURITY;` after every `CREATE TABLE` |\n| Service key in client code | Move to server-side only; use anon key on client |\n| Missing rollback migration | Always write rollback.sql before applying |\n| Unbounded `.collect()` equivalent | Add `.limit(n)` to all queries |\n| `.filter()` without index | Add index on filtered columns |\n| Auth user not in context | Use `auth.uid()` in policies, not `current_user` |\n","html":"

Overview

\n

Supabase wraps PostgreSQL with auth, storage, realtime, and edge functions. The primary gotcha in production: RLS is disabled by default on new tables. Always enable it.

\n

Project Reference

\n
Project ref: gmgxxiqgshbbgzhqzngq\nSupabase URL: https://gmgxxiqgshbbgzhqzngq.supabase.co\n
\n

RLS: Row Level Security

\n

Enable on every table. Without RLS, any authenticated user can read/write all rows.

\n
-- Enable RLS\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\n-- Users can only see their own rows\nCREATE POLICY \"owner_select\" ON projects\n  FOR SELECT USING (auth.uid() = user_id);\n\n-- Users can only insert their own rows\nCREATE POLICY \"owner_insert\" ON projects\n  FOR INSERT WITH CHECK (auth.uid() = user_id);\n\n-- Users can only update their own rows\nCREATE POLICY \"owner_update\" ON projects\n  FOR UPDATE USING (auth.uid() = user_id)\n  WITH CHECK (auth.uid() = user_id);\n\n-- Users can only delete their own rows\nCREATE POLICY \"owner_delete\" ON projects\n  FOR DELETE USING (auth.uid() = user_id);\n
\n

Service Role Bypass

\n

The service role key bypasses RLS. Use it only in server-side code, never in client-side or frontend bundles.

\n
// Server-side only (API routes, edge functions)\nconst supabase = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!);\n\n// Client-side (respects RLS)\nconst supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!);\n
\n

Migration Workflow

\n

Always pair every migration with a rollback:

\n
# Create migration\nsupabase migration new add_projects_table\n\n# Edit the generated file\n# supabase/migrations/20260512_add_projects_table.sql\n
\n
-- Migration\nCREATE TABLE projects (\n  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,\n  name TEXT NOT NULL,\n  status TEXT NOT NULL DEFAULT 'active',\n  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()\n);\n\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\nCREATE POLICY \"owner_all\" ON projects\n  USING (auth.uid() = user_id)\n  WITH CHECK (auth.uid() = user_id);\n\nCREATE INDEX projects_user_id_idx ON projects(user_id);\n
\n
-- Rollback (always create this before applying migration)\nDROP TABLE IF EXISTS projects CASCADE;\n
\n
# Apply locally\nsupabase db push\n\n# Apply to remote\nsupabase db push --linked\n
\n

TypeScript Types Generation

\n
supabase gen types typescript --project-id gmgxxiqgshbbgzhqzngq > src/types/database.ts\n
\n

Use the generated types throughout the app:

\n
import { Database } from \"@/types/database\";\n\ntype Project = Database[\"public\"][\"Tables\"][\"projects\"][\"Row\"];\ntype ProjectInsert = Database[\"public\"][\"Tables\"][\"projects\"][\"Insert\"];\n
\n

Common Queries

\n
const supabase = createClient(url, anonKey);\n\n// Select with filter\nconst { data, error } = await supabase\n  .from(\"projects\")\n  .select(\"id, name, status, created_at\")\n  .eq(\"status\", \"active\")\n  .order(\"created_at\", { ascending: false });\n\n// Insert\nconst { data, error } = await supabase\n  .from(\"projects\")\n  .insert({ name: \"New Project\", user_id: userId })\n  .select()\n  .single();\n\n// Update\nconst { error } = await supabase\n  .from(\"projects\")\n  .update({ status: \"archived\" })\n  .eq(\"id\", projectId);\n\n// Delete\nconst { error } = await supabase\n  .from(\"projects\")\n  .delete()\n  .eq(\"id\", projectId);\n
\n

Storage

\n
// Upload file\nconst { data, error } = await supabase.storage\n  .from(\"avatars\")\n  .upload(`${userId}/avatar.png`, file, { upsert: true });\n\n// Get public URL\nconst { data } = supabase.storage\n  .from(\"avatars\")\n  .getPublicUrl(`${userId}/avatar.png`);\n\n// Download\nconst { data, error } = await supabase.storage\n  .from(\"private-docs\")\n  .download(`${userId}/report.pdf`);\n
\n

Storage bucket policies use the same RLS syntax as tables.

\n

Edge Functions

\n
// supabase/functions/send-email/index.ts\nimport { serve } from \"https://deno.land/std@0.168.0/http/server.ts\";\n\nserve(async (req) => {\n  const { email, subject, body } = await req.json();\n\n  // Call external service\n  const res = await fetch(\"https://api.resend.com/emails\", {\n    method: \"POST\",\n    headers: {\n      Authorization: `Bearer ${Deno.env.get(\"RESEND_API_KEY\")}`,\n      \"Content-Type\": \"application/json\",\n    },\n    body: JSON.stringify({ from: \"noreply@example.com\", to: email, subject, html: body }),\n  });\n\n  return new Response(JSON.stringify({ success: res.ok }), {\n    headers: { \"Content-Type\": \"application/json\" },\n  });\n});\n
\n
# Deploy\nsupabase functions deploy send-email\n\n# Invoke\nsupabase functions invoke send-email --body '{\"email\":\"user@example.com\"}'\n
\n

Backup Strategy

\n

Free tier: no automated backups (tables can be exported manually).\nPro tier ($25/mo): daily automated backups + point-in-time recovery.

\n
# Manual export (any tier)\npg_dump \"postgresql://postgres:[password]@db.gmgxxiqgshbbgzhqzngq.supabase.co:5432/postgres\" \\\n  --schema=public \\\n  --data-only \\\n  > backup_$(date +%Y%m%d).sql\n\n# Restore\npsql \"postgresql://...\" < backup_20260512.sql\n
\n

Common Pitfalls

\n

| Pitfall | Fix |\n|---------|-----|\n| RLS not enabled | ALTER TABLE t ENABLE ROW LEVEL SECURITY; after every CREATE TABLE |\n| Service key in client code | Move to server-side only; use anon key on client |\n| Missing rollback migration | Always write rollback.sql before applying |\n| Unbounded .collect() equivalent | Add .limit(n) to all queries |\n| .filter() without index | Add index on filtered columns |\n| Auth user not in context | Use auth.uid() in policies, not current_user |

\n"}