ADR-003: Row-Level Security for Multi-Tenant Isolation

Status: Accepted Date: 2024-11-15 Deciders: Platform architect

Context

The platform is multi-tenant: multiple organizations share the same database. Tenant A must never see Tenant B's data. This includes engagements, deliverables, runs, agent configurations, API keys, and audit logs.

The platform needed a tenant isolation strategy that:

• Prevents cross-tenant data access even if application code has a bug
• Works with the existing PostgreSQL database on Supabase
• Does not require a separate database per tenant (cost-prohibitive at this stage)
• Can be enforced at the database level as a defense-in-depth layer

Decision

Use Supabase's Row-Level Security (RLS) policies to enforce tenant isolation at the PostgreSQL level. Every table with tenant-scoped data has a tenantId column and an RLS policy that restricts access to rows matching the authenticated user's tenant.

Application-level filtering (WHERE clauses in Prisma queries) provides the primary isolation. RLS provides a second layer — even if a query accidentally omits the tenant filter, the database blocks cross-tenant access.

Alternatives Considered

1. Application-level filtering only

Simpler to implement but a single bug in a query could expose data across tenants. This is the most common approach in multi-tenant SaaS, but it relies entirely on developer discipline. Rejected as insufficient for a platform handling client consulting data.

2. Schema-per-tenant (PostgreSQL schemas)

Each tenant gets their own schema within the same database. Provides strong isolation but introduces migration complexity — every schema migration must be applied to every tenant schema. Rejected due to operational complexity for a single-developer project.

3. Database-per-tenant

Strongest isolation. Each tenant gets their own database. Rejected due to cost (Supabase charges per project) and connection management complexity. Appropriate for enterprise-grade isolation but premature at this stage.

4. Citus or similar multi-tenant extension

Distributes data across shards by tenant ID. Rejected as overkill — the current data volume doesn't justify distributed database complexity.

Consequences

Positive:

• Defense in depth: application bugs cannot leak data across tenants
• Database-level enforcement: works regardless of which application path accesses the data
• Supabase provides RLS natively — no additional infrastructure
• Audit-friendly: RLS policies are declarative and inspectable
• Compatible with Prisma ORM (Prisma queries work normally; RLS operates transparently)

Negative:

• RLS policies must be maintained alongside schema migrations
• Debugging query failures requires checking both application logic and RLS policies
• Performance overhead: RLS adds a WHERE clause to every query (negligible at current scale)
• The Python engine uses psycopg2 directly (not Prisma), requiring manual RLS context setting

Risks:

• RLS policies can be accidentally disabled or misconfigured during migrations. Mitigated by the Supabase security advisor which checks for tables without RLS.
• Service role key bypasses RLS — any code using the service role key has unrestricted access. The service role key is only used in server-side API routes, never exposed to the client.