Supabase Backend

Supabase Backend

Supabase is "Firebase but Postgres." Best fit: small teams, prototype-to-prod path, want auth+DB+storage+realtime in one bill. Trade-off: row-level security is your security model — get it wrong and your whole DB is exposed.

When to use

• New app, want managed Postgres + auth + storage + realtime in one provider.
• Frontend-heavy apps (Next.js, Expo) where direct DB access from the client is desirable.
• Replacing Firebase for relational use cases.

When NOT to use

• You need fine-grained server-side control of every query → use plain Postgres + your own API.
• Stateless services on Cloudflare/Vercel only — Supabase is overkill.
• Latency-sensitive at scale — Supabase RPC adds ~50ms over direct Postgres.

The mental model: RLS is your security

In Supabase, your client (browser/mobile) gets a JWT and queries Postgres directly via PostgREST. The only thing protecting your data is row-level security. No RLS = full DB exposed to anyone with your anon key (which is in your client bundle).

-- Always: enable RLS on every table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Then: explicit policies for each operation
CREATE POLICY "users see own projects" ON projects
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "users insert own projects" ON projects
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "users update own projects" ON projects
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "users delete own projects" ON projects
  FOR DELETE USING (auth.uid() = user_id);

auth.uid() is a built-in function returning the user's UUID from the JWT. USING clause filters which rows the user sees. WITH CHECK clause validates which rows they can insert/update.

Schema rule: every business table has user_id (or org_id)

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,
  created_at  timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

auth.users is Supabase's managed users table. Don't fight it; reference it.

Client setup (Next.js)

// src/lib/supabase/server.ts
import { createServerClient } from "@supabase/ssr";
import { cookies } from "next/headers";

export async function getSupabase() {
  const cookieStore = await cookies();
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll: () => cookieStore.getAll(),
        setAll: (toSet) => toSet.forEach(({ name, value, options }) => cookieStore.set(name, value, options)),
      },
    }
  );
}

// src/lib/supabase/client.ts (for client components)
import { createBrowserClient } from "@supabase/ssr";

export const supabase = createBrowserClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
);

The anon key is fine to ship — RLS is what gates access. The service_role key is the dangerous one (bypasses RLS); never ship it.

Migrations: use the CLI, never the dashboard

# Local dev
supabase start                           # spins up Docker Postgres
supabase migration new add_projects      # creates supabase/migrations/_add_projects.sql
# Edit the SQL
supabase db reset                        # apply locally
supabase db push                         # apply to remote linked project

Schema changes via the dashboard SQL editor are not in migrations and won't replay on a fresh dev DB. Hard rule: any DDL goes through supabase migration new.

Auth helpers

// Sign up (emails OTP / verification link via configured SMTP)
const { data, error } = await supabase.auth.signUp({ email, password });

// Sign in
await supabase.auth.signInWithPassword({ email, password });

// Magic link (passwordless)
await supabase.auth.signInWithOtp({ email });

// Get current user (server)
const { data: { user } } = await supabase.auth.getUser();

Configure SMTP in dashboard → Auth → Email. Default uses Supabase's relay which is rate-limited and from noreply@mail.app.supabase.io — switch to Brevo/Resend/SES before prod.

Realtime

Subscribe to row-level changes:

supabase
  .channel("projects")
  .on("postgres_changes", { event: "*", schema: "public", table: "projects", filter: `user_id=eq.${userId}` }, (payload) => {
    console.log("change:", payload);
  })
  .subscribe();

Realtime respects RLS — users only get events for rows they can read. Convenient and secure.

Storage

const { data, error } = await supabase.storage.from("avatars").upload(`${userId}/profile.png`, file);
const { data: { publicUrl } } = supabase.storage.from("avatars").getPublicUrl(`${userId}/profile.png`);

Bucket policies use the same RLS-style:

CREATE POLICY "users upload to own folder" ON storage.objects
  FOR INSERT WITH CHECK (bucket_id = 'avatars' AND (storage.foldername(name))[1] = auth.uid()::text);

Edge Functions (Deno) for server-only logic

// supabase/functions/charge/index.ts
import { serve } from "https://deno.land/std/http/server.ts";

serve(async (req) => {
  const { customerId, amount } = await req.json();
  // Use service_role here for admin actions
  // ...
  return new Response(JSON.stringify({ ok: true }));
});

Edge Functions are where you put webhooks and admin operations that need to bypass RLS. They run on Deno + Cloudflare Workers infra; cold start ~50ms.

Anti-patterns

• Tables without ENABLE ROW LEVEL SECURITY — exposed to anyone with anon key. Add to migrations day 1.
• Forgetting WITH CHECK on INSERT/UPDATE policies — users can write rows they couldn't read. Asymmetric data leak.
• Servicerole key in frontend — full DB bypass. Ship anon key only; servicerole lives in edge functions / your backend.
• Schema changes via dashboard SQL editor — not in migrations, drift between dev and prod.
• Default Supabase email sender in prod — rate limits hit users; emails land in spam. Configure SMTP before launch.
• Not handling auth.users cascade — referenced from many tables; if you skip ON DELETE CASCADE, deletions break.
• Storing PII without RLS — Supabase's Postgres is just Postgres; same compliance posture (GDPR/DPDP) applies.
• No local Postgres for dev — supabase start is one command. Dev-against-prod is how data gets corrupted.
• Realtime channel without filter — fires for every row in the table, network and RLS overhead.
• select * in production queries — every PostgREST endpoint accepts column selection. Be explicit; reduces bandwidth and accidental column leaks.

Verify it worked

• [ ] Every table in public schema has RLS enabled (SELECT relname FROM pg_class WHERE relrowsecurity = false AND relnamespace = 'public'::regnamespace returns 0 user-tables).
• [ ] User A cannot read User B's projects (manual test with two test accounts).
• [ ] WITH CHECK clauses present on all INSERT/UPDATE policies.
• [ ] Service_role key is in .env.local only, not committed; not loaded in browser bundle.
• [ ] All schema changes are in supabase/migrations/; no dashboard-only DDL.
• [ ] supabase db reset rebuilds DB cleanly from migrations.
• [ ] Production SMTP configured; magic-link email arrives within 30s, lands in inbox.
• [ ] Realtime channel only fires for rows the user can read (manual test).
• [ ] Storage bucket has folder-isolation policy; user can't write into another user's folder.
• [ ] Edge function works with service_role for admin actions; doesn't expose service key to client.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: MaheshAwasare
• Source: MaheshAwasare/claude-skills-pro
• License: MIT

Install and usage instructions live in the source repository linked above.