VanaGreen: a second layer of data isolation, built into the database

In a multi-tenant application like VanaGreen, data isolation between clients normally lives in one place: every query in the code has to include a tenant_id filter. It works, but it has a structural weak point: it depends on the discipline of every line of code, written today or a year from now, by every developer, in every new function. A new query, a rushed refactor, a route added in a hurry: a single forgotten filter is enough to expose one client's data to another.

What we added

We put Postgres Row-Level Security (RLS) in place as a second isolation layer, directly in the database, not just in the code. Documented in ADR-0005: every relevant table now has an RLS policy that automatically filters rows by the current session's tenant context, no matter what the query above is asking for. In practice, even if the application completely forgets a filter, the database doesn't return or allow writes to rows outside the active tenant. A backstop, not the first line of defense: the application code stays exactly the same, the API contract hasn't changed at all.

Tenant context reaches Postgres through a dedicated database role, propagated via AsyncLocalStorage on every request, plus a separate bypass_rls mechanism for the administrative flows that genuinely need to see across tenant boundaries (cron jobs, internal tools). operator and mobile sessions, used by regular field users, never touch this bypass.

What the testing sweep uncovered

Turning on RLS over code written over years, with no idea a second isolation layer would ever exist, meant a massive sweep: nearly 90 test files that wrote directly to the database, bypassing the normal request cycle, so with no tenant context set at all. Fixing them wasn't purely mechanical: along the way, four real production bugs surfaced, not test gaps:

• API key authentication (/api/v1/public/, /api/v1/platform/) looked up the key with no tenant context set. With RLS active, any request with a bearer token would have failed outright with INVALID_API_KEY.
• The fleet-maintenance-cron and auth-expiry-cron jobs read tenant profiles with no context. Expiry alerts (inspection, environmental authorization) would have gone out silently, never sent to anyone.
• processDueDeliveries, the backoff retry in the webhook module, delivered every webhook with no tenant context. Any retried delivery would have failed.
• Migration 0166: the document_templates table mixes global rows (the default fallback for any tenant) with client-specific overrides, in the same table. The standard policy didn't let any tenant see the global rows, so any template request without its own override returned 404. Reads now explicitly allow global rows too; writes stay strict.

All four would have broken real flows the moment RLS was switched on, not just tests. They were found and fixed first.

How safe, how fast

Before activation, the mechanism went through two separate validation passes. The full regression run (QA-1) initially surfaced 314 failures, almost all from context-less test fixtures, not the real application; after the sweep, 0 real failures remained (just 2 pre-existing flaky tests, unrelated to RLS). A dedicated adversarial test (SEC-1, 7/7 green) explicitly tried cross-tenant access, both read and write, on three real tables, with no tenant_id in the query: all blocked.

On performance (QA-2), on the highest-traffic routes, average response time increased moderately: GET /api/collections from 5.6ms to 10.5ms, GET /api/reports/dashboard from 9.0ms to 17.6ms. Under 25ms even at p99. Small enough that it doesn't justify a per-request batching fallback, which is ready but unused for now.

Why it matters

None of this is ever visible in the VanaGreen interface: no endpoint, no response, no schema exposed to the client has changed. It's the kind of thing that, done right, is completely invisible. But it means that, from now on, data isolation between VanaGreen clients no longer depends exclusively on a correctly written filter in every new line of code. And four bugs that would have silenced legal alerts or broken API integrations exactly when it mattered most were found and closed before they ever reached a real client.