Supabase RLS, Migrations, Backup, and Production Patterns

Overview

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.

Project Reference

Project ref: gmgxxiqgshbbgzhqzngq
Supabase URL: https://gmgxxiqgshbbgzhqzngq.supabase.co

RLS: Row Level Security

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

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

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

-- Users can only insert their own rows
CREATE POLICY "owner_insert" ON projects
  FOR INSERT WITH CHECK (auth.uid() = user_id);

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

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

Service Role Bypass

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

// Server-side only (API routes, edge functions)
const supabase = createClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!);

// Client-side (respects RLS)
const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!);

Migration Workflow

Always pair every migration with a rollback:

# Create migration
supabase migration new add_projects_table

# Edit the generated file
# supabase/migrations/20260512_add_projects_table.sql

-- Migration
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "owner_all" ON projects
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE INDEX projects_user_id_idx ON projects(user_id);

-- Rollback (always create this before applying migration)
DROP TABLE IF EXISTS projects CASCADE;

# Apply locally
supabase db push

# Apply to remote
supabase db push --linked

TypeScript Types Generation

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

Use the generated types throughout the app:

import { Database } from "@/types/database";

type Project = Database["public"]["Tables"]["projects"]["Row"];
type ProjectInsert = Database["public"]["Tables"]["projects"]["Insert"];

Common Queries

const supabase = createClient(url, anonKey);

// Select with filter
const { data, error } = await supabase
  .from("projects")
  .select("id, name, status, created_at")
  .eq("status", "active")
  .order("created_at", { ascending: false });

// Insert
const { data, error } = await supabase
  .from("projects")
  .insert({ name: "New Project", user_id: userId })
  .select()
  .single();

// Update
const { error } = await supabase
  .from("projects")
  .update({ status: "archived" })
  .eq("id", projectId);

// Delete
const { error } = await supabase
  .from("projects")
  .delete()
  .eq("id", projectId);

Storage

// Upload file
const { data, error } = await supabase.storage
  .from("avatars")
  .upload(`${userId}/avatar.png`, file, { upsert: true });

// Get public URL
const { data } = supabase.storage
  .from("avatars")
  .getPublicUrl(`${userId}/avatar.png`);

// Download
const { data, error } = await supabase.storage
  .from("private-docs")
  .download(`${userId}/report.pdf`);

Storage bucket policies use the same RLS syntax as tables.

Edge Functions

// supabase/functions/send-email/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";

serve(async (req) => {
  const { email, subject, body } = await req.json();

  // Call external service
  const res = await fetch("https://api.resend.com/emails", {
    method: "POST",
    headers: {
      Authorization: `Bearer ${Deno.env.get("RESEND_API_KEY")}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ from: "noreply@example.com", to: email, subject, html: body }),
  });

  return new Response(JSON.stringify({ success: res.ok }), {
    headers: { "Content-Type": "application/json" },
  });
});

# Deploy
supabase functions deploy send-email

# Invoke
supabase functions invoke send-email --body '{"email":"user@example.com"}'

Backup Strategy

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

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

# Restore
psql "postgresql://..." < backup_20260512.sql

Common Pitfalls

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