SoConnective — Documentation

Date: 2026-06-10
Status: Accepted (implements ADR 0002 §3)
Authors: Claude Code · Pamela

Context

ADR 0002 §3 chose row-per-tenant multi-tenancy with PostgreSQL Row-Level Security as "the key piece of the system." This ADR records how it was implemented in packages/db.

Decision

Row-per-tenant + tenant_id on every tenant table; the agency and each client are an organization. Same schema for all tenants → marginal cost per client ~0.
RLS ENABLED and FORCED on each tenant table, with a tenant_isolation policy comparing tenant_id to a transaction-local app.tenant_id (SET LOCAL semantics).
A dedicated runtime role app_user that is NOSUPERUSER and NOBYPASSRLS. This is non-negotiable: superusers bypass RLS even with FORCE, so the application must connect as a non-privileged role. Migrations/admin use the owner; tenant queries use app_user via the withTenant(tenantId, fn, actorId?) helper.
Base conventions on every tenant table: created_at, updated_at (+ version) maintained by a BEFORE UPDATE trigger, deleted_at soft-delete, created_by.
Append-only audit_log populated by an AFTER trigger (who/what/when/before/after); the runtime role may only SELECT/INSERT (UPDATE/DELETE revoked) → immutable history.
CI guard: a test fails if any table with a tenant_id column lacks enabled+forced RLS. Plus a cross-tenant isolation test (insert as A → B sees zero rows).
Single source of truth: the same schema is applied to the platform database, so Payload (payload schema) and the domain (public schema) live in one database.

Key lesson

The cross-tenant isolation test caught that the default Postgres role is a superuser and bypasses RLS even when forced. The app_user role was therefore introduced. Lesson: always test isolation against the exact role the app uses.

Consequences

Strong isolation enforced in the database, not in application code.
Migrations run once for all tenants; trivial cross-tenant reporting for the agency.
Escape path to schema/DB-per-tenant remains open for a future enterprise client.