Supabase fundamentals explained

What Supabase is (and is not)

Supabase is PostgreSQL plus platform services, not a proprietary document database. Your data lives in standard Postgres tables you can query with SQL, export with pg_dump, and migrate to any host. On top of that core, Supabase adds:

• Auth (GoTrue) — email/password, magic links, phone OTP, and OAuth providers; issues JWTs consumed by Postgres RLS.
• Auto-generated REST and GraphQL APIs (PostgREST) — CRUD over tables and views with filtering, pagination, and nested selects.
• Realtime — Postgres logical replication broadcast to WebSocket clients on table or row filters.
• Storage — S3-backed buckets with RLS-style policies on object paths.
• Edge Functions — Deno functions for webhooks, payment callbacks, and logic that must not run in the browser.

It is not a replacement for a full application server when you need complex orchestration, long-running jobs, or heavy compute. Reach for FastAPI or Next.js API routes when business logic spans dozens of tables, external APIs, and sagas. Supabase shines when CRUD, auth, and realtime are 80% of the backend and RLS can enforce the rest.

Core concepts

• Project — one Postgres database, API URL, anon key, and service role key.
• Anon key — safe to embed in frontend; RLS must block unauthorized reads/writes.
• Service role key — bypasses RLS; server-only, never commit to git or ship to clients.
• RLS policy — SQL predicate evaluated per row; auth.uid() maps JWT sub to your users table.
• Migration — versioned SQL in supabase/migrations/; applied via CLI or dashboard.

Project setup and the JavaScript client

Install the CLI and initialize a local project for reproducible schema changes:

npm install -g supabase
supabase init
supabase start          # local Postgres + full stack via Docker
supabase db diff -f init_schema   # capture dashboard edits as migration

In your frontend, create a singleton client with the project URL and anon key from the dashboard Settings > API page:

import { createClient } from '@supabase/supabase-js'

export const supabase = createClient(
  import.meta.env.VITE_SUPABASE_URL,
  import.meta.env.VITE_SUPABASE_ANON_KEY
)

Basic queries mirror PostgREST syntax — filters, ordering, and joins via foreign key hints:

const { data, error } = await supabase
  .from('orders')
  .select('id, total, line_items(quantity, product_id)')
  .eq('user_id', userId)
  .order('created_at', { ascending: false })
  .limit(20)

Enable RLS on every public table before shipping. A table without RLS but exposed through the anon key is world-readable. The dashboard warns when RLS is disabled; treat those warnings as release blockers.

Row-level security: authorization in SQL

RLS is Supabase's most important production concept. Instead of filtering WHERE user_id = $1 in application code, you attach policies to the table and Postgres enforces them for every query — including ones from the auto-generated API.

alter table orders enable row level security;

create policy "Users read own orders"
  on orders for select
  using (auth.uid() = user_id);

create policy "Users insert own orders"
  on orders for insert
  with check (auth.uid() = user_id);

auth.uid() returns the UUID from the JWT when the request carries a valid session. For team or organization models, store org_id on rows and check membership in a org_members table inside the policy. Use security definer functions sparingly to centralize complex checks, but audit them — they run with elevated privileges.

Policy patterns worth knowing

