The battle-tested architectural playbook for engineering enterprise B2B SaaS platforms. Learn how we implement strict multi-tenant isolation via Postgres Row-Level Security (RLS), edge domain routing, and sub-50ms query latency.
1. The Multi-Tenancy Trilemma: Database, Schema, or Row-Level Security?
Every B2B SaaS founder and engineering architect eventually faces the foundational data isolation decision: how should enterprise customer data be physically partitioned?
There are three primary architectural patterns, each with profound trade-offs across cost, operational complexity, and security guarantees:
| Multi-Tenancy Strategy | Isolation Level | Infrastructure Cost | Migration Complexity | Max Tenants Before Sharding |
|---|---|---|---|---|
| Database-per-Tenant | Maximum (Separate DB instances) | Astronomical ($150+/mo per tenant) | High (Run migrations N times) | ~500 |
| Schema-per-Tenant | Moderate (Single DB, separate schemas) | High (Pg connection pool exhaustion) | Extreme (Schema limits in Postgres) | ~2,500 |
| Row-Level Security (RLS) | High (Single shared schema + RLS policies) | Optimal ($0.01/mo per tenant) | Low (Single migration pipeline) | 500,000+ |
At The Pagal Engineers, our standard enterprise architecture relies on PostgreSQL Row-Level Security (RLS) paired with automated tenant session context injection. This provides true database-level isolation guarantees at a fraction of the operational cost of multi-database infrastructure.
2. Implementing Hardened Postgres Row-Level Security (RLS)
Application-level filtering (`WHERE tenant_id = 'xxx'`) is a ticking time bomb. A single forgotten WHERE clause in an obscure reporting query can leak confidential enterprise data across client accounts.
With Postgres RLS, the database kernel enforces isolation automatically, even if the application code attempts an unbounded `SELECT * FROM invoices`.
-- 1. Enable RLS on the table
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- 2. Define strict tenant isolation policy
CREATE POLICY tenant_isolation_policy ON invoices
FOR ALL
USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);
-- 3. Composite indexing for sub-5ms query performance
CREATE INDEX idx_invoices_tenant_created
ON invoices (tenant_id, created_at DESC);In your application data-access layer (Prisma, Drizzle, or Kysely), every transaction begins by setting the session variable:
// Secure Tenant Transaction Runner
export async function withTenantContext<T>(
tenantId: string,
operation: (tx: PrismaTransaction) => Promise<T>
): Promise<T> {
return await db.$transaction(async (tx) => {
// Set Postgres session config scoped strictly to this transaction
await tx.$executeRawUnsafe(
`SELECT set_config('app.current_tenant_id', '${tenantId}', true);`
);
return await operation(tx);
});
}3. Multi-Tenant Subdomain & Custom Domain Routing with Next.js Edge Middleware
Enterprise B2B clients demand custom branded domains (e.g., `crm.acmecorp.com`) and automated subdomains (e.g., `acme.yourplatform.com`).
Next.js App Router edge middleware allows sub-millisecond host rewriting before requests reach application page handlers:
// middleware.ts - Edge-native Multi-tenant Host Resolution
import { NextRequest, NextResponse } from 'next/server';
export const config = {
matcher: ['/((?!api/|_next/|_static/|[\\w-]+\\.\\w+).*)'],
};
export default async function middleware(req: NextRequest) {
const url = req.nextUrl;
const hostname = req.headers.get('host') || '';
// Clean root domain
const rootDomain = process.env.NEXT_PUBLIC_ROOT_DOMAIN || 'thepagalengineers.com';
const isSubdomain = hostname.endsWith(`.${rootDomain}`);
let tenantSlug = '';
if (isSubdomain) {
tenantSlug = hostname.replace(`.${rootDomain}`, '');
} else if (!hostname.includes(rootDomain) && !hostname.includes('localhost')) {
// Custom enterprise domain lookup from edge cache (Upstash Redis)
tenantSlug = await resolveCustomDomain(hostname);
}
// Rewrite internal URL structure transparently
if (tenantSlug) {
url.pathname = `/_tenants/${tenantSlug}${url.pathname}`;
return NextResponse.rewrite(url);
}
return NextResponse.next();
}4. Enterprise Identity: SCIM Provisioning & SAML SSO Integration
Closing Fortune 500 accounts requires two non-negotiable features: SAML 2.0 / OIDC Single Sign-On (Okta, Azure AD, Ping Identity) and SCIM 2.0 automated employee deprovisioning.
### Essential Enterprise Architecture Requirements:
5. Real-World Scaling Benchmarks: Handling 10M+ Requests Daily
By combining Postgres RLS, PgBouncer transaction-mode connection pooling, and multi-region Cloudflare edge caching, our SaaS platform blueprints consistently deliver:
Architecture is not about adopting every trendy tool; it is about choosing primitives with predictable performance envelopes.
Frequently Asked Questions
What is the best multi-tenancy model for an early-to-growth B2B SaaS?▾
Postgres Row-Level Security (RLS) on a unified database with dedicated composite indexes (tenant_id + entity_id). It provides enterprise-grade data isolation without the immense infrastructure cost and migration nightmare of database-per-tenant.
Does PostgreSQL Row-Level Security impact query performance under heavy load?▾
When proper composite indexes are defined on (tenant_id, ...columns), RLS overhead is negligible (typically less than 1-2%). Postgres optimizes the RLS filter directly into the query execution planner.
How do you handle custom client domains in Next.js?▾
Use Next.js Edge Middleware combined with an edge key-value cache (like Upstash Redis) to map incoming host headers to the corresponding tenant ID, then transparently rewrite requests to the internal tenant route.
How should B2B SaaS handle asynchronous background tasks without blocking web workers?▾
Decouple heavy tasks (e.g. PDF exports, bulk CSV imports, email sequences) using Redis-backed message queues like BullMQ or temporal state machines running on dedicated background worker pods.
Vishal Verma
Founding Principal Architect
Founder and Principal Systems Architect at The Pagal Engineers. Specializes in distributed multi-tenant architectures, high-throughput cloud backends, and full-stack performance optimization.