P0 tenant isolation blocker

Tenant isolation and RLS readiness

Review the tenant isolation contract, withTenant runtime boundary, forced RLS invariants, RLS audit, pgTAP suites, and production data gates before live school data access.

Otto Demo School

School Administrator · Sam Director

P0 tenant isolation blocker

Tenant isolation and RLS readiness

Local readiness proof for withTenant, transaction-local app.tenant_id, forced RLS, non-owner app role, public grant revokes, pgTAP suites, platform identity boundaries, and the gates that block production school data.

Local proof, not production data access

This page does not run a live migration, import production school data, create support grants, or approve tenant data access. It keeps full db:migrate, db:rls:audit, and test:rls checks as activation gates.

RLS suites

Local pgTAP suites that cover tenant isolation surfaces.

Drizzle migrations

Migration files that carry schema and RLS history.

Isolation invariants

Rules that every tenant-owned table must satisfy.

Production gates

Items that still block live school data.

Readiness sources

Source documents and code used for the audit

8 sources

Technical architecture

docs/prd/34-technical-architecture.md

Technical architecture

Evidence: Requires one database, tenant_id on tenant-owned rows, a non-owner app role, transaction-local context, withTenant, and forced RLS.
Readiness use: Primary source for the production tenant isolation contract.

Data model

docs/prd/33-data-model.md

Data model

Evidence: Requires tenant_id on every tenant-owned row, forced row-level security, and CI failure when a table lacks it.
Readiness use: Defines the table shape that production schema expansion must keep satisfying.

Build plan

docs/prd/52-build-plan-for-ai-assisted-development.md

Build plan

Evidence: Names tenant isolation and RLS as high-risk areas that need tests before implementation claims.
Readiness use: Keeps this blocker in the dangerous-work review lane instead of treating it as routine UI work.

Drizzle RLS proof

docs/specs/005-drizzle-rls-proof/spec.md

Drizzle RLS proof

Evidence: Proves transaction-local app.tenant_id, withTenant, non-owner otto_rls_app, forced RLS, public revokes, explicit grants, and pgTAP allow/deny cases.
Readiness use: Base pattern for every tenant-owned table.

Tenant identity and authorization

docs/specs/006-tenants-users-roles-permissions/spec.md

Tenant identity and authorization

Evidence: Introduces real tenant, campus, user, auth identity, membership, role, permission, and scope schema with RLS tests.
Readiness use: Confirms platform identity and tenant authorization are separate.

Database RLS audit

packages/db/src/rls-audit.ts

Database RLS audit

Evidence: Audits otto_private tenant-owned tables for tenant_id, forced RLS, public grants, app-role policies, and platform-owned table allowlist.
Readiness use: Provides the automated drift guard that must run before production data access.

pgTAP RLS suites

