Multi-Tenant Data Isolation

The platform enforces strict tenant-level data isolation at both the application layer (tRPC middleware) and the database layer (PostgreSQL Row-Level Security). This document covers the architecture, configuration, and API for tenant management.

Architecture Overview

Isolation is applied in two complementary layers:

Application layer — tenantProcedure tRPC middleware injects tenant context and checks membership/permissions before any procedure runs.
Database layer — PostgreSQL RLS policies ensure that even if application-layer checks are bypassed, queries can only return rows belonging to the caller's org_id.

Request
  └─► tRPC tenantProcedure (loads tenant + membership, injects context)
        └─► Drizzle query (inside withTenantContext → SET LOCAL app.current_org_id)
              └─► PostgreSQL RLS policy (verifies org_id at DB level)

Database Schema

`tenants`

The canonical tenant registry. One record per organization.

Column	Type	Notes
`id`	`text`	Primary key, UUID
`org_id`	`text`	FK → `organizations.id`. Indexed.
`tier`	`tenant_tier`	`starter`, `professional`, or `enterprise`
`settings`	`jsonb`	Arbitrary tenant configuration
`ai_features_enabled`	`text[]`	List of enabled AI feature flags
`soft_deleted_at`	`timestamp`	GDPR soft-delete timestamp
`created_at` / `updated_at`	`timestamp`	Auto-managed

`tenant_members`

Explicit membership records with per-module permission grants.

Column	Type	Notes
`id`	`text`	Primary key, UUID
`tenant_id`	`text`	FK → `tenants.id`
`user_id`	`text`	FK → `users.id`
`role`	`tenant_member_role`	Member's role within the tenant
`permissions`	`jsonb`	Per-module grants, e.g. `{ "hr": "read", "finance": "write" }`
`invited_by`	`text`	FK → `users.id`
`joined_at`	`timestamp`	When the member joined
`soft_deleted_at`	`timestamp`	Soft-delete timestamp

Unique constraint on (tenant_id, user_id).

`data_isolation_policies`

Per-module encryption and backup configuration.

Column	Type	Notes
`id`	`text`	Primary key, UUID
`tenant_id`	`text`	FK → `tenants.id`
`module`	`data_isolation_module`	`hr`, `finance`, or `legal`
`encryption_key_id`	`text`	Reference to the module's encryption key
`backup_policy`	`jsonb`	`{ retentionDays, frequency, crossRegion }`

Unique constraint on (tenant_id, module).

Row-Level Security

All tenant-related tables have RLS enabled and enforced (FORCE ROW LEVEL SECURITY).

Policy Logic

tenants — org_id = current_setting('app.current_org_id')::text
tenant_members — EXISTS (SELECT 1 FROM tenants t WHERE t.id = tenant_members.tenant_id AND t.org_id = current_setting('app.current_org_id')::text)
data_isolation_policies — same EXISTS pattern through tenants

Setting Tenant Context

Use withTenantContext() to execute queries inside a transaction with the correct app.current_org_id set:

import { withTenantContext } from "~/db/rls";

const result = await withTenantContext(db, orgId, async (tx) => {
  return tx.select().from(tenants).where(eq(tenants.orgId, orgId));
});

withTenantContext opens a transaction, calls SET LOCAL app.current_org_id = <orgId>, runs the callback, and commits. All RLS policies fire automatically against the set context.

Note: SET LOCAL scopes the setting to the current transaction only. It is automatically cleared when the transaction ends.

tRPC Middleware

`tenantProcedure`

Extends orgProcedure with tenant-specific context loading. Use it for any procedure that needs tenant or permission data.

import { tenantProcedure } from "~/lib/trpc/trpc";

export const myRouter = createTRPCRouter({
  doSomething: tenantProcedure.query(async ({ ctx }) => {
    // ctx.tenantId       — the resolved tenant's ID
    // ctx.tenantTier     — 'starter' | 'professional' | 'enterprise'
    // ctx.tenantPermissions — caller's per-module permission map
  }),
});

If the tenant record does not exist for the current org, the procedure throws a NOT_FOUND tRPC error.

Tenant API (tRPC)

All procedures are under the tenant namespace. Mutations require admin role or higher unless noted.

`tenant.get`

Fetch the tenant record for the current org. If no tenant record exists, one is automatically provisioned (idempotent). Also applies RLS policies for the tenant.

const tenant = await trpc.tenant.get.query();

`tenant.update`

Update tenant-level settings. Requires admin+.

await trpc.tenant.update.mutate({
  tier: "professional",
  settings: { someKey: "someValue" },
  aiFeaturesEnabled: ["smart-scheduling", "payroll-forecast"],
});

`tenant.listMembers`

List all active tenant members and their permissions.

const members = await trpc.tenant.listMembers.query();
// Returns: Array<{ userId, role, permissions, joinedAt, ... }>

`tenant.upsertMember`

Create or update a tenant member's role and permissions. Requires admin+.

await trpc.tenant.upsertMember.mutate({
  userId: "user_abc",
  role: "member",
  permissions: { hr: "read", finance: "write" },
});

`tenant.removeMember`

Soft-delete a tenant member record. Requires admin+.

await trpc.tenant.removeMember.mutate({ userId: "user_abc" });

`tenant.getIsolationPolicies`

Retrieve all data isolation policies for the tenant.

const policies = await trpc.tenant.getIsolationPolicies.query();
// Returns: Array<{ module, encryptionKeyId, backupPolicy }>

`tenant.setIsolationPolicy`

Create or update the isolation policy for a specific module. Requires admin+.

await trpc.tenant.setIsolationPolicy.mutate({
  module: "hr",
  encryptionKeyId: "key-arn-123",
  backupPolicy: {
    retentionDays: 90,
    frequency: "daily",
    crossRegion: true,
  },
});

Audit Logging

All mutating procedures (update, upsertMember, removeMember, setIsolationPolicy) automatically write an entry to the platform audit log. No additional configuration is required.

Tenant Tiers

Tier	Value
Starter	`starter`
Professional	`professional`
Enterprise	`enterprise`

The active tier is stored on the tenants record and exposed via ctx.tenantTier in any tenantProcedure.

Soft Deletes

Both tenants and tenant_members support soft deletion via a soft_deleted_at timestamp column. Records are not physically removed, supporting GDPR data-retention workflows and audit trails.