The database schema¶

PostgreSQL 18. Two migrations, V1__events.sql and V2__consumer_checkpoints.sql, in modules/persistence/src/main/resources/db/migration/, run by Flyway with -Duser.timezone=UTC from cobalt's startup. Everything on this page is transcribed from those files; where they and ADR-0000 §5 differ, the migration is right and the differences are called out.

The schema is events. pg_trgm is installed for the dimension tables only.

Object	Kind	Rows
`events.cloud_event`	RANGE-partitioned fact table	the whole event log
`events.cloud_event_2026_07`, `…_2026_08`, `…_default`	partitions	one month each, plus a safety net
`events.device`, `events.room`, `events.person`, `events.dim_event_type`	dimension tables	thousands
`events.event_rollup_hourly`	materialized view	one row per (hour, type, source, severity)
`events.consumer_checkpoint`	table	one row per (group, topic, partition)
`events.saved_search`	table	content-addressed filter ASTs

What question this answers: which of these objects actually constrain each other, and which only look related?

erDiagram
    cloud_event {
        timestamptz occurred_at PK "partition key; NOT generated"
        uuid event_uid PK "surrogate, gen_random_uuid()"
        jsonb raw "the CloudEvent, verbatim"
        bytea payload_sha256 "over the canonical rendering"
        timestamptz ingested_at "DEFAULT now(); BRIN"
        text ce_id "GENERATED from raw"
        text ce_source "GENERATED from raw"
        text ce_type "GENERATED from raw"
        jsonb data "GENERATED: raw of data"
        jsonb extensions "GENERATED: raw minus the reserved keys"
        text device_id "GENERATED from data.deviceId"
        smallint severity_rank "GENERATED via events.severity_rank"
        tsvector search_doc "GENERATED, four weighted passes"
    }

    event_rollup_hourly {
        timestamptz bucket UK "date_trunc hour"
        text ce_type UK
        text ce_source UK
        text severity UK "coalesce(severity, none)"
        bigint event_count
        bigint error_count "FILTER severity_rank at least 50"
        bigint device_count "count DISTINCT device_id"
    }

    consumer_checkpoint {
        text group_id PK
        text topic PK
        int partition PK
        bigint next_offset "Kafka convention: last processed plus one"
        bigint records "cumulative, not per batch"
        text owner "which replica wrote it"
        timestamptz updated_at
    }

    saved_search {
        text slug PK "base32 of sha256 of the AST"
        jsonb ast
        text label
        timestamptz created_at
    }

    flyway_schema_history {
        text version "Flyway owns this table's shape"
        int checksum "what validateOnMigrate compares"
        boolean success
    }

    cloud_event ||..o{ event_rollup_hourly : "recomputed from, by REFRESH; no constraint"
    cloud_event ||..o| consumer_checkpoint : "same transaction as the insert; no constraint"

Two things this is meant to make unmissable.

There is not one foreign key in this schema. The dotted edges are the only relationships that exist, and both are behavioural rather than declared: the rollup is a materialized view over the fact table, and the checkpoint row is written inside the same transaction as the batch of events it accounts for. saved_search and flyway_schema_history connect to nothing at all, and flyway_schema_history is not even in the events schema — it lands in the connection's default schema, public, because V1__events.sql is what creates events and a history table inside the schema its own migrations create is a chicken-and-egg problem.

cloud_event here shows thirteen of its columns and it has more. The ones omitted are the rest of the generated projections (ce_specversion, ce_subject, ce_dataschema, ce_datacontenttype, room_id, person_id, site_id, severity, metric_value, tags) — they are the same idea repeated and the full list is in the table below. The four dimension tables (device, room, person, dim_event_type) are left out because nothing writes them yet; see open items.

The partitions are also absent, because an ER diagram cannot say what a partition is. They are below.

Raw JSONB plus generated columns¶

raw                jsonb NOT NULL,          -- the CloudEvent, verbatim
payload_sha256     bytea NOT NULL,

ce_id              text  GENERATED ALWAYS AS (raw ->> 'id')     STORED,
ce_source          text  GENERATED ALWAYS AS (raw ->> 'source') STORED,
ce_type            text  GENERATED ALWAYS AS (raw ->> 'type')   STORED,
device_id          text  GENERATED ALWAYS AS (raw #>> '{data,deviceId}') STORED,
…

There is no second write. A projection cannot drift from the payload it claims to describe because it is the payload — PostgreSQL recomputes every generated column from raw on every insert and update, in the same statement, inside the same transaction. There is no application code that could forget to update device_id when it updates raw, no backfill that could half-finish, and no consumer redelivery that could write the columns from one version of an event and the JSON from another. The class of bug where a search result and its detail page disagree is not mitigated here; it is unrepresentable.

The full set of projections:

Column	Expression	For
`ce_specversion`, `ce_id`, `ce_source`, `ce_type`, `ce_subject`, `ce_dataschema`, `ce_datacontenttype`	`raw ->> '<attr>'`	CloudEvents context attributes
`data`	`raw -> 'data'`	payload search, detail
`extensions`	`raw - '{specversion,id,source,type,subject,time,dataschema,datacontenttype,data,data_base64}'::text[]`	custom CE extensions
`device_id`, `room_id`, `person_id`, `site_id`	`raw #>> '{data,<key>}'`	smart-home dimensions
`severity`	`lower(raw #>> '{data,severity}')`	facet value
`severity_rank`	`events.severity_rank(raw #>> '{data,severity}')`	ordered comparison
`metric_value`	`events.jsonb_num(raw, '{data,value}')`	charting
`tags`	`events.jsonb_text_array(raw, '{data,tags}')`	tag filter
`search_doc`	weighted `tsvector`, below	free text

Three constraints on what may go into a generated column¶

Extraction helpers must be non-throwing. A bare (raw #>> '{data,value}')::float8 aborts the entire insert — a whole 500-event batch from cobalt — on one malformed payload from one device. events.jsonb_num and events.jsonb_text_array are plpgsql, IMMUTABLE PARALLEL SAFE, and return NULL on any exception. A bad payload becomes a missing dimension rather than an outage.

Expressions must be IMMUTABLE. This is what forbids occurred_at from being generated: text::timestamptz is only STABLE (it depends on TimeZone), and PostgreSQL forbids generated columns in a partition key regardless. It is also why search_doc uses the two-argument to_tsvector('simple', …) form — the one-argument form reads default_text_search_config and is only STABLE, so PostgreSQL rejects it outright here.

The reserved-attribute list must match the domain. The raw - '{…}'::text[] literal is written on one line on purpose: unquoted array elements are whitespace-trimmed, and wrapping that literal is exactly the kind of edit that silently changes which keys count as extensions. It must stay identical to com.worxbend.kernel.event.Envelope.ReservedAttributes.

`severity_rank` and the domain¶

events.severity_rank(text) maps debug→10 … emergency/panic→80, btrim(lower(…)), aliases included (warn/warning, err/error, crit/critical, emerg/emergency/panic). Text alone cannot be range-compared; the rank can, which is what makes "at least warning" an index scan instead of an IN list.

These are the same numbers and the same spellings as com.worxbend.kernel.search.Severity, and they must stay that way. If they drift, the UI's alert filter and partial index (11) disagree about what an alert is: a crit event would be an alert to the domain and a NULL rank to the database — missing from the alert feed and from severity >= warn searches, while still rendering as critical in the detail view.

`search_doc`¶

setweight(to_tsvector('simple',  coalesce(raw ->> 'type', '')),            'A') ||
setweight(to_tsvector('simple',  coalesce(raw ->> 'source', '') || ' ' ||
                                 coalesce(raw ->> 'subject', '')),         'B') ||
setweight(to_tsvector('english', coalesce(raw #>> '{data,message}', '')),  'C') ||
setweight(jsonb_to_tsvector('simple', coalesce(raw -> 'data', '{}'::jsonb),
                            '["string"]'),                                 'D')

'simple' for identifiers — device ids, MQTT topics, reverse-DNS type strings — because English stemming mangles them. 'english' only for the one field that is actually prose, data.message. The D-weight pass sweeps every string value anywhere in data, which is what makes an unknown payload shape searchable without anyone registering its fields.

`payload_sha256`¶

SHA-256 over the canonical JSON rendering (Json.noSpaces), not over the wire bytes, and it does not pretend otherwise. jsonb already discards key order, insignificant whitespace and duplicate keys, so hashing the received octets would produce a digest that could never be recomputed from what was stored. Hashing the canonical form gives a digest that can be recomputed from the stored row — which is what makes it useful for detecting silent corruption and cross-partition duplicates.

The rendering is produced once and both used. NewEvent carries the noSpaces string it hashed, and the insert binds that string rather than handing the Json back to a codec that would render it a second time. The saving is per record on the consume path and it is the largest single cost there: rendering a 1.5 KB CloudEvent measures at ~4.2 µs against ~0.9 µs for the SHA-256 over it, so the second rendering was roughly half of the write path's per-record CPU. The correctness half matters as much — the digest is now over the bytes that were sent, not over a second rendering that merely ought to have been equal to them.

Table constraints¶

CONSTRAINT cloud_event_pk PRIMARY KEY (occurred_at, event_uid),
CONSTRAINT cloud_event_specversion_ck CHECK (raw ->> 'specversion' = '1.0'),
CONSTRAINT cloud_event_required_ck    CHECK (raw ? 'id' AND raw ? 'source' AND raw ? 'type')

The primary key includes the partition key because PostgreSQL requires it. event_uid is a surrogate (DEFAULT gen_random_uuid()); ce_id is the CloudEvents id. Keeping them distinctly named is why nothing in this schema is ambiguous about which identity is meant.

Monthly partitioning¶

) PARTITION BY RANGE (occurred_at);

CREATE TABLE events.cloud_event_2026_07 PARTITION OF events.cloud_event
    FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-08-01 00:00:00+00');
CREATE TABLE events.cloud_event_2026_08 PARTITION OF events.cloud_event
    FOR VALUES FROM ('2026-08-01 00:00:00+00') TO ('2026-09-01 00:00:00+00');
CREATE TABLE events.cloud_event_default PARTITION OF events.cloud_event DEFAULT;

Three things follow from range-partitioning on occurred_at:

Retention is metadata, never a DELETE. ALTER TABLE … DETACH PARTITION … CONCURRENTLY followed by DROP TABLE removes a month in constant time and generates no dead tuples. A DELETE of a month of events would be hours of vacuum work on a table that is otherwise never vacuumed for dead tuples at all.

Every time-bounded query prunes. occurred_at >= $1 AND occurred_at < $2 — which Filter.Occurred compiles to and which the histogram always applies — eliminates whole partitions at plan time.

Bounds carry an explicit +00. Partition bounds are parsed in the session timezone, so a bare date silently shifts every partition by the server's offset. This is why Flyway runs with -Duser.timezone=UTC as well.

Where a row physically lands¶

What question this answers: an insert arrives — which table does the tuple go in, who guaranteed that table exists, and what happens when nobody did?

flowchart TB
    I["INSERT from cobalt<br/>occurred_at = the producer's CloudEvents time"]
    R{"is occurred_at inside<br/>a declared month range?"}
    L1["events.cloud_event_2026_07"]
    L2["events.cloud_event_2026_08"]
    LN["events.cloud_event_YYYY_MM<br/>created ahead by the job"]
    DF["events.cloud_event_default<br/>tripwire, must stay empty"]
    AL["alarm: partition.default.rows is gauged"]
    J["PartitionMaintenance, on a schedule<br/>under pg_try_advisory_lock"]
    C["CREATE TABLE IF NOT EXISTS ... PARTITION OF<br/>for monthsAhead months, plus both autovacuum reloptions"]
    D["ALTER TABLE ... DETACH PARTITION CONCURRENTLY<br/>then DROP — retention is metadata, never a DELETE"]

    I --> R
    R -- yes --> L1
    R -- yes --> L2
    R -- yes --> LN
    R -- no --> DF
    DF --> AL
    J --> C
    C -.->|"makes the range exist before a row needs it"| LN
    J --> D
    D -.->|"retires months past retainMonths"| L1

The parent events.cloud_event stores no tuples at all, which is why the autovacuum reloptions are set on each leaf and why the maintenance job has to repeat them on every partition it creates — they are not inherited, so the newest month, the only one actually being written to, would otherwise be the one month that never gets an insert-driven vacuum.

The default partition is a tripwire, not a fallback¶

It exists so a clock-skewed producer cannot fail an insert outright, and it must stay empty. partition.default.rows is gauged and alerted on, because once the default partition holds rows, creating an overlapping partition takes ACCESS EXCLUSIVE and scans it — a maintenance outage caused by one bad clock. The alarm firing is the cheap moment to act.

The actual prevention is at the edge: wolfram's TimeClamp rejects an event whose time is implausible (asymmetric window — hours ahead, months behind) or absent, rather than clamping it, because a rewritten timestamp produces a row that is silently wrong and unrepairable.

Partition maintenance is not in this migration¶

Deliberately. Migrations are versioned and immutable; partitions are a rolling concern. The migration ships two months plus the default, and creating months N+3 ahead with CREATE TABLE IF NOT EXISTS … PARTITION OF under a pg_try_advisory_lock is a scheduled job's responsibility (ADR §5). That job is not implemented yet — see open items.

Autovacuum settings go on the leaves¶

ALTER TABLE events.cloud_event_2026_07 SET (
    autovacuum_vacuum_insert_scale_factor = 0.0,
    autovacuum_vacuum_insert_threshold    = 50000);
-- repeated for _2026_08 and _default

Append-only tables are never touched by dead-tuple autovacuum, so insert-driven vacuums have to be forced or the visibility map goes stale and index-only scans stop being index-only.

This differs from ADR §5, which sets the reloptions on the parent. That does not work: a partitioned table stores no tuples, and PostgreSQL rejects storage parameters on it outright ("cannot specify storage parameters for a partitioned table"). Nor are these settings inherited, which is the sharp edge for the rolling partition job: it must repeat both reloptions on every partition it creates, or the newest month — the only one actually being written to — is the one month that never gets an insert-driven vacuum.

Planner statistics targets are set on the parent, where they are inherited:

ALTER TABLE events.cloud_event ALTER COLUMN ce_type   SET STATISTICS 1000;
ALTER TABLE events.cloud_event ALTER COLUMN device_id SET STATISTICS 1000;

Both are high-cardinality and heavily skewed — a handful of chatty devices produce most rows — and the default 100 buckets is not enough for the planner to tell a selective device from a firehose.

The dedup contract¶

CREATE UNIQUE INDEX cloud_event_identity_uk
    ON events.cloud_event (occurred_at, ce_source, ce_id);

This one index is what makes at-least-once delivery survivable. Kafka redelivers; cobalt replays after a crash; the insert is:

INSERT INTO events.cloud_event (occurred_at, raw, payload_sha256)
VALUES (?, ?, ?)
ON CONFLICT (occurred_at, ce_source, ce_id) DO NOTHING

CloudEvents guarantees that (source, id) is unique per producer. So a redelivered record collides, the insert does nothing, and the write is idempotent — at-least-once delivery becomes observationally exactly-once at the database, and nowhere else in the pipeline. No consumer-side dedup cache, no transactional Kafka, no exactly-once semantics to configure and misconfigure.

Three details:

Two of the three conflict columns are generated from raw. ce_source and ce_id are not values the writer supplies; they are projections of the document it is inserting. The dedup key therefore cannot disagree with the payload it deduplicates, which is the same argument as the generated-column design, applied to correctness of delivery rather than correctness of search.
occurred_at is in the index because a unique index on a partitioned table must contain the partition key. That is a PostgreSQL requirement, not a design choice, and it is also why the row's timestamp is passed to the insert separately even though it is already inside raw.
The batch's written count is the number of rows the insert actually created; batch.size - written is consume.records.duplicate. That metric is the direct evidence that redelivery is being absorbed rather than duplicated, and it can only under-report, never claim a write that did not happen.

Because Committer.flow sits strictly downstream of the write, an offset is a receipt for a durable effect. The combination — commit after the write, dedup on the CloudEvents identity — is the whole delivery story, and it is drawn stage by stage in flows.

Every index, and the query it serves¶

Fact table¶

#	Index	Definition	Query shape
—	`cloud_event_pk`	`(occurred_at, event_uid)`	detail lookup by `EventRef`; both columns bound, so it prunes to one partition
1	`cloud_event_identity_uk`	`UNIQUE (occurred_at, ce_source, ce_id)`	`INSERT … ON CONFLICT DO NOTHING` — above
2	`cloud_event_type_time_ix`	`(ce_type, occurred_at DESC, event_uid DESC)`	`WHERE ce_type = ANY($1) ORDER BY occurred_at DESC, event_uid DESC LIMIT 50`, plus the keyset seek
3	`cloud_event_device_time_ix`	`(device_id, occurred_at DESC, event_uid DESC) WHERE device_id IS NOT NULL`	device drilldown
4	`cloud_event_source_time_ix`	`(ce_source, occurred_at DESC, event_uid DESC)`	per-integration timeline (one CE `source` URI)
5a	`cloud_event_room_time_ix`	`(room_id, occurred_at DESC) WHERE room_id IS NOT NULL`	room facet drilldown
5b	`cloud_event_person_time_ix`	`(person_id, occurred_at DESC) WHERE person_id IS NOT NULL`	person facet drilldown
6	`cloud_event_ingested_brin`	`brin (ingested_at) WITH (pages_per_range = 32, autosummarize = on)`	`WHERE ingested_at >= now() - $1` — ingestion-lag dashboards, backfill windows
7	`cloud_event_data_gin`	`gin (data jsonb_path_ops)`	`data @> $1::jsonb`, and partially `data @? $1::jsonpath`
8	`cloud_event_extensions_gin`	`gin (extensions)`	`jsonb_exists(extensions, $1)`, `extensions ->> $1 = $2`
9	`cloud_event_tags_gin`	`gin (tags array_ops) WHERE tags IS NOT NULL`	`tags @> $1::text[]`
10	`cloud_event_search_gin`	`gin (search_doc)`	`search_doc @@ websearch_to_tsquery($1::regconfig, $2)`
11	`cloud_event_alerts_ix`	`(occurred_at DESC) WHERE severity_rank >= 50`	`WHERE severity_rank >= 50 ORDER BY occurred_at DESC LIMIT 20`
12	`cloud_event_metric_ix`	`(device_id, occurred_at DESC) INCLUDE (metric_value) WHERE metric_value IS NOT NULL`	`WHERE device_id = $1 AND metric_value IS NOT NULL ORDER BY occurred_at DESC`

Why each is shaped the way it is:

(2)–(5) put the equality key first and the sort key second. That single ordering lets one index supply the filter, the ORDER BY and the keyset range scan. The keyset predicate is the row-value form (occurred_at, event_uid) < ($1, $2) — never the expanded a < x OR (a = x AND b < y), which the planner cannot turn into a seek and which would scan every row with a < x regardless of b. Both columns are NOT NULL, which avoids the NULLS FIRST/LAST keyset trap, and event_uid is the total-order tiebreaker. Page 10 000 costs what page 1 costs; OFFSET is never used.

(3), (5), (9), (11), (12) are partial. System and aggregate events carry no deviceId and never appear in device views, so excluding them shrinks the index rather than bloating it with a NULL run. severity_rank >= 50 selects well under 1 % of rows, so the alert feed's index is orders of magnitude smaller than the table.

(6) is BRIN, not btree. The table is append-only, so ingested_at is almost perfectly correlated with physical order — the exact case BRIN is for. A few KB replaces a multi-GB btree. autosummarize matters because the newest range is the one every lag query reads and it would otherwise stay unsummarised until the next vacuum.

(7) is jsonb_path_ops, (8) is default jsonb_ops. path_ops is about half the size and materially faster for @> and @?, at the cost of the ? / ?| / ?& key-existence operators — which the UI does not use on data. Extension filtering is key-existence based, so extensions needs jsonb_ops; it is a tiny column, so the size cost is irrelevant. (In SQL, key existence is always spelled jsonb_exists(extensions, ?) and never with the bare ? operator, which collides with the JDBC placeholder.)

(12) uses INCLUDE. metric_value rides along as a non-key payload column, so plotting a chart is an index-only scan with no heap fetch per point.

Free text is websearch_to_tsquery, never to_tsquery. It accepts "quoted phrase" -excluded or and, critically, does not raise a syntax error on malformed input — so a stray & typed into the search box is not a 500.

Deliberately absent: a trigram GIN on `ce_subject`¶

Unanchored ILIKE '%…%' over the fact table is not supported. A trigram GIN over 10⁸ rows is a multi-GB index that also slows every insert. Substring discovery happens on the dimension tables and resolves into an exact equality filter; whole-word search is served by (10).

Dimension tables — where `pg_trgm` belongs¶

Index	Table	Query shape
`device_label_trgm_ix`	`events.device`	`WHERE label ILIKE $1` — autocomplete
`device_id_trgm_ix`	`events.device`	`WHERE device_id ILIKE $1` — autocomplete
`dim_event_type_trgm_ix`	`events.dim_event_type`	`WHERE ce_type ILIKE $1`
`room_label_trgm_ix`	`events.room`	`WHERE label ILIKE $1`
`person_label_trgm_ix`	`events.person`	`WHERE label ILIKE $1`

These tables hold thousands of rows, not hundreds of millions. events.device additionally carries label, room_id, first_seen, last_seen and event_count; room, person and dim_event_type are (id, label?, last_seen).

Every index in the migration carries a catalog COMMENT naming its query shape, so an index nobody can name gets dropped.

The hourly rollup¶

CREATE MATERIALIZED VIEW events.event_rollup_hourly AS
SELECT date_trunc('hour', occurred_at) AS bucket, ce_type, ce_source,
       coalesce(severity, 'none') AS severity,
       count(*)                                    AS event_count,
       count(*) FILTER (WHERE severity_rank >= 50) AS error_count,
       count(DISTINCT device_id)                   AS device_count,
       avg(metric_value) AS avg_value, min(metric_value) AS min_value,
       max(metric_value) AS max_value
FROM events.cloud_event
WHERE occurred_at >= now() - interval '90 days'
GROUP BY 1,2,3,4
WITH NO DATA;

CREATE UNIQUE INDEX event_rollup_hourly_uk
    ON events.event_rollup_hourly (bucket, ce_type, ce_source, severity);
CREATE INDEX event_rollup_hourly_bucket_ix
    ON events.event_rollup_hourly (bucket DESC);
REFRESH MATERIALIZED VIEW events.event_rollup_hourly;

A materialized view rather than counters maintained by the consumer. Counters incremented by an at-least-once consumer drift on redelivery — the row that ON CONFLICT DO NOTHING correctly declines to write is the row a counter would have double-counted. The MV is recomputed from the fact table, so it is idempotent and authoritative by construction.

event_rollup_hourly_uk is not decoration: a unique index is what makes REFRESH MATERIALIZED VIEW CONCURRENTLY legal. Without it the refresh takes ACCESS EXCLUSIVE and every dashboard read blocks for the duration. event_rollup_hourly_bucket_ix serves the landing-page histogram (WHERE bucket >= $1 ORDER BY bucket DESC).

coalesce(severity, 'none') is required rather than cosmetic: NULL in a unique index column would let two rows with otherwise identical keys coexist, and CONCURRENTLY needs the key to be genuinely unique.

WITH NO DATA followed by an explicit REFRESH keeps the migration fast on a populated database and makes the first population an ordinary, interruptible statement rather than part of the DDL transaction.

The refresh cadence (every ~5 minutes, guarded by pg_try_advisory_lock so replicas do not race) is a scheduled job, not part of the migration. The tripwire that reopens the "MV vs. counters" decision is a refresh taking longer than 60 seconds.

Saved searches¶

CREATE TABLE events.saved_search (
    slug       text PRIMARY KEY,       -- base32(sha256(ast))[0,12]
    ast        jsonb NOT NULL,
    label      text,
    created_at timestamptz NOT NULL DEFAULT now());

Content-addressed, so the table is immutable by construction: the key is the hash of the value. Only filters too long for a querystring land here (?s=k3f9x2mq7z1a); short ones stay in the URL, where they remain readable and hand-editable. Cursors are a separate mechanism and are legitimately opaque — base64url of (occurred_at, event_uid, filterFingerprint), where the fingerprint invalidates a cursor whose filter changed.

How the read path uses all of this¶

Query	Shape	Index
Search page	`SELECT occurred_at, event_uid, ingested_at, ce_id, ce_source, ce_type, ce_subject, device_id, room_id, person_id, severity, severity_rank, metric_value FROM events.cloud_event WHERE … ORDER BY occurred_at DESC, event_uid DESC LIMIT n`	(2)–(5) + partition pruning
Detail	same columns `+ raw`, `WHERE occurred_at = ? AND event_uid = ?`	`cloud_event_pk`
Facets	`WITH cand AS MATERIALIZED (SELECT dims … LIMIT 50000)`, then `GROUP BY GROUPING SETS ((ce_type),(ce_source),(device_id),(room_id),(person_id),(severity),())` `UNION ALL` the tag pass — one statement	whichever of (2)–(11) the filter selects
Tag facet	the `UNION ALL` branch: `cand CROSS JOIN LATERAL unnest(cand.tags)`	(9) via the candidate set
Histogram	`generate_series(…) LEFT JOIN (SELECT date_bin(…), count(*) … GROUP BY 1)`	(2)–(5) + pruning
Result total	`SELECT count(*) FROM (SELECT 1 FROM … LIMIT 10001) t`	as the filter selects

The list projection omits data and raw, so the planner never de-TOASTs payloads for rows the user will not open. Detail fetches them by primary key.

Both facet passes are one statement, and that is a cost decision. They read the same candidate set, so writing them as two statements meant splicing WITH cand AS MATERIALIZED (… LIMIT 50000) into each — and running the capped fact-table scan twice per page view for one panel. A CTE referenced twice inside one statement is evaluated once, so the tag pass now arrives as a UNION ALL branch reading the same tuplestore. FilterAccessPathIT asserts the plan holds exactly one CTE cand and exactly two CTE Scan on cand, and that nothing after the first consumer names the fact table. The tag branch carries a gid of -1 — grouping() over six columns returns 0–63, so a negative value cannot collide — and the tags are re-sorted in Scala, because UNION ALL promises the branches no ordering.

MATERIALIZED on the candidate CTE is not optional, and merging the passes gives it a second reason. Without it the planner may inline the CTE into each grouping set and into the tag branch, re-applying the filter each time and turning one capped scan into seven uncapped ones — deleting the cap's entire purpose. When the cap is reached (50 000 by default, 200 000 at most), every facet count is a lower bound and the UI renders "50,000+" — the same honest approximation Kibana and GitHub ship, and a signed-off product decision rather than a hidden implementation detail. Totals are bounded the same way: SELECT 1 inside the subquery, because the LIMIT can only stop a scan that is producing rows.

The candidate set is whatever the filter selects, in whatever order the plan produces it. There is no ORDER BY inside the CTE and there must not be one — sorting 10⁸ rows to take the newest 50 000 would cost far more than the facets are worth. The consequence is that on a filter with no time bound at all the 50 000 candidates are an arbitrary slice of history rather than a recent one, so the facet counts on the unfiltered landing page describe a different population from the result list beside them. Every filter the UI produces from the filter bar or a histogram bar carries from/until, so this is reachable only from a bare /events. It is listed under open items rather than fixed here because the fix is a decision about what the landing page searches, not about SQL.

The histogram's generate_series skeleton is what makes an empty hour render as a zero bar. Without it a quiet period disappears from the chart and the shape of the data becomes a lie told by omission. The window is re-bounded in the query itself rather than trusted to the caller's filter, so a broader filter cannot bin months of events into the first bucket.

Open items¶

These are provisioned by the migration but not yet read or maintained by any code, and are listed so nobody mistakes DDL for behaviour:

Dimension-table population. events.device, room, person and dim_event_type have no writer, so autocomplete has nothing to complete against yet.
events.saved_search has no reader or writer; long filters are not yet persisted.
An unbounded search does not prune. /events with no query string compiles to WHERE TRUE, so the page, its facet candidate set and its bounded total all read every partition; only the histogram defaults to a window. The LIMITs keep each of them finite, but the facets then describe an arbitrary 50 000 rows rather than the ones on screen. Giving the default page the same 24-hour window the histogram already assumes would fix both, and is a product decision about the landing page rather than a schema one.
severity >= warn has no access path. Index (11) is partial on severity_rank >= 50, which is error. A predicate implies that only at or above 50, so >= error, >= critical and above are index scans and >= warn (40) — a filter one click of the severity facet produces — is a sequential scan of the fact table. FilterAccessPathIT pins all three, so the cliff is recorded rather than discovered during an incident. Moving the index's threshold to 40 would close it, and 50 is the same constant as Severity.Error and cobalt's alert feed: changing it changes what this system calls an alert, which is not a change to make inside a query plan.

The rolling partition job and the MV refresh job used to be listed here and no longer belong: both ship, in modules/persistence/.../maintenance, driven by cobalt's MaintenanceJobs, and events.event_rollup_hourly is read by ferrite's overview page through OverviewRepository.

See the event model for what is stored in raw and why it is the canonical form.