# Fix XP Race Condition Plan

## Problem
`src/routes/xp.js` uses a read-modify-write pattern for XP updates, causing race conditions:

```javascript
// Line 134-138: READ
let { data: xpData } = await supabase.from('user_xp').select('*').eq('user_id', userId).single();

// Line 147: MODIFY (in JavaScript)
const newTotalXP = xpData.total_xp + amount;

// Line 177-186: WRITE
await supabase.from('user_xp').update({ total_xp: newTotalXP }).eq('user_id', userId);
```

**Race Condition Example:**
```
Request A: SELECT total_xp → 100
Request B: SELECT total_xp → 100 (before A updates)
Request A: UPDATE total_xp = 150 (100 + 50)
Request B: UPDATE total_xp = 150 (100 + 50)
Result: 150 XP (should be 200!)
```

**Additional Issues Found:**
1. `add_user_xp` RPC function is called in `duelsService.js` and `challengesServiceV2.js` but doesn't exist
2. Fallback code uses invalid `supabase.raw()` syntax

## Severity
**HIGH** - Data corruption possible under concurrent requests (common during competitions/duels)

## Solution
Create a PostgreSQL function for atomic XP increment and use it consistently.

## Database Migration

```sql
-- Atomic XP increment function
-- Handles: XP addition, level calculation, streak updates
CREATE OR REPLACE FUNCTION add_user_xp(
  p_user_id UUID,
  p_xp_amount INTEGER,
  p_action TEXT DEFAULT NULL,
  p_reason TEXT DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
  v_result JSON;
  v_old_xp INTEGER;
  v_new_xp INTEGER;
  v_old_level INTEGER;
  v_new_level INTEGER;
  v_level_up BOOLEAN := FALSE;
  v_streak INTEGER;
  v_streak_bonus INTEGER := 0;
  v_last_activity DATE;
  v_today DATE := CURRENT_DATE;
BEGIN
  -- Lock the row for update (prevents race condition)
  SELECT total_xp, level, current_streak, last_activity_date
  INTO v_old_xp, v_old_level, v_streak, v_last_activity
  FROM user_xp
  WHERE user_id = p_user_id
  FOR UPDATE;

  IF NOT FOUND THEN
    -- Create new record
    INSERT INTO user_xp (user_id, total_xp, level, current_streak, longest_streak, last_activity_date)
    VALUES (p_user_id, p_xp_amount, 1, 1, 1, v_today)
    RETURNING json_build_object(
      'total_xp', total_xp,
      'level', level,
      'level_up', false,
      'streak', current_streak,
      'streak_bonus', 0
    ) INTO v_result;

    RETURN v_result;
  END IF;

  -- Calculate streak bonus
  IF v_last_activity IS NOT NULL THEN
    IF v_today - v_last_activity = 1 THEN
      -- Consecutive day
      v_streak := v_streak + 1;
      v_streak_bonus := LEAST(v_streak * 5, 100); -- Cap at 100
    ELSIF v_today - v_last_activity > 1 THEN
      -- Streak broken
      v_streak := 1;
    END IF;
    -- Same day: keep streak as is
  ELSE
    v_streak := 1;
  END IF;

  -- Calculate new XP and level
  v_new_xp := v_old_xp + p_xp_amount + v_streak_bonus;
  v_new_level := GREATEST(1, FLOOR(SQRT(v_new_xp / 100.0))::INTEGER);
  v_level_up := v_new_level > v_old_level;

  -- Atomic update
  UPDATE user_xp
  SET
    total_xp = v_new_xp,
    level = v_new_level,
    current_streak = v_streak,
    longest_streak = GREATEST(longest_streak, v_streak),
    last_activity_date = v_today
  WHERE user_id = p_user_id;

  -- Return result
  v_result := json_build_object(
    'total_xp', v_new_xp,
    'old_xp', v_old_xp,
    'xp_gained', p_xp_amount + v_streak_bonus,
    'level', v_new_level,
    'old_level', v_old_level,
    'level_up', v_level_up,
    'streak', v_streak,
    'streak_bonus', v_streak_bonus
  );

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;
```

