Manage Users

Author: Patrick Miguel M. Babala
Developers & Reviewers: Patrick Babala, Sean Caintic, and Christian Denzon
Creation Date: September 29, 2026
Status: Published
References: PR #840, de87a0ab, PR #777, PR #406, #520, #558, #563, #607, docs/FEATURE_AUDIT_MAY2026.md, QA-073.5, QA-081, #857, #859, #862, 20260219_add_reviewer_role.sql, 20260414_create_audit_logs_table.sql, 20260430_add_profile_image_url_to_app_users.sql, 20260526_create_admin_reauth_tokens.sql, 20260612_create_user_roles_multi_role.sql, 20260612_update_rls_policies_multi_role.sql, 20260909_create_auth_users_triggers.sql, app/api/admin/list-users, app/api/admin/change-role, app/api/admin/delete-user, app/api/admin/invite-user, app/api/admin/suspend-user, app/api/admin/unsuspend-user, app/api/admin/reauth, app/api/admin/sync-users, app/api/admin/lookup-user, app/admin/manage-users/page.tsx, components/admin/manage-users/*, components/admin/ReAuthModal.tsx, components/shared/confirm-destructive-modal.tsx

Introduction & Goals

Problem Summary

WyzQuests is a multi-role platform (ADMIN / AGENCY / CREATOR / REVIEWER / LEARNER). Platform administrators need a single console to list every user, assign one-or-many platform roles, invite new users, suspend/reactivate access, and permanently delete accounts — with destructive actions gated behind a re-authentication challenge and recorded in an audit trail. The feature is the Super-Admin "Manage Users" module (docs/FEATURE_AUDIT_MAY2026.md:112-119), and is registered in the admin nav under "Administration" (constants/getNavGroups.ts:65).

Note: Provenance note: this checkout is on branch fix/security-issue-859. One code-level divergence from origin/develop is called out in Known Limitations (invite email rate limiting).

Goals & Non-Goals

Goals:

• List all users with their platform roles and agency-assigned role, sortable, from one admin screen (app/admin/manage-users/page.tsx:405-548).

• Support multiple roles per user via the normalized user_roles table (migration 20260612_create_user_roles_multi_role.sql).

• Invite users by email: create a Supabase Auth account, sync the profile, assign roles, send a branded "set your password" email, roll back cleanly on failure (app/api/admin/invite-user/route.ts).

• Gate destructive actions (role change, delete, suspend) behind a ≤5-minute single-use re-auth token (W0.5; migration 20260526_create_admin_reauth_tokens.sql).

• Suspend/unsuspend access via app_users.status.

• Record every sensitive action in audit_logs.

Non-Goals:

• Self-service sign-up provisioning (handled by the auth.users triggers, migration 20260909_create_auth_users_triggers.sql; explicitly not this module).

