Multi-Tenancy Architecture

Overview

PensionsPortal.ie uses a broker-centric tenancy model. Every piece of data in the system — employers, schemes, members, documents, audit logs — is anchored to a single broker firm. A broker firm is the unit of tenancy.

CRITICAL: Tenant MUST NOT be trusted from client input. Always derive from server-side JWT.The brokerId (tenantId) is resolved exclusively from the verified server-side JWT. Any brokerId supplied in a request body, query parameter, or header is ignored entirely.

Tenancy Model

Each broker firm maps to exactly one tenant. The brokerId stored in the users table is the tenant anchor for the entire role hierarchy beneath it.

Broker (Tenant)
├── BrokerAdmin user(s)
├── BrokerUser user(s)
└── Employers
    ├── EmployerAdmin user(s)
    ├── EmployerUser user(s)
    └── Schemes
        └── Members
            └── Contributions, Fund Choices

Concept	Value
Tenant anchor	`brokerId` on the `employers` table
Tenant unit	One broker firm = one tenant
Tenant root entity	`users` with `role = BrokerAdmin` linked to a broker record
Child entities	employers → schemes → members, keyFunctionHolders, writtenPolicies, oraReports
Isolation layer	Application layer (ActorContext) — not database RLS

Key invariants:

Every employers row has a non-nullable brokerId column.
Every schemes row belongs to an employer, which belongs to a broker.
Every members row belongs to a scheme, which traces back to a broker.
All service queries filter by actor.tenantId before touching any data.

Role Hierarchy

Role	Scope	Notes
`SuperAdmin`	System-wide	Platform ops only. Bypasses tenant check. Never issued to customers.
`BrokerAdmin`	Single broker tenant	Full CRUD within their tenant
`BrokerUser`	Single broker tenant	Read + limited write within their tenant
`EmployerAdmin`	Single employer (within tenant)	Manages their employer record + schemes
`EmployerUser`	Single employer (within tenant)	Read access to their employer’s data
`Member`	Single member record	Self-service portal access only

Tenant Resolution Flow

Tenant identity is established once — at authentication — and is carried forward in an opaque, server-signed JWT for the lifetime of the session. Step 1 — User authenticates The user submits credentials via the Auth.js Credentials provider (POST /api/auth/callback/credentials). On success, Auth.js mints a signed JWT via the jwt() callback in auth.ts. The token payload includes:

{
  userId:     string,        // users.id (CUID2)
  role:       Role,          // 'SuperAdmin' | 'BrokerAdmin' | ... | 'Member'
  brokerId:   string,        // users.brokerId — the tenant anchor
  employerId: string | null, // populated for EmployerAdmin / EmployerUser / Member
}

