usda-vision/docs/OAUTH_USER_SYNC_FIX.md

OAuth User Synchronization Fix

Problem

When a user signs on with an OAuth provider (Microsoft Entra/Azure AD) for the first time, the user is added to auth.users in Supabase but NOT to the application's user_profiles table. This causes the application to fail when trying to load user data, as there's no user profile available.

Solution

Implemented automatic user profile creation for OAuth users through a multi-layered approach:

1. Database Trigger (00003_oauth_user_sync.sql)

• Location: supabase/migrations/00003_oauth_user_sync.sql and management-dashboard-web-app/supabase/migrations/00003_oauth_user_sync.sql

• Function: handle_new_oauth_user()

  • Automatically creates a user profile in public.user_profiles when a new user is created in auth.users
  • Handles the synchronization at the database level, ensuring it works regardless of where users are created
  • Includes race condition handling with ON CONFLICT DO NOTHING

• Trigger: on_auth_user_created

  • Fires after INSERT on auth.users
  • Ensures every new OAuth user gets a profile entry

2. Client-Side Utility Function (src/lib/supabase.ts)

• Function: userManagement.syncOAuthUser()
  • Provides a fallback synchronization mechanism for any OAuth users that slip through
  • Checks if user profile exists before creating
  • Handles race conditions gracefully (duplicate key errors)
  • Includes comprehensive error logging for debugging

Logic:

1. Get current authenticated user from Supabase Auth
2. Check if user profile already exists in user_profiles table
3. If exists: return (no action needed)
4. If not exists: create user profile with:
   - id: user's UUID from auth.users
   - email: user's email from auth.users
   - status: 'active' (default status)
5. Handle errors gracefully:
   - "No rows returned" (PGRST116): Expected, user doesn't exist yet
   - "Duplicate key" (23505): Race condition, another process created it first
   - Other errors: Log and continue

3. App Integration (src/App.tsx)

• Updated: Auth state change listener
• Triggers: When SIGNED_IN or INITIAL_SESSION event occurs
• Action: Calls userManagement.syncOAuthUser() asynchronously
• Benefit: Ensures user profile exists before the rest of the app tries to access it

How It Works

OAuth Sign-In Flow (New)

1. User clicks "Sign in with Microsoft"
   ↓
2. Redirected to Microsoft login
   ↓
3. Microsoft authenticates and redirects back
   ↓
4. Supabase creates entry in auth.users
   ↓
5. Database trigger fires → user_profiles entry created
   ↓
6. App receives SIGNED_IN event
   ↓
7. App calls syncOAuthUser() as extra safety measure
   ↓
8. User profile is guaranteed to exist
   ↓
9. getUserProfile() and loadData() succeed

Backward Compatibility

• Non-invasive: The solution uses triggers and utility functions, doesn't modify existing tables
• Graceful degradation: If either layer fails, the other provides a fallback
• No breaking changes: Existing APIs and components remain unchanged

Testing Recommendations

Test 1: First-Time OAuth Sign-In

1. Clear browser cookies/session
2. Click "Sign in with Microsoft"
3. Complete OAuth flow
4. Verify:
   • User is in Supabase auth
   • User is in user_profiles table
   • App loads user data without errors
   • Dashboard displays correctly

Test 2: Verify Database Trigger

1. Directly create a user in auth.users via SQL
2. Verify that user_profiles entry is automatically created
3. Check timestamp to confirm trigger fired

Test 3: Verify Client-Side Fallback

1. Manually delete a user's user_profiles entry
2. Reload the app
3. Verify that syncOAuthUser() recreates the profile
4. Check browser console for success logs

Files Modified

1. Database Migrations:

   • /usda-vision/supabase/migrations/00003_oauth_user_sync.sql (NEW)
   • /usda-vision/management-dashboard-web-app/supabase/migrations/00003_oauth_user_sync.sql (NEW)

2. TypeScript/React:

   • /usda-vision/management-dashboard-web-app/src/lib/supabase.ts (MODIFIED)

     • Added syncOAuthUser() method to userManagement object

   • /usda-vision/management-dashboard-web-app/src/App.tsx (MODIFIED)

     • Import userManagement
     • Call syncOAuthUser() on auth state change

Deployment Steps

1. Apply Database Migrations:

   # Run the new migration
   supabase migration up
   

2. Deploy Application Code:

   • Push the changes to src/lib/supabase.ts and src/App.tsx
   • No environment variable changes needed
   • No configuration changes needed

3. Test in Staging:

   • Test OAuth sign-in with a fresh account
   • Verify user profile is created
   • Check app functionality

4. Monitor in Production:

   • Watch browser console for any errors from syncOAuthUser()
   • Check database logs to confirm trigger is firing
   • Monitor user creation metrics

Future Enhancements

• Assign default roles to new OAuth users (currently requires manual assignment)
• Pre-populate first_name and last_name from OAuth provider data
• Add user profile completion workflow for new OAuth users
• Auto-disable account creation for users outside organization