Appearance
Data Model
Core tables
departments
Maps the routing taxonomy to operational teams.
| Column | Notes |
|---|---|
id | UUID primary key |
name / name_ar | English and Arabic display names |
l1_labels | L1 taxonomy labels that route to this department |
l2_labels | Extra label mapping support |
profiles
Application profile layer on top of auth.users.
| Column | Notes |
|---|---|
id | FK to auth.users(id) |
full_name | Display name |
role | end_user, agent, or admin |
department_id | Optional owning department |
email, avatar_url | Added in later migrations |
tickets
The main service desk entity.
| Column | Notes |
|---|---|
ticket_number | Human-friendly identity column |
title_ar / description_ar | Arabic ticket content |
submitter_id | End user who opened the ticket |
assigned_agent_id | Agent currently responsible |
department_id | Routed department |
status | new, pending_review, open, in_progress, resolved, closed |
priority | Added by later migration |
classification | JSONB classifier payload |
routing_mode | auto, review, or manual |
routing_confidence | Stored as numeric score |
routing_reason | Human-readable explanation |
routing_overridden | Tracks agent correction for analytics |
ticket_comments
Threaded human discussion on a ticket.
| Column | Notes |
|---|---|
ticket_id | Parent ticket |
author_id | Optional profile FK |
body | Comment text |
created_at | Timestamp |
notifications
Per-user activity feed.
| Column | Notes |
|---|---|
user_id | Target profile |
kind | Notification type |
title / body | Stored message payload |
is_read | Read state |
ticket_id / ticket_number | Added later for deep-linking |
meta | Structured JSON payload for UI localization and rendering |
quick_actions
Admin-managed operational guidance tied to taxonomy nodes.
| Column | Notes |
|---|---|
l1_label, l2_label, l3_label | Matching criteria |
action_type | checklist, template, or link |
action_payload | Action-specific JSON data |
sort_order | UI ordering |
ticket_audit_log
Tracks changes such as status updates, assignment, and routing overrides.
This table is introduced after the initial schema and is used by the ticket detail view for historical traceability.
Important schema evolution
| Migration area | Why it matters |
|---|---|
| RLS policies | Determines who can read and mutate tenant-like data |
| realtime publications | Enables queue and notification live updates |
| priority | Adds triage depth beyond status alone |
| invitations | Supports admin-driven user onboarding |
| audit log | Makes operational changes explainable |
| quick actions | Gives agents reusable response content |
| notification ticket refs | Allows accurate deep links from alerts |
Current shared application types
The shared package intentionally stays small:
ClassificationResultTicketRoutingMode- routing thresholds
- top-level L1 taxonomy labels
The richer API response types currently live in the web app rather than in @arabic-itsm/shared.