unite-supabase-journey-guard

name	unite-supabase-journey-guard
description	Apply this skill for Unite-Hub Supabase migrations, PostgREST/Data API visibility, founder-scoped Playwright journeys, or errors such as PGRST205, access=denied, stale Supabase linked refs, or migration history drift. Prevents repeating the SQL/cache/auth loop by enforcing the exact verification sequence for core journeys.

Unite Supabase Journey Guard

Use this before claiming any Supabase-backed Unite-Hub journey is fixed or GREEN.

Non-Negotiables

• Do not use broad supabase db push when only selected migrations should land. This repo has historical migration drift.
• Prefer targeted supabase db query --linked --file <migration.sql> for approved additive files, then supabase migration repair --linked --status applied <versions>.
• Verify by effect, not file presence:
  1. SQL file runs with no errors.
  2. supabase_migrations.schema_migrations records the intended version.
  3. Postgres shows table exists, RLS enabled, policies present, grants present.
  4. Supabase REST/Data API returns HTTP 200; table existence alone is not enough.
  5. The relevant Playwright journey passes, or the remaining blocker is logged honestly.
• Never print Supabase service-role keys. Load them through existing support helpers or the Supabase CLI only in memory.
• Throwaway e2e users use @unite-hub.test. If private access is configured, e2e must run through e2e/support/run-with-supabase-admin.ts, which enables the explicit non-production test access gate.

Migration Checklist

1. Confirm the linked ref before applying:
   • cat supabase/.temp/project-ref
   • Expected approved core-journey lane: lksfwktwtmyznckodsau.
2. Inspect SQL for idempotence:
   • CREATE TABLE IF NOT EXISTS
   • CREATE INDEX IF NOT EXISTS
   • guarded policies, or DROP POLICY IF EXISTS followed by recreate when safe
   • explicit grants for authenticated and service_role
   • RLS enabled on exposed public tables
3. Check legacy-name collisions before choosing a table name. In this project, dedicated drip lifecycle tables must use founder_drip_*, not drip_*, because public.drip_campaigns can be legacy workspace-scoped.
4. Apply only the approved files:
   • supabase db query --linked --file supabase/migrations/<file>.sql
5. Repair history only for versions actually applied:
   • supabase migration repair --linked --status applied <version...>

Verification Snippets

Postgres table/RLS/policy/grant proof:

select c.relname as table_name, c.relrowsecurity as rls_enabled,
       array_agg(distinct p.policyname) filter (where p.policyname is not null) as policies,
       array_agg(distinct g.grantee || ':' || g.privilege_type) filter (where g.grantee is not null) as grants
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policies p on p.schemaname = n.nspname and p.tablename = c.relname
left join information_schema.role_table_grants g
  on g.table_schema = n.nspname and g.table_name = c.relname
  and g.grantee in ('authenticated','service_role')
where n.nspname = 'public'
  and c.relname in ('ai_file_cache','ai_file_transcripts','founder_drip_campaigns','founder_drip_steps','founder_drip_enrollments','founder_drip_events')
group by c.relname, c.relrowsecurity
order by c.relname;

Migration history proof:

select version, name, created_by
from supabase_migrations.schema_migrations
where version in ('20260325000001','20260607235936','20260608000000')
order by version;

REST/Data API proof must use the Supabase client and report HTTP status for every target table. A PGRST205 result means the journey is not GREEN yet.

Private Access E2E Gate

If a Playwright journey redirects to /auth/login?access=denied after successful password sign-in:

1. Do not loosen production private access.
2. Confirm the test is running through e2e/support/run-with-supabase-admin.ts.
3. Confirm the test user email ends with @unite-hub.test.
4. Confirm UNITE_HUB_TEST_ALLOW_PRIVATE_ACCESS=1 is set by the runner.
5. Re-run the affected e2e spec once. If it still fails, inspect the login/session cookie path instead of retrying blindly.

Done Criteria

• git diff --check passes.
• pnpm run type-check passes.
• pnpm run lint passes.
• Affected e2e guard passes, or the remaining blocker is documented with the exact failing URL/status/body.
• EVIDENCE.md, STATUS.md, COVERAGE.md, and DECISIONS_NEEDED.md are updated only with verified facts.