supabase/tests/*_rls_test.sql

pgTAP RLS suites

Evidence: Eighteen local RLS suites cover tenant context, identity, seeded schools, academics, records, attendance, forms, communications, automations, LMS, payments, SAP, control plane, and AI/settings-adjacent schema.
Readiness use: Shows breadth of local tenant isolation evidence while keeping full production regression as a gate.

Agent rules

AGENTS.md

Agent rules

Evidence: Forbids raw tenant data access outside withTenant and requires forced RLS, policies, and pgTAP coverage for tenant-owned tables.
Readiness use: Keeps future tickets from bypassing the tenant isolation contract.

Isolation invariants

Rules every tenant-owned table must satisfy

8 invariants

Tenant-owned rows carry tenant_id

Local pattern present

Requirement: Every dependent tenant-owned table has tenant_id uuid not null; tenants.id is the root boundary exception.
Local evidence: Ticket 006 schema tests and RLS suites check tenant_id on identity, academic, records, attendance, communications, automations, LMS, payments, SAP, and other tenant-owned rows.
Production gate: Run db:rls:audit and pgTAP whenever a new tenant-owned table is added.

Forced RLS is required

Local pattern present

Requirement: Tenant-owned tables must have row-level security enabled and FORCE ROW LEVEL SECURITY so owners cannot create false positives.
Local evidence: Ticket 005 and Ticket 006 pgTAP coverage checks enabled and forced RLS under otto_private.
Production gate: Block production migrations that add tenant-owned tables without forced RLS.

Non-owner app role

Local pattern present

Requirement: Application data access uses a non-owner role without bypass-RLS rights.
Local evidence: otto_rls_app is the tested local role; RLS tests assert it does not bypass RLS and does not own protected tables.
Production gate: Provision production app credentials with no owner or bypass privileges.

Transaction-local context

Local pattern present

Requirement: withTenant sets app.tenant_id and optional app.user_id transaction-locally, not session-globally.
Local evidence: packages/db/src/tenant-context.ts validates UUIDs and uses set_config(..., true) inside a transaction.
Production gate: Forbid raw tenant queries and add review/lint coverage for app data access modules.

Missing tenant context fails closed

Local pattern present

Requirement: RLS policies compare tenant_id to app.tenant_id and must deny access when the setting is absent or empty.
Local evidence: pgTAP suites include no-context and wrong-tenant read/write denial cases.
Production gate: Keep no-context tests mandatory in every new tenant-owned schema suite.

Platform identity boundary

Local pattern present

Requirement: users and auth_identities are platform-owned, minimal, and cannot become a back door into tenant data.
Local evidence: Ticket 006 and rls-audit classify users/auth_identities as platform-owned with no direct app-role access.
Production gate: Keep provider auth, platform identity bootstrap, and tenant membership resolution in separate reviewed modules.

Public grants are revoked

Local audit present

Requirement: PUBLIC must not have select, insert, update, or delete privileges on protected tenant-owned tables.
Local evidence: rls-audit checks public privileges and Ticket 005 documents explicit public revokes.
Production gate: Run the audit after every migration and fail production promotion on drift.

pgTAP proves allow and deny paths

Regression suite present

Requirement: Tenant A can access Tenant A data, cannot access Tenant B data, and wrong-tenant writes are denied or affect zero rows.
Local evidence: Eighteen RLS suites exercise local allow/deny behavior across schema areas.
Production gate: Run the full RLS suite against a migrated production-like database before live data import.

Coverage map

What local RLS evidence covers and what still needs production gates

6 areas

Identity and authorization

Local RLS proof present

Tables or suites: tenants, campuses, memberships, membership_roles, roles, permission_sets, scope_assignments, identity_authorization_rls_test.sql
Local evidence: Ticket 006 proves tenant-owned authorization rows while platform users/auth_identities stay separate.
Production gate: Add invitation, role switching, support grants, and membership lifecycle tests before live access.

Academics and student records

Local RLS proof present

Tables or suites: programs, courses, cohorts, sections, schedules, people, students, enrollments, documents, holds, notes, academic_* and student_records RLS suites
Local evidence: Schema tests and pgTAP suites cover tenant ownership across academic and student record foundations.
Production gate: Extend access-level and role-specific field minimization before restricted production records.

Attendance and compliance

Local RLS proof present

Tables or suites: attendance records, correction events, policy versions, SAP policy/evaluation/appeal/plan rows, attendance and SAP RLS suites
Local evidence: Attendance and SAP suites keep tenant isolation separate from regulatory correctness.
Production gate: Run full RLS plus worked-example math review before official attendance or SAP actions.

Forms, communications, and automations

Local RLS proof present

Tables or suites: form templates/envelopes/signatures, communication threads/messages/events/groups, automation definitions/runs/approvals, forms/communications/automation RLS suites
Local evidence: Local suites protect high-volume message and workflow rows from cross-school access.
Production gate: Keep outbound provider delivery disabled until tenant-scoped routing, audit, and kill switches are reviewed.

LMS, payments, billing, and AI

Local RLS proof present

Tables or suites: lessons, quizzes, submissions, gradebook, ledger, payments, refunds, subscription rows, AI policies/runs/content chunks, LMS/payment/AI-adjacent suites
Local evidence: Tenant-owned money, learning, and AI rows carry tenant context in schema and proof data.
Production gate: Run extra review for grade release, money movement, ledger writes, and AI tool access before live use.

Control plane and platform-owned rows

Activation blocked

Tables or suites: feature_flags, entitlement_definitions, users, auth_identities, auth_sessions, auth_devices, auth_mfa_factors, rls-audit allowlist
Local evidence: Platform-owned tables are named explicitly and must not grant broad direct access to otto_rls_app.
Production gate: Keep platform access behind narrow server-only modules and audited support grants.

Runtime guards

Controls that keep tenant data behind server and database checks

5 guards

withTenant request boundary

Helper present

Protects: Tenant-owned reads, writes, exports, reports, search, AI retrieval, and workflow actions.
Proof: packages/db/src/tenant-context.ts opens a transaction and sets app.tenant_id transaction-locally after UUID validation.
Failure mode: Calls without tenant context fail closed at RLS instead of relying on UI navigation.

RLS drift audit

Audit present

Protects: New migrations that accidentally add tenant-owned tables without forced RLS or policies.
Proof: packages/db/src/rls-audit.ts checks tenant-owned tables, app-role policies, public grants, and platform table allowlist.
Failure mode: Promotion should stop when audit failures appear.

pgTAP tenant regression

Suite present

Protects: Cross-tenant reads, wrong-tenant inserts, wrong-tenant updates, missing context, and app-role bypass.
Proof: supabase/tests includes eighteen RLS suites for local database verification.
Failure mode: No production-like data import until the full suite passes against the migrated database.

Server-only identity bootstrap

Boundary present

Protects: Platform-owned users/auth_identities from becoming a tenant data bypass.
Proof: Auth readiness keeps WorkOS subject mapping separate from tenant memberships and product access.
Failure mode: Provider login creates no tenant access until active membership and tenant context resolve.

Support grant boundary

Activation blocked

Protects: Otto support access, view-as-role, restricted data, and visible audit logging.
Proof: Support access remains blocked until grants, expiry, banners, audit log, and redaction are implemented.
Failure mode: Support cannot rely on ordinary auth or hidden impersonation to access tenant data.

Activation gates

What blocks production school data

6 gates

Full RLS suite before production data

Blocks production data

Why it matters: Passing unit tests and proof pages do not prove the migrated database is safe for real schools.
Owner: Platform and security
Next action: Run db:migrate, db:rls:audit, and test:rls against a production-like database before import or pilot activation.

Line-by-line migration review

Blocks schema promotion

Why it matters: A single missing tenant_id, weak grant, or owner/bypass role can break isolation while the UI still looks correct.
Owner: Platform reviewer
Next action: Review every SQL migration touching otto_private for tenant_id, forced RLS, grants, policies, and tenant-aware foreign keys.

Raw database client ban

Blocks app data access

Why it matters: Application code must not sidestep withTenant or reach tenant data through trusted connections.
Owner: Platform
Next action: Add automated import checks and review patterns for approved data-access modules.

Role and field minimization

Blocks restricted records

Why it matters: Tenant isolation by school is necessary but not enough for restricted records, payments, grades, and support access.
Owner: Security and product
Next action: Layer permission scopes, field redaction, restricted-record gates, and step-up on top of tenant RLS.

Support access grants

Blocks support access

Why it matters: Support access must be school-granted, time-limited, visible, logged, and unable to perform sensitive actions.
Owner: Support, security, and platform
Next action: Implement grants, expiry, banners, redaction, audit rows, and no-send/no-sign/no-money/no-grade constraints.

External security review

Blocks pilot launch

Why it matters: Tenant isolation is one of the areas where an AI agent cannot be the final judge.
Owner: Security reviewer and Sam
Next action: Include withTenant, RLS audit, pgTAP suites, migrations, and app data-access patterns in the external security review package.

Proof boundary

Production data remains blocked

No production data access

This is deterministic local tenant isolation readiness proof, not a live database migration or production RLS run.
No production school data, tenant import, support grant, role change, trusted database credential, or production audit row is created by this page.
Existing local RLS suites and audit code are evidence, but production data remains blocked until db:migrate, db:rls:audit, and test:rls pass against a production-like database.
Tenant isolation by school is necessary but not sufficient for restricted records; field minimization, support grants, step-up, and audit logging still need production enforcement.
Application code must continue to treat withTenant as the only tenant data path; UI navigation and button hiding are never access controls.