A production-ready helpdesk and ticketing system database schema. Features customer and agent management, multi-channel ticket tracking with threaded messages, category-based organization, and SLA policy enforcement with priority-based response and resolution time targets. Ideal for customer support platforms, IT service desks, or internal issue tracking systems.
The SLA is stored as a policy and measured with timestamps. sla_policies defines first_response_minutes and resolution_minutes per priority, and tickets records first_responded_at and resolved_at. A breach is therefore arithmetic against a row that is attached to the ticket — not a number buried in reporting code that nobody can audit.
sla_policy_id is pinned onto the ticket rather than looked up by priority at read time. Policies get renegotiated, and a ticket opened under last quarter's terms has to keep being judged by them. Copying the reference at creation is what makes a historical SLA report reproducible after the policy changes.
Agents and customers are separate tables because they are different populations, and ticket_messages reconciles that with sender_type plus sender_id. One thread then holds the customer's reply and the agent's answer in order, with is_internal_note marking the lines the customer must never see.
first_responded_at and resolved_at are written once, when those things happen. Deriving them from the message history would make every SLA figure depend on message metadata staying correct, and an internal note or an automated reply would quietly count as a first response. Two explicit columns mean the moment is recorded deliberately, and a null is an unambiguous not yet.
Copying first_response_minutes onto every ticket would freeze the targets perfectly and duplicate them millions of times. Pointing at the policy row keeps one definition and still freezes history, because the reference is captured at creation — changing a policy creates a new row rather than editing the one tickets already point at. That discipline is what the design depends on.
is_internal_note on ticket_messages keeps staff discussion in chronological order beside the customer conversation, which is how an agent picking up a handover understands it. The risk is obvious and one-sided: a read path that forgets the filter shows private notes to the customer, so the flag has to be applied at the query, never in the template.
Merging them with a role column is tempting and wrong here. The two populations have different fields, different lifecycles and very different numbers — customers arrive by the thousand and are often created from an inbound email alone. Separating them costs the polymorphic sender on messages, which is the one place the design pays for the split.