Ticket Numbering Developer Guide

Ticket numbering — Developer Guide

Everything that decides what a ticket's reference looks like, everything that turns a reference back into a ticket, and the migration tool that rewrites them all. Built for discussion #71.

The user-facing page is Ticket numbering; the "should I change it?" question has its own page.

---

1. 📁 The files involved

Colour key: 🗄️ schema · ⚙️ engine · 🔌 API · 🖥️ UI · 🧪 tests

🎨	File	What it does
⚙️	includes/ticket_numbering.php	TicketNumbering — the whole engine. Settings, generation, the counters, the parser patterns, the reverse lookup, and plan/apply for renumbering
🗄️	ticket_number_counters	one row per sequence. PK is the counter key; no auto-increment
🗄️	ticket_number_history	every number a ticket has ever had. The reason renumbering is safe at all
🗄️	tenants.ticket_code	the short code {COMPANY} renders
🔌	api/tickets/numbering_preview.php	validates a proposed configuration and returns three examples. Writes nothing
🔌	api/tickets/numbering_renumber.php	preview or apply. A thin door onto planRenumber() / applyRenumber()
🖥️	tickets/settings/index.php	the Ticket numbering tab and the renumbering tool
🖥️	system/companies/index.php	the per-company Ticket code field and the clash column
⚙️	includes/services/tickets.php	analyst-raised tickets — the only caller that knows a ticket type
⚙️	api/tickets/check_mailbox_email.php	emailed tickets. Also the reference parser's only real consumer
⚙️	api/self-service/create_ticket.php	portal tickets
🧪	tests/ticket-numbering.php	61 assertions

---

2. 🔴 The landmine that had to be defused first

Before any of this could be built, one thing had to be fixed: the old number format was hardcoded into the email-subject parser in seven places.

Every notification FreeITSM sends carries [SDREF:<number>] in its subject. Inbound mail was matched by a regex that knew the shape of the old random format — three letters, three digits, five digits. Change the format without touching that, and every reply to every email ever sent matches nothing and silently becomes a brand-new ticket. Not an error, not a warning: a duplicate, for every customer, for every historical thread.

So the parser is now format-agnostic and must stay that way:

const REF_PATTERN      = '/\[SDREF:\s*([A-Za-z0-9._\/-]{3,60})\s*\]/i';
const REF_LINE_PATTERN = '/\[\*{3}\s*SDREF:\s*([A-Za-z0-9._\/-]{3,60})\s*REPLY ABOVE THIS LINE\s*\*{3}\]/i';

The tag is a delimiter. The database is the authority. Whatever sits between SDREF: and ] is looked up with findTicketId(); the pattern never encodes what a number looks like. This is why validateFormat() bans square brackets — a bracket inside a number would break the delimiter — and why it bans spaces and caps the length at the column width of 50.

⚠️ If you ever find yourself "tidying" REF_PATTERN to be stricter, stop. Every number the install has ever issued, under every format it has ever used, has to keep parsing. The pattern's looseness is the feature.

---

3. The settings, and the default that changes nothing

const DEFAULTS = [
    'ticket_number_style'    => 'random',          // random | sequential
    'ticket_number_format'   => 'TICKET-{######}',
    'ticket_number_start'    => '1',
    'ticket_number_scope'    => 'global',          // global | per_type | per_company
    'ticket_number_reset'    => 'never',           // never | yearly | monthly
];

The defaults reproduce today's CKQ-418-73926 exactly, so upgrading changes nothing until somebody chooses otherwise. Settings are cached per request in a class property; forget() clears it and withSettings() overrides it for tests.

They are declared in tickets/settings/manifest.php under Cap::TICKETS_NUMBERING, marked sensitive. settingKeyOwners() is derived from the manifests, so listing them in setting_keys is all that's needed for the permission and the settings-key registry to agree — D005's registry self-check proves it.

---

4. Generating a number

next(PDO $conn, ?int $ticketTypeId, ?int $tenantId) is the single entry point. Three callers, and all three must pass what they know:

Caller	type	company
TicketsService::createTicket()	✅	✅
api/tickets/check_mailbox_email.php	✗ — an emailed ticket has no type yet	✅
api/self-service/create_ticket.php	✗	✅

⚠️ Both intake paths originally passed null for the company, and the email path worked its routing out after numbering. Under per-company numbering every emailed ticket would have carried the default company's number whatever it was routed to — on an MSP install, where email is the busiest intake path and per-company numbering is the whole point. If you add a fourth caller, pass the company.

