# Multi-Tenant SaaS Database Design: Tenant IDs, Isolation and Row-Level Security
> Database per tenant, schema per tenant or shared tables? Honest trade-offs, why tenant_id usually wins early, and how Postgres RLS stops data leaks.
- Author: Tai Pham Phu
- Published: 14 Oct 2026
- Tags: Backend, Architecture, System Design, Software Design, PostgreSQL, Multi-Tenancy
- URL: https://phamphutai.com/2026/10/14/multi-tenant-saas-database-design/
Every multi-tenant SaaS has the same nightmare bug, and it's one line long.
```sql
SELECT * FROM invoices WHERE status = 'overdue';
```
Nothing is wrong with the syntax. It runs fast. The test passes, because the test database only has one customer in it. Then it ships, and a customer opens their "overdue invoices" page and sees invoices from forty other companies.
The missing piece is `AND tenant_id = $1`. One forgotten condition, and you have a data leak, an apology email, and possibly a legal problem.
A **tenant** is one customer account in your product: a company, a team, a workspace. **Multi-tenant** means many tenants share the same running application. Most B2B SaaS is built this way, because running a separate copy of the whole product for every customer is expensive and slow to maintain.
This post covers how to choose an isolation model, why most early products should pick the simplest one, and how to make it safe with layers of defence, ending with Postgres **Row-Level Security** as the net under everything.
---
## The three common models
**1. Database per tenant.** Every customer gets their own database. Tenant A's data and tenant B's data never share a table, a schema or even a connection string.
**2. Schema per tenant.** One database, but each customer gets their own Postgres **schema** (a named folder of tables inside a database). `tenant_acme.invoices`, `tenant_globex.invoices`, and so on.
**3. Shared tables with a `tenant_id` column.** One database, one set of tables. Every tenant-owned row carries a `tenant_id` saying who it belongs to. Isolation is done by filtering.
Here's how they compare. None of them is free.
| | Database per tenant | Schema per tenant | Shared tables + `tenant_id` |
|---|---|---|---|
| **Isolation** | Strongest. A bad query can't cross tenants. | Strong, if every query uses the right schema. | Weakest by default. Depends on every query filtering correctly. |
| **Cost** | Highest. Each database has overhead, even when idle. | Medium. One server, but many objects to manage. | Lowest. One set of tables, one connection pool. |
| **Migrations** | Run N times. Some tenants end up on old versions. | Run N times, inside one database. Slow at scale. | Run once. |
| **Noisy neighbours** | Easy to move a heavy tenant to its own server. | Heavy tenants still share one database server. | Heavy tenants share tables, indexes and caches with everyone. |
| **Per-tenant restore** | Easy. Restore one database. | Possible. Dump and restore one schema. | Hard. You have to extract one tenant's rows from a shared backup. |
| **Cross-tenant analytics** | Painful. Query N databases or copy data out. | Painful. Query N schemas. | Easy. It's one `GROUP BY tenant_id`. |
| **Onboarding a tenant** | Create a database, run migrations, set up connections. | Create a schema, run migrations. | Insert one row in `tenants`. |
A **noisy neighbour** is one tenant whose heavy usage slows down everyone else on the same hardware. A **migration** is a script that changes your database structure, like adding a column.
---
## Why most early SaaS should start with shared tables
If you're a small team building a product with tens or hundreds of customers, I think the shared-table model is the right default. Not because it's the most secure out of the box, but because its weaknesses are fixable and its strengths matter most when you're small.
- **You'll change the schema a lot.** Early products add columns every week. Running a migration once is very different from running it 400 times and handling the 3 that failed.
- **You'll want to see across tenants.** "How many customers used feature X this week?" is one query, not a script that loops through databases.
- **Costs stay flat.** A free-trial tenant who never comes back costs you a few rows, not a database.
Stronger isolation makes sense with a concrete reason: a large customer who contractually requires their own database, data that must stay in one country, or a tenant big enough to need its own hardware. Moving one tenant out later is real work, but you do it when someone is paying for it.
In practice, the more common mistake goes the other way: picking the "enterprise-grade" option on day one, for customers who don't exist yet. The same instinct that leads teams to [start with a modular monolith instead of microservices](https://phamphutai.com/2026/10/14/start-with-a-modular-monolith/) applies here: pick the simple shape, and keep the boundaries clean so you can split later.
The price of the shared model is that one missing `WHERE` clause. So the rest of this post is about making that mistake hard to make, and harmless when it happens anyway.
---
## Layer 1: `tenant_id` on every tenant-owned table
Put `tenant_id` directly on every table that holds tenant data. Not just the "top" tables. All of them.
It's tempting to skip it on child tables, since an `invoice_lines` row belongs to an invoice that already has a `tenant_id`. But then every query on `invoice_lines` needs a join to find the owner, and every forgotten join is a leak. Repeating the column is cheap.
```sql
CREATE TABLE tenants (
id uuid PRIMARY KEY,
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE invoices (
id uuid PRIMARY KEY,
tenant_id uuid NOT NULL REFERENCES tenants (id),
number text NOT NULL,
status text NOT NULL,
total_cents bigint NOT NULL
);
```
`NOT NULL` matters. A row with no tenant is a row nobody owns, and your filters will either hide it forever or, worse, it turns up in whatever code path forgot to filter.
Some tables are genuinely global, like the `tenants` table itself or a list of currencies. Be suspicious every time someone adds a new one.
---
## Layer 2: indexes and unique constraints that include `tenant_id`
Almost every query in a multi-tenant app starts with "for this tenant." Your indexes should match that.
```sql
-- Most lists are "this tenant's invoices, newest first, filtered by status"
CREATE INDEX invoices_tenant_status_idx ON invoices (tenant_id, status);
-- Invoice numbers are unique per tenant, not globally
ALTER TABLE invoices
ADD CONSTRAINT invoices_tenant_number_key UNIQUE (tenant_id, number);
```
The unique constraint is the one people get wrong. If you write `UNIQUE (number)`, then tenant B can't create invoice `INV-001` because tenant A already has one. Worse, the error message tells tenant B that a number exists somewhere else. Postgres's own docs warn about this: uniqueness and foreign-key checks bypass row security, so they can reveal that a value exists in rows you can't see. Scoping uniqueness to the tenant removes most of that problem.
---
## Layer 3: foreign keys that include `tenant_id`
Here's a subtler leak. An invoice line points to a product:
```sql
-- Before: only checks that the product exists somewhere
product_id uuid NOT NULL REFERENCES products (id)
```
Postgres checks that the product exists, not that it belongs to the **same tenant**. If your API accepts a `product_id` from the request body, tenant A can attach tenant B's product to an invoice and read its name and price.
The fix is a **composite foreign key**: a reference made of two columns instead of one.
```sql
-- The parent needs a unique key on (tenant_id, id) to be referenced
ALTER TABLE products
ADD CONSTRAINT products_tenant_id_id_key UNIQUE (tenant_id, id);
CREATE TABLE invoice_lines (
id uuid PRIMARY KEY,
tenant_id uuid NOT NULL REFERENCES tenants (id),
invoice_id uuid NOT NULL,
product_id uuid NOT NULL,
quantity int NOT NULL,
FOREIGN KEY (tenant_id, invoice_id) REFERENCES invoices (tenant_id, id),
FOREIGN KEY (tenant_id, product_id) REFERENCES products (tenant_id, id)
);
```
(`invoices` needs the same `UNIQUE (tenant_id, id)` constraint.) Now the database itself refuses a line whose product belongs to a different tenant. A whole class of bugs becomes a constraint error.
---
## Layer 4: a query layer that requires a tenant
The first three layers are about the schema. This one is about your code: **it should be impossible to call a repository without saying which tenant you're acting for.** Not "remember to pass it." Impossible to forget.
Before, the tenant is just another optional filter:
```ts
// Before: easy to call without a tenant, easy to forget the WHERE
async function findOverdueInvoices(db: Pool) {
const { rows } = await db.query(
"SELECT * FROM invoices WHERE status = 'overdue'",
);
return rows;
}
```
After, the tenant context is a required argument, and the repository builds the filter itself:
```ts
type TenantContext = { readonly tenantId: string };
class InvoiceRepository {
constructor(private readonly db: PoolClient, private readonly ctx: TenantContext) {}
async findOverdue() {
const { rows } = await this.db.query(
"SELECT * FROM invoices WHERE tenant_id = $1 AND status = 'overdue'",
[this.ctx.tenantId],
);
return rows;
}
}
```
You can't construct the repository without a `TenantContext`, and you get the context from one place: your authentication middleware, after it has checked who the user is and which tenant they belong to. Never take `tenantId` from the request body or a query string.
This fits naturally with the layering in [Clean Architecture in Practice](https://phamphutai.com/2026/03/14/clean-architecture-in-practice-how-to-structure-large-backend-systems/): the use case receives a tenant context, the repository interface requires it, and the business logic never builds SQL by hand.
It's a strong layer, but it's still code written by humans. Someday someone will write a raw query for a report, in a hurry. That's what the last layer is for.
---
## Layer 5: Postgres Row-Level Security as the safety net
**Row-Level Security (RLS)** is a Postgres feature that lets you attach rules, called **policies**, to a table. Postgres then adds those rules to every query on that table automatically. Even `SELECT * FROM invoices` with no `WHERE` clause only returns the rows the policy allows.
The trick for multi-tenancy is to make the policy compare each row's `tenant_id` with a setting that your app sets at the start of every transaction.
The app sets the tenant once per transaction. The policy filters every query on the table, including the ones that forgot to.
### Turning it on
```sql
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id')::uuid);
```
A few details worth understanding:
- **`app.tenant_id` is a custom setting.** Postgres accepts any two-part dotted name as a placeholder, so you don't need to define it first.
- **If the tenant was never set, the query errors.** `current_setting` throws when the setting doesn't exist. On a pooled connection where it was set in an earlier transaction, it typically comes back as an empty string instead, and the `::uuid` cast fails. Either way, forgetting the tenant fails loudly. That's what you want, so don't "fix" it with `missing_ok`.
- **With no `FOR` clause, the policy covers all commands, and `USING` doubles as `WITH CHECK`.** `USING` filters the rows you can see, update or delete. `WITH CHECK` limits the rows you can write. So a `SELECT` silently skips other tenants' rows, and an `INSERT` with someone else's `tenant_id` fails.
- **RLS with no policy means default deny.** No rows are visible. Good to know when rolling out table by table.
### Setting the tenant per transaction
You set the value inside a transaction, with `SET LOCAL` or with `set_config(..., true)`:
```sql
BEGIN;
SET LOCAL app.tenant_id = '6f1c2b9e-...';
-- or, the same thing, but usable with a bind parameter:
SELECT set_config('app.tenant_id', '6f1c2b9e-...', true);
SELECT * FROM invoices; -- only this tenant's rows
COMMIT; -- the setting disappears here
```
The third argument to `set_config` is `is_local`. When it's `true`, the value only lasts until the end of the current transaction, committed or not, exactly like `SET LOCAL`. Outside a transaction block, `SET LOCAL` just emits a warning and does nothing.
In application code I prefer `set_config`, because it's an ordinary function and accepts a bind parameter like `$1`. `SET` doesn't, so you'd end up building SQL strings.
### The gotchas
**1. Table owners bypass RLS by default.** From the Postgres docs: the table's owner is normally not subject to row security policies. If your app connects as the role that ran the migrations, which is common in small projects, your policies do nothing. Two fixes, and I'd do both:
- add `FORCE ROW LEVEL SECURITY` on the table, which makes the owner subject to the policies too;
- better, have your app connect as a **separate role** that doesn't own the tables, and keep the owner role for migrations.
**2. Superusers and `BYPASSRLS` roles always bypass it.** No policy and no `FORCE` changes that. If your app connects as `postgres`, RLS is decoration. Check what role your production app actually uses.
**3. Connection pooling.** A pool reuses each database connection for many requests. A session-level `SET app.tenant_id = 'A'` would stay on the connection after your request, and the next request to borrow it, maybe for tenant B, could run as tenant A.
With `set_config(..., true)`, the value dies with the transaction, and the connection goes back clean. It also fits external poolers: PgBouncer in **transaction pooling** mode only lends a server connection for one transaction, and its docs list `SET` as unsupported in that mode.
### A `withTenant` helper in TypeScript
Here's the helper that ties it together, using `node-postgres` (the `pg` package). Its docs are clear that all statements in a transaction must run on the **same client**, so you check one out of the pool rather than using `pool.query`.
```ts
import { Pool, type PoolClient } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export async function withTenant(
tenantId: string,
fn: (client: PoolClient) => Promise,
): Promise {
const client = await pool.connect();
try {
await client.query("BEGIN");
await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
const result = await fn(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK");
throw err;
} finally {
client.release();
}
}
```
A request handler takes `tenantId` from the auth middleware and calls `withTenant(tenantId, (client) => new InvoiceRepository(client, { tenantId }).findOverdue())`. The repository still filters by `tenant_id` itself. **RLS is the net, not the floor**, and your code stays correct even if RLS is accidentally left off somewhere.
---
## Tests that try to break in
Most test suites have one tenant in the database, so they can't catch the bug this post is about. Add tests that try to cross the line:
- **Create two tenants** with similar data, then read as A and assert you never see B, on every list endpoint.
- **Try to write across tenants.** Use tenant B's `product_id` in tenant A's invoice request. Expect a 404 or a constraint error, never a success.
- **Test RLS directly.** As the app role, set the tenant to A, run `SELECT * FROM invoices` with no filter, and assert only A's rows return. With no tenant set, assert it fails.
- **Check the role.** Assert the app's role isn't a superuser, lacks `BYPASSRLS` and doesn't own the tables. Boring, cheap, and it protects the whole safety net.
---
## Background jobs must carry `tenant_id`
HTTP requests get their tenant from authentication. Background jobs don't have a logged-in user, so they need the tenant passed explicitly.
```ts
type SendInvoiceReminderJob = {
tenantId: string;
invoiceId: string;
};
async function handleSendInvoiceReminder(job: SendInvoiceReminderJob) {
await withTenant(job.tenantId, async (client) => {
const repo = new InvoiceRepository(client, { tenantId: job.tenantId });
// ...load the invoice, send the reminder
});
}
```
Every job payload that touches tenant data includes `tenantId`, and the handler uses `withTenant` like a request does. Don't let a worker load a row by ID alone and "figure out" the tenant from it. That's the missing-`WHERE` bug in a different hat.
Jobs that span tenants, like a nightly billing run, should loop over tenants and process each inside its own `withTenant` call. I go deeper into queues, retries and the outbox pattern in [Background Jobs Done Right](https://phamphutai.com/2026/10/14/background-jobs-done-right/).
---
## Admin and support access
Sooner or later, someone on your team needs to look at a customer's data to help them. How you allow that matters as much as how you block it.
- **Don't give your support tool a superuser connection.** That skips every layer at once.
- **Make support access a tenant context too.** An agent "enters" one tenant, and the tool calls `withTenant` with that ID, so the same policies apply.
- **Log it.** Record who accessed which tenant, when, and why (a ticket number).
- **Keep truly cross-tenant work separate.** Analytics, billing and migrations can use a wider role on a separate connection, ideally against a read replica. Keep that role out of your web app's configuration.
---
## Per-tenant export and deletion
Every SaaS eventually hears "give us all our data" and "delete all our data." Because every tenant-owned table has a `tenant_id`, an **export** is a loop over those tables with `WHERE tenant_id = $1`. Keep a list of tenant-owned tables in code (or derive it from the schema) so new tables don't get forgotten.
**Deletion** is the same loop, in reverse dependency order, or a set of `ON DELETE CASCADE` foreign keys hanging off `tenants`. Don't forget data outside Postgres: stored files, search indexes, logs and backups. Write down what you delete immediately and what ages out with backup retention.
Per-tenant **restore** is this model's honest weak spot. You can't roll back the whole database for one tenant. Plan for it with soft deletes on important tables, or a tested script that pulls one tenant's rows out of a backup restored elsewhere.
---
## Checklist
If you're building on shared tables, here's the short version:
- `tenant_id uuid NOT NULL` on every tenant-owned table.
- Indexes, unique constraints and foreign keys include `tenant_id`.
- Repositories require a tenant context that comes from authentication.
- RLS enabled and forced, with a policy on `current_setting('app.tenant_id')`.
- App role: not a superuser, no `BYPASSRLS`, not the table owner.
- Tenant set with `set_config(..., true)` per transaction, never session-level `SET`.
- Tests with two tenants that try to cross over.
- Jobs carry `tenantId`; support access is a logged tenant context.
- A known way to export, delete and restore one tenant.
**Each layer will fail someday. The point is that they won't all fail on the same day.**
---
*Sources: PostgreSQL documentation on [Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), [CREATE POLICY](https://www.postgresql.org/docs/current/sql-createpolicy.html), [SET](https://www.postgresql.org/docs/current/sql-set.html), [configuration settings functions](https://www.postgresql.org/docs/current/functions-admin.html) and [customized options](https://www.postgresql.org/docs/current/runtime-config-custom.html); [node-postgres transactions](https://node-postgres.com/features/transactions); [PgBouncer features](https://www.pgbouncer.org/features.html).*
*Related: [Background Jobs Done Right: Queues, Retries and the Outbox Pattern](https://phamphutai.com/2026/10/14/background-jobs-done-right/), [Start With a Modular Monolith, Not Microservices](https://phamphutai.com/2026/10/14/start-with-a-modular-monolith/), and [Clean Architecture in Practice](https://phamphutai.com/2026/03/14/clean-architecture-in-practice-how-to-structure-large-backend-systems/).*