Where the Tenant Boundary Lives

Litehouse started life as one system for one place. It ran the smart district around the Honda Center in Anaheim, for one organization, as one application. Then it had to become a product, sold to other districts and campuses, and that meant every table that had quietly assumed there was only one customer now had to know which customer each row belonged to. We planned and ran that migration across a data model of more than 40 relations, from single organization and single application to multi-tenant and multi-application.

What’s the real question underneath a migration like that? It isn’t which database to use. It’s where the tenant boundary lives. In some designs the boundary is a column, and every query has to remember it. In some it’s a rule the database enforces whether the query remembers or not. In others it’s a wall, a separate database or a separate server, and there’s nothing to remember at all. Each choice moves the risk somewhere different, and costs something different.

AWS has the most widely used vocabulary for this. In its storage guidance, a silo is where “storage of tenant data is fully isolated from any other tenant data,” a pool is where “tenants share all of the system’s storage constructs,” which “requires the introduction of a partitioning key that is used to scope and control access to tenant data,” and a bridge is “an appealing compromise” in between (AWS). We ended up using all three, in different places, for different reasons. Here’s the whole layout before the details:

Shared Tables With an org_id

The simplest design is a pool. Every tenant’s data lives in the same tables, and every user-facing table gets an org_id column. Every query filters on it.

The details matter more than the idea. We made org_id part of the keys, not just a column beside them, so the primary key on a tenant table was (org_id, id) and every index on a tenant table led with org_id. That does two things. Every query that filters by org hits an index built for exactly that. And foreign keys can carry the org with them:

CREATE TABLE sites (
  org_id  UUID NOT NULL,
  id      UUID NOT NULL,
  name    TEXT NOT NULL,
  PRIMARY KEY (org_id, id)
);

CREATE TABLE sensors (
  org_id   UUID NOT NULL,
  id       UUID NOT NULL,
  site_id  UUID NOT NULL,
  PRIMARY KEY (org_id, id),
  FOREIGN KEY (org_id, site_id) REFERENCES sites (org_id, id)
);

Notice what that foreign key does. A sensor can only point at a site in its own org, because the reference includes the org. A bug that tried to attach one tenant’s sensor to another tenant’s site doesn’t produce a cross-tenant link, it produces a constraint error. The schema itself refuses to let tenants touch.

Why not put org_id on join tables too? We didn’t, because a row that links two tenant rows has no tenant data of its own, and it’s only reachable through rows that already carry an org. That keeps join tables small and keeps the column where it means something. The fair counterargument is that a join table without org_id can’t use a composite foreign key, so it can’t prove its two sides belong to the same org, and if anything ever queries it directly, it’s unfiltered. That trade is only safe when join tables are reached through their parents and never on their own, and it’s worth writing down as a rule, because nothing in the schema enforces it.

The upside of shared tables is that they’re cheap and simple. One schema, one migration, one connection pool, and Microsoft’s guidance for Azure SQL notes that “multitenant databases have the lowest per-tenant cost” (Microsoft). The downside is in the same document: “A multitenant database necessarily sacrifices tenant isolation,” with an “increased risk of encountering noisy neighbors.” And the boundary is only as good as the least careful query. MongoDB’s documentation describes the same model in its own world without any euphemism: “every database query must filter by that field. You must manage security at the application level” (MongoDB). One forgotten WHERE is one tenant’s data on another tenant’s screen. It’s worth seeing, because it’s the whole argument for the rest of this essay. Pick a design and make the query forget its filter:

Row-Level Security

So in Postgres we didn’t leave the boundary to the queries. We put it in the database, with row-level security policies on org_id.

A policy is a condition Postgres adds to every query against a table, whether the query mentions it or not. The application sets the current org once at the start of each transaction, and the policy compares every row against it:

ALTER TABLE sensors ENABLE ROW LEVEL SECURITY;
ALTER TABLE sensors FORCE ROW LEVEL SECURITY;

CREATE POLICY org_isolation ON sensors
  USING (org_id = current_setting('app.org_id')::uuid)
  WITH CHECK (org_id = current_setting('app.org_id')::uuid);

-- at the start of every transaction, from the application
SELECT set_config('app.org_id', $1, true);

Each piece is there for a reason, and the Postgres documentation explains all of them (PostgreSQL). USING decides which existing rows a query can see, and WITH CHECK decides which rows it’s allowed to write, so a bug can’t insert a row into another org either. If the policy were missing entirely, Postgres fails closed: “If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.” The FORCE line is the one people forget. “Table owners normally bypass row security as well,” and superusers and roles with BYPASSRLS always do, so an application that connects as the owner of its tables isn’t protected by any of this unless the table forces it. And the true in set_config makes the setting local to the transaction. Postgres says that with it, “the new value will only apply during the current transaction” (PostgreSQL).

That last detail is what makes RLS safe with a connection pooler. PgBouncer in transaction mode hands a server connection to a client “only during a transaction,” and it warns that this “breaks a few session-based features of PostgreSQL” (PgBouncer). A plain session-level SET of the org would survive on that connection after the transaction ends, and the next request to borrow it, from a different tenant, would inherit it. A transaction-local setting ends with its transaction, so each request starts clean.

