Back to writing

A foreign key doesn't guarantee the tenant

A foreign key proves both rows exist. It doesn't prove they belong to the same customer.

In a multi-tenant database, this table is referentially valid:

user_org_unit(user_id, org_unit_id)

The two foreign keys guarantee that the user exists and that the unit exists. Neither guarantees they belong to the same tenant. A bug in a handler, a migration script or a bulk import, and that's it: a user from customer A is now part of a unit from customer B. The database accepts it, because nobody told it that's impossible.

Tenant-aware invariants

Every sensitive relationship should preserve tenant equality:

tenant(user)     = tenant(org_unit)
tenant(resource) = tenant(org_unit)
tenant(grantee)  = tenant(resource) = tenant(grantor)
tenant(user)     = tenant(role)   -- when the role is tenant-scoped

The cleanest way to guarantee it is a composite key. Each table exposes the pair (id, tenant_id) as unique, and join tables reference the pair:

ALTER TABLE app_user ADD UNIQUE (id, tenant_id);
ALTER TABLE org_unit ADD UNIQUE (id, tenant_id);

CREATE TABLE user_org_unit (
  tenant_id   uuid NOT NULL,
  user_id     uuid NOT NULL,
  org_unit_id uuid NOT NULL,
  PRIMARY KEY (user_id, org_unit_id),
  FOREIGN KEY (user_id, tenant_id)
    REFERENCES app_user (id, tenant_id),
  FOREIGN KEY (org_unit_id, tenant_id)
    REFERENCES org_unit (id, tenant_id)
);

With the same tenant_id column taking part in both foreign keys, a cross-tenant link is no longer a possible state. When moving everything to composite keys at once is too costly, a constraint trigger works as an intermediate step. What doesn't work is “we already validate it in the handler”.

The database should be able to refuse impossible cross-tenant states.

Identity is not an email

Along the same lines: a live relationship points to a stable identifier. Email is a contact channel and often the login, but it changes. Company domains change, spellings get fixed, people change their names. A user_role keyed by email is a painful migration scheduled for later. Use an immutable UUID in relationships and, if you like, keep a text snapshot in the audit events, so the trail stays readable even after the record changes.

Tests that try to break it

The right test here doesn't check that the handler rejects. It tries to build the forbidden state directly in the database and passes when the database refuses:

  • linking a user from tenant A to a unit from tenant B;
  • sharing an object from tenant A with someone from tenant B;
  • assigning a role from another tenant;
  • tying a resource to an incompatible unit.

If any of those inserts goes through, isolation depends on the discipline of each endpoint. And endpoint discipline is exactly what fails the day someone writes a script in a hurry.