# Admin dashboard — staff workload & richer live feed

**Date:** 2026-08-11  
**Status:** Approved for planning  
**Approach:** Ticket-list enrichments + client-side grouping (no dedicated dashboard API)

## Goal

On the admin (staff) dashboard:

1. Show how many **active staff users** exist.
2. Under that, list **each active staff member** with their **open ticket count** (including zero).
3. Enrich the **live ticket queue** with clear **status**, **who is responsible** (assignee), and **when the ticket was last worked on** (last message time).

Portal (`company_user`) dashboard is unchanged.

## Decisions

| Topic | Choice |
|-------|--------|
| “Active users” | Staff roles `admin` / `user` with `is_active = true` (not portal users) |
| Workload list | All active staff, including those with **0** open tickets |
| Last worked on | Timestamp of the **latest message** on the ticket (inbound or outbound) |
| API shape | Enrich existing ticket list/detail; aggregate on the frontend |

## Data / API

### Ticket joins (`ticketService` list + getById)

Extend current `assigned_user` JSON and add a last-message subquery:

- `assigned_user`: `{ id, email, display_name }` (`display_name` may be null)
- `last_message_at`: `timestamptz | null` — `MAX` of message timestamps for that ticket (use the same timestamp column messages already expose to the API, typically `created_at`)

No schema migration required if messages already have a reliable created timestamp.

### Shared types

Update `Ticket` in `packages/shared`:

- `assigned_user?: Pick<User, 'id' | 'email' | 'display_name'>`
- `last_message_at?: string | null`

### Endpoints reused by dashboard

- `GET /api/v1/users` — filter client-side to active staff
- `GET /api/v1/tickets?status=open&limit=100` — open counts by `assigned_to`
- Existing recent-tickets fetch for the live feed (limit 10), now returning enriched fields

No new `/dashboard/*` route.

## UI

### Staff workload block (staff only)

- Card / section titled clearly (e.g. “Active users”) with the active-staff count.
- Below: list of each active staff member:
  - Label: `display_name` or fallback to `email`
  - Value: open ticket count (0 allowed)
- Sort: open count descending, then name ascending.
- Optional footer line: **Unassigned** open ticket count (open tickets with `assigned_to = null`).

### Live ticket queue

Each row shows (in addition to existing title / client / priority):

- **Status** — distinct badge or label (`OPEN` / `CLOSED`), not only buried in the priority chip
- **Responsible** — assignee `display_name` or `email`, else “Unassigned”
- **Last worked on** — relative time from `last_message_at`; if null, fall back to `updated_at`

Keep existing card/list visual language (`SoKpiCard`, white bordered panels). No portal changes.

## Realtime

- Keep `ticket:created` socket prepend into the live feed.
- Create payload may omit `last_message_at` until a later list refresh; v1 accepts null then fallback.

## Edge cases

- Inactive staff excluded from Active users / workload list; feed still shows stored assignee if present.
- Missing `display_name` → email.
- No messages → use `updated_at` for “Last worked on”.
- Open-count aggregation uses `limit=100` (same as current open KPI). Counts are approximate if more than 100 open tickets exist; document this limitation.

## Out of scope

- Online/presence (“who is logged in right now”)
- Dedicated dashboard summary API
- Changing assignment rules or auto-assign
- Portal dashboard workload views

## Testing

- Manual (admin): active count matches active staff; every active staff appears with correct open counts; unassigned line correct; live feed shows status, assignee, last message relative time.
- Update `docs/TestSprite_PRD_WA_Ticketing.md` for enriched ticket fields and dashboard display (flows / acceptance / API contracts as needed).

## Implementation notes

- Touch: `apps/api/src/services/ticketService.ts`, `packages/shared/src/types.ts`, `apps/frontend/src/pages/Dashboard.tsx`, docs (README / CHANGELOG / NewKnowledgeBase / TestSprite PRD as per project rules).
- Prefer subquery or lateral join for `last_message_at` on list/getById only; avoid N+1 from the frontend.