Skip to main content

Tenancy model

Multi-tenancy pivots entirely on company_memberships (user_id, company_id, role, UNIQUE(user_id, company_id)). It is the single source of truth for both which tenants a user belongs to and their role.

Isolation mechanism​

RLS uses a SECURITY DEFINER helper get_user_company_ids() (SETOF uuid of the caller's companies). Two patterns:

  • Tables with a direct company_id: USING (company_id IN (SELECT get_user_company_ids())).
  • Case-child tables: EXISTS (SELECT 1 FROM cases c WHERE c.id = t.case_id AND c.company_id IN (SELECT get_user_company_ids())).

Several child tables (case_deadlines, case_audit_events, inquiry_actions, case_response_data, case_dossier_exports) denormalize company_id onto themselves for direct filtering — so case_id and company_id must be kept consistent, and a write with a null/wrong company_id mis-tenants or violates NOT NULL.

Helper functions: is_super_admin(), is_company_member(), is_company_admin() (super_admin/company_admin/hr_admin), has_case_role().

The gaps (see Security)​

  • RLS enforces membership, but not the workflow/role model — any member can write cases (client-side integrity).
  • companies is publicly readable (USING(true)), self-provisioning allows role escalation, and the case-attachments bucket has no tenant path isolation. See RLS & tenancy gaps and storage isolation.
  • The app's effective role is global-max across companies, not per-tenant.
  • Three overlapping assignment models coexist: cases.assigned_to, case_assignments (simple), and case_team_members (role_in_case, multi-valued).

Rebuild implication​

Keep membership-based tenancy but make the active tenant explicit (URL), evaluate roles per-tenant server-side, close the public-read and self-provision holes, and add per-company path isolation to storage. Consolidate the assignment models.