Help Desk & Ticket System

You are a senior full-stack engineer specialising in customer service systems for Malaysian SMEs. Build a complete Help Desk & Ticket System for {{company_name}} that replaces paid tools like Zendesk or Freshdesk, and eliminates the chaos of managing support via scattered WhatsApp messages.

## Tech Stack

- Framework: Next.js 14 (App Router)
- Styling: Tailwind CSS + shadcn/ui (new-york style, light mode)
- Database: Supabase (PostgreSQL + Auth + Row Level Security)
- Deployment: Vercel
- Fonts: Inter + Noto Sans SC via next/font
- State: React Server Components + Server Actions (no extra state library)

## Database Schema

Create the following tables with full SQL. Always create indexes on every foreign key column — Postgres does NOT auto-index foreign keys.

### customers
Columns: id (uuid pk), name (text not null), email (text unique), phone (text), company (text), notes (text), created_at. Indexes: email, phone.

### agents
Columns: id (uuid pk references auth.users), name, email, role (enum: admin/agent/supervisor), is_active (bool default true), current_load (int default 0), created_at.

### sla_policies
Columns: id (uuid pk), name, priority (enum: urgent/high/medium/low), first_response_hours (int default {{sla_response_hours}}), resolution_hours (int default {{sla_resolution_hours}}), created_at.

### tickets (primary table)
Columns: id (uuid pk), ticket_number (serial unique), subject, description, priority (enum: urgent/high/medium/low, default medium), status (enum: open/pending/on_hold/resolved/closed, default open), channel (enum: email/whatsapp/web_form/phone/walk_in), customer_id (uuid FK→customers), assigned_agent_id (uuid FK→agents), sla_policy_id (uuid FK→sla_policies), first_response_due_at (timestamptz), resolution_due_at (timestamptz), first_responded_at (timestamptz), resolved_at, closed_at, is_sla_breached (bool default false), tags (text[] default '{}'), merged_into_ticket_id (uuid self-ref FK), created_at, updated_at.
Indexes: status, priority, customer_id, assigned_agent_id, resolution_due_at, created_at DESC.

### ticket_messages
Columns: id (uuid pk), ticket_id (uuid FK→tickets ON DELETE CASCADE), sender_type (enum: customer/agent/system), sender_id (uuid nullable), message (text not null), is_internal_note (bool default false), attachments (jsonb default '[]'), created_at.
Indexes: ticket_id, created_at.

### canned_responses
Columns: id (uuid pk), title, shortcut (text unique), body, category, usage_count (int default 0), created_by (uuid FK→agents), created_at.

### knowledge_base_articles
Columns: id (uuid pk), title, slug (text unique), body (text — Markdown), category, is_published (bool default false), view_count (int default 0), helpful_count (int default 0), author_id (uuid FK→agents), created_at, updated_at.
Indexes: slug, is_published.

### ticket_assignments (audit log)
Columns: id (uuid pk), ticket_id (uuid FK), assigned_to (uuid FK→agents), assigned_by (uuid FK→agents), reason (text), created_at.
Index: ticket_id.

## RLS Policies

Set up Row Level Security on all tables. Use `(select auth.uid())` instead of `auth.uid()` directly in policy expressions — this prevents per-row function evaluation and is 100x faster at scale.
- Public (anon) can INSERT into tickets and ticket_messages (for the customer portal).
- Anon can SELECT tickets WHERE customer email matches (token-based portal lookup).
- Agents can SELECT/UPDATE tickets assigned to them or unassigned.
- Admin/supervisor role can SELECT/UPDATE/DELETE all rows.
- All write operations on sla_policies, canned_responses, agents restricted to authenticated admin/supervisor.

## File Structure


src/app/
  portal/               # Customer self-service portal (public)
    page.tsx            # Ticket submission form
    tickets/page.tsx    # Ticket status lookup (email + ticket number)
  agent/
    layout.tsx          # Auth-protected agent shell with sidebar
    inbox/page.tsx      # Split-panel inbox (list + detail)
  admin/
    layout.tsx          # Admin auth wrapper
    analytics/page.tsx  # KPI dashboard
    agents/page.tsx     # Agent management
    sla/page.tsx        # SLA policy config
    canned/page.tsx     # Canned response management
    kb/page.tsx         # Knowledge base CMS
  help/
    page.tsx            # Public KB article list
    [slug]/page.tsx     # Public article detail
src/components/
  ticket-list.tsx       # Left-panel ticket list with filters
  ticket-detail.tsx     # Right-panel conversation + reply box
  sla-badge.tsx         # Real-time SLA countdown badge
  canned-picker.tsx     # / triggered canned response popover
  kb-suggest.tsx        # Article suggestion on ticket creation
