Multi-Tenancy Architecture

Overview

The Refresh App Web implements a comprehensive multi-tenancy architecture that provides complete data isolation while maintaining excellent performance. Tenant isolation is enforced at both the application level (user identity resolution, permission checking) and the database level (Row-Level Security using JWT claims).

Multi-Tenancy Model

Tenant Hierarchy

Organization (Tenant)
├── Tenant Settings
├── Tenant Users (with roles: owner, admin, manager, member, viewer)
├── Groups
│   ├── Group Settings
│   └── Group Users (with roles: admin, manager, member)
└── Tenant Data
    ├── Integrations
    ├── Employees (HRIS)
    ├── Survey Responses
    ├── Reports
    └── Resources

Key Concepts

Tenant:

• Primary isolation boundary

• Represents an organization/company (your customers)

• Has its own branding, settings, and data

• Users can belong to multiple tenants via linked accounts

Group:

• Sub-organization within a tenant

• Represents a team, department, or division

• Inherits tenant context

• Users can belong to multiple groups

Active Tenant:

• Currently selected tenant for the request

• Specified via URL path parameter (:tenantId in route)

• Determines which work user_id to use for data operations

• Encoded in Neon JWT for RLS enforcement

User-Tenant Relationship

Multiple User Records Per Person

A single person can have multiple users records, one per tenant they've worked with:

┌─────────────────────────────────────────────────────────────────┐
│                     TAYLOR (The Person)                          │
├─────────────────────┬─────────────────────┬─────────────────────┤
│  users (personal)   │  users (acme work)  │  users (newco work) │
│  id: user-P         │  id: user-A         │  id: user-N         │
├─────────────────────┼─────────────────────┼─────────────────────┤
│  tenant_users:      │  tenant_users:      │  tenant_users:      │
│  (none)             │  acme (inactive)    │  newco (active)     │
└─────────────────────┴─────────────────────┴─────────────────────┘

See User Management for complete details on user identity lifecycle.

Tenant Context Resolution

When a request arrives for a tenant route (e.g., /api/v1/tenants/newco/...):

// 1. Resolve logged-in user from JWT
const loggedInUserId = await resolveIdentity(jwt); // Could be user-P (personal)

// 2. Find work user_id for requested tenant
const workUserId = await getWorkUserIdForTenant(loggedInUserId, 'newco');
// Returns user-N (the NewCo work account)

// 3. Use work user_id for all tenant data operations
const surveys = await db.query.surveyResponses.findMany({
	where: eq(surveyResponses.userId, workUserId) // user-N
});

Application-Level Isolation

Request Flow

1. Request arrives with Authorization header + tenant in URL path
   ↓
2. Validate FusionAuth JWT → get fusionauth_id
   ↓
3. Resolve internal user_id via user_identities
   ↓
4. For tenant routes: find work user_id for :tenantId URL param
   ↓
5. Validate tenant_users record exists with status = 'active'
   (bypass_rls users skip this check)
   ↓
6. Get role from tenant_users → lookup permissions
   ↓
7. Generate Neon JWT with work user_id + tenant_id
   ↓
8. Execute database query with RLS enforcement

Tenant and Group Context: URL Path Parameters

Tenant and group context are passed via URL path parameters (not headers):

GET /api/v1/tenants/{tenant_id}/groups/{group_id}/reports

Why URL path, not headers?

Benefit	URL Path
Self-documenting URLs	✅
Standard REST convention	✅
Easier to audit/log	✅
Works with browser history/bookmarks	✅
Shareable URLs	✅
No hidden state	✅
Validated by middleware	✅

Headers are reserved for:

• Cross-cutting concerns (Authorization, Request-Id, tracing)

• Internal validation (bypass users may send X-Tenant-Id to validate against URL)

Example Route Structure

/api/v1/tenants                           # List user's tenants
/api/v1/tenants/{tenant_id}               # Get tenant details
/api/v1/tenants/{tenant_id}/members       # List tenant members
/api/v1/tenants/{tenant_id}/groups        # List groups in tenant
/api/v1/tenants/{tenant_id}/groups/{group_id}           # Get group
/api/v1/tenants/{tenant_id}/groups/{group_id}/reports   # Group reports
/api/v1/tenants/{tenant_id}/surveys       # Tenant surveys
/api/v1/personal/resources                # Personal resources (no tenant)

Middleware Implementation

Middleware validates tenant access from URL path parameters:

// Middleware
async function tenantMiddleware(request: Request) {
	// Extract from URL path parameter
	const tenantId = request.params.tenant_id;

	// Personal routes don't need tenant context
	if (isPersonalRoute(request.url)) {
		return next();
	}

	// Tenant routes require context
	if (!tenantId) {
		return error(400, 'Tenant ID required in URL path');
	}

	// Bypass users can access any tenant (internal admins)
	if (locals.authContext.hasAnyBypass) {
		locals.tenantId = tenantId;
		locals.canBypassRls = true;
		return next();
	}

	// Regular users: resolve work user_id for this tenant via membership
	const workUserId = await getWorkUserIdForTenant(locals.loggedInUserId, tenantId);

	if (!workUserId) {
		return error(403, 'Not authorized for this tenant');
	}

	locals.tenantId = tenantId;
	locals.workUserId = workUserId;
	locals.canBypassRls = false;

	return next();
}

