Issue 120 Workflow Notes Could Never Be Written

The workflow "Add a note to the ticket" action could never have worked

Reported: #120 by Kraleemil, August 2026 · Module: Workflows · Fixed in: #1391

---

What you saw

A workflow with an Add a note to the ticket action fails on every run, and the run history shows:

Action 1 (add_ticket_note): SQLSTATE[23000]: Integrity constraint violation:
1048 Column 'analyst_id' cannot be null

Not intermittently, and not on particular tickets. Every time, on every installation, since the action was written.

---

What was actually wrong

The engine writes the history entry with no analyst against it, on purpose, and its own comment says so:

// Use the same `ticket_audit` table the rest of the app writes to
// for analyst-visible activity. analyst_id is null to flag this as
// a workflow-engine-driven note (so the UI can render it differently
// if it wants to).
$conn->prepare(
    "INSERT INTO ticket_audit (ticket_id, analyst_id, field_name, old_value, new_value, created_datetime)
     VALUES (?, NULL, 'Workflow Note', NULL, ?, UTC_TIMESTAMP())"
)->execute([$ticketId, $note]);

That is a reasonable design: a history entry made by automation genuinely has no person behind it, and marking it as such lets the screen say so.

The column was NOT NULL.

`analyst_id`  INT NOT NULL,

So the intent and the schema contradicted each other, and the database won. There was no configuration under which this action could have succeeded.

🔑 The comment is what makes this worth reading. It is not a mistake in the sense of a typo or an oversight in the logic — the author decided what NULL should mean here and wrote it down. What never happened was checking that the column agreed.

---

Why it only ever broke in workflows

The natural first thought is that it must be about tickets with nobody assigned. It is not, and the distinction matters:

column	means
ticket_audit.analyst_id	who performed the action
tickets.assigned_analyst_id	who the ticket is assigned to — and already NULL-able

An unassigned ticket was never relevant. What matters is whether there is somebody doing the thing.

All eleven writers of ticket_audit were checked:

• nine take a signed-in person from the session. api/tickets/log_ticket_audit.php refuses the request outright if there is no session, so from the UI the column is populated by definition — you cannot reach the endpoint without being signed in;
• includes/calendar_sync/pull.php runs from cron, but attributes the change to the analyst whose calendar entry moved, which is a real person;
• workflow/includes/engine.php is the only writer with no actor at all, because a workflow fires from a trigger with no session behind it.

So this was only ever going to surface in one place, and adding notes by hand — including to unassigned tickets — was always fine.

---

The fix

The column is relaxed to allow it:

`analyst_id`  INT NULL,

Safe in a way that tightening never is: every existing row already has an analyst, so a looser rule cannot invalidate one. The fk_ticket_audit_analyst foreign key is unaffected — a NULL never violates a foreign key, it simply has nothing to check. Both screens that read ticket history already LEFT JOIN the analysts table, so an entry without one was always going to appear.

Two places, because installations arrive by two routes:

• database/freeitsm.sql — new installations
• api/system/db_verify.php — existing ones, following the five probe-then-MODIFY precedents already in that file

Existing installations pick this up by running System → Database Verification, which reports it in plain English:

analyst_id: NOT NULL -> NULL (a workflow writes history entries that no analyst made)

…and it said "Unknown"

With the column fixed, the entry appears in the ticket's history with a blank author, and the screen rendered that as Unknown.

"Unknown" says we do not know when the truth is nobody did it — and it hides the two cases worth telling apart: an entry written by automation, and one written by somebody who has since left the organisation. The notes list already makes exactly this three-way split, so the history now makes it too:

case	shown as
a real person	their name
analyst_id IS NULL — a workflow wrote it	System
a real id with no row left — they have left	Former analyst

No new translation strings: both labels already existed for the notes list and are already translated into all 24 languages.

---

📁 Files changed

File	What
🟥 database/freeitsm.sql	ticket_audit.analyst_id becomes NULL-able
🟥 api/system/db_verify.php	the same change for existing installations, reported in plain English
🟨 api/tickets/get_ticket_audit.php	says which of analyst / system / former an entry is
🟩 assets/js/inbox.js · assets/js/mobile.js	print that, instead of "Unknown"

---

How it was verified

The reporter's condition was recreated on a development database and the whole path driven, with every insert inside a transaction that was rolled back, so nothing persisted:

1. tighten the column to NOT NULL     -> nullable = NO
2. run the action's exact INSERT      -> FAILED: SQLSTATE[23000] ... 'analyst_id' cannot be null
3. run Database Verification          -> REPORTED: analyst_id: NOT NULL -> NULL
4.                                       nullable = YES
5. run the action's exact INSERT      -> SUCCEEDED
6. NULL rows left behind: 0    total rows: 160  (unchanged)

Step 2 is the one that matters: it reproduces the reported error byte for byte before the fix, which is what makes step 5 mean something. Row counts identical at the end, so the check left nothing behind.

---

What this means for you

If you have a workflow with an Add a note to the ticket action, it will start working once you have pulled the update and run System → Database Verification. Entries it writes appear in the ticket's History tab, attributed to System.

Nothing changes for entries made by people, and nothing needs re-running for history already recorded.

---

⚠️ What this does not cover

Issue #120 also reports that workflow notification emails create new tickets rather than threading onto the existing one. That is a separate problem and is not fixed by this change — it is still being investigated, and so far has not been reproducible on a correctly configured installation. See the issue for the current state.

---

Related

• Workflows
• Database Integrity — why Database Verification is worth running after every update
• Bugs resolved