Skip to main content

4. Two-client model: RLS for reads, service role for privileged writes

Date: 2026-06-06

Status​

Accepted

Context​

The app handles PII and money across three audiences (admins, contractors, facility contacts) and is multi-tenant-ready (company_id on every table). We need defense in depth: a bug in application logic must not be able to leak one contractor's or one company's data to another. Supabase offers Row Level Security (enforced in Postgres) and a service-role key (which bypasses RLS).

Decision​

Exactly two ways to construct a Supabase client, both in src/db/clients/:

  1. createUserClient(accessToken) — anon key + the user's JWT, so all RLS policies apply. Used for reads in Server Components and user-scoped queries. This is the safety net.
  2. createServiceClient() — service-role key, bypasses RLS. import 'server-only' makes a client-bundle import a build error. Used only in server actions, webhook handlers, and cron handlers, and only after the action independently verifies the caller's identity and role (or checks the request HMAC / shared secret).

RLS policies (migration 0003) scope every table by company_id and role via SECURITY DEFINER helper functions in a private app schema (app.is_admin(), app.is_owner(), app.current_company_id(), app.current_contractor_id()) — placed in app so they are not exposed through the PostgREST API. Contractor self-service writes (logging hours, leave requests) go through RLS-permitted policies; all admin/owner writes go through the service client behind explicit role checks. Audit logging is enforced by database triggers, independent of either client.

Rule of thumb: RLS guards the read path; explicit role checks guard the write path. Never rely on only one.

Consequences​

  • Cross-tenant / cross-role reads are blocked by Postgres even if app code is wrong.
  • The service client is powerful and quarantined to server-only modules behind verification; it must never be imported into client components.
  • Some access rules that can't be a single-table policy (contractor → invoices) are expressed as a contractor self-select policy on line items plus an EXISTS policy on invoices (migration 0005), avoiding a SECURITY DEFINER view.
  • Integration tests must assert RLS per role/tenant against a real Postgres (planned in the testing phase); the security advisor is run after every schema change (currently clean).