Реализация системы тикетов поддержки на сайте

Реализация системы тикетов поддержки

Система тикетов организует обращения пользователей: каждое обращение получает номер, статус, приоритет, ответственного агента. В отличие от live-чата — асинхронное общение с историей переписки.

Структура базы данных

CREATE TABLE tickets (
    id          SERIAL PRIMARY KEY,
    number      VARCHAR(20)  NOT NULL UNIQUE,  -- TKT-2024-001234
    user_id     INTEGER REFERENCES users(id),
    agent_id    INTEGER REFERENCES users(id),
    subject     VARCHAR(255) NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'open',  -- open|pending|resolved|closed
    priority    VARCHAR(20)  NOT NULL DEFAULT 'normal', -- low|normal|high|urgent
    category    VARCHAR(100),
    channel     VARCHAR(20)  NOT NULL DEFAULT 'web',   -- web|email|api
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    resolved_at TIMESTAMPTZ,
    closed_at   TIMESTAMPTZ
);

CREATE TABLE ticket_messages (
    id         SERIAL PRIMARY KEY,
    ticket_id  INTEGER  NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
    user_id    INTEGER  REFERENCES users(id),
    body       TEXT     NOT NULL,
    is_private BOOLEAN  NOT NULL DEFAULT false,  -- внутренние заметки агентов
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE ticket_attachments (
    id         SERIAL PRIMARY KEY,
    message_id INTEGER NOT NULL REFERENCES ticket_messages(id) ON DELETE CASCADE,
    filename   VARCHAR(255) NOT NULL,
    s3_key     TEXT         NOT NULL,
    size       INTEGER      NOT NULL
);

CREATE INDEX ON tickets(user_id, status, created_at DESC);
CREATE INDEX ON tickets(agent_id, status, priority);
CREATE INDEX ON ticket_messages(ticket_id, created_at);

Laravel: основной API

class TicketController extends Controller
{
    // Создать обращение
    public function store(StoreTicketRequest $request): JsonResponse
    {
        $ticket = Ticket::create([
            'number'   => $this->generateNumber(),
            'user_id'  => auth()->id(),
            'subject'  => $request->subject,
            'priority' => $request->priority ?? 'normal',
            'category' => $request->category,
            'status'   => 'open',
            'channel'  => 'web',
        ]);

        // Первое сообщение — описание проблемы
        $message = $ticket->messages()->create([
            'user_id' => auth()->id(),
            'body'    => $request->body,
        ]);

        // Загрузить вложения
        foreach ($request->file('attachments', []) as $file) {
            $key = Storage::disk('s3')->putFile("tickets/{$ticket->id}", $file);
            $message->attachments()->create([
                'filename' => $file->getClientOriginalName(),
                's3_key'  => $key,
                'size'    => $file->getSize(),
            ]);
        }

        // Назначить агента по категории или round-robin
        $agent = $this->assignAgent($ticket);
        if ($agent) {
            $ticket->update(['agent_id' => $agent->id]);
            $agent->notify(new NewTicketAssignedNotification($ticket));
        }

        // Уведомить пользователя
        auth()->user()->notify(new TicketCreatedNotification($ticket));

        // Уведомить superadmin о новом тикете
        event(new TicketCreatedEvent($ticket));

        return response()->json(TicketResource::make($ticket->load('messages')), 201);
    }

    // Ответить на тикет
    public function reply(Request $request, Ticket $ticket): JsonResponse
    {
        $this->authorize('reply', $ticket);

        $request->validate(['body' => 'required|string|max:10000']);

        $isAgent = auth()->user()->hasRole('support');

        $message = $ticket->messages()->create([
            'user_id'    => auth()->id(),
            'body'       => $request->body,
            'is_private' => $request->boolean('is_private') && $isAgent,
        ]);

        // Обновить статус тикета
        if ($isAgent) {
            $ticket->update(['status' => 'pending', 'agent_id' => auth()->id()]);
            // Уведомить пользователя о ответе
            $ticket->user->notify(new TicketReplyNotification($ticket, $message));
        } else {
            $ticket->update(['status' => 'open']);
            // Уведомить агента
            $ticket->agent?->notify(new TicketUserReplyNotification($ticket));
        }

        return response()->json(TicketMessageResource::make($message), 201);
    }

    // Закрыть тикет
    public function resolve(Ticket $ticket): JsonResponse
    {
        $this->authorize('resolve', $ticket);

        $ticket->update([
            'status'      => 'resolved',
            'resolved_at' => now(),
            'agent_id'    => auth()->id(),
        ]);

        $ticket->user->notify(new TicketResolvedNotification($ticket));

        return response()->json(['status' => 'resolved']);
    }

    private function generateNumber(): string
    {
        $year  = now()->year;
        $count = Ticket::whereYear('created_at', $year)->count() + 1;
        return sprintf('TKT-%d-%06d', $year, $count);
    }

    private function assignAgent(Ticket $ticket): ?User
    {
        // Назначить агента с наименьшей нагрузкой в категории
        return User::role('support')
            ->where('is_available', true)
            ->withCount(['tickets' => fn($q) => $q->whereIn('status', ['open', 'pending'])])
            ->orderBy('tickets_count')
            ->first();
    }
}

SLA и эскалация

class TicketSlaService
{
    const SLA_HOURS = [
        'urgent' => 2,
        'high'   => 8,
        'normal' => 24,
        'low'    => 72,
    ];

    public function checkEscalations(): void
    {
        Ticket::whereIn('status', ['open', 'pending'])
            ->get()
            ->each(function (Ticket $ticket) {
                $slaHours = self::SLA_HOURS[$ticket->priority];
                $deadline = $ticket->created_at->addHours($slaHours);

                if (now()->gt($deadline) && !$ticket->escalated_at) {
                    $ticket->update(['escalated_at' => now()]);

                    // Уведомить руководителя
                    User::role('support-manager')->get()
                        ->each(fn($m) => $m->notify(new TicketEscalatedNotification($ticket)));
                }
            });
    }
}

// В schedule
$schedule->call(fn() => app(TicketSlaService::class)->checkEscalations())->everyFifteenMinutes();

React: пользовательский портал

function TicketPortal() {
  const { data: tickets } = useQuery({ queryKey: ['tickets'], queryFn: () => api.get('/api/tickets') });

  return (
    <div>
      <header>
        <h1>Мои обращения</h1>
        <a href="/tickets/create" className="btn btn--primary">Создать обращение</a>
      </header>

      <table>
        <thead>
          <tr>
            <th>Номер</th><th>Тема</th><th>Статус</th><th>Приоритет</th><th>Дата</th>
          </tr>
        </thead>
        <tbody>
          {tickets?.data.map(ticket => (
            <tr key={ticket.id}>
              <td><a href={`/tickets/${ticket.id}`}>{ticket.number}</a></td>
              <td>{ticket.subject}</td>
              <td><TicketStatusBadge status={ticket.status} /></td>
              <td><PriorityBadge priority={ticket.priority} /></td>
              <td>{formatDate(ticket.created_at)}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

function TicketStatusBadge({ status }: { status: string }) {
  const colors: Record<string, string> = {
    open:     'bg-blue-100 text-blue-800',
    pending:  'bg-yellow-100 text-yellow-800',
    resolved: 'bg-green-100 text-green-800',
    closed:   'bg-gray-100 text-gray-600',
  };

  const labels: Record<string, string> = {
    open:     'Открыт',
    pending:  'Ожидание',
    resolved: 'Решён',
    closed:   'Закрыт',
  };

  return (
    <span className={`badge ${colors[status]}`}>{labels[status] ?? status}</span>
  );
}

Панель агента поддержки

function AgentDashboard() {
  const { data } = useQuery({
    queryKey: ['agent-tickets'],
    queryFn: () => api.get('/api/agent/tickets?status=open,pending&sort=priority'),
    refetchInterval: 30000,  // обновлять каждые 30 секунд
  });

  return (
    <div className="agent-dashboard">
      <div className="stats">
        <StatCard label="Открытых" value={data?.stats.open} />
        <StatCard label="Просроченных" value={data?.stats.overdue} color="red" />
        <StatCard label="Решено сегодня" value={data?.stats.resolved_today} color="green" />
      </div>

      <div className="ticket-queue">
        {data?.tickets.map(ticket => (
          <TicketCard key={ticket.id} ticket={ticket} />
        ))}
      </div>
    </div>
  );
}

Получение тикетов по email

// Парсинг входящей почты через Mailgun Inbound или similar
class InboundEmailController extends Controller
{
    public function receive(Request $request): Response
    {
        $from    = $this->parseEmail($request->sender);
        $subject = $request->subject;
        $body    = $request->stripped_text;  // Mailgun

        // Найти существующий тикет по subject (Re: TKT-2024-001234)
        preg_match('/TKT-\d{4}-\d{6}/', $subject, $matches);

        if ($matches) {
            $ticket = Ticket::where('number', $matches[0])->first();
            $ticket?->messages()->create(['body' => $body, 'user_id' => $ticket->user_id]);
        } else {
            // Новый тикет
            $user = User::firstOrCreate(['email' => $from['email']], ['name' => $from['name']]);
            // ... создать тикет
        }

        return response('OK', 200);
    }
}

Срок реализации