Recent Trail Developer Guide

Recent trail — Developer Guide

How the "Recent" pane in the waffle menu is built: one small table, two endpoints, and a grouping loop. Written to be followed by somebody who has not worked on FreeITSM before, so the snippets are real code with the reasoning beside them.

The user-facing page is Recent — getting back to what you were doing.

---

1. 📁 The files involved

Colour key: 🗄️ schema · ⚙️ engine · 🔌 API · 🖥️ UI · 📞 call sites

🎨	File	What it does
🗄️	database/freeitsm.sql	analyst_recent_trail — the whole storage
🗄️	includes/db_verify_schema.php	the same columns, so an upgrade creates the table
🗄️	includes/db_verify_indexes.php	generated — regenerate, never hand-edit
⚙️	includes/recent_trail.php	everything: write, prune, read, group, resolve, gate
🔌	api/system/recent_trail.php	GET — the grouped trail for the signed-in analyst
🔌	api/system/recent_trail_visit.php	POST — "I have just opened this record"
🖥️	includes/waffle-menu.php	the tabs, the pane, the CSS, and window.trailVisit()
📞	assets/js/inbox.js · assets/js/tasks.js · assets/js/knowledge.js · assets/js/problem-management.js · assets/js/change-management.js · asset-management/index.php	one line each
📞	cmdb/object.php, contracts/view.php	one line each, server-side

⚠️ Adding an index to database/freeitsm.sql means running php scripts/gen_db_verify_indexes.php. The mirror in includes/db_verify_indexes.php is what gives an existing install an index it never received, and tests/db-verify-indexes/run.php fails if the two drift.

---

2. 🗄️ The table

