How SiteLive Keeps Data Safe: Roles + Row Level Security on Supabase

The problem: one database, thousands of tenants

SiteLive is a multi-tenant platform. Thousands of independent business owners build and run websites, stores, review widgets, and lead inboxes on shared infrastructure backed by a single Supabase (Postgres) database.

The hard security question in any multi-tenant SaaS is simple to state and easy to get wrong: how do you guarantee that Customer A can never see Customer B's data?

Our answer has two layers: Row Level Security (RLS) in Postgres, where the database itself enforces "you can only read/write your own rows," and an explicit role and authorization model for the small number of privileged paths (admins, background jobs, public form submissions) that must operate above a single tenant.

The actors in the system

Rather than a sprawling permissions matrix, SiteLive has a small, well-defined set of actor types.

Roles are columns on the user profile table, not custom JWT claims. This keeps role changes instant and auditable.

-- Super admin flag
ALTER TABLE public.profiles ADD COLUMN is_super_admin boolean NOT NULL DEFAULT false;

-- Granular admin role: 'super_admin' | 'crm_staff' | NULL (normal user)
ALTER TABLE public.profiles ADD COLUMN admin_role text DEFAULT NULL;

The single source of truth for "who is this and what can they do" is a shared actor context:

type ActorContext = {
  actorUserId: string;       // the real logged-in user
  effectiveUserId: string;   // the user whose data we're acting on (differs when impersonating)
  isSuperAdmin: boolean;
  isAdmin: boolean;          // super_admin OR crm_staff
  canDelete: boolean;        // super_admin only
  impersonatingUserId: string | null;
};

Layer 1: Row Level Security does the heavy lifting

Every tenant-owned table has RLS enabled, and the policies bind data to the logged-in user via auth.uid(). For directly-owned tables it's a straight comparison; for child tables it's a subquery back to the owned sites.

-- A user can only see their own (non-deleted) sites
CREATE POLICY "Users can view their own active sites"
  ON sites FOR SELECT TO authenticated
  USING (auth.uid() = user_id AND deleted_at IS NULL);

-- Lead/enquiry submissions are visible only to the owner of the site they belong to
CREATE POLICY "Site owners can read submissions"
  ON public.form_submissions FOR SELECT
  USING (site_id IN (SELECT id FROM public.sites WHERE user_id = auth.uid()));

-- Store products are managed only by the site owner
CREATE POLICY "Site owners can manage products"
  ON public.store_products FOR ALL
  USING (site_id IN (SELECT id FROM public.sites WHERE user_id = auth.uid()));

The important property: even if we made a mistake in application code and forgot a WHERE user_id = ... filter, Postgres would still refuse to return another tenant's rows. Security doesn't depend on us being perfect in every query.

A few deliberate patterns fall out of this:

• Public reads of published data only — for example, blog posts are world-readable only when their status is "published."

• No public write policies — tables like form submissions, store orders, and site reviews have no insert policy for anonymous users. All writes go through validated server routes.

• Server-only tables — sensitive internal tables (admin sessions, CRM activity timelines, audit logs) have RLS enabled with no user policies at all, so only the service role can touch them.

Layer 2: two connection types, on purpose

SiteLive uses two kinds of Supabase client, and the distinction is the crux of the model.

The service-role client is powerful, since it ignores RLS, so it is never shipped to the browser and is only used on the server.

/**
 * Admin Supabase client using the service role key.
 * This bypasses RLS — never expose to the client.
 */
export function createAdminClient() {
  return createClient<Database>(url, serviceKey, { auth: { persistSession: false } });
}

Because the admin client bypasses RLS, any route that uses it re-applies the ownership check in application code. That's the defense-in-depth seam:

// Even with the admin client, non-super-admins are still scoped to their own sites
let siteQuery = admin.from("sites").select("id, user_id").eq("id", siteId);
if (!actor.isSuperAdmin) siteQuery = siteQuery.eq("user_id", actor.effectiveUserId);

Impersonation: actor vs. effective user

When support needs to "see what the customer sees," a super admin can impersonate a user. The design keeps two identities separate: the actor, who is the real admin (their session never changes, so audit logs always show who actually did something), and the effective user, who is the customer whose data is in scope.

Starting impersonation sets a short-lived, signed, httpOnly cookie (capped at a few hours), and it's only honored if the requester is genuinely a super admin and the token matches the logged-in user. Because RLS still keys on the admin's own identity, impersonated routes switch to the admin client and scope data by the effective user instead. Sensitive side effects are suppressed — for example, an admin's location never overwrites the customer's billing region during impersonation.

The two CRMs (and why "lead" means two things)

SiteLive has two distinct CRMs, and they sit on opposite sides of the security boundary.

The site-owner leads CRM covers the enquiries a customer's website visitors submit. It's stored with pipeline columns (new, contacted, trial booked, converted, lost), and it's secured by RLS: an owner sees only leads for sites they own.

The platform CRM covers platform signups our internal team triages and pitches. It's stored on the profile table plus an activity timeline, secured by application-layer role checks, and the activity timeline is service-role-only:

CREATE POLICY "Service role full access on lead_activity"
  ON public.lead_activity FOR ALL
  USING (auth.role() = 'service_role') WITH CHECK (auth.role() = 'service_role');

CRM staff can work the platform CRM but get a read-only admin panel and cannot perform destructive actions — those require super-admin-only delete permissions.

The public surface: generated sites hold no secrets

Every website SiteLive generates is a static export. It has no database credentials. When a visitor submits a contact form, places an order, or reads reviews, the site calls a small set of public API routes that are hardened without any logged-in user:

• Site scoping — the site ID is validated on every request.

• Feature/existence checks — for example, the store must be enabled before an order is accepted.

• Service role plus server-side business rules — order line items and totals are always recomputed on the server from current prices; client-sent amounts are ignored.

• Rate limiting — per-IP windows on forms, reviews, and orders.

• Payload caps and honeypots — small size limits and hidden fields to shed bot traffic.

• PII minimization — the public reviews API never returns reviewer emails or IP addresses.

Defense in depth, everywhere else

Beyond RLS and role checks, the platform layers additional protections: upload sanitization with magic-byte verification and script-stripping so a malicious file can't smuggle executable content; hardened serving with strict content-type and framing headers on served assets; signed webhook verification for payment and messaging providers before anything is processed; a billing region lock that pins a user's currency and gateway to prevent price manipulation; and an audit trail with soft deletes, so actions are logged with an actor and deletes are reversible.

Takeaway

The core idea is layered trust. Postgres RLS is the floor — the database refuses cross-tenant access even if the app is buggy. The role model is the ceiling — a small, explicit set of actors with escalating capability. The service role is the pressure valve for admins, jobs, and public traffic, always paired with an application-layer check so bypassing RLS never means bypassing authorization.

That combination lets SiteLive move fast on features while keeping each customer's data provably isolated.