Skip to main content

​
Overview

PensionPortal.ai 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
ConceptValue
Tenant anchorbrokerId on the employers table
Tenant unitOne broker firm = one tenant
Tenant root entityusers with role = BrokerAdmin linked to a broker record
Child entitiesemployers → schemes → members, keyFunctionHolders, writtenPolicies, oraReports
Isolation layerApplication 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

RoleScopeNotes
SuperAdminSystem-widePlatform ops only. Bypasses tenant check. Never issued to customers.
BrokerAdminSingle broker tenantFull CRUD within their tenant
BrokerUserSingle broker tenantRead + limited write within their tenant
EmployerAdminSingle employer (within tenant)Manages their employer record + schemes
EmployerUserSingle employer (within tenant)Read access to their employer’s data
MemberSingle member recordSelf-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

PensionPortal.ai 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:
LayerMechanismFailure Mode
JWT signingAuth.js AUTH_SECRET signs JWT; tampering is detectable401 Unauthorized
Middlewareauth() validates JWT on every /api/* request401 Unauthorized
requireTenant()Checks actor.tenantId === targetTenantId at service layer403 Forbidden
Scoped queriesDrizzle queries filter by employer.brokerId = actor.tenantIdEmpty result set
Signed blob URLsVercel Blob signed URLs with expiryAccess denied at CDN layer
Role isolationrequireRole() prevents lower roles reaching higher-role endpoints403 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