In most multi-tenant schemas there is a tenant_id column, and it is nullable. Somewhere early on, somebody decided that NULL means global: a row that belongs to no tenant and applies to all of them.
It is a reasonable encoding. It is also the source of a specific bug that hides work from the people responsible for it, and the bug is invisible in code review because every query involved looks correct.
This post is the failure, why NULL makes it silent, and what to do instead. For anyone building tenant-scoped software.
The shape of the bug
You have an admin view listing support tickets. It shows global tickets, because the admin console is a global surface:
SELECT * FROM tickets WHERE tenant_id IS NULL ORDER BY created_at DESC;
Correct, and it does exactly what it says. Meanwhile customers raise tickets from inside their own tenant, so those rows carry a real tenant_id.
The result: tenant-scoped tickets are invisible in the admin console. Not filtered out with a notice, not behind a tab. Absent. There is no "all tenants" view because nobody wrote one, and nobody noticed it was missing, because the page is full of rows and looks like it is working.
The general form: the admin surface shows one slice, the users create rows in another slice, and no page shows the union. It surfaces when a customer asks why nobody answered their ticket.
Why NULL specifically makes it silent
Three properties of SQL NULL combine badly here.
NULL is not a value, so it does not group. GROUP BY tenant_id puts every global row in one bucket, which is usually what you want, right up until you want a count per tenant including global, and then you need COALESCE and somebody forgets it.
NULL fails equality silently. WHERE tenant_id = :tenant never matches a global row, and WHERE tenant_id != :tenant does not match it either. Both are correct SQL and neither errors. A developer writing an exclusion filter gets a result set that quietly omits every global row.
NULL is the default. A column that is nullable will receive NULL from any insert path that forgets to set it. So a bug in a write path does not produce an error, it produces a row that is silently promoted to global scope and visible to everyone.
That last one is the security-relevant case, and it is worth stating plainly: in this encoding, forgetting to set the tenant is the same as marking the row visible to all tenants. The failure direction is toward disclosure.
What to do instead
Make the column NOT NULL and use a sentinel. A real row in the tenants table, id 0 or a fixed UUID, named "global". Then:
ALTER TABLE tickets ALTER COLUMN tenant_id SET NOT NULL;
-- global rows carry the sentinel, not NULL
Now equality works, grouping works, joins work, and a write path that forgets the tenant gets a constraint violation instead of a silently global row. Failing loudly on an insert is a much better outcome than a row that leaks.
If you cannot migrate, make scope explicit. Add a scope column with values GLOBAL and TENANT, keep them consistent with a check constraint, and filter on scope rather than on the nullability of the identifier:
ALTER TABLE tickets ADD COLUMN scope text NOT NULL DEFAULT 'TENANT';
ALTER TABLE tickets ADD CONSTRAINT scope_matches_tenant CHECK (
(scope = 'GLOBAL' AND tenant_id IS NULL) OR
(scope = 'TENANT' AND tenant_id IS NOT NULL)
);
The constraint is the valuable part. It makes the invalid combinations unrepresentable, so a write path cannot produce a row whose scope and tenant disagree.
Build the union view deliberately. Any admin surface that exists to oversee everything needs a query that returns everything, with the tenant shown as a column:
SELECT t.*, COALESCE(tn.name, 'GLOBAL') AS tenant_name
FROM tickets t
LEFT JOIN tenants tn ON tn.id = t.tenant_id
ORDER BY t.created_at DESC;
And give the operator a filter, defaulting to everything. A default of global-only is the bug, dressed as a default.
How to find out whether you have it
Two queries and a read.
-- Does the distribution match your expectation?
SELECT CASE WHEN tenant_id IS NULL THEN 'global' ELSE 'tenant' END AS scope,
count(*)
FROM tickets GROUP BY 1;
If you expected mostly tenant rows and see mostly global, you have a write path defaulting to NULL. If you expected a mix and see only global, your admin view may be the only thing creating rows, which is its own finding.
Then grep for the pattern:
grep -rn "tenant_id IS NULL\|tenantId == null\|tenant_id = NULL" --include=*.sql --include=*.java --include=*.ts .
Read each hit and ask whether that query is a scope decision or an accident. In most codebases there are a handful, and at least one of them is answering a different question than the person writing it believed.
The concession
Nullable tenant is not wrong, and plenty of large systems use it successfully. It is compact, it needs no seed data, and the semantics are obvious once you know them.
The argument here is narrower: it is dangerous as a default, because the encoding makes the unsafe state (global) the one you reach by omission, and SQL's NULL semantics mean the mistake never raises an error. If you already have it and a migration is impractical, the check constraint plus an explicit union view gets you most of the safety for very little work.
The implication
Ask of your own schema: what happens when a write path forgets to set the tenant. If the answer is that the row becomes visible to everyone, the encoding is working against you, and no amount of care in the application layer fixes a default that points at disclosure.
Make the safe state the one you get by omission. Then the remaining bugs are the loud kind.
