Multi-Tenancy & Tenant Isolation

Relevant source files

The following files were used as context for generating this wiki page:

• packages/db/prisma/migrations/20261006184700_row_level_security/migration.sql
• apps/api/test/tenant-isolation.int.test.ts
• packages/db/src/client.ts
• packages/db/prisma/schema.prisma
• README.md

This system implements multi-tenancy through Postgres Row-Level Security (RLS) to ensure data isolation between different clinics or organizations. Every request runs under strict RLS policies, preventing a signed-in user in Organization A from accessing data belonging to Organization B.

The architecture relies on the database-level enforcement of tenant boundaries rather than application-level filtering alone. This approach satisfies critical security requirements for clinical data handling and auditing.

Sources: README.md:10-12, apps/api/test/tenant-isolation.int.test.ts:7-10

Core Architecture & Roles

The database uses least-privilege roles to control access. The API connects as a privileged owner but switches to restricted roles inside transactions using SET LOCAL ROLE.

Database Roles

Role Name	Purpose	Permissions
dhc_app	Tenant requests	Sees only rows matching the active organisation_id set in the transaction.
dhc_auth	Authentication & Identity	Accesses users, sessions, and otp_challenges. Cannot access clinical tables.

Sources: packages/db/prisma/migrations/20261006184700_row_level_security/migration.sql:1-10

Access Control Mechanism

The system determines the current tenant via a custom Postgres function app_current_organisation_id(). This function retrieves a UUID from the database session setting app.organisation_id.

This diagram shows the sequence of steps the API takes to establish a secure, tenant-isolated database session.

Sources: packages/db/prisma/migrations/20261006184700_row_level_security/migration.sql:24-27, packages/db/src/client.ts:18-27

Tenant Isolation Helpers

The @dhc/db package provides two primary TypeScript helpers to wrap database operations within the correct security context.

withTenant

This function executes queries as the dhc_app role. You must provide a valid organization UUID. It sets the local role and the app.organisation_id configuration within a transaction.

• Path: packages/db/src/client.ts
• Logic:
  1. Validates the organization UUID.
  2. Executes SET LOCAL ROLE dhc_app.
  3. Executes SELECT set_config('app.organisation_id', organisationId, true).
  4. Runs the provided callback function.

withAuth

This function executes queries as the dhc_auth role. It provides access to platform-level identity tables but blocks access to clinical tables like patients or consultations.

• Path: packages/db/src/client.ts

Sources: packages/db/src/client.ts:18-39

Row-Level Security Policies

RLS policies are applied to every table containing an organisation_id column.

Policy Implementation Patterns

Table Type	Policy Name	Logic
Tenant Data	tenant_isolation	USING (organisation_id = app_current_organisation_id())
Auth Lookup	auth_lookup	Allows dhc_auth to SELECT for verification (e.g., membership checks).
Global User	auth_access	Allows access to the users table for authentication regardless of tenant.

Protected Entities

The following entities (among others) are strictly isolated by the tenant_isolation policy:

• patients
• memberships
• clinics
• consultation_types
• appointments
• vitals
• consultations

Sources: packages/db/prisma/migrations/20261006184700_row_level_security/migration.sql:31-72, packages/db/prisma/schema.prisma:110-150

Audit Log Security

The audit_logs table features specialized security measures to prevent tampering.

1. Append-Only Restriction: A database trigger audit_logs_append_only intercepts UPDATE and DELETE operations and raises an exception.
2. Isolated Reading: The dhc_app role can only read audit logs where the organisation_id matches the current session.
3. Global Writing: The dhc_auth role can insert logs globally to record authentication events (e.g., failed logins).

Sources: packages/db/prisma/migrations/20261006184700_row_level_security/migration.sql:57-72

Data Relationships & Tenancy

The organisation_id acts as the root partition for the entire data model.

The ER diagram illustrates how the Organisation entity serves as the primary owner for all clinical and operational data, enforcing the tenant boundary.

Sources: packages/db/prisma/schema.prisma:110-150, packages/db/prisma/schema.prisma:340-400

Validation and Testing

Isolation is verified through a dedicated integration test suite apps/api/test/tenant-isolation.int.test.ts.

• API Layer: Tests confirm that searching for or fetching a patient ID belonging to another clinic returns a 404 NOT FOUND error, identical to a non-existent record.
• Database Layer: Tests verify that dhc_app sees zero rows when no organization ID is set.
• Write Protection: The suite confirms that RLS rejects attempts to INSERT or UPDATE a row using an organisation_id that does not match the session.
• Column Protection: An automated test ensures that every table in the public schema containing an organisation_id column has RLS enabled and a dhc_app policy applied.

Sources: apps/api/test/tenant-isolation.int.test.ts:25-50, apps/api/test/tenant-isolation.int.test.ts:80-120

Summary

Tenant isolation in this platform is a fundamental security layer implemented at the database level. By utilizing Postgres RLS and restricted session roles, the system ensures that organizational boundaries are respected even if application-level logic fails. The append-only audit log further guarantees the integrity of all clinical and administrative actions within each tenant's scope.