Transactions & Tenant Isolation

Why Tenant Safety Cannot Live in Review Comments

In a shared-schema system, the most dangerous bug is not usually a crash. It is a successful query that returns the wrong tenant's data. That kind of bug slips through because the code still 'works.' The endpoint returns JSON. The tests that only used one tenant still pass. A reviewer may even skim past the missing predicate because the query is otherwise ordinary. Fieldwork therefore treats tenant scoping as a structural rule, not a reviewer preference.

The rejected approach was trusting every query author to remember WHERE tenant_id = $1 every time. That works until it doesn't, and the failure mode is unacceptable. Another rejected option was relying only on Postgres row-level security. RLS is powerful and may be worth adding later, but I do not want the application layer to become careless just because the database might catch mistakes. Fieldwork starts by making scope explicit in application code and can add database guardrails later as a second layer, not as an excuse for a sloppy first layer.

The Transaction Shape for Multi-Step Writes

Task creation in Fieldwork is often more than one statement. We may validate membership, insert the task, update a project counter, and write an outbox record for a later event. Those steps either all happen or none of them should. A transaction is not sophistication here; it is the minimum honest unit of work. The key is to keep transaction boundaries aligned to workflows instead of blindly wrapping every single repository call in BEGIN/COMMIT.

Making Tenant Scope Impossible to Forget

The simplest useful trick in this system is a small Scope type passed into repositories. Not because wrapper structs are fashionable, but because they force call sites to acknowledge the boundary every time. Fieldwork uses a tenant scope object that carries tenant_id and, where relevant, workspace_id. Repository methods accept that scope instead of a loose pile of optional strings. If you do not have scope, you cannot make the call. That is exactly the friction I want.

Scope objects also make tests more honest. A unit test creating a task has to say which tenant and workspace the action belongs to. That nudges test authors toward multi-tenant thinking instead of accidentally proving behavior only in an unscoped world. Once the repository layer adopts this shape consistently, missing tenant filters become rarer because the entire call chain is designed around the idea that scope is always present.

Defense in Depth for Cross-Table Writes

Application-level scoping is necessary, but Fieldwork also uses query shapes that verify containment across tables during writes. Updating a task status does not only filter by task.id. It filters by tenant_id and workspace_id and, when joining through projects, ensures the project belongs to the same tenant. These extra predicates look redundant when everything is healthy. They become invaluable when data or code drifts.

update tasks
set status = $4,
    updated_at = now(),
    completed_at = case when $4 = 'done' then now() else null end
where id = $3
  and tenant_id = $1
  and workspace_id = $2
  and exists (
      select 1
      from projects
      where projects.id = tasks.project_id
        and projects.tenant_id = $1
        and projects.workspace_id = $2
  );

The Decision

• Use explicit transactions for multi-step writes like task creation and event outbox inserts.
• Pass tenant scope as a required input type through repositories.
• Write predicates that include tenant and workspace filters even when another key might imply them.
• Treat database-level protections as additive guardrails, not permission to be careless in application code.

Fieldwork's position is simple: tenant isolation is too important to entrust to habit. Transactions give write flows atomicity, and explicit scope objects make the repository API honest about what every query requires. The rejected alternative was a looser style where tenant filters live in good intentions and review diligence. That is not strong enough for a shared-schema system carrying real customer boundaries.