> Section: [8. Observing it](https://jaque.sh/docs/ui/dashboard.md)
> Next: observability/sla
> 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. 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.

| 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, started 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, started or cancelled |
| `downtime_id` | String | Downtime identifier |
| `action` | String | `scheduled`, `started` or `cancelled` |
| `author` | String | Who scheduled it; empty on a started or cancelled row |
| `comment` | String | Free-text reason; empty on a started or cancelled row |
| `start_at` | DateTime64(3) | Downtime window start; the instant it took effect on a started row; epoch on a cancelled row |
| `end_at` | DateTime64(3) | Downtime window end; the instant it is due to end on a started row; epoch on a cancelled row |

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.

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

```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 ('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`.

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

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