Skip to content
ClinicFlow Docs

ADR-002: Full-RLS Tenant Isolation with ES256 JWTs

ADR-002: Full-RLS Tenant Isolation with ES256 JWTs

Section titled “ADR-002: Full-RLS Tenant Isolation with ES256 JWTs”

Status: accepted
Date: 2026-06-10
Deciders: @ErnestNuma

Context

Section titled “Context”

ClinicFlow is a multi-tenant SaaS. Every piece of data is scoped to a clinic_id. Originally, tenant isolation relied on two mechanisms:

  1. Application-layer filtering — every DB query explicitly added .eq('clinic_id', req.user.clinicId). A tenantScopingGuard.test.js regression test caught missing filters.
  2. Supabase Row-Level Security (RLS) — basic policies existed but were inconsistently applied and used the legacy auth.uid() pattern (Supabase Auth user IDs), not the application’s own JWT claims.

This had several problems:

  • Two auth systems: The app used custom ES256 JWTs for the Express middleware chain, but Supabase RLS used auth.uid() which maps to Supabase Auth user IDs. These were different IDs — the same user had one ID in the app’s users table and a different ID in auth.users. RLS couldn’t correlate them.
  • Inconsistent enforcement: Some tables had RLS, some didn’t. A bug in the app layer could leak data with no DB-level backstop.
  • No column-level protection: Sensitive columns (patient DUI, WhatsApp tokens) were readable by any authenticated query even though the app layer masked them in responses.
  • Privilege creep: The supabaseAdmin (service-role) client was used in many more places than necessary because RLS couldn’t express the app’s auth model.

Decision

Section titled “Decision”

Implement full RLS coverage across all tables, keyed to the application’s own ES256 JWT claims rather than Supabase Auth.

Architecture

Section titled “Architecture”
  1. Register ES256 public key with Supabase as a custom JWKS endpoint. This lets Supabase validate the app’s JWTs and read clinic_id from the token claims, making app.clinic_id available in RLS policies via current_setting('app.clinic_id').
  2. Set app.clinic_id session variable on every database connection from the req-scoped Supabase client (the one used in route handlers). This is done automatically by the client factory.
  3. Write RLS policies on every table that reference current_setting('app.clinic_id') instead of auth.uid(). This means RLS enforces the same tenant boundary the app layer already uses.
  4. Column-level security for sensitive columns (dui, whatsapp_token) — only accessible when the session’s app.role is admin.
  5. Audit every supabaseAdmin call site — service-role usage should be the exception, not the default. Most admin usages were replaced with a tenant-scoped client.
  6. Drop legacy HS256 JWT support — only ES256 asymmetric keys remain.

Client access model after migration

Section titled “Client access model after migration”
Client class Key type RLS enforced? When used
Request-scoped (req.db) Publishable/anon + session vars Yes (full) All authenticated route handlers
Tenant-system Service role (with app.clinic_id set) Yes (via session) Background jobs, cron
Privileged (supabaseAdmin) Service role (no session) No Bootstrapping, migrations, superadmin bulk ops

Implementation

Section titled “Implementation”

The migration shipped as 20260610_critical_full_rls.sql — a single idempotent migration covering all tables, storage buckets, and RPC permissions. Every table’s operation class (authenticated vs. service-role) was documented in a full access inventory.

Consequences

Section titled “Consequences”

Positive:

  • Defense in depth: Tenant isolation at the DB layer even if app-layer filtering fails
  • Simplified auth model: One JWT format for both the Express middleware and the database — no more ID mismatch
  • Column-level protection: Sensitive data encrypted at rest (AES-256) AND access-controlled at the read level
  • Removed privilege creep: Fewer service-role calls = smaller blast radius per compromised endpoint
  • Auditable: The access inventory is a single source of truth for who can do what at the DB level

Negative:

  • Migration complexity: Every existing RLS policy had to be rewritten; tables without policies got them for the first time
  • Session variable overhead: Every DB connection now runs SELECT set_config('app.clinic_id', ...) — negligible latency but another moving part
  • ES256 key management: Private key must be kept secret and rotated; no recovery if lost

Risks:

  • A bug in the session-variable setting code would cause all queries to fail (fail-closed, not open — acceptable)
  • Supabase might change how custom JWKS endpoints work (monitor changelog)

Compliance

Section titled “Compliance”
  • tenantScopingGuard.test.js — automated regression test that verifies every route handler applies .eq('clinic_id', ...)
  • privilegedImportGuard.test.js — ensures supabaseAdmin isn’t imported where a tenant-scoped client should be used
  • RLS inventory doc maintained alongside schema changes
Section titled “Links”