Support Ticket System Implementation for Website

Support Ticket System Implementation

Ticket system organizes user requests: each request gets number, status, priority, responsible agent. Unlike live-chat — asynchronous communication with message history.

Database Structure

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,  -- internal agent notes
    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: Core API

class TicketController extends Controller
{
    // Create request
    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',
        ]);

        // First message — problem description
        $message = $ticket->messages()->create([
            'user_id' => auth()->id(),
            'body'    => $request->body,
        ]);

        // Upload attachments
        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(),
            ]);
        }

        // Assign agent by category or round-robin
        $agent = $this->assignAgent($ticket);
        if ($agent) {
            $ticket->update(['agent_id' => $agent->id]);
            $agent->notify(new NewTicketAssignedNotification($ticket));
        }

        // Notify user
        auth()->user()->notify(new TicketCreatedNotification($ticket));

        // Notify superadmin about new ticket
        event(new TicketCreatedEvent($ticket));

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

    // Reply to ticket
    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,
        ]);

        // Update ticket status
        if ($isAgent) {
            $ticket->update(['status' => 'pending', 'agent_id' => auth()->id()]);
            // Notify user about reply
            $ticket->user->notify(new TicketReplyNotification($ticket, $message));
        } else {
            $ticket->update(['status' => 'open']);
            // Notify agent
            $ticket->agent?->notify(new TicketUserReplyNotification($ticket));
        }

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

    // Close ticket
    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
    {
        // Assign agent with least load in category
        return User::role('support')
            ->where('is_available', true)
            ->withCount(['tickets' => fn($q) => $q->whereIn('status', ['open', 'pending'])])
            ->orderBy('tickets_count')
            ->first();
    }
}

SLA and Escalation

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()]);

                    // Notify manager
                    User::role('support-manager')->get()
                        ->each(fn($m) => $m->notify(new TicketEscalatedNotification($ticket)));
                }
            });
    }
}

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

React: User Portal

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

  return (
    <div>
      <header>
        <h1>My Requests</h1>
        <a href="/tickets/create" className="btn btn--primary">Create Request</a>
      </header>

      <table>
        <thead>
          <tr>
            <th>Number</th><th>Subject</th><th>Status</th><th>Priority</th><th>Date</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>
  );
}

Email Inbound

// Parse incoming mail via Mailgun Inbound
class InboundEmailController extends Controller
{
    public function receive(Request $request): Response
    {
        $from    = $this->parseEmail($request->sender);
        $subject = $request->subject;
        $body    = $request->stripped_text;  // Mailgun

        // Find existing ticket by 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 {
            // New ticket
            $user = User::firstOrCreate(['email' => $from['email']], ['name' => $from['name']]);
            // ... create ticket
        }

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

Implementation Timeline