Configurable Lookups Developer Guide - edmozley/freeitsm GitHub Wiki

Never identify a lookup row by its name โ€” Developer Guide

FreeITSM lets administrators rename almost every lookup value: ticket statuses, priorities, task and change statuses, service impact levels, morning-check statuses, ticket origins. Every one of those tables has a name column that exists to be edited.

Code that asks "give me the row called 'Open'" is therefore asking a question the database is entitled to answer with nothing โ€” and a lookup that matches nothing counts nothing, silently, as a confident zero.

This page collects the pattern, the places it was found, and what to write instead.

This is not a hypothetical. Between #70 and #79 the same fault has been found in more than thirty places across five modules โ€” and three of them were wrong in English, on a stock installation, with nothing renamed.

1. Why it fails silently

Three separate mechanisms turn a missed name into a plausible number rather than an error:

Shape What a miss produces
(SELECT id FROM t WHERE name = 'Open') in an INSERT NULL written to a nullable column โ€” no error
WHERE name IN ('Urgent','High') in a COUNT 0 โ€” indistinguishable from "none right now"
data.statuses['Fail'] || 0 in JavaScript 0 โ€” the || swallows undefined

None of them throws. All of them produce a number that looks like an answer. That is what makes this class worth a page: the failure mode is not a crash, it is quiet, confident wrongness, and it can persist for months.

The worst example found so far is not subtle. Watchtower's Morning Checks card counted statuses named 'OK', 'Warning' and 'Fail'. FreeITSM has never shipped those names โ€” the statuses are Green, Amber and Red. So all three counts were permanently zero, the "checks failed" alert could never fire, and the green "all checks completed and passing" line โ€” which was conditioned on those same empty counts โ€” appeared on a morning when every check was red.

2. What to use instead

Every lookup table carries flags that exist precisely so code does not have to know names.

You want Ask for Not
The status a new record starts in is_default = 1 name = 'Open'
Anything finished is_closed = 1 name IN ('Closed','Resolved')
Anything still live is_closed = 0 name NOT IN ('Closed','Cancelled')
A serious priority ranked above the default name IN ('Urgent','High','Critical')
A record not yet submitted still in the default status name = 'Draft'
Time that counts against uptime counts_as_downtime = 1 name = 'Major Outage'
A colour for a badge the row's colour a CSS class picked by name

The two derivations worth knowing

Some questions have no flag, and can still be answered from data rather than words.

"High priority" = ranked above the default priority.

tp.display_order > COALESCE(
    (SELECT display_order FROM ticket_priorities WHERE is_default = 1 LIMIT 1),
    (SELECT MIN(display_order) FROM ticket_priorities))

On stock data this selects exactly High, Critical and Urgent โ€” the same three the hardcoded list named, and the same number. But it survives renaming, and a priority somebody adds above Normal is now included, instead of being silently left out of the very tile meant to catch it.

"Not yet submitted" = still in the status it started in.

is_default marks the starting status โ€” Draft for changes. So cs.is_default = 0 separates submitted and waiting on somebody from still being written, without naming a status.

The pattern for a default with no anchor

SELECT id FROM ticket_statuses
 WHERE is_active = 1
 ORDER BY is_default DESC, display_order, id
 LIMIT 1
  • is_active filters โ€” a deactivated status is absent from the dropdown, so starting records in one reproduces the original symptom by another route.
  • is_closed is deliberately not filtered โ€” an administrator who makes a closed status the default has said what they meant.
  • It can only return nothing if the table is empty.

3. When there is genuinely no fact to read

Sometimes the question really is a judgement nothing records. Which morning-check status counts as a failure? Nothing in FreeITSM knows. morningChecks_Statuses carries a label, a colour, a sort order and an active flag โ€” and no notion of pass or fail.

Two rules for that case:

Claim only what you know. The card now says "all checks completed", never "all passing", and shows green only when every check sits in the most favourable status defined. Saying less is better than saying something confident and wrong.

Give the judgement a home, and make it optional. Watchtower Settings lets an administrator name the statuses that need attention. Crucially, the default is still correct without it โ€” see ยง5.

๐Ÿ”ด Do not borrow a domain fact to make a view decision

The most instructive mistake in this whole sweep was made while fixing it.

Having removed the hardcoded 'Major Outage' test, the red-versus-amber decision was pointed at the impact level's counts_as_downtime flag. That reads perfectly well โ€” until somebody wants a degraded service drawn amber, because that flag decides their uptime percentages. The advice written at the time was "turn it off if you don't want degraded showing red", which would have had an administrator falsify their uptime reporting to change a dashboard colour.

