Multi-tenant schemas are a people problem before they're a data problem
Designing schema isolation for a new enterprise product taught me the hard part isn't the foreign keys — it's getting every team to agree on what a tenant boundary means.
- Databases
- Architecture
When I designed the multi-tenant schemas for new product modules on a compliance SaaS platform, the database design itself was not the hard part. Row-level tenant IDs, indexed correctly, with query paths that cannot accidentally cross a tenant boundary — that is a well-understood problem. The hard part was getting every team that touched the schema to agree on what "tenant" meant in the first place.
Everyone has a different tenant in mind
To billing, a tenant is an account. To reporting, it is whatever rollup the customer actually asked for, which is sometimes a group of accounts. To support, it is whoever is looking at the screen right now. None of these are wrong — they are just different boundaries wearing the same name, and a schema that only encodes one of them quietly breaks the other two.
Isolation is the easy 80%
Reducing query complexity and enforcing isolation across enterprise accounts is the part you can get right with a good migration and a code review checklist. The part that actually took time was writing down, in one place, which boundary the word "tenant" referred to in each context — and making sure the schema, the API, and the reporting layer all pointed at the same definition instead of three plausible ones.