[Gap-Audit] RLS test suite wedges on cloud Supabase when prior runs leave residue

[TortoiseWolfe]

Summary

pnpm test:rls against cloud Supabase fails on beforeAll if a prior run died mid-test and left orphan rows that block deletion of the canonical *@scripthammer.test users. The test fixture's afterAll cleanup is correct for successful runs; what's missing is recovery from killed runs.

Surfaced while verifying #44 — cloud had two stale *@scripthammer.test users from a 2026-04-16 run with one orphaned payment_intents row referencing userA. The user delete failed with payment_intents_template_user_id_fkey (FK constraint), wedging every test file that called createTestUser('test-user-a@scripthammer.test', …) in its beforeAll.

What's shipped

• tests/fixtures/test-users.ts:155-209 — createTestUser() has a delete-and-recreate retry path for "already registered" errors. The retry uses auth.admin.deleteUser(), which fails when payment FKs reference the user.
• tests/rls/payment-rls.test.ts:67-73 — per-describe-block afterAll cleans up payment intents/results then deletes test users. Works perfectly when tests complete.
• vitest.rls.config.ts — fileParallelism: false prevents same-run races; doesn't help with cross-run residue.

Gap

Three failure modes left unhandled:

1. Killed run during a test body — a kill -9 on the runner skips afterAll. Next run inherits orphan payment_intents, payment_results, subscriptions, webhook_events rows.
2. Soft-deleted users with reserved emails — Supabase's auth uniqueness can hold the email after a soft delete, blocking recreation. The fixture currently calls deleteUser(id) without should_soft_delete=false.
3. Cloud-only residue — local docker compose --profile supabase down -v wipes everything; cloud Supabase persists indefinitely until something cleans it.

Plan

Idempotent precondition cleanup at fixture startup:

1. Add tests/rls/__setup__/cleanup-stale.ts (or extend tests/fixtures/test-users.ts) — a globalSetup hook for vitest.rls.config.ts that:
   • Lists all *@scripthammer.test auth users
   • For each, deletes their dependent rows via service role: payment_intents, payment_results, subscriptions, then the user itself with should_soft_delete=false
2. Wire it via globalSetup in vitest.rls.config.ts so it runs once before any test file
3. Document it in the fixture comment so future test authors know the cleanup is centralized

Reference recipe (this is what unblocked the cloud verification in #44):

// 1. orphan intents → 2. their results → 3. subscriptions → 4. user (hard delete)
const intents = await fetch('/rest/v1/payment_intents?template_user_id=eq.' + uid + '&select=id', {headers}).then(r=>r.json());
for (const i of intents) await fetch('/rest/v1/payment_results?intent_id=eq.' + i.id, {method:'DELETE',headers});
await fetch('/rest/v1/payment_intents?template_user_id=eq.' + uid, {method:'DELETE',headers});
await fetch('/rest/v1/subscriptions?template_user_id=eq.' + uid, {method:'DELETE',headers});
await fetch('/auth/v1/admin/users/' + uid + '?should_soft_delete=false', {method:'DELETE',headers});

Reference

• Surfaced during [Gap-Audit] 042 Payment RLS Policies: un-skip 25 E2E test stubs and verify policies #44 cloud verification (closed)
• Branch with the RLS test suite at 55/55: 044/payment-rls-verify
• Fixture: tests/fixtures/test-users.ts
• Config: vitest.rls.config.ts

[TortoiseWolfe]

Scope expansion: cross-shard collision affects E2E too

This issue was filed for RLS tests, but #57 (filed today) shows the same root cause affects the E2E messaging shards on parallel CI: cross-shard collisions on shared Supabase Cloud test users.

Evidence:

• Run `24970006226` chromium-msg shard: 1 hard failure (round 1), then 31 hard failures (round 2 rerun) — wild swing on identical config rules out code regression and indicates shared-state instability.
• 31-failure cascade starts at `complete-user-workflow.spec.ts:206`, which fails with the auth-loss redirect pattern (`window.location.href='/'` from `AuthContext.signOut()`). That's the same cascade that triggers when a test user is deleted by a parallel shard's `afterAll`.

Proposed unified scope:

1. Cleanup-stale on startup (original [Gap-Audit] RLS test suite wedges on cloud Supabase when prior runs leave residue #50 scope) — globalSetup hook for both `vitest.rls.config.ts` and Playwright `global-setup.ts` that scrubs orphan FKs + hard-deletes residual `*@scripthammer.test` users before tests run.
2. Per-shard test-user namespace — instead of one shared `test-user-a@scripthammer.test`, generate `test-user-a-${process.env.PLAYWRIGHT_PROJECT_NAME || 'local'}@scripthammer.test`. Then chromium-msg, firefox-msg, webkit-msg, and `pnpm test:rls` all use disjoint user pools and can never collide.
3. Single source of truth — both `tests/fixtures/test-users.ts` (RLS) and `tests/e2e/auth.setup.ts` (E2E) currently have their own user-create logic. Unify on a fixture that respects shard naming.

If accepted: close #57 as duplicate-of-#50, bump priority to P0 (currently P2 — it's now blocking every PR's CI signal).

cc @TortoiseWolfe — if you want me to take this on as the next piece of stability work, say the word and I'll branch off main, design via brainstorm/spec, and implement. Otherwise it stays in the queue.

[TortoiseWolfe]

Cross-reference: PR #58 fixes a different (also-evident) bug from the same CI run — the workflow's Start server step was silently proceeding to Playwright when wait-on timed out, causing all firefox/webkit shards to cascade-fail with ECONNREFUSED. That noise was making it harder to see #50/#57's actual cross-shard test-user-collision signal. #58 reduces the noise; once it lands, the real failure mode here will be cleaner to investigate.