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

Date: 2026-06-06

Status

Accepted — amended 2026-08-06 (see Amendments below; the rule of thumb still stands).

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).

Amendments

2026-08-06 — the user client is createServerSupabase(), and hours-table self-service writes no longer go through RLS.

1. createUserClient(accessToken) never got wired up. The App Router made the cookie-backed createServerSupabase() (src/db/clients/server.ts) the user client instead; user.ts sat unused with zero importers for two months while describing itself as the defense-in-depth path, which is worse than not existing — it reads as coverage that isn't there. Deleted. The two-client model is unchanged; only the constructor's name and its token source are.
2. Migrations 0102 (time_entries) and 0103 (time_segments) removed every write policy from both hours tables and revoked the write grants from anon/authenticated. So for those two tables the sentence "contractor self-service writes go through RLS-permitted policies" no longer holds: every write is a service-role server action, behind the locked / approved / invoiced / ended-assignment guards (ADR-0050 §3). The policies were never on a live path — nothing in the product ever posted hours under the contractor's own JWT — but they granted a capability only an attacker would use: a contractor could PATCH their own already-invoiced hours straight over PostgREST, and any staff admin could PATCH or DELETE anyone's, never meeting an app guard.

The rule of thumb survives both, with the write half doing more of the work than it did in 2026-06: RLS guards the read path; explicit role checks guard the write path. Leave rules on the tables that carry billed or paid money, and the read path is the only thing RLS should be asked to enforce.