Security / platform

Supabase SQL Architecture

RLS, storage, and auth for a single-repo storefront + admin

PostgreSQLSupabaseRLSStorageRealtime

A database design that lets anonymous customers, authenticated customers, and the admin all touch the same tables safely, with write paths gated at the database layer rather than scattered through route handlers.

Technical narrative

This is where the archive goes deeper than the homepage: architecture choices, operating constraints, and the decisions that make the product maintainable over time.

Three callers, one schema

Three principals reach the database. The anon key powers the public storefront and can only read storefront-eligible rows. The authenticated key (a signed-in customer) unlocks order history scoped to the caller. The service role key is used once, inside the checkout server action, to create an orders row even when the buyer is anonymous. Admin access is layered on top of the authenticated key via an admin_users membership check.

anon: read storefront_status='live' rows on products / variants / images / tags
authenticated: read own orders; read full product catalog
admin (via is_admin): all writes on products / variants / inventory / promotions / orders
service_role: reserved for checkout order creation; never exposed to the client

The is_admin() gate

A single SQL helper, public.is_admin(), is the one place the admin check lives at the database layer. Policies on every write-protected table reference it. If a new table arrives tomorrow, the pattern is one line.

sql

create or replace function public.is_admin()
returns boolean
language sql stable security definer
set search_path = public
as $$
  select exists (
    select 1 from admin_users
    where user_id = auth.uid()
  );
$$;

create policy "admin full access on products"
  on products
  for all
  using (public.is_admin())
  with check (public.is_admin());

Security definer + explicit search_path, the defensive posture Supabase recommends.

Anonymous reads, gated by column

sql

create policy "public reads storefront products"
  on products
  for select
  to anon, authenticated
  using (storefront_status = 'live');

Draft and archived rows never leave the database through the anon key.

The checkout service-role boundary

Because the shop accepts anonymous pickup orders, customers need to create an orders row without an authenticated session. Rather than poke an RLS hole for anon inserts, the checkout server action uses the service role key, never shipped to the client, to validate the cart server-side and insert the order. Every other write path refuses to accept the service role.

Storage and public images

Product images live in a product-images bucket. Public URLs are generated via getPublicUrl() and rendered through next/image with remotePatterns whitelisting the Supabase CDN. Uploads are restricted to admin; reads are public.

Realtime

Only one table opts into Realtime: orders. That is enough to power the admin fulfillment queue without introducing websocket traffic for the rest of the schema.

Back to the archive Work With Me