Tenancy and RLS

OpsDNA’s multi-tenancy rests on two deliberately independent walls: selection (which tenant does this request act in?) and enforcement (may this request touch these rows?). The walls are independent because application code can be wrong: selection is a convenience that narrows to exactly one tenant, while enforcement is tenant_id row-level security in Postgres itself — so a bug in selection can never leak another tenant’s rows. This page explains both walls, the grant model that drives them, and the runtime resolution flow. Normative sources: DATA-MODEL.md rule 7, ADR-0023 (decoupled tenancy), and ADR-0033 (docs/adrs/0033-tenant-resolution-and-multi-tenant-selection-security-model.md).

The policy layer comes from ADR-0023, which decoupled three things that used to be ~1:1: the WorkOS organization (optional managed SSO/SCIM), the entity.organization (collaboration/business identity), and the tenant (isolation). Access is the OpsDNA grant, not org membership: an external auditor or LP with no WorkOS org can hold an active permission_grant into a tenant and get in, and one WorkOS org can front many tenants (a fund admin’s SSO over its clients). The RLS perimeter never moves: isolation is always a real tenant_id, never app-level organization filtering — the “entrypoint-row” anti-pattern ADR-0015 forbids.

ADR-0033 then answered the runtime question: given an authenticated request, how does the data plane pick exactly one tenant — and what happens when the answer is none, or more than one?

Wall 2 first: FORCE RLS on tenant_id

In the verified catalog snapshot (DATA-MODEL.md v3.0), 346 of 354 tables carry both RLS and FORCE RLS keyed on tenant_id alone; the eight exceptions are deliberate platform-global surfaces (e.g. reference.organization, public.opsdna_migrations, the approval catalog tables). Application code reaches tenant data only through withTenantTransaction (packages/persistence/src/tenant-context.ts), which sets the app.tenant_id session setting the policies key on. Organization and operator scoping belong to authorization, never a second pervasive RLS key.

Wall 1: the resolveTenant decision tree

verified WorkOS JWT (JWKS-checked subject + optional org_id claim)
        │
   org_id present? ──yes──► resolve_tenant_for_workos_org (SECURITY DEFINER)
        │                      └─ no link → tenant_link_not_found (400)
        no                     (forwarded header IGNORED on this path)
        │
   resolve_active_grants_for_subject  (keyed on the VERIFIED subject only)
        │
   0 grants ──► no_workspace (400)
   1 grant  ──► resolved  (the single-tenant common case)
   2+ grants ─► consult x-opsdna-active-tenant header (UNTRUSTED):
                  names a tenant IN the grant set → resolved
                  no pick            → ambiguous (400)
                  pick outside set   → preferred_not_granted (400)

The security property of the forwarded header (§3 of ADR-0033) is worth internalizing: a caller can forge any value, but the header only ever selects within the verified subject’s active-grant set — it can never reach a tenant the identity is not already entitled to, and it never influences the grant lookup itself. Implementation lives at apps/api/src/lib/resolve-tenant.ts (the tree), apps/api/src/lib/grant-driven-tenant.ts (the pure gate), and apps/api/src/lib/grant-tenant-resolver.ts → the auth.resolve_active_grants_for_subject SQL function, which crosses the auth_provider_account provider-subject → identity bridge.

The grant model

The typed shape is in packages/identity/src/schema.ts. A PermissionGrantRecord binds an identity_id to a tenant_id with a persona (gp_team | lp | auditor | attorney | company_user), a scope (tenant | ledger_scope | lp_account | matter | audit_engagement | company_workspace + optional scope_id), a template (administrator | read_only | guest_viewer), a validity window, and full grant/revoke attribution. Per-grant CapabilityOverrideRecord rows apply allow/deny tweaks. Tenants themselves carry a lifecycle (stub → claimed → active → suspended → soft_deleted) with audited transitions:

export interface PermissionGrantRecord {
  readonly permission_grant_id: string
  readonly identity_id: string
  readonly tenant_id: string
  readonly persona: PortalPersona
  readonly scope_type: PermissionScopeType
  readonly scope_id: string | null
  readonly template: PermissionTemplate
  readonly valid_from: string
  readonly valid_until: string | null
  // …grant/revoke attribution + optimistic-concurrency version
}

Note the three planes that never merge: auth.identity (the login principal) is distinct from entity.investor / entity.legal_entity (the juridical spine) and from crm.person (the relationship record). Grants authorize an identity’s actions in a tenant; they are not entity-model facts.

See /persistence for withTenantTransaction and the tenant-scoped repository patterns, and the layer cake for where tenant and organization sit in the entity spine.