Support Ticketing

Design the schema for a Zendesk-like support system: customers file tickets, tickets flow through queues, agents work them, and SLAs are tracked per priority. Reports show breached SLAs and agent throughput.

Clarifying questions

Are queues role-based (Billing team, Tier 2) or skill-based (any agent who can handle Spanish + iOS)?
Do SLAs pause during 'awaiting customer' status, or run continuously?
Can a ticket move between queues mid-life, and do we keep the history of which queue it sat in?
Are private notes (internal to agents) and customer-visible replies the same entity?
Do we need to merge two tickets into one without losing message history?

Core requirements

File a ticket with priority, severity, and queue assignment.
Track every message in a thread (customer + agent + system).
Compute first-response and resolution SLA (Service Level Agreement) per ticket, with breach status.
Move a ticket between queues without rewriting history.
Report on agent throughput and breached SLAs over time windows.
Support distinct internal notes vs public replies in the same thread.

Canonical schema (PostgreSQL)

CREATE TABLE queues (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  sla_first_response_minutes INTEGER NOT NULL,
  sla_resolution_minutes INTEGER NOT NULL
);

CREATE TABLE agents (
  user_id UUID PRIMARY KEY,
  display_name TEXT NOT NULL,
  email CITEXT UNIQUE NOT NULL,
  skills TEXT[] NOT NULL DEFAULT '{}'::text[],
  active BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE tickets (
  id BIGSERIAL PRIMARY KEY,
  number TEXT UNIQUE NOT NULL,
  requester_id UUID NOT NULL,
  queue_id UUID NOT NULL REFERENCES queues(id),
  assignee_id UUID REFERENCES agents(user_id),
  subject TEXT NOT NULL,
  priority TEXT NOT NULL CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
  severity TEXT NOT NULL CHECK (severity IN ('s1', 's2', 's3', 's4')),
  status TEXT NOT NULL CHECK (status IN ('new', 'open', 'pending', 'on_hold', 'solved', 'closed')),
  first_responded_at TIMESTAMPTZ,
  resolved_at TIMESTAMPTZ,
  sla_first_response_due_at TIMESTAMPTZ NOT NULL,
  sla_resolution_due_at TIMESTAMPTZ NOT NULL,
  sla_paused_at TIMESTAMPTZ,
  sla_pause_seconds_total INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ticket_messages (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
  author_id UUID NOT NULL,
  author_kind TEXT NOT NULL CHECK (author_kind IN ('customer', 'agent', 'system')),
  visibility TEXT NOT NULL CHECK (visibility IN ('public', 'internal')),
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ticket_assignments (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
  queue_id UUID NOT NULL REFERENCES queues(id),
  agent_id UUID REFERENCES agents(user_id),
  assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  released_at TIMESTAMPTZ
);

CREATE TABLE sla_events (
  id BIGSERIAL PRIMARY KEY,
  ticket_id BIGINT NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL CHECK (event_type IN ('paused', 'resumed', 'breached_first_response', 'breached_resolution')),
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Key indexes

CREATE INDEX idx_tickets_queue_status ON tickets(queue_id, status, priority) WHERE status NOT IN ('solved', 'closed')

Agent queue view filters open tickets by queue and sorts by priority — partial index ignores resolved noise.

CREATE INDEX idx_tickets_assignee_open ON tickets(assignee_id, updated_at DESC) WHERE status NOT IN ('solved', 'closed')

'My open tickets' is every agent's home page — partial composite serves it without touching resolved rows.

CREATE INDEX idx_tickets_sla_response_due ON tickets(sla_first_response_due_at) WHERE first_responded_at IS NULL AND status != 'closed'

Breach-warning cron scans only tickets still owing a first response.

CREATE INDEX idx_messages_ticket_created ON ticket_messages(ticket_id, created_at)

Thread view loads chronologically — ascending order matches the display.

CREATE INDEX idx_assignments_agent_active ON ticket_assignments(agent_id, assigned_at DESC) WHERE released_at IS NULL

Throughput reporting against currently-held tickets per agent.

Key decisions to defend

SLA pause is tracked as cumulative pause_seconds plus a sla_paused_at marker on the ticket — breach check is current_time - sla_due_at - pause_seconds, no event replay needed for the hot read.
ticket_assignments is a separate event-style table, so queue and assignee changes are first-class history; the tickets row carries 'current' values for fast reads.
Internal notes and public replies share ticket_messages with a visibility flag — different tables would force every read to UNION and add a class of 'leaked an internal note' bugs.
Status is a flat enum, not a nested workflow table — Zendesk-style state machines do not need a Petri net, just CHECK constraints.
sla_events is an immutable audit log for breach forensics; the tickets row holds current breach state for cheap dashboard queries.

Likely follow-ups

· Add round-robin assignment that respects agent capacity — where does the cursor live?
· Merge two tickets — what happens to thread, SLA, and assignment history?
· Skill-based routing: a ticket needs 'Spanish + iOS' — how does the queue model adapt?
· Show 'tickets breaching SLA in the next 15 min' for the queue manager — index strategy?

Dive deeper

Ticketing tests whether you separate current state (fast read on the tickets row) from historical state (assignment, SLA, message tables). The SLA pause modeling is the classic trap — store cumulative pause seconds, not a stream of pause/resume events you replay on every dashboard render. The visibility flag on messages is a small detail that tells the interviewer you've actually used the product and thought about the failure modes.

All database design prompts