Building Multi-Tenant SaaS: Security and Data Isolation

Building the Catalyst PSA platform as a multi-tenant SaaS application was one of our biggest architectural challenges. With hundreds of enterprise clients, each with sensitive financial and operational data, we couldn't afford a single security mistake. Over three years of running in production, our multi-tenant architecture has proven secure, scalable, and maintainable.

Here's everything we learned about building multi-tenant SaaS applications the right way.

What is Multi-Tenancy?

Multi-tenancy means running a single application instance that serves multiple customers (tenants), while keeping each tenant's data completely isolated. It's the foundation of modern SaaS.

The alternative approaches are:

Single-tenant: Each customer gets their own application instance and database
Multi-instance: Shared application code but separate databases per customer
Multi-tenant: Shared application and database with logical data isolation

We chose multi-tenancy because it offers:

Lower infrastructure costs (one database vs. hundreds)
Easier maintenance (one codebase to update)
Simpler deployment (one application to deploy)
Better resource utilization (shared compute)

But it requires solving hard problems: data isolation, security, and performance at scale.

Multi-Tenancy Models: Choosing the Right Approach

There are three main multi-tenancy models for databases:

1. Separate Database Per Tenant

Each tenant gets their own database. Maximum isolation but expensive and hard to manage at scale.

tenant_1_db
tenant_2_db
tenant_3_db
...

We ruled this out because managing hundreds of databases would be operational nightmare. Schema migrations alone would take hours.

2. Separate Schema Per Tenant

One database, but each tenant gets their own schema (namespace).

Database: rook_psa
  - tenant_1 schema
  - tenant_2 schema
  - tenant_3 schema

Better than separate databases, but still creates hundreds of schemas. PostgreSQL can handle this, but query planning and connection pooling become complex.

3. Shared Schema with Tenant ID Column

All tenants share the same tables, with a tenant_id column on every table for isolation.

CREATE TABLE projects (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  name VARCHAR(255) NOT NULL,
  -- other columns
);

This is what we chose. It's the most scalable approach and simplest to manage, but requires discipline to prevent data leakage.

Implementing Row-Level Security

Our first line of defense was PostgreSQL's Row-Level Security (RLS). RLS enforces tenant isolation at the database level, so even a buggy query can't leak data across tenants.

Setting Up RLS

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Create policy to only show rows for current tenant
CREATE POLICY tenant_isolation_policy ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
 
-- Policy applies to all operations
CREATE POLICY tenant_isolation_policy_all ON projects
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

Before executing any queries, we set the current tenant:

export class TenantContextInterceptor implements NestInterceptor {
  intercept(context: ExecutionContext, next: CallHandler): Observable<any> {
    const request = context.switchToHttp().getRequest();
    const tenantId = request.user.tenantId;
 
    // Set PostgreSQL session variable
    return from(this.setTenantContext(tenantId)).pipe(
      mergeMap(() => next.handle())
    );
  }
 
  private async setTenantContext(tenantId: string): Promise<void> {
    await this.db.execute(
      sql`SET LOCAL app.current_tenant_id = ${tenantId}`
    );
  }
}

Now every query automatically filters by tenant ID. Even if we write:

const projects = await this.db.select().from(projects);

PostgreSQL RLS ensures we only get the current tenant's projects. This is defense in depth—even bugs can't leak data.

Performance Considerations with RLS

RLS has a cost—PostgreSQL has to apply the policy on every query. We addressed this with:

Indexes on tenant_id: Every table has an index on (tenant_id, id)

CREATE INDEX idx_projects_tenant ON projects(tenant_id, id);

Partition tables by tenant_id for huge tables:

CREATE TABLE time_entries (
  id UUID,
  tenant_id UUID,
  -- other columns
) PARTITION BY HASH (tenant_id);

Connection pooling per tenant for large customers:

export class TenantAwareConnectionPool {
  private pools: Map<string, Pool> = new Map();
 
