One database per tenant, and how we pay for it
UTSUROI splits a control DB from a database for each tenant. Here is what the Cloudflare Workers + Turso setup bought us, and what we gave up.
UTSUROI’s backend runs on Cloudflare Workers with Hono. Application data lives in Turso (libSQL). Only authentication goes through Supabase Auth.
The database layout is not the usual “one big DB with a tenant ID column.” There is one control DB, and one database per tenant.
Why we split it
Three reasons.
1. Leaks between tenants become much less likely. Forgetting WHERE tenant_id = ? in one place cannot expose another company’s data unless you open the wrong database, because each tenant has its own. That is one less thing to guard in review.
2. One tenant’s load does not spill over to others. SQLite serializes writes, so in a shared DB a heavy tenant can stall a light one. With separate databases, it cannot.
3. It fits Workers. On a runtime that lives at the edge, opening one small DB per request is simpler than holding long connections to a large shared one.
What goes in the control DB
This is the heart of the design. Careless, and the control DB grows into a second shared DB.
The rule: it holds only what you must know before you can choose a tenant DB.
- The list of tenants and where each one lives (the basis for routing)
- Coordination state that spans tenants (low frequency)
- Secrets needed before the tenant is resolved (stored encrypted; never a plaintext token)
Everything you can read or write once the tenant is known stays out: tenant-local entities, tenant-local IDs, high-frequency writes, caches, and idempotency records for tenant-scoped workflows. Those belong in the tenant DB.
Every time we add a table, we ask the same question.
Can this move to the tenant DB after one lookup in the control plane?
If it can, it goes in the tenant DB.
What we gave up
Once you split, the SQLite databases are independent of each other. What you lose is clear.
- No foreign keys across databases. An ID that crosses the boundary is only a logical reference. When we receive a board ID or lane ID, we check that it exists in the selected tenant DB before using it.
- No JOINs across databases. If you want one, query twice in the application and match the results.
- No transactions across databases. This is the heaviest one.
How you deal with the third is most of the practical work in this design. For a workflow that updates both planes, we require three things:
- Make each step idempotent, so running it twice with the same input breaks nothing.
- Persist the state needed for recovery, so you can tell later how far it got.
- Decide the retry behavior up front. Do not ship it to production half-defined.
“Roll back on failure” is not available. So you aim for “if it dies partway, running it again makes everything consistent.”
Deduplicate inbound events on the tenant side
Events from outside (a chat message, for example) arrive more than once. It is tempting to add a “receipts” table to the control DB. We don’t. A design that writes to the control plane for every event hits a shared resource in proportion to request volume.
Instead, we deduplicate with a deterministic ID or a unique constraint in the tenant DB. Once the tenant is resolved, deduplication stays inside the tenant DB.
The one thing we keep in control is the index that maps an external service’s ID to a tenant, because it is needed before the tenant is known. Put the other way, that is the only index control holds.
Migrations run once per tenant
This is the other cost. When the schema changes, the migration has to run against every tenant DB.
In UTSUROI the Drizzle schema is the source of truth, and generating and registering migrations is automated, and one command applies them to every tenant. Change the schema, run one command, and both the SQL and the registry are generated. We dropped the habit of registering in three places by hand early on. Any place you still sync by hand will drift eventually.
When it pays off
A database per tenant trades away atomicity in return for far fewer leak worries and no noisy neighbors.
I think it is worth it only if you can build idempotency and retries into the design. If you can, day-to-day implementation actually gets simpler. The one thing to keep in mind is which tenant’s DB you have open, and you no longer think about tenant boundaries in every query.