Multi-tenant SaaS on a shared Postgres schema is the default because it is cheap, simple and fast to ship: one database, one connection pool, one migration path, every customer's rows in the same tables with a workspaceId column telling you who owns what. It is also the architecture where one forgotten WHERE clause hands customer A the data of customer B. I learned this pattern the honest way while building Estate360, a multi-tenant real estate CRM for builders and brokerages, on a shared schema with every query scoped by workspaceId, and where the enforcement is not a code review checklist but a test that fails the build. This essay is the whole pattern, including the part nobody likes talking about: the RLS policies I wrote that do nothing.
The project: Estate360, built on Next.js 16, Prisma 7 and Supabase Postgres, with a Groq, Gemini, Mistral and NVIDIA fallback pool behind the AI surfaces.
Why shared-schema multi-tenancy is the default, and the danger
The alternatives are well known: a database per tenant, a schema per tenant, or a shared schema with a discriminator column. Database per tenant is operationally heavy at small scale; schema per tenant multiplies migration pain; shared schema with workspaceId is the pragmatic default. Pragmatic does not mean safe. Shared schema means the database will happily return any row to anyone who asks for it. The database does not know your tenancy model; only your queries do.
So the entire isolation story collapses to one question: can any code path reach tenant data without a workspaceId predicate? Everything else, the tests, the reviews, the policies, exists to answer that question continuously and automatically, not once at design time.
Belt and suspenders: app-layer scoping plus a CI guard
My defense is two layers that fail independently.
Layer one: app-layer scoping everywhere. Every query that touches a tenant-owned table includes the workspaceId predicate. Not most queries. Every one, including the boring ones in admin scripts and report generators, because attackers and bugs both specialize in the boring paths.
Layer two: a CI guard that treats an unscoped query as a build failure. Estate360 runs three tenant-scope test gates. The centerpiece is a static guard that scans every tenant-model call site in app/, lib/ and modules/ and fails the build if one lacks a workspaceId predicate. Exceptions are allowed, but they must be registered alongside a checkable reason, which means an exception is a visible, reviewed artifact, not a silent bypass. Two more tests assert that every tenant read path has a recorded broker-visibility decision, and that the broker predicate actually reaches Prisma rather than dying somewhere in the call chain. Vitest covers the units, Playwright covers the end-to-end flows.
The philosophy: scoping is necessary, but a human remembering to scope is not a control. A control is a machine that says no.
| Layer | What it does | What it misses |
|---|---|---|
| App-layer workspaceId scoping | Filters every tenant query at the call site | Depends on every developer, every time, forever |
| CI guard test | Fails the build on any unscoped tenant-model call site | Only sees call sites matching its scan rules; needs the two companion gates for visibility and predicate propagation |
| Postgres RLS | Database-side policies as a backstop | Inert until the app connects as a role that does not own the tables and does not carry BYPASSRLS |
The uncomfortable truth about RLS
Row Level Security is the answer everyone reaches for, and it is a good answer with one brutal precondition. Estate360 has RLS enabled and 31 policies written. They stay inert. The application connects as a role that owns every table and carries BYPASSRLS, so Postgres skips the policies entirely, whether or not they exist and whether or not they are correct.
Enabled is not enforced. If your app role owns the tables, or connects with BYPASSRLS, RLS is decoration: it documents intent, it audits well in a screenshot, and it stops exactly nothing. The real control lives in role separation. The app needs a dedicated role that does not own the tables and does not bypass RLS, plus a separate migration role that does own them. Estate360 has a switch for this, but it is gated by a script that refuses to proceed until its checks pass, because an untested role flip on a production tenant table is how you take the CRM down on a Tuesday.
Until that switch flips, query scoping is the only deployed isolation layer, and pretending otherwise is how breaches happen. I would rather state that plainly than gesture at 31 policies.
The CI guard pattern
If you take one thing from this essay, take the pattern, not the Postgres specifics. It generalizes.
- Enumerate the tenant-model call sites. Decide what counts as a tenant-model call (in my case, Prisma client calls against tenant-owned models) and scan the directories that can contain them: app/, lib/, modules/.
- Assert the predicate. Every scanned call site must contain a workspaceId filter, or a registered exception with a checkable reason. Unregistered equals fail.
- Add companion gates for the semantics the static scan cannot see. Ours verify two things: that every tenant read path records a broker-visibility decision, and that the broker predicate actually reaches Prisma. Static analysis checks shape; these check behavior.
- Fail the build on violation. Not a warning, not a lint with a quiet exit code. The PR does not merge.
- Add the guard before the first tenant, not after the incident. Retrofitting means deciding under pressure which unscoped paths are bugs and which are "load-bearing". Do it calmly, in an empty system, where the answer is always "fix it".
Testing that it actually works
One subtlety worth naming: Estate360 has a second isolation boundary inside each tenant. Brokers see a subset of the workspace, not all of it. Tenant A versus workspaceId scoping is the outer wall; broker visibility is an inner partition, and it is just as easy to get wrong. That is why two of the three gates exist: one asserts every tenant read path records a broker-visibility decision, and the other asserts that predicate actually reaches Prisma instead of being dropped somewhere in the call chain. If you only test the outer wall, you have secured the building and left the interior doors unlocked.
A guard you never see fail is a guard you do not understand. My test setup uses two test tenants seeded with different data, then asserts invisibility in both directions: tenant A cannot read tenant B's rows on any tenant read path, and tenant B cannot read tenant A's. Broker-scoped queries get the same treatment, because brokers see a subset within a tenant, which is a second isolation boundary that is just as easy to get wrong.
These tests run on every PR. The friction is the point. If a developer adds a new tenant read path without thinking about who is allowed to see it, the suite says no before review does. Review is for judgment; machines are for invariants.
Three tenant-scope gates in total: the static call-site scan, the visibility-decision check, and the predicate-propagation check. Each covers what the others cannot. No single test proves isolation; the triangle does.
Honest limitations
Application-layer filters are necessary but not sufficient, and this essay would be dishonest if it stopped at the CI guard. What still matters:
- Audit logs. When something does leak, or almost leaks, you need to know which workspaceId, which path, which user. Isolation without auditability is blind.
- Least-privilege DB roles. Even outside RLS, the app role should not hold DDL rights it never uses. Ownership and bypass flags are the whole ballgame for RLS, and they are also good hygiene on their own.
- Backups and exports. A nightly dump is one giant unscoped read. Exports for a single tenant must carry the same workspaceId discipline as the app; a CSV download that ignores tenancy is a data leak with a friendly UI.
- Tests prove the paths you scan. The guard knows about call sites matching its rules. New modules, new models, new raw SQL: each needs to be folded into the scan or the coverage quietly expires.
None of this is exotic. It is the same lesson every time: pick a small set of invariants, encode them in machines that say no, and be honest in your docs about which layers are deployed and which are written but inert.
I build and harden multi-tenant systems like this for real estate and other B2B workflows. Fixed scope from ₹25,000, fixed quote in writing, free 15-minute intro call. If you are shipping a shared-schema SaaS, the cheapest week of your life is the week the guard goes in, before the first tenant signs. Talk to me.