Where: supabase/migrations/20260724000001_xp_idempotency.sql, hooks/useMessages.ts, pages/Chat.tsx, components/resources/UploadDialog.tsx, hooks/useCreateSession.ts
Bug: award_activity_xp() logs every award to user_activity_log under UNIQUE NULLS NOT DISTINCT (user_id, activity_type, reference_id). That constraint treats all NULL reference_ids as equal. Three call sites never pass a reference_id:
chat_message (useMessages.ts line 607, Chat.tsx line 417)resource_upload (UploadDialog.tsx line 106)host_session (useCreateSession.ts line 116)
The first time any of these fires for a user, the row inserts fine and XP is granted. Every later attempt same day or months later tries to insert the same (user_id, activity_type, NULL) row, hits the unique constraint, and the function's exception handler silently returns with no XP. The "10 per day" rate-limit branch in the same function is effectively dead code for these three activities, since the unique violation fires before that cap is ever reached.
Expected: Users earn XP each time they send a message / upload a resource / host a session, up to the daily cap.
Actual: XP for these three activities is granted exactly once per user, permanently.
Suggested fix: Pass a distinguishing reference_id per event (e.g. the new message id, upload id, or session id) instead of null, mirroring what session_join already does correctly with sessionId.
Where:
supabase/migrations/20260724000001_xp_idempotency.sql,hooks/useMessages.ts,pages/Chat.tsx,components/resources/UploadDialog.tsx,hooks/useCreateSession.tsBug:
award_activity_xp()logs every award touser_activity_logunderUNIQUE NULLS NOT DISTINCT (user_id, activity_type, reference_id). That constraint treats all NULLreference_ids as equal. Three call sites never pass areference_id:chat_message(useMessages.tsline 607,Chat.tsxline 417)resource_upload(UploadDialog.tsxline 106)host_session(useCreateSession.tsline 116)The first time any of these fires for a user, the row inserts fine and XP is granted. Every later attempt same day or months later tries to insert the same
(user_id, activity_type, NULL)row, hits the unique constraint, and the function's exception handler silently returns with no XP. The "10 per day" rate-limit branch in the same function is effectively dead code for these three activities, since the unique violation fires before that cap is ever reached.Expected: Users earn XP each time they send a message / upload a resource / host a session, up to the daily cap.
Actual: XP for these three activities is granted exactly once per user, permanently.
Suggested fix: Pass a distinguishing
reference_idper event (e.g. the new message id, upload id, or session id) instead ofnull, mirroring whatsession_joinalready does correctly withsessionId.