EHQ Brain

ADR-001 Multi-Tenancy Access Model

2 min read

ADR-001: Multi-Tenancy and Access Model

Status: Accepted Owner: @bilal @deen Date: 2025-12-06

Context

Envo serves multiple property management organisations. We need to:

  1. Isolate data between organisations completely
  2. Allow multiple users to access a single organisation
  3. Allow a single user to access multiple organisations
  4. Support Envo staff temporarily accessing any organisation for support
  5. Maintain full audit trails of who accessed what

Options Considered

OptionProsCons
A: Schema-per-tenantComplete isolation, simpler RLSComplex migrations, connection pooling issues, harder cross-tenant queries
B: Single DB with landlord_idSimple, standardTerminology locked, doesn’t model orgs cleanly
C: Single DB with organisation_id + junction tableFlexible, supports all access patterns, future-proofSlightly more complex RLS

Decision

Option C: Single database with organisations and junction-based access.

Data Model

users (synced from Supabase Auth)
├── id (uuid, maps to auth.uid())
├── email, full_name, phone
├── created_at, updated_at

organisations
├── id (uuid)
├── name, type (company | individual | hmo_operator)
├── plan (basic | premium | partner)
├── billing_email
├── created_at, updated_at, deleted_at

user_organisation_access
├── user_id (FK → users)
├── organisation_id (FK → organisations)
├── role (owner | admin | staff | envo_support)
├── granted_by, granted_at, expires_at, revoked_at, is_active

Access Patterns

  1. Organisation owner: role = 'owner', expires_at = null
  2. Organisation staff: role = 'staff', expires_at = null
  3. Envo support access: role = 'envo_support', expires_at = NOW() + interval '4 hours'

Session Scoping

  • Query user_organisation_access for active, non-expired access on login
  • If multiple organisations, present picker
  • Store selected organisation_id in session/JWT claims
  • All RLS policies filter by organisation_id from session

Consequences

Positive

  • Clean separation of identity (users) from tenancy (organisations)
  • Supports all current and future access patterns
  • Full audit trail via granted_by, granted_at, revoked_at
  • Envo staff access is time-boxed and auditable

Negative

  • RLS policies slightly more complex (junction table join)
  • Session must carry organisation_id context
  • Migration needed if schema-per-tenant required for compliance later

Risks

  • Performance: Junction table adds a join. Mitigated by indexing + caching in JWT.
  • Complexity: More tables. Mitigated by clear naming and documentation.

Graph View

Backlinks