Skip to content

ADR-0001: Multi-tenancy + Owner RLS Bypass

  • Status: Accepted
  • Date: 2025-01-10
  • Owners: Engineering
  • Related docs:
    • Runbook: runbooks/owner-dashboard-setup.md
    • Supabase overview: architecture/supabase.md

Context

This project is multi-tenant: multiple companies/tenants share the same Supabase Postgres database.

We need: - Strong tenant isolation so normal users can only read/write data belonging to their tenant. - A privileged “owner” role that can view/manage data across tenants (e.g., an internal dashboard). - A clear, consistent pattern for how Row Level Security (RLS) is applied across tables.

Constraints: - The app relies on Supabase Auth for user identity. - RLS should be the primary enforcement mechanism at the database layer. - We need a safe way to perform “admin/owner” operations without leaking privileged keys to the client.

Decision

We implement multi-tenancy using:

1) A tenant identifier (e.g., company_id / tenant_id) present on: - the user profile (so we know which tenant a user belongs to) - all tenant-scoped tables (so policies can filter by tenant)

2) A user profile table that stores: - user_id (ties to Supabase Auth user) - tenant_id (company/tenant membership) - role (e.g., tenant_user, admin, owner)

3) RLS policies that enforce: - Tenant-scoped users can access rows only where row.tenant_id = current_user_tenant_id() - Owners can access rows across tenants using an explicit bypass rule

4) A helper SQL function (example name): - get_tenant_id() or current_user_tenant_id()

Behavior: - Returns the tenant/company id for normal tenant users - Returns NULL (or a special value) for owner to indicate privileged access - Policies use this to allow owner access without duplicating logic everywhere

5) Owner privileges are server-side only - Privileged operations (cross-tenant, backfills, Stripe reconciliation)