The upside of RLS is that the database enforces the boundary even when the application gets it wrong. The downside is performance and a false sense of completeness. Policy columns need indexes, which (org_id, id) already gives you, and Supabase’s guidance on RLS performance reports improvements “over 100x on large tables” from indexing the columns policies use (Supabase). The same page has the best one-line rule on the subject: “Do not rely on RLS for filtering but only for security.” Queries should still filter by org. The policy is there for the day one doesn’t. And RLS only protects what it’s actually configured on, which is the risk in the other direction. In 2025, a CVE was published describing “an insufficient database Row-Level Security policy” in apps generated by Lovable on Supabase, which the vendor disputes (CVE-2025-48757). A policy that’s missing or wrong is a shared table with nobody guarding it.

The Platform in Its Own Database

The second thing we separated wasn’t tenants from each other. It was the platform from the tenants. Application data, the records that describe the platform itself, like organizations, applications, users and what they’re allowed to do, lived in a different database from the user data each tenant created.

The reason is that those two kinds of data have different owners, different access patterns and different blast radii. Tenant data is read and written by tenants, scoped by org, and grows with usage. Platform data is read on every request to decide who someone is and which org they’re in, and it’s the one place where cross-tenant queries are legitimate, because it’s where the list of tenants lives. Mixing them means the most sensitive tables in the system share a database, a set of credentials and a failure domain with the busiest ones. Separating them means a tenant-facing service can be given credentials that can’t reach the platform’s records at all, and the platform’s own data can be backed up, migrated and audited on its own schedule.

The cost is that some work now crosses databases. A query that needs both a user’s org memberships and that org’s data is two queries, and there’s no foreign key between them, so the application has to keep them consistent. In practice that’s a small price, because the platform data changes slowly and the tenant data references it by ID, not by join.

A Database Per Org

MongoDB was where the shared-table model got harder, because there was no equivalent to RLS to fall back on. MongoDB’s own guidance for the shared model says it plainly: the “segmentation of data is purely logical and must be enforced in the application tier.” Every query, every aggregation pipeline and every $lookup has to carry the tenant filter, and nothing in the server notices when one doesn’t.

So in MongoDB we moved the boundary up a level. Every org got its own database, for everyone. By default that was logical, a database per org on a shared cluster, and the application picked the database from the org on each request. As an option, an org could have its own physical deployment instead. Either way, a query that forgets the org can’t reach another tenant’s data, because it was never pointed at their database in the first place.

That’s a silo, and the trade-offs are well documented. Microsoft’s guidance says “the use of single-tenant databases gives strong tenant isolation,” and adds the benefits that come with it, like being able to restore one tenant with “no impact on other tenants.” The physical option goes further. A tenant on its own deployment has its own resources, so no noisy neighbor, and its data can sit in a region or account of its choosing, which is what larger or regulated customers tend to ask for.

The costs are scale and change. MongoDB recommends one database per tenant for “a small and stable number (up to a few hundred) tenants,” and warns that “each collection and index is stored on disk as a separate file, which can lead to a very large number of open files and high memory usage,” with a guideline of fewer than 1,000 data files per node. Microsoft makes the same point about indexes: a schema with 20 indexes becomes 20,000 indexes across 1,000 tenant databases. And every schema change, every index and every migration now runs once per org, so the vendor “must design procedures to carefully manage schema customizations at scale.” Physical deployments multiply the bill as well as the operations. A silo trades a class of bugs for a class of chores.

Choosing

So why not silo everything? Because none of these is the right answer everywhere, which is why we used three of them. Here’s how they compare, first as a picture and then in detail:

The pattern in that table is that every step to the right moves the boundary from code you have to remember into structure you don’t, and pays for it in operations. Shared tables are cheap and only as safe as your queries. RLS keeps the cost of shared tables and moves enforcement into the database, which is why it was our default in Postgres. A database per org takes enforcement out of the query entirely, which is why it was our default in MongoDB, where there was no RLS to lean on. And a physical deployment is the option you offer when a customer’s isolation is worth more than the cost of running it.

Takeaways

If I were designing a multi-tenant system again, I’d decide where the boundary lives before writing the first table, because moving it later means touching every table, as we did. I’d put org_id into the keys, lead every index with it and carry it through composite foreign keys, so the schema refuses cross-tenant links on its own. I’d turn on RLS with FORCE, set the org with a transaction-local setting, and keep filtering in the queries anyway. I’d keep the platform’s own data in its own database with its own credentials. And on any store without row-level security, I’d make the boundary structural, a database per tenant, and offer a physical one to the customers who need it.

The question was never which database. It was who has to remember the tenant.

Sources

1. AWS, “SaaS Storage Strategies: Partitioning Models,” 2021
2. Microsoft, “Multitenant SaaS database tenancy patterns,” Azure SQL documentation
3. MongoDB, “Build a Multi-Tenant Architecture,” Atlas documentation
4. PostgreSQL, “Row Security Policies,” PostgreSQL 18 documentation
5. PostgreSQL, “System Administration Functions,” PostgreSQL 18 documentation
6. PgBouncer, “Features”
7. Supabase, “RLS Performance and Best Practices”
8. CVE-2025-48757, May 2025 (disputed by the supplier)