The claim / prove / jump loop

$step = 1;
for ($attempt = 0; $attempt < 40; $attempt++) {
    $seq    = self::claimNext($conn, $cfg, $ticketTypeId, $tenantId);
    $number = self::render(...);
    if (!self::inUse($conn, $number)) return $number;
    self::windCounterTo($conn, self::counterKey(...), $seq + $step);
    $step = min($step * 2, 65536);
}

Two ideas, both load-bearing:

Uniqueness is proven against the table, never assumed from the counter. Two requests can read the same counter, and a renumbered install has gaps. inUse() checks live tickets and history.

A counter that has fallen BEHIND the estate is jumped over, not crawled past. This was originally ten single-step attempts and it took a live install's mail collection down: the counter said 50 while renumbered tickets ran to 106, so the first 57 claims were all taken and ten attempts could never clear them. Doubling the stride clears 57 in six. windCounterTo() uses GREATEST, so counters only ever move forward and a jump cannot hand a lower number to a request that overtakes us.

claimNext, and the two traps in it

INSERT INTO ticket_number_counters (counter_key, next_value) VALUES (?, ?)
ON DUPLICATE KEY UPDATE next_value = LAST_INSERT_ID(next_value + 1)

LAST_INSERT_ID(expr) makes the read and the increment one statement, so two tickets created in the same instant cannot take the same number. A SELECT then an UPDATE would race, and the collision would only appear under load.

⚠️ Read rowCount() before lastInsertId(). MySQL returns 1 for a fresh insert and 2 when ON DUPLICATE KEY actually updated. On the insert path lastInsertId() is meaningless — the table has no AUTO_INCREMENT — so it returns 0 and the first ticket on every counter would be numbered zero.

---

5. Counter keys, and what a scope really is

counterKey($cfg, $ticketTypeId, $tenantId, ?DateTimeImmutable $at = null)
//  global,  never   -> "t"
//  per_type          -> "t:ty42"
//  per_company       -> "t:co7"
//  + yearly          -> "t:2026"      (monthly: "t:202608")

The reset period is part of the key rather than a stored date. A yearly reset is simply a different counter each year, so nothing has to notice midnight on the 31st of December and no scheduled job can fail to run.

🔴 't' is a real production key. Any test that writes or deletes counter rows must use a key naming something that cannot exist. A test suite that swept 't' reset a live install's counter beneath 106 renumbered tickets and stopped mail collection. tests/ticket-numbering.php now photographs every non-999999 counter before the run and asserts they are byte-identical afterwards.

A scope nothing in the format distinguishes is a collision

Counting per ticket type gives type 1 and type 2 their own sequences — and then renders both as TICKET-000001. validateFormat($format, $scope) therefore demands {TYPE} for per_type and {COMPANY} for per_company. The preview endpoint, the Save handler and planRenumber() all run it, so what is refused in one place is refused everywhere.

---

6. Rendering, and why $at exists

render(string $format, int $seq, ?PDO $conn, ?int $ticketTypeId,
       ?DateTimeImmutable $at = null, ?int $tenantId = null): string

