Skip to content

Table of Contents

ADR-0002: Multi-tenancy via shared database + org_id row scoping

  • Status: Accepted
  • Date: 2026-06-26
  • Deciders: Backend team

Context

Taskly is a SaaS serving many independent organizations (tenants) from one deployment. Tenant data must be strictly isolated — no request may ever read or mutate another tenant's rows — while remaining cheap to operate and scalable to many small tenants. The candidate strategies are:

  1. Database per tenant — strongest isolation, but heavy operations (connection pools, migrations, backups multiply per tenant); poor fit for thousands of small tenants.
  2. Schema per tenant — medium isolation, but migrations and connection management still scale with tenant count; search_path juggling is error-prone.
  3. Shared database, shared schema, org_id row scoping — one schema, one migration set, one pool; isolation enforced in the application.

Decision

Use strategy 3: shared database, shared schema, org_id row scoping.

  • Every tenant-owned table (projects, tasks, memberships, refresh_tokens) carries an org_id column with a composite index whose leading column is org_id.
  • The tenant id comes from the verified JWT org_id claim, resolved at the HTTP edge by tenantGuard (which also asserts the path {org_id} equals the claim) and passed into use-cases as domain.Actor.TenantID. It is never read from request bodies.
  • Repository ports take tenantID as an explicit parameter, so every query carries WHERE org_id = $1. Omitting it is a visible, reviewable mistake rather than a silent default.
  • Defense in depth (optional, shipped behind 0002_rls.up.sql): PostgreSQL Row-Level Security policies key off current_setting('app.current_org'), so the database rejects cross-tenant access even if application code has a bug.

Consequences

Positive

  • One pool, one migration set, one backup; trivially scales to many tenants.
  • Tenant isolation is unit-testable at the service layer and integration-testable at the repo layer ("tenant A cannot read B" tests).
  • RLS provides a hard, database-level backstop when enabled.

Negative / costs

  • Isolation correctness depends on discipline (always scope by org_id); a forgotten predicate is a data leak. Mitigated by explicit tenantID params, code review, isolation tests, and optional RLS.
  • A single very large tenant ("noisy neighbour") shares the pool with everyone; if one tenant grows 100× we would revisit per-tenant pools, partitioning, or a dedicated DB for that tenant.

Neutral

  • Users are global (not tenant-scoped) and join tenants via memberships, enabling one identity to belong to many organizations with different roles.