Multi-tenancy in zoth — how different companies' data coexist safely

You asked: "how does data from different companies live in the same DB and stay secure from each other?" Here is zoth's complete answer, layer by layer, with the honest audit history.

Layer 1 — the token carries the tenant

JWT claims include tid (tenant ID); middleware.Tenant() asserts a tenant exists in context for every protected route. API keys are tenant-scoped by construction (issued per tenant, looked up by hash, tenant attached, no user, no role). A request cannot act without a tenant identity.

Layer 2 — every query is tenant-scoped in the repository

The pattern: SQLC queries take tenant_id as a parameter; object fetches use GetByTenantAndID-style methods. Examples to cite: policy.Service.GetByID maps foreign-tenant rows to ErrNotFound (not "access denied" — existence hiding, Day 72's point); the access controller does explicit existing.TenantID != tenantID checks before approve/deny/revoke; integration.getTenantIntegration same. The discipline: the WHERE clause is the security boundary — not a post-fetch check (TOCTOU-free because it's one statement).

Layer 3 — the tricky case: resources (no tenant_id column)

Resources derive tenancy through their parent integration: GetResourceByIDAndTenant INNER JOINs integrations and filters integrations.tenant_id = $1. A cross-tenant resource cannot be expressed by the query — the join eliminates it. The emitter's PlatformBridge.resolve re-verifies integration.TenantID == event.TenantID before touching credentials ("bridge tenant mismatch" check) — defense in depth at the asynchronous boundary, where a poisoned event could otherwise provision across tenants.

Layer 4 — the bug class you fixed (the story)

SECURITY_AUDIT.md finding C-4 (critical): pre-remediation, some object fetches were by bare ID, with tenant checks after or missing — the classic IDOR (insecure direct object reference) surface: /access-requests/{uuid-of-other-tenant} would load the row. Remediation: every fetch became tenant-asserted as in Layer 2. The interview framing: "I ran a self-audit against the OWASP IDOR class and re-architected the repository layer so scoping lives in the query, not in after-the-fact conditionals. The regression test suite covers cross-tenant fetch attempts per controller." That's a better story than never having the bug — it shows the audit → fix → test loop.

Layer 5 — cryptographic isolation of the scariest data

Integration credentials (OAuth tokens to other companies' SaaS) are AES-256-GCM encrypted per row with a server-held key (ENCRYPTION_KEY, 32 bytes) — base64-JSON envelope in credentials_encrypted. Even a read-only DB leak doesn't yield platform tokens. The known limitation to state: a single symmetric key means the app server can decrypt everything (no per-tenant KEK or KMS envelope). Upgrade path: per-tenant data keys wrapped by a KMS master (Azure Key Vault — zoth already lives in Azure), which also buys key rotation. Day 32... Day 42... (full treatment on Day 42 of this track).

The multi-tenancy ladder (know where you stand)

1. Shared schema + tenant_id column + query scoping ← zoth (cheapest, one DB to operate; isolation = code discipline + tests)
2. Schema-per-tenant (search_path; harder migrations × N)
3. DB-per-tenant (strongest isolation, cost × N; for compliance-heavy/enterprise-tier customers)

Escalation triggers: a customer contractually demanding physical isolation; noisy-neighbor write contention; per-tenant encryption keys requiring KMS per tenant. At 1–10 tenants, level 1 with rigorous tests is the correct cost point — and the tests that matter: the cross-tenant fetch attempts per controller (which the remediation added).

The remaining soft spots (say them before asked)

• middleware.Tenant() only asserts presence — the scoping is in repositories; a new query written without $tenant_id compiles fine. Mitigation options: a linter/review rule (all SQLC queries must bind tenant), or RLS (below).
• Postgres RLS (Row-Level Security) is the structural upgrade: policies like USING (tenant_id = current_setting('app.tenant')::uuid) with the middleware setting app.tenant per transaction — the database itself rejects unscoped access even if code forgets. You didn't use it; the honest answer is "RLS adds a per-row policy evaluation cost and a testing dimension; with the repository discipline + tests in place I judged it redundant — I'd add it the day a second team writes queries against this schema." That's a complete position.