support

Support tickets linked to contacts, with priority levels, status tracking, and SLA metrics.

Tables

tickets

Column	Type	Description
id	uuid	Primary key, auto-generated
org_id	uuid	References organizations. CASCADE on delete
contact_id	uuid	References contacts. SET NULL on delete
assignee_id	uuid	References users. SET NULL on delete
creator_id	uuid	References users. SET NULL on delete
title	text	Ticket title (required)
description	text	Optional ticket description
status	ticket_status	Current status, defaults to 'open'
priority	ticket_priority	Priority level, defaults to 'medium'
channel	channel_type	How the ticket was created (email, sms, etc.)
resolved_at	timestamptz	When the ticket was resolved
closed_at	timestamptz	When the ticket was closed
first_response_at	timestamptz	For SLA tracking
created_at	timestamptz	Row creation time
updated_at	timestamptz	Auto-updated on change via trigger
metadata	jsonb	Freeform JSON, defaults to empty object

Enums

ticket_status

Value	Description
open	New or reopened, needs attention
pending	Waiting on customer or third party
resolved	Solution provided
closed	Ticket closed, no further action

ticket_priority

Value	Description
low	Low priority
medium	Medium priority (default)
high	High priority
urgent	Needs immediate attention

channel_type

Value	Description
email	Email channel
sms	SMS / text message
chat	Live chat or messaging
phone	Phone call
social	Social media

Row-Level Security

RLS is enabled and scoped to org_id via get_user_org_id(). SELECT, INSERT, and UPDATE are open to all org members. DELETE requires admin privileges via is_admin().

Dependencies

• crm (contacts, companies)

Example Queries

-- Open tickets sorted by priority and age
SELECT
  t.title,
  t.priority,
  t.channel,
  c.first_name || ' ' || coalesce(c.last_name, '') AS contact,
  u.full_name AS assignee,
  extract(epoch FROM now() - t.created_at) / 3600 AS hours_open
FROM tickets t
LEFT JOIN contacts c ON c.id = t.contact_id
LEFT JOIN users u ON u.id = t.assignee_id
WHERE t.status IN ('open', 'pending')
ORDER BY
  CASE t.priority
    WHEN 'urgent' THEN 0
    WHEN 'high' THEN 1
    WHEN 'medium' THEN 2
    WHEN 'low' THEN 3
  END,
  t.created_at ASC;

-- Average first response time by priority (last 30 days)
SELECT
  t.priority,
  count(*) AS ticket_count,
  round(
    avg(extract(epoch FROM t.first_response_at - t.created_at)) / 3600, 1
  ) AS avg_response_hours
FROM tickets t
WHERE t.first_response_at IS NOT NULL
  AND t.created_at >= now() - interval '30 days'
GROUP BY t.priority
ORDER BY
  CASE t.priority
    WHEN 'urgent' THEN 0
    WHEN 'high' THEN 1
    WHEN 'medium' THEN 2
    WHEN 'low' THEN 3
  END;

-- Ticket volume by channel and status
SELECT
  t.channel,
  count(*) FILTER (WHERE t.status = 'open') AS open,
  count(*) FILTER (WHERE t.status = 'pending') AS pending,
  count(*) FILTER (WHERE t.status = 'resolved') AS resolved,
  count(*) FILTER (WHERE t.status = 'closed') AS closed,
  count(*) AS total
FROM tickets t
GROUP BY t.channel
ORDER BY total DESC;

-- Unassigned tickets ordered by priority
SELECT
  t.title,
  t.priority,
  t.channel,
  t.created_at,
  c.first_name || ' ' || coalesce(c.last_name, '') AS contact
FROM tickets t
LEFT JOIN contacts c ON c.id = t.contact_id
WHERE t.assignee_id IS NULL
  AND t.status IN ('open', 'pending')
ORDER BY
  CASE t.priority
    WHEN 'urgent' THEN 0
    WHEN 'high' THEN 1
    WHEN 'medium' THEN 2
    WHEN 'low' THEN 3
  END,
  t.created_at ASC;

-- Average resolution time by assignee (last 30 days)
SELECT
  u.full_name,
  count(*) AS resolved_count,
  round(
    avg(extract(epoch FROM t.resolved_at - t.created_at)) / 3600, 1
  ) AS avg_resolution_hours
FROM tickets t
JOIN users u ON u.id = t.assignee_id
WHERE t.resolved_at IS NOT NULL
  AND t.resolved_at >= now() - interval '30 days'
GROUP BY u.id, u.full_name
ORDER BY avg_resolution_hours ASC;