Skip to content
Omniglass Omniglass Omniglass

Storage

Partial

This page describes how storage works: the patterns every other leaf’s entities land on (scope, audit, retention, lineage), not a per-table column dump.

Postgres is the relational system of record (entities, events, alarms, actions, audit, config, settings): the record/state/intent lane. It is never a message bus: the live signal travels on NATS JetStream, Postgres is the durable record. Operator mutations and the record/state/intent lane (config, ack/snooze, settings, manual commands) write synchronously through the Storage Gateway.

Column schemas live with each owning feature: samples, events, alarms and actions (alarm / action), commands, config and credentials, core entities and templates, collection, calculations, files, time, and identity and access.

Conventions

Section titled “Conventions”
  • Identity is three columns. id is a uuid: immutable, the primary key, and what every foreign key stores. name is the renameable identifier an operator types and an address carries (the rm215a in boi.17c.rm215a). label is an optional friendly string a human reads (“HQ Boardroom DSP”), and a surface that has none falls back to the name rather than re-casing it. A rename moves name and nothing else, which is why references store the id and why audit_log.resource_id does too. storage.ValidateName is the one validator, applying the one kebab name rule to every table whose declared identity shape bears a name, so a call site cannot skip validation or invent a second rule (ADR-0076, core entities).
  • No tenant_id. Isolation is per-database; no tenant column anywhere. The registries and catalogs (metric_type, property_type, event_type, interface_type, location_type, secret_type, vendor, driver, component_type, system_type, product, standard) carry an official boolean (the per-registry template / org / official scope ladder is future design, property scope): official: true rows are the ship-with canonical set, official: false operator- or org-authored. The boolean is authority, not provenance: a standard ships official: false, installed only if absent (example content a fleet owns); the canonical catalogs, location_type among them since ADR-0106, ship official: true through an authoritative ON CONFLICT DO UPDATE, so a release can correct or withdraw the shared vocabulary (the seed model). An official: true row is never written by an operator, but it is no longer a dead end: an edit forks it into registry_shadow, a registry-agnostic table keyed (registry, row_id) on the shipped row’s own uuid, and reads resolve the shadow over the official row (ADR-0095). One uuid and one name per logical row either way, so no foreign key, walk, or URL learns about the fork; restore is deleting the shadow. component_type is the first adopter and location_type the second.
  • Three storage shapes. Ground-truth records: append-only, immutable, named for what they are (below). There is no telemetry table: samples are published to the JetStream data lane (raw appears only on a collection.failed event or a dev raw-mode tap, samples), and a schedule fire is an event with origin=scheduled. Samples (metric / property) are the typed firehose, log_line and node_log the untyped raw arrival beside them (ADR-0066: no registered name, no catalog gate). Stateful entities and projections (alarm, action) hold state directly; everything “current” is a rebuildable read, views by default. The model is not event-sourced.
  • Provenance and lineage on every sample: provenance (observed / calculated / intended / declared), source (which sensor or path, for observed), and a lineage pointer enforced per provenance by a CHECK (the lineage CHECK). A declared row is an operator’s assertion recorded in the series itself (ADR-0079); the metric lane admits the first three provenances today (no declared-metric writer exists, and its CHECK refuses the row).
  • Ownership is the exclusive-arc, though not one uniform arc: the sample tables, event, and command carry owner_kind (component / system / location / node) plus the matching typed FK and a CHECK (no platform or global arm on a sample); log_line is component-only (a single NOT NULL component_id; a node’s self-logs live in node_log); variable’s arc is platform / component / system / location (no node arm; platform sets all three FKs null); alarm carries no arc, a single NOT NULL component_id, component-local by design today. Full pattern: core entities.
  • A write struct takes the Write suffix; the bare noun is the row: MetricSampleWrite in, MetricSample back, likewise PropertySampleWrite, EventWrite, LogLineWrite. A carrier is named for what it carries: hence the wire message is a TelemetryBatch (ADR-0072).
  • Keys: samples and events use a surrogate id plus ts; each catalog (metric_type, property_type, event_type, command_type) is name-unique with the official boolean deciding authority, and the three ingest catalogs refuse a name a sibling holds; structural entities carry a unique, renameable name over a uuid primary key; a task is content-addressed (sha256 over (interface_id, mode, spec)); a node by its principal_id. Every foreign key stores the target’s primary key, so a rename is free (ADR-0056).
  • A location, system, or component name is unique within its placement, not across the fleet (ADR-0089): each table trades its old global UNIQUE (name) constraint for a set of partial unique indexes, one per placement bucket, plus a plain btree on the bare name column for the ambiguity scan every bare-name resolve runs. component and system both carry three buckets (parent, location, orphan: component_parent_name_key / component_location_name_key / component_orphan_name_key and the matching triple on system), since both carry their own parent_id and location_id. location carries only two (location_parent_name_key / location_root_name_key): it has no location_id column of its own, so its two buckets are parented and root (db/migrations/20260808090000_names_scope_to_placement.sql). A dotted address (boi.17c.415a.$comp.display-1) resolves structurally against these same indexes, one deterministic hop per segment, before any scope or ambiguity check runs; see core entities for the grammar and identity and access for how scope and ambiguity are then decided against the resolved candidate set.

