# Multi-tenancy and RLS audit

Read-only audit of Scope's tenant-isolation posture. Current as of
2026-05-31. Produced for WP1 of the Bar 1 enterprise foundation and for
procurement security review. No code was changed to produce this report.

## Methodology

Enumerated every table, every Row Level Security policy, and every
administrative database client usage across the migrations and the
application code, then classified each. Counts are reproducible:

```
tables:            grep -rhoE "create table .*[a-z_]+" supabase/migrations/*.sql | sort -u
RLS enables:       grep -rh "enable row level security" supabase/migrations/*.sql
policies:          grep -rh "create policy" supabase/migrations/*.sql
admin-access use:  grep -rln createServiceRoleClient lib/ app/
```

## 1. Table inventory and RLS

- Total tables: 82
- Tables with Row Level Security enabled: 82 (100%)
- Total RLS policies defined: 154

Every table in the schema has RLS enabled. There is no table carrying
tenant data without RLS turned on. This is the database-layer backstop.

### Tenant-scoping column by table family

The tenant is an organization. The scoping column varies by family but
every tenant-scoped table carries one:

| Table family | Tenant column | Examples |
|--------------|---------------|----------|
| Matters and dispatch | buyer_id | scopes, projects, master_scopes, scope_instant_quotes, scope_adverse_parties |
| Governance | organization_id / tenant_id | org_dispatch_policies, audit_log, user_roles, org_utbms_overrides, carrier_feed_tokens |
| Roster and vendor relationships | buyer_org_id | buyer_vendor_roster, buyer_vendor_panel, buyer_ocg_rules |
| Vendor-owned records | vendor_id / organization_id | vendor_rate_cards, vendor_credentials, vendor_insurance, vendor_party_relationships, vendor_availability |
| User and identity | org_id (via users) | users, user_roles, user_dispatch_overrides, notification_prefs |
| Bids and activity | scope_id -> scopes.buyer_id | bids, scope_activity, scope_status_events |
| Platform reference (NOT tenant-scoped, public read) | none | industries, matter_types, credential_types, service_categories, network_averages, roles |

The reference tables (industries, matter_types, credential_types,
service_categories, network_averages, roles) are intentionally public
read - they are the shared catalog every tenant draws from, not tenant
data. Their RLS policies grant read to all authenticated users by design.

## 2. RLS policy review

All 154 policies were reviewed. Every tenant-scoped policy gates by the
caller's organization, resolved through one of two patterns:

- Direct: `org_id = (select org_id from users where id = auth.uid())`
  or the equivalent join.
- Indirect (child rows): join to the parent and check the parent's
  tenant column, e.g. `scope_adverse_parties` gates through
  `scopes.buyer_id`.

No policy was found that grants cross-tenant read or write to a normal
authenticated caller. The append-only `audit_log` and the
`carrier_feed_tokens` tables deliberately have read-only tenant policies
and no INSERT/UPDATE policy for normal callers - writes flow only through
the administrative client from the server.

## 3. Administrative database access audit

The administrative database client bypasses RLS. This is correct for genuine
admin/system paths and a layered-hardening consideration everywhere else.

- Total files using the administrative client: 197
- In API route handlers (reachable from a request): 94
- In server-internal lib code: 56 (timers, dispatch, stripe,
  notifications, mcp, etc. - not directly request-reachable)

### Classification

**Justified - genuine cross-tenant / system paths (no change needed):**

| Bucket | Count | Why justified |
|--------|-------|---------------|
| Webhook handlers (Stripe Connect, inbound email, inbound SMS) | 3 | Map external events to arbitrary tenants; signature-verified (HMAC-SHA256), no caller identity to scope by. |
| Platform metrics (scopes-dispatched, vendors-active, etc.) | 6 | Intentionally platform-wide aggregate signal; cross-tenant by design and documented as such in each route. |
| Timer / cron handlers | 10 | System actor, no request identity; act across tenants on schedule. |
| Audit-log + RBAC helpers | 2 | The audit_log write path is administrative-access-only by design (no INSERT policy); RBAC reads user_roles with a query-scoped tenant filter. |

**App-layer tenant-scoped - functionally safe, layered-hardening retrofit
candidate:**

| Bucket | Count | Pattern observed |
|--------|-------|------------------|
| Authenticated API routes | ~74 + the 11 new v1 routes | Each gates on session.orgId (or the bearer token's org) and filters every query by the tenant column in application code, e.g. `.eq("id", session.orgId)`, `vendor_id: orgId`, `buyer_id = caller.orgId`. Verified by spot-check across vendors/rates, settings/delivery, vendors/profile, and all WP2 v1 endpoints. |

These routes ARE tenant-isolated - but isolation is enforced in
application code, not by RLS, because the administrative client bypasses RLS.
The risk is structural, not a present leak: a future route that forgets
its tenant filter would not be caught by the database, because RLS is
not in the loop on the administrative database client.

## 4. Findings

1. **Strength:** RLS is enabled on every table with a sound,
   tenant-scoped policy set (154 policies, no cross-tenant grants). The
   database-layer backstop is fully in place.

2. **Strength:** Genuine admin paths (webhooks, metrics, timers, audit
   writes) are correctly the only places that intentionally cross
   tenants, and each is justified.

3. **Layered-hardening gap:** Authenticated tenant-scoped routes use the
   administrative database client and rely on application-code tenant filtering
   rather than RLS enforcement. Every route audited filters correctly,
   so there is no present cross-tenant leak. But the database is not
   acting as a backstop on these paths, so a missing filter on a future
   route would not be caught by RLS.

## 5. Prioritized remediation

These are layered hardening steps, not fixes for a present
leak. Sequenced by leverage.

1. **Tenant-filter guard helper (low effort, high leverage).** A shared
   query helper for the administrative database client that requires a tenant id
   and applies it, so "forgot the filter" becomes a type error rather
   than a silent cross-tenant read. Retrofit the ~85 authenticated
   routes to route their reads through it.

2. **Migrate read paths to the RLS-enforced client where practical
   (medium effort).** For authenticated tenant-scoped reads that do not
   need cross-tenant access, use the session/anon client so RLS enforces
   isolation as the backstop. Reserve the administrative client for the justified
   admin paths in section 3.

3. **CI lint (low effort).** A lint rule that flags an administrative database query
   in an authenticated route that lacks a tenant-column filter, so the
   gap cannot silently reappear.

## 6. Bottom line

Scope is tenant-isolated today, enforced by RLS at the database layer on
every table and by explicit tenant filtering in every authenticated
route. The hardening opportunity is to make the database the backstop on
the authenticated routes (not just the application code), so a future
mistake is caught by the database rather than relying on every developer
remembering the filter. No present cross-tenant exposure was found.