One desk, three kinds of people

M19 replaces help desk, IT service management and customer support software: Zendesk, Freshdesk and Freshservice, Jira Service Management, ServiceNow ITSM at small-business scale, Zoho Desk and Intercom ^[1][2]. It is one desk for three groups of people: customers who ask a business for help, employees who ask for a laptop or a password reset, and the people who look after plant IT and OT, the computers that run machines. Table names start with svc_.

The desk takes tickets from email, a portal, a chat widget, phone logging, Slack or Teams, and an API. It uses queues, groups and routing, SLA policies with business hours, a service catalogue with approvals, incident, problem and change handling per ITIL 4 ^[3], a knowledge base, satisfaction surveys, and a configuration database (CMDB) that includes PLCs, HMIs and SCADA servers linked to the plant's equipment list.

The capabilities and their tiers

Each capability carries a tier. MVP must exist for a small business to cancel the old tool with confidence. STD is parity with mainstream tools. BIC is best-in-class work, here the AI assist, which is advanced.

What a ticket holds

A ticket is a row in svc_ticket; every message on it, public reply or internal note, is a row in svc_ticket_message. Messages are never edited: a correction is a new message.

Threading: a reply joins its ticket

An email has a Message-ID header. An answer to it carries In-Reply-To and References headers naming the message it answers ^[4]. The desk makes each outgoing message's ID before it is saved, stores it in channel_message_id and sends it in that header, so a reply can be matched exactly. If the headers do not match, the ticket number in the subject is the second try. The subject words and the sender address alone are never used, because one person has many tickets and subjects are reused.

• Two robots never answer each other. Mail marked Auto-Submitted, and mail from the desk's own addresses, is ignored.
• Sending twice adds nothing. channel_message_id is unique, so the same email cannot be stored twice.
• Unknown sender. The ticket is made with requester_person_id empty and the address kept for the lead to link or create.
• Mail from the host is authenticated. The web address the mail host forwards to carries a secret that the host sends with every request, kept in the host's environment variables; a request without it is refused.

Routing, priority and macros

A ticket takes the default_group_id of its category. The next agent is the group member whose last assignment is oldest, which is worked out by reading svc_ticket, so routing never writes to the group row. Priority comes from impact and urgency. The module does not fix the table; you write your own down. The one below is an example.

Who may do what

Access is written once, in the Role and Exposure Matrix, and row-level security (the database rule that decides which rows each login may read or change) is generated from it. Five roles are people; three logins are not.

What an SLA policy holds

A policy in svc_sla_policy is chosen for a ticket by its conditions (for example priority p3) and its priority_order. It has up to three targets in minutes: first_response_min, next_response_min and resolution_min. It points at svc_business_hours (a time zone, the opening periods for each weekday, and a list of holidays) and lists pause_statuses, the ticket statuses during which the clock stops. ISO/IEC 20000-1:2018 sets the requirements for a service management system and expects service levels to be agreed and monitored ^[1]; ITIL 4 calls the practice service level management ^[2].

The numbers below are lab values used through this module to practise the arithmetic. They are not recommendations; use your own.

{"mon": [["09:00","17:00"]], "tue": [["09:00","17:00"]], "wed": [["09:00","17:00"]],
 "thu": [["09:00","17:00"]], "fri": [["09:00","17:00"]], "sat": [], "sun": []}

Counting business time

The rule is the same every time: start the clock at the ticket's creation or, if that is outside business hours or on a holiday, at the next opening; count only minutes inside the opening periods; stop counting during a pause. Times are stored in UTC, the one agreed reference time with no daylight saving, so a stored moment means the same instant wherever it is read, and the sums are done in the business hours' time zone using the language's own time-zone support, never hand-made offsets, so a day when the clocks change still comes out right.

Three of four tickets meet every target: an SLA attainment of 75 percent (tickets meeting all targets divided by tickets under an SLA). These four tickets are the seed the clock must reproduce exactly in a test.

Events, warnings and attainment

