Support Helpdesk Schema — Lab

Diagram Views

erDiagram users ||--o{ tickets : opens users ||--o{ tickets : assigned_to sla_policies ||--o{ tickets : governs tickets ||--o{ ticket_comments : contains users ||--o{ ticket_comments : writes tickets ||--o{ ticket_status_history : tracks users ||--o{ ticket_status_history : changes users { bigint id PK text email text role } sla_policies { bigint id PK text name int first_response_minutes int resolution_minutes } tickets { bigint id PK bigint customer_id FK bigint assignee_id FK bigint sla_policy_id FK text priority text status } ticket_comments { bigint id PK bigint ticket_id FK bigint author_id FK bool is_internal } ticket_status_history { bigint id PK bigint ticket_id FK bigint changed_by FK text old_status text new_status }

SQL Snippets

schema.sql

create table users (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  role text not null check (role in ('customer','agent','admin')),
  created_at timestamptz not null default now()
);

create table sla_policies (
  id bigint primary key generated always as identity,
  name text not null unique,
  first_response_minutes integer not null check (first_response_minutes > 0),
  resolution_minutes integer not null check (resolution_minutes > 0)
);

create table tickets (
  id bigint primary key generated always as identity,
  customer_id bigint not null references users(id),
  assignee_id bigint references users(id),
  sla_policy_id bigint references sla_policies(id),
  subject text not null,
  priority text not null check (priority in ('low','med','high','urgent')),
  status text not null check (status in ('new','open','pending','resolved','closed')),
  first_response_due_at timestamptz,
  resolution_due_at timestamptz,
  created_at timestamptz not null default now(),
  resolved_at timestamptz
);

create table ticket_comments (
  id bigint primary key generated always as identity,
  ticket_id bigint not null references tickets(id) on delete cascade,
  author_id bigint not null references users(id),
  body text not null,
  is_internal boolean not null default false,
  created_at timestamptz not null default now()
);

create table ticket_status_history (
  id bigint primary key generated always as identity,
  ticket_id bigint not null references tickets(id) on delete cascade,
  changed_by bigint not null references users(id),
  old_status text,
  new_status text not null,
  changed_at timestamptz not null default now()
);

create index idx_tickets_assignee_status on tickets(assignee_id, status);
create index idx_tickets_priority_due on tickets(priority, resolution_due_at);
create index idx_ticket_comments_ticket_created on ticket_comments(ticket_id, created_at);

seed.sql

insert into users (email, full_name, role)
values
  ('customer1@help.dev', 'Customer One', 'customer'),
  ('agent1@help.dev', 'Agent One', 'agent'),
  ('agent2@help.dev', 'Agent Two', 'agent');

insert into sla_policies (name, first_response_minutes, resolution_minutes)
values
  ('Standard', 240, 2880),
  ('Priority', 60, 720);

insert into tickets (
  customer_id,
  assignee_id,
  sla_policy_id,
  subject,
  priority,
  status,
  first_response_due_at,
  resolution_due_at
)
values
  (1, 2, 2, 'Cannot complete checkout', 'urgent', 'open', now() + interval '1 hour', now() + interval '12 hours'),
  (1, 3, 1, 'Invoice PDF is blank', 'med', 'pending', now() + interval '4 hours', now() + interval '2 days');

insert into ticket_comments (ticket_id, author_id, body, is_internal)
values
  (1, 1, 'Checkout fails after card submission.', false),
  (1, 2, 'Investigating payment gateway logs.', true),
  (2, 1, 'Attached screenshot with issue.', false);

insert into ticket_status_history (ticket_id, changed_by, old_status, new_status)
values
  (1, 2, 'new', 'open'),
  (2, 3, 'new', 'pending');

queries.sql

-- 1) Open tickets breaching first response SLA
select
  t.id,
  t.subject,
  t.priority,
  t.first_response_due_at,
  u.email as assignee_email
from tickets t
left join users u on u.id = t.assignee_id
where t.status in ('new', 'open')
  and t.first_response_due_at is not null
  and t.first_response_due_at < now()
order by t.first_response_due_at;

-- 2) Agent workload by status
select
  u.email,
  t.status,
  count(*) as ticket_count
from tickets t
join users u on u.id = t.assignee_id
group by u.email, t.status
order by u.email, t.status;

-- 3) Average resolution time for resolved tickets
select
  round(avg(extract(epoch from (t.resolved_at - t.created_at)) / 3600)::numeric, 2) as avg_resolution_hours
from tickets t
where t.status in ('resolved', 'closed')
  and t.resolved_at is not null;