• Public read, authenticated write — for select using (true) plus insert policies with auth.role() = 'authenticated'.
• Role-based access — store role in auth.users raw metadata or a profiles table; reference in policies.
• Service role bypass — backend jobs use the service key; never expose that path to browsers.
• Storage policies — mirror table rules on storage.objects with path prefixes like user/{uid}/*.

Test policies with the SQL editor's "Run as user" impersonation and with integration tests that call the REST API using real JWTs, not only by reading policy SQL.

Authentication flows

Supabase Auth implements standard OAuth 2.0 and OIDC flows. Email signup stores users in auth.users; your app typically mirrors profile fields in a public profiles table via a trigger on insert.

const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: { redirectTo: 'https://app.example.com/auth/callback' }
})

// PKCE session after redirect
const { data: { session } } = await supabase.auth.getSession()

Sessions refresh automatically in the client. For server-rendered apps, use the @supabase/ssr helpers to pass cookies between server components and the browser. Configure email templates, redirect allow-lists, and JWT expiry in the dashboard Auth settings. Short-lived JWTs (default one hour) limit exposure; refresh tokens stay in httpOnly cookies when using SSR helpers correctly.

Map external identities to application users with a profiles table keyed by id uuid references auth.users primary key. Never duplicate password hashes in public schemas; let GoTrue own credential storage.

Storage, realtime, and edge functions

Storage

Buckets hold avatars, PDFs, and exports. Upload from the client when policies allow, or generate signed URLs for time-limited downloads. Set allowedMimeTypes and max file size on buckets to reduce abuse.

await supabase.storage.from('avatars').upload(
  `${userId}/profile.png`,
  file,
  { upsert: true, contentType: 'image/png' }
)

Realtime

Subscribe to postgres_changes on a table filter so dashboards update when rows change — no custom WebSocket server required:

supabase.channel('orders')
  .on('postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'orders',
      filter: `user_id=eq.${userId}` },
    payload => refreshCart(payload.new)
  )
  .subscribe()

Realtime adds load on replication slots; scope filters tightly and unsubscribe on unmount. For high-volume feeds, consider polling or a dedicated stream processor like Kafka.

Edge functions

Deploy TypeScript/Deno handlers for Stripe webhooks, Slack notifications, or secrets that cannot live in the browser. Invoke with supabase.functions.invoke() and validate the caller's JWT inside the function when the endpoint is not fully public.

Harbor Commerce: storefront worked example

Harbor Commerce rebuilt a small B2B catalog app that previously ran a custom Node API over Postgres. The team migrated schema with supabase db pull, enabled RLS on products, carts, and orders, and deleted 2,400 lines of Express routes that only proxied CRUD.

Their policy model:

• products — public select where published = true; writes restricted to service role via edge function after admin JWT check.
• carts — full CRUD where user_id = auth.uid().
• orders — buyers select own rows; insert via edge function after Stripe webhook confirms payment (service role insert).

Realtime on order_status updates let buyers see fulfillment progress without refresh. Product images moved to a catalog storage bucket with CDN caching. The migration took two weeks: one for schema and RLS tests, one for swapping the React data layer from REST to @supabase/supabase-js. The highest-leverage lesson was writing RLS policies before connecting the frontend — policies are harder to retrofit once clients depend on over-broad access.

BaaS decision table

Common pitfalls

• Shipping with RLS disabled — the anon key is public; every table without RLS is a data breach waiting to happen.
• Service role key in frontend env vars — Vite and Next.js bundle NEXT_PUBLIC_*; use server-only secrets for admin paths.
• Overusing security definer functions — one bug bypasses all policies; prefer simple policies on each table.
• N+1 queries via nested selects — PostgREST embeds are convenient but can explode row counts; paginate and profile slow queries.
• Ignoring connection limits — serverless functions opening fresh pools exhaust Postgres connections; use Supavisor pooler URLs.
• Realtime on hot tables — broadcasting every row change on a high-write table can overwhelm clients and replication.
• Skipping migration discipline — dashboard-only schema edits drift from git; always capture SQL in versioned migrations.

Production checklist

• Enable RLS on every table in the public schema exposed to the API.
• Write and test select, insert, update, and delete policies per role.
• Store anon key in frontend env; service role only in edge functions or CI secrets.
• Configure Auth redirect URLs and email rate limits before launch.
• Use supabase/migrations/ for all schema changes; review in pull requests.
• Point serverless workloads at the connection pooler, not the direct Postgres port.
• Enable daily backups and test a restore on a staging project quarterly.
• Add database indexes for columns used in RLS predicates and frequent filters.
• Monitor slow queries via pg_stat_statements and the dashboard Reports tab.
• Document which operations require edge functions vs client-side Supabase calls.

Key takeaways

• Supabase is managed PostgreSQL with auth, storage, realtime, and edge functions — not a walled garden.
• RLS is your authorization layer; the anon key is safe only when policies are correct.
• JWT claims flow from Auth into Postgres via auth.uid() and custom claims.
• Edge functions hold secrets and webhooks; keep complex orchestration out of the browser.
• When you outgrow client-side queries, pair Supabase Postgres with Prisma or a dedicated API service.

Related reading

• PostgreSQL fundamentals explained — the database under Supabase
• Prisma fundamentals explained — typed ORM when you add a custom API layer
• OAuth 2.0 and OpenID Connect explained — standards behind social login
• Next.js fundamentals explained — SSR auth patterns with Supabase