Tenant & Team Management — Test Plan & Results

First end-to-end shakedown of the multi-tenancy (white-label) and team (shared credit pool) subsystems, which had never been exercised as a whole. This document is both the reproducible test plan and the recorded results of the first run (2026-08-24, against a fresh local Supabase built from the repo migrations).

TL;DR

The tenant + team data/logic layer works. A full create-and-manage exercise of a tenant and a team (24 assertions) passes against the current schema — see Phase 3.
But three classes of pre-existing defects surfaced, none previously caught because the features were never run against a clean database or as a non-service-role user:
1. Repo migrations do not apply to a fresh DB (blocks self-hosting / branching). 4+ ordering/validity failures.
2. profiles RLS has infinite recursion for the authenticated role (masked in prod by service-role access).
3. The pgTAP DB test suite has bit-rotted — 9 of 15 files fail against current schema, and they can false-pass.
Plus two smaller team wiring gaps: quota alerts are never sent, and invite emails use the wrong sender ([email protected]).

See Findings for the full list with severity.

Environment


DB under test	Fresh local Supabase (Postgres 17.6), all repo migrations applied
Why not a Supabase branch	`create_branch` off prod inherited prod’s drifted migration history → `MIGRATIONS_FAILED`, 0 tables. Branch deleted.
Why not the existing local stack	A docker-compose stack (`accessible-pdf-supabase-`) was up on `:54322` but stale* (PG15, pre-entitlement schema). Stopped it and brought up a current CLI stack in its place (approved: “reset in place”).
Access pattern	Tests run as `service_role`/superuser — the same context the backend API uses. RLS-as-user is called out explicitly where relevant.

Reproduce

supabase start                 # if not already up
supabase db reset              # applies migrations + seed (SEE FINDING 1 — currently fails midway)
# Because reset fails, the working DB was built by applying migrations tolerantly:
#   see scripts in this doc's Finding 1. Then:
export PGPASSWORD=postgres PATH="/opt/homebrew/opt/libpq/bin:$PATH"
psql -h 127.0.0.1 -p 54322 -U postgres -d postgres -v ON_ERROR_STOP=1 -f /tmp/tenant_team_exercise.sql

Schema map (what these features are)

Tenants (white-label / multi-tenancy) — migration 004 + 254 (SSO): tenants, tenant_domains, tenant_branding, tenant_content, tenant_admins, tenant_sso_config, tenant_sso_logins. A default tenant is hardcoded (2de55acf-…). profiles.tenant_id scopes every user. Program licensing (annual PDF/ACR/audit allocations) via grant_org_program().

Teams (shared credit pool) — migration 023 + entitlement layer 195–200: teams, team_members, team_invites, team_credit_transactions, team_member_quotas, team_member_usage. Two credit models coexist:

Legacy cents pool — deduct_team_credits() / add_team_credits() (now price-aware, cents).
Entitlement pool (current) — the owner’s team/enterprise package allowance is the pool; members draw via consume_entitlement() when their profiles.credit_source = team:<id>, capped per-member by team_member_quotas.

