How DirtFleet keeps your data separate from the next org's

DirtFleet is a single-database multi-tenant SaaS — every org's rows live in the same Postgres alongside every other org's. The thing that keeps Acme Earthmoving's flag from showing up on City Construction's dashboard is a column called organizationId. Get that wrong once and the whole product is broken. Here's how we make sure it stays right.

The shape of the bug we don't want

A repair log written for an asset in org A, accidentally readable from org B. A flag raised against asset X1 in org A, accidentally resolvable by a user in org B. A POST that takes an asset id from the request body and writes the linked log without verifying the asset belongs to the caller. The bug is always either "skipped a where-clause" or "trusted body-supplied id without re-verifying."

Three guarantees, in order of strength

1. organizationId on every domain row

Every domain table — Asset, HoursLog, Flag, RepairLog, WorkOrder, Tool, Project, Yard, WebhookSubscription, etc. — carries a non-null organizationId column. There's no row in the database that doesn't answer the question "which org does this belong to." That column is the seam every query filters on; nothing happens without it.

2. FK cascades respect tenancy

Every relation that links one tenant row to another (a Flag pointing at an Asset, a RepairLog pointing at a WorkOrder) is wrapped by a unique index that includes organizationIdon the parent side. A row in org A literally cannot reference an id from org B — Postgres rejects the insert. This is belt-and-suspenders: even if a route handler skipped the verification, the FK would catch it.

3. Every lib function takes orgId as its first arg

The functions in lib/*.ts that wrap DB writes all have the same shape: createHoursLog(orgId, userId, raw), raiseFlag(orgId, userId, raw), updateWorkOrder({ orgId, workOrderId, ... }). The org id is never inferred from the input body; it's always passed by the route handler, which got it from the authenticated session or the API key. The body can carry any id it wants — the lib verifies that id belongs to the caller before writing, and refuses with a clear error otherwise.

Why we 404 (not 403) on cross-tenant ids

Pass an id from another org to GET /api/v1/assets/{id} and you get 404 not_found, not 403 forbidden. The 403 would confirm that the id exists somewhere. That's information leak — a probing client could enumerate ids that exist anywhere in the system, even if they can't read them. The 404 matches the only correct mental model: "there is no asset with that id, from your point of view." Same response shape as garbage ids, same status code, same body.

How we test it

Two layers. First, unit tests that mock auth + the Prisma call and assert the route passes organizationId into the where clause. These are cheap, they live next to the route file, and they catch the "forgot the scope" bug at PR time.

Second, DB-integration tests that boot up a temporary Postgres, seed two orgs with overlapping ids, and try every cross-tenant scenario we can think of: read with the wrong key, write with an id from the other org in the body, list and assert the result count, update something I shouldn't see. Those tests are slower but they catch the bug that says "the code looks right but the production query optimizer drops the index in some edge case."

What we don't do

• Database-level row security (RLS). Considered. Postgres RLS would let us enforce tenancy at the DB layer without trusting the application code at all. The downsides: every connection has to set a session variable (current_org_id), Prisma doesn't cleanly support that, and the operator-side complexity of debugging a wrong-tenant read with RLS in the way is high. The cost outweighs the benefit at our current scale. Revisit at 1000-tenant scale.
• Schema-per-tenant. A separate Postgres schema per org. Beautifully isolating, terribly operationally: migrations are O(N) where N is tenant count, connections fragment, and the join across two tenants for any analytics becomes a manual reduction job. The SaaS pattern of one big schema with organizationId columns is what every multi-tenant shop converges on for a reason.
• Per-org encryption keys. Some compliance frameworks (HIPAA-adjacent) require this. Our data isn't PHI — we'd be paying a complexity tax for theatre. The encryption is at the storage layer (managed Postgres at rest, TLS in transit) and the access is at the application layer. If a customer needs per-org KMS, that's an enterprise contract item and an architecture change.

The one bug that almost happened

Around iteration 22 we added the public API key auth. The first version cached the resolved key in memory keyed by tokenHash and returned { orgId, scopes }. Code review caught that the cache had no max size — a production with thousands of keys would grow the heap unbounded. Easy fix (LRU cap). What it would NOT have caught: if we'd cached by anything other than tokenHash (say, accidentally by request.headers.get("authorization") which includes the Bearer prefix and could collide across keys with different prefixes — extremely unlikely but not impossible), the same key id could have ended up serving two orgs.

Lesson: the cache key has to be the canonical hash, not the raw header. Documented in lib/api-keys.ts; a unit test asserts the cache key shape.

Cross-tenant isolation is not the kind of thing you can think about once. It's a property of every line of code that touches the database. The boring parts — every lib function takes orgId first, every FK relation has a compound index, every detail endpoint 404s on cross-tenant ids — are the boring parts that actually keep customers' data separate.

→ API reference · → Security posture · → API design retrospective