drop-customer-support-spec

Drop Customer Support System — Implementation Spec

Project: Drop Fintech App MC Task: #1187 Created: 2026-02-17 Architecture: Software Architect Agent

1. Overview

This spec defines a built-in customer support ticket system for Drop. No external dependencies (no Zendesk, no Intercom). MVP scope.

Scope:

Non-goals (future):

2. Database Schema

2.1 support_tickets Table

CREATE TABLE IF NOT EXISTS support_tickets (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id),
  subject TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL CHECK(category IN ('transaction_issue','account_access','verification','general','dispute')),
  status TEXT DEFAULT 'open' CHECK(status IN ('open','in_progress','waiting_user','resolved','closed')),
  priority TEXT DEFAULT 'normal' CHECK(priority IN ('low','normal','high','urgent')),
  transaction_id TEXT REFERENCES transactions(id), -- optional link to transaction
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  resolved_at TEXT,
  closed_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_support_tickets_user ON support_tickets(user_id);
CREATE INDEX IF NOT EXISTS idx_support_tickets_status ON support_tickets(status);
CREATE INDEX IF NOT EXISTS idx_support_tickets_category ON support_tickets(category);
CREATE INDEX IF NOT EXISTS idx_support_tickets_created ON support_tickets(created_at);
CREATE INDEX IF NOT EXISTS idx_support_tickets_transaction ON support_tickets(transaction_id);

PostgreSQL version:

2.2 ticket_messages Table

