Skip to content

Multitenancy

1. Overview

SIS uses a single shared schema with a tenant_id UUID column on every tenant-scoped table. Tenant isolation is currently enforced at the service layer — every service method filters by tenantId in its Prisma where clause. PostgreSQL Row-Level Security (RLS) is planned as a Phase 2 defense-in-depth safety net at the database level.

2. Current Status

Current status: Only service-layer tenantId filtering is active. Every service method filters by tenantId in its where clause. No PostgreSQL RLS policies exist in the database yet. RLS is planned as a Phase 2 defense-in-depth safety net. The SQL patterns in §4 show the target architecture, not current state.

3. Tenant Resolution

Tenant identity is established during login using a password-first two-step approach that eliminates Host header dependency.

Step 1 — Credential verification:

POST /api/v1/auth/login { email, password }
  1. Find all active User records matching email across all active tenants
  2. Verify password against each with argon2.verify (in parallel)
  3. Collect matches — user + tenant pairs where the password is valid
Matches Response
0 401 — "Invalid credentials" (generic, no enumeration)
1 Normal login: cookies set, return { user }
2+ 200 with { requiresTenantSelection, tenants[], selectionToken }

Step 2 — Tenant selection (multi-match only):

The frontend shows a tenant picker and completes login:

POST /api/v1/auth/login/select-tenant { selectionToken, tenantId }
→ cookies set, return { user }

The selectionToken is a short-lived (60s) JWT containing { sub: "tenant-selection", matchedUserIds: [...] }. The tenant list is only returned after password verification — it does not leak email-to-tenant mappings.

After authentication, tenantId is embedded in the signed JWT. All subsequent requests use the JWT payload, not the Host header. Tenant status (ACTIVE/TRIAL) is validated at login and re-validated on every token refresh.

Full auth details: chapter 03

4. RLS Target Architecture

The following SQL pattern is the planned Phase 2 design — it is not currently active.

ALTER TABLE students ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON students
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

SET LOCAL app.current_tenant_id = '<tenant-uuid>';

When deployed, RLS will act as a database-level safety net independent of service-layer filtering. Both layers will coexist: service-layer filtering as the primary enforcement path, RLS as the backstop.

5. Implementation Rules

  1. Every tenant-scoped table has tenant_id UUID NOT NULL + FK to tenants.
  2. Child tables (Department, Grade, Period) carry their own tenantId even though it is derivable via FK chain — defense-in-depth: every query path is independently tenant-safe, future RLS policies stay simple, and future CRUD APIs have a direct tenantId filter as a safety net.
  3. Services MUST filter by tenantId in every where clause (RLS is deferred — service-layer filtering is the sole enforcement mechanism today).
  4. Access the tenant identity in controllers via the @TenantId() tenantId: string decorator, which extracts tenantId from req.user.tenantId (populated by JwtAuthGuard).
  5. Platform admin (is_platform_admin flag on User): bypasses tenant scope and all permission guards (ScopeGuard, ActionGuard, FieldWriteGuard, FieldFilterInterceptor).
  6. Users are per-tenant (user.tenantId). A single user can link to multiple domain tables simultaneously (e.g., both Teacher and Staff). Capabilities are defined by roles, not by which domain tables the user links to.
  7. No cross-tenant queries in application code — tenant isolation is the service layer's responsibility. Cross-tenant analytics (e.g., admin reporting) require an explicit bypass handled by an admin-only reporting path.

6. Trust Proxy

Configured in main.ts via app.set('trust proxy', 1). This causes req.ip to return the client's real IP address when the server is behind Railway's load balancer and Cloudflare's proxy. Required for accurate rate limiting (throttler uses req.ip as the key) and for meaningful IP logging in security events.

7. Trade-offs

Three trade-offs were accepted in choosing shared-schema multitenancy:

  • Weaker isolation than schema-per-tenant — acceptable for an EdTech SaaS where tenants are schools, not competing businesses with adversarial threat models. RLS (Phase 2) closes the gap at the database level.
  • Discipline required for new tables — every new table must include tenant_id and, eventually, an RLS policy. This is a code-review checklist item (see docs/workflows.md).
  • Cross-tenant analytics require an RLS bypass — queries that span tenants (e.g., platform-level reporting) must use a superuser connection or SET ROLE to bypass RLS. Acceptable because this is handled by a dedicated admin-only reporting service.