• Agency membership management (/api/admin/agencies/* owns that; list-users?mode=members only feeds its pickers).

• Billing/subscription, token budgets, branding.

• Identity-provider migration itself (Phase 1 backfill in lib/supabase/scripts/backfill-supabase-auth.ts).

• Soft-delete / GDPR erasure workflow — hard delete only.

Glossary

High-Level Architecture

System Diagram

+--------------------------------------------------------------------------------------------------------------------------+
|                                    Admin Browser (React 19 / Next 16 App Router)                                         |
|  +--------------------------------+  +-------------------+  +----------------------------+  +-------------------------+  |
|  | /admin/manage-users page.tsx   |  | ReAuthModal.tsx   |  | ConfirmDestructiveModal.tsx|  | MultiSelect.tsx         |  |
|  |                                |  +---------+---------+  +--------------+-------------+  +-------------------------+  |
|  |                                |            |                           |                                             |
|  |                                |  +---------v---------------------------v------------------------------------------+  |
|  |                                |  | SuspendDialog / UnsuspendDialog / InviteUserDialog                             |  |
|  +----------------+---------------+  +-------------------------------------+------------------------------------------+  |
+-------------------|--------------------------------------------------------|---------------------------------------------+
                    |                                                        |
                    v                                                        v
+--------------------------------------------------------------------------------------------------------------------------+
|                                              Middleware (middleware.ts)                                                  |
|  MW: Session refresh + page-nav suspension check (skips /api)                                                            |
+-----------------------------------------------------------+--------------------------------------------------------------+
                                                            |
                                                            v
+--------------------------------------------------------------------------------------------------------------------------+
|                                       Next.js Route Handlers (Node runtime)                                              |
|  LU: GET /api/admin/list-users       CR: POST /api/admin/change-role    DU: DELETE /api/admin/delete-user                    |
|  IU: POST /api/admin/invite-user     SU: POST /api/admin/suspend-user   US: POST /api/admin/unsuspend-user                   |
|  RF: GET+POST /api/admin/reauth      SY: POST /api/admin/sync-users     LK: GET /api/admin/lookup-user                       |
+--------------------+--------------------------------------+---------------------------------------+----------------------+
                     |                                      |                                       |
                     v                                      v                                       v
+---------------------------------------------------+  +------------------------------------+  +---------------------------+
|                     lib/auth                      |  |              Postgres              |  |       Supabase Auth       |
|  AUTH: authenticateRole / authenticateUserWithRole|  |  PG: app_users · user_roles        |  |  GO: createUser           |
|        / invalidateAuthCaches                     |  |      admin_reauth_tokens           |  |      deleteUser           |
|  SUSP: suspension.ts: AccountSuspendedError       |  |      audit_logs · agencies         |  |      getUserById          |
|  RE:   reauth.ts: issue/consume token             |  |      agency_members                |  |      listUsers            |
|  SUPA: supabase-server.ts: getCurrentSupabaseUser |  +------------------------------------+  +-------------+-------------+
|        (request-memoized)                         |                                                         |
+---------------------------------------------------+                                                         v
                                                                                               +---------------------------+
                                                                                               |           SMTP            |
                                                                                               |  MAIL: via EmailService   |
                                                                                               |        (branded templates)|
                                                                                               +---------------------------+

Technologies Used

Detailed Design & Implementation

Data Model / Schema

Note: app_users is not defined in supabase/migrations/ (no CREATE TABLE app_users exists anywhere in the repo; confirmed by grep and stated in migration 20260909_create_auth_users_triggers.sql:41-54 and docs/clerk_to_supabase_migration/Plan - Migration from Clerk to Supabase.md:39). It was created out-of-band. Columns below are evidenced from code, not from a checked-in DDL.

+-----------------------+              +-----------------------+
|       app_users       | 1          * |      user_roles       |
|-----------------------|--------------|-----------------------|
| id (PK, UUID)         |              | id (PK, UUID)         |
| clerk_id (UK, TEXT)   |              | user_id (FK, UUID)    |
| email (UK, TEXT)      |              | role (TEXT)           |
| name (TEXT)           |              | created_at (TSTZ)     |
| role (TEXT)           |              +-----------------------+
| status (TEXT)         |
| profile_image_url     |              +-----------------------+
| agency_id (UUID, null)| 1          * |      audit_logs       |
+-----------------------+--------------|-----------------------|
       | 1                             | id (PK, UUID)         |
       |                               | user_id (FK, UUID)    |
       | (logical link via             | action (VARCHAR)      |
       |  clerk_id ↔ admin_clerk_id)   | entity_type (VARCHAR) |
       |                               | entity_id (UUID)      |
       v *                             | details (JSONB)       |
+-----------------------+              | ip_address (VARCHAR)  |
|  admin_reauth_tokens  |              | user_agent (TEXT)     |
|-----------------------|              | created_at (TSTZ)     |
| id (PK, UUID)         |              +-----------------------+
| admin_clerk_id (TEXT) |
| token (UK, TEXT)      |
| created_at (TSTZ)     |
| expires_at (TSTZ)     |
| used (BOOLEAN)        |
| ip_address (TEXT)     |
+-----------------------+
 
+-----------------------+              +-----------------------+
|       agencies        | 1          * |    agency_members     |
|-----------------------|--------------|-----------------------|
| id (PK, UUID)         |              | id (PK, UUID)         |
| owner_clerk_id (TEXT) |              | user_clerk_id (TEXT)  |
| owner_id (UUID)       |              | agency_id (FK, UUID)  |
| name (TEXT)           |              | role (TEXT)           |
+-----------------------+              | status (TEXT)         |
                                       +-----------------------+

• Composite keys:

  • user_roles: UNIQUE(user_id, role) (20260612_create_user_roles_multi_role.sql:17).

• Foreign keys:

  • user_roles.user_id references app_users(id) (20260612_create_user_roles_multi_role.sql:14).

  • audit_logs.user_id references app_users(id) (20260414_create_audit_logs_table.sql).

  • agency_members.agency_id references agencies(id) (20260318_create_agency_members.sql).

  • admin_reauth_tokens.admin_clerk_id has a logical link to app_users.clerk_id (no DB FK).

  • agency_members.user_clerk_id has a logical link to app_users.clerk_id (no DB FK).

  • agencies.owner_id / owner_clerk_id link to app_users (UUID/TEXT).

• ON DELETE CASCADE / SET NULL:

  • user_roles.user_id: ON DELETE CASCADE (20260612_create_user_roles_multi_role.sql:14).

  • audit_logs.user_id: ON DELETE SET NULL (20260414_create_audit_logs_table.sql).

• Triggers:

  • auth.users trigger invokes handle_new_user on insert to upsert app_users and seed LEARNER in user_roles (20260909_create_auth_users_triggers.sql:56-69).

• RLS Helper Functions:

  • user_has_role(uuid, text): checks if a specific user_id holds a role in user_roles (20260612_create_user_roles_multi_role.sql:48-59).

  • user_has_any_role(uuid, text[]): checks if a specific user_id holds any role in a given array (20260612_create_user_roles_multi_role.sql:63-74).

  • set_primary_role(uuid): SQL helper to update primary role (20260612_create_user_roles_multi_role.sql:113-137; not used by this feature as the app maintains primary role in TypeScript).

Table: app_users (out-of-band; columns evidenced from code)

Table: user_roles (20260612_create_user_roles_multi_role.sql)

Indexes: idx_user_roles_user_id on user_id, idx_user_roles_role on role.

RLS Policies on user_roles:

• Users can view their own roles (SELECT): user_id IN (SELECT id FROM app_users WHERE clerk_id = auth.uid()::text)

• Admins can manage roles (ALL): EXISTS (SELECT 1 FROM app_users WHERE clerk_id = auth.uid()::text AND user_has_role(id,'ADMIN'))

Table: admin_reauth_tokens (20260526_create_admin_reauth_tokens.sql)

Indexes: idx_admin_reauth_tokens_token on token, idx_admin_reauth_tokens_clerk_expires on (admin_clerk_id, expires_at).

RLS Policies on admin_reauth_tokens:

• No direct client access to reauth tokens (ALL): USING (false) WITH CHECK (false) (20260526)

• Admins can manage reauth tokens (ALL): USING (admin via user_has_role ADMIN) (20260612_update_rls_policies_multi_role.sql:284-291)

• Admins can view reauth tokens (SELECT): USING (admin via user_has_role ADMIN) (20260612…:293-300)

Note: The later 20260612 migration drops and recreates token policies; the earlier blanket USING(false) deny-all is superseded by the admin-scoped policies (Postgres permissive policies OR together). Latest migration wins. Since all token access is via the service-role client (lib/auth/reauth.ts), RLS is effectively bypassed at runtime.

Table: audit_logs (20260414_create_audit_logs_table.sql, re-declared in 20260526_create_admin_reauth_tokens.sql:18-64)

Indexes: idx_audit_logs_user_id on user_id, idx_audit_logs_action on action, idx_audit_logs_created_at on created_at DESC, idx_audit_logs_entity on (entity_type, entity_id).

RLS Policies on audit_logs:

• Admins can view audit logs (SELECT): EXISTS (SELECT 1 FROM app_users WHERE clerk_id = auth.uid()::text AND user_has_role(id,'ADMIN')) (20260612…:194-200)

• System can insert audit logs (INSERT): WITH CHECK (true) (20260414…:35-37)

API Specification

All endpoints require the ADMIN platform role. authenticateRole("ADMIN") returns the internal app_users.id (lib/auth/authenticate.ts:569-591). The re-auth route deliberately bypasses the dev/staging View-As bypass by calling authenticateUserWithRole() and checking role !== "ADMIN" explicitly (app/api/admin/reauth/route.ts:51-58,199-202).

Response envelope (lib/api/response.ts:66-76,116-145):

• Success: { "success": true, "message"?: string, "data": <T>, "error": null }

• Error: { "success": false, "message": string, "data": null, "error": { "code": string, "message": string, "details"?: unknown } }

Logic & Workflows

Listing Users (Dashboard)

1. Authenticate calling identity using authenticateRole("ADMIN") (app/api/admin/list-users/route.ts:9).

2. Read optional mode and agencyId query parameters (:14-20).

3. Construct base query selecting id, clerk_id, name, email, role, and status from app_users (:22-24).

4. If mode=picker, filter out LEARNER and REVIEWER roles, and exclude existing agency owners unless agencyId equals the row being edited (:26-47).

5. If mode=members, filter out LEARNER, AGENCY, and ADMIN roles, and exclude all owners and active agency_members records (:48-84).

6. Await database query execution; on error, return internal server error (:86-90).

7. In dashboard mode, fetch assigned roles from user_roles for all returned user IDs (:93-108).

8. Fetch active agency_members rows where role is CREATOR or REVIEWER to resolve agencyRole and agencyName (:110-139).

9. Enrich records and return formatted payload (:141-150).

• Supporting details: Reads from user_roles and agency_members are individually isolated in try/catch blocks with silent fallback (:96-108, :114-138) to ensure the table still renders even if role lookups fail.

Changing User Roles (Re-Auth Gated)

1. Authenticate calling identity using authenticateRole("ADMIN") to resolve adminId (app/api/admin/change-role/route.ts:22).

2. Retrieve current session auth UUID (clerkId) via getCurrentSupabaseUser() (:23-26).

3. Validate request schema {userId, roles[], reauthToken} with Zod (:28-34).

4. Invoke consumeReAuthToken(clerkId, reauthToken) (:37-51). On thrown validation error, log REAUTH_TOKEN_INVALID to audit_logs and return 403.

5. Retrieve current roles from user_roles (falling back to app_users.role) (:53-68).

6. Calculate difference: delete removed roles and insert newly assigned roles into user_roles using onConflict: "user_id, role" with ignoreDuplicates: true (:73-88).

7. If roles changed, calculate highest priority role and update app_users.role (:90-99).

8. Record action in audit_logs with action code ROLE_CHANGE and payload details {new_roles, previous_roles} (:101-109).

9. Return 200 success response.

• Supporting details: Role insertion is idempotent via ignoreDuplicates. However, steps 6 and 7 execute as separate PostgREST calls without transactional rollback, which may leave cached primary roles temporarily misaligned if mid-sequence failure occurs. Audit log write failure does not abort the operation.

Deleting a User (Re-Auth and Confirmation Gated)

1. Authenticate caller using authenticateRole("ADMIN") and resolve admin session UUID (clerkId) (app/api/admin/delete-user/route.ts:26-30).

2. Validate body schema containing {userId, reauthToken, confirm_text: "DELETE"} with Zod (:32-38).

3. Invoke consumeReAuthToken(clerkId, reauthToken). On token rejection, write REAUTH_TOKEN_INVALID to audit_logs and return 403 (:40-56).

4. Query app_users for {id, clerk_id, email, name}; return 404 if user is missing (:58-67).

5. Query user_roles for the target user (:69-84).

6. If roles contain ADMIN, reject request and return 403 "Cannot delete admin users" (:86-89).

7. Invoke supabase.auth.admin.deleteUser(clerk_id) (:91-99). On failure, log error to console and proceed.

8. Delete records from agency_members where user_clerk_id = clerk_id (:101-111). On failure, log error to console and proceed.

9. Delete target row from app_users; return 500 "Failed to delete user" on error (:113-122).

10. Insert audit record into audit_logs with action USER_DELETE containing {email, name, roles} (:124-136).

11. Return 200 success response.

• Supporting details: Destructive operations do not implement compensating rollbacks. If database deletion of app_users fails after auth deletion succeeds, the target user will be left in an orphaned database state.

Inviting a User (With Rollback)

1. Authenticate caller using authenticateRole("ADMIN") and resolve admin session (app/api/admin/invite-user/route.ts:53-57).

2. Parse request body {email, roles[]} using Zod (:59-65).

3. Verify email is not registered in app_users; return 409 conflict if already present (:68-81).

4. Verify email is not present in auth.users via findAuthUserByEmail; return 409 conflict if found (:83-94).

5. Generate cryptographically random 16-character temporary password (:99, passwordUtils.ts:13-35).

6. Derive user display names from email string (:100, supabase/admin.ts:21-32).

7. Create auth account via supabase.auth.admin.createUser({email, password, email_confirm: true, user_metadata}) (:102-134). Return 409 on duplicate key failure.

8. Synchronize user profile via syncUser({id, email, name, image: null}) (:140-150). On failure, call auth.admin.deleteUser(authUserId) and return 500.

9. Retrieve internal user record from app_users by clerk_id to get newUser.id (:152-161). On failure, execute rollback and return 500.

10. Remove default LEARNER assignment created by system triggers in user_roles (:166-174). On failure, trigger rollbackInvitedUser(...).

11. Insert requested roles into user_roles (:176-185). On failure, trigger rollbackInvitedUser(...).

12. Update primary role on app_users.role to match highest priority role (:187-195). On failure, trigger rollbackInvitedUser(...).

13. Dispatch invitation email using EmailService.getBrandingData and renderUserInvitationEmail (:200-227). On dispatch failure, trigger rollbackInvitedUser(...) and return 500.

14. Record action in audit_logs with action code INVITE_USER (:246-257).

15. Return 201 created response.

• Supporting details: rollbackInvitedUser deletes created user_roles, removes the row from app_users, and deletes the auth identity in auth.users.

User Suspension and Unsuspension

1. Authenticate caller using authenticateRole("ADMIN") (app/api/admin/suspend-user/route.ts:15).

2. Retrieve admin identity auth UUID (clerkId) (:16-17).

3. Parse request payload {userId, reauthToken} (:19-23).

4. Validate and consume re-auth token via consumeReAuthToken(clerkId, reauthToken); return 403 on failure (:27-35).

5. Execute database update: UPDATE app_users SET status = 'Suspended' WHERE clerk_id = userId (:37-44).

6. Evict cached credentials from local in-memory store via invalidateAuthCaches(userId) (:48, authenticate.ts:1159-1162).

7. Return 200 success response.

• Supporting details: Unsuspension follows the identical control flow via POST /api/admin/unsuspend-user, skipping re-auth token validation and setting status = 'Active'. invalidateAuthCaches invalidates memory in the serving process; cross-instance cache eviction is bounded by TTLs (5 minutes for userIdCache and 30 seconds for roleCache).

Issuing and Consuming Re-Authentication Tokens

1. Process POST /api/admin/reauth by verifying admin session via authenticateUserWithRole() without bypass, requiring role === "ADMIN" (app/api/admin/reauth/route.ts:55-58).

2. Retrieve authenticated admin UUID (authUserId) (:60-63).

3. Parse payload containing either password or confirmEmail (:65-74, reauthSchema.ts:9-17).

4. Inspect account identity provider via getUserById to determine challenge strategy (:81-88).

5. Execute challenge verification:

   • Password challenge: authenticate credentials via standalone client signInWithPassword({email, password}) with persistSession: false (:97-131). On invalid credentials, log REAUTH_FAILED to audit_logs and return 401. On connection error, log REAUTH_FAILED and return 500.

   • OAuth challenge: assert confirmEmail matches account email exactly (:149-167). On mismatch, log REAUTH_FAILED to audit_logs and return 401.

6. Issue token via issueReAuthToken(authUserId, adminId, ip), creating an opaque UUID with a 5-minute expiration in admin_reauth_tokens (:170, lib/auth/reauth.ts:15-37).

7. Log REAUTH_SUCCESS to audit_logs and return 200 with {token, expiresInSeconds: 300} (:172-185).

• Supporting details: When consuming via consumeReAuthToken(adminClerkId, token), verify token exists, verify matching admin_clerk_id, assert used = false, verify expires_at >= NOW(), and update used = true.

User Profile Synchronization

1. Authenticate caller using authenticateRole("ADMIN") (app/api/admin/sync-users/route.ts:56).

2. If clerkId parameter is provided, look up target identity via getUserById, format metadata using profileFromAuthUser, and update app_users by clerk_id (:64-93).

3. If no parameter is provided, select all non-null clerk_id values from app_users and process profiles in batches of 10 using Promise.all (:96-142).

4. Return aggregate summary {synced, failed, errors?} (:145-149).

• Supporting details: Name resolution follows the exact priority hierarchy used by triggers: full_name → name → preferred_username → combined first/last → local email segment.

User Lookup

1. Authenticate caller using authenticateRole("ADMIN") (app/api/admin/lookup-user/route.ts:18).

2. Record enumeration audit log with action USER_LOOKUP (:30-41).

3. Query app_users by normalized lowercase email (:43-55).

4. Determine account eligibility: if user possesses LEARNER, ADMIN, or AGENCY as their primary role, flag as ineligible with specific error reason; otherwise flag as eligible (:57-74).

5. Return user details and eligibility flag (:76-82).

Infrastructure & Operations

Dependencies

Upstream:

• Supabase Postgres: data storage across app_users, user_roles, admin_reauth_tokens, audit_logs, agencies, and agency_members.

• Supabase Auth Admin API: identity management functions createUser, deleteUser, getUserById, and listUsers (lib/supabase/admin.ts).

• Service Role Client: unconstrained Postgres client supabase from lib/supabase.ts.

• Environment Variables: NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY, and SUPABASE_SERVICE_ROLE_KEY.

Downstream:

• Email Delivery: EmailService.sendEmail and EmailService.getBrandingData (lib/email/emailService.ts) utilizing template lib/email/templates/UserInvitationEmail.tsx.

• Client Consumers: Next.js frontend pages and dialog modals under app/admin/manage-users/page.tsx and components/admin/manage-users/*.

Monitoring & Alerting

Observability is maintained through structured log outputs and audit records; no standalone Prometheus or Grafana metric exporters are registered for this feature.

Deployment Plan

Migrations:

Apply migrations in strict timestamp order:

1. 20260219_add_reviewer_role.sql

2. 20260414_create_audit_logs_table.sql

3. 20260430_add_profile_image_url_to_app_users.sql

4. 20260526_create_admin_reauth_tokens.sql (re-creates audit_logs, indexes, and initial token policies)

5. 20260612_create_user_roles_multi_role.sql (creates user_roles, executes initial role backfill, relaxes role constraints, adds helper functions)

6. 20260612_update_rls_policies_multi_role.sql (updates RLS policies to use multi-role helper functions; depends on previous migration)

7. 20260909_create_auth_users_triggers.sql (provisions triggers on auth.users; must be applied after historical identity backfill)

Backfills:

• Multi-Role Backfill: Execute INSERT INTO user_roles (user_id, role) SELECT id, role FROM app_users ON CONFLICT DO NOTHING (20260612_create_user_roles_multi_role.sql:33-36).

• Profile Image Column: Handled via ALTER TABLE app_users ADD COLUMN IF NOT EXISTS profile_image_url TEXT (20260430_add_profile_image_url_to_app_users.sql:8-9).

• Identity Backfill: Run lib/supabase/scripts/backfill-supabase-auth.ts to write auth_migration_map and update app_users.clerk_id values prior to enabling auth triggers.

Rollout:

• Feature Flags: No feature flags gate this feature; access is restricted exclusively by the ADMIN role.

• Point of No Return: Once app_users.clerk_id is migrated to Supabase Auth UUIDs and legacy identity integration is deprecated, rollback requires restoring from auth_migration_map.

• Post-Deployment Verification: Create a test user via auth.admin.createUser and confirm handle_new_user triggers provision an app_users row and assign the default LEARNER role in user_roles.

Testing & Quality Assurance

Test Strategy

• Unit Testing (Jest, Node environment):

  • Location: tests/api/suspension-enforcement.test.ts (396 lines).

  • Scope: Validates AccountSuspendedError status codes and JSON envelope format; confirms fail-open semantics on database lookup errors; validates suspension checks across both direct database queries and cached authentication hits; ensures cache invalidation via invalidateAuthCaches; verifies global error mapping in ApiResponseHelper.handleError.

• Integration Testing:

  • Location: tests/integration/user-attribution-db.test.ts.

  • Scope: Tests user attribution and clerk_id persistence behavior across database operations.

• End-to-End Testing (Playwright):

  • tests/e2e/admin/secure-user-delete.spec.ts (QA-073.5): Verifies user deletion is blocked when an incorrect administrator password is provided, asserting that the identity confirmation modal remains open.

  • tests/e2e/admin/role-change-verification-prompt.spec.ts (QA-081): Asserts that modifying a user's platform roles triggers the "Confirm Identity" challenge modal.

  • tests/e2e/suspension/suspension.spec.ts: Validates account lockout and UI redirection behavior when suspended users attempt site navigation.

Known Limitations

• Missing Unit Test Coverage on Route Handlers: No unit tests exist for list-users, change-role, invite-user, suspend-user, unsuspend-user, sync-users, lookup-user, or reauth. Only shared suspension middleware and authentication wrappers have unit tests.

• Brittle End-to-End Tests: E2E specifications embed hardcoded credentials (secure-user-delete.spec.ts:18,28, role-change-verification-prompt.spec.ts:18,28) and rely on fragile selectors including auto-generated IDs (#radix-_r_2e_) and positional table rows (tr:nth-child(26) > td:nth-child(4)).

• Hard Deletes Only: User deletion removes records directly from app_users without a soft-delete grace period or deleted_at timestamp.

• Orphan Risk on Partial Deletion: If database deletion of app_users fails after auth.admin.deleteUser has run, the authentication account is permanently lost while the application profile remains stranded.

• Non-Transactional Role Updates: Role adjustments execute across multiple sequential database calls; errors between role deletions and role insertions can leave user assignments partially applied.

• In-Memory Cache Propagation Delay: invalidateAuthCaches invalidates memory exclusively within the serving process; multi-instance environments experience cache staleness up to 5 minutes for user ID lookups and 30 seconds for role lookups.

• Missing Token Cleanup Job: Expired records in admin_reauth_tokens are never purged by an automated routine, requiring operational maintenance.

• Out-of-Band Schema Management for app_users: The core app_users table lacks an initial checked-in migration file, preventing clean schema initialization in local environments via supabase db reset.

• Stale User Interface Labels: UI text displays "Sync from Clerk" (page.tsx:393-397) despite identity logic utilizing Supabase Auth. Dropdowns also continue to expose the legacy AGENCY role option.

• Inconsistent Payload Validation: suspend-user and unsuspend-user accept arbitrary string identifiers via z.string().min(1) instead of enforcing strict UUID validation as used in change-role.

• Asymmetric Security Challenges: Account suspension requires re-authentication, whereas account unsuspension requires only standard administrative authorization.

• Single-Role Eligibility Checks: lookup-user determines eligibility by reading app_users.role rather than checking assignments across the full user_roles relation.

• Permissive Audit Log Insert Policy: audit_logs allows unvalidated record insertions from any client possessing an API key via WITH CHECK (true).

• Superseded Token RLS Policies: RLS rules on admin_reauth_tokens allow administrative read access when using the anonymous client key, rather than completely denying external client access.

• Branch Divergence on Email Rate Limiting: Rate-limiting utilities for user invitations (lib/auth/email-rate-limit.ts) present on origin/develop are omitted in this branch checkout.

Maintenance & Support

Troubleshooting

Changelog

• September 25, 2026 — Improve email rate-limit enforcement/tracking (origin/develop only) (8586c812, bce0ff8d).

• September 22, 2026 — Invite-email send rate limiting (origin/develop only) (263f62db).

• September 21, 2026 — Relocate suspension into the auth chain, unify 403 ACCOUNT_SUSPENDED on bypass routes, cache invalidation, reviewer-page router-cache opt-out on fix/perf-middleware-claims-router-cache (18249c59, adf8a0ed, 11d352cf, 2f3daaa, PR #840, merge de87a0ab).

• September 10, 2026 — Supabase Auth Phase 2 & 3: swap admin/reauth/change-role/invite/delete/suspend/sync auth layer; shared name helper, reauth error classification on develop (3e0e48c3, c8241ffd, 97f2b5e5).
  

• September 09, 2026 — auth.users triggers provision app_users + seed LEARNER role, replacing Clerk Svix webhooks (20260909_create_auth_users_triggers.sql).

• August 20, 2026 — Standardize destructive actions on shared ConfirmDestructiveModal on develop (13373adb, PR #777).

• August 18, 2026 — Admin email invite to Manage Users; secure invite (no credentials in email, rollback on failure) on develop (af8bc9cd, 63fa942c).

• July 02, 2026 — Orphaned members / dropdown entries / autofill fixes on develop (622b43f7).

• July 01, 2026 — Multi-role UI: agency roles in roles[], MultiSelect fixes, suspend re-auth fixes on develop (fe8c7321, 1c193458, d1e06bca, d5ce8e8f, 015e278a, 49c22765, b50e31f5, dcdd23d9).

• May 30, 2026 — Release syncs on develop (18806bee, PR #558; 085d6ed1, PR #520).

• June 03, 2026 — Release sync on develop (4b16f314, PR #563).

• June 13, 2026 — Finish multi-role across admin routes and Manage Users UI on develop (695b743e).

• June 12, 2026 — Multi-role system (20260612_create_user_roles_multi_role.sql, 20260612_update_rls_policies_multi_role.sql) + fallback on develop (e03c0f29, 54707ee5).

• June 10, 2026 — Release sync develop → staging on develop (4c15fb6d, PR #607).

• June 09, 2026 — Audit entity_id/unsuspend fixes; de-duplicate Status header on develop (2f13874b, dc9cc0ca).

• June 08, 2026 — Super Admin UI/UX fixes; Sushi Standards cleanup on develop (cd05d235, a86e7f83).

• June 05, 2026 — Restrict agency ownership to ADMIN/AGENCY; remove legacy label on develop (b2ff74be, cc146ae3).

• June 03, 2026 — Member-picker filters & empty state; PR review fixes on develop (196988b5, a8d79993, 380048b7, d98e1ae1).

• June 02, 2026 — Sync user profile; tooltip; agency-owner business rules; lookup-user on develop (4e62fc54, 96efa0e7, 22563d1a, 80481c36, 65e148e1).

• May 26, 2026 — W0.5 re-auth tokens (20260526_create_admin_reauth_tokens.sql), decouple delete from DeleteUserModal, reauth hardening, View-As ADMIN enforcement on develop (235f3f80, 484320dd, 01cea9e7, 9c85ab0a, c5d9fe7b, e99b88d0, d250640d, 3fdabac4, 95943621, 93ae206b).

• May 21, 2026 — Fix 10 silent bugs across API routes/UI on develop (30654059).

• April 16, 2026 — Mobile responsiveness and button spacing on develop (34725c9b).

• April 15, 2026 — Merge develop into feature/secure-user-delete (ae4b4adf).

• April 14, 2026 — Secure user delete with admin password + audit logging (20260414_create_audit_logs_table.sql); disable autofill on develop (97f2b7e6, e0558f8b).

• April 13, 2026 — Agency owner-picker filtering, mode=picker, change-role auth hardening, AGENCY legacy handling on develop (dd7c0432, 6e23dd39, 30360977, 0d4f1d39, 5a2c0d83, ca3c043e, f6665f78, 4f961ba4, 22594833, 3033cae9).

• March 25, 2026 — Restore AGENCY role option in dropdown on develop (66351a43).

• March 23, 2026 — Remove console.logs on develop (9e0df77a).

• March 18, 2026 — Rollback when Supabase status update fails on develop (4a2bf638).

• March 17, 2026 — Add suspend and unsuspend user actions on develop (5c920d20).

• February 20, 2026 — Rename USER → LEARNER in ManageUsersPage on develop (2208649e).

• February 19, 2026 — Add REVIEWER role — DB constraint (20260219_add_reviewer_role.sql) + admin UI on develop (ffe339d2).

• October 07, 2025 — Global color theme for agencies / app on develop (9bc834c0, bbc826c2).

• October 05, 2025 — Codebase revision and restructuring on develop (86583d17).

• July 15, 2025 — Fixed manage-users UI on develop (3c626b7a).

• July 14, 2025 — Fixed root layouts on develop (858f9c72).

• July 10, 2025 — Initial RBAC with Clerk + Supabase; first manage-users routes/UI on develop (55f5df18).

Document version:

1.0 - Draft, Initial Technical Guide, 07/29/2026

1.1 - Published, Manage Users Technical Guide, 07/29/2026