I’ve been building SaaS applications for years, and one question keeps coming up: how do you securely isolate customer data while keeping costs manageable? That challenge led me to explore PostgreSQL Row-Level Security combined with NestJS and Prisma. Today, I’ll share practical insights for building scalable multi-tenant systems that handle data isolation at the database layer. This approach has served me well in production environments with thousands of tenants.

Multi-tenancy requires careful architectural decisions. Should you use separate databases for each customer? Add tenant ID columns everywhere? Or leverage database-level security? Let’s compare the options. Database-per-tenant offers maximum isolation but becomes expensive to manage. Adding tenant IDs to every table is simpler but risks accidental data leaks. PostgreSQL RLS strikes a balance by enforcing data separation directly in your database.

-- Essential RLS setup
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

How do we implement this in a NestJS application? First, we establish the project foundation. Install core dependencies:

npm install @nestjs/core prisma @nestjs/jwt passport

Our directory structure centers around tenant isolation:

src/
├── middleware/   # Tenant context handlers
├── guards/       # Authentication checks
├── tenants/      # Onboarding logic
└── modules/      # Tenant-scoped resources

PostgreSQL configuration is critical. We enable RLS on tenant-specific tables and create context-setting functions:

CREATE OR REPLACE FUNCTION set_tenant_context(tenant_uuid UUID)
RETURNS void AS $$
BEGIN
  PERFORM set_config('app.tenant_id', tenant_uuid::text, true);
END;
$$ LANGUAGE plpgsql;

In Prisma, we model tenant relationships explicitly. Notice how every tenant-scoped table references the tenants table:

model Tenant {
  id        String   @id @default(uuid())
  name      String
  users     User[]
}

model User {
  id        String   @id @default(uuid())
  tenant    Tenant   @relation(fields: [tenantId], references: [id])
  tenantId  String
}

Now comes the magic piece: tenant-aware middleware. This intercepts requests and sets the PostgreSQL context:

// tenant.middleware.ts
import { PrismaService } from '../prisma.service';

@Injectable()
export class TenantMiddleware implements NestMiddleware {
  constructor(private readonly prisma: PrismaService) {}

  async use(req: Request, res: Response, next: NextFunction) {
    const tenantId = req.headers['x-tenant-id'] as string;
    
    if (!tenantId) throw new UnauthorizedException('Tenant missing');
    
    await this.prisma.$executeRaw`SELECT set_tenant_context(${tenantId}::uuid)`;
    next();
  }
}

But how do we authenticate users across tenants? JSON Web Tokens (JWT) with tenant context solve this elegantly:

// auth.service.ts
@Injectable()
export class AuthService {
  constructor(private prisma: PrismaService) {}

  async login(email: string, password: string) {
    const user = await this.prisma.user.findUnique({ where: { email } });
    if (!user || !verifyPassword(password, user.passwordHash)) 
      throw new UnauthorizedException();

    const payload = { sub: user.id, tenantId: user.tenantId };
    return { access_token: this.jwtService.sign(payload) };
  }
}

Tenant onboarding must be seamless. Our service handles database setup without manual intervention:

// tenant.service.ts
async onboardTenant(name: string, adminEmail: string) {
  const tenant = await this.prisma.tenant.create({ data: { name } });
  
  // Apply RLS policies automatically
  await this.prisma.$executeRaw`
    CREATE POLICY tenant_${tenant.id}_isolation 
    ON orders USING (tenant_id = ${tenant.id}::uuid)
  `;
  
  await this.createAdminUser(tenant.id, adminEmail);
  return tenant;
}

Performance optimization becomes crucial at scale. We leverage PostgreSQL indexes on tenant IDs:

CREATE INDEX concurrently users_tenant_id_idx ON users(tenant_id);

For testing, we verify isolation works as intended:

it('prevents cross-tenant data access', async () => {
  // Create two tenants
  const tenantA = await createTestTenant();
  const tenantB = await createTestTenant();
  
  // Set tenant A context
  await setTenantContext(tenantA.id);
  await createOrder('order_a');
  
  // Switch to tenant B
  await setTenantContext(tenantB.id);
  const orders = await getOrders();
  
  expect(orders).toHaveLength(0); // Should see no orders
});

Common pitfalls? Forgetting to set tenant context in background jobs tops the list. Always pass tenant IDs explicitly in async operations. Another gotcha: connection pooling. Use middleware that sets context per-request rather than per-connection.

What about schema migrations? Prisma Migrate handles tenant-agnostic changes well. For tenant-specific adjustments, we script custom migrations that loop through tenants:

// migration-script.ts
const tenants = await getAllTenants();
for (const tenant of tenants) {
  await setTenantContext(tenant.id);
  await this.prisma.$executeRaw`ALTER TABLE orders ADD COLUMN priority INT`;
}

I’ve found this architecture scales beautifully to thousands of tenants while keeping operational costs predictable. The database handles heavy lifting for data isolation, while NestJS provides clean abstractions. Prisma bridges both worlds elegantly.

This approach transformed how I build SaaS applications. If you implement one thing today, make it PostgreSQL RLS. It pays long-term security dividends. What challenges have you faced with multi-tenancy? Share your experiences below - I read every comment. If this helped you, consider sharing it with others facing similar architectural decisions.