  getConnection(tenantId: string): Pool {
    if (this.isLargeTenant(tenantId)) {
      // Dedicated pool for large tenants
      return this.getOrCreatePool(tenantId);
    }
    // Shared pool for small tenants
    return this.getSharedPool();
  }
}

Application-Level Tenant Isolation

RLS is our safety net, but we also enforce tenant isolation in application code for performance and clarity.

Tenant-Scoped Repositories

Every repository automatically scopes queries to the current tenant:

export class ProjectRepository {
  constructor(
    private readonly db: DrizzleService,
    private readonly tenantContext: TenantContext
  ) {}
 
  async findById(id: string): Promise<Project | null> {
    const result = await this.db
      .select()
      .from(projects)
      .where(
        and(
          eq(projects.id, id),
          eq(projects.tenantId, this.tenantContext.currentTenantId)
        )
      )
      .limit(1);
 
    return result[0] ? this.toDomain(result[0]) : null;
  }
 
  async save(project: Project): Promise<void> {
    // Ensure tenant ID is set
    const data = {
      ...this.toPersistence(project),
      tenantId: this.tenantContext.currentTenantId,
    };
 
    await this.db
      .insert(projects)
      .values(data)
      .onConflictDoUpdate({
        target: [projects.id],
        set: data,
      });
  }
}

Notice how tenantId is always included in queries and set on inserts. This is redundant with RLS, but makes our code explicit and prevents performance penalties from RLS policy checks.

Tenant Context from JWT

We extract tenant ID from authenticated user's JWT token:

@Injectable()
export class JwtStrategy extends PassportStrategy(Strategy) {
  validate(payload: JwtPayload): AuthenticatedUser {
    return {
      userId: payload.sub,
      tenantId: payload.tenantId,
      email: payload.email,
      roles: payload.roles,
    };
  }
}

Every request carries tenant context. We never trust client-provided tenant IDs—always from the authenticated token.

Preventing Cross-Tenant Data Leakage

Even with RLS and scoped repositories, we added additional safeguards:

1. Foreign Key Validation

When creating relationships across entities, validate tenant consistency:

export class AssignConsultantToProjectUseCase {
  async execute(projectId: string, consultantId: string): Promise<void> {
    const project = await this.projectRepository.findById(projectId);
    const consultant = await this.consultantRepository.findById(consultantId);
 
    // This check is critical!
    if (project.tenantId !== consultant.tenantId) {
      throw new TenantMismatchError(
        'Cannot assign consultant from different tenant'
      );
    }
 
    await this.assignmentRepository.create(projectId, consultantId);
  }
}

2. Audit Logging

We log every cross-tenant attempt (which should never happen):

@Injectable()
export class TenantSecurityAuditor {
  logCrossTenantAttempt(
    userId: string,
    userTenantId: string,
    attemptedTenantId: string,
    resource: string
  ): void {
    this.logger.error('SECURITY: Cross-tenant access attempt', {
      userId,
      userTenantId,
      attemptedTenantId,
      resource,
      timestamp: new Date(),
    });
 
    // Trigger security alert
    this.alertingService.sendSecurityAlert({
      severity: 'HIGH',
      type: 'CROSS_TENANT_ACCESS_ATTEMPT',
      details: { userId, resource },
    });
  }
}

3. Integration Tests

We wrote extensive tests to ensure tenant isolation:

describe('Tenant Isolation', () => {
  it('should not allow access to another tenant projects', async () => {
    const tenant1Token = await createAuthToken({ tenantId: 'tenant-1' });
    const tenant2Token = await createAuthToken({ tenantId: 'tenant-2' });
 
    // Create project as tenant 1
    const project = await request(app)
      .post('/projects')
      .set('Authorization', `Bearer ${tenant1Token}`)
      .send({ name: 'Tenant 1 Project' });
 
    // Try to access as tenant 2
    const response = await request(app)
      .get(`/projects/${project.body.id}`)
      .set('Authorization', `Bearer ${tenant2Token}`);
 
    expect(response.status).toBe(404); // Not found, not 403
  });
});

We return 404, not 403, to avoid leaking that the resource exists.

Managing Shared Resources