Group Context

Groups are always scoped within a tenant. The :groupId URL path parameter is used when an operation is group-specific:

// Group middleware runs after tenant middleware
async function groupMiddleware(request: Request) {
	// Extract from URL path parameter
	const groupId = request.params.group_id;

	if (!groupId) {
		return next(); // Group context optional for some routes
	}

	// Verify group belongs to the active tenant
	const group = await db.query.groups.findFirst({
		where: and(eq(groups.id, groupId), eq(groups.tenantId, locals.tenantId))
	});

	if (!group) {
		return error(404, 'Group not found in this tenant');
	}

	// Bypass users can access any group (internal admins)
	if (locals.canBypassRls) {
		locals.groupId = groupId;
		return next();
	}

	// Regular users: verify group membership
	const groupMembership = await db.query.groupUsers.findFirst({
		where: and(eq(groupUsers.groupId, groupId), eq(groupUsers.userId, locals.workUserId))
	});

	if (!groupMembership) {
		return error(403, 'Not authorized for this group');
	}

	locals.groupId = groupId;
	locals.groupRole = groupMembership.role;

	return next();
}

Personal vs Tenant Routes

Route Type	Tenant Context	User ID Used	Data Access
Personal	Not in URL	Primary user_id	Personal resources only
Tenant	:tenantId in URL	Work user_id	Tenant-owned data

