A minimal but sufficient schema fits in one table. Each field solves a specific failure.
| Field | Type | Why it exists | Failure if missing |
|---|
item_id | string, primary key | Stable handle for every mention, timer and audit entry | Duplicate work, lost threads |
created_at | timestamp | Start of the wait clock | No SLA measurement possible |
channel | enum | Email, chat, phone, API, internal | Triage cannot set the response format |
raw_message | text | The user's words, unsummarized | Reviewer has to reconstruct context |
normalized_summary | text | One or two sentences for scanning | Reviewers read everything, slow triage |
candidates | JSON array | Top routes with scores and the reason each lost | No feedback loop is possible |
priority_score | float | Orders the queue | Ordering is whoever shouts loudest |
tier | enum | Customer or account tier used in scoring | Priority is opaque and disputed |
state | enum | New, claimed, working, resolved, closed, reopened | Items disappear without trace |
owner_id | string, nullable | The person who owns it right now | Everyone assumes someone else did |
deadline_at | timestamp | When the timer fires | Items sit indefinitely |
escalation_level | integer | Which tier is currently responsible | Escalation is ad hoc |
resolution_code | enum | Why it resolved, used for rule generation | Learning loop never closes |
rule_id_created | string, nullable | The routing rule the resolution produced | The same miss repeats |
Two of these deserve emphasis. The candidates array is the field most teams omit, and it is the one that turns a queue into a training set. Store the route name, the score, and one line on why the router ranked it: too low a score, two candidates tied, no candidate at all. When a reviewer later says the right answer was candidate three, that single record is a labelled example.
The resolution_code is the other. Free-text notes are useful to a human and useless to a router. A small closed list (wrong intent, missing information, catalog gap, policy exception, duplicate) tells you whether to add a rule, add a route to the catalog, or change the form that collects the request.
Microsoft's intent-driven routing documentation shows the same concept from the platform side: classification rules read intent fields and attach skills or priority to the work item before it reaches a queue (intent-driven routing). Your schema should do that work explicitly so that you can see and change it.
A workable row looks like this:
{
"item_id": "q-2025-04812",
"created_at": "2025-06-02T09:14:11Z",
"channel": "email",
"raw_message": "Our invoice shows a charge for a plan we cancelled...",
"normalized_summary": "Disputed charge after cancellation",
"candidates": [
{"route": "billing.refunds", "score": 0.41, "note": "below threshold 0.55"},
{"route": "billing.cancellations", "score": 0.38, "note": "lexical match only"},
{"route": "account.status", "score": 0.21, "note": "no supporting terms"}
],
"tier": "enterprise",
"state": "new",
"owner_id": null,
"priority_score": 78.5,
"deadline_at": "2025-06-02T09:59:11Z",
"escalation_level": 0,
"resolution_code": null,
"rule_id_created": null
}
Store the candidates as written, not as a regenerated list you compute later. Routing logic changes, and a replay against a newer router will not reproduce what the reviewer actually saw.