{######} pads with str_pad, which only ever pads — the width is a floor, never a limit, so ticket 1,000,000 simply gets one character longer. There is no wrap-around case to handle because there is no wrap-around.

$at defaults to now, which is right for a new ticket and wrong for a renumber: a ticket raised in 2024 must not come back stamped INC-2026-…. planRenumber() passes each ticket's own created_datetime, for both the date tokens and the counter key.

{TYPE} and {COMPANY} can both render empty

typeCode() derives three letters from the ticket type's name (Incident → INC) and returns '' for a null or deleted type. companyCode() → codeFor() prefers tenants.ticket_code, then the slug, then three letters of the name.

⚠️ A derived code is a convenience, not a guarantee. "Acme Ltd" and "Acme Group" both derive ACM. codeClashes() groups every active company by effective code and reports any sharing one — the numbering screen calls it when per-company counting is chosen and refuses, naming the companies. cleanCode() normalises input to A–Z0–9, upper case, 12 characters.

preview() substitutes stand-ins (INC, ACME) for those two tokens, because a preview has no ticket and showing somebody -00001 would read as a broken token.

NULL tenant means the default company

resolveTenant(?PDO $conn, ?int $tenantId): ?int

A NULL tenant_id does not mean "no company" — it means the default company. Both spellings exist in the wild: one live install has 16 tickets stored with NULL alongside 84 carrying the default company's real id, written by different code paths years apart. Unresolved, those 16 would draw from a counter of their own and render {COMPANY} as nothing, coming out -00001 beside DEF-00001. Resolved once, at the two places a number is decided (next() and planRenumber()), both halves of the same company share one sequence.

---

7. Recognising a number

inUse(PDO $conn, string $number): bool          // tickets ∪ history
findTicketId(PDO $conn, string $number): ?int   // tickets first, then history

History counts as in use. A number that once belonged to a renumbered ticket must never be handed to a different one, or a reply quoting it would land on a stranger's ticket — which is worse than not matching at all. uq_tnh_number enforces that a retired number can only be recorded once.

findTicketId() is the reverse: the live number first, then the alias table. It is deliberately not scoped by company — the reference in an email subject carries no company, and tickets.ticket_number is uniquely indexed across the whole install for exactly that reason.

---

8. Renumbering

Split in two so the dangerous half can be tested:

planRenumber(PDO $conn, array $cfg, ?array $rows = null): array   // decides, writes nothing
applyRenumber(PDO $conn, array $plan): void                       // writes, decides nothing

api/tickets/numbering_renumber.php is 59 lines: authenticate, plan, and either describe the plan or apply it. The preview and the live run share one code path, so what somebody is shown is what would actually happen.

🧪 The optional $rows is what makes this testable. planRenumber() normally reads every ticket; tests hand it a set of their own, so a bug in a test cannot rewrite the real estate. A tool this destructive proving itself only by being run on real data is not a proof.

What the plan does

Tickets are ordered by created_datetime, id — id alone is nearly the same and not quite, once merges and imports have moved things around. Each ticket gets a sequence per counter key, not one shared sequence:

🔴 A single shared sequence would renumber everything from one run of numbers and then leave every type's counter still sitting at 1, so the next new incident would be handed a number a renumbered ticket already has. Worse than never having renumbered. applyRenumber() winds every key the run touched, in the same transaction.

A ticket already carrying its target number is skipped entirely — renumbering a ticket to the number it already has would still write a history row, and history is what old replies are matched against.

assertPlanSafe — the guard that doesn't depend on foresight

validateFormat() refuses the causes knowable from the settings. assertPlanSafe() refuses what the plan actually contains:

• any number appearing twice in the plan — catches, for instance, a {TYPE} rendering empty because that type was deleted years ago, which no settings check could see;
• any planned number retired by a different ticket — a ticket taking back its own old number is fine (a renumber being undone); taking somebody else's would silently redirect an old email thread.

Either refuses the whole run before a single row is written, naming the number involved.

Applying

One transaction for the entire run — a half-renumbered estate would have two schemes in it and a counter matching neither. Per ticket, history first, then the update: the old number is recorded before it stops being the ticket's own.

---

9. 🧪 Tests

php tests/ticket-numbering.php — 61 assertions. The ones that matter:

Group	Proves
the format engine	padding is a floor; every token; each validation rule including the scope rules
the parser	every format, old and new, still parses — the whole feature rests on this
a retired number	is in use, still resolves, and is skipped by the generator
the company code	precedence, normalisation, and that two similar names do collide
planning	oldest-first, own-year, per-key sequences, skip-if-matching, and all four refusals
applying, for real	on scratch tickets: both numbers resolve, one history row each, the counter winds, twice is a no-op, a stranger's retired number is refused
🔴 isolation	no real counter was touched — before/after comparison of every live counter row

Scratch rows are ZZNUM-prefixed and swept before and after. Writing tests run under company 999999 so their counter key is t:co999999.

---

10. What isn't done

• {TYPE} is empty for emailed and portal tickets, which have no type until an analyst sets one. Per-type counting suits desks where analysts raise tickets choosing a type; it does not suit a mostly-email desk. There is no default ticket type to fall back on — ticket_types has no is_default column.
• The 23 non-English locales. Every string added for this is English-only.
• No API for renumbering. Deliberate: it is a migration tool, not an operation.

---

See also

• Ticket numbering — the user-facing page
• Changing your ticket number format — the migration question
• Merging tickets — Developer Guide — the same alias principle, applied to merges
• Multi-Tenancy — companies, and what NULL means
• Database Verification — Developer Guide — how the two tables get created