CREATE TABLE IF NOT EXISTS ticket_messages (
  id TEXT PRIMARY KEY,
  ticket_id TEXT NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE,
  sender_type TEXT NOT NULL CHECK(sender_type IN ('user','admin')),
  sender_id TEXT, -- user_id or admin user_id (optional, for audit)
  message TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_ticket_messages_ticket ON ticket_messages(ticket_id);
CREATE INDEX IF NOT EXISTS idx_ticket_messages_created ON ticket_messages(created_at);

PostgreSQL version:

3. Status Flow

open → in_progress → waiting_user → resolved → closed
  ↓         ↓             ↓             ↓
  └─────────┴─────────────┴─────────────┘
          (can reopen if needed)

Status definitions:

Priority:

4. API Endpoints

4.1 User Endpoints

POST /api/support/tickets

Create new support ticket.

Request:

{
  "subject": "Transaction failed but money was deducted",
  "description": "I tried to send 500 NOK to Serbia but...",
  "category": "transaction_issue",
  "transaction_id": "tx_rem_123" // optional
}

Response (201):

{
  "data": {
    "id": "tkt_abc123",
    "subject": "...",
    "description": "...",
    "category": "transaction_issue",
    "status": "open",
    "priority": "normal",
    "created_at": "2026-02-17T10:30:00Z"
  }
}

Validation:

Auto-priority logic:

Side effects:

GET /api/support/tickets

List user's tickets.

Query params:

Response:

{
  "data": [
    {
      "id": "tkt_abc123",
      "subject": "Transaction failed...",
      "category": "transaction_issue",
      "status": "in_progress",
      "priority": "normal",
      "created_at": "2026-02-17T10:30:00Z",
      "updated_at": "2026-02-17T11:00:00Z",
      "unread_messages": 2 // count of admin messages since last user visit
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 10,
    "total": 5,
    "totalPages": 1
  }
}

GET /api/support/tickets/[id]

Get ticket detail + conversation thread.

Response:

{
  "data": {
    "ticket": {
      "id": "tkt_abc123",
      "subject": "Transaction failed...",
      "description": "I tried to send...",
      "category": "transaction_issue",
      "status": "in_progress",
      "priority": "normal",
      "transaction_id": "tx_rem_123",
      "created_at": "2026-02-17T10:30:00Z",
      "updated_at": "2026-02-17T11:00:00Z"
    },
    "messages": [
      {
        "id": "msg_1",
        "sender_type": "user",
        "message": "I tried to send...",
        "created_at": "2026-02-17T10:30:00Z"
      },
      {
        "id": "msg_2",
        "sender_type": "admin",
        "message": "Thank you for reporting. We are investigating...",
        "created_at": "2026-02-17T11:00:00Z"
      }
    ],
    "transaction": { /* if transaction_id is set */ }
  }
}

Authorization:

POST /api/support/tickets/[id]/messages

Add user message to conversation.

Request:

{
  "message": "I tried again and it worked this time"
}

Response (201):

{
  "data": {
    "id": "msg_3",
    "ticket_id": "tkt_abc123",
    "sender_type": "user",
    "message": "I tried again...",
    "created_at": "2026-02-17T12:00:00Z"
  }
}

Side effects:

4.2 Admin Endpoints

All admin endpoints require requireAdmin() middleware (TBD: define admin role or separate admin auth).

GET /api/admin/support/tickets

List ALL tickets with filters.

Query params:

Response:

{
  "data": [
    {
      "id": "tkt_abc123",
      "user_id": "usr_demo1",
      "user_email": "amir@example.com",
      "subject": "Transaction failed...",
      "category": "transaction_issue",
      "status": "open",
      "priority": "normal",
      "created_at": "2026-02-17T10:30:00Z",
      "updated_at": "2026-02-17T11:00:00Z"
    }
  ],
  "pagination": { ... }
}

PATCH /api/admin/support/tickets/[id]

Update ticket status, priority, or admin notes.

Request:

{
  "status": "in_progress",
  "priority": "high"
}

Response:

{
  "data": { /* updated ticket */ }
}

Side effects:

POST /api/admin/support/tickets/[id]/messages

Admin reply to ticket.

Request:

{
  "message": "Thank you for reporting. We have issued a refund.",
  "change_status": "resolved" // optional
}

Response (201):

{
  "data": {
    "id": "msg_4",
    "ticket_id": "tkt_abc123",
    "sender_type": "admin",
    "message": "Thank you for reporting...",
    "created_at": "2026-02-17T13:00:00Z"
  }
}

Side effects:

5. Email Notifications

When admin replies, send email to user:

Subject: "Svar på din support-henvendelse [#{ticket_id}]"

Body (plain text):

Hei,

Vi har svart på din support-henvendelse:

Emne: {ticket.subject}
Svar: {admin_message}

Logg inn på Drop for å se hele samtalen:
{NEXT_PUBLIC_APP_URL}/support/tickets/{ticket_id}

Hvis du har flere spørsmål, kan du svare direkte i billetten.

Vennlig hilsen,
Drop Support

Implementation:

6. UI Pages

6.1 /support — Help Center (User)

Layout:

Design:

6.2 /support/tickets — Ticket List (User)

Layout:

Empty state:

6.3 /support/tickets/[id] — Conversation (User)

Layout:

6.4 /support/new — Create Ticket (User)

Form fields:

Validation:

Success:

6.5 /admin/support — Admin Dashboard

Layout:

Click ticket:

6.6 /admin/support/[id] — Admin Ticket Detail

Layout:

Audit trail (bottom):

7. Integration Points

7.1 Audit Logging

All support actions logged to audit_log table:

Action Resource Type Details
support.ticket_created support_ticket ticket_id, category, priority
support.message_added ticket_message ticket_id, sender_type
support.admin_reply ticket_message ticket_id, admin message preview
support.ticket_updated support_ticket ticket_id, old_status, new_status

Implementation: Use auditLog() helper from @/lib/middleware.

When user creates ticket with transaction_id:

7.3 Email Notifications (Future)

Placeholder implementation:

8. File List

8.1 Database Migration

File: src/lib/db.ts Changes:

8.2 API Routes

User Endpoints

  1. src/app/api/support/tickets/route.ts — GET (list), POST (create)
  2. src/app/api/support/tickets/[id]/route.ts — GET (detail)
  3. src/app/api/support/tickets/[id]/messages/route.ts — POST (add message)

Admin Endpoints

  1. src/app/api/admin/support/tickets/route.ts — GET (list all)
  2. src/app/api/admin/support/tickets/[id]/route.ts — PATCH (update status/priority)
  3. src/app/api/admin/support/tickets/[id]/messages/route.ts — POST (admin reply)

8.3 UI Pages

User Pages

  1. src/app/support/page.tsx — Help center (FAQ + create button)
  2. src/app/support/tickets/page.tsx — Ticket list
  3. src/app/support/tickets/[id]/page.tsx — Conversation view
  4. src/app/support/new/page.tsx — Create ticket form

Admin Pages

  1. src/app/admin/support/page.tsx — Admin dashboard (ticket table)
  2. src/app/admin/support/[id]/page.tsx — Admin ticket detail

8.4 Components

  1. src/components/support/ticket-card.tsx — Ticket list item (subject, status, date)
  2. src/components/support/message-bubble.tsx — Chat message (user/admin differentiation)
  3. src/components/support/status-badge.tsx — Status indicator (open, in_progress, resolved, closed)
  4. src/components/support/category-badge.tsx — Category indicator
  5. src/components/support/priority-badge.tsx — Priority indicator (urgent, high, normal, low)
  6. src/components/support/faq-accordion.tsx — FAQ section (static hardcoded questions)

8.5 Types

File: src/types/support.ts

export interface SupportTicket {
  id: string;
  user_id: string;
  subject: string;
  description: string;
  category: TicketCategory;
  status: TicketStatus;
  priority: TicketPriority;
  transaction_id: string | null;
  created_at: string;
  updated_at: string;
  resolved_at: string | null;
  closed_at: string | null;
}

export interface TicketMessage {
  id: string;
  ticket_id: string;
  sender_type: 'user' | 'admin';
  sender_id: string | null;
  message: string;
  created_at: string;
}

export type TicketCategory = 'transaction_issue' | 'account_access' | 'verification' | 'general' | 'dispute';
export type TicketStatus = 'open' | 'in_progress' | 'waiting_user' | 'resolved' | 'closed';
export type TicketPriority = 'low' | 'normal' | 'high' | 'urgent';

8.6 Utilities

File: src/lib/support-utils.ts

import { TicketCategory, TicketPriority } from '@/types/support';

export function getAutoPriority(category: TicketCategory): TicketPriority {
  if (category === 'dispute') return 'high';
  if (category === 'account_access') return 'high';
  if (category === 'transaction_issue') return 'normal';
  if (category === 'verification') return 'normal';
  return 'low'; // general
}

export function getCategoryLabel(category: TicketCategory): string {
  const labels: Record<TicketCategory, string> = {
    transaction_issue: 'Transaksjonsproblem',
    account_access: 'Tilgangsproblem',
    verification: 'Verifisering',
    general: 'Generelt',
    dispute: 'Tvist',
  };
  return labels[category];
}

export function getStatusLabel(status: TicketStatus): string {
  const labels = {
    open: 'Åpen',
    in_progress: 'Under behandling',
    waiting_user: 'Venter på deg',
    resolved: 'Løst',
    closed: 'Lukket',
  };
  return labels[status];
}

export function getPriorityLabel(priority: TicketPriority): string {
  const labels = {
    urgent: 'Hastesak',
    high: 'Høy',
    normal: 'Normal',
    low: 'Lav',
  };
  return labels[priority];
}

9. Acceptance Criteria

9.1 Database

9.2 API Endpoints

9.3 Authorization

9.4 UI Pages

9.5 Integration

9.6 Validation

9.7 Edge Cases

10. Dependencies

10.1 Existing Infrastructure

10.2 New Dependencies

None. All features use existing infrastructure.

10.3 Admin Role (TBD)

Current state: Users table has role field with values user or merchant.

Requirement: Add admin role for support staff.

Two options:

Option A: Extend existing role enum

ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check CHECK(role IN ('user','merchant','admin'));

Option B: Separate admin table

CREATE TABLE IF NOT EXISTS admins (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);

Recommendation: Option A (extend role enum) for MVP. Simpler, uses existing auth.

Implementation:

  1. Add migration to extend role enum
  2. Create requireAdmin() middleware: 
    export async function requireAdmin(request?: NextRequest) {
  const { user, error } = await requireAuth(request);
  if (error) return { user: null, error };
  if ((user as Record<string, unknown>).role !== 'admin') {
    return { user: null, error: jsonError('forbidden', 'Admin role required', 403) };
  }
  return { user, error: null };
}
  3. Use in all admin endpoints

11. Testing Checklist

11.1 Unit Tests (Future)

11.2 Integration Tests (Future)

11.3 Manual Testing (MVP)

  1. User flow:

    • Create ticket with subject, description, category
    • View ticket list (should show new ticket)
    • Open ticket detail (should show initial message)
    • Add reply message
    • Verify status changes to 'open' if was 'waiting_user'

  2. Admin flow:

    • View all tickets dashboard
    • Filter by status, priority, category
    • Open ticket detail
    • Change status to 'in_progress'
    • Add admin reply
    • Verify user sees reply in conversation

  3. Authorization:

    • User A cannot view User B's tickets (404)
    • Non-admin cannot access /admin/support (403)

  4. Transaction linking:

    • Create ticket linked to transaction
    • Verify transaction summary shows in ticket detail

12. Implementation Order

Phase 1: Database + Types (Day 1)

  1. Add schemas to src/lib/db.ts
  2. Create src/types/support.ts
  3. Create src/lib/support-utils.ts
  4. Add requireAdmin() to src/lib/middleware.ts

Phase 2: User API (Day 1-2)

  1. POST /api/support/tickets (create)
  2. GET /api/support/tickets (list)
  3. GET /api/support/tickets/[id] (detail)
  4. POST /api/support/tickets/[id]/messages (reply)

Phase 3: User UI (Day 2-3)

  1. /support (help center)
  2. /support/new (create form)
  3. /support/tickets (list)
  4. /support/tickets/[id] (conversation)
  5. Components: ticket-card, message-bubble, status-badge, category-badge, priority-badge, faq-accordion

Phase 4: Admin API (Day 3)

  1. GET /api/admin/support/tickets (list all)
  2. PATCH /api/admin/support/tickets/[id] (update)
  3. POST /api/admin/support/tickets/[id]/messages (admin reply)

Phase 5: Admin UI (Day 4)

  1. /admin/support (dashboard)
  2. /admin/support/[id] (detail)

Phase 6: Integration (Day 4-5)

  1. Audit logging for all actions
  2. Transaction linking
  3. Email notification (placeholder)

Phase 7: Testing + Refinement (Day 5)

  1. Manual testing of all flows
  2. Edge case validation
  3. UI polish (spacing, colors, responsive)

13. Future Enhancements (Out of Scope)

  1. File attachments — allow users to upload screenshots
  2. Multi-language support — English, Bosnian translations
  3. Public FAQ system — CMS-backed knowledge base
  4. Live chat — real-time messaging via WebSocket
  5. AI chatbot — auto-respond to common questions
  6. SLA tracking — response time targets per priority
  7. Auto-close old tickets — after 7 days in 'resolved' status
  8. Admin assignment — assign tickets to specific support staff
  9. Internal notes — admin-only notes not visible to user
  10. Ticket templates — pre-filled forms for common issues

14. Notes for Implementation

14.1 Design Consistency

14.2 Security

14.3 Performance

14.4 Norwegian Text

All UI text in Norwegian (nb):

15. Spec Version History

Version Date Changes
1.0 2026-02-17 Initial spec — database, API, UI, integration

END OF SPEC

Revision #5
Created 2026-02-18 08:44:43 UTC by John
Updated 2026-07-12 20:00:29 UTC by John