Live Updates Developer Guide

🛠️ Live updates - Developer Guide (1 of 2): the design and the server

How a FreeITSM page keeps up with changes other people make. Built for #267 (5.0.0). User guide: Live updates.

• Page 1 (this page): why it is built this way, the files, one beat end to end, the endpoint, every signature with its PHP and SQL, the indexes, cost, server-side traps, the PHP test.
• Page 2: live-pulse.js, the fetch wrapper and "mine", each screen's wiring and busy rules in JavaScript, the browser checks, and adding live updates to another screen.

The one-line version: every analyst page loads assets/js/live-pulse.js. Parts of the page watch() the topics they show. Every 15 seconds, while the tab is visible, the page sends one GET api/system/pulse.php?topics=…. The server answers with an md5 per topic, built from a few "newest change" values (each one an indexed query). When a hash moves, that part's callback runs, and the callback decides whether it may redraw now or must show the "Updated · Refresh" prompt.

---

1. Why it is built this way

Before 5.0.0 each part of the app kept itself current in its own way, or not at all:

Part	Before	Problem
Ticket list	60 s loop in tickets/index.php; refreshed when get_inbox_activity.php's newest message id moved	a colleague changing status, priority, owner or team never moved it
Open email ticket	nothing	stale until you clicked away and back
Open chat ticket	its own 15 s setInterval in inbox.js	a second loop, thread only
Tasks board, open task	nothing	stale until reload
Bell	its own 60 s loop	up to a minute late

One more loop per screen would have made it worse. So instead there is one mechanism that sends one request per page per beat, stops entirely in a background tab, and leaves the "never wipe work in progress" decision with the part that owns the screen, using one shared prompt.

Why polling, not websockets. FreeITSM installs are plain PHP + MySQL behind Apache or nginx, often on shared or locked-down hosting. A socket server is a second long-running process to install, secure, keep alive and proxy. Short polling needs nothing new. At 15 seconds and one cheap request per visible tab, the load is negligible (section 7).

Why hashes of "newest change" values, not a change log. A log table written from WorkflowEngine::dispatch() would say exactly what changed. It was rejected because:

