Domain Model

All Drizzle schemas live in @nx/core under packages/core/src/models/schemas/helpdesk/ (30 table directories). Helpdesk declares repository classes only. The helpdesk PostgreSQL schema is defined by PostgresSchemas.HELPDESK = 'helpdesk'.

1. Full ERD

2. Entities

One block per primary table. Schema source root: packages/core/src/models/schemas/helpdesk/. Common columns (id, createdAt, updatedAt, deletedAt, metadata) come from generateCommonColumnDefs().

Ticket

Property	Value
Table	helpdesk.Ticket
Source	helpdesk/ticket/schema.ts
Soft-delete	yes
Owner ID column	merchantId (+ organizerId)

Fields:

Field	Type	Required	Default	Description
id	text	✓	Snowflake	PK
organizerId	text	✓	-	Organizer scope
merchantId	text	✓	-	Tenant scope
reporterId	text	✓	-	Who opened the ticket
reporterType	text (TActorTypes)	✓	USER	USER / AGENT / SYSTEM
category	text	✓	-	TicketCategory ref (soft ref)
priority	integer (TTicketPriorities)	✓	MEDIUM	LOW/MEDIUM/HIGH/URGENT/CRITICAL
status	text (TTicketStatuses)	✓	OPEN	See enum below
subject	i18n jsonb	✓	-	{ en, vi }
description	i18n jsonb	✓	-	{ en, vi }
context	jsonb	✓	{}	Enriched order/product context
assignedToAgentId	text	-	-	Current agent
assignedToUserId	text	-	-	Agent's user id
assignedAt	timestamptz	-	-	-
resolvedAt / resolvedBy / resolutionNote	timestamptz / text / i18n	-	-	Resolution data
closedAt / closedBy	timestamptz / text	-	-	Closure data
sessionId	text	-	-	Originating session
metadata	jsonb	✓	{}	Misc flags (e.g. categorySkills, tagIds)

Status enum (TicketStatuses):

Value	Description
OPEN	New, not yet assigned
ASSIGNED	Assigned to an agent
IN_PROGRESS	Being worked on
WAITING_USER	Awaiting reporter reply
WAITING_AGENT	Awaiting an agent
WAITING_INTERNAL	Awaiting another team
PENDING	Holding / uncategorized
ASSIGNMENT_FAILED	Auto-assign found no agent
ESCALATED	Escalated to higher tier
RESOLVED	Agent resolved, awaiting confirm
CLOSED	Confirmed complete
REOPENED	Reopened after resolution/closure

Helper sets: ACTIVE_SET = {OPEN, IN_PROGRESS, REOPENED}, RESOLVED_SET = {RESOLVED, CLOSED}.

Indexes: IDX_Ticket_merchant_status_created_at, IDX_Ticket_assigned_to_status, IDX_Ticket_reporter_created_at, IDX_Ticket_category_priority_status, IDX_Ticket_status, IDX_Ticket_priority, IDX_Ticket_created_at.

TicketMessage

Property	Value
Table	helpdesk.TicketMessage
Source	helpdesk/ticket-message/schema.ts
Soft-delete	yes

Field	Type	Required	Default	Description
ticketId	text	✓	-	Parent ticket
authorId (col sender_id)	text	✓	-	Author
authorType (col sender_type)	text (TActorTypes)	✓	-	USER/AGENT/SYSTEM
type (col message_type)	text (TTicketMessageTypes)	✓	-	See enum
content	i18n jsonb	✓	-	Body { en, vi }
isInternal	boolean	✓	false	Hidden from reporter
attachmentIds	text[]	✓	{}	Attachment refs

Message types (TicketMessageTypes): COMMENT, USER_REPLY, AGENT_REPLY, INTERNAL_NOTE, SYSTEM_MESSAGE, AUTO_REPLY.

TicketEvent (audit / event sourcing)

Property	Value
Table	helpdesk.TicketEvent
Source	helpdesk/ticket-event/schema.ts

Field	Type	Required	Description
ticketId	text	✓	Parent
eventType	text (TTicketEventTypes)	✓	See enum
actorId / actorType	text	-	Who acted
oldValue / newValue	text	-	Status/value diff
changes	jsonb	-	Structured diff
reason	text	-	Optional reason

Event types (TicketEventTypes, dotted-string values, e.g. ticket.created): TICKET_CREATED, TICKET_UPDATED, TICKET_ASSIGNED, TICKET_ASSIGNMENT_FAILED, TICKET_REASSIGNED, TICKET_STATUS_CHANGED, TICKET_PRIORITY_CHANGED, TICKET_RESOLVED, TICKET_CLOSED, TICKET_REOPENED, TICKET_ESCALATED, MESSAGE_CREATED, MESSAGE_UPDATED, SLA_WARNING, SLA_BREACHED, SLA_CRITICAL_BREACH.

