0002 — Multi-tenancy via single Supabase + organization_id partitioning

• Status: accepted
• Date: 2026-05-06
• Deciders: Derek

Context

Ark is multi-tenant from day one. A small NFP rolling onto ark cannot afford a $25/mo dedicated Supabase project; the consulting agency’s pricing model only works if onboarding cost approaches zero. We’re going from a one-org rough prototype to a controlled potential rollout — schema iteration is frequent, and a single source of truth for the schema makes those changes universal across tenants.

Two viable strategies:

A. Single Supabase project, every table carries organization_id, RLS enforces isolation.

B. Per-tenant Supabase project, no shared schema, no cross-tenant queries possible.

(B) is safer in the limit: no policy can leak data across tenants because there’s no cross-tenant data path. But (B) makes schema iteration cost ~N× and onboarding cost real money per tenant.

Decision

Strategy A — single Supabase project with organization_id partitioning. Every domain table includes organization_id UUID NOT NULL REFERENCES organizations(id). Every RLS policy filters on the org claim in the JWT. The API and packages enforce a with_org() discipline so org context is never derived from the request body.

This is treated as an intermediate scaling stage, not the end state. A tenant who outgrows shared infrastructure (cost, isolation, compliance) graduates to a dedicated Supabase project via a documented migration path.

Consequences

Easier:

• One schema, one migration runner, universal updates
• Onboarding cost approaches zero
• Cross-cutting analytics/admin queries possible
• Cheap to iterate during the pitch/early-rollout phase

Harder:

• A single missing or wrong RLS policy is catastrophic — leaks data across orgs
• Permission/role logic must consistently scope on org context
• Service-role queries (admin/migration) bypass RLS — these are the highest-risk paths and need explicit org scoping in code

The mitigations are non-optional, not nice-to-have:

1. Keystone test: packages/db/test/rls-isolation.spec.ts boots a real Postgres, applies all migrations, creates two orgs with users, exercises every CRUD path, and asserts no cross-org reads or writes. CI fails if this test fails. New tables and policies extend this test before being merged.
2. with_org(orgId) helper in packages/db is the only blessed way to acquire a Supabase client for a known org. Direct supabase access is restricted to migrations and explicitly justified service-role flows.
3. Audit log: an org_access_audit table records any service-role write with the asserted org id. A nightly job flags any row where the asserted org id doesn’t match the row’s org id. Drift is investigated.
4. Migration linter: pnpm migrate:lint parses every SQL file and errors if any new table omits organization_id or any new policy doesn’t reference it. (Implemented in Phase 0.)

Trip-wires

We revisit this decision (and consider per-tenant projects) if any of:

• The RLS isolation test fails in CI more than twice in any quarter (signals our policy-writing process is too error-prone)
• A real cross-org leak makes it past CI to production (any single occurrence — write the post-mortem, reconsider)
• A tenant requires isolation we can’t credibly provide on shared infra (HIPAA, certain GDPR scenarios)
• Total Supabase cost on shared exceeds what dedicated would cost for the largest few tenants
• We hit per-row scaling limits where org-level tenant isolation would help query planning

Alternatives considered

• Per-tenant Supabase project. Cleaner. Costs money per tenant. Schema iteration is N× the effort. If a tenant ever needs this, we have the graduation path; we don’t pay the cost up front for everyone.
• Per-tenant Postgres schemas (single project, schemas as namespaces). A middle ground; Supabase’s auth/RLS doesn’t naturally fit per-schema deployment. The complexity isn’t worth it vs. the row-partitioning approach.
• Application-layer-only org scoping (no RLS). Strictly more dangerous. RLS gives defense-in-depth: even a buggy query handler can’t leak data if the policy is right. This option is rejected on principle.