Tenancy model

Shape

public.msp           - one row per MSP partner / direct enterprise
  ↓ 1..N
public.msp_user      - one row per signed-in human, mapped by entra_oid
public.tenant        - one row per customer Entra tenant connected under an MSP
  ↓ 1..1
tenant_<uuid>.*      - per-tenant private schema with its own snapshots,
                       intent, exclusions, change requests, etc.

Why schema-per-tenant

• Hard isolation - a SQL injection or RLS bypass on shared tables can't cross into tenant-specific data
• Per-tenant retention - we can drop one customer's history with drop schema cascade without touching anyone else
• Per-tenant migration - schema migrations are applied via provision_tenant_schema which uses if not exists everywhere; new tables get backfilled by an idempotent ALTER block in each schema-changing migration
• Backup granularity - full-DB PITR is the default; per-schema dump is trivial when needed

Naming

tenant_ + the tenant UUID with dashes stripped. So tenant 6ba7b810-9dad-11d1-80b4-00c04fd430c8 lives in tenant_6ba7b8109dad11d180b400c04fd430c8.

Helpers:

• lib/snapshot/schema.ts::tenantSchemaName(tenantId) - uuid → schema
• lib/snapshot/schema.ts::tenantIdFromSchema(schemaName) - schema → uuid

Provisioning

public.provision_tenant_schema(p_tenant_id uuid) is SECURITY DEFINER, service-role-only. Called after Connect saves valid Graph credentials (or on first successful credential store). Idempotent - create table if not exists everywhere.

When the schema gets new columns / tables in a migration:

1. Update provision_tenant_schema so future tenants get the new shape
2. Add an idempotent do $$ ... for v_schema in select ... loop alter table ... add column if not exists block for existing tenants

This pattern is exercised in migrations 008, 009, 015.

Cross-schema queries

Postgres can't directly JOIN across tenant_a and tenant_b without dynamic SQL. The rollup pattern in lib/rollup/aggregate.ts loops MSP tenants in parallel and queries each schema via the supabase-js .schema() builder, then caches the assembled result for 5 minutes.

Scale ceiling

Per-tenant schemas with 9 tables × N tenants → ~9N tables. Postgres comfortably handles tens of thousands of tables, but information_schema.schemata queries (used by the exclusion sweep + the BG cron + the rollup) get slow past 1k tenants per project.

Mitigations if we hit that ceiling:

1. Replace information_schema.schemata scans with iteration over public.tenant rows
2. Materialize a public.tenant_schema_state table that mirrors the per-tenant aggregates so rollup doesn't need to fan out

Neither is needed today.

Why not row-level multi-tenancy on shared tables

Simpler model on paper. We chose schema-per-tenant because:

• RLS bugs are deadly in a CA-management product. Schema separation is a second wall.
• Per-tenant table sizes stay small, so per-tenant queries are fast even at scale
• Customer-leaving (data deletion) is a single SQL command

The cost: per-tenant ALTERs are loops, and rollup is a fan-out. Both are manageable.