## Code Changes

### File: `src/routes/xp.js`

Replace the read-modify-write pattern in `/award` endpoint with:

```javascript
router.post('/award', async (req, res) => {
  try {
    const userId = req.user?.id;
    if (!userId) {
      return res.status(401).json({ success: false, error: 'User ID required' });
    }

    const { action, customAmount, relatedId, reason } = req.body;
    const amount = customAmount || XP_AWARDS[action] || 0;

    if (amount <= 0) {
      return res.status(400).json({ success: false, error: 'Invalid action or amount' });
    }

    // Use atomic increment function
    const { data, error } = await supabase.rpc('add_user_xp', {
      p_user_id: userId,
      p_xp_amount: amount,
      p_action: action || null,
      p_reason: reason || null
    });

    if (error) throw error;

    res.json({
      success: true,
      ...data,
      action,
    });
  } catch (error) {
    console.error('Error awarding XP:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});
```

### File: `src/services/duelsService.js`

Remove the broken fallback (line 474-480). The RPC will work once we create the function.

## Implementation Steps
- [x] Phase 1: Create `add_user_xp` function in `supabase/xp-atomic-migration.sql` ✅
- [ ] Phase 2: Apply migration to Supabase - QA
- [x] Phase 3: Update `src/routes/xp.js` to use RPC ✅
- [x] Phase 4: Fix `src/services/duelsService.js` fallback ✅
- [ ] Phase 5: Fix `src/services/challengesServiceV2.js` if needed - Skipped (already correct)
- [ ] Phase 6: Test concurrent XP awards - QA

## Build Progress

### ✅ All Code Changes Complete
**Commit:** 024ffd0 "Fix XP race condition with atomic increment function"
**Pushed:** main branch

**Files created:**
- `supabase/xp-atomic-migration.sql` - add_user_xp function with FOR UPDATE locking

**Files modified:**
- `src/routes/xp.js` - Uses RPC, has legacy fallback if function doesn't exist yet
- `src/services/duelsService.js` - Removed broken supabase.raw() fallback
- `supabase/PENDING_MIGRATIONS.md` - Added migration entry

## Files to Modify

| File | Changes |
|------|---------|
| `supabase/xp-atomic-migration.sql` | NEW - Create `add_user_xp` function |
| `src/routes/xp.js` | Replace read-modify-write with RPC call |
| `src/services/duelsService.js` | Remove broken fallback |
| `src/services/challengesServiceV2.js` | Verify RPC usage is correct |

## Success Criteria
1. `add_user_xp` function exists in database
2. Concurrent XP awards don't lose data:
   ```bash
   # Run 10 concurrent +10 XP requests
   # Result should be +100 XP total, not less
   ```
3. Level ups still trigger correctly
4. Streaks still calculated correctly

## Notes for Builder

### Key Points
1. The `FOR UPDATE` clause in the function locks the row during the transaction
2. This prevents concurrent reads from getting stale data
3. Level calculation: `FLOOR(SQRT(total_xp / 100))`
4. Streak bonus: `MIN(streak_days * 5, 100)`

### Test Command
```javascript
// Run this 10 times concurrently
await fetch('/api/xp/award', {
  method: 'POST',
  headers: { 'Authorization': 'Bearer ...', 'Content-Type': 'application/json' },
  body: JSON.stringify({ action: 'call_made' })
});
```

## Notes for QA
1. Test with rapid button clicks to trigger concurrent requests
2. Verify final XP matches expected total
3. Check level-up notifications still work
4. Verify streak bonuses apply correctly

## Why This Matters
- XP is currency in the gamification system
- Race conditions can cheat users out of earned XP
- During competitions (duels, challenges), concurrent updates are common
- Data integrity is critical for trust in the system