Multi-tenant database design: shared tables, the tenant column, and the three ways it leaks

How shared-table multi-tenancy leaks in SvelteKit, the three ways to isolate tenants, and why Postgres RLS is the one mechanism that isn't a discipline.

There are three ways to put more than one customer in one database: a database
per tenant, a schema per tenant, or a shared table with a tenant column. For
most B2B products the third is the right answer, and it is the one that makes
you responsible for isolation in application code.

This is the design I keep coming back to, the failure modes it has, and how to
test that it actually holds.

The shape

Three tables carry almost all of it:

• organizations — id, name, created_at, slug
• memberships — org_id, user_id, role, created_at
• everything else — org_id plus whatever the feature needs

The rule that makes this work: org_id appears on every tenant-scoped table,
and it is the first column of every index that starts with a foreign key. The
index ordering is not a detail. A query filtered on org_id and created_at
that has an index on (created_at) alone will still be correct, and it will
still read the whole table to find your tenants' rows.

Two consequences worth stating up front:

• A user can belong to many orgs, so a user is never the tenant key. If you find yourself writing user_id into a scoping WHERE, you have built a single-tenant app that happens to have a login form.
• Membership is the authority, not the session. The session says who you are; the memberships row says what you can reach. Resolve membership per request and treat everything downstream as already authorised.

This is the schema I use in the Postgres kit I maintain, with the Postgres
specifics — RLS as a second layer, pooling, and why the org_id index is not
optional.
The shared-table model itself is not vendor-specific; the isolation strategy is
the part that changes.

How it leaks

Isolation is a rule applied in query construction, and anything that does not
go through your query helper is a second implementation of that rule. The leaks
I have actually seen, in rough order of frequency:

1. The detail route that forgot the filter.
Index routes filter by org_id because you wrote them together. The detail
route takes a row id, and if it selects by primary key alone it returns another
tenant's row. This is the most common one by a wide margin, and it is also the
one that survives code review, because the route looks obviously correct.

2. The second query builder.
A search, an export, a report, an admin view — each of these reaches for the
raw client because the helper does not quite fit. Each is now a place org_id
can be omitted. Count them: grep for direct selects and count the ones that do
not go through the scoped helper.

3. The job with no request context.
A background task runs as the system. It has no org_id in scope, so it looks
up "the" row, and it does so without knowing which tenant asked for the work.
If the job is triggered by an org-scoped event, the tenant identity has to be
carried in the payload explicitly — not re-derived.

4. The one that is not a bug.
Under row-level security, every connection must carry a tenant setting, and
with a pooled connection pool that setting has to be set on checkout and
cleared on return. Miss the clear and the next request inherits it. This one
only appears in production, at volume, and it is why I treat RLS as
defense-in-depth rather than as the primary mechanism — the application filter
is the thing you can test; the pool behaviour is the thing you can get subtly
wrong.

Testing it, which is the part everyone skips

An isolation bug is the easiest kind of bug to write and the hardest to find,
because the happy path works. The test that catches it is boring and has to
run every time:

• Seed two organizations with overlapping data — same-ish names, similar timestamps, so an unfiltered query returns the wrong row rather than an empty one.
• For every read path, assert that tenant A gets exactly its own rows. Assert the count, not the absence of a crash.
• For every id-addressable route, request a resource belonging to tenant B while authenticated as tenant A, and assert 404 or 403. Both are acceptable; returning the row is not.
• For every write path, assert tenant A cannot mutate tenant B's row even when it knows the id.

That last group is the one that earns its keep. Reads fail loudly in review;
writes fail silently in production.

In the three kits I maintain this is a declared count enforced in CI rather
than a convention — the declarations and the actual test files are compared on
every run, so the count cannot drift. The number itself is not the point;
what matters is that the comparison exists and fails when they disagree.

What to index

The minimum that keeps queries fast as tenants accumulate:

• (org_id, created_at DESC) on every listable table
• (org_id, user_id) on memberships, with a unique constraint — this is what makes a double-accept of the same invite fail in the database rather than in application code
• org_id as a foreign key with a delete rule you have actually chosen. ON DELETE CASCADE on an audit log is a decision, and usually the wrong one.

Where to start

Shared tables, org_id everywhere, index leading with org_id, membership as
the authority. Then write the two-tenant test before you write the second
feature, because every feature you add is a new place to forget.

Comments

[Cenk KURTOĞLU]

RLS being “the one mechanism that isn’t a discipline” is the right way to put it, and there’s one pooling detail that decides whether it actually holds in a request-per-connection backend. The value RLS reads (a tenant GUC, or an identity) has to be scoped to the transaction, not the session. If you set it with SET (or SET SESSION) on a connection you later return to the pool, the value survives the checkout, and the next request that gets that backend can inherit the previous tenant — a leak that is invisible in dev and rare under load. Two rules close it: set it transaction-scoped (set_config('app.current_tenant', $1, true) inside BEGIN ... COMMIT, or SET LOCAL inside a transaction block), and on release run RESET ALL / DISCARD ALL so a connection can never carry a tenant back. Two related gotchas: SET x = $1 takes no bind parameter at all — it is a syntax error, set_config() is the parameterized form — and SET LOCAL outside a transaction is a no-op with a warning. To prove the isolation the article promises, a two-account probe that asserts account B sees zero of account A’s rows, with the first transaction held open, is the test that catches this: github.com/cekuu35/supabase-rls-le...