MySQL Ticket System Database: Agents, Queues and SLAs

Project goal: a helpdesk where customers open tickets, agents work on them, and managers watch response times. It shows a work queue (several agents taking tickets without taking the same one), an event history, and SLA reports.

Requirements

Customers open tickets with a subject and a priority (low, normal, high, urgent). Each ticket has a due time (its SLA deadline).
Agents take tickets from a queue: highest priority first, oldest first, and two agents must never get the same ticket.
Everything that happens to a ticket (created, assigned, comment, status change) is recorded as an event.
Reports: the open queue, tickets that missed their deadline, average resolution time per agent, and a count of tickets by status and priority.

Tables and relationships

Table	Relationship
`customers` 1 — N `tickets`	A ticket belongs to one customer
`agents` 1 — N `tickets`	An agent works on many tickets (`assigned_agent`, empty until claimed)
`tickets` 1 — N `ticket_events`	The history of a ticket

The schema

query.sql

SQL

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_customers_email (email)
);

CREATE TABLE agents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL
);

CREATE TABLE tickets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    subject VARCHAR(120) NOT NULL,
    priority ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    status ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open',
    assigned_agent INT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    due_at DATETIME NOT NULL,
    resolved_at DATETIME NULL,
    KEY idx_tickets_queue (status, assigned_agent, priority, created_at),
    KEY idx_tickets_agent (assigned_agent, status),
    CONSTRAINT chk_ticket_due CHECK (due_at > created_at),
    CONSTRAINT fk_tickets_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
    CONSTRAINT fk_tickets_agent FOREIGN KEY (assigned_agent) REFERENCES agents (id)
);

