ADR-004: PostgreSQL Row-Level Security for UDOM Multi-Tenancy
Status
Accepted
Context
CODITECT is multi-tenant by design. UDOM documents contain tenant-specific research corpora that may include proprietary annotations, domain-specific quality thresholds, and compliance-auditable provenance chains. Tenants in pharmaceutical and financial services must have guaranteed data isolation per regulatory requirements (HIPAA, SOC2).
Decision
Use PostgreSQL Row-Level Security (RLS) for UDOM table isolation. Every UDOM table includes a tenant_id column with RLS policies enforcing tenant boundary at the database level. Application code sets app.current_tenant session variable on each connection. This leverages CODITECT's existing RLS infrastructure — no new isolation mechanism needed.
Consequences
Positive:
- Database-level enforcement — even application bugs cannot leak cross-tenant data
- Zero additional infrastructure — uses existing PostgreSQL and CODITECT RLS patterns
- Compliance-auditable — RLS policies are DDL, versioned in migration scripts
- Performance: RLS adds <1ms overhead per query with proper indexes
Negative:
- All UDOM queries must flow through tenant-aware connection pool (no raw SQL shortcuts)
- Cross-tenant analytics (e.g., platform-wide quality statistics) require superuser context or materialized views
Neutral:
- Shared extraction workers are stateless — tenant isolation applies only at storage and API layers
Alternatives Considered
-
Schema-per-tenant: Separate PostgreSQL schema per tenant. Rejected — operational complexity of managing 100+ schemas with identical structure, migration coordination burden.
-
Database-per-tenant: Separate database per tenant. Rejected — extreme operational overhead, connection pool management, cost at scale.
-
Application-level filtering only: WHERE tenant_id = ? on every query. Rejected — single application bug can expose cross-tenant data; unacceptable for regulated industries.