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 -- so it runs on -target engine or -target all, not 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 or cancelled downtime.

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 -- not an exhaustive analytics surface. Any further question over these tables starts as a query.

A HARD/CRITICAL or HARD/WARNING 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 ('CRITICAL', 'WARNING')
    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. What is out of scope here

The engine gains no store, RPC or UI page from this: a native availability render in the dashboard, built on this same table shape, is a separate, later decision (ADR-027). Downsampling or rollup tables are not part of this model either -- the tables above are append-only rows derived one-to-one from events, and any aggregation happens at query time.