Support Tickets

support

Customer support tickets with response tracking and SLA management.

Collections

Tickets

Support tickets and requests

Schema Fields

subject:stringdescription:stringstatus:stringpriority:stringcategory:stringassignee:stringchannel:stringtags:array

Customers

Customer profiles

Schema Fields

name:stringemail:stringplan:stringcompany:string

Responses

Ticket replies and internal notes

Schema Fields

body:stringtype:stringauthor:stringtimestamp:string

Relationships

tickets

submitted_by

customersTicket submitted by customer

tickets

has_response

responsesTicket has response

tickets

related_to

ticketsRelated tickets

Example Queries

Query this template's data using the SQL endpoint.

Open tickets by priority

Count open tickets grouped by priority to identify escalation needs.

SELECT
  data.priority.:String as priority,
  count() as ticket_count
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'tickets'
  AND deleted = false
  AND data.status.:String IN ('open', 'in_progress', 'waiting')
GROUP BY priority
ORDER BY ticket_count DESC

Tickets by category and channel

Breakdown of ticket categories across support channels.

SELECT
  data.category.:String as category,
  data.channel.:String as channel,
  count() as ticket_count
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'tickets'
  AND deleted = false
GROUP BY category, channel
ORDER BY ticket_count DESC

Agent workload

Count active tickets per assignee to balance workload.

SELECT
  data.assignee.:String as agent,
  count() as active_tickets,
  countIf(data.priority.:String = 'urgent') as urgent_count
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'tickets'
  AND deleted = false
  AND data.status.:String IN ('open', 'in_progress')
GROUP BY agent
ORDER BY active_tickets DESC

Tickets by customer plan

Join tickets with customers to see ticket volume by plan tier.

WITH
  ticket_customers AS (
    SELECT source_id as ticket_id, target_id as customer_id
    FROM relationships FINAL
    WHERE workspace_id = {workspace_id:String}
      AND rel_type = 'submitted_by'
      AND deleted = false
  )
SELECT
  c.data.plan.:String as plan,
  count() as ticket_count
FROM records t FINAL
JOIN ticket_customers tc ON tc.ticket_id = t.id
JOIN records c FINAL ON c.id = tc.customer_id
  AND c.workspace_id = {workspace_id:String}
  AND c.deleted = false
WHERE t.workspace_id = {workspace_id:String}
  AND t.collection = 'tickets'
  AND t.deleted = false
GROUP BY plan
ORDER BY ticket_count DESC

Quick Start

Create the collections using the Rekor CLI:

rekor collections upsert tickets --workspace my-support --schema @tickets.json

Full Schema (JSON)

Copy the complete template definition:

{
  "collections": [
    {
      "id": "tickets",
      "name": "Tickets",
      "description": "Support tickets and requests",
      "icon": "ticket",
      "color": "#ef4444",
      "json_schema": {
        "type": "object",
        "required": [
          "subject",
          "status",
          "priority"
        ],
        "properties": {
          "subject": {
            "type": "string"
          },
          "description": {
            "type": "string"
          },
          "status": {
            "type": "string",
            "enum": [
              "open",
              "in_progress",
              "waiting",
              "resolved",
              "closed"
            ]
          },
          "priority": {
            "type": "string",
            "enum": [
              "low",
              "medium",
              "high",
              "urgent"
            ]
          },
          "category": {
            "type": "string",
            "enum": [
              "bug",
              "feature_request",
              "question",
              "billing",
              "other"
            ]
          },
          "assignee": {
            "type": "string"
          },
          "channel": {
            "type": "string",
            "enum": [
              "email",
              "chat",
              "phone",
              "web"
            ]
          },
          "tags": {
            "type": "array",
            "items": {
              "type": "string"
            }
          }
        }
      }
    },
    {
      "id": "customers",
      "name": "Customers",
      "description": "Customer profiles",
      "icon": "user",
      "color": "#3b82f6",
      "json_schema": {
        "type": "object",
        "required": [
          "name",
          "email"
        ],
        "properties": {
          "name": {
            "type": "string"
          },
          "email": {
            "type": "string",
            "format": "email"
          },
          "plan": {
            "type": "string",
            "enum": [
              "free",
              "pro",
              "enterprise"
            ]
          },
          "company": {
            "type": "string"
          }
        }
      }
    },
    {
      "id": "responses",
      "name": "Responses",
      "description": "Ticket replies and internal notes",
      "icon": "message-square",
      "color": "#22c55e",
      "json_schema": {
        "type": "object",
        "required": [
          "body",
          "type"
        ],
        "properties": {
          "body": {
            "type": "string"
          },
          "type": {
            "type": "string",
            "enum": [
              "reply",
              "internal_note"
            ]
          },
          "author": {
            "type": "string"
          },
          "timestamp": {
            "type": "string",
            "format": "date-time"
          }
        }
      }
    }
  ],
  "relationships": [
    {
      "type": "submitted_by",
      "source": "tickets",
      "target": "customers",
      "description": "Ticket submitted by customer"
    },
    {
      "type": "has_response",
      "source": "tickets",
      "target": "responses",
      "description": "Ticket has response"
    },
    {
      "type": "related_to",
      "source": "tickets",
      "target": "tickets",
      "description": "Related tickets"
    }
  ]
}