People and routing
Customers, Agents, Teams, Queues, TicketCategories, TicketTags. Define verified participants, queue ownership and the classification used to route requests.
- Customers
- Agents
- Teams
- Queues
- TicketCategories
- TicketTags
Data model
Customer conversations, service episodes and accountable handoffs.
18 tables · 52 relationships
How the model is organised
Requests and conversations
Tickets, TicketComments, TicketAttachments, TicketActivityLog. Retain each customer request with its public or private conversation and protected files.
- Tickets
- TicketComments
- TicketAttachments
- TicketActivityLog
Service commitments
SLAPolicies, ServiceEpisodes, SLABreaches. Preserve versioned targets, reopen cycles and missed commitments without rewriting history.
- SLAPolicies
- ServiceEpisodes
- SLABreaches
Handoffs and feedback
Escalations, AssignmentHistory, CSATSurveys. Record accountable changes in ownership and feedback from the actual closed episode and recipient.
- Escalations
- AssignmentHistory
- CSATSurveys
Knowledge in context
KBArticles, TicketKnowledgeLinks. Keep maintained guidance and deliberate internal article use separate from a public customer answer.
- KBArticles
- TicketKnowledgeLinks
Keys and references
WorkspaceID is server assigned throughout. Every ticket child and any optional comment reference must agree on its parent. Commands compare current revisions and retain durable replay keys. Service episode sequence and survey keys are unique per ticket occurrence; service actors are named explicitly instead of fabricated as human agents.
Migration order
- Configure verified Agents and Customers identities, team membership, queues, categories and scoped access.
- Prepare SLAPolicies and pinned business calendars; obtain a different Support Manager approval before activation.
- Import tickets with actual ownership, public/private comments and protected attachments, preserving intake identifiers.
- Reconcile ServiceEpisodes, earlier targets, pauses, resolutions and SLABreaches; do not recalculate historical success from current policy.
- Import assignment and escalation history, maintained KBArticles and deliberate TicketKnowledgeLinks.
- Reconcile actual CSAT invitation and response state, then test intake, clock arithmetic, routing concurrency, customer access and provider reconciliation.
Tables
18 tables
CustomersEnd users who open tickets. A customer may be an internal employee (for an IT helpdesk) or an external contact (for a B2B support team).master
| Field | Type | Required | Details |
|---|---|---|---|
CustomerNumber | text | yes | |
FullName | text | yes | |
Email | text | yes | |
Phone | text | ||
Company | text | For external customers | |
Type | select | yes | Internal, External, Partner |
VIPFlag | boolean | yes | Extra-careful handling |
PortalAccess | boolean | yes | Can log into customer portal |
PreferredLanguage | text | Configured language code; does not imply automatic translation | |
Active | boolean | yes | |
PrincipalID | text | Verified external identity; unique in workspace | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
AgentsInternal support staff — L1 reps, L2 specialists, managers, admins.master
| Field | Type | Required | Details |
|---|---|---|---|
AgentID | text | yes | |
FullName | text | yes | |
Email | text | yes | |
Role | select | yes | Admin, Support Manager, Agent L1, Agent L2 |
Team | reference | → Teams | |
Manager | reference | → Agents | |
Skills | text | Comma-separated skill tags | |
Shift | select | Day, Swing, Night | |
MaxConcurrent | number | Max concurrently open tickets | |
Active | boolean | yes | |
PrincipalID | text | Verified internal identity; unique in workspace | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
TeamsFunctional support groupings (IT Support, Billing, Technical, Tier 2).master
| Field | Type | Required | Details |
|---|---|---|---|
TeamCode | text | yes | |
Name | text | yes | |
Manager | reference | → Agents | |
CoverageHours | select | yes | BusinessHours, Extended, 24x7 |
Active | boolean | yes | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
QueuesRouting destinations for tickets. A queue belongs to a team.master
| Field | Type | Required | Details |
|---|---|---|---|
QueueCode | text | yes | |
Name | text | yes | |
Team | reference | yes | → Teams |
RequiredSkills | text | Comma-separated skill tags | |
RoutingStrategy | select | yes | RoundRobin, LeastLoaded, SkillMatch, Manual |
OverflowQueue | reference | → Queues (self) | |
Active | boolean | yes | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
RoutingCursor | reference | → Agents; Server-owned cursor; atomic eligible-agent round-robin reservation. |
SLAPoliciesResponse and resolution targets by priority, applied to tickets at intake.master
| Field | Type | Required | Details |
|---|---|---|---|
PolicyCode | text | yes | |
Name | text | yes | |
Priority | select | yes | Low, Medium, High, Urgent |
FirstResponseMinutes | number | yes | Target in business minutes |
ResolutionMinutes | number | yes | |
BusinessHoursOnly | boolean | yes | Pause outside business hours |
PauseOnPending | boolean | yes | Pause while waiting on customer |
EffectiveFrom | date | yes | |
Active | boolean | yes | |
Status | select | Draft, Submitted, Approved, Retired | |
Version | text | Immutable policy version after approval | |
CalendarVersion | text | Configured holiday/business-hours calendar and time zone | |
PreparedBy | reference | yes | → Agents; immutable material preparer of this policy version. |
ApprovalRevision | number | Exact protected revision approved; changed targets invalidate it. | |
SubmittedBy | reference | → Agents | |
ApprovedBy | reference | → Agents; Support Manager distinct from submitter | |
ApprovedAt | datetime | Decision time | |
ReopenDays | number | Configured positive reopen window in days | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
TicketCategoriesTopical categorization for triage and reporting (Bug, HowTo, Billing, AccessRequest, Outage, …).master
| Field | Type | Required | Details |
|---|---|---|---|
CategoryCode | text | yes | |
Name | text | yes | |
DefaultQueue | reference | → Queues | |
DefaultPriority | select | Low, Medium, High, Urgent | |
Active | boolean | yes | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
TicketsThe core support record — one per customer issue.transaction
| Field | Type | Required | Details |
|---|---|---|---|
TicketNumber | text | yes | |
Subject | text | yes | |
Customer | reference | yes | → Customers |
Category | reference | → TicketCategories | |
Source | select | yes | Email, WebForm, Portal, API, Phone |
Queue | reference | → Queues; Nullable only while configuration exception is unresolved. | |
Assignee | reference | → Agents | |
Priority | select | yes | Low, Medium, High, Urgent |
Status | select | yes | New, Open, Pending, OnHold, Resolved, Closed |
SLAPolicy | reference | → SLAPolicies; Approved policy required before triage completes; null on visible configuration exception. | |
FirstResponseDue | datetime | Server projection of active episode first-response target; null while configuration unresolved. | |
ResolutionDue | datetime | Server projection of active episode EndTime; null while configuration unresolved. | |
FirstResponseAt | datetime | Store UTC timestamp; display in configured time zone. | |
ResolvedAt | datetime | Store UTC timestamp; display in configured time zone. | |
ClosedAt | datetime | Store UTC timestamp; display in configured time zone. | |
ReopenedCount | number | yes | Default 0 |
Resolution | text | Final resolution notes | |
RootCauseCategory | select | Bug, Config, HowTo, Billing, Access, ThirdParty, Other | |
CreatedAt | datetime | Server intake time | |
IntakeKey | text | Unique source connector and event identifier | |
PolicyVersion | text | Frozen approved SLAPolicy version | |
CalendarVersion | text | Pinned configured business-hours calendar and time zone | |
FirstResponseTargetMinutes | number | Frozen first-response target at intake | |
ResolutionTargetMinutes | number | Frozen resolution target for the service episode | |
PendingSince | datetime | Start of current authorized pending pause | |
PausedBusinessMinutes | number | Accumulated eligible paused minutes | |
Episode | number | Starts at 1; increments on authorized reopen | |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
ConfigurationException | longtext | Missing policy, calendar or queue; visible until explicitly resolved. | |
PublicResolution | longtext | Customer-facing summary; internal Resolution stays private. | |
CurrentEpisode | reference | → ServiceEpisodes; Set atomically when valid triage creates an episode. |
TicketCommentsEvery reply and internal note on a ticket.line
| Field | Type | Required | Details |
|---|---|---|---|
Author | reference | → Agents (agent reply or note) | |
AuthorCustomer | reference | → Customers (customer reply) | |
Visibility | select | yes | Public, Internal |
Body | text | yes | |
CreatedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
Ticket | reference | yes | → Tickets; Mandatory parent; related comment and actor scope must match this ticket. |
CommandKey | text | Unique reply command; retry returns existing comment | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
DeliveryStatus | select | yes | NotRequired, Queued, Accepted, Failed, Unknown; portal persistence is NotRequired, email requires provider acceptance. |
ProviderMessageID | text | Correlated outbound message; unknown sends reconcile before retry. |
TicketAttachmentsFiles attached to a ticket or its comments (logs, screenshots, receipts).line
| Field | Type | Required | Details |
|---|---|---|---|
FileName | text | yes | |
FileSize | number | yes | Bytes |
MimeType | text | yes | |
StorageURL | text | yes | |
UploadedBy | reference | → Agents (or customer via UploadedByCustomer) | |
UploadedByCustomer | reference | → Customers | |
UploadedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
Ticket | reference | yes | → Tickets; Mandatory parent; related comment and actor scope must match this ticket. |
Comment | reference | → TicketComments; optional, same ticket required | |
Visibility | select | Public, Internal | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
TicketActivityLogImmutable audit of every state, owner, priority, queue, and SLA change.line
| Field | Type | Required | Details |
|---|---|---|---|
EventType | select | yes | StatusChange, Assignment, Reassignment, QueueChange, PriorityChange, SLAApplied, Escalated, Reopened, Closed |
OldValue | text | ||
NewValue | text | ||
ChangedBy | reference | → Agents; only when ActorKind = Agent | |
ChangedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
Ticket | reference | yes | → Tickets; Mandatory parent; related comment and actor scope must match this ticket. |
ActorKind | select | Agent, Customer, Service | |
CustomerActor | reference | → Customers; customer events only | |
ServiceName | text | Allowlisted service principal name when automated | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
CommandKey | text | yes | Unique authorized event occurrence. |
Episode | number | yes |
EscalationsEvery escalation hop on a ticket.transaction
| Field | Type | Required | Details |
|---|---|---|---|
EscalationNumber | text | yes | |
Ticket | reference | yes | → Tickets |
Reason | select | yes | SLARisk, CustomerRequest, ComplexityL2, VIP, Complaint |
FromAgent | reference | → Agents | |
ToAgent | reference | yes | → Agents |
EscalatedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
AcknowledgedAt | datetime | Store UTC timestamp; display in configured time zone. | |
ResolvedAt | datetime | Store UTC timestamp; display in configured time zone. | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
Status | select | yes | Recorded |
CommandKey | text | yes | Unique ticket/escalation occurrence. |
SLABreachesSLA clock breach events per ticket.transaction
| Field | Type | Required | Details |
|---|---|---|---|
Ticket | reference | yes | → Tickets |
SLAPolicy | reference | yes | → SLAPolicies |
BreachType | select | yes | FirstResponse, Resolution |
TargetAt | datetime | yes | When it was due Store UTC timestamp; display in configured time zone. |
BreachedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
MinutesOver | number | yes | |
Acknowledged | boolean | yes | |
AcknowledgedBy | reference | → Agents | |
Episode | number | Ticket service episode at breach | |
AcknowledgedAt | datetime | Acknowledgment time, never removes breach | |
RootCauseNote | text | Manager review reason | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
RecordNumber | text | yes | |
Status | select | yes | Recorded |
BreachKey | text | yes | Unique Ticket/Episode/BreachType; retries preserve original target and timestamp. |
ServiceEpisode | reference | yes | → ServiceEpisodes; Same ticket and episode required. |
AssignmentHistoryAudit of every assignee change on a ticket, for workload reporting and reassignment review.transaction
| Field | Type | Required | Details |
|---|---|---|---|
Ticket | reference | yes | → Tickets |
FromAgent | reference | → Agents (null on first assignment) | |
ToAgent | reference | yes | → Agents |
Assigner | reference | → Agents; otherwise ServiceName is required | |
Reason | select | InitialAssign, Reassign, Escalation, Overflow, AutoRoute | |
AssignedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
ServiceName | text | Allowlisted routing principal on automated assignment | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
RecordNumber | text | yes | |
Status | select | yes | Recorded |
CommandKey | text | yes | Unique authorized ownership change. |
CSATSurveysOne feedback invitation per eligible closed ticket episode. Unsent surveys have no score or SentAt; response is an explicit verified recipient action.transaction
| Field | Type | Required | Details |
|---|---|---|---|
Ticket | reference | yes | → Tickets |
Customer | reference | yes | → Customers |
Score | number | Integer 1–5, required only when Status = Responded | |
Comment | text | ||
SentAt | datetime | Set only after provider accepts the survey message. | |
RespondedAt | datetime | Store UTC timestamp; display in configured time zone. | |
Status | select | Queued, Sent, Responded, Expired, Failed | |
SurveyKey | text | Unique ticket and closure episode | |
Episode | number | Closed ticket episode surveyed | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
RecordNumber | text | yes | |
RecipientTokenHash | text | Store only hash of single-purpose expiring token. | |
TokenExpiresAt | datetime | ||
TokenConsumedAt | datetime | ||
DeliveryStatus | select | yes | Queued, Accepted, Failed, Unknown |
ProviderMessageID | text | ||
ServiceEpisode | reference | yes | → ServiceEpisodes; Must match Ticket/Episode and be closed before invitation. |
KBArticlesInternal-only notes that agents link to tickets as quick-reference macros. Deliberately lightweight — no authoring workflow, no versioning, no customer-facing portal. Teams needing a real KB link out to their external wiki.master
| Field | Type | Required | Details |
|---|---|---|---|
ArticleNumber | text | yes | |
Title | text | yes | |
Body | text | yes | Markdown |
Category | reference | → TicketCategories | |
Author | reference | yes | → Agents |
UseCount | number | yes | Default 0, incremented on link |
LastUpdatedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
Active | boolean | yes | |
ExternalURL | text | Optional approved knowledge-source URL | |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
TicketTagsFree-form tags on tickets for reporting and ad-hoc grouping. Modeled as a separate table so reports can count and group independently.transaction
| Field | Type | Required | Details |
|---|---|---|---|
Ticket | reference | yes | → Tickets |
Tag | text | yes | e.g. "outage-2026-04-15", "vpn", "billing-dispute" |
TaggedBy | reference | yes | → Agents |
TaggedAt | datetime | yes | Store UTC timestamp; display in configured time zone. |
WorkspaceID | text | yes | Server-assigned tenant scope; every reference and query enforces it. |
Revision | number | yes | Expected revision for authorized changes; append-only events cannot be edited. |
RecordNumber | text | yes | |
Status | select | yes | Recorded |
ServiceEpisodesService-time history for initial intake and each authorized reopen. The schedule shows elapsed service windows against due targets, not appointments.transaction
| Field | Type | Required | Details |
|---|---|---|---|
EpisodeNumber | text | yes | |
Ticket | reference | yes | → Tickets |
Episode | number | yes | Unique sequence per ticket; starts at one. |
CommandKey | text | yes | Unique intake/reopen intent for this episode. |
StartTime | datetime | yes | Intake time or verified reopen time. |
EndTime | datetime | yes | Current resolution deadline computed from approved targets and eligible pauses. |
OriginalEndTime | datetime | yes | Frozen initial resolution deadline; never overwritten by pause or acknowledgment. |
FirstResponseEndTime | datetime | Required only for Episode 1; reopening creates a resolution window and does not invent a second first-response obligation. | |
FirstResponseAt | datetime | ||
ResolvedAt | datetime | ||
ClosedAt | datetime | ||
Status | select | yes | Open, Pending, OnHold, Resolved, Closed |
SLAPolicy | reference | yes | → SLAPolicies |
PolicyVersion | text | yes | |
CalendarVersion | text | yes | |
FirstResponseTargetMinutes | number | yes | |
ResolutionTargetMinutes | number | yes | |
PausedBusinessMinutes | number | yes | |
PendingSince | datetime | ||
WorkspaceID | text | yes | |
Revision | number | yes | |
PriorityAtStart | select | yes | Low, Medium, High, Urgent; frozen episode priority; later edits cannot rewrite it. |
CategoryAtStart | reference | → TicketCategories; Frozen episode category; null remains uncategorized. | |
AgentAtResolution | reference | → Agents; Immutable current-assignee snapshot when ResolvedAt is first set. | |
TeamAtResolution | reference | → Teams; Immutable owning queue-team snapshot at resolution. |
TicketKnowledgeLinksInternal, auditable links between a ticket and a permitted knowledge note or external-source reference.line
| Field | Type | Required | Details |
|---|---|---|---|
Ticket | reference | yes | → Tickets |
Article | reference | yes | → KBArticles |
LinkedBy | reference | yes | → Agents |
LinkedAt | datetime | yes | |
LinkKey | text | yes | Unique deliberate ticket/article link occurrence; retry does not increase UseCount. |
Status | select | yes | Active, Removed |
WorkspaceID | text | yes | |
Revision | number | yes |