Skip to content

STORY-F-008: Tenant enforcement via Finnest.Repo.prepare_query + Tenant plug

Epic: Multi-Tenancy Priority: Must Have Story Points: 2 Status: Not Started Assigned To: Unassigned Created: 2026-04-17 Sprint: 2

User Story

As a developer or auditor, I want every Ecto query automatically scoped to the current tenant via Finnest.Repo.prepare_query, with any query missing tenant context raising a hard error, so that cross-tenant data leakage is structurally impossible (NFR-004) — no developer can forget an org_id filter because the Repo enforces it.

Description

Background

F-007 lands the Organisation + Office + User schemas. F-008 makes the Repo fail-closed — every query is tenant-scoped automatically via prepare_query/3, and queries without a set tenant context raise RuntimeError.

This is Commandment #31 in action: boundaries enforced by tooling, not convention. Any developer who forgets to scope will see a test fail the moment they run it.

Scope

In scope:

  • Finnest.Repo.prepare_query/3 callback:
  • Reads org_id from Finnest.Core.Tenant.current_org_id/0 (nil-safe — raise if not set AND skip_org_scope: true opt not passed)
  • Inspects the query; if the schema being queried has an :org_id field AND the query doesn't already filter by org_id, injects where: r.org_id == ^org_id
  • Opt-out via Repo.all(query, skip_org_scope: true) — used by: migrations, cross-tenant audit reports, id_mappings lookups during migration
  • Allowlist of schemas that don't have org_id (Organisation itself, IdMapping, future reference-data — match architecture tests in F-007)
  • FinnestWeb.Plugs.Tenant — plug that reads authenticated user's org_id from session/JWT and calls Finnest.Core.Tenant.put_org_id/1; rejects requests where authenticated user has no org (403)
  • Apply the Tenant plug to the :authenticated router pipeline (after auth, before controller)
  • Oban worker base: Finnest.Core.OnbanWorker macro — wraps perform/1 to set Tenant + CorrelationId from job args, clear on exit (ensures workers inherit the same enforcement)
  • Fully enforce the architecture test tenant_query_raises_test.exs (stubbed in F-007): clear tenant context → Finnest.Repo.all(Finnest.Core.User) raises

Out of scope:

  • Full list of Oban workers (none exist yet; macro is foundation for future ones in F-011+)
  • LiveView session-level tenant propagation (handled in F-017 Agent Chat LV via on_mount hook)

Technical Notes

  • prepare_query/3 is called for every query — performance matters. Query inspection should be minimal (check if query's from clause's schema has org_id field; check if wheres already contains an org_id filter; inject only when needed)
  • Inspection via Ecto.Query internal API — brittle but standard Elixir practice. Write careful tests.
  • Edge case: joins with multiple schemas. Scope the primary table by org_id; rely on FK integrity for joined tables to be org-consistent. Cross-schema JOINs are forbidden (AR-07) except for reference data, so this edge case is bounded.
  • Edge case: Repo.aggregate/3, Repo.exists?/2, Repo.one/2 — all go through prepare_query, should work uniformly
  • Oban job args include "org_id" + "correlation_id" keys by convention; base worker macro reads both
  • Benchmark: prepare_query overhead should be <100µs per query in dev (real DB latency dominates)

Dependencies

  • Blocked by: STORY-F-003 (Tenant module), STORY-F-007 (schemas to test against)
  • Blocks: F-009 V2Repo (confirms the prepare_query hook doesn't accidentally apply to V2Repo), F-012 (agents rely on tenant injection), F-016 (event store writes need tenant context)

Acceptance Criteria

  • Finnest.Repo.prepare_query/3 implemented; automatically scopes queries with org_id
  • skip_org_scope: true opt bypasses scoping; used safely by migrations (verify: mix ecto.migrate still works)
  • Finnest.Core.Tenant.clear(); Finnest.Repo.all(Finnest.Core.User) raises RuntimeError with message mentioning "tenant context"
  • Finnest.Core.Tenant.put_org_id(org_a.id); Finnest.Repo.all(Finnest.Core.User) returns only org A's users (mixed-org data scenario test)
  • Finnest.Core.Tenant.put_org_id(org_a.id); Finnest.Repo.get(Finnest.Core.User, user_b.id) returns nil (not 403 — doesn't leak existence)
  • Explicit where: u.org_id == ^org_b.id query with org_a in context raises (defensive — context mismatch detected) OR at minimum returns empty (acceptable fallback)
  • FinnestWeb.Plugs.Tenant sets context; unauthenticated request has no context; authenticated sets correctly
  • Finnest.Core.OnbanWorker macro extracts org_id from job args; sets tenant; cleans up on exit
  • Architecture test tenant_query_raises_test.exs now fully passes (was stubbed in F-007)
  • Performance test: 10,000 queries through prepare_query in <200ms overhead (microbenchmark)
  • Reference-data schemas (Organisation, IdMapping, and future CredentialType) on allowlist — their queries don't require tenant context
  • mix format, credo --strict, dialyzer, mix boundary all green

Testing Requirements

  • Unit: prepare_query logic — injects when schema has org_id, skips when not, respects opt-out
  • Property-based test (StreamData): random query shapes always return tenant-scoped or raise
  • Integration: multi-tenant scenario — Org A + Org B with data; tenant context swaps; results correctly filtered
  • Chaos: deliberately broken test — try Finnest.Repo.all(User) without context → clean error message, no hang, no leak
  • Oban worker test: job with "org_id" arg runs with correct tenant context

References