Some resources need to be shared across tenants (like system users, feature flags, or lookup tables).

Approach 1: No tenant_id Column

For truly global data:

CREATE TABLE system_feature_flags (
  id UUID PRIMARY KEY,
  feature_name VARCHAR(100) NOT NULL,
  enabled BOOLEAN DEFAULT false
  -- No tenant_id
);
 
-- Disable RLS for system tables
ALTER TABLE system_feature_flags DISABLE ROW LEVEL SECURITY;

Approach 2: NULL tenant_id for Shared Data

For data that can be shared or tenant-specific:

CREATE TABLE industry_codes (
  id UUID PRIMARY KEY,
  tenant_id UUID, -- NULL for shared codes
  code VARCHAR(50),
  description TEXT
);
 
CREATE POLICY industry_codes_policy ON industry_codes
  USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id')::uuid);

This lets tenants see both shared industry codes and their custom ones.

Handling Schema Migrations

With shared schemas, migrations affect all tenants simultaneously. We developed a safe migration process:

1. Backward-Compatible Changes Only

-- ✅ Safe: Adding nullable column
ALTER TABLE projects ADD COLUMN archived_at TIMESTAMP NULL;
 
-- ❌ Unsafe: Adding non-nullable column
ALTER TABLE projects ADD COLUMN status VARCHAR(50) NOT NULL;
 
-- ✅ Safe version:
ALTER TABLE projects ADD COLUMN status VARCHAR(50) NULL DEFAULT 'active';
-- Then deploy code that sets status
-- Then make column non-nullable in next migration

2. Blue-Green Deployments

We run two application versions during migrations:

Deploy new code (compatible with old schema)
Run migration
Shift traffic to new code
Shutdown old code

3. Data Migrations in Batches

For large data migrations, we process by tenant:

async function migrateProjectStatuses() {
  const tenants = await db.select().from(tenants);
 
  for (const tenant of tenants) {
    await db.transaction(async (tx) => {
      await tx
        .update(projects)
        .set({ status: 'active' })
        .where(
          and(
            eq(projects.tenantId, tenant.id),
            isNull(projects.status)
          )
        );
    });
 
    console.log(`Migrated tenant ${tenant.id}`);
  }
}

This prevents long-running transactions that lock all tenants.

Performance at Scale

With 300+ tenants and millions of rows, performance required careful optimization:

1. Tenant-Aware Indexing

Every query filters by tenant_id, so it's the first column in composite indexes:

-- Good
CREATE INDEX idx_projects_tenant_status ON projects(tenant_id, status);
 
-- Bad
CREATE INDEX idx_projects_status_tenant ON projects(status, tenant_id);

2. Tenant-Based Sharding for Large Customers

Our largest customers got dedicated database instances:

export class TenantRouter {
  getDatabaseConnection(tenantId: string): DatabaseConnection {
    const tenant = this.getTenant(tenantId);
 
    if (tenant.dedicatedDatabase) {
      return this.getDedicatedConnection(tenant.databaseUrl);
    }
 
    return this.getSharedConnection();
  }
}

3. Caching with Tenant Isolation

Cache keys include tenant ID:

const cacheKey = `tenant:${tenantId}:project:${projectId}`;
const cached = await this.redis.get(cacheKey);

This prevents cross-tenant cache pollution.

Lessons Learned

RLS is essential: It saved us from several bugs that could have been security incidents
Make tenant_id explicit: Include it in every query for performance and clarity
Test tenant isolation religiously: It's too important to trust
Start with shared schema: You can always shard later if needed
Monitor per-tenant metrics: Detect noisy neighbors and performance issues early

Conclusion

Building multi-tenant SaaS is complex, but with the right architecture, it's secure and scalable. Row-Level Security, tenant-scoped repositories, explicit tenant validation, and comprehensive testing gave us confidence serving hundreds of enterprise clients from a single application instance.

After 27 years of building applications, I can say that multi-tenancy done right is the most cost-effective way to deliver SaaS at scale. The key is paranoia about data isolation and defense in depth at every layer.