# v7.0 Phase 5.7 — Admin lifecycle controls Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development.

**Goal:** Ship Phase 5.7 (admin disable-user + SSO disconnect cascade + cleanup RPC). Folds 4 codex CRITICAL + 11 WARNING + 2 NOTE from spec passes 1+2.

**Skip Vercel preview deploy verification** — agent env doesn't have Vercel access; CI does the real verification.

## File structure

### New
- `data/deltas/20260509140000_phase5_7_lifecycle.sql`
- `apps/web/app/api/dashboard/orgs/[orgId]/members/[userId]/disable/route.ts`
- `apps/web/app/api/dashboard/orgs/[orgId]/members/[userId]/enable/route.ts`
- `scripts/cleanup-expired-sso-state.ts`
- 4 test files

### Modified
- `apps/web/__tests__/_helpers/supabase-stub.ts` — `auth.refresh_tokens` table behavior + 5 new RPC handlers + record_workos_sign_in updated for disabled/inactive/pending refusals + cascade in apply_workos_event.
- `apps/web/lib/dashboard/membership-guard.ts` — MAP gains 8 new error codes (`cannot_disable_self`, `cannot_disable_owner`, `cannot_enable_owner`, `member_disabled`, `member_inactive`, `invite_pending`, `invalid_status_transition`, `invalid_state_age`, `invalid_event_age`, `invalid_user_id`).
- `apps/web/components/admin/MembersList.tsx` — disable/enable buttons + lock icon for disabled rows.
- `apps/web/app/api/auth/sso/callback/route.ts` — handle 4 new RPC error codes (member_disabled → 403 redirect to `/login/sso?reason=member_disabled`; member_inactive → 403 reason=member_inactive; invite_pending → 403 reason=invite_pending; cannot_enable_owner unrelated to callback).
- `package.json` AND `apps/web/package.json` — bump both to 6.3.0-pre.12 (codex plan-pass NOTE #2).
- `CHANGELOG.md`

## Tasks

### Task 0: Pull spec
```
git fetch origin spec/v7.0-phase5.7-lifecycle
git checkout origin/spec/v7.0-phase5.7-lifecycle -- docs/specs/v7.0-phase5.7-lifecycle.md
git add docs/specs/v7.0-phase5.7-lifecycle.md
git commit -m "docs: pull Phase 5.7 spec from PR #135 for impl reference"
```

### Task 1: Migration
Write `data/deltas/20260509140000_phase5_7_lifecycle.sql` per spec verbatim:
1. ALTER memberships status CHECK + add disabled_at/disabled_by columns.
2. `revoke_user_sessions(p_user_id)` SECURITY DEFINER RPC.
3. `disable_member(p_caller_user_id, p_org_id, p_target_user_id)` RPC with state transition matrix + owner protection + last-owner guard + idempotent noop on already-disabled.
4. `enable_member(p_caller_user_id, p_org_id, p_target_user_id)` RPC with symmetric owner protection.
5. `cleanup_expired_sso_states(p_state_age_hours, p_event_age_days)` RPC.
6. `record_workos_sign_in` REPLACE — adds disabled/inactive/pending refusals (raises member_disabled / member_inactive / invite_pending).
7. `apply_workos_event` REPLACE — adds set-based cascade refresh-token DELETE on `connection.deleted` events with bounded user-IDs sample in audit metadata.
8. REVOKE/GRANT for all 5 new RPCs (record_workos_sign_in + apply_workos_event already granted in Phase 5.4/5.6).

Privilege grep test (`phase5-7-privilege.test.ts`):
- REVOKE FROM PUBLIC,anon,authenticated for all 5 NEW RPCs.
- GRANT TO service_role.
- 5 new SECURITY DEFINER + 5 new search_path locks (matching the new RPCs).
- memberships CHECK includes 'disabled'.
- disabled_at + disabled_by columns exist.
- record_workos_sign_in raises member_disabled / member_inactive / invite_pending.
- apply_workos_event has cascade DELETE on auth.refresh_tokens.

- [ ] **1.1: Write migration SQL.**
- [ ] **1.2: Write privilege grep test.**
- [ ] **1.3: Run grep test.** Expected: PASS.
- [ ] **1.4: Commit.**

### Task 2: Stub extensions

In `apps/web/__tests__/_helpers/supabase-stub.ts`:

1. Initialize `auth.refresh_tokens` table behavior — rows have `id`, `user_id`, `created_at`. Default empty.
2. Add `disable_member` handler. Implements full state machine + last-owner guard + audit + revoke_user_sessions invocation (which deletes rows from auth.refresh_tokens for that user_id and returns count).
3. Add `enable_member` handler. Symmetric owner check + clears disabled_at/by.
4. Add `revoke_user_sessions` handler. DELETE from auth.refresh_tokens; return count.
5. Add `cleanup_expired_sso_states` handler. Walk sso_authentication_states + processed_workos_events; delete by age; return counts.
6. Update `record_workos_sign_in` handler — refuse member_disabled / member_inactive / invite_pending BEFORE upserting membership.
7. Update `apply_workos_event` handler — on connection.deleted, walk active members + verified-domain emails, delete refresh_tokens, audit with `cascadeRevokedUserCount`/`cascadeRevokedTokenCount`/`cascadeRevokedUserIdsSample`.

- [ ] **2.1-2.7: Implement stub extensions.**
- [ ] **2.8: Add `lib/dashboard/membership-guard.ts` MAP entries** for 8+ new error codes.

### Task 3: Disable/Enable routes

Both follow the Phase 5.4 admin route pattern: `assertSameOrigin → resolveSessionUserId → admin route check → service-role RPC → mapPostgresError`.

- POST `/api/dashboard/orgs/:orgId/members/:userId/disable/route.ts` — body none. Returns RPC result (`{ membershipId, status, noop, revokedTokenCount, revokedApiKeyCount }`).
- POST `/api/dashboard/orgs/:orgId/members/:userId/enable/route.ts` — same pattern. Returns `{ membershipId, status }`.

Tests `__tests__/api/dashboard/orgs/members/disable.test.ts` (covers tests 1-8 + 11-12 + 20a-20d from spec). `__tests__/api/dashboard/orgs/members/enable.test.ts` (covers tests 9-10 + WARNING #3 owner re-enable case).

- [ ] **3.1-5: Write routes, tests, run, commit.**

### Task 4: SSO disconnect cascade tests

`__tests__/api/workos/webhook-cascade.test.ts`:
- Connection.deleted event triggers refresh_tokens deletion for ALL members (active OR disabled) with verified-domain emails (test 13). **Codex plan-pass WARNING #1 — include disabled members too** (belt-and-suspenders; if a disabled member somehow holds a session, revoke).
- Members WITHOUT verified-domain email keep their refresh tokens (test 14).
- Audit metadata includes `cascadeRevokedUserCount`, `cascadeRevokedTokenCount` ONLY (codex plan-pass WARNING #5 — drop user IDs sample to reduce audit-log PII).
- Audit metadata DOES NOT include user IDs (regression assertion).

- [ ] **4.1: Write test file.**
- [ ] **4.2: Run.** Expected: PASS.
- [ ] **4.3: Commit.**

### Task 5: SSO callback handles new error codes

Modify `apps/web/app/api/auth/sso/callback/route.ts`:
- After `record_workos_sign_in`, when error mapped to 403:
  - `member_disabled` → 302 to `/login/sso?reason=member_disabled`
  - `member_inactive` → 302 to `/login/sso?reason=member_inactive`
  - `invite_pending` → 302 to `/login/sso?reason=invite_pending`
- All three clear sso_state cookie + audit append the refusal reason.

Update `app/login/sso/page.tsx` to render banners for the 3 new reasons.

Tests `__tests__/api/auth/sso/callback-disabled.test.ts` (3 tests, one per reason; verify 302 + redirect URL + audit row + cookie clear).

- [ ] **5.1-5: Write, tests, run, commit.**

### Task 6: cleanup script

`scripts/cleanup-expired-sso-state.ts`:
```typescript
#!/usr/bin/env tsx
// Ops/cron-callable wrapper around cleanup_expired_sso_states RPC.
// No HTTP route per codex pass-1 CRITICAL #3.
import { createServiceRoleClient } from '../apps/web/lib/supabase/service';
async function main(): Promise<void> {
  const stateAgeHours = Number(process.env.STATE_AGE_HOURS ?? 24);
  const eventAgeDays = Number(process.env.EVENT_AGE_DAYS ?? 30);
  const client = createServiceRoleClient();
  const { data, error } = await client.rpc('cleanup_expired_sso_states', {
    p_state_age_hours: stateAgeHours,
    p_event_age_days: eventAgeDays,
  });
  if (error) { console.error('[cleanup] RPC failed', error); process.exit(1); }
  console.log('[cleanup] success', data);
}
main();
```

Test `__tests__/scripts/cleanup-expired-sso-state.test.ts` — exercise the RPC handler directly (don't shell out the script):
- Default args succeed with empty tables.
- Out-of-range args raise `invalid_state_age` / `invalid_event_age`.
- Consumed/expired states get deleted.
- Old events get deleted.

- [ ] **6.1-5.**

### Task 7: UI — MembersList disable/enable buttons

Modify `apps/web/components/admin/MembersList.tsx`:
- Add Disable button next to each active member (disabled for owners by non-owners, with tooltip).
- Add Enable button next to each disabled member.
- Disabled rows render with reduced opacity + lock icon.
- Confirm dialog on disable: "This signs the user out of every device. They cannot sign in until you re-enable them. Continue?"

No test file needed — covered indirectly by the route tests + manual jsdom render.

- [ ] **7.1: Modify component.**
- [ ] **7.2: Verify renders cleanly via existing MembersList test.**

### Task 7b: Disabled-state enforcement regression tests (codex plan-pass CRITICAL #2)

The spec's "disabled-state enforcement audit" table claims every org-scoped path already excludes status='disabled' via Phase 1's `user_is_active_member` SECURITY DEFINER helper or RPC-level checks. Add a test file that proves this for the routes most likely to be re-used by a still-valid JWT after disable:

`__tests__/api/dashboard/disabled-user-jwt.test.ts`:
- Disabled member with previously-issued JWT calls `GET /api/dashboard/orgs/:orgId/audit` → 403 not_member (RPC `list_audit_events` requires status='active').
- Same disabled JWT calls `PATCH /api/dashboard/orgs/:orgId` → 403 not_owner.
- Same disabled JWT calls `POST /api/dashboard/orgs/:orgId/sso/setup` → 403 not_admin.
- Same disabled JWT calls `POST /api/dashboard/orgs/:orgId/members/invite` → 403 not_admin.

If any test fails, the spec's audit table is wrong and Phase 5.7 has more work to do.

- [ ] **7b.1: Write test file.**
- [ ] **7b.2: Run.** Expected: PASS (all 4 routes already enforce status='active').
- [ ] **7b.3: Commit.**

### Task 8: Final wiring + version bump + push + PR

- `package.json` + `apps/web/package.json` bump to `6.3.0-pre.12`.
- `CHANGELOG.md` Phase 5.7 entry.
- Run full vitest + tsc.
- Push + PR.

## Skip directives

- Skip Vercel preview deploy.
- Skip running migration locally.
- Skip live Supabase auth.refresh_tokens behavior tests (mocked in stub; runbook documents the coupling).
- Skip live cron wiring (Phase 6 ships it).
- Skip API-key auth integration with disabled-state (Phase 5.8 ships it).