CREATE TABLE IF NOT EXISTS `analyst_recent_trail` (
    `id`               INT NOT NULL AUTO_INCREMENT,
    `analyst_id`       INT NOT NULL,
    `entity_type`      VARCHAR(40) NOT NULL,
    `entity_id`        INT NOT NULL,
    `visited_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_analyst_recent_trail_analyst` (`analyst_id`, `visited_datetime`),
    CONSTRAINT `fk_analyst_recent_trail_analyst`
        FOREIGN KEY (`analyst_id`) REFERENCES `analysts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Four things to notice, because each is a decision rather than a default.

There is no unique key on (analyst_id, entity_type, entity_id). Every other most-recently-used list in the product has one. This is a log, not a recents list: the same ticket opened at 09:00 and again at 15:00 has to be two rows, under two headings, or the outline is describing an afternoon that did not happen. Deduplicating would delete the very fact the feature exists to show.

Which means the table needs a cap instead. A unique key is what normally stops such a table growing; here recentTrailPrune() does that job.

entity_type is the entity vocabulary, not a module key — knowledge_article, not knowledge. Which module a row is filed under is worked out at render time, so re-homing a record type is one line in one PHP file rather than a data migration.

The index is (analyst_id, visited_datetime). Every read and every prune is "this analyst, newest first". One index serves both.

---

3. ⚙️ Writing a visit

function entityVisit(string $type, int $id, ?PDO $conn = null): void
{
    if (!recentTrailShouldRecord()) {
        return;
    }
    recentTrailWrite($type, $id, $conn);
}

entityVisit() is the page-load path and recentTrailWrite() is the core. They are split because the browser ping has to skip the guard — more on that in §5.

The guard: what is not a visit

function recentTrailShouldRecord(): bool
{
    if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'GET') {
        return false;
    }
    if (strtolower($_SERVER['HTTP_X_REQUESTED_WITH'] ?? '') === 'xmlhttprequest') {
        return false;
    }
    $purpose = strtolower(($_SERVER['HTTP_SEC_PURPOSE'] ?? '') . ' ' . ($_SERVER['HTTP_PURPOSE'] ?? ''));
    if (strpos($purpose, 'prefetch') !== false || strpos($purpose, 'prerender') !== false) {
        return false;
    }
    return true;
}

Without this the trail fills with noise. The same module directories serve XHR fragments and polling endpoints, and a heartbeat firing every thirty seconds would write a "visit" every thirty seconds and push everything you actually did off the end.

The prefetch check is the subtle one: a browser guessing where you might go next is not you choosing to go there, and a record in your trail that you never looked at is worse than one missing that you did.

Collapsing a refresh, but only a refresh

$last = $conn->prepare(
    "SELECT id, entity_type, entity_id
       FROM analyst_recent_trail
      WHERE analyst_id = ?
   ORDER BY visited_datetime DESC, id DESC
      LIMIT 1"
);
$last->execute([$analystId]);
$row = $last->fetch(PDO::FETCH_ASSOC);

if ($row && $row['entity_type'] === $type && (int)$row['entity_id'] === $id) {
    $conn->prepare("UPDATE analyst_recent_trail SET visited_datetime = UTC_TIMESTAMP() WHERE id = ?")
         ->execute([(int)$row['id']]);
    return;
}

$conn->prepare(
    "INSERT INTO analyst_recent_trail (analyst_id, entity_type, entity_id, visited_datetime)
     VALUES (?, ?, ?, UTC_TIMESTAMP())"
)->execute([$analystId, $type, $id]);

This one rule is the whole difference between an outline and a deduplicated list. The check is against the newest row only. Hit F5, or read one long ticket over ten minutes, and the timestamp moves — one line. Open something else in between and the chain is broken, so coming back is genuinely a second visit and gets its own row.

🌍 visited_datetime is named explicitly and set to UTC_TIMESTAMP(). Connections are pinned to UTC, so the column default would in fact be right — naming it anyway is the house rule that came out of a sweep finding 302 INSERTs across 220 tables letting CURRENT_TIMESTAMP fire in whatever zone the server happened to be in. See Timezones & Time Handling.

Pruning

if (random_int(1, 10) === 1) {
    recentTrailPrune($conn, $analystId);
}

Roughly one insert in ten. Paying for the prune on every record page anybody opens would tax the whole product to serve a drawer most of those visits never open.

DELETE t FROM analyst_recent_trail t
   JOIN (SELECT visited_datetime, id
           FROM analyst_recent_trail
          WHERE analyst_id = ?
       ORDER BY visited_datetime DESC, id DESC
          LIMIT 1 OFFSET 250) cut
  WHERE t.analyst_id = ?
    AND (t.visited_datetime < cut.visited_datetime
         OR (t.visited_datetime = cut.visited_datetime AND t.id <= cut.id))

The derived table is not decoration. MySQL refuses a subquery that names the table you are deleting from, and materialising it into a JOIN is the standard way round that. LIMIT 1 OFFSET 250 finds the 251st-newest row and everything at or below it goes, in one statement however far over the cap the analyst is.

---

4. ⚙️ Reading it back: filter first, group second

$labels = recentTrailLabels($conn, $analystId, $rows);

foreach ($rows as $row) {
    if (!isset($labels[$key])) {
        continue;                       // gone, or not yours any more
    }
    $sameModule = $current !== null && $current['module'] === $module;
    if ($sameModule && recentTrailWithinGap($visited, $current['started'])) {
        $current['records'][] = $record;
        $current['started']   = $visited;
    } else {
        if ($current !== null) $groups[] = $current;
        $current = ['module' => $module, 'started' => $visited,
                    'latest' => $visited, 'records' => [$record]];
    }
}

🔴 The order matters, for two separate reasons.

1. A heading whose every record has been deleted or put out of reach must not render at all. Grouping first and filtering after would leave a dated "Assets" heading with nothing under it — ugly, and a small confession that something was there.
2. When a record in the middle of a run drops out, the runs either side of it are the same module and correctly merge into one. Grouping first would leave a seam where an invisible record used to be.

What opens a new heading

const RECENT_TRAIL_GAP_MINUTES = 30;

The module changing, or thirty minutes passing. The time half is not optional: without it, closing the laptop in Tickets on Tuesday and opening Tickets on Wednesday would append to Tuesday's heading, producing one group spanning two days under a single date.

The rows arrive newest first, so the gap is measured from the group's earliest row so far back to the one about to be added. They are adjacent in time even though the loop walks backwards through it.

Resolving labels: batch the expensive half, reuse the authoritative half

foreach ($byType as $type => $ids) {
    // 🔴 THE MODULE GATE FIRST — one check for the whole type, before any query.
    if (!analystCanAccessModule($conn, $analystId, RECENT_TRAIL_MODULES[$type])) {
        continue;
    }
    ...
}

then, per type, one query for the labels and the module's own per-record gate on each:

case 'ticket':
    $sql  = "SELECT id, TRIM(CONCAT(COALESCE(ticket_number,''), ' ', COALESCE(subject,'')))
               AS label FROM tickets WHERE id IN ($in)";
    $gate = fn($id) => analystCanAccessTicket($conn, $analystId, $id);
    break;

Why not simply call recordPreview(), which already resolves a record with its gates? Because it is the right authority and the wrong shape: it runs a multi-join query per record to fill a card of fields, and the drawer needs one line of text. Sixty previews to draw sixty one-line rows would make a control that exists on all 91 screens noticeably slow. So the expensive half is batched and the authoritative half is not reimplemented.

🔒 Knowledge is the exception, and worth reading as a pattern. Its visibility is folders, audiences and lifecycle — not a tenancy filter — so its own SQL is folded into the label query rather than approximated:

$viewer = KnowledgeViewer::forAnalyst($conn, $analystId);
[$vis, $args] = knowledgeVisibilitySql($conn, $viewer, 'a');
$stmt = $conn->prepare("SELECT a.id, a.title AS label FROM knowledge_articles a WHERE a.id IN ($in)" . $vis);

An approximation here would be a way to read a restricted article. See Knowledge — folders and security: Developer Guide.

⚠️ Never activeTenantFilter(). That answers "is this in the company I am currently looking at", which is a view setting rather than a permission, and would silently drop records the analyst is perfectly entitled to open.

---

5. 🔌 Two endpoints, and why the second one exists

api/system/recent_trail.php returns the grouped trail. Timestamps leave as UTC with an explicit Z so the browser renders the reader's own clock:

function recentTrailIso(string $stored): string
{
    return str_replace(' ', 'T', trim($stored)) . 'Z';
}

🔴 api/system/recent_trail_visit.php is the half that nearly got missed. Server-side hooks alone would have been almost useless, because most of this product opens records without a page load: the ticket inbox, the tasks board, assets, problems, changes and knowledge are each one screen whose list swaps the record in place, and most never touch the URL when they do. Server hooks would have recorded the moments you arrived at a module and missed every record you opened once you were there — exactly the work the trail exists to lead you back to.

Only two record types are real pages of their own, and those are the two that are still server-side:

// cmdb/object.php
require_once '../includes/recent_trail.php';
entityVisit('cmdb_object', (int) ($_GET['id'] ?? 0));

The write endpoint deliberately has no per-record gate:

Being able to write "I looked at ticket 91" into your own trail discloses nothing. The write is unverified, and the read re-checks the module and the record every single time the drawer is opened — so a record you were never entitled to see cannot be made to appear by claiming to have visited it. Checking here as well would cost a query per record opened, on every screen, to defend against somebody putting a row into their own history that they will never be shown.

---

6. 🖥️ The browser side

One helper, defined once

window.trailVisit = function (type, id) {
    id = parseInt(id, 10);
    if (!type || !id || id <= 0) return;
    try {
        fetch(BASE_URL + 'api/system/recent_trail_visit.php', {
            method: 'POST',
            credentials: 'same-origin',
            keepalive: true,
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify({ type: type, id: id })
        }).catch(function () {});
    } catch (e) { /* never breaks the page it is hung off */ }
    waffleTrailLoaded = false;   // the cached trail is now stale
};

It lives in includes/waffle-menu.php because the waffle is on all 91 screens — that makes it the one place a helper can be defined once and called from every module's own JS with no extra script tag anywhere.

keepalive: true lets the ping survive the navigation that sometimes immediately follows it. Failures are swallowed: a dropped ping means one row missing from a list of recent things.

Call sites: pick the choke point, not the entry point

// assets/js/inbox.js
function displayEmail(email, recordings) {
    currentEmail = email;
    ...
    if (window.trailVisit) window.trailVisit('ticket', email.ticket_id);

displayEmail() rather than loadTicketById(), because every way of putting a ticket in the reading pane ends up there — the list, a deep link, a search result, the previous/next keys. A hook per entry point would have missed one.

The others follow the same rule and all sit after the success check, so a record that failed to load never appears as one you read:

Module	Function
Tickets	displayEmail()
Tasks	openDetailPanel()
Knowledge	viewArticle()
Problems	pmOpenDetail()
Changes	viewChange()
Assets	selectAsset()

The headings borrow their identity from the tiles above

The API returns a module key, not a name or an icon:

function waffleModuleTile(key) {
    var safe = (window.CSS && CSS.escape) ? CSS.escape(key) : key;
    var icon = document.querySelector('#waffleTabModules .waffle-module-icon.' + safe);
    if (!icon) return null;
    var link = icon.closest('.waffle-module-link');
    var name = link ? link.querySelector('.waffle-module-name') : null;
    return { svg: icon.querySelector('svg'), name: name ? name.textContent.trim() : key };
}

The icon, colour and translated name are cloned out of the module grid in the other pane. One definition of what "Tickets" looks like, the analyst's own language for free, and a module whose tile is absent (no access) has no heading either — which is the same answer the server-side gate already gave for its records.

Rendering is DOM, not HTML strings

label.textContent = rec.label;

textContent, not innerHTML: a ticket subject is whatever a requester typed into an email.

The search filters the trail, not the product

group.hidden = hits === 0;
group.classList.toggle('collapsed', q ? false : i > 0);

A group survives if any record in it matches, and is forced open so the match is visible — a collapsed heading that happens to contain the answer is indistinguishable from one that does not. Clearing the box restores the pane exactly as it was found: newest open, the rest closed.

---

7. 🐛 The bug worth learning from

The search box on a phone was falling 53 pixels past the bottom of the drawer, where the panel's own overflow: hidden hid it completely. The cause:

/* desktop, earlier in the file */
.waffle-panel.active { display: block; }

/* inside @media (max-width: 768px) */
.waffle-panel { display: flex; flex-direction: column; }   /* ← never applied */

🔴 A media query adds no specificity. .waffle-panel.active is two classes and beats .waffle-panel however far down the file it sits and whatever it is wrapped in. The drawer silently stayed display: block, so the pane never became a flex column and the footer had nothing holding it in.

The fix is to match the specificity:

.waffle-panel.active { display: flex; flex-direction: column; }

⚠️ And the reason it survived the first round of checking: measuring the page body reported no overflow at all, because a container that clips cannot report an overflow. The assertion could not fail. Measuring the panes — is the list scrollable, is the search box inside the viewport, does it stay put when the list scrolls — found it immediately.

---

8. 🧪 Checking it works

There is no automated suite for this one; it was verified by driving the real pages. Worth repeating if you change it:

• Grouping — script an afternoon straight into the table with known timestamps, read it back through entityRecentTrail(), and check the shape: a return to a module opens a second heading; a 90-minute gap opens one without the module changing.
• A run of only-deleted records renders no heading — and pair it with a positive control (the same query plus one real record), or "0 groups" is equally consistent with a broken query.
• The refresh rule — three views of one ticket is one row; another record in between makes coming back two.
• At 360px, measure the panes, per §7.

---

User-facing page: Recent — getting back to what you were doing. Related: Command palette, Record previews.