Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

Status: Accepted
Date: 2026-04-12
Participants: Platform Architecture Team, Jeremy Bloom

​
Context

Encore Health OS serves multiple behavioral health organizations on a single database instance. Every table that contains organization-scoped data must be isolated so one organization cannot see another’s records. Two primary patterns exist: application-layer tenant filtering and database-layer Row-Level Security (RLS). The platform chose Supabase (Postgres) as its database, which provides native RLS policies. All mutations in the frontend use the Supabase JS client with the authenticated user’s JWT, enabling the database to enforce access control directly.

​
Options Considered

​
Option A: Application-layer tenant filtering

  • How it works: Every query in the application explicitly includes WHERE organization_id = $orgId. The database has no RLS policies; enforcement is solely in code.
  • Pros: Simple to implement initially; no database policy complexity.
  • Cons: Single missed .eq('organization_id', orgId) in a mutation leaks cross-tenant data; requires 100% code review coverage with no automated enforcement; no defense in depth.
  • Why not chosen: Single point of failure with no safety net. A missed filter in any of thousands of queries creates a data breach. Unacceptable for a HIPAA-regulated system handling PHI.

​
Option B: Schema-per-tenant (database sharding)

  • How it works: Each organization gets its own Postgres schema or database. Cross-tenant queries are impossible by construction.
  • Pros: Perfect isolation; no RLS complexity; easy to migrate tenants.
  • Cons: Expensive for many small tenants; complex migration management (627+ migrations × N tenants); no cross-tenant reporting; Supabase doesn’t natively support schema-per-tenant with its auth system.
  • Why not chosen: Operational complexity is prohibitive for a platform serving dozens of small behavioral health orgs; incompatible with Supabase auth JWT model.

​
Option C: Database-level RLS with organization_id column ✓

  • How it works: Every tenant-scoped table has an organization_id UUID column referencing pf_organizations. Postgres RLS policies on each table enforce that organization_id = (SELECT organization_id FROM pf_user_role_assignments WHERE user_id = auth.uid() LIMIT 1). SECURITY DEFINER helper functions prevent recursive RLS. All mutations in application code also include .eq('organization_id', orgId) as a defense-in-depth layer.
  • Pros: Enforcement at the database level (can’t be bypassed by app code); works with Supabase JWT auth natively; defense-in-depth with app-layer filters; auditable via RLS policy test suite (100% coverage required); SECURITY DEFINER helpers safely cross table boundaries.
  • Cons: Every new table requires RLS policies; RLS policy complexity for cross-table lookups; recursive RLS must be avoided (constitution §5.7); requires SECURITY DEFINER helpers.
  • Why chosen: Only option that provides database-enforced tenant isolation for a HIPAA-regulated multi-tenant SaaS. Defense in depth: even if app code misses a filter, the database blocks the query.

​
Decision

All tenant-scoped tables include organization_id UUID NOT NULL REFERENCES pf_organizations(id). Row-Level Security is enabled on every such table with policies enforcing organization_id matches the authenticated user’s organization. Application code additionally filters all mutations with .eq('organization_id', orgId). RLS tests achieve 100% coverage (enforced in CI via check-rls-coverage). SECURITY DEFINER helper functions are used for cross-table lookups in policies (constitution §5.3, §5.7).

​
Consequences

​
Positive

  • Data leakage between tenants is prevented at the database layer regardless of application bugs
  • RLS policy test suite provides continuous verification
  • Supabase auth JWT integrates natively with auth.uid() in policies
  • 100% RLS coverage enforced in CI blocks regressions

​
Negative

  • Every new table requires RLS boilerplate
  • Complex cross-table lookups require SECURITY DEFINER functions (which must avoid recursive RLS)
  • Developers must understand RLS activation model and SECURITY DEFINER patterns

​
Mitigations

  • migration-helper agent and create-migration command scaffold RLS boilerplate automatically
  • database-patterns.mdc and DATABASE_DEVELOPMENT_GUIDE.md document the patterns
  • Pre-commit hook lints staged migration files for RLS issues (lint-rls-migration.ts)
  • specs/_templates/db-preflight.md checklist ensures RLS is not forgotten