Step 2 — brokerId is set at user creation time brokerId is written into the users record at provisioning time and stored permanently. It is never resolved from client request parameters and never changes after provisioning. Step 3 — Middleware verification Next.js middleware (middleware.ts) intercepts every /api/* request (excluding /api/auth/* and /api/health/*). It calls auth() to verify the JWT signature and extract the session payload. Requests without a valid session receive 401 { error: "Unauthorized" } immediately. Step 4 — ActorContext construction Inside each API route handler, the verified session is used to build an ActorContext:

const actor = buildActorContext({
  userId:    session.user.userId,
  tenantId:  session.user.brokerId,  // always from JWT — never from request
  role:      session.user.role,
  email:     session.user.email,
  ipAddress: req.headers.get("x-forwarded-for") ?? "unknown",
  userAgent: req.headers.get("user-agent") ?? "unknown",
});

Step 5 — Service layer enforcement Service methods receive actor as a mandatory second argument. Before any data access, the service calls requireTenant(actor, targetTenantId), which throws a TenantMismatchError (surfaced as 403) if actor.tenantId !== targetTenantId, unless actor.role === 'SuperAdmin'. Step 6 — Scoped database query All queries include an explicit tenant scope:

const schemes = await db
  .select()
  .from(schemesTable)
  .innerJoin(employersTable, eq(schemesTable.employerId, employersTable.id))
  .where(eq(employersTable.brokerId, actor.tenantId)); // tenant scope

Step 7 — Audit logging Every state-changing operation appends a record to audit_logs containing actorId and tenantId, providing a full audit trail for IORP II regulatory examination.

Sequence Diagram

Browser → POST /api/schemes
    │
    ├─► Middleware: auth()
    │       → verify JWT signature
    │       → extract { userId, brokerId, role }
    │       → 401 if missing/invalid
    │
    ├─► API Route: buildActorContext(userId, tenantId=brokerId, role, email, ip, ua)
    │
    ├─► SchemeService.create(input, actor)
    │       → validate input (Zod)
    │       → load employer record
    │       → requireTenant(actor, employer.brokerId)
    │               → throws 403 if actor.tenantId !== employer.brokerId
    │
    ├─► INSERT INTO schemes (employerId, ...)
    │       WHERE employer.brokerId = actor.tenantId
    │
    └─► AuditLog: record action with actorId + tenantId

Database Isolation Strategy

Approach: Application-Layer Isolation

PensionsPortal.ie enforces tenant isolation at the application layer via ActorContext, rather than using PostgreSQL Row-Level Security (RLS). Justification: Neon’s serverless PostgreSQL uses connection pooling (PgBouncer in transaction mode). Per-tenant RLS policies that rely on SET LOCAL session variables are incompatible with PgBouncer transaction-mode pooling, because session variables do not persist across pooled connections. Application-layer enforcement via ActorContext provides equivalent isolation with:

Full observability — every check is logged with actor identity
Testability — service layer is unit-testable without a live DB
Portability — no DB-vendor-specific policy DSL

An RLS migration path (using SET app.current_tenant_id + policies) has been considered for future implementation if the connection pooling architecture changes. If Neon adds support for session-level variables in transaction-mode pooling, RLS policies could supplement the existing application-layer enforcement. This is not currently planned.

Query Scoping Pattern

Every Drizzle ORM query that touches tenant-scoped data includes an explicit brokerId filter:

// Correct — always scoped to actor's tenant
const schemes = await db
  .select()
  .from(schemesTable)
  .innerJoin(employersTable, eq(schemesTable.employerId, employersTable.id))
  .where(
    and(
      eq(schemesTable.id, input.schemeId),
      eq(employersTable.brokerId, actor.tenantId) // tenant scope
    )
  );

// Incorrect — missing tenant scope (never do this)
const scheme = await db
  .select()
  .from(schemesTable)
  .where(eq(schemesTable.id, input.schemeId));

SuperAdmin Bypass

The SuperAdmin role bypasses the tenant check to allow platform operations:

export function requireTenant(actor: ActorContext, targetTenantId: string): void {
  if (actor.role === "SuperAdmin") return; // internal platform ops only
  if (actor.tenantId !== targetTenantId) {
    throw new TenantMismatchError(
      `Cross-tenant access denied: actor=${actor.tenantId}, target=${targetTenantId}`
    );
  }
}

SuperAdmin tokens are issued only to platform engineering accounts. They are never provisioned for broker or employer users. All SuperAdmin actions are still recorded in audit_logs.

Storage Isolation

Vercel Blob Path Convention

All documents (scheme PDFs, written policies, trust deeds) are stored in Vercel Blob with a tenant-scoped path prefix:

/{tenantId}/{schemeId}/{documentType}/{filename}

Example:

/brk_01HZ8K3X7M9NQ/sch_01HZ8K4Y2P5RT/trust-deed/trust-deed-2024.pdf

Access Controls

All blob URLs are signed with expiry — knowing the path does not grant access.
Cross-tenant URL guessing is not possible (signed URLs, not publicly accessible paths).
Document metadata is stored in the documents table scoped by employerId → brokerId.
Blob URLs are only returned to requests that pass the requireTenant check for the owning broker.

// documents always joined through employer for tenant scoping
const doc = await db
  .select()
  .from(documentsTable)
  .innerJoin(schemesTable, eq(documentsTable.schemeId, schemesTable.id))
  .innerJoin(employersTable, eq(schemesTable.employerId, employersTable.id))
  .where(
    and(
      eq(documentsTable.id, documentId),
      eq(employersTable.brokerId, actor.tenantId)
    )
  );

Cross-Tenant Access Prevention

Multiple defence layers prevent any tenant from accessing another tenant’s data:

Layer	Mechanism	Failure Mode
JWT signing	Auth.js `AUTH_SECRET` signs JWT; tampering is detectable	401 Unauthorized
Middleware	`auth()` validates JWT on every `/api/*` request	401 Unauthorized
`requireTenant()`	Checks `actor.tenantId === targetTenantId` at service layer	403 Forbidden
Scoped queries	Drizzle queries filter by `employer.brokerId = actor.tenantId`	Empty result set
Signed blob URLs	Vercel Blob signed URLs with expiry	Access denied at CDN layer
Role isolation	`requireRole()` prevents lower roles reaching higher-role endpoints	403 Forbidden

All service methods accept ActorContext as a mandatory second argument — it is never optional:

async function createScheme(
  input: CreateSchemeInput,
  actor: ActorContext // mandatory — never optional
): Promise<Scheme> {
  requireTenant(actor, input.employerId); // resolved to brokerId internally
  // ...
}

Provisioning Lifecycle

1. [SuperAdmin] Creates broker record in database
       INSERT INTO brokers (id, name, ...) VALUES (...)

2. [SuperAdmin] Creates BrokerAdmin user linked to brokerId
       INSERT INTO users (id, email, role='BrokerAdmin', brokerId=...) VALUES (...)

3. [BrokerAdmin] Logs in — receives JWT with brokerId embedded
       brokerId is now the tenantId for all subsequent operations

4. [BrokerAdmin] Creates employer records under their tenant
       INSERT INTO employers (id, brokerId=actor.tenantId, name, ...) VALUES (...)

5. [BrokerAdmin / BrokerUser] Creates schemes under employers
       INSERT INTO schemes (id, employerId, ...) VALUES (...)

6. [EmployerAdmin] Adds members to schemes
       INSERT INTO members (id, schemeId, employerId, ...) VALUES (...)

7. [Planned] BrokerAdmin invites BrokerUser via email invitation system

The email invitation system for BrokerUser onboarding is a roadmap item. BrokerUser accounts are currently created directly by SuperAdmin via the Tenant Provisioning Runbook.

Failure and Rollback Plan

If tenant provisioning fails mid-way (e.g., broker record created but BrokerAdmin user creation fails), orphaned records are identified and cleaned by a daily reconciliation job. The reconciliation job checks for:

brokers records with no associated BrokerAdmin user (orphaned broker)
users with brokerId referencing a non-existent broker (dangling FK)
employers with brokerId referencing a non-existent broker

The daily reconciliation job is not yet implemented. Until it is, manual cleanup via SuperAdmin tooling or direct database access is required for failed provisioning runs. See the Tenant Provisioning Runbook — Failure and Rollback Plan for the manual cleanup procedure.

ActorContext Type Reference

export interface ActorContext {
  userId:    string;   // Authenticated user's CUID2
  tenantId:  string;   // brokerId — the tenant anchor (from JWT only)
  role:      Role;     // Enum: SuperAdmin | BrokerAdmin | ... | Member
  email:     string;   // For audit logging
  ipAddress: string;   // For audit logging
  userAgent: string;   // For audit logging
}

export type Role =
  | "SuperAdmin"
  | "BrokerAdmin"
  | "BrokerUser"
  | "EmployerAdmin"
  | "EmployerUser"
  | "Member";

Role hierarchy for requireRole checks:

SuperAdmin > BrokerAdmin > BrokerUser > EmployerAdmin > EmployerUser > Member

Getting Started

Annual Compliance Statement

Own-Risk Assessment

Key Function Holders

Written Policies

Supervisory Readiness

OMA Transition

AI Assistant

Compliance Calendar

Overview

Tenancy Model

Role Hierarchy

Tenant Resolution Flow

Sequence Diagram

Database Isolation Strategy

Approach: Application-Layer Isolation

Query Scoping Pattern

SuperAdmin Bypass

Storage Isolation

Vercel Blob Path Convention

Access Controls

Cross-Tenant Access Prevention

Provisioning Lifecycle

Failure and Rollback Plan

ActorContext Type Reference

​Overview

​Tenancy Model

​Role Hierarchy

​Tenant Resolution Flow

​Sequence Diagram

​Database Isolation Strategy

​Approach: Application-Layer Isolation

​Query Scoping Pattern

​SuperAdmin Bypass

​Storage Isolation

​Vercel Blob Path Convention

​Access Controls

​Cross-Tenant Access Prevention

​Provisioning Lifecycle

​Failure and Rollback Plan

​ActorContext Type Reference

Overview

Tenancy Model

Role Hierarchy

Tenant Resolution Flow

Sequence Diagram

Database Isolation Strategy

Approach: Application-Layer Isolation

Query Scoping Pattern

SuperAdmin Bypass

Storage Isolation

Vercel Blob Path Convention

Access Controls

Cross-Tenant Access Prevention

Provisioning Lifecycle

Failure and Rollback Plan

ActorContext Type Reference