• svc_sla_event holds one row per ticket and metric (first_response, next_response, resolution): target_at, met_at, breached and paused_minutes. This build computes first_response and resolution; next_response_min is stored in the policy but not computed, so no next_response event is written.
• Warning. At a share of the target's business minutes that you choose (80 percent is an example) the lead gets an action item and the ticket shows as at risk before it breaches. When the target passes, breached is set.
• Free way first. The clock can be run whenever an agent or the lead opens the queue. A scheduled job is optional; its limits are on your host's own pricing page.

The fifteen tables

Every table carries the standard columns id, tenant_id, created_at, created_by, updated_at, updated_by, row_version, archived_at and ext (extra data as JSON), and is keyed to the spine tables core_person, core_site and core_equipment, so the desk has no people list, site list or equipment list of its own. The table below shows the columns the module adds. Types: text, int, bool, ts (timestamp with time zone), uuid, json. An arrow is a foreign key; soft link means a plain uuid with no foreign key, so the desk works with it empty. req means required.

Rules the database enforces

• Unique per tenant: ticket_no, problem_no, cr_no, ci_no, the group name, and channel_message_id when present.
• One row only: one svc_sla_event per ticket and metric; one svc_csat_response per ticket.
• Never wrong: a ticket cannot be its own parent; an item cannot relate to itself; a change cannot reach authorize without both an implementation plan and a back-out plan.
• Your choice, written down: the satisfaction scale. The specification leaves it open; 1 to 5 with 4 and 5 counted as positive is an example.
• Numbers: ticket and change numbers come from a numbering function that runs with its owner's rights, so no role needs a right to change the sequence table.

Incidents and major incidents