Routes: workers/api/src/routes/teams.ts, tenant.ts, tenant-admin.ts, tenant-sso-admin.ts, departments.ts. UI: apps/web/src/app/teams/*, /admin/tenants/* (super-admin), /tenant-admin/* (tenant-admin — branding, content, departments, domains, lti, sso, usage, users — all present).

Runtime note: teams is mounted in all three API entrypoints, but tenant/tenant-admin are mounted only in index.ts and index-aws.ts (Lambda), not server.ts (Node). Prod frontends call api-pdf (Lambda) so this is OK today, but the Node runtime would 404 all tenant-admin calls — a parity gap.

Phase 1 — Migrations apply to a fresh DB (FAILED)

supabase db reset on a clean DB fails — the repo migrations are not fresh-applicable. Applying all 282 files tolerantly (multi-pass) leaves these unresolved:

Migration	Error	Type
`20260326000001_002_music_scores_lyrics.sql`	`ALTER TABLE music_scores` before the table exists	Ordering — the `_002_` alter got an earlier timestamp than `_043_` create (`20260326000002`). Resolves only if create runs first.
`20260407000002_064_security_advisor_fixes.sql`	`function public.deduct_credits(uuid, integer) does not exist`	Ordering — hardens a function signature not yet present on fresh apply
`20260517000007_113_org_chart_annual_plan.sql`	`ON CONFLICT` has no matching unique constraint	Fresh-apply validity bug
`20260605000001_146_drop_vpat_source_file_url.sql`	`Direct deletion from storage tables is not allowed`	Env-specific (storage trigger)
`20260816000000_249_workshop_category_and_trainings.sql`	`syntax error at or near "workshops_category_check$$"`	Genuine SQL syntax error

Also observed: duplicate version prefixes (20260816000000 and 20260816020000 each used by two different files) → non-deterministic apply order. Internal sequence numbers (_NNN_) and date prefixes disagree throughout.

Impact: blocks self-hosting / Supabase branching / any clean re-provision. Prod survives only because migrations were applied incrementally over time. HIGH.

Phase 2 — pgTAP DB test suite health (9/15 FAIL)

Run each supabase/tests/*.test.sql with -v ON_ERROR_STOP=1:

Test	Result	Note
cents_denomination_personal	✅ pass
department_caps	✅ pass
department_hierarchy	✅ pass
entitlement_webhook	✅ pass
legacy_deduct_to_cents	✅ pass
vpat_check_key	✅ pass
team_entitlement	❌ fail	fixture collides with `account_packages` auto-provision (migration 231)
entitlement_rpcs	❌ fail	same `account_packages` collision
credit_idempotency	❌ fail	`credit_balances` dup-key (fixture rot)
can_manage	❌ fail	infinite recursion in `profiles` RLS (see Finding 2)
cents_denomination_teams	❌ fail	assertion: idempotent export bal 8900 ≠ 9400
program_use	❌ fail	assertion: “TEST 20a (pre-extend draw) FAILED”
multiplier_pricing	❌ fail	`credit_transactions` check-constraint violation
course_maps_rls	❌ fail	`syntax error at "RAISE"` (harness)
s3_integrations_constraints	❌ fail	`syntax error at "RAISE"` (harness)

Root causes: (a) migration 231 added a signup trigger that inserts account_packages, which collides with older tests’ fixtures; (b) real behavior drift in cents/program logic; (c) two tests use a psql-incompatible RAISE form.

False-pass hazard: several tests end with RAISE NOTICE '... ALL TESTS PASSED' outside the assertion transaction. Run without ON_ERROR_STOP=1 (as a naive psql -f would), every statement errors yet “ALL TESTS PASSED” still prints. CI must run these with ON_ERROR_STOP=1 (or via supabase test db) and check exit codes.

Phase 3 — Tenant/Team management exercise (PASSED)

/tmp/tenant_team_exercise.sql — one transaction, rolled back, 24 assertions. All pass:

Tenant

T1 create tenant · T2 branding row · T3 primary domain · T4 appoint tenant admin
T5 duplicate-slug rejected (unique constraint)
T6 grant_org_program(pdf=500, acr=100, audit=50) — requires a tenant_admins row with role='owner' and an owner without an active paid subscription

Team

TM1 create team · TM2 add existing member (auto-joined) · TM3 invite unregistered member
TM4 duplicate-member rejected (unique (team_id,email))
TM5 invited member auto-joins on signup — handle_new_user links the team_members row and marks the invite accepted
TM6 pool enrollment (credit_source=team:<id>) + per-member cap (remediation=5)
TM7 consume_entitlement(M1,'pdf_convert',3) → member usage +3 and owner pool usage +3; response scope:team, member_remaining:2
TM8 idempotent replay (same key) does not double-charge
TM9 over-cap draw blocked → scope:member_cap, status:insufficient_quota
TM10 owner draws from pool with no per-member cap
TM11 legacy deduct_team_credits is price-aware — charges product_price_cents('pdf_convert','team') × qty (50¢ × 2 = 100¢) against the cents pool
TM12 over-quota legacy deduct rejected · TM13 delete-guard blocks deletion while pool balance > 0 · TM14 invalid role rejected (check constraint)

Conclusion: the core create/manage flows for both tenants and teams are functionally correct at the data layer. The failures above are provisioning/RLS/test-harness issues, not broken tenant/team logic.

Phase 4 — API/route layer (static confirmation)

Routes are thin wrappers over the RPCs validated in Phase 3. Two wiring gaps confirmed by inspection:

Quota alerts never sent — member_quota_alert_pct / pool_alert_threshold are only read/written by the GET/PUT /api/teams/:id/alert-settings routes. No code consumes them to dispatch an email/Telegram alert. The feature is a stored no-op.
Invite email wrong sender — teams.ts:587 uses from: '[email protected]' (a different product; also likely not a verified Resend domain for this account, so invites may silently fail). Should be [email protected].

Recommended live HTTP walkthrough (next manual step)

Not run here (needs the app running + real JWTs). Boot apps/web + the Lambda-shaped API against the local DB and exercise, as three users (super-admin, tenant/team owner, member):

# Teams
POST   /api/teams                         {name}
POST   /api/teams/:id/members             {email, role, creditQuota}   # existing → auto-join; new → invite email
POST   /api/teams/accept-invite/:token
PUT    /api/teams/:id/members/:uid/pool    {enrolled:true}
PUT    /api/teams/:id/members/:uid/quota   {quotaGroup:'remediation', limit:5}
GET    /api/teams/:id/entitlement
GET    /api/teams/:id/reports/usage?format=csv
DELETE /api/teams/:id                      # expect 400 while pool balance > 0
# Tenant (super-admin then tenant-admin)
POST   /api/tenant-admin/tenants           {slug, displayName, adminEmail}
POST   /api/tenant-admin/tenants/:id/admins {email, role}
PUT    /api/tenant-admin/branding          # verify WCAG contrast hardening
POST   /api/tenant-admin/tenants/:id/program {pdf, acr, audit}
GET    /api/tenant/config?domain=…         # public routing

Assert role boundaries: admin cannot add admins; admin cannot modify another admin’s quota; member cannot manage; cross-tenant access denied.

Findings — issues to file

#	Severity	Finding
1	High	Repo migrations don’t apply to a fresh DB (music_scores ordering; 064/113 ordering/validity; 249 syntax error; duplicate version prefixes). Blocks self-hosting/branching.
2	High	`profiles` RLS policies “Admins can view/update all profiles” self-reference `profiles` → `infinite recursion detected in policy` for the `authenticated` role. Masked in prod by service-role access; breaks any client-side/anon read of `profiles`. Fix: use the existing `SECURITY DEFINER is_admin()` instead of the inline sub-select.
3	Medium	pgTAP suite rotted: 9/15 fail vs current schema (fixture collisions with migration 231, behavior drift, 2 harness syntax errors). Plus false-pass hazard when not run with `ON_ERROR_STOP`.
4	Medium	Team quota alerts (`member_quota_alert_pct`/`pool_alert_threshold`) are stored/editable but never consumed to notify anyone.
5	Low	Team invite email sender is `[email protected]` (wrong product/brand; likely unverified domain → silent failures).
6	Low	Runtime parity: `tenant`/`tenant-admin` routes are absent from `server.ts` (Node). OK today (frontend uses Lambda) but a latent 404 if traffic shifts.

Teardown

Supabase test branch qiglqxegcbnxsqyfsxwm — deleted (no ongoing cost).
The prior docker-compose local stack (accessible-pdf-supabase-*) was stopped, replaced by a current-schema CLI stack on :54322. Its old data was disposable (approved). To restore it: docker start accessible-pdf-supabase-db … after supabase stop.