# Lead Assignment System Plan (Scalable to 20 Users)
**Created:** Jan 20, 2026
**Target:** Monday Jan 27, 2026
**Initial Users:** Tony (sales), Michael (admin)
---
## Investigation Summary
### Current State: User Management
**3 Separate User Storage Systems (Problem!)**
| System | Location | Data | Used For |
|--------|----------|------|----------|
| `auth.users` | Supabase Auth | UUID, email | Login, JWT tokens |
| `ALLOWED_EMAILS` | `AuthContext.jsx:7` | Email array (6 entries) | Frontend whitelist |
| `USER_NAMES` | `AuthContext.jsx:17` | Email→Name mapping | Display names |
| `team_members` | `supabase/schema.sql:105` | name, email, role, phone | Unused |
| `user_xp` | Gamification tables | user_id, display_name, level, streak | Battleground |
**Problems for 20-User Scale:**
1. `ALLOWED_EMAILS` is hardcoded - requires code deploy for new users
2. `USER_NAMES` is hardcoded - display names in code, not DB
3. No role differentiation (admin vs caller vs closer)
4. `team_members` table exists but isn't used
5. No link between auth.users and team_members
### Current State: Lead Assignment
| Component | Status | Details |
|-----------|--------|---------|
| `leads.assigned_to` | ✅ Exists | UUID column in schema.sql line 18 |
| `leads.assigned_at` | ❌ Missing | Need for stale detection |
| `communications.user_id` | ❌ Missing | Need for activity tracking |
| Call list `assignedTo` filter | ❌ Not implemented | Param exists, not used |
---
## Solution: Database-Driven User System
### Design Principles
1. **Single source of truth** - `user_xp` becomes central user profile table
2. **Database-driven whitelist** - Move from hardcoded array to DB query
3. **Roles in database** - admin/closer/caller stored per user
4. **Backwards compatible** - Existing gamification still works
### Why `user_xp`?
- Already has `user_id` (UUID from auth.users)
- Already has `display_name`
- Already connected to gamification/leaderboards
- Just needs `role` and `email` columns added
---
## Database Schema Changes
### Migration 1: Extend user_xp for User Management
```sql
-- Add user management columns to user_xp
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS email VARCHAR(255);
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'caller';
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
-- Create unique index on email for lookup
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_xp_email ON user_xp(email) WHERE email IS NOT NULL;
-- Create index for role-based queries
CREATE INDEX IF NOT EXISTS idx_user_xp_role ON user_xp(role);
```
### Migration 2: Add Lead Assignment Columns
```sql
-- Add assigned_at timestamp to leads
ALTER TABLE leads ADD COLUMN IF NOT EXISTS assigned_at TIMESTAMPTZ;
-- Add user_id to communications for activity tracking
ALTER TABLE communications ADD COLUMN IF NOT EXISTS user_id UUID;
-- Index for fast assignment queries
CREATE INDEX IF NOT EXISTS idx_leads_assigned_to ON leads(assigned_to)
WHERE assigned_to IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_leads_assigned_at ON leads(assigned_at);
CREATE INDEX IF NOT EXISTS idx_communications_user_id ON communications(user_id);
```
### Seed Data: Initial Users
```sql
-- Update existing user_xp records with email and role
-- Tony (Sales Director)
UPDATE user_xp SET email = 'tonymccabe53@gmail.com', role = 'closer'
WHERE user_id = (SELECT id FROM auth.users WHERE email = 'tonymccabe53@gmail.com' LIMIT 1);
-- Michael (Admin/Founder)
UPDATE user_xp SET email = 'admin@rateright.com.au', role = 'admin'
WHERE user_id = (SELECT id FROM auth.users WHERE email = 'admin@rateright.com.au' LIMIT 1);
-- If no user_xp record exists, insert new ones
INSERT INTO user_xp (user_id, display_name, email, role, level, total_xp)
SELECT
id,
CASE email
WHEN 'tonymccabe53@gmail.com' THEN 'Tony'
WHEN 'admin@rateright.com.au' THEN 'Michael'
END,
email,
CASE email
WHEN 'admin@rateright.com.au' THEN 'admin'
ELSE 'closer'
END,
1,
0
FROM auth.users
WHERE email IN ('tonymccabe53@gmail.com', 'admin@rateright.com.au')
AND id NOT IN (SELECT user_id FROM user_xp WHERE user_id IS NOT NULL)
ON CONFLICT DO NOTHING;
```
---
## API Endpoints
### 1. User Management (NEW)
#### GET /api/users
Returns all active users with roles.
```javascript
// Returns:
{
users: [
{ user_id, display_name, email, role, level, is_active }
]
}
```
#### POST /api/users/sync
Syncs auth.users email to user_xp (run after new user signup).
```javascript
// Auto-creates user_xp record if missing
// Called from AuthContext on first login
```
### 2. Call List Filtering (MODIFY GET /api/call-list)
Add query params:
```
?filter=mine|available|all
mine = assigned_to = current user
available = assigned_to IS NULL OR assigned_to = current user
all = no filter (admin only)
```
Backend implementation:
```javascript
// In callList.js
const { filter } = req.query;
const userId = req.user?.id;
let query = supabase.from('leads')
.select('*')
.is('deleted_at', null);
if (filter === 'mine') {
query = query.eq('assigned_to', userId);
} else if (filter === 'available') {
// Unassigned OR assigned to me
query = query.or(`assigned_to.is.null,assigned_to.eq.${userId}`);
}
// 'all' = no filter (default for admins)
```
### 3. Claim/Release Endpoints (NEW)
#### POST /api/leads/:id/claim
```javascript
// Sets assigned_to = req.user.id, assigned_at = NOW()
// Fails if already assigned to someone else (unless admin)
```
#### POST /api/leads/:id/release
```javascript
// Sets assigned_to = NULL, assigned_at = NULL
// Only owner or admin can release
```
### 4. Auto-Assign on Call (MODIFY POST /api/calls/log)
```javascript
// After logging call, if lead is unassigned:
if (!lead.assigned_to) {
await supabase.from('leads')
.update({
assigned_to: req.user.id,
assigned_at: new Date().toISOString()
})
.eq('id', leadId);
}
// Also add user_id to communications insert
await supabase.from('communications').insert({
...existingFields,
user_id: req.user.id
});
```
### 5. Auto-Assign on SMS (MODIFY src/routes/sms.js)
```javascript
// Add user_id to all SMS communications
await supabase.from('communications').insert({
...existingFields,
user_id: req.user?.id || null
});
```
### 6. Who's Working What (NEW GET /api/leads/assignments)
```javascript
// Returns assignment summary for dashboard/dropdown
{
summary: {
unassigned: 45,
assigned: 12
},
byUser: [
{ userId, name, count, lastActivity }
]
}
```
---
## Auto-Release Job
**Purpose:** Release leads with no activity for 24 hours
**Location:** `src/jobs/releaseStaleAssignments.js`
**Logic:**
```javascript
async function releaseStaleAssignments() {
const staleThreshold = new Date();
staleThreshold.setHours(staleThreshold.getHours() - 24);
// Find assigned leads with no recent activity
const { data: staleLeads } = await supabase
.from('leads')
.select('id, assigned_to, assigned_at')
.not('assigned_to', 'is', null)
.lt('assigned_at', staleThreshold.toISOString())
.is('deleted_at', null);
// For each, check if any communications in last 24h
for (const lead of staleLeads) {
const { data: recentComms } = await supabase
.from('communications')
.select('id')
.eq('lead_id', lead.id)
.gte('created_at', staleThreshold.toISOString())
.limit(1);
if (!recentComms?.length) {
// No activity - release the lead
await supabase.from('leads')
.update({ assigned_to: null, assigned_at: null })
.eq('id', lead.id);
}
}
}
```
**Schedule:** Hourly via cron in `src/jobs/index.js`
---
## Frontend Changes
### 1. AuthContext.jsx - Database-Driven Users
```javascript
// Remove hardcoded ALLOWED_EMAILS array
// Add function to check user access from DB
const checkUserAccess = async (email) => {
const { data: userProfile } = await supabase
.from('user_xp')
.select('user_id, display_name, role, is_active')
.eq('email', email.toLowerCase())
.eq('is_active', true)
.single();
return userProfile; // null if not allowed
};
// On auth state change:
const userProfile = await checkUserAccess(session.user.email);
if (userProfile) {
setUser({
...session.user,
displayName: userProfile.display_name,
role: userProfile.role
});
} else {
// Not allowed - sign out
await supabase.auth.signOut();
}
```
**Keep ALLOWED_EMAILS as fallback** for bootstrapping (if user_xp empty).
### 2. CallList.jsx - Filter Toggle
```jsx
// Add to header
{user?.role === 'admin' && (
)}
```
### 3. CallList.jsx - Assignee Badge
```jsx
// On each lead card
{lead.assigned_to && (
{getAssigneeName(lead.assigned_to)}
)}
```
### 4. LeadProfile.jsx - Claim/Release
```jsx
// If unassigned, show claim button
{!lead.assigned_to && (
)}
// If assigned, show who and release option
{lead.assigned_to && (