CREATE TABLE ticket_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT UNSIGNED NOT NULL,
    agent_id INT UNSIGNED NULL,
    event_type ENUM('created', 'assigned', 'comment', 'status') NOT NULL,
    detail VARCHAR(200) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_events_ticket (ticket_id, id),
    CONSTRAINT fk_events_ticket FOREIGN KEY (ticket_id) REFERENCES tickets (id) ON DELETE CASCADE,
    CONSTRAINT fk_events_agent FOREIGN KEY (agent_id) REFERENCES agents (id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

The ENUM for priority is declared in ascending order (low, normal, high, urgent). An ENUM sorts by its position in the list, not alphabetically, so ORDER BY priority DESC puts urgent first (ENUM).

Sample data

query.sql

SQL

INSERT INTO customers (name, email) VALUES ('Maya', 'maya@example.com'), ('Rohan', 'rohan@example.com'), ('Isha', 'isha@example.com');
INSERT INTO agents (name) VALUES ('Agent Anil'), ('Agent Bina');

INSERT INTO tickets (customer_id, subject, priority, status, assigned_agent, created_at, due_at, resolved_at) VALUES
(1, 'Cannot log in', 'high', 'resolved', 1, '2026-03-02 09:00:00', '2026-03-02 13:00:00', '2026-03-02 11:30:00'),
(2, 'Invoice is wrong', 'normal', 'resolved', 2, '2026-03-02 10:00:00', '2026-03-03 10:00:00', '2026-03-03 15:00:00'),
(3, 'App crashes on start', 'urgent', 'resolved', 1, '2026-03-03 08:00:00', '2026-03-03 10:00:00', '2026-03-03 09:30:00'),
(1, 'Feature request: dark mode', 'low', 'open', NULL, '2026-03-04 12:00:00', '2026-03-11 12:00:00', NULL),
(2, 'Payment failed', 'urgent', 'open', NULL, '2026-03-05 09:30:00', '2026-03-05 11:30:00', NULL),
(3, 'Password reset email missing', 'high', 'open', NULL, '2026-03-05 09:45:00', '2026-03-05 13:45:00', NULL),
(1, 'Question about pricing', 'normal', 'open', NULL, '2026-03-05 10:00:00', '2026-03-06 10:00:00', NULL);

INSERT INTO ticket_events (ticket_id, agent_id, event_type, detail, created_at) VALUES
(1, 1, 'assigned', 'Assigned to Agent Anil', '2026-03-02 09:05:00'),
(1, 1, 'comment', 'Reset the password for the customer', '2026-03-02 11:25:00'),
(1, 1, 'status', 'open -> resolved', '2026-03-02 11:30:00');

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 7 rows affected
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Claiming the next ticket

Two agents must not get the same ticket. The pattern is SELECT ... FOR UPDATE SKIP LOCKED: it locks the row it picks, and skips rows that another transaction has already locked, instead of waiting for them (Locking):

query.sql

SQL

DELIMITER //

CREATE PROCEDURE claim_next_ticket(IN p_agent INT UNSIGNED, IN p_when DATETIME, OUT p_ticket INT UNSIGNED)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    SET p_ticket = NULL;
    START TRANSACTION;

    SELECT id INTO p_ticket
    FROM tickets
    WHERE status = 'open' AND assigned_agent IS NULL
    ORDER BY priority DESC, created_at, id
    LIMIT 1
    FOR UPDATE SKIP LOCKED;

    IF p_ticket IS NOT NULL THEN
        UPDATE tickets SET status = 'in_progress', assigned_agent = p_agent WHERE id = p_ticket;
        INSERT INTO ticket_events (ticket_id, agent_id, event_type, detail, created_at) VALUES (p_ticket, p_agent, 'assigned', CONCAT('Claimed by agent ', p_agent), p_when);
    END IF;

    COMMIT;
END//

DELIMITER ;

Output

OUTPUT

Query OK, 0 rows affected

Both agents claim a ticket:

query.sql

SQL

CALL claim_next_ticket(1, '2026-03-05 10:05:00', @t1);
CALL claim_next_ticket(2, '2026-03-05 10:06:00', @t2);
CALL claim_next_ticket(1, '2026-03-05 10:07:00', @t3);
SELECT @t1 AS first_claim, @t2 AS second_claim, @t3 AS third_claim;

Output

OUTPUT

+-------------+--------------+-------------+
| first_claim | second_claim | third_claim |
+-------------+--------------+-------------+
|           5 |            6 |           7 |
+-------------+--------------+-------------+
1 row in set

The two urgent/high tickets go first: ticket 5 (urgent, “Payment failed”), then ticket 6 (high), then ticket 7 (normal). Each agent gets a different ticket. The “dark mode” request (low priority) is still waiting. In a real run with agents working at the same moment, SKIP LOCKED makes the second agent skip the row the first one has locked, and take the next. That concurrent behaviour was tested with two sessions in Locking.

Query 1: the open queue

query.sql

SQL

SELECT id, subject, priority, created_at, due_at
FROM tickets
WHERE status = 'open' AND assigned_agent IS NULL
ORDER BY priority DESC, created_at;

Output

OUTPUT

+----+----------------------------+----------+---------------------+---------------------+
| id | subject                    | priority | created_at          | due_at              |
+----+----------------------------+----------+---------------------+---------------------+
|  4 | Feature request: dark mode | low      | 2026-03-04 12:00:00 | 2026-03-11 12:00:00 |
+----+----------------------------+----------+---------------------+---------------------+
1 row in set

Query 2: who is working on what

query.sql

SQL

SELECT a.name AS agent, t.id, t.subject, t.priority, t.status
FROM tickets t JOIN agents a ON a.id = t.assigned_agent
WHERE t.status = 'in_progress'
ORDER BY a.name, t.priority DESC;

Output

OUTPUT

+------------+----+------------------------------+----------+-------------+
| agent      | id | subject                      | priority | status      |
+------------+----+------------------------------+----------+-------------+
| Agent Anil |  5 | Payment failed               | urgent   | in_progress |
| Agent Anil |  7 | Question about pricing       | normal   | in_progress |
| Agent Bina |  6 | Password reset email missing | high     | in_progress |
+------------+----+------------------------------+----------+-------------+
3 rows in set

Query 3: SLA breaches

A ticket breached its SLA if it was resolved after the deadline, or if it is still open after the deadline. The “current time” is fixed here, so that the result is repeatable (use NOW() in a real report):

query.sql

SQL

SELECT t.id, t.subject, t.priority, t.status,
       CASE WHEN t.resolved_at IS NULL THEN 'still open' ELSE 'resolved late' END AS breach,
       TIMESTAMPDIFF(MINUTE, t.due_at, COALESCE(t.resolved_at, '2026-03-05 12:00:00')) AS minutes_late
FROM tickets t
WHERE COALESCE(t.resolved_at, '2026-03-05 12:00:00') > t.due_at
ORDER BY minutes_late DESC;

Output

OUTPUT

+----+------------------+----------+-------------+---------------+--------------+
| id | subject          | priority | status      | breach        | minutes_late |
+----+------------------+----------+-------------+---------------+--------------+
|  2 | Invoice is wrong | normal   | resolved    | resolved late |          300 |
|  5 | Payment failed   | urgent   | in_progress | still open    |           30 |
+----+------------------+----------+-------------+---------------+--------------+
2 rows in set

Ticket 2 was resolved five hours late. Ticket 5 is still unresolved and already 30 minutes past its deadline at noon on 5 March. (Ticket 6 is still open, but its deadline is 13:45, so it is not late yet.) COALESCE(resolved_at, now) treats an open ticket as “resolved right now” for the comparison.

Query 4: resolution time per agent

query.sql

SQL

SELECT a.name, COUNT(*) AS resolved, ROUND(AVG(TIMESTAMPDIFF(MINUTE, t.created_at, t.resolved_at)) / 60, 1) AS avg_hours, SUM(t.resolved_at <= t.due_at) AS within_sla
FROM tickets t JOIN agents a ON a.id = t.assigned_agent
WHERE t.resolved_at IS NOT NULL
GROUP BY a.id, a.name
ORDER BY a.name;

Output

OUTPUT

+------------+----------+-----------+------------+
| name       | resolved | avg_hours | within_sla |
+------------+----------+-----------+------------+
| Agent Anil |        2 |       2.0 |          2 |
| Agent Bina |        1 |      29.0 |          0 |
+------------+----------+-----------+------------+
2 rows in set

Query 5: tickets by status and priority in one table

A pivot with conditional aggregation:

query.sql

SQL

SELECT priority,
       SUM(status = 'open') AS open_,
       SUM(status = 'in_progress') AS in_progress,
       SUM(status IN ('resolved', 'closed')) AS done,
       COUNT(*) AS total
FROM tickets
GROUP BY priority
ORDER BY priority DESC;

Output

OUTPUT

+----------+-------+-------------+------+-------+
| priority | open_ | in_progress | done | total |
+----------+-------+-------------+------+-------+
| urgent   |     0 |           1 |    1 |     2 |
| high     |     0 |           1 |    1 |     2 |
| normal   |     0 |           1 |    1 |     2 |
| low      |     1 |           0 |    0 |     1 |
+----------+-------+-------------+------+-------+
4 rows in set

Query 6: the history of one ticket

query.sql

SQL

SELECT e.created_at, COALESCE(a.name, 'system') AS who, e.event_type, e.detail
FROM ticket_events e LEFT JOIN agents a ON a.id = e.agent_id
WHERE e.ticket_id = 1
ORDER BY e.id;

Output

OUTPUT

+---------------------+------------+------------+-------------------------------------+
| created_at          | who        | event_type | detail                              |
+---------------------+------------+------------+-------------------------------------+
| 2026-03-02 09:05:00 | Agent Anil | assigned   | Assigned to Agent Anil              |
| 2026-03-02 11:25:00 | Agent Anil | comment    | Reset the password for the customer |
| 2026-03-02 11:30:00 | Agent Anil | status     | open -> resolved                    |
+---------------------+------------+------------+-------------------------------------+
3 rows in set

Common mistakes

Alphabetical sorting of a status or priority. A VARCHAR priority sorts high, low, normal, urgent. An ENUM in the right order, or a lookup table with a rank column, sorts correctly.
Two agents claiming the same ticket, from a SELECT followed by an UPDATE. Use FOR UPDATE SKIP LOCKED in a transaction (Locking).
Not recording events. “Who changed the status, and when?” is the first question after a complaint.
Computing SLA with NOW() in a report that you then compare with another report from a different moment. Pass the report time as a parameter.
Index missing for the queue. Without (status, assigned_agent, priority, created_at), every claim scans the tickets table.
Deleting tickets. Close them. The history, and the customer’s right to see it, depend on the rows.

Final result

query.sql

SQL

1
2

SELECT status, COUNT(*) AS tickets FROM tickets GROUP BY status ORDER BY status;
SELECT COUNT(*) AS events FROM ticket_events;

Output

OUTPUT

+-------------+---------+
| status      | tickets |
+-------------+---------+
| open        |       1 |
| in_progress |       3 |
| resolved    |       3 |
+-------------+---------+
3 rows in set

+--------+
| events |
+--------+
|      6 |
+--------+
1 row in set

Next steps: a ticket_comments table with visibility (public or internal), tags and categories, automatic escalation (an event that raises the priority when a deadline is near: Events), and a customer-facing view that hides internal notes.