Tenancy uses polymorphic owner-record tables scoped by a global query hook

Context

commerce-api is multi-tenant: almost every row belongs to a merchant (a store), which belongs to an organisation (the top-level tenant; an org can own many merchants), and some rows belong to a user or an OAuth app. Two things had to be decided: how a row records which tenant owns it, and how every query is guaranteed to be scoped to the caller's tenant so one merchant can never read another's data.

The failure mode to avoid is a query that silently forgets its tenant filter and leaks across merchants — which is exactly what happens when scoping is left to be remembered at each call site.

Decision

Link rows to their owner through polymorphic connector tables, and enforce tenant isolation with a single global Ecto query hook, not a per-table foreign key and not per-query filtering.

• Polymorphic owner-records. A set of connector tables — merchant_records, organisation_records, user_records, app_records — each shaped (owner_id, record, record_id) where record is the target table's name and record_id the target row's id, link any owned row to its owner. Most owned tables carry no direct merchant_id/org_id column; the connector is the link. (A few tables, e.g. webhook_urls, additionally carry direct owner columns to back unique constraints, but are still read-scoped through the connector.)
• Automatic scoping in the Repo. The tenant repo's prepare_query/3 hook rewrites every query on a tenant-owned schema to INNER JOIN the connector and filter by the ambient tenant, before the query runs. The tenant is carried in per-process context set by the auth pipeline, so scoping is a Repo-level default rather than something each query opts into. Adding a new merchant-owned table is scoped for free once it declares the owner association; adding a new owner type is a new connector table, not a column migration across every table.
• Explicit, named escape hatches. Bypassing scope requires an explicit flag — skip_org_id, schema_migration, or a system/operator scope — so an unscoped query is deliberate and greppable, never the accidental default.

Consequences

• Positive: tenant isolation is enforced in one place; a normal read cannot forget its filter, and the set of unscoped queries is exactly the set that names an escape flag.
• Positive: owner types and owned tables evolve cheaply — no per-table FK sprawl, and a new tenant dimension is one connector table.
• Neutral / to revisit: correctness depends on the ambient tenant context being set. Bulk update_all on the non-customer path bypasses the connector join (the guard keys off update-named ops, an UPDATE..JOIN limitation), so such call sites must pre-scope via a scoped read or skip_org_id; a general fix is a tracked follow-up over the ~48 sites. (delete_all does not hit this bypass and stays scoped; customer (buyer) scoping deliberately uses a plain column filter, so it too is safe on bulk writes.)
• Neutral / to revisit: when no tenant resolves (reachable from child-process preloaders that don't inherit the request context), the hook runs unscoped and logs rather than raising — chosen because those preloads only load associations of already-scoped parent rows, and a prior raise crashed the preloader Tasks. It is a considered fail-open, and the log line is the audit trail; it is worth revisiting if any top-level path could reach it.
• Neutral / to revisit: skip_org_id is used broadly (hundreds of sites, concentrated in workers, services and migrators), so "is this query scoped?" often means "does it pass the flag?" — each use is a small trust decision.

Alternatives considered

• A direct merchant_id/org_id FK on every table + a WHERE at each call site: rejected — every query has to remember to scope (the exact leak we are preventing), and adding an owner dimension touches every table. The connector + global hook makes scoping the default and centralises it.
• Per-query scoping helpers without a Repo hook: rejected — still relies on discipline at each call site; putting the scope in prepare_query makes it impossible to omit by accident.
• Postgres row-level security (RLS): not adopted — DB-enforced isolation is stronger against a forgotten filter and would close the unscoped-fallback gap, but it would move tenancy into the database with a SET-per-request session variable and complicate the connection-pooled, multi-repo setup; the app-level hook was chosen for fit. Worth reconsidering if the fail-open path ever proves reachable from a request.