History and Audit Trail

The ticket_history table is a complete, append-only log of everything that ever happened to a ticket. Every state change, owner change, queue transfer, note, email sent, or attribute update writes a row here. It is the primary source for SLA reporting, response-time analysis, and compliance audit trails.

The ticket_history Table

Column	Type	Description
id	BIGINT	Primary key.
name	VARCHAR(200)	Human-readable description of the event (e.g. %%open%%new%%, %%agent@example.com%%). The format varies by history type — see the note below.
history_type_id	SMALLINT	The category of event. Joins to ticket_history_type.id.
ticket_id	BIGINT	The ticket this event belongs to. Joins to ticket.id.
article_id	BIGINT	The article associated with this event, if any (e.g. when an email is sent). NULL otherwise.
type_id	SMALLINT	The ticket type at the time of the event. Joins to ticket_type.id.
queue_id	INTEGER	The queue the ticket was in at the time of the event. Joins to queue.id.
owner_id	INTEGER	The ticket owner at the time of the event. Joins to users.id.
priority_id	SMALLINT	The ticket priority at the time of the event. Joins to ticket_priority.id.
state_id	SMALLINT	The ticket state at the time of the event. Joins to ticket_state.id.
create_time	DATETIME	When the event occurred. This is the timestamp to use for all time-based history queries.
create_by	INTEGER	Agent who triggered the event. Joins to users.id.

Note

The name column stores a structured string whose format depends on the history type. For state changes it looks like %%open%%new%% (old state %% new state). For email events it may contain the recipient address. For reporting purposes, rely on history_type_id/ticket_history_type.name to filter event types, and use create_time for timing — avoid parsing name unless you need its specific content.

ticket_history_type — resolves ticket_history.history_type_id

Name	When it is written
NewTicket	Ticket was created.
StateUpdate	Ticket state changed.
OwnerUpdate	Owner changed.
ResponsibleUpdate	Responsible agent changed.
PriorityUpdate	Priority changed.
Move	Ticket moved to a different queue.
TitleUpdate	Ticket title changed.
TypeUpdate	Ticket type changed.
CustomerUpdate	Customer contact or organization changed.
ServiceUpdate	Associated service changed.
SLAUpdate	Associated SLA changed.
EmailAgent	Agent sent an email.
EmailCustomer	Customer sent an email (inbound).
SendAnswer	Agent sent a reply.
PhoneCallAgent	Agent created a phone-call note (outbound).
PhoneCallCustomer	Agent created a phone-call note (inbound from customer).
AddNote	Note added to the ticket. Whether it is internal or customer-visible is determined by article.is_visible_for_customer.
FollowUp	Customer sent a reply to an existing ticket (inbound email or message creating a new article).
SetPendingTime	The pending-until time was set or changed.
TicketDynamicFieldUpdate	A dynamic field value on the ticket was changed.
Merged	Ticket was merged into another ticket.
Lock	Ticket was locked.
Unlock	Ticket was unlocked.
EscalationSolutionTimeStart	Solution escalation clock started.
EscalationSolutionTimeStop	Solution escalation clock stopped (resolved before deadline).
EscalationResponseTimeStart	First-response escalation clock started.
EscalationResponseTimeStop	First-response escalation clock stopped.
EscalationUpdateTimeStart	Update escalation clock started.
EscalationUpdateTimeStop	Update escalation clock stopped.

Common History Queries

When was a ticket first responded to?

-- First agent response per ticket (first EmailAgent or SendAnswer event)
SELECT
    th.ticket_id,
    MIN(th.create_time) AS first_response_at
FROM   ticket_history      th
JOIN   ticket_history_type tht ON tht.id = th.history_type_id
WHERE  tht.name IN ('EmailAgent', 'SendAnswer', 'PhoneCallAgent')
GROUP BY th.ticket_id;

Average first-response time per queue (in minutes):

SELECT
    q.name                                       AS queue,
    ROUND(AVG(
        TIMESTAMPDIFF(MINUTE, t.create_time, fr.first_response_at)
    ))                                           AS avg_first_response_min
FROM ticket t
JOIN queue q ON q.id = t.queue_id
JOIN (
    SELECT   th.ticket_id, MIN(th.create_time) AS first_response_at
    FROM     ticket_history th
    JOIN     ticket_history_type tht ON tht.id = th.history_type_id
    WHERE    tht.name IN ('EmailAgent', 'SendAnswer', 'PhoneCallAgent')
    GROUP BY th.ticket_id
) fr ON fr.ticket_id = t.id
WHERE t.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND   t.archive_flag = 0
GROUP BY q.id, q.name
ORDER BY avg_first_response_min;

Full state-change timeline for one ticket:

SELECT
    th.create_time,
    tht.name                                     AS event_type,
    th.name                                      AS detail,
    CONCAT(u.first_name, ' ', u.last_name)       AS agent
FROM   ticket_history      th
JOIN   ticket_history_type tht ON tht.id = th.history_type_id
JOIN   users               u   ON  u.id  = th.create_by
WHERE  th.ticket_id = :ticket_id
ORDER BY th.create_time;

Tickets that escalated in the last 7 days (by history event):

SELECT DISTINCT
    t.tn,
    t.title,
    th.create_time AS escalated_at,
    q.name         AS queue
FROM   ticket_history      th
JOIN   ticket_history_type tht ON tht.id = th.history_type_id
JOIN   ticket              t   ON  t.id  = th.ticket_id
JOIN   queue               q   ON  q.id  = t.queue_id
WHERE  tht.name IN (
           'EscalationSolutionTimeStart',
           'EscalationResponseTimeStart',
           'EscalationUpdateTimeStart'
       )
AND    th.create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);