counts_as_downtime answers "does time at this level count against uptime". It does not answer "should the dashboard shout". They are different questions and now have different answers, stored separately.

Test for it: if changing a display preference would require editing a value that some other module reports on, the display preference needs its own storage.

4. Where it has been found

Watchtower โ€” 18 places

Card Was Now
Tickets headline = the three statuses named Open, In Progress, On Hold every open status; the total was reading 88 against a true 100
Tickets tp.name IN ('Urgent','High','Critical') ranked above the default priority
Changes cs.name NOT IN ('Closed','Cancelled') is_closed = 0. There has never been a change status called Closed โ€” of four finished statuses that list caught one, so completed and failed changes counted as upcoming work
Changes cs.name IN ('Submitted','Pending Approval') approval_datetime IS NULL and not still in the default status
Changes cs.name = 'In Progress' the scheduled work window, which already says exactly that
Tasks ts.name = 'In Progress' / 'To Do' every open task status; Blocked was invisible
Morning Checks statuses['OK' | 'Warning' | 'Fail'] โ€” names that never existed one figure per status, from morningChecks_Statuses
Service Status 'Major Outage' || 'Partial Outage' decided red vs amber the levels marked serious; a renamed total outage was drawn amber
Service Status three names picked one of four badge styles the level's own colour
Browser extension statuses['Fail'], and tickets/tasks summed by name shared server-side counts

Earlier, in Service Status โ€” #70

Renaming Operational split the status board in two, because a healthy service has no stored impact level โ€” it is derived, and six places derived it by writing the word Operational into the answer.

Tickets โ€” #79

Seven creation paths resolved the starting status by the name Open.

5. ๐Ÿ”ด Correctness must not depend on configuration

When the Watchtower faults were found, the obvious fix was a settings screen: let the administrator tick which statuses each figure should count. That was built โ€” but second, and deliberately.

If ticking boxes is what makes the numbers right, then:

  • every existing installation stays wrong until somebody finds the screen;
  • a fresh installation is wrong until somebody configures it;
  • and a dashboard showing nothing reads as "nothing needs attention", which is the most dangerous thing it can say untruthfully.

So the queries were made correct with no configuration at all โ€” count every status, read every flag โ€” and the settings screen exists only to trim what is already true. Leave it alone and the numbers are right.

This shows up in the storage as watchtower_items.is_customised, which separates "not configured" from "configured to show nothing". Without that column an empty selection is indistinguishable from an untouched one, and would silently mean "all".

6. How to check your own code

Three greps, in order of how much they find.

# 1. SQL comparing a user-editable name to a literal
grep -rnE "(name|Label|Status) *(=|IN|<>|!=) *\(?'" --include=*.php .

# 2. JavaScript comparing to a capitalised literal, or indexing by one
grep -rnE "(===|!==|==) *'[A-Z]|\['[A-Z][a-z]" --include=*.js --include=*.php .

# 3. The known vocabulary, anywhere
grep -rnE "'(Open|Closed|In Progress|On Hold|To Do|Done|Blocked|Normal|High|Urgent|Critical|Fail|Warning|OK|Green|Amber|Red|Major Outage|Partial Outage|Operational|Draft|Submitted|Approved|Cancelled|Completed)'" --include=*.php --include=*.js .

Grep 3 will also match comments and seed data โ€” read the hits, do not count them.

Two exceptions that are not this fault

  • App-written keys. ticket_audit.field_name = 'status' is an English literal, but the audit log writes it as a fixed internal key that no administrator can edit and nothing translates. Same for direction = 'Inbound' and workflow_executions.status = 'failed'.
  • Seed data. INSERT IGNORE INTO ticket_statuses โ€ฆ ('Open', โ€ฆ) is where the names come from. Creating them by name is fine; finding them by name is not.

The distinction is simple: does an administrator have a screen where they can change this value? If yes, never match on it.

7. How to prove a fix

The check that actually settles it is a whole-payload diff across a rename.

  1. Capture the endpoint's output.
  2. Rename every configurable lookup โ€” UPDATE ticket_statuses SET name = CONCAT('DE-', name) and the same for priorities, task and change statuses, impact levels, morning-check labels, origins.
  3. Capture it again.
  4. Diff every numeric and boolean field. All must be identical.
  5. Confirm the labels did change โ€” otherwise the test proves nothing except that you read no names at all, including the ones you should be displaying.

Applied to Watchtower this compared 77 numeric and boolean fields, all identical, while Open โ†’ DE-Open, Green โ†’ DE-Green and High/Critical/Urgent โ†’ DE-High/DE-Critical/DE-Urgent all followed the rename.

Step 5 is the control. Without it a page that renders nothing at all passes.

Related pages