Choosing a data isolation strategy for a multi-tenant SaaS

I'm currently building a SaaS product where every business that signs up gets its own workspace, its own users, its own data, its own view of the product, with zero visibility into anyone else's. That's the whole premise of multi-tenancy, and the first real architectural decision it forces is where does one tenant's data end and another's begin, at the database level.

There are three common answers. Database per tenant, where every business gets a fully separate Postgres database. Schema per tenant, where they share a database but get their own namespace inside it. Or shared schema, where everything lives in the same tables and a tenant_id column does the separating. I went with shared schema, and it wasn't a difficult call for a project at this stage.

Database per tenant gives you the strongest isolation, but it turns every migration into a fan out problem, since a schema change has to run against every tenant's database, and provisioning a new tenant means spinning up new infrastructure instead of inserting a row. Schema per tenant is a middle ground, but Postgres wasn't designed to comfortably hold thousands of schemas, and most tooling including Prisma treats schema per tenant as a second class citizen. For a product where I expect a lot of tenants and don't yet know their individual scale, shared schema was the option that let me move fast without boxing myself in.

The tradeoff is that isolation moves from the infrastructure layer into application code, and that's a much easier place to make a mistake. Every table that holds tenant owned data gets a tenant_id foreign key, and every single query has to be scoped by it. Forget one WHERE clause and you've leaked one tenant's data into another tenant's response. That's the failure mode this whole approach lives or dies on.

To make that hard to get wrong, I'm not relying on remembering to add tenant_id to every query by hand. I'm using a Prisma client extension that wraps the query layer. It reads the current tenant from request context and injects the tenant_id filter automatically on every read, and stamps it on every write. The application code calls prisma.booking.findMany() like normal. It never gets the chance to forget the scope, because the scoping doesn't live in the call site anymore.

That still leaves the request context itself as the thing that has to be correct. The tenant gets resolved once, early in the request lifecycle, from the authenticated session, and passed down through a context object rather than trusted from anything the client sends. A tenant_id in a request body or query param is not something I treat as authoritative, since that's exactly the kind of value a malicious or buggy client could tamper with.

On the schema side, tenant_id is indexed on every tenant scoped table, almost always as the leading column in a composite index alongside whatever the table is normally queried by, for example tenant_id and created_at for anything list and paginate, or tenant_id and email for lookups. Without that, every query technically works but degrades the moment any tenant's table grows, since Postgres ends up scanning rows that belong to other tenants just to filter them out.

The honest tradeoff of shared schema is that a single bug in the scoping layer is a cross tenant data leak, whereas with database per tenant that same bug class is architecturally impossible. I've accepted that risk in exchange for simplicity, and I'm managing it with the Prisma extension as the single choke point, tests that specifically assert tenant A can never see tenant B's rows, and the plan to move genuinely large or sensitive tenants onto dedicated databases later if it's ever warranted, without having to redesign anything.

That last point is really the thing that made this decision comfortable. Shared schema now doesn't foreclose stronger isolation later. It's the option that matches where the product actually is today, not where it might be in a year.