An SLA report over an arbitrary range needs the state history for that range, and jaque's live surfaces, the dashboard, Livestatus, the RPCs, only ever answer from the retained window of the event log. That window is enough for "what is happening now" and "what happened in the last N days" for whatever N the log's retention holds, but it is not a promise to answer a question about last year. Rather than grow the engine into a warehouse, jaque draws the line at the sink boundary: history older than the log belongs to an events-input sink writing to a database the operator already runs, and the questions above become SQL over it.

This is ADR-027's decision. The engine's own retention is unchanged; a deployment that wants range queries wider than its state_changed retention configures a history sink, and that sink becomes the system of record for state older than the log.

1. Enabling it

An events-input sink of type clickhouse writes state history instead of forwarding an encoded event stream: three domain tables it creates and migrates itself, one row per relevant event, no aggregation. Point it at the same URL a metrics-input clickhouse sink would use, with input: "events":

sinks: history: {
	type:  "clickhouse"
	url:   "clickhouse://ch:9000/jaque"
	input: "events"
}

The three table names are fixed and not configurable: setting table on an events-input clickhouse sink is a config error. This sink follows the same role rule as every other events-input sink; see Sinks and perfdata section 6. It runs on -target engine or -target all, and never on -target sink.

Insert frequency is bounded by the sink's flush_interval: at most one insert per table per interval, and only when matching events occurred in it. A deployment expecting flap storms that wants those coalesced into fewer, larger inserts can enable async_insert on the database server side; jaque needs no config for that.

2. Tables

Only payload types with a table are written; everything else, checks, notifications, acks, commands, is skipped by this sink. Status and type columns carry the state model's String() forms: PENDING/OK/WARNING/CRITICAL/UNKNOWN for status, SOFT/HARD for type. All three tables are ordered by (object_id, ts, seq) and partitioned by month; a row is deduplicated by seq on rewrite, so a follower that re-reads part of the log after a membership change does not double a row.

state_changed

One row per state transition.

downtime

One row per scheduled, started or cancelled downtime.

A fixed, flexible or triggered downtime writes a scheduled row when the operator asks for it, and a started row the instant it actually takes effect: for a fixed downtime that is its start time, for a flexible one the first problem inside its window, and for a triggered one whenever its trigger takes effect. A recurring downtime, declared in config rather than scheduled, writes no row at all.

reachability_changed

One row per reachability change.

3. Reference queries

These are the starting queries for the three questions the roadmap names: SLA, MTTR, top flappers. They are not an exhaustive analytics surface. Any further question over these tables starts as a query.

A HARD/WARNING, HARD/CRITICAL or HARD/UNKNOWN row opens a problem interval that runs until the next state_changed row for the same object; the queries below approximate that with leadInFrame over (object_id, ts), and treat an object with no later row as still in its last state at query time.

Monthly SLA percent per object, downtime excluded

WITH transitions AS (
    SELECT
        object_id,
        ts,
        to_status,
        to_type,
        leadInFrame(ts, 1, now()) OVER (
            PARTITION BY object_id ORDER BY ts
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_ts
    FROM state_changed
),
problem_seconds AS (
    SELECT
        object_id,
        toStartOfMonth(ts) AS month,
        sum(dateDiff('second', ts, next_ts)) AS down_seconds
    FROM transitions
    WHERE to_type = 'HARD' AND to_status IN ('WARNING', 'CRITICAL', 'UNKNOWN')
    GROUP BY object_id, month
),
excluded_seconds AS (
    SELECT
        object_id,
        toStartOfMonth(start_at) AS month,
        sum(dateDiff('second', start_at, end_at)) AS downtime_seconds
    FROM downtime
    WHERE action = 'scheduled'
      AND downtime_id NOT IN (
          SELECT downtime_id FROM downtime WHERE action = 'cancelled'
      )
    GROUP BY object_id, month
)
SELECT
    p.object_id,
    p.month,
    1 - (greatest(p.down_seconds - coalesce(e.downtime_seconds, 0), 0)
         / (30 * 86400)) AS sla_ratio
FROM problem_seconds p
LEFT JOIN excluded_seconds e USING (object_id, month)
ORDER BY p.object_id, p.month;

This treats every month as 30 days for the denominator; a caller that needs calendar-accurate month lengths substitutes dateDiff('second', toStartOfMonth(month), toStartOfMonth(month) + INTERVAL 1 MONTH).

MTTR per object

Mean time to recovery: the average length of a HARD problem interval before the object returns to OK.

WITH transitions AS (
    SELECT
        object_id,
        ts,
        to_status,
        to_type,
        leadInFrame(ts, 1, now()) OVER (
            PARTITION BY object_id ORDER BY ts
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS next_ts
    FROM state_changed
)
SELECT
    object_id,
    avg(dateDiff('second', ts, next_ts)) AS mttr_seconds
FROM transitions
WHERE to_type = 'HARD' AND to_status IN ('CRITICAL', 'WARNING')
GROUP BY object_id
ORDER BY mttr_seconds DESC;

Top flappers by flap count

SELECT
    object_id,
    count() AS flap_count
FROM state_changed
WHERE to_flapping = true
GROUP BY object_id
ORDER BY flap_count DESC
LIMIT 20;

4. The availability view

The dashboard has a native availability view over these tables, enabled with a config block naming which datasource to query and, optionally, an uptime target:

ui: availability: {
	ds:     "ch"
	target: 99.9
}

ds must name an entry from the datasources: block (see the panels page) of type clickhouse, and it must share its host and database with a sink whose input is events; a config declaring this block without such a sink is refused at load. target is a percent from 0 to 100 and, when set, colors objects whose uptime falls below it. The view lists objects with an uptime percent over the selected range, a breakdown by state, and an expandable timeline of state intervals per object.

The percent follows the same semantics as the reference queries above, made precise: only HARD transitions open an interval, and each interval runs to the next HARD transition, so time spent in a SOFT retry belongs to the state that preceded it. An object counts as down for HARD WARNING, CRITICAL or UNKNOWN by default, the same set the state model's IsProblem() marks as a problem, and a request can override which statuses count as down. Time the engine recorded the object as unreachable, from reachability_changed, always counts as down regardless of that set. An interval that is already open when the range starts is included, clipped to the range boundary. Scheduled, non-cancelled downtime that overlaps a down interval is subtracted from its down time by default. The "Exclude scheduled downtimes" checkbox that controls this starts checked, and can be unchecked to see raw uptime. PENDING time is undetermined: it was never checked, so it counts as neither up nor down, and the same is true for any part of the range with no recorded state at all; both are excluded from the denominator: uptime is 100 * (1 - down / determined), where determined is the time actually covered by hard states or recorded unreachability. The row also reports the range's undetermined seconds separately, so a mostly-unchecked object shows as undetermined rather than as a misleadingly high or low uptime. An object with no history at or before the range end has no percent to show and is left out of the list rather than shown at zero.

Report export and scheduling are not part of this view. Business hours, error budgets and a named SLO declaration are not part of it either; see SLA for those.

Hovering an interval in the expanded timeline shows its state, its time range and its duration, and a time axis below the timeline marks the window in evenly spaced steps. Dragging across the timeline zooms the whole view to that range, reflected back into the range picker.

5. What is out of scope here

Group rollups, error budgets and ack-aware accounting are not part of this model. Downsampling or rollup tables are not part of the underlying table shape either: the tables above are append-only rows derived one-to-one from events, and any aggregation happens at query time.