Multi-Tenant SaaS Architecture: Isolating Data Between Organizations

#saas #architecture #postgres #typescript

Multi-Tenant SaaS Architecture: Isolating Data Between Organizations

When multiple organizations share your SaaS, you need to ensure one org can never see another's data. Three patterns exist — each with different tradeoffs on cost, isolation strength, and complexity.

Pattern 1: Row-Level Isolation

Every table has an org_id column. All queries filter by it.

// Prisma schema
model Project {
  id    String @id @default(cuid())
  orgId String
  name  String
  org   Organization @relation(fields: [orgId], references: [id])
  @@index([orgId])
}

// EVERY query must include orgId
async function getProjects(orgId: string) {
  return prisma.project.findMany({ where: { orgId } });
}

Risk: Forgetting orgId in a query exposes all tenant data.

Mitigation: Middleware that injects org context:

// Extend Prisma with org isolation
const orgPrisma = (orgId: string) => prisma.$extends({
  query: {
    $allModels: {
      async findMany({ args, query }) {
        args.where = { ...args.where, orgId };
        return query(args);
      }
    }
  }
});

// Usage — orgId always applied
const db = orgPrisma(session.orgId);
const projects = await db.project.findMany();

Pattern 2: Schema-Per-Tenant

Each org gets its own PostgreSQL schema. Tables are identical, data is isolated.

-- Create schema for new org
CREATE SCHEMA org_acme;
CREATE TABLE org_acme.projects (id uuid PRIMARY KEY, name text);

-- Query org's schema
SET search_path TO org_acme;
SELECT * FROM projects;

// Switch schema based on org
async function getOrgDb(orgSlug: string) {
  await prisma.$executeRaw`SET search_path TO ${orgSlug}`;
  return prisma;
}

Pros: Strong isolation, easy per-tenant backups

Cons: Schema migrations must run N times, harder to query across orgs

Pattern 3: Database-Per-Tenant

Each org gets their own database. Maximum isolation, maximum cost.

Use when: Enterprise compliance requirements, PII regulations

Invitation System

// Create invite link
const invite = await prisma.orgInvite.create({
  data: {
    orgId,
    email,
    role: 'member',
    token: crypto.randomUUID(),
    expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000),
  }
});

// Accept invite
async function acceptInvite(token: string, userId: string) {
  const invite = await prisma.orgInvite.findUnique({ where: { token } });
  if (!invite || invite.expiresAt < new Date()) throw new Error('Invalid invite');

  await prisma.orgMember.create({
    data: { orgId: invite.orgId, userId, role: invite.role }
  });
  await prisma.orgInvite.delete({ where: { token } });
}

RBAC Within an Org

type OrgRole = 'owner' | 'admin' | 'member' | 'viewer';

const permissions: Record<OrgRole, string[]> = {
  owner:  ['*'],
  admin:  ['read', 'write', 'invite', 'remove_member'],
  member: ['read', 'write'],
  viewer: ['read'],
};

function can(role: OrgRole, action: string): boolean {
  const perms = permissions[role];
  return perms.includes('*') || perms.includes(action);
}