Service Uptime and History Developer Guide

Service uptime and history — Developer Guide

How per-service history and uptime are produced, why there is no status-history table, and what the phase-2 update log changed. The plain-language version, including the worked example, is Service uptime and history.

Asked for in discussion #59. Phase 1 in a4c152af, phase 2 in 03f73d27.

---

1. 📁 The files involved

🟢 Derivation

File	Role
includes/services/service_uptime.php	ServiceUptime — the whole read side: segments, summary, daily strip
api/service-status/get_service_history.php	One service's history and uptime
api/service-status/get_incident_updates.php	An incident's update thread

🟠 Write path

File	Role
includes/services/service_status.php	recordIncidentUpdate() — appends a snapshot on every save
api/service-status/save_uptime_settings.php	Window + portal visibility

🔵 Schema

Table	Role
status_incident_updates	One row per moment: status, comment, author, time
status_incident_update_services	Per-service impact at that moment
service_impact_levels.counts_as_downtime	Whether time at a level counts

⚪ UI

File	Role
service-status/index.php	Strip, uptime, segment table, update thread
service-status/settings/index.php	Impact-level flag + the Uptime tab
service-status/help.php	Section 5 — how to record an incident so the figures are right

---

2. 🧠 Why there is no status-history table

The request was for a table recording each service's status changes. There is nothing to record: status_services has no status column. A service's status is computed live in get_dashboard.php:

COALESCE((SELECT il.name
            FROM status_incident_services sis
            JOIN status_incidents si ON sis.incident_id = si.id
            JOIN service_impact_levels il ON il.id = sis.impact_level_id
           WHERE sis.service_id = ss.id AND (sst.is_resolved = 0 OR sst.id IS NULL)
           ORDER BY il.severity_order ASC LIMIT 1), 'Operational')

A previous_status → new_status log would therefore sit permanently empty. But every period a service was not Operational is an incident that touched it, so the history is derived instead — which is why it works retrospectively on data every install already has.

---

3. 🔴 What phase 2 fixed

replaceIncidentServices() deletes and re-inserts, so status_incident_services only ever holds the current impact:

$conn->prepare("DELETE FROM status_incident_services WHERE incident_id = ?")->execute([$incidentId]);

Downgrading a service from Major Outage to Degraded therefore destroyed the earlier value, and phase 1 applied the single surviving level to the incident's whole duration. Demonstrated before the fix: an incident left Degraded for an hour then set to Operational reported Operational, uptime back to 100% — the hour had vanished.

The log

Each save appends one status_incident_updates row plus one status_incident_update_services row per affected service.

⚠️ Each update is a full SNAPSHOT, not a diff. A diff makes the reader reconstruct state, and one missing row silently shifts a service's entire timeline. A snapshot costs a few rows and cannot drift.

A service is "restored" either by moving it to a non-counting level or by dropping it from the snapshot. Both end its interval at that update, which is why there is no per-service resolved flag.

recordIncidentUpdate() is best-effort and wrapped in try/catch: an install that has not run Database Verification has no tables to write to, and failing to save an incident because its audit trail could not be appended is a worse outcome than the missing trail.

---

4. 🧩 Reading it back

ServiceUptime::segmentsFor() walks an incident's updates in time order. A service's interval runs from the update that named it to the next update, whatever that update says — including saying nothing about the service, which is how removal is detected.

⚠️ segmentsFor() returns null, not [], when there is no log. null means "this incident predates phase 2" and the caller falls back to the incident's own start and end. An empty array would mean "the log says this service was never impacted" — a completely different claim that would erase every historical outage.

The bug the worked example caught

The outer query originally started FROM status_incident_services, i.e. the current links. Phase 2 made that wrong: a restored service has no current link, so the incident vanished from its history entirely. The four-service scenario reported all four services at 100% uptime.

It now matches the incident if it touched the service in the current links or anywhere in the log:

FROM status_incidents si
LEFT JOIN status_incident_services sis ON sis.incident_id = si.id AND sis.service_id = ?
WHERE (sis.id IS NOT NULL
       OR EXISTS (SELECT 1 FROM status_incident_update_services y
                    JOIN status_incident_updates u ON u.id = y.update_id
                   WHERE u.incident_id = si.id AND y.service_id = ?))

The EXISTS clause is omitted entirely when the log tables are absent (updateLogAvailable(), cached), so an un-migrated install degrades to exact phase-1 behaviour rather than erroring.

Adjacent segments merge

Every save writes a snapshot, so a service untouched by five updates produces five identical adjacent rows — the three-day Email outage listed as 9h + 18h + 8h + 14h + 5h. Only adjacent same-level segments merge, so a genuine Major → Degraded → Major stays three rows, which is the distinction the feature exists to make.

---

5. ➗ The uptime arithmetic

Two things it must get right, both verified against hand-computed answers:

Overlaps are unioned, not summed. Two incidents downing the same service at once is one outage; summing can report more downtime than there are seconds in the window, and uptime below zero. Fixture: a 4h outage with a 1h outage entirely inside it reports 4h, not 5h.

Intervals are clipped to the window, and one that started before it but has not ended must still count. A naive created_datetime >= cutoff drops exactly the long outage nobody wants missing. Fixture: an incident open 200 days reports the whole window at 7, 30 and 90 days.

Only levels with counts_as_downtime = 1 are included. That flag lives on the impact level, not on a separate rules screen, so a custom level is asked the question when it is created rather than needing a second list to be remembered. A once-only migration clears it for Maintenance / Operational / No Disruption, guarded by a marker row so an administrator who decides maintenance does count is not reverted on the next verification.

---

6. ✅ Verification

The worked example on the plain-language page is the test fixture — built through the real save_incident.php, six saves, then read back:

Service	Segments produced	Downtime	Uptime (7d)
Printing	Major 9h	9h	94.643%
VPN	Major 1d 3h, Degraded 8h	35h	79.167%
File Services	Major 2d 1h	49h	70.833%
Email	Major 2d 6h	54h	67.857%

The VPN row is the point of phase 2: two segments at different levels within one incident.

Two faults the screenshots caught that reasoning did not

• .svc-day-info used var(--border), a divider token that is #343b45 in dark mode — an excluded day rendered as a near-black gap that read as "broken".
• The strip tooltip hardcoded "maintenance" for any excluded day. "Excluded" also covers Operational and No Disruption, so a day reported a level it never had. Found by Ed opening a service whose only incident was logged at Operational.

A third of the same family, caught in review: the update thread showed "No updates were recorded" when the request failed, conflating "none" with "could not load".

---

7. 🔮 Not done

• Per-service resolution timestamps. Removal is inferred from the next snapshot, so precision is "when you saved", not "when it actually recovered". Back-dating an update would need an editable timestamp.
• Uptime in the portal is implemented as a setting but the portal does not yet render it.
• No aggregate view. Uptime is per service; there is no board-wide figure or export.

---

Related pages

• Service uptime and history — plain language, with the worked example
• Service Status · Database Verification — Developer Guide