Multi-Tenant SaaS Architecture: Row-Level Security, Schema Isolation, and High-Throughput API Contracts

Architecting a B2B SaaS platform begins with a fundamental decision that dictates your infrastructure costs, compliance posture, and database query complexity for the next five years: How will you isolate tenant data?

If your tenant isolation strategy is flawed, a single careless query can expose one enterprise customer's private financial records to another — an instant, catastrophic SOC2 and GDPR breach. Furthermore, a single "noisy neighbor" running massive analytical exports can monopolize CPU resources and degrade performance for all other tenants.

At Codedway, we have architected multi-tenant backends serving both high-volume self-serve SaaS and strict Fortune 500 enterprise clients.

Here is our comprehensive architectural guide to Multi-Tenant Isolation Strategies and designing high-throughput API Contracts using REST, GraphQL, and gRPC.

1. Comparing the Three Multi-Tenancy Models

Every multi-tenant architecture represents a trade-off between Isolation Guarantee, Infrastructure Cost, and Operational Complexity.

Model 1: Shared Database, Shared Schema (Row-Level Security)
┌─────────────────────────────────────────────────────────┐
│                     POSTGRESQL DB                       │
│  ┌───────────────────────────────────────────────────┐  │
│  │ Table: Orders (Contains tenant_id on every row)   │  │
│  │ [ Tenant A ] [ Tenant B ] [ Tenant C ]            │  │
│  └───────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────┘

Model 2: Shared Database, Separate Schemas
┌─────────────────────────────────────────────────────────┐
│                     POSTGRESQL DB                       │
│  ┌─────────────────────────┐   ┌─────────────────────┐  │
│  │ Schema: tenant_a        │   │ Schema: tenant_b    │  │
│  │ Tables: orders, users   │   │ Tables: orders, ... │  │
│  └─────────────────────────┘   └─────────────────────┘  │
└─────────────────────────────────────────────────────────┘

Model 3: Database-per-Tenant (Isolated Physical Instances)
┌─────────────────────────┐       ┌─────────────────────────┐
│     POSTGRESQL DB A     │       │     POSTGRESQL DB B     │
│  (Dedicated instance)   │       │  (Dedicated instance)   │
└─────────────────────────┘       └─────────────────────────┘

Trade-Off Matrix

| Dimension | Model 1: Shared DB + RLS | Model 2: Schema-per-Tenant | Model 3: DB-per-Tenant | |---|---|---|---| | Data Isolation | Logical (Enforced by SQL policy) | Logical (Database namespace) | Physical (Separate disk/VPC) | | Infrastructure Cost | Lowest (Maximum density) | Low-Moderate | Highest (Cost per tenant) | | Schema Migrations | Instant (Single migration run) | Complex (Loop through 1,000 schemas) | Complex (Fleet orchestrator required) | | Enterprise Compliance | Acceptable for 90% of SaaS | Good (Dedicated backup restore) | Highest (Healthcare/GovCloud required) | | Max Practical Tenants | 1,000,000+ | ~2,500 (PostgreSQL catalog limit) | Hundreds (Connection pool limits) |

2. Deep Dive: Hardening Shared DB with PostgreSQL Row-Level Security (RLS)

For 90% of B2B SaaS companies, Shared Database with PostgreSQL Row-Level Security (RLS) is the gold standard. RLS shifts tenant boundary enforcement from fragile application code directly into the PostgreSQL kernel.

Even if an engineer writes a careless query missing a WHERE tenant_id = '...' filter, PostgreSQL intercepts the query and automatically enforces the isolation predicate at the disk page access level.

Production RLS Setup Script

-- 1. Enable RLS on core tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing_records ENABLE ROW LEVEL SECURITY;

-- 2. Define a secure app session parameter
-- The application connection pool sets this variable on every checkout
CREATE OR REPLACE FUNCTION current_app_tenant() RETURNS UUID AS $$
  SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::UUID;
$$ LANGUAGE SQL STABLE;

-- 3. Define the strict tenant isolation policy
CREATE POLICY tenant_isolation_policy ON projects
  FOR ALL
  USING (tenant_id = current_app_tenant())
  WITH CHECK (tenant_id = current_app_tenant());

-- 4. Force RLS enforcement even for table owners
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Application Connection Middleware in TypeScript

When your backend borrows a database connection from the connection pool, it must atomically bind the tenant context:

import { Pool, PoolClient } from "pg";

export class TenantBoundDatabase {
  constructor(private pool: Pool) {}

  async runInTenantContext<T>(
    tenantId: string,
    callback: (client: PoolClient) => Promise<T>
  ): Promise<T> {
    const client = await this.pool.connect();
    try {
      // Begin transaction and inject local tenant variable
      await client.query("BEGIN");
      
      // Set session variable strictly scoped to this transaction
      await client.query(
        "SET LOCAL app.current_tenant_id = $1", 
        [tenantId]
      );

      const result = await callback(client);
      
      await client.query("COMMIT");
      return result;
    } catch (error) {
      await client.query("ROLLBACK");
      throw error;
    } finally {
      // Return cleaned connection to pool
      client.release();
    }
  }
}

3. High-Throughput API Contracts: REST vs. GraphQL vs. gRPC

A multi-tenant system requires strict, predictable API contracts. Depending on the client and throughput requirements, choose the right protocol:

When to Use REST

• External Public Developer APIs: REST with JSON OpenAPI specifications remains the universal lingua franca for third-party integrations, webhooks, and partner developers.

When to Use GraphQL

• Complex Internal Frontend Dashboards: Highly flexible UI views that require aggregating data across projects, users, billing, and settings in a single network round-trip.
• Guardrail: Always enforce query depth limits and complexity budgets; otherwise, an aggressive user can craft an exponentially nested query that brings down your database.

When to Use gRPC

• High-Throughput Inter-Service Microservices: Internal microservice communication, background workers, and telemetry ingest pipelines.
• Binary Protocol Buffers over HTTP/2 provide 7x faster serialization and drastically smaller packet sizes compared to JSON.

Example gRPC Protocol Buffer Contract

syntax = "proto3";

package telemetry.v1;

option go_package = "github.com/codedway/telemetry/v1";

service TelemetryService {
  rpc RecordEvent (RecordEventRequest) returns (RecordEventResponse);
  rpc StreamTelemetry (stream StreamTelemetryRequest) returns (stream StreamTelemetryResponse);
}

message RecordEventRequest {
  string tenant_id = 1;
  string device_id = 2;
  int64 timestamp_epoch_ms = 3;
  bytes payload = 4;
}

message RecordEventResponse {
  bool acknowledged = 1;
  int32 processing_time_us = 2;
}

4. Mitigating the Noisy Neighbor: Token Bucket Rate Limiting in Redis

To protect multi-tenant infrastructure, you must enforce rate limiting partitioned by tenant_id at your API Gateway:

// Token Bucket Algorithm using Redis Lua Script for atomicity
const RATE_LIMIT_LUA = `
  local key = KEYS[1]
  local limit = tonumber(ARGV[1])
  local current = tonumber(redis.call('get', key) or "0")

  if current + 1 > limit then
    return 0 -- Rate limit exceeded
  else
    redis.call('incrby', key, 1)
    if current == 0 then
      redis.call('expire', key, 60) -- 60-second window
    end
    return 1 -- Allowed
  end
`;

By coupling PostgreSQL Row-Level Security with atomic Redis rate limiting and typed API contracts, you build a multi-tenant foundation capable of serving thousands of organizations with enterprise-grade security and predictable performance.