All Docs
FeaturesCSI Teachable Replacement AppUpdated March 13, 2026

Multi-Tenant SSO & Data Isolation

Multi-Tenant SSO & Data Isolation

This page documents the multi-tenancy and SSO identity federation system introduced in v1.0.1.

Overview

Every organization on the platform is a fully isolated tenant. Learners and instructors authenticate through their organization's existing identity provider (OIDC or SAML) rather than managing separate platform credentials. Tenant data is isolated at the database level using Postgres Row-Level Security, so cross-tenant data leakage is prevented even in the event of application-layer bugs.

Database Schema

organizations table (extended)

The customDomain column (unique, nullable) was added to support CNAME-based tenant routing alongside the existing slug-based routing.

organizations
  id            text  PK
  name          text
  slug          text  UNIQUE
  customDomain  text  UNIQUE   ← new in v1.0.1
  logoUrl       text
  primaryColor  text
  createdAt     timestamp
  updatedAt     timestamp

organization_members table

Tracks SSO-provisioned membership with IdP-side identity linkage.

organization_members
  id              text  PK
  organization_id text  FK → organizations.id
  userId          text  FK → users.id
  role            org_member_role_ext  (owner | admin | instructor | learner)
  externalIdpSub  text  — subject claim from the IdP, used for deduplication
  joinedAt        timestamp

sso_providers table

Stores per-organization OIDC and SAML configuration. clientSecret is stored encrypted.

sso_providers
  id              text  PK
  organization_id text  FK → organizations.id
  protocol        sso_protocol  (oidc | saml)
  issuerUrl       text
  clientId        text
  clientSecret    text  — AES-256-GCM encrypted hex
  metadataUrl     text  — SAML metadata endpoint
  isActive        boolean
  createdAt       timestamp
  updatedAt       timestamp

SSO Encryption

SSO client secrets are encrypted at rest using AES-256-GCM before being written to the database.

Setup

  1. Generate a 256-bit key:

    openssl rand -hex 32

  2. Set the key in your environment:

    SSO_ENCRYPTION_KEY=<64-hex-char-output>

How it works

FunctionDescription
encryptSsoSecret(plaintext)Encrypts a secret using AES-256-GCM. Returns a hex-encoded ciphertext (includes IV and auth tag).
decryptSsoSecret(encryptedHex)Server-side only. Decrypts the stored hex string back to plaintext for use in the Auth.js provider factory.
maskSecret(secret)Returns a redacted string showing only the first and last 4 characters — safe for display in the admin settings UI.

Note: decryptSsoSecret is never called from a tRPC procedure. It is used only inside resolveProviderConfig, which is a server-side export not accessible over HTTP.

SSO tRPC Router

All SSO management is exposed through the sso tRPC router.

Tenant Resolution (public)

sso.resolveTenant({ slug?: string; customDomain?: string })
// Returns: { orgId, orgName, providerType: 'oidc' | 'saml' | null }

Used by the login page to determine which IdP to redirect the user to, without requiring authentication.

Provider Management (admin only)

// Get masked config for the settings UI
sso.getSsoConfig({ orgId })

// Create or update an OIDC provider
sso.upsertOidcProvider({ orgId, issuerUrl, clientId, clientSecret })

// Create or update a SAML provider
sso.upsertSamlProvider({ orgId, metadataUrl })

// Enable or disable SSO for an org
sso.setSsoActive({ orgId, isActive: boolean })

All mutations require the caller to hold the owner or admin role in the target org (enforced via adminProcedure). All mutations are recorded in the audit log.

Member Management (admin only)

sso.addMember({ orgId, userId, role, externalIdpSub? })
sso.removeMember({ orgId, userId })
sso.listMembers({ orgId })

Available roles: owner, admin, instructor, learner.

Server-Side Provider Factory

import { resolveProviderConfig } from '@/lib/routers/sso';

const config = await resolveProviderConfig(orgId);
// Returns decrypted provider config for use in Auth.js provider factory

This function is for server-side use only (e.g., inside auth.config.ts). It is not a tRPC procedure.

Middleware & Tenant Routing

The middleware (src/middleware.ts) resolves the current tenant on every request.

Tenant resolution order:

  1. x-tenant-id header — set by a trusted reverse proxy (e.g., Vercel Edge, Nginx) based on the request hostname.
  2. x-org-id cookie — set after the user completes SSO sign-in.

The resolved tenant is forwarded as the x-org-id header to all downstream route handlers and server components.

Route behaviour:

  • /api/* routes (including /api/auth, /api/trpc, /api/inngest, webhooks) always pass through — no auth check.
  • All other routes require an authenticated session; unauthenticated requests are redirected to /sign-in.

Row-Level Security

All tenant-scoped tables have Postgres RLS enabled. The application sets the current tenant context inside each database transaction:

import { setOrgContext } from '@/db/rls';

await db.transaction(async (tx) => {
  await setOrgContext(tx, orgId); // SET LOCAL app.current_org_id = '...'
  // all queries within this transaction are automatically filtered by org
});

RLS-protected tables

TableFilter column
org_membersorg_id
subscriptionsorg_id
usage_eventsorg_id
api_keysorg_id
audit_logorg_id
webhooksorg_id
organization_membersorganization_id
sso_providersorganization_id

RLS policies are applied by applyRlsPolicies(db), which is safe to run multiple times (idempotent).

Environment Variables

VariableRequiredDescription
SSO_ENCRYPTION_KEYYes64 hex characters (256-bit key). Generate: openssl rand -hex 32
DATABASE_URLYesNeon Postgres connection string
AUTH_SECRET / NEXTAUTH_SECRETYesAuth.js session secret. Generate: openssl rand -base64 32
NEXTAUTH_URLYesFull public URL of the application

See .env.example in the repository root for the complete list of optional variables (OAuth providers, Stripe, Twilio, Resend, storage, Inngest).

Security Summary

  • SSO clientSecret is never returned in plaintext over any API — getSsoConfig returns the masked form only.
  • resolveProviderConfig (which calls decryptSsoSecret) is a server-side-only export, not reachable via HTTP.
  • All admin mutations require owner or admin role (RBAC via adminProcedure).
  • Database-level RLS ensures cross-tenant data cannot be accessed even in the event of application-layer vulnerabilities.
  • All state-changing admin operations are recorded in the audit log.