• Not every write dispatches an event. 24 files update tickets and 13 update tasks, and some paths announce nothing (#266 was exactly that).
• The values already exist. Almost every write stamps updated_datetime or adds a message, note or history row. Reading the newest of those catches writes from any path, including ones written before this feature existed.
• Nothing to grow, prune or index, and nothing extra on the write path.

The cost of this choice is the first trap in section 8.

---

2. The files

File	🎨	Role
includes/live_pulse.php	⚙️	livePulseSignatures(), one function per topic, livePulseOne(), livePulseSeconds(), LIVE_PULSE_MAX_TOPICS
api/system/pulse.php	🔌	the endpoint (section 4)
includes/waffle-menu.php renderNotificationBell()	🖥️	prints window.FREEITSM_LIVE and loads live-pulse.js before the bell, on every analyst page
assets/js/live-pulse.js	🖥️	window.LivePulse - page 2
tickets/index.php, assets/js/inbox.js, assets/js/ticket-workspace.js	🖥️	the ticket list and the open ticket - page 2
assets/js/tasks.js	🖥️	the board and the open task - page 2
assets/js/notification-bell.js	🖥️	the bell - page 2
system/preferences/index.php, includes/preference_templates.php	🖥️	the live_updates preference
database/freeitsm.sql, includes/db_verify_indexes.php (generated)	🗄️	two new indexes (section 6)
tests/live-pulse.php	🧪	section 9

---

3. One beat, end to end

 tickets page (visible tab)                           server
 ─────────────────────────                            ──────
 ticket list     watch('tickets')        ─┐
 open ticket     watch('ticket:1451')     ├─ every 15 s ──►  GET api/system/pulse.php
 bell            watch('notifications')  ─┘                   ?topics=tickets,ticket:1451,notifications
                                                                  │
                                          livePulseSignatures()   │  per topic: module gate,
                                                                  │  company scope, a few
                                                                  ▼  indexed newest-row reads
                 ◄──────────────────── { sig: { tickets: "9f…", "ticket:1451": "c2…", notifications: "41…" } }
 for each topic:
   first answer      → baseline, nothing runs
   same hash         → nothing
   hash moved        → callback(topic, { mine })
   null              → stored, nothing runs (gone / not allowed)

The page side - when beats happen, the debounce, "mine", other tabs - is page 2 §1.

How the page learns its settings

renderNotificationBell() runs on every analyst page (the portal has its own bell and no live updates). It reads the analyst's preference and prints it before loading the script, so the bell that follows can watch:

// includes/waffle-menu.php - renderNotificationBell()
$url = defined('BASE_URL') ? BASE_URL : $path_prefix;
require_once __DIR__ . '/live_pulse.php';
$liveSeconds = 15;
try { $liveSeconds = livePulseSeconds(connectToDatabase(), (int)$_SESSION['analyst_id']); } catch (Throwable $e) { /* the default */ }
echo '<script>window.FREEITSM_LIVE = ' . json_encode(['seconds' => $liveSeconds, 'url' => $url], JSON_HEX_TAG | JSON_UNESCAPED_SLASHES) . ';</script>'
   . '<script src="' . htmlspecialchars($url) . 'assets/js/live-pulse.js?v=3"></script>';
notificationBellRender($url, [ /* … */ ]);

// includes/live_pulse.php - 15 (default), 30, 60 or 0 = off. Anything else reads as the default.
function livePulseSeconds(PDO $conn, int $analystId): int
{
    try {
        $st = $conn->prepare("SELECT preference_value FROM user_preferences WHERE analyst_id = ? AND preference_key = 'live_updates'");
        $st->execute([$analystId]);
        $v = $st->fetchColumn();
    } catch (Throwable) {
        $v = false;
    }
    return in_array((string)$v, ['15', '30', '60', '0'], true) ? (int)$v : 15;
}

BASE_URL, not $path_prefix: on a pretty URL served deeper than its file, the filesystem prefix points one folder too high (the same lesson the bell learned).

---

4. The endpoint

GET api/system/pulse.php?topics=tickets,ticket:42,tasks,task:7,notifications
→ 200 { "success": true, "sig": { "tickets": "9f3a…", "ticket:42": "c2d1…", "tasks": "…", "task:7": null, "notifications": "…" } }
→ 401 { "success": false, "error": "Not authenticated" }

// api/system/pulse.php
session_start(['read_and_close' => true]);   // a beat never holds the session lock against the page's real requests
require_once '../../config.php';
require_once '../../includes/functions.php';
require_once '../../includes/tenancy.php';
require_once '../../includes/live_pulse.php';

header('Content-Type: application/json');
header('Cache-Control: no-store');            // no proxy may answer with an old hash

if (!isset($_SESSION['analyst_id'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'error' => 'Not authenticated']);
    exit;
}
try {
    $topics = array_filter(array_map('trim', explode(',', (string)($_GET['topics'] ?? ''))));
    $conn = connectToDatabase();
    echo json_encode(['success' => true, 'sig' => (object)livePulseSignatures($conn, (int)$_SESSION['analyst_id'], $topics)]);
} catch (Throwable $e) {
    error_log('[api/system/pulse] ' . $e->getMessage());
    echo json_encode(['success' => false, 'error' => 'Could not check for changes.']);
}

It is a GET and writes nothing, so tests/get-must-not-write.php passes it as it is. (object) keeps an empty answer as {} rather than [].

The dispatcher

// includes/live_pulse.php
const LIVE_PULSE_MAX_TOPICS = 12;

function livePulseSignatures(PDO $conn, int $analystId, array $topics): array
{
    $out = [];
    foreach (array_slice(array_values(array_unique($topics)), 0, LIVE_PULSE_MAX_TOPICS) as $topic) {
        $topic = (string)$topic;
        try {
            if ($topic === 'tickets')            $out[$topic] = livePulseTickets($conn, $analystId);
            elseif ($topic === 'tasks')          $out[$topic] = livePulseTasks($conn, $analystId);
            elseif ($topic === 'notifications')  $out[$topic] = livePulseNotifications($conn, $analystId);
            elseif (preg_match('/^ticket:(\d+)$/', $topic, $m)) $out[$topic] = livePulseTicket($conn, $analystId, (int)$m[1]);
            elseif (preg_match('/^task:(\d+)$/', $topic, $m))   $out[$topic] = livePulseTask($conn, $analystId, (int)$m[1]);
        } catch (Throwable $e) {
            error_log('[live_pulse] ' . $topic . ': ' . $e->getMessage());
            $out[$topic] = null;   // a part-upgraded install: that part simply does not refresh
        }
    }
    return $out;
}

/** One value: the first column of the first row, or ''. */
function livePulseOne(PDO $conn, string $sql, array $params): string
{
    $st = $conn->prepare($sql);
    $st->execute($params);
    $v = $st->fetchColumn();
    return $v === false || $v === null ? '' : (string)$v;
}

• Unknown topics are left out, not answered, so a typo in a page's topic is visible as "never fires".
• At most 12 topics a request. A page shows a handful, so this only matters against a crafted request.
• An exception answers null for that topic only, so one missing table doesn't take every part of the page down with it.

---

5. The signatures, with their SQL

Every value is one query that walks an index from the newest row and stops at the first row the analyst may see (ORDER BY … DESC LIMIT 1), or a MAX() / COUNT() on an indexed column. The values are joined with | and md5'd with the topic name. Only the hash leaves the server.

tickets - the ticket list

function livePulseTickets(PDO $conn, int $analystId): ?string
{
    if (!analystCanAccessModule($conn, $analystId, 'tickets')) return null;
    [$f, $p] = ticketTenantFilter($conn, $analystId, 't');
    $parts = [
        livePulseOne($conn, "SELECT t.updated_datetime FROM tickets t WHERE 1 = 1{$f} ORDER BY t.updated_datetime DESC LIMIT 1", $p),
        livePulseOne($conn, "SELECT t.id FROM tickets t WHERE 1 = 1{$f} ORDER BY t.id DESC LIMIT 1", $p),
        livePulseOne($conn, "SELECT e.id FROM emails e JOIN tickets t ON t.id = e.ticket_id WHERE 1 = 1{$f} ORDER BY e.id DESC LIMIT 1", $p),
        livePulseOne($conn, "SELECT n.id FROM ticket_notes n JOIN tickets t ON t.id = n.ticket_id WHERE 1 = 1{$f} ORDER BY n.id DESC LIMIT 1", $p),
        livePulseOne($conn, "SELECT a.id FROM ticket_audit a JOIN tickets t ON t.id = a.ticket_id WHERE 1 = 1{$f} ORDER BY a.id DESC LIMIT 1", $p),
        livePulseOne($conn, "SELECT COUNT(*) FROM tickets t WHERE t.deleted_datetime IS NOT NULL{$f}", $p),
    ];
    return md5('tickets|' . implode('|', $parts));
}

Value	Catches
newest updated_datetime	status, priority, owner, team, subject - every write that stamps the row, including workflow actions that write it directly (set_ticket_status)
newest ticket id	a new ticket
newest emails.id	a reply, a chat message, a collected email
newest ticket_notes.id	a note
newest ticket_audit.id	a change that is recorded but doesn't stamp the row; also breaks ties between two writes in the same second
trash count	binning or restoring through the REST API, which writes no history row and doesn't stamp the row (section 8)

ticketTenantFilter() is the inbox's own company scope, including the "All companies" view, so the hash moves for exactly the tickets the list would show. Deleted rows are not filtered out of the updated_datetime read, so binning a ticket also moves it. On a single-company install $f is '' and WHERE 1 = 1 keeps the SQL valid.

ticket:N - the open ticket

function livePulseTicket(PDO $conn, int $analystId, int $ticketId): ?string
{
    if ($ticketId <= 0 || !analystCanAccessModule($conn, $analystId, 'tickets')) return null;
    $row = $conn->prepare("SELECT updated_datetime, deleted_datetime FROM tickets WHERE id = ?");
    $row->execute([$ticketId]);
    $t = $row->fetch(PDO::FETCH_ASSOC);
    // analystCanAccessTicket() is a company check, true for any id on a single-company
    // install - so the row is read first (found in #277).
    if (!$t || !analystCanAccessTicket($conn, $analystId, $ticketId)) return null;
    $parts = [
        (string)$t['updated_datetime'], (string)$t['deleted_datetime'],
        livePulseOne($conn, "SELECT MAX(id) FROM emails WHERE ticket_id = ?", [$ticketId]),
        livePulseOne($conn, "SELECT MAX(id) FROM ticket_notes WHERE ticket_id = ?", [$ticketId]),
        livePulseOne($conn, "SELECT MAX(id) FROM ticket_audit WHERE ticket_id = ?", [$ticketId]),
    ];
    return md5("ticket:$ticketId|" . implode('|', $parts));
}

A ticket that doesn't exist and a ticket the analyst can't open both answer null. The two cases are deliberately indistinguishable, so the endpoint can't be used to probe for ids.

tasks and task:N

function livePulseTasks(PDO $conn, int $analystId): ?string
{
    if (!analystCanAccessModule($conn, $analystId, 'tasks')) return null;
    [$f, $p] = activeTenantReadFilter($conn, $analystId, 't');   // the board's own scope (api/tasks/list.php)
    $parts = [
        livePulseOne($conn, "SELECT t.updated_datetime FROM tasks t WHERE 1 = 1{$f} ORDER BY t.updated_datetime DESC LIMIT 1", $p),
        livePulseOne($conn, "SELECT COUNT(*) FROM tasks t WHERE 1 = 1{$f}", $p),   // a delete moves nothing else
        livePulseOne($conn, "SELECT e.id FROM task_events e JOIN tasks t ON t.id = e.task_id WHERE 1 = 1{$f} ORDER BY e.id DESC LIMIT 1", $p),
    ];
    return md5('tasks|' . implode('|', $parts));
}

function livePulseTask(PDO $conn, int $analystId, int $taskId): ?string
{
    if ($taskId <= 0 || !analystCanAccessModule($conn, $analystId, 'tasks')) return null;
    $row = $conn->prepare("SELECT updated_datetime FROM tasks WHERE id = ?");
    $row->execute([$taskId]);
    $updated = $row->fetchColumn();
    if ($updated === false || !analystCanAccessTask($conn, $analystId, $taskId)) return null;
    $parts = [
        (string)$updated,
        livePulseOne($conn, "SELECT MAX(id) FROM task_comments WHERE task_id = ?", [$taskId]),
        livePulseOne($conn, "SELECT MAX(id) FROM task_events WHERE task_id = ?", [$taskId]),
        livePulseOne($conn, "SELECT MAX(id) FROM task_audit WHERE task_id = ?", [$taskId]),
        livePulseOne($conn, "SELECT COUNT(*) FROM tasks WHERE parent_task_id = ?", [$taskId]),   // a subtask added or removed
    ];
    return md5("task:$taskId|" . implode('|', $parts));
}

• The task count is there because deleting a task leaves the newest updated_datetime where it was.
• The board's drag (api/tasks/reorder.php) stamps updated_datetime, and since #266 it also writes task_events.

notifications - the bell

function livePulseNotifications(PDO $conn, int $analystId): ?string
{
    $parts = [
        livePulseOne($conn, "SELECT COUNT(*) FROM notifications WHERE analyst_id = ? AND read_datetime IS NULL", [$analystId]),
        livePulseOne($conn, "SELECT MAX(updated_datetime) FROM notifications WHERE analyst_id = ? AND read_datetime IS NULL", [$analystId]),
    ];
    return md5("notifications|$analystId|" . implode('|', $parts));
}

Both reads use ix_notif_unread (analyst_id, read_datetime, updated_datetime). updated_datetime is there because the router coalesces a burst of events on one record into one unread row and bumps its time, rather than adding a row (the unread count wouldn't move).

---

6. The indexes

Two "newest updated_datetime" walks had no index to walk, so they would have been scans:

-- database/freeitsm.sql, in CREATE TABLE `tickets`
    -- GH #267: live updates read the newest change.
    KEY `ix_tickets_updated_datetime` (`updated_datetime`),

-- in CREATE TABLE `tasks`
    -- GH #267: live updates read the newest change.
    KEY `ix_tasks_updated_datetime` (`updated_datetime`),

php scripts/gen_db_verify_indexes.php          # writes includes/db_verify_indexes.php (650 indexes)
php scripts/gen_db_verify_indexes.php --check  # CI: the generated list matches freeitsm.sql

Database Verify creates them on existing installs. The other reads use indexes that already existed: the primary keys of emails, ticket_notes, ticket_audit and task_events, their ticket_id / task_id keys, ix_tickets_deleted_datetime, and ix_notif_unread.

⚠️ The comment must be on its own line. dbVerifyParseIndexesFromSql() matches a KEY line with /^…KEY name (cols),?\s*$/, so KEY … , -- comment is silently skipped. The generator reported the same 648 indexes until the comments were moved above.

---

7. Cost

• One request per visible page per beat. At the default 15 seconds that is 4 a minute per tab somebody is looking at. Background tabs send nothing.

• A tickets-page beat is about 12 small queries:

  • list: 6 values;
  • open ticket: 1 row plus 3 MAX() reads;
  • bell: 2 reads.

  Each newest-row read stops at the first visible row.

• Company scope adds tenant_id conditions to the walk. On a multi-company install the walk may pass a few other companies' rows before it finds a visible one. That is bounded and cheap, and the same approach the old inbox marker used.

• The session is opened read_and_close, so a beat never queues behind, or holds up, a real request from the same page.

• A slower beat: each analyst can choose 30 or 60 seconds. There is no install-wide override yet.

---

8. Traps (server side)

TRAP: a writer that touches nothing is invisible. A change that neither stamps updated_datetime nor writes a message, note or history row won't refresh anybody's screen. New write paths should stamp updated_datetime. If they can't, add a value to the topic's signature, as was done for the trash.

TRAP: binning through the REST API. TicketsService::deleteTicket($conn, $ctx, $id, false) and restoreTicket(…, false) (the API passes writeAudit = false) only do this:

$conn->prepare("UPDATE tickets SET deleted_datetime = UTC_TIMESTAMP(), deleted_by = ? WHERE id = ?")

No history row, no updated_datetime. That was invisible until the trash count joined the tickets signature. Found while writing this guide; tests/live-pulse.php now covers it.

TRAP: only ever a hash. Every page calls this every 15 seconds. A raw id, time or count would make it a feed of other people's activity. The test checks the response contains no dates, ids or counts.

TRAP: read the row before the access check. analystCanAccessTicket() and analystCanAccessTask() are company checks. On a single-company install they are true for any id, including ones that don't exist.

TRAP: closure capture in tests. An arrow function captures by value when it is created. fn($t) => livePulseSignatures($conn, $me, $t) defined before $me is set always asks as analyst 0, and every module gate says no. Use function ($t) use ($conn, &$me) { … }.

Page-side traps (the background-writes one above all) are on page 2.

---

9. The PHP test

tests/live-pulse.php runs on a disposable install with Database Verify first. 22 checks:

// the shape of every check: look, change something as "somebody else", look again
$sig = function (array $topics) use ($conn, &$me) { return livePulseSignatures($conn, $me, $topics); };

$before = $sig(['tickets'])['tickets'];
$conn->prepare("UPDATE tickets SET deleted_datetime = UTC_TIMESTAMP() WHERE id = ?")->execute([$ticketId]);   // the API's bin
$binned = $sig(['tickets'])['tickets'];
$conn->prepare("UPDATE tickets SET deleted_datetime = NULL WHERE id = ?")->execute([$ticketId]);
check('binning through the API (no history, no updated time) moves the list', $binned !== $before);
check('...and restoring it moves it back', $sig(['tickets'])['tickets'] === $before);

• Nothing changed: two looks agree, and every topic answers a 32-character hash.
• Tickets: an updated time moves the list and that ticket but not the board; a note moves both; a history row moves the ticket; an API bin and restore moves the list and back.
• Tasks: a new task moves the board; a comment moves the open task; deleting it moves the board, and the task then answers null.
• Bell: a new notification moves it.
• Limits: a nonexistent ticket or task is null; unknown topics are left out; at most 12 topics per request; the preference reads 15 by default and 0 when off.
• The endpoint (forged session, real HTTP): a hash per topic, nothing but hashes, and 401 when signed out.

FREEITSM_TEST_DATABASE=<throwaway db> FREEITSM_TEST_URL=http://localhost/<that install> php tests/live-pulse.php

Next: page 2 - the browser side.