Multi-Tenant Database Architecture for B2B SaaS

The Multi-Tenancy Dilemma

The defining characteristic of B2B SaaS is multi-tenancy: a single instance of your software serves dozens, hundreds, or thousands of different corporate clients (tenants).

The single most critical engineering decision you will make when starting a SaaS is how to separate their data.

If a junior developer forgets to add WHERE tenant_id = ? to a single SQL query, Client A will suddenly see Client B's invoices. This is a catastrophic data breach that will destroy your startup.

At DevApps Technology, we specialize in architecting secure multi-tenant PostgreSQL databases. Here are the three primary architectural patterns.

1. Database-per-Tenant (Siloed)

In this model, every single client gets their own completely separate PostgreSQL database instance.

• Pros: Absolute, physical data isolation. If Client A needs to restore a backup from yesterday, you can restore their database without affecting Client B. It is very easy to comply with strict HIPAA or DoD security requirements.
• Cons: It is a scaling nightmare. If you have 5,000 clients, you have 5,000 databases. If you need to add a new status column to the users table, you must run a migration script 5,000 times. It is also extremely expensive in cloud hosting costs.

Verdict: Only use for high-ticket Enterprise SaaS where clients demand on-premise or VPC isolation.

2. Schema-per-Tenant (Bridge Model)

In PostgreSQL, a "Schema" is a namespace within a database. In this model, all clients share the same physical database, but each client gets their own schema (tenant_a.users, tenant_b.users).

• Pros: Good logical isolation. Your Node.js backend simply sets the search_path to the specific tenant before executing queries, meaning developers don't have to manually write WHERE tenant_id = ?.
• Cons: PostgreSQL starts to suffer performance degradation when you have thousands of schemas. Managing migrations across 1,000 schemas is still complex (usually requiring tools like Knex.js or Graphile Migrate).

Verdict: A solid middle ground, but often too complex for early-stage startups.

3. Shared Table with Row-Level Security (RLS)

This is the modern standard for 95% of B2B SaaS startups. All tenants share the exact same tables. Every single table has a tenant_id column.

Historically, this relied entirely on developers perfectly writing their ORM queries. Today, we enforce this at the database kernel level using PostgreSQL Row-Level Security (RLS).

-- Enable RLS on the invoices table
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Create a policy that forces the database to only return rows
-- where the tenant_id matches the current session variable
CREATE POLICY tenant_isolation_policy ON invoices
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

How it works in Node.js

When a user makes an API request:

1. The Node.js server verifies their JWT to find their tenant_id.
2. The server opens a transaction with PostgreSQL and executes: SET LOCAL app.current_tenant_id = '123'.
3. The server then runs a simple query: SELECT * FROM invoices.
4. The Magic: Even though the server asked for all invoices, the PostgreSQL engine intercepts the query and automatically applies the RLS policy, returning only tenant 123's invoices.

• Pros: Extremely easy to manage migrations (you only have one set of tables). Highly scalable. RLS guarantees security even if a developer writes a sloppy query.
• Cons: Requires deep understanding of PostgreSQL policies and transaction management in your ORM (Prisma/Drizzle).

Architecting a new SaaS platform? A flawed database architecture will force a complete rewrite in year two. Contact DevApps Technology to get your database schema right on day one.