> Section: [8. Observing it](https://jaque.sh/docs/ui/dashboard.md)
> Next: observability/archive
> Index: https://jaque.sh/llms.txt


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"`:

```cue
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](https://jaque.sh/docs/observability/sinks-and-perfdata.md) 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.

| Column | Type | Meaning |
|---|---|---|
| `seq` | UInt64 | Event log sequence |
| `object_id` | String | Object the transition belongs to |
| `ts` | DateTime64(3) | Transition time |
| `from_status` | String | Status before the transition |
| `from_type` | String | SOFT or HARD before the transition |
| `from_attempt` | Int32 | Retry attempt before the transition |
| `to_status` | String | Status after the transition |
| `to_type` | String | SOFT or HARD after the transition |
| `to_attempt` | Int32 | Retry attempt after the transition |
| `to_flapping` | Bool | Whether the object entered flapping at this row |

### `downtime`

One row per scheduled or cancelled downtime.

| Column | Type | Meaning |
|---|---|---|
| `seq` | UInt64 | Event log sequence |
| `object_id` | String | Object the downtime applies to |
| `ts` | DateTime64(3) | Time the downtime was scheduled or cancelled |
| `downtime_id` | String | Downtime identifier |
| `action` | String | `scheduled` or `cancelled` |
| `author` | String | Who scheduled it; empty on a cancelled row |
| `comment` | String | Free-text reason; empty on a cancelled row |
| `start_at` | DateTime64(3) | Downtime window start; epoch on a cancelled row |
| `end_at` | DateTime64(3) | Downtime window end; epoch on a cancelled row |

### `reachability_changed`

One row per reachability change.

| Column | Type | Meaning |
|---|---|---|
| `seq` | UInt64 | Event log sequence |
| `object_id` | String | Object whose reachability changed |
| `ts` | DateTime64(3) | Change time |
| `reachable` | Bool | Reachable after this row |

## 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

```sql
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`.

```sql
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

```sql
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.