# Investigation: Dashboard and Team Stats Show 0 Calls

**Date:** 2026-01-29
**Reporter:** Main agent (via user)
**User Affected:** Vena (user_id: `dc61845c-d191-4213-9616-3d4385056c1b`)
**Symptoms:** 
- Dashboard shows 0 calls
- Team performance shows 0 calls
- Vena has 266 XP (so activity IS being tracked)
- Vena made 30 calls through the app (10 answered)

---

## Root Cause Analysis

### Bug #1: Team Performance Uses Wrong Column Name (CRITICAL)

**Location:** `src/routes/team.js`

The team performance endpoint queries communications by `created_by`, but this column **does not exist** on the `communications` table.

**Evidence:**

```js
// team.js:69-72
const { data: todayComms } = await db
  .from('communications')
  .select('type, created_by, metadata')  // ❌ created_by doesn't exist!
  .gte('created_at', todayStart.toISOString())
  .in('created_by', userIds);
```

**Actual columns on `communications` table:**
- `handled_by` - Added by migration `20260120112449_add_handled_by_column.sql`
- `user_id` - Added by migration `20260120110914_lead_assignment.sql`
- NO `created_by` column exists!

**How calls.js logs calls:**
```js
// calls.js:80-84
const { data: comm, error: commError } = await supabase
  .from('communications')
  .insert({
    // ...
    handled_by: callHandledBy,  // ✓ This exists
    user_id: callHandledBy,     // ✓ This exists
    // No created_by field!
  })
```

**Result:** The Supabase query silently returns 0 rows because `created_by` is null/undefined for all rows.

---

### Bug #2: Dashboard Stats May Have Timezone Issues (SECONDARY)

**Location:** `src/routes/dashboard.js`

The dashboard queries ALL communications for "today" using AEST timezone:

```js
// dashboard.js:89-92
supabase
  .from('communications')
  .select('type, metadata')
  .gte('created_at', todayStart.toISOString()),
```

This query has **no user filter**, so it should return all calls for today. If it shows 0, possible causes:

1. **Timezone edge case:** The `getTodayStartAEST()` function may have edge cases around midnight
2. **Calls exist but from yesterday:** If Vena's calls were yesterday AEST, they won't appear in "today" stats
3. **Calls are being logged but not with correct type:** Check if `type` is `call_outbound` vs just `call`

**Key observation:** The dashboard uses `getTodayStartAEST()` correctly, but:
- The query counts calls with type `'call'` OR `'call_outbound'`
- calls.js inserts with type `'call_outbound'`
- voice.js inserts inbound calls with type `'call_inbound'`

This should work correctly unless the timezone calculation is off.

---

### Why XP Works But Stats Don't

**XP Award Flow (works ✓):**
```
calls.js → awardXPForCall(userId, outcome, leadId)
         → user_xp table (direct update, no query by column)
```

XP is awarded by inserting into `user_xp` using the `userId` directly. No column name confusion.

**Team Stats Flow (broken ✗):**
```
team.js → query communications WHERE created_by IN (userIds)
        → created_by column doesn't exist
        → Returns 0 rows
```

---

## Database Schema Evidence

**communications table columns:**
| Column | Added By | Used In calls.js |
|--------|----------|------------------|
| `handled_by` | Migration 20260120112449 | ✓ Yes |
| `user_id` | Migration 20260120110914 | ✓ Yes |
| `created_by` | Never added | ✗ Doesn't exist |

**user_xp table columns:**
| Column | Purpose |
|--------|---------|
| `user_id` | Primary identifier |
| `total_xp` | XP balance |
| `display_name` | User name |

---

## Files Affected

### Primary Bug (team stats)
- `src/routes/team.js` - Uses wrong column name `created_by`

### Secondary Queries Using `created_by` (found via grep)
- `src/routes/notion.js` - Lines 118, 123, 188, 255 - Same bug pattern (4 occurrences)

### Files That Use Correct Column
- `src/routes/manager.js` - Uses `handled_by` correctly ✓

### Files That Write Correctly
- `src/routes/calls.js` - Writes `handled_by` and `user_id` ✓
- `src/routes/voice.js` - Writes inbound calls (no user tracking)
- `src/routes/sms.js` - Need to verify

---

## Impact Assessment

| Feature | Impact | Severity |
|---------|--------|----------|
| Team performance dashboard | 0 calls for all users | CRITICAL |
| Manager dashboard | Likely 0 calls shown | HIGH |
| Individual user stats | May be affected | HIGH |
| Dashboard total calls | May work if timezone OK | MEDIUM |
| XP system | Working correctly | NONE |

---

## Verification Needed

Before fixing, verify in database:

```sql
-- Check if communications exist for Vena
SELECT id, type, handled_by, user_id, created_at 
FROM communications 
WHERE handled_by = 'dc61845c-d191-4213-9616-3d4385056c1b'::uuid
   OR user_id = 'dc61845c-d191-4213-9616-3d4385056c1b'::uuid
ORDER BY created_at DESC
LIMIT 20;

-- Check if Vena is in user_xp table
SELECT * FROM user_xp 
WHERE user_id = 'dc61845c-d191-4213-9616-3d4385056c1b'::uuid;

-- Check column existence
SELECT column_name FROM information_schema.columns 
WHERE table_name = 'communications'
ORDER BY ordinal_position;

-- Count all calls today
SELECT COUNT(*), type 
FROM communications 
WHERE created_at >= (NOW() AT TIME ZONE 'Australia/Sydney')::date 
  AND type IN ('call', 'call_outbound', 'call_inbound')
GROUP BY type;
```

---

## Summary

**Primary Bug:** `team.js` queries `created_by` column which doesn't exist. Should use `handled_by` or `user_id`.

**Secondary Issue:** Need to verify timezone calculations in `dashboard.js` aren't causing edge cases.

**Why XP works:** XP is awarded directly to `user_xp` table without needing to query by column name.