When I started Agaro ERP, the multi tenant question arrived before almost every other question, and I am glad it did, because it is the one decision that cannot be revised cheaply. You can refactor a bad report. You cannot refactor tenancy after customer data from two companies has learned to live in the same queries.
The three models, and the one I picked
There are three standard shapes. Separate databases per tenant, one database with a schema per tenant, or one database with shared tables and a tenant column. Each buys something and each costs something. Separate databases give the strongest isolation and the worst operational economics at small tenant counts. Schema per tenant sits in the middle but turns every migration into a fan out across hundreds of schemas. Shared tables with a tenant column win on cost and operational simplicity, and they put the entire burden of isolation on application discipline.
For a mid market ERP where hundreds of tenants need to run on sane infrastructure, I chose shared tables with a tenant column, on Postgres, through Prisma. That choice is only defensible under one condition: the tenant filter must be enforced structurally, not remembered by every developer on every query.
Enforce it in one place or not at all
The finding I would hand another builder is this: isolation enforced by convention will eventually fail, and it will fail silently. A single forgotten where tenantId in a reporting query is a cross tenant leak, and nobody notices a leak in a report until a customer notices it for you.
So the rule in Agaro’s codebase is that application code never writes a tenant filter. Every tenant scoped model goes through a data access layer that injects the tenant context from the authenticated session, and there is no path to these tables that does not pass through it. Prisma client extensions make this clean: the extension reads the tenant from the request context and appends the filter itself. Application queries simply cannot express a cross tenant read of a scoped model.
Two supporting decisions matter as much. First, the tenant id lives in the composite unique constraints and every foreign key that needs it, so uniqueness and joins are tenant correct at the database level, not just at the query level. Second, a test suite dedicated to isolation: fixtures in two tenants, an assertion that no query, however malformed, returns the other tenant’s rows, run in CI on every change. That suite is the enforcement of the enforcement.
What isolation costs, priced honestly
Shared table tenancy pushes complexity into three places. Noisy neighbors on shared resources, which means every expensive job needs tenant aware rate limits and queue fairness. Backups and deletion, where “delete this customer” becomes a cascade across every scoped table, which is why deletion in Agaro is a real subsystem with its own verification, not a SQL statement. And the temptation of the cross tenant query: analytics genuinely wants to see across tenants, and the answer is a separate, explicitly audited path, never a query that quietly skips the filter.
The payoff for paying those costs is that isolation becomes a fact of the architecture instead of a feature on a roadmap. Enterprise buyers ask how tenant data is separated, and the honest answer, enforced in one layer, proven by a dedicated suite, is an answer they can verify. That verifiability is what turns tenancy from a liability into a selling point.