Personal Routes: /personal/*, /settings/account, /settings/linked-accounts

Tenant Routes: /tenants/{tenant_id}/dashboard, /tenants/{tenant_id}/surveys, etc.

Cookie for UI Convenience

For the SvelteKit frontend, activeTenantId cookie stores the last-selected tenant:

// Set when user switches tenants in UI
cookies.set('activeTenantId', tenantId, {
	path: '/',
	maxAge: 60 * 60 * 24 * 30, // 30 days
	httpOnly: true,
	secure: process.env.NODE_ENV === 'production'
});

The frontend uses this cookie for navigation (e.g., redirecting to /tenants/{activeTenantId}/dashboard after login).

Database-Level Isolation

Row-Level Security (RLS)

RLS policies automatically filter database queries based on JWT claims:

-- Enable RLS on tenant-scoped tables
ALTER TABLE survey_responses ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only access data for their active tenant
CREATE POLICY tenant_isolation_policy ON survey_responses
  USING (
    tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id'
  );

Neon JWT Generation

After resolving the work user_id for a tenant, generate a Neon JWT:

async function generateNeonJwt(workUserId: string, tenantId: string): Promise<string> {
	const claims = {
		sub: workUserId, // Work user_id for this tenant
		tenant_id: tenantId, // For RLS filtering
		role: 'authenticated',
		iss: 'https://app.refresh.tech',
		iat: Math.floor(Date.now() / 1000),
		exp: Math.floor(Date.now() / 1000) + 8 * 60 * 60 // 8 hours
	};

	return signJwt(claims, PRIVATE_KEY);
}

Database Connection with JWT

import { neon } from '@neondatabase/serverless';

function createDbClient(neonJwt: string) {
	const sql = neon(DATABASE_URL, {
		authToken: neonJwt
	});

	return drizzle(sql);
}

// Usage
const db = createDbClient(locals.neonJwt);
const data = await db.query.surveyResponses.findMany();
// RLS automatically filters to tenant_id from JWT

Role-Based Access Control

Tenant Roles

Stored in tenant_users.role, mapped to permissions:

Role	Description	Example Permissions
tenant:owner	Full control	tenant:delete, billing:manage
tenant:admin	Manage tenant	member:invite, survey:delete
tenant:manager	Create content	survey:create, report:export
tenant:member	Basic access	survey:respond, survey:view:own
tenant:viewer	Read-only	survey:view, report:view

Group Roles

Stored in group_users.role:

Role	Description
group:admin	Manage group settings and members
group:manager	Add members, create content
group:member	Participate in group activities

Permission Resolution

async function getEffectivePermissions(
	userId: string,
	tenantId: string,
	groupId?: string
): Promise<Set<string>> {
	const permissions = new Set<string>();

	// Get tenant role and permissions
	const tenantUser = await db.query.tenantUsers.findFirst({
		where: and(
			eq(tenantUsers.userId, userId),
			eq(tenantUsers.tenantId, tenantId),
			eq(tenantUsers.status, 'active')
		)
	});

	if (tenantUser) {
		const tenantPerms = await db.query.permissions.findMany({
			where: eq(permissions.role, tenantUser.role)
		});
		tenantPerms.forEach((p) => permissions.add(p.permission));
	}

	// Add group permissions if applicable
	if (groupId) {
		const groupUser = await db.query.groupUsers.findFirst({
			where: and(eq(groupUsers.userId, userId), eq(groupUsers.groupId, groupId))
		});

		if (groupUser) {
			const groupPerms = await db.query.permissions.findMany({
				where: eq(permissions.role, groupUser.role)
			});
			groupPerms.forEach((p) => permissions.add(p.permission));
		}
	}

	return permissions;
}

See Authorization for complete details.

Data Model

Tenant Table

export const tenants = pgTable('tenants', {
	id: uuid('id').primaryKey().defaultRandom(),
	tenantName: text('tenant_name').notNull(),
	logoUrl: text('logo_url'),
	description: text('description'),
	metadata: jsonb('metadata').$type<{
		features?: string[];
		plan?: string;
	}>(),
	onboardingComplete: boolean('onboarding_complete').default(false),
	createdDate: timestamp('created_date').notNull().defaultNow(),
	updatedDate: timestamp('updated_date').notNull().defaultNow()
});

Tenant Users Table

export const tenantUsers = pgTable('tenant_users', {
	id: uuid('id').primaryKey().defaultRandom(),
	tenantId: uuid('tenant_id')
		.notNull()
		.references(() => tenants.id),
	userId: uuid('user_id')
		.notNull()
		.references(() => users.id),
	role: text('role').notNull(), // tenant:owner, tenant:admin, etc.
	status: text('status').notNull(), // active, inactive
	createdDate: timestamp('created_date').notNull().defaultNow(),
	updatedDate: timestamp('updated_date').notNull().defaultNow()
});

Groups Tables

export const groups = pgTable('groups', {
	id: uuid('id').primaryKey().defaultRandom(),
	tenantId: uuid('tenant_id')
		.notNull()
		.references(() => tenants.id),
	groupName: text('group_name').notNull(),
	createdDate: timestamp('created_date').notNull().defaultNow()
});

export const groupUsers = pgTable('group_users', {
	id: uuid('id').primaryKey().defaultRandom(),
	groupId: uuid('group_id')
		.notNull()
		.references(() => groups.id),
	userId: uuid('user_id')
		.notNull()
		.references(() => users.id),
	role: text('role').notNull(), // group:admin, group:manager, group:member
	createdDate: timestamp('created_date').notNull().defaultNow()
});

Security Considerations

Tenant Isolation Guarantees

1. Application Level:

   • :tenantId URL path parameter required for tenant routes

   • Work user_id resolved for requested tenant (via tenant_users membership)

   • tenant_users.status = 'active' check (bypass_rls users skip this)

2. Database Level:

   • RLS policies enforce WHERE tenant_id = jwt_claim

   • Cross-tenant queries blocked by Postgres

   • Cannot bypass RLS from application code

3. JWT Security:

   • Neon JWT contains tenant_id (not user-controllable)

   • RS256 signing prevents tampering

   • 8-hour expiration with refresh

Attack Prevention

Attack	Prevention
Tenant ID tampering	JWT signed with private key, verified by Neon
Cross-tenant access	RLS policies enforce isolation at DB level
Unauthorized tenant access	tenant_users membership check
JWT forgery	RS256 requires private key to sign

Performance Optimizations

1. Permission Caching

const permissionCache = new LRUCache<string, Set<string>>({
	max: 1000,
	ttl: 1000 * 60 * 5 // 5 minutes
});

function getCacheKey(userId: string, tenantId: string): string {
	return `${userId}:${tenantId}`;
}

2. JWT Reuse

Only regenerate Neon JWT when:

• Token expired

• Tenant context changes

• User permissions change

3. Database Indexes

-- Essential indexes for multi-tenant queries
CREATE INDEX idx_tenant_users_user_tenant ON tenant_users(user_id, tenant_id);
CREATE INDEX idx_tenant_users_tenant_status ON tenant_users(tenant_id, status);
CREATE INDEX idx_survey_responses_tenant ON survey_responses(tenant_id);
CREATE INDEX idx_survey_responses_user ON survey_responses(user_id);

Best Practices

1. Always Use Work User ID for Tenant Data

// Good - uses work user_id for tenant context
const workUserId = await getWorkUserIdForTenant(loggedInUserId, tenantId);
await db.insert(surveyResponses).values({
	userId: workUserId, // Work user_id
	tenantId
	// ...
});

// Bad - uses logged-in user directly
await db.insert(surveyResponses).values({
	userId: loggedInUserId, // Could be personal account!
	tenantId
	// ...
});

2. Validate Tenant Access

// Good - explicit check
const workUserId = await getWorkUserIdForTenant(userId, tenantId);
if (!workUserId) {
	throw error(403, 'Not authorized for this tenant');
}

// Bad - assume access
const data = await db.query.tenants.findFirst({
	where: eq(tenants.id, tenantId) // No access check!
});

3. Use Neon JWT for All Tenant Queries

// Good - JWT enforces RLS
const db = createDbClient(neonJwt);
const data = await db.query.surveyResponses.findMany();

// Bad - bypasses RLS
const db = createRootDbClient(); // Admin connection
const data = await db.query.surveyResponses.findMany();

Related Documentation

• Authentication Flow - JWT and FusionAuth

• User Management - Identity lifecycle

• Authorization - Roles and permissions

• Security - Security architecture

• Database Architecture - RLS policies

---

Last updated: December 2025