An incident is an unplanned interruption or reduction in the quality of a service; the aim is to restore service quickly. In the desk it is a ticket with ticket_type incident. When many people are affected the lead sets is_major_incident. Related tickets are linked with parent_ticket_id, one communication template posts one public update to each, and a post-incident review follows (it is held outside the desk in this build, and its outcome goes into the problem's root cause). Agents cannot set the flag themselves; a macro that tries is refused for an agent.

Problems and known errors

A problem is the underlying cause of one or more incidents. A row in svc_problem is linked from the incidents through problem_id, records the root_cause and a workaround, and moves open, investigating, known_error, resolved, closed. When a workaround is known the problem is a known error, and the workaround is written as an internal knowledge article so the next agent does not have to ask. In the lab, a problem links at least two incidents; a fault that keeps returning is the reason to open one.

Change enablement

ITIL 4 describes change enablement as making as many changes succeed as possible by assessing risk, authorising them and managing the change schedule ^[1]. A row in svc_change_request has a type (standard, normal or emergency), a risk of low, medium or high, an impact analysis, an implementation plan, a back-out plan (how to undo it), a scheduled window, the board's decision time and the configuration item it touches. ISO/IEC 20000-1:2018, the requirements for a service management system, also includes change management and configuration management ^[3].

The three types, in ITIL 4 terms: a standard change is low risk, well understood and pre-authorised; a normal change is assessed and decided through the change board; an emergency change is urgent and is assessed and decided quickly. In this build all three types keep the same record and the same two gates, so the type is recorded but does not change the route.

• Refused: a move to authorize with no back-out plan.
• Refused: scheduled for a PLC or HMI item with no ext.moc_ref. The specification says change requests on PLC and HMI items need a link to the plant's management-of-change record (module M11) before authorisation. This build always asks for a reference for those two classes; with no M11 it can be the plant's own MOC document number. The agent who wrote the change records it in ext.moc_ref before moving the change to authorize, so the approver is not refused.
• Not by the author. Nobody approves a change they created. The approver records the decision with a core_approval row and the cab_decision_at time.
• Flagged: a second change on the same item in an overlapping window.

The configuration database

A configuration item (CI) is anything that has to be managed to deliver a service. In svc_configuration_item there are thirteen classes, both IT (server, workstation, laptop, network_device, application, database, printer, mobile_device, cloud_service) and OT (plc, hmi, scada_server, historian). Each has an owner, a site, a status (planned, in_service, maintenance, retired), and, for firmware and addresses, firmware_version and ip_address. A CI can point at a core_equipment row through equipment_id, which links the IT view of a PLC to the maintenance view of the machine it runs.

svc_ci_relationship records dependencies with five relations: depends_on, hosts, connects_to, part_of and backs_up. This is the second pitfall: a CMDB without relationships cannot assess the impact of a change. A recursive query, one that follows links again and again, lists everything affected by an item and goes into the change's impact analysis. It follows depends_on and connects_to backwards, to the items that point at the one being changed, and hosts forwards, to the items the changed item hosts. That works because connects_to is always recorded from the item that uses the link to the item it talks to: the operator screen connects_to the PLC, never the PLC to the screen. Write the convention down; a row stored the other way round drops out of the impact list.

Knowledge that deflects

An article in svc_kb_article is internal or public, and is a draft, published or retired. A change to a published article becomes a new row with the next version, and the old one is retired; nothing is edited in place. The portal's request form offers published public articles as the person types. If an article answers the question, no ticket is raised, and the visit counts toward the deflection rate: portal sessions with an article view and no ticket, divided by portal sessions. Agents see suggestions while they type, and each article keeps helpful_yes and helpful_no counts. ITIL 4 calls this practice knowledge management ^[4].

The specification names the event svc.kb_gap.detected and the deflection rate; the six sessions write them down but do not build them, because both need the portal's sessions to be recorded. The AI assist (tier BIC) can suggest a category and priority, summarise a long thread and propose a reply or an article. Every suggestion waits for the agent's approval, and a message from the assistant is recorded with author_type ai. Tickets hold personal data, so never paste ticket text into a chat or a prompt that you have not been cleared to use.

Satisfaction and complaints

When a ticket is resolved the desk prepares a survey link carrying a signed single-use token: a random value the server stamps with a secret key, so it cannot be guessed or altered and the requester can answer once without signing in. The intake route writes one svc_csat_response; a second answer is refused. CSAT is positive responses divided by responses, and you decide which scores are positive and write it down. If the email setting allows, the survey is emailed; otherwise the link shows on the ticket. Nothing emails a customer until you switch email on, and the sending provider's price is on its own price page.

A customer who is unhappy may be making a complaint, a ticket of type complaint. ISO 10002:2018 gives guidelines for handling complaints in an organisation ^[1]. The desk records it and publishes svc.ticket.complaint; investigating a quality complaint is the job of module M08.

The six measures

A median is the middle value of the numbers sorted from least to most; with an even count it is the mean of the middle two. Count first response in business minutes or calendar minutes to match what the old tool counts, and write which.

Moving out of the old tool

The old tool can export tickets with comments, users, organisations and articles through its API, as the specification notes for Zendesk, Freshdesk and Jira Service Management; check that your plan includes it on the vendor's own page instead of guessing. The vendors' pages show the words they use for tickets, requests and articles ^[2][3]. Closed tickets arrive as history and articles arrive as drafts for the lead to review. The goal is a cutover you can undo until a set date, and a cancellation only after the new desk matches the old one.

1. Export everything and count it: tickets open and closed, comments, users, organisations, articles. Choose a date that separates history from live tickets. Keep the export in a private folder and decide how long you will hold it.
2. Map old statuses, priorities, channels, types, groups and tags onto the desk's, and list anything unmapped in a report; never guess.
3. Import with the import login. A unique legacy key on tickets, messages and articles (the old tool's name and id) makes the import idempotent: running it twice gives the same result.
4. Run side by side for at least a week, importing each morning while the old tool stays live.
5. Compare first response, time to resolution and SLA attainment with the old tool's own reports for the same days, and explain every difference. The usual causes are calendar time against business hours, another holiday list, another pause rule, another time zone, merged or spam tickets, and solved against closed.
6. Cut over on a set date with a final import, a rollback plan and the date after which switching back is no longer possible. Then cancel the paid tool after the final export is stored and write the saving from real invoices.