# 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**:
```typescript
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**:
   ```bash
   # 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