src/lib/
  supabase/client.ts
  supabase/server.ts
  supabase/admin.ts
  ticket-actions.ts     # Server Actions
  sla-utils.ts          # SLA calculation helpers


## Feature 1 — Ticket Creation (Multi-Channel)

Build a public form at `/portal` with fields: customer name, email, phone, company name, issue category (select), description (textarea, min 20 chars), file attachments (max 5 files, 10 MB each, stored in Supabase Storage bucket `ticket-attachments`).

On submit (Server Action `createTicket`):
1. Upsert customer by email (create if new, link if existing).
2. Generate ticket_number in format TKT-YYYYMM-XXXXX.
3. Determine SLA policy by priority (default: medium).
4. Set first_response_due_at = now() + sla_policy.first_response_hours.
5. Set resolution_due_at = now() + sla_policy.resolution_hours. Malaysian timezone: Asia/Kuala_Lumpur.
6. Call auto-assignment logic (see Feature 4).
7. Insert system message: "Ticket created via [channel]".
8. Return ticket number + lookup link to customer.

Support channels configured: {{support_channels}}. Add code comments explaining how to wire each channel (email webhook, WhatsApp API callback, phone manual entry).

## Feature 2 — Agent Inbox (Split Panel)

Route `/agent/inbox`. Left panel (380px fixed width, scrollable): ticket list. Right panel (flex-1): ticket detail. On mobile, stack vertically — tap ticket to slide in detail via shadcn Sheet.

Left panel features:
- Tab bar: All / My Tickets / Unassigned / SLA Warning / Breached.
- Search input (debounced 300ms): searches ticket_number, customer name, subject.
- Sort select: Newest Update / SLA Deadline / Priority.
- Each ticket card: priority colour bar on left edge (red/orange/blue/gray), ticket number chip, customer name, subject (2-line clamp), channel icon, SLA countdown badge, last-updated relative time.
- Load 20 tickets per page, infinite scroll via Intersection Observer.

Right panel features:
- Header bar: ticket number, status dropdown (open/pending/on_hold/resolved/closed), assign-to agent dropdown, priority select, "Merge" button, "Close" button.
- Collapsible customer sidebar: name, email, phone, company (RM-denominated contract value if stored), ticket history count.
- Conversation thread: customer messages left-aligned (gray-100 bg), agent messages right-aligned (brand colour bg), internal notes highlighted in amber-50 with "Internal Note" pill. Each message: sender name, timestamp, attachment previews.
- Reply composer at bottom: rich text (bold, italic, lists, links via Tiptap or simple textarea). Slash `/` triggers canned response picker. Button to toggle between public reply and internal note. Knowledge base article insert button. Send triggers Server Action `replyTicket`.

## Feature 3 — SLA Timer & Auto-Escalation

Create `sla-badge.tsx` Client Component that accepts `resolution_due_at` and `is_sla_breached` props.
- Calculate remaining time client-side with useEffect + setInterval (update every 60 seconds).
- Colour rules: green (>50% time remaining), amber (10–50%), red (<10% or breached).
- Display format: "4h 15m" or "BREACHED 2h ago".

Create Supabase Edge Function `sla-scanner` scheduled every 5 minutes:
- Query tickets WHERE resolution_due_at < now() AND status NOT IN ('resolved','closed') AND is_sla_breached = false.
- UPDATE is_sla_breached = true for matches.
- Bump priority one level (low→medium→high→urgent).
- Insert system message into ticket_messages.
- (Stub) Send notification to assigned agent and supervisor via email.

SLA Pause: when status changes to on_hold, record pause timestamp. On resume, extend resolution_due_at by the paused duration.

## Feature 4 — Auto-Assignment

Implement in Server Action, selectable strategy (stored in app settings):

**Round-Robin**: Maintain a global counter in a settings table. Assign to next active agent in id-sorted order, skipping is_active=false agents.

**Load Balancing**: SELECT agent with MIN(current_load) WHERE is_active=true. After assigning, INCREMENT current_load. After resolving, DECREMENT current_load.

Priority routing overrides: urgent tickets auto-assign to supervisor/admin role only. If no eligible agent is available, ticket enters the "Unassigned" queue and a Slack/email alert fires.

Server Action `assignTicket`: updates assigned_agent_id, inserts record to ticket_assignments, inserts system message in ticket_messages.

## Feature 5 — Canned Responses

- In the reply composer, typing `/` opens a Command palette (shadcn Command component) listing canned responses with title, shortcut code, and body preview.
- Fuzzy-search by title or shortcut as user continues typing.
- On select: insert body into editor. Auto-replace variables: `{{customer_name}}`, `{{ticket_number}}`, `{{agent_name}}`, `{{company_name}}`.
- Admin page `/admin/canned` to create/edit/delete canned responses. Group by category. Display usage_count sorted by most used.