Migrations: three buckets, kept separate

Section titled “Migrations: three buckets, kept separate”

A schema change is authored with dbmate: pure-DDL migrations under db/migrations/, embedded into the binary and applied by the migrate run mode. Two rules hold everywhere: a migration runs exactly once (dbmate keys on the timestamp version, not the contents, so it is never edited after it ships, only followed by a new one), and DDL is idempotent (IF NOT EXISTS, a guarded DO block for a Postgres statement with no IF NOT EXISTS form of its own, e.g. a column rename).

A change that both reshapes the schema and needs default rows for the shape to be usable never mixes the two in one migration. Three buckets, never conflated:

  • Schema migrations (db/migrations/*.sql, dbmate): pure DDL. No seed rows: a schema dump or a future squash silently drops any row a migration inserted, so a migration that seeds data is a landmine for whoever collapses the chain later.

  • Boot seed phase (idempotent upsert on every server start, internal/seed/*.yaml): ship-with reference data, authoritative via ON CONFLICT DO UPDATE for a canonical catalog (a release can correct it, and can withdraw a value it once shipped, which is the half insert-if-absent cannot do at all); operator rows are never touched. location_type seeds this way since ADR-0106, which is what an authoritative seed COSTS: the rows have to be platform-owned, so an operator’s version of one lives in registry_shadow and resolves over it. Narrow carve-out: a seeded table can additionally reconcile its child rows to the declared set every boot, deleting one that dropped out (refusing instead if something still points at it) rather than leaving it in place, but only where BOTH hold: the table has no operator write path (nothing but the seed ever writes a row there) and its rows carry a packed positional ordering where a leftover orphan does not sit inert, it collides with the position a renamed or reordered entry now wants. choice_alternate is the only table this applies to today (ADR-0087). Absent both preconditions, an operator-writable or unordered table keeps the ordinary insert-if-absent rule above.

    A row that is both shipped and operator-owned splits into two columns. The global label rules (label_rule, one row per labelled entity kind) carry default_template and template: the seed writes only the first, authoritatively, and the second is the operator’s, resolved over it. Neither single-column arrangement works, since an authoritative seed stomps the operator on the next restart and a seed-if-absent freezes the shipped default at the first boot. It is the same shipped-values-and-operator-values-live-apart shape the registry fork gives a registry row, at the scale where an overlay table would be more machinery than the three rows are worth (ADR-0098). A derived column is maintained by the gateway and proved by a recompute-and-compare. The generated label is the worked example: it is stored, so sort, filter and search stay in SQL, and the staleness that buys is paid for by an invariant rather than by a trigger (logic lives in Go, never in the database). What that invariant is stopped being an enumeration of write paths when a rule gained the ability to read facts on OTHER rows: it is now a fleet-wide question the gateway answers, PreviewLabelRecompute returning nothing, so a write path nobody thought of fails it rather than a list nobody updated passing (ADR-0100).

  • One-time data backfills (dbmate, data-only): transforming existing operator rows to match a new constraint, run once, and idempotent on a second run (a repeat changes nothing, proven by a test that executes the migration’s up-SQL twice).

Worked example: the product classification floor (#614). Making product.component_type_id and component.product_id both NOT NULL needed all three buckets, landed as three migrations in this order, because reversing the order would either fail (a NOT NULL added before any row satisfies it) or silently orphan existing operator components (a backfill run against a still-optional column has nothing forcing it to run at all):

  1. Schema (nullable): 20260807110000_product_component_type_and_icon.sql adds product.component_type_id (nullable, FK to component_type, on delete restrict) and product.icon (nullable). Pure DDL, safe against any existing row.
  2. Boot seed, then backfill: the boot seed ships the component_type tree (internal/seed/component_types.yaml) and three generic products (internal/seed/products.yaml) pointing at the matching generic types, so the chain a backfill needs already exists by the time it runs. 20260807113000_product_type_backfill.sql is data-only and idempotent (ON CONFLICT (name) DO NOTHING for the generics it also inserts defensively, WHERE component_type_id IS NULL / WHERE product_id IS NULL guards on the updates): it folds kind='vm' to 'app', points every null product.component_type_id at the type matching the product’s kind, and points every null component.product_id at generic-device.
  3. Schema (floor): 20260807116000_product_type_floor.sql sets both columns NOT NULL and narrows the kind check constraint to device | app | service. Pure DDL again, now safe because step 2 already closed every gap it depends on.

Running the chain against a fresh database is the same three steps in the same order: nullable column, then the seed and backfill that make every row satisfy the coming constraint, then the constraint itself. A fresh database and an upgraded one converge on the identical end state, which is the point of keeping the buckets separate rather than reaching for a single migration with an inline UPDATE.

How the records relate

Section titled “How the records relate”

The relationships, not the columns (those live on each owning leaf, linked above).

metricpropertyeventalarmactioncommand calc_ruleevent_rulefire opens · clear resolvesaction_ruleopens an intended valueopens an intended value

The structural and template entities relate as shown on core entities and templates; the collection entities (interface_type / interface / task) on collection.

Ground-truth records

Section titled “Ground-truth records”

The immutable, append-only records: the lineage targets and what a backtest reads, none derived. Columns for audit_log live on audit, session_log on nodes:

  • log_line (a component’s own words, the untyped raw ingest lane, not a sample, component-only, ADR-0066);
  • node_log (a node’s self-logs, the same payload shape without the owner arc, split by origin, ADR-0079);
  • audit_log (operator actions: actor, verb, resource, old -> new; the lineage target for operator writes; secret decrypts always recorded, audit).

The lineage CHECK (the pattern)

Section titled “The lineage CHECK (the pattern)”

Lineage lives on the derived row, no separate execution table: a derived row is the evidence of its rule’s run. The pointer per provenance is enforced, making “intended with no command” impossible at the storage layer. The real four-branch CHECK on property (the metric lane carries the same CHECK minus the declared arm, since no declared-metric writer exists yet):

CHECK (
     (provenance = 'observed'   AND event_id IS NULL AND command_id IS NULL)
  OR (provenance = 'calculated' AND source_rule IS NOT NULL AND event_id IS NULL AND command_id IS NULL)
  OR (provenance = 'intended'   AND command_id IS NOT NULL AND source_rule IS NULL)
  OR (provenance = 'declared'   AND source_rule IS NULL AND event_id IS NULL AND command_id IS NULL)
)

Observed and calculated are distinguished by the provenance column, not a pointer-presence trick. An intended value names its command (command_id), not the event derived from it; the caused event stays stamped but optional (ADR-0079). Three layers: the CHECK enforces which pointers are populated, foreign keys enforce the ids are real, the app enforces the value conforms to the catalog’s data_type and validation.

The trace columns live beside the lineage pointers, but not on the sample tables: event carries correlation_id and source_event_id (plus source_log_line_id and derived_by_rule_id), log_line and node_log carry correlation_id; metric and property carry none today. Orthogonal to the lineage CHECK.

Current value and projections: views by default

Section titled “Current value and projections: views by default”

alarm and action are stateful entities holding current state in a real table (not event-sourced). Everything else that is “current state” is a read model, default a plain SQL view or a per-series indexed read (always-correct, never stale, zero maintenance); a worker-maintained table is a measured optimization, earned only when a read profile shows the derived read too slow. The schema holds zero SQL views and zero maintained caches today: the shipped current-value read is the latest series row, derived on read; the once-built latest-value cache retired with the fold (ADR-0079).

A series read carries an owner index for the arc it reads

Section titled “A series read carries an owner index for the arc it reads”

“Derived on read” only stays cheap while the read is a per-series indexed read rather than a scan of the series, so property carries one partial owner index per arc a read is built on: property_owner_idx (component_id, property_type_id, instance, ts desc) for the component arc, and property_system_owner_idx (property_type_id, system_id, id desc) for the system arc, which the two health reads take, the bulk verdict read behind GET /systems:health and the location rollup a recompute pays per location (#725). Each is partial on its arc column, so the telemetry lane pays nothing for an index it never uses: a component-owned insert does not touch the system index at all (measured: 150,000 of them cost the same to the millisecond with it and without it), while the system arc’s own writes, a health transition and only on a transition, pay about 2.7us a row. Adding it took the bulk read over 1,500 systems from 51 ms to 10 ms, and the location rollup from 45 ms to 0.7 ms, at 1,521,600 property rows.

An index existing is not an index being reached, and the ways a read stops reaching one leave pg_indexes reporting it present throughout, so both reads are held to reaching it by an access-path assertion rather than a catalog test (ADR-0094 as amended, and test-driven for the instrument). The next arc that grows a bulk read of the series adds its index the same way: measured first, guarded second.

Retention: the provenance-aware floor

Section titled “Retention: the provenance-aware floor”

Retention as a feature is unbuilt, but its one non-negotiable rule shipped first, as the PruneSamples gateway primitive (ADR-0080): a prune deletes series rows older than a cutoff from both sample tables, except any declared row (an operator’s assertion is the whole truth however old, not a sample) and except the latest row of every series (a prune must not erase a current value). No caller wires it yet; any future retention feature calls this primitive rather than writing its own DELETE, so the first purge cannot silently destroy a declared value or a current reading.

The Storage Gateway and tiering

Section titled “The Storage Gateway and tiering”

The Storage Gateway is the only door to the database (no direct access, no PostgREST), and it injects IAM scope per action: every query carries visible_set(P, action) for the specific action it performs, so a read filters by read-scope and an :ack write by ack-scope. A write whose action-scoped predicate matches 0 rows surfaces as a 403 or 404, never a silent success, matching the up-front canDo decision (identity and access). Per-database isolation (one database per tenant, paired one-to-one with one NATS account) means no tenant context to set. Scope arrives as an explicit per-call scope.Set argument; the named three-mode contract (scoped / node / system) is the Design formalization of that convention, not a built mode switch. The CDC publisher reads committed changes from the WAL, a replication-protocol stream beneath the table surface, not a second path around the Gateway. Every read and write goes through the Gateway, so the physical backend swaps beneath it:

  • default: Postgres for everything (samples, ground-truth records, views, registries). Postgres is BYO today.
  • blobs: opaque bytes (a firmware image, a config dump, a capture, later a large log_line body or a collection.failed raw payload) live in the content-addressed blob store, a blob.Store seam behind the same gateway. The default pgblobs backend holds bytes inline in Postgres; a row references a blob by its sha256, never inline bytes.

The gateway also accepts a query tracer (storage.WithQueryTracer), installed on the pool’s connection config so it observes every statement the gateway issues, whichever method issued it. That whole-pool reach is the point: the read paths query the pool directly, so a wrapper around a call argument cannot see them. It is where an OpenTelemetry pgx tracer attaches, and it is the seam the test harness counts round trips through, which is how a LIST is held to a constant number of queries rather than an N+1 (counting round trips).