Support Helpdesk Schema — StealThis.dev

コード

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);

insert into users (email, full_name, role)
values
  ('[email protected]', 'Customer One', 'customer'),
  ('[email protected]', 'Agent One', 'agent'),
  ('[email protected]', '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');

-- 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;

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
  }

Helpdesk model for support operations, SLA tracking, and ticket lifecycle auditing.