SlaPolicy

Property	Value
Table	helpdesk.SlaPolicy
Source	helpdesk/sla-policy/schema.ts
Soft-delete	yes

Per-priority response/resolution deadlines, warning thresholds, escalation rules, and business-hours flag. Defaults: see SLA & Escalation.

SlaTracker

Property	Value
Table	helpdesk.SlaTracker
Source	helpdesk/sla-tracker/schema.ts
Cardinality	1:1 per ticket (UQ_SlaTracker_ticket_id)

Field	Type	Required	Default	Description
ticketId	text	✓	-	Ticket (unique)
policyId (col sla_policy_id)	text	✓	-	SlaPolicy
priority	integer	✓	20	Snapshot priority
firstResponseDeadline	timestamptz	✓	-	Response deadline
resolutionDeadline	timestamptz	✓	-	Resolution deadline
firstResponseAt / resolvedAt	timestamptz	-	-	Actuals
firstResponseStatus	text (TSlaStatuses)	✓	OK	OK/WARNING/BREACHED
resolutionStatus	text (TSlaStatuses)	✓	OK	OK/WARNING/BREACHED
timeToFirstResponse / timeToResolution	integer	-	-	Minutes
breachedAt / breachMinutes / breachReason	timestamptz / integer / text	-	0	Breach tracking
escalationLevel	integer	✓	0	Current escalation level
warningsSent	jsonb[]	✓	[]	Warnings already dispatched
lastCheckedAt	timestamptz	-	-	Last monitor pass

SLA status enum (SlaStatuses): OK, WARNING, BREACHED.

SlaEscalation

Property	Value
Table	helpdesk.SlaEscalation
Source	helpdesk/sla-escalation/schema.ts

Field	Type	Required	Description
ticketId	text	✓	Ticket
escalationType	text (TSlaEscalationTypes)	✓	SLA_BREACH / MANUAL / CUSTOMER_REQUEST / HIGH_IMPACT / INCIDENT_REPORT
escalationLevel	smallint (TSlaEscalationLevel)	✓	1 / 2 / 3
previousPriority / newPriority	smallint	-	Priority change
escalatedBy / escalatedById	text	✓ / -	Actor
reason	text	✓	Why
actions	jsonb[]	✓	Actions taken
escalatedAt / resolvedAt	timestamptz	✓ / -	Timestamps

Supporting entities

Table	Source	Purpose
TicketAssignment	helpdesk/ticket-assignment/	Assignment history per ticket
TicketCategory	helpdesk/ticket-category/	Categories with ticket counts
TicketTag / TicketTagMapping	helpdesk/ticket-tag*/	Tags + M:N mapping, usage counts
Agent / AgentGroup / AgentGroupMember	helpdesk/agent*/	Agents, groups, membership
AssignmentRule	helpdesk/assignment-rule/	Routing rules → groups/agents
Article / ArticleCategory / ArticleView / ArticleFeedback	helpdesk/article*/	Knowledge base
Survey / SurveyQuestion / SurveyResponse	helpdesk/survey*/	CSAT surveys
FeatureRequest / FeatureVote	helpdesk/feature-*/	Feature voting
Notification / NotificationTemplate / NotificationPreference / NotificationDeliveryLog / NotificationBatch	helpdesk/notification*/	Notification engine
Compensation	helpdesk/compensation/	SLA-breach compensation records
JobExecutionLog	helpdesk/job-execution-log/	Worker idempotency / audit

3. Cross-entity Invariants

Invariant	Enforcement
Exactly one SlaTracker per ticket	UQ_SlaTracker_ticket_id unique index
Ticket status transitions follow the state machine	UpdateTicketStatusUseCase (XState)
Every status change writes a TicketEvent	Use-cases write event in same operation
TicketCategory.ticketCount reflects tickets	TicketCreatedListener.incrementTicketCount()
TicketTag.usageCount reflects mappings	TicketCreatedListener.incrementUsageCount()
escalationLevel increases monotonically	SLA monitor + escalation worker

4. Soft-delete Behavior

Behavior	Detail
Read default	deletedAt IS NULL (via generateCommonColumnDefs())
Hard-delete	Not the default; soft-delete via deletedAt timestamp
Restore	Clear deletedAt

5. Related Pages

• Architecture
• Ticket System
• SLA & Escalation
• Decisions