Error with workflow, and ticket email flow

[Kraleemil]

Which module is the bug in

Tickets / Workflows (Automations)

Describe the bug

Workflow actions related to ticket notes and ticket-related emails are broken in several ways:

1. The "Add a note to the ticket" workflow action fails with a database integrity error because analyst_id is not being set:

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

2. The default sample workflow that emails a user when a ticket is created is misconfigured (or the mail-reply handling is broken): instead of appending the outgoing/incoming mail as a note or reply on the existing ticket, it creates a brand new ticket each time. This happens both when the "ticket created" notification is sent, and when the end user replies to that email.

To Reproduce

Steps to reproduce the behavior:

1. Go to Workflows/Automations and create (or open the sample) workflow that includes an "Add a note to the ticket" action.
2. Trigger the workflow (e.g., on ticket creation) and check the ticket — the "add a note to the ticket" action fails with the analyst_id SQL error above.
3. Separately, use the default sample workflow that emails the requester when a new ticket is created.
4. Observe that, rather than attaching that notification email as a note/conversation entry on the original ticket, the system generates a new ticket.
5. Reply to that notification email as the end user.
6. Observe that the reply also creates a new ticket instead of being threaded onto the original ticket as a reply.

Expected behavior

• The "Add a note to the ticket" workflow action should complete successfully without requiring/failing on analyst_id; if the action is triggered by the system/workflow (not a specific analyst), it should either allow a null/system value for analyst_id or default to a system/automation user.
• Outbound notification emails generated by a workflow (e.g., "ticket created" confirmation) should be logged as a note/reply on the originating ticket, not spawn a new ticket.
• Incoming replies from the requester to a ticket-related email should be threaded onto the existing ticket (matched via ticket ID/reference in subject or headers), not create a duplicate ticket.

Screenshots

Any errors in console (dev tools)

Not checked/not applicable — the reported error is a backend/PHP SQL error (see above), not a browser console error.

Additional comments

Related feature request (not a bug, but tied to the same area): it would be great to have a compact "preview" entry in the ticket's conversation timeline for the automatic "ticket received" email — similar to TopDesk — rather than either a full note or (as currently happens) a duplicate ticket. This would make the conversation order/history much easier to follow.
Generally TopDesk does it really good, maybe research it a bit.

[edmozley]

Hi Kristian @Kraleemil ,

Thanks for this - two clearly separated problems in one report, which made it much easier to work on. Taking them in turn.

1. The add_ticket_note error - fixed

You were right about the cause, and it was worse than intermittent: that action had never worked, on any installation, since it was written.

The engine writes the history entry with no analyst against it deliberately - that is how an entry is marked as made by automation rather than by a person, and its own code comment says so. The column was NOT NULL. So the intent and the schema contradicted each other and the database won. There was no configuration under which it could have succeeded.

Fixed in both places installations arrive by:

• database/freeitsm.sql for new installations
• api/system/db_verify.php for existing ones

To pick it up:

git pull

then sign in as an administrator and run System - Database Verification. It will report the change in plain English:

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

It is worth running that after every update as a matter of habit - it is non-destructive and backfills anything newer versions expect.

One related thing came out of it: with the column fixed, those entries appeared in the ticket history with an author of "Unknown". That says we do not know when the truth is nobody did it, so the history now names the case - System for an entry written by a workflow, Former analyst for one written by somebody who has since left. No new translations were needed; both labels already existed for the notes list.

Full write-up, including why this only ever broke in workflows and never when adding notes by hand:
https://github.com/edmozley/freeitsm/wiki/Issue-120-Workflow-Notes-Could-Never-Be-Written

2. The email threading - we cannot reproduce it, and could use your help

This one we have not managed to make happen, and rather than guess at a fix we would rather understand your setup first.

What we tested, end to end, on a clean installation:

1. Enabled the Auto-acknowledge a new ticket sample workflow
2. Emailed the service desk from an ordinary external address, creating TICKET-000109
3. Both acknowledgements went out correctly, each with [SDREF:TICKET-000109] in the subject
4. Replied to the workflow's email from the external address
5. The reply threaded onto TICKET-000109 correctly. No duplicate ticket

We also checked the machinery on both sides and it looks sound: the workflow's send action adds the [SDREF:...] marker to the subject, and the inbound matcher reads it back, is deliberately format-agnostic, and even follows merges and renumbers.

So we think something about the environment is different rather than the threading code being broken - and there is one specific thing we suspect. Your first symptom is that the outgoing notification becomes a new ticket. That can only happen if that outgoing email arrives back into the mailbox FreeITSM is polling. We have confirmed there is currently no guard against FreeITSM ingesting mail it sent itself, so if it does come back, nothing stops it becoming a ticket.

Some questions that would let us confirm that and fix it properly:

1. On the ticket where this happens, what is the requester's email address? Specifically, is it the same address as the mailbox FreeITSM polls? Raising a test ticket from the service desk's own account would produce exactly what you describe.
2. Which folder is that mailbox configured to collect from? Is there any chance Sent items are included?
3. Is there any forwarding, redirect or journaling rule on that mailbox that would copy outgoing mail back into the inbox? A "keep a copy in Inbox" rule would do it.
4. Which provider is the mailbox - Microsoft 365, Google, or basic IMAP?
5. When the duplicate ticket appears, what is its subject line, exactly? In particular, is [SDREF:...] present in it, and does the reference in it match the original ticket?
6. Who is the requester on the duplicate ticket - your end user, or the service desk address?
7. What ticket number format is the installation using (System - Tickets - Numbering)?

Question 5 is the most useful single one. If the duplicate's subject still carries a valid [SDREF:...] then the matcher is failing and that is our bug; if the marker is missing or malformed then something is stripping or rewriting it in transit, which points somewhere else entirely.

Screenshots of the duplicate ticket and of the mailbox configuration would help too, if you are able to share them.

On the TopDesk-style compact preview entry in the timeline - noted, and we agree it is a good idea. We have kept it separate from this issue so the bug work is not held up by it.

Thanks again for the report.

Ed

[Kraleemil]

Hi Ed,

Thanks for the analyst_id fix, and a clear explanation.

On the threading side, correction from what I said yesterday: I retested replying to the workflow's acknowledgement email, and that actually threaded onto the ticket correctly. So that part works as you described.

Two things I'm still seeing, separate from that:

1. Duplicate ticket when replying to my own original mail (not the acknowledgement, the very first email that created the ticket).
   SDREF is not present since it's my own mail, and i am using format T{YY}{MM}-nnnnn, i am using IMAP/SMTP

2. Replies go to whoever sent the most recent message on a thread, not the original requester. E.g. requester A opens a ticket, requester B replies on the same thread, my reply goes to B instead of A.

On 2 - TopDesk handles this by keeping a Requester field on the ticket itself, separate from the mail thread. Replying from the ticket always goes to that requester by default; you add extra people with ";extra@email.com" rather than it defaulting to the last sender. Thought it might be useful alongside the compact-preview idea you already flagged.
The TopDesk screenshot shows the left side with info about the ticket, like who is the Operator is and the opertator group, Priority, Categories.

Thanks,
Kristian