chore: Database migration SQL - auth0_sub to UUID (#206) #211

New Issue

egullickson · 2026-02-16T15:01:32Z

egullickson commented

2026-02-16 15:01:32 +00:00

Relates to #206

Milestone 1: Database migration SQL

Multi-phase SQL migration to convert all user_id columns from VARCHAR(255) storing auth0_sub to UUID referencing user_profiles.id.

Phases

Add user_profile_id UUID column to all 18 affected tables
Backfill UUID values from user_profiles join
admin_users: add id UUID PK, add user_profile_id UUID, backfill, create user_profiles for admins without one
Set NOT NULL constraints, add FK constraints with ON DELETE CASCADE, add indexes
Drop old VARCHAR columns and FK constraints, rename user_profile_id to user_id

Files

backend/src/features/user-profile/migrations/005_migrate_user_id_to_uuid.sql (new)

Acceptance Criteria

Migration executes without error
All tables have user_id UUID referencing user_profiles.id
user_profiles.auth0_sub remains as VARCHAR UNIQUE column
admin_users has id UUID PK + user_profile_id UUID FK
No data loss (row counts match before/after)
All FK constraints pass validation

Relates to #206 ## Milestone 1: Database migration SQL Multi-phase SQL migration to convert all user_id columns from VARCHAR(255) storing auth0_sub to UUID referencing user_profiles.id. ### Phases 1. Add `user_profile_id UUID` column to all 18 affected tables 2. Backfill UUID values from `user_profiles` join 3. admin_users: add `id UUID PK`, add `user_profile_id UUID`, backfill, create user_profiles for admins without one 4. Set NOT NULL constraints, add FK constraints with ON DELETE CASCADE, add indexes 5. Drop old VARCHAR columns and FK constraints, rename `user_profile_id` to `user_id` ### Files - `backend/src/features/user-profile/migrations/005_migrate_user_id_to_uuid.sql` (new) ### Acceptance Criteria - [ ] Migration executes without error - [ ] All tables have user_id UUID referencing user_profiles.id - [ ] user_profiles.auth0_sub remains as VARCHAR UNIQUE column - [ ] admin_users has id UUID PK + user_profile_id UUID FK - [ ] No data loss (row counts match before/after) - [ ] All FK constraints pass validation

egullickson added the

labels 2026-02-16 15:02:02 +00:00

egullickson added this to the Sprint 2026-02-02 milestone 2026-02-16 15:02:07 +00:00

egullickson referenced this issue

2026-02-16 15:55:46 +00:00

chore: Migrate user identity from auth0_sub to MVP-generated UUID #206

egullickson referenced this issue

2026-02-16 16:21:45 +00:00