## Feature 6 — Knowledge Base

Public page `/help`: grid of published articles grouped by category. Header search bar uses Supabase full-text search (tsvector on title+body with GIN index). For Chinese text, fall back to ILIKE on title for v1.

Article detail `/help/[slug]`: render Markdown body (react-markdown + remark-gfm). "Was this helpful?" Yes/No buttons increment helpful_count via Server Action. Show related articles by category.

During ticket submission: after user types subject/description, call a debounced query to fetch top 3 matching KB articles and display them as "Before you submit — these articles might help" to deflect simple tickets.

Admin KB editor at `/admin/kb`: Markdown editor with live preview (two-column Card), title, slug (auto-generated from title), category, tags, publish toggle.

## Feature 7 — Customer Self-Service Portal

Route `/portal/tickets`: customer enters their email + ticket number (no account needed). Server fetches matching ticket and messages (RLS enforced via service role with email match check). Display: status badge, SLA progress bar (% time elapsed), full conversation thread (excluding internal notes), and a reply textarea. Submitting adds a new ticket_messages row with sender_type='customer'.

## Feature 8 — Ticket Merge & Split

**Merge**: Agent picks a secondary ticket and merges into the current (primary) ticket. All messages from secondary are re-linked to primary (UPDATE ticket_messages SET ticket_id = primary_id). Secondary ticket merged_into_ticket_id = primary_id, status = 'closed'. System message inserted in primary thread: "Ticket #TKT-XXXXX was merged into this ticket."

**Split**: Agent selects specific messages, clicks "Split into new ticket". A new ticket is created with those messages, original ticket retains the rest. Cross-reference system messages inserted in both tickets.

## Business Rules

1. Urgent tickets require an "Impact Scope" field — form validation blocks submission without it.
2. Auto-close: ticket in status 'pending' with no customer reply for {{sla_resolution_hours}} hours → status = 'closed', system sends satisfaction survey stub email.
3. Duplicate detection: if same customer submits within 24 hours with >60% subject similarity (simple token overlap), surface a banner: "You may already have a similar open ticket — would you like to view it instead?"
4. Full audit trail: every status change, assignment, and merge triggers a system message (sender_type='system') in ticket_messages. Never delete these rows.
5. Malaysian context: company names default to include "Sdn Bhd", currency displays as RM, phone numbers formatted as +60-XX-XXXXXXXX, all timestamps stored as UTC but displayed in Asia/Kuala_Lumpur.

## Analytics Dashboard

Route `/admin/analytics`. Fetch all data server-side with Promise.all for parallel queries.

Summary cards (4 in a row):
1. New tickets today vs yesterday (delta arrow).
2. SLA compliance rate this week (% of tickets resolved within SLA).
3. Average first response time (minutes) this week.
4. Average resolution time (hours) this week.

Charts (use recharts or Victory — client components wrapped in Suspense):
- 7-day ticket volume line chart, series per priority.
- Channel distribution doughnut chart.
- Agent resolved tickets bar chart (leaderboard).
- Hourly heatmap (day-of-week × hour) showing ticket volume.

Performance table: agent name, open tickets, resolved this week, SLA hit rate %, avg response time. Sortable columns.

SLA breach list: all currently breached tickets with ticket number, subject, customer, assigned agent, breach duration. Click to open ticket.

## UI/UX Tokens

- Nav background: #0F172A (dark navy)
- Content background: #FFFFFF
- Primary accent (CTA, SLA breach): #FCD34D (yellow-gold)
- Priority urgent: #EF4444 red; high: #F97316 orange; medium: #3B82F6 blue; low: #94A3B8 slate
- Fonts: Inter for Latin/numbers, Noto Sans SC for CJK
- Spacing: generous — min p-6 on cards, gap-4 between elements
- All conditional classes via cn() utility (clsx + tailwind-merge)

## Deliverables Checklist

1. Full SQL migration script with RLS policies and indexes.
2. Next.js App Router file tree with all routes.
3. Server Actions: createTicket, replyTicket, assignTicket, updateTicketStatus, mergeTickets, splitTicket, toggleKBHelpful.
4. Supabase Edge Function: sla-scanner (Deno, scheduled).
5. Agent inbox split-panel component with real-time SLA badge.
6. Canned response Command palette component.
7. Knowledge base public pages + admin editor.
8. Customer self-service portal.
9. Analytics dashboard with all charts and tables.
10. .env.local template with all required variables.

---
Company: {{company_name}} | Channels: {{support_channels}} | SLA First Response: {{sla_response_hours}}h | SLA Resolution: {{sla_resolution_hours}}h