Ticket Views Developer Guide - edmozley/freeitsm GitHub Wiki

Ticket views — developer guide

Discussion #62, step 2 of 3 · Ships in 2.8.0 · User page: Who has seen a ticket

Records who opens a ticket, so that when portal managers read their people's tickets (step 3), those people can see when they did.

Storage: its own table, one row per viewer per day

CREATE TABLE IF NOT EXISTS `ticket_views` (
    `id`                    INT NOT NULL AUTO_INCREMENT,
    `ticket_id`             INT NOT NULL,
    `viewer_type`           VARCHAR(10) NOT NULL,   -- 'analyst' or 'user' (portal)
    `viewer_id`             INT NOT NULL,
    `view_date`             DATE NOT NULL,
    `first_viewed_datetime` DATETIME NOT NULL,
    `last_viewed_datetime`  DATETIME NOT NULL,
    `view_count`            INT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_ticket_views_day` (`ticket_id`, `viewer_type`, `viewer_id`, `view_date`),
    KEY `ix_ticket_views_ticket` (`ticket_id`, `last_viewed_datetime`)
) ...

Why not ticket_audit: its actor column is analyst_id, and a manager reading in the portal is a portal user, not an analyst. Views are also many times more frequent than changes. Twenty opens in a morning should cost one row, not twenty.

🔴 The unique key is what makes the write an upsert. ticketViewRecord() is INSERT … ON DUPLICATE KEY UPDATE last_viewed_datetime = …, view_count = view_count + 1. Database Verification creates tables from a column list with no constraints, so on a verify-built install the key comes only from includes/db_verify_indexes.php. That list is generated from freeitsm.sql by scripts/gen_db_verify_indexes.php. Without the key, every view would add a row. It was checked on a verify-built table: three opens made one row with a count of 3.

Recording

includes/ticket_views.php:

Analyst views: api/tickets/get_email_detail.php, the endpoint that opens a ticket in the inbox (also used by direct ticket links and the phone layout). The call sits after every access check, so a refused request never counts as a view. ticketViewRecord() never throws, so a failed write can't stop a ticket from opening.
Portal views: ticketViewRecord($conn, $ticketId, 'user', $userId), called by api/self-service/get_ticket_detail.php when portalTicketAccess() answers role = manager - and not for a confidential stub (stub = true): the manager saw nothing of it, and telling the requester their manager "has seen" it would alarm the person the stub protects. The requester's own views aren't recorded - they aren't worth showing anybody.

The day is the UTC day (UTC_DATE()), like every other stored time.

Reading

Who	Function	Shows
Analysts: the Audit window	`ticketViewsAsAuditRows()`, merged in `api/tickets/get_ticket_audit.php`	every view: desk and portal
The requester: Who has seen this ticket	`ticketViewsForRequester()`, returned as `seen_by` by `api/self-service/get_ticket_detail.php`	portal viewers other than the requester, never the desk

Views are shaped as audit rows (field_name: 'Viewed', the viewer's name in analyst_name) so that both the desktop Audit window (inbox.js) and the phone one (mobile.js) show them with no change to either. The count goes in new_value as words. The time stays in created_datetime (the last view that day), which the browser formats in the reader's own time zone. A time written into the text would have been UTC.

Portal viewers get (self-service portal) after their name, and author_kind: 'portal'.

Why the desk is left out for the requester (Ed's call): "Viewed by the service desk, Monday 09:02" invites "why did you look and not do anything about it".

seen_by is null, not [], before Database Verification, so the portal shows no panel. An empty list would render "Nobody outside the service desk has looked at it", a reassurance with nothing behind it yet.

🔴 `seen_by` goes to the requester only

Done in step 3: get_ticket_detail.php returns seen_by only when the viewer is the requester, never to a manager. A manager must not see who else has looked, and must not find their own name listed as if they were the requester.

How it was tested

On docker/proxy-test (throwaway data):

Before Verification: tickets open, the audit loads, and the portal gets seen_by: null.
Verification built the table and uq_ticket_views_day.
Three analyst opens made one row with view_count 3.
With simulated portal views (a second requester standing in for a manager, and the requester themselves):
- the Audit window listed all three, portal viewers marked;
- the requester's seen_by held only the other person.
Both screens driven in headless Chrome:
- the panel with a viewer, and with nobody;
- the Audit window with "Viewed" rows among the changes, including a view recorded live by the harness opening the ticket.

Real manager views were tested in step 3 - see Portal managers — Developer Guide.