bug: Post-migration bugs from UUID identity migration (#206) #220

New Issue

egullickson · 2026-02-16T17:18:42Z

egullickson commented

2026-02-16 17:18:42 +00:00

Context

PR #219 migrated all user identity from auth0_sub VARCHAR(255) to user_profiles.id UUID. The app has bugs after this migration. This issue documents all changes made for a debugging session.

Architecture Change

Before: JWT sub ("auth0|xxx") -> userContext.userId -> WHERE user_id = auth0_sub
After:  JWT sub ("auth0|xxx") -> profile lookup -> userContext.userId (UUID) -> WHERE user_id = UUID

auth0_sub now exists ONLY in user_profiles table for JWT-to-profile resolution. All other tables reference user_profiles.id UUID via user_id FK.

Files Changed (38 files, 9 commits)

Migration SQL

backend/src/core/identity-migration/migrations/001_migrate_user_id_to_uuid.sql (NEW) - 5-phase migration: add UUID columns, backfill, admin restructure, constraints, drop/rename. Two patches already applied for user_preferences dedup/mixed-format rows.
backend/src/_system/migrations/run-all.ts - Added core/identity-migration to end of MIGRATION_ORDER.

Auth Plugin (core change)

backend/src/core/plugins/auth.plugin.ts - Renamed userId to auth0Sub for JWT sub. After getOrCreate(auth0Sub), sets userId = profile.id (UUID). Auth0 Management API calls use auth0Sub. updateEmail() and updateEmailVerified() called with userId (UUID).
backend/src/core/plugins/admin-guard.plugin.ts - Query changed from WHERE auth0_sub = $1 to WHERE user_profile_id = $1. SELECT now returns id, user_profile_id instead of auth0_sub.

Admin System (full refactor)

backend/src/features/admin/domain/admin.types.ts - AdminUser.auth0Sub replaced with id + userProfileId. Revoke/reinstate/bulk requests use id instead of auth0Sub.
backend/src/features/admin/data/admin.repository.ts - getAdminByAuth0Sub() replaced with getAdminById() + getAdminByUserProfileId(). createAdmin() accepts userProfileId instead of auth0Sub. revokeAdmin()/reinstateAdmin() use id. mapRowToAdminUser maps id + user_profile_id. Removed updateAuth0SubByEmail().
backend/src/features/admin/domain/admin.service.ts - Added getAdminByUserProfileId(). createAdmin() takes userProfileId + createdByAdminId. revokeAdmin()/reinstateAdmin() take id + actor admin ID. Removed linkAdminAuth0Sub().
backend/src/features/admin/api/admin.controller.ts - verifyAccess uses getAdminByUserProfileId(userId) (removed email fallback + linkAdmin logic + debug logs). createAdmin looks up user_profiles by email to get UUID. Revoke/reinstate get actor's admin record for admin ID. Response returns id/userProfileId instead of auth0Sub.
backend/src/features/admin/api/users.controller.ts - All route params changed from :auth0Sub to :userId. All methods use userId (UUID) from params. promoteToAdmin looks up actor admin record.
backend/src/features/admin/api/admin.routes.ts - Routes: :auth0Sub -> :id (admin routes), :auth0Sub -> :userId (user routes).
backend/src/features/admin/api/admin.validation.ts - adminAuth0SubSchema -> adminIdSchema with .uuid() validation. Bulk schemas use ids array with .uuid().
backend/src/features/admin/api/users.validation.ts - userAuth0SubSchema -> userIdSchema with .uuid() validation.

User Profile (parameter type change)

backend/src/features/user-profile/data/user-profile.repository.ts - Added getById(id). Changed 15+ methods from auth0Sub parameter to userId (UUID): update, updateSubscriptionTier, deactivateUser, reactivateUser, adminUpdateProfile, updateEmailVerified, markOnboardingComplete, updateEmail, requestDeletion, cancelDeletion, hardDeleteUser, getUserWithAdminStatus, getUserVehiclesForAdmin. All SQL changed from WHERE auth0_sub = $1 to WHERE id = $1. listAllUsers JOIN changed: v.user_id = up.id, au.user_profile_id = up.id. mapRowToUserWithAdminStatus: isAdmin checks row.admin_id instead of row.admin_auth0_sub.
backend/src/features/user-profile/domain/user-profile.service.ts - All methods changed from auth0Sub to userId (UUID). Uses getById() instead of getByAuth0Sub() for lookups. adminHardDeleteUser now calls auth0ManagementClient.deleteUser(profile.auth0Sub) using profile's auth0Sub.
backend/src/features/user-profile/api/user-profile.controller.ts - getProfile uses userProfileRepository.getById(userId) instead of getOrCreateProfile(). All methods use userId from userContext. Added userProfileRepository as class field. getDeletionStatus also changed to use getById().

Supporting Code

backend/src/features/audit-log/data/audit-log.repository.ts - JOIN changed from up.auth0_sub to up.id.
backend/src/features/backup/api/backup.controller.ts - Uses userContext.userId instead of auth0Sub.
backend/src/features/ocr/api/ocr.controller.ts - All 7 endpoints changed from (request as any).user?.sub to request.userContext?.userId.

Frontend

frontend/src/features/admin/types/admin.types.ts - AdminUser.auth0Sub -> id + userProfileId.
frontend/src/features/admin/api/admin.api.ts - revokeAdmin/reinstateAdmin use id. All users.* methods use userId param. Removed encodeURIComponent() wrappers.
frontend/src/features/admin/hooks/useAdmins.ts - Mutation functions use id instead of auth0Sub.
frontend/src/features/admin/hooks/useUsers.ts - All mutation shapes use userId instead of auth0Sub. Query keys use userId.
frontend/src/pages/admin/AdminUsersPage.tsx - All user.auth0Sub references -> user.id. Vehicle expansion, tier change, deactivate, reactivate, edit, promote, delete all use user.id.
frontend/src/features/admin/mobile/AdminUsersMobileScreen.tsx - Same changes as desktop: user.auth0Sub -> user.id throughout.

Tests

backend/src/features/admin/tests/unit/admin.service.test.ts - Test mocks use id/userProfileId instead of auth0Sub.
backend/src/features/admin/tests/integration/admin.integration.test.ts - Updated for new admin schema and UUID params.
backend/src/features/auth/tests/unit/auth.service.test.ts - Added deletionRequestedAt/deletionScheduledFor mock fields.
backend/src/features/auth/tests/integration/auth.integration.test.ts - Minor fixture update.
backend/src/features/admin/tests/unit/admin.guard.test.ts - UUID test values.
backend/src/features/audit-log/__tests__/audit-log.integration.test.ts - UUID test values.
backend/src/core/middleware/require-tier.test.ts - UUID test value.
backend/src/core/plugins/tests/tier-guard.plugin.test.ts - UUID test values.
backend/src/features/fuel-logs/tests/fixtures/fuel-logs.fixtures.json - UUID userId.
backend/src/features/maintenance/tests/fixtures/maintenance.fixtures.json - UUID userId.
backend/src/features/stations/tests/integration/community-stations.api.test.ts - UUID test values.
frontend/src/features/admin/__tests__/* - UUID test values.

Key Invariants (post-migration)

userContext.userId is always UUID after auth plugin
request.user.sub is always auth0_sub format (raw JWT)
auth0_sub only exists in user_profiles table
admin_users has own id UUID PK + user_profile_id UUID FK (not renamed to user_id)
All feature tables: user_id UUID references user_profiles.id
Admin routes: /admin/admins/:id/... and /admin/users/:userId/...

PR: #219 ## Context PR #219 migrated all user identity from `auth0_sub VARCHAR(255)` to `user_profiles.id UUID`. The app has bugs after this migration. This issue documents all changes made for a debugging session. ## Architecture Change ``` Before: JWT sub ("auth0|xxx") -> userContext.userId -> WHERE user_id = auth0_sub After: JWT sub ("auth0|xxx") -> profile lookup -> userContext.userId (UUID) -> WHERE user_id = UUID ``` `auth0_sub` now exists ONLY in `user_profiles` table for JWT-to-profile resolution. All other tables reference `user_profiles.id` UUID via `user_id` FK. ## Files Changed (38 files, 9 commits) ### Migration SQL - `backend/src/core/identity-migration/migrations/001_migrate_user_id_to_uuid.sql` (NEW) - 5-phase migration: add UUID columns, backfill, admin restructure, constraints, drop/rename. Two patches already applied for `user_preferences` dedup/mixed-format rows. - `backend/src/_system/migrations/run-all.ts` - Added `core/identity-migration` to end of MIGRATION_ORDER. ### Auth Plugin (core change) - `backend/src/core/plugins/auth.plugin.ts` - Renamed `userId` to `auth0Sub` for JWT sub. After `getOrCreate(auth0Sub)`, sets `userId = profile.id` (UUID). Auth0 Management API calls use `auth0Sub`. `updateEmail()` and `updateEmailVerified()` called with `userId` (UUID). - `backend/src/core/plugins/admin-guard.plugin.ts` - Query changed from `WHERE auth0_sub = $1` to `WHERE user_profile_id = $1`. SELECT now returns `id, user_profile_id` instead of `auth0_sub`. ### Admin System (full refactor) - `backend/src/features/admin/domain/admin.types.ts` - `AdminUser.auth0Sub` replaced with `id` + `userProfileId`. Revoke/reinstate/bulk requests use `id` instead of `auth0Sub`. - `backend/src/features/admin/data/admin.repository.ts` - `getAdminByAuth0Sub()` replaced with `getAdminById()` + `getAdminByUserProfileId()`. `createAdmin()` accepts `userProfileId` instead of `auth0Sub`. `revokeAdmin()`/`reinstateAdmin()` use `id`. `mapRowToAdminUser` maps `id` + `user_profile_id`. Removed `updateAuth0SubByEmail()`. - `backend/src/features/admin/domain/admin.service.ts` - Added `getAdminByUserProfileId()`. `createAdmin()` takes `userProfileId` + `createdByAdminId`. `revokeAdmin()`/`reinstateAdmin()` take `id` + actor admin ID. Removed `linkAdminAuth0Sub()`. - `backend/src/features/admin/api/admin.controller.ts` - `verifyAccess` uses `getAdminByUserProfileId(userId)` (removed email fallback + linkAdmin logic + debug logs). `createAdmin` looks up `user_profiles` by email to get UUID. Revoke/reinstate get actor's admin record for admin ID. Response returns `id`/`userProfileId` instead of `auth0Sub`. - `backend/src/features/admin/api/users.controller.ts` - All route params changed from `:auth0Sub` to `:userId`. All methods use `userId` (UUID) from params. `promoteToAdmin` looks up actor admin record. - `backend/src/features/admin/api/admin.routes.ts` - Routes: `:auth0Sub` -> `:id` (admin routes), `:auth0Sub` -> `:userId` (user routes). - `backend/src/features/admin/api/admin.validation.ts` - `adminAuth0SubSchema` -> `adminIdSchema` with `.uuid()` validation. Bulk schemas use `ids` array with `.uuid()`. - `backend/src/features/admin/api/users.validation.ts` - `userAuth0SubSchema` -> `userIdSchema` with `.uuid()` validation. ### User Profile (parameter type change) - `backend/src/features/user-profile/data/user-profile.repository.ts` - Added `getById(id)`. Changed 15+ methods from `auth0Sub` parameter to `userId` (UUID): `update`, `updateSubscriptionTier`, `deactivateUser`, `reactivateUser`, `adminUpdateProfile`, `updateEmailVerified`, `markOnboardingComplete`, `updateEmail`, `requestDeletion`, `cancelDeletion`, `hardDeleteUser`, `getUserWithAdminStatus`, `getUserVehiclesForAdmin`. All SQL changed from `WHERE auth0_sub = $1` to `WHERE id = $1`. `listAllUsers` JOIN changed: `v.user_id = up.id`, `au.user_profile_id = up.id`. `mapRowToUserWithAdminStatus`: `isAdmin` checks `row.admin_id` instead of `row.admin_auth0_sub`. - `backend/src/features/user-profile/domain/user-profile.service.ts` - All methods changed from `auth0Sub` to `userId` (UUID). Uses `getById()` instead of `getByAuth0Sub()` for lookups. `adminHardDeleteUser` now calls `auth0ManagementClient.deleteUser(profile.auth0Sub)` using profile's auth0Sub. - `backend/src/features/user-profile/api/user-profile.controller.ts` - `getProfile` uses `userProfileRepository.getById(userId)` instead of `getOrCreateProfile()`. All methods use `userId` from `userContext`. Added `userProfileRepository` as class field. `getDeletionStatus` also changed to use `getById()`. ### Supporting Code - `backend/src/features/audit-log/data/audit-log.repository.ts` - JOIN changed from `up.auth0_sub` to `up.id`. - `backend/src/features/backup/api/backup.controller.ts` - Uses `userContext.userId` instead of `auth0Sub`. - `backend/src/features/ocr/api/ocr.controller.ts` - All 7 endpoints changed from `(request as any).user?.sub` to `request.userContext?.userId`. ### Frontend - `frontend/src/features/admin/types/admin.types.ts` - `AdminUser.auth0Sub` -> `id` + `userProfileId`. - `frontend/src/features/admin/api/admin.api.ts` - `revokeAdmin`/`reinstateAdmin` use `id`. All `users.*` methods use `userId` param. Removed `encodeURIComponent()` wrappers. - `frontend/src/features/admin/hooks/useAdmins.ts` - Mutation functions use `id` instead of `auth0Sub`. - `frontend/src/features/admin/hooks/useUsers.ts` - All mutation shapes use `userId` instead of `auth0Sub`. Query keys use `userId`. - `frontend/src/pages/admin/AdminUsersPage.tsx` - All `user.auth0Sub` references -> `user.id`. Vehicle expansion, tier change, deactivate, reactivate, edit, promote, delete all use `user.id`. - `frontend/src/features/admin/mobile/AdminUsersMobileScreen.tsx` - Same changes as desktop: `user.auth0Sub` -> `user.id` throughout. ### Tests - `backend/src/features/admin/tests/unit/admin.service.test.ts` - Test mocks use `id`/`userProfileId` instead of `auth0Sub`. - `backend/src/features/admin/tests/integration/admin.integration.test.ts` - Updated for new admin schema and UUID params. - `backend/src/features/auth/tests/unit/auth.service.test.ts` - Added `deletionRequestedAt`/`deletionScheduledFor` mock fields. - `backend/src/features/auth/tests/integration/auth.integration.test.ts` - Minor fixture update. - `backend/src/features/admin/tests/unit/admin.guard.test.ts` - UUID test values. - `backend/src/features/audit-log/__tests__/audit-log.integration.test.ts` - UUID test values. - `backend/src/core/middleware/require-tier.test.ts` - UUID test value. - `backend/src/core/plugins/tests/tier-guard.plugin.test.ts` - UUID test values. - `backend/src/features/fuel-logs/tests/fixtures/fuel-logs.fixtures.json` - UUID userId. - `backend/src/features/maintenance/tests/fixtures/maintenance.fixtures.json` - UUID userId. - `backend/src/features/stations/tests/integration/community-stations.api.test.ts` - UUID test values. - `frontend/src/features/admin/__tests__/*` - UUID test values. ## Key Invariants (post-migration) 1. `userContext.userId` is always UUID after auth plugin 2. `request.user.sub` is always auth0_sub format (raw JWT) 3. `auth0_sub` only exists in `user_profiles` table 4. `admin_users` has own `id UUID PK` + `user_profile_id UUID FK` (not renamed to user_id) 5. All feature tables: `user_id UUID` references `user_profiles.id` 6. Admin routes: `/admin/admins/:id/...` and `/admin/users/:userId/...`

egullickson added the

labels 2026-02-16 17:18:47 +00:00

egullickson referenced this issue

2026-02-16 17:18:48 +00:00

chore: migrate user identity from auth0_sub to UUID #219

egullickson referenced this issue from a commit

2026-02-16 17:39:14 +00:00

fix: migrate remaining controllers from Auth0 sub to UUID identity (refs #220)