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.

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

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.

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.

next(PDO $conn, ?int $ticketTypeId, ?int $tenantId) is the single entry point. Every path that opens a ticket calls it, and must pass what it knows:

⚠️ 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 caller, pass the company.

🔴 Until 3.1.0 the last five rows weren't callers at all. Each made its own random XXX-NNN-NNNNN, so a chat, form, merge, split or workflow ticket ignored the configured format. That stayed invisible on any install still on the random default. See Chat tickets ignored your ticket numbering. The only generator left outside the engine is the demo-data importer, on purpose: demo tickets mustn't use up the real counter.

$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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

• {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.

• 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