One database file per tenant, and what it costs

Two companies run their week on a portal I built. Each one sees its own devices, its own tickets, its own security findings, and nothing belonging to the other. The obvious way to do that is one database, a tenant_id column on every table, and a filter on every query.

I did the other thing. Every client company gets its own SQLite file, created the moment the company is added and deleted along with it.

The bug it makes impossible

Write the query that forgets its tenant filter and a shared database hands back everybody’s rows to whoever asked. Not an error. A successful response, with the wrong company’s data in it, rendered into a page that looks completely normal.

You can guard against that. Query builders, row-level security, a repository layer nobody is allowed to bypass, a test that checks every table has a filter. All of them work. All of them are things you have to keep doing correctly, forever, including on the Friday you add one endpoint in a hurry.

With a file each, the query that forgets its filter returns the rows of the one company whose file is open. The boundary is not enforced. It just isn’t crossable, because the other company’s rows are not in the file.

That is the whole argument, and it is smaller than it sounds. It removes one class of bug. It does nothing about the class above it, which is opening the wrong file in the first place, and nothing at all about somebody with legitimate access seeing more than they should. The tenant is read from the token once, on the way in, and if that is wrong then everything after it is wrong too.

The bill

It arrives at the seams, and it is real.

Every schema change runs once per tenant instead of once. With two companies that is a loop. With two hundred it is a job, with retries, and a way of knowing which tenants are on which version when it fails halfway. I have two, so I have a loop, and I am aware that is not the same as having solved it.

Anything that reports across all tenants at once gets more awkward. There is no GROUP BY tenant_id. You open every file and add the numbers up yourself, which is fine for a count and annoying for anything that wants a join.

Connections multiply. One process holding a handle per tenant is nothing at two and a real limit somewhere north of a few hundred, depending on how you pool them.

And one thing goes the other way, which I did not plan for and would now list as a reason on its own: a client asking for their data is a file copy. No export script that has to be right about which rows belong to whom, no risk of a WHERE clause quietly including somebody else in an archive that leaves the building. Same for deleting them.

Where it would be the wrong call

If the product is cross-tenant analytics, this is backwards. You would be fighting the storage layout on every screen.

If you expect hundreds or thousands of tenants, the migration story and the file handles stop being footnotes. There are designs that shard rather than isolate, and at that size they earn their complexity.

If tenants need to share anything, it gets worse fast. Mine do not share a single row, which is what makes this cheap.

What it did not save me from

Two of the bugs I have written up since happened inside this design, and isolation had nothing to say about either.

One flag guarded both “can see every client” and “can create logins”. Both of those are authorisation questions above the storage layer, and no amount of file separation would have caught a permission modelled at the wrong level of abstraction.

The bootstrap endpoint returned 258MB of JSON to the tenants who had been using it longest, because employee photos were stored as base64 on the user records. Every one of those rows was correctly isolated. It was still the wrong response.

That is the honest summary of what this design buys. It closes one door properly and leaves the rest of the building exactly as secure as you made it.

The part I would defend

I decided not to rely on remembering. That is the only bit of this I would call opinionated, and it is the bit I would keep.

Isolation you can see beats isolation you have to maintain, because the second kind degrades every time someone new touches the code and nobody notices until it has already returned the wrong rows to somebody. The cost lands at the seams, in code I write deliberately, on days when I am thinking about exactly that problem. That is a much better place to pay.

Two tenants is not a stress test, though. Ask me again at fifty.

If you are reading this because your own version of the problem is currently a spreadsheet, the decision above is the one waiting for you at the far end. Here is how I think about when that switch is worth making.