Telemetry Measurements Schema

The first production schema is now a single canonical file: schema/telemetry_schema.sql.

Legacy compatibility migrations were removed because this database has no prior production deployments. The schema file represents the current design directly and runs on an empty PostgreSQL database.

Source worker ingestion is documented in docs/mqtt_ingestion_api.md, with MQTT used there as a concrete example.

A draft note for future counter-style telemetry, including energy and traffic counters, is in docs/energy_counter_draft.md.

Design note

Generic segment tables

Segment tables are now created through:

• telemetry.create_segment_table(p_table_name, p_value_type)

Supported value types are:

• double precision
• boolean
• smallint

The generated tables keep the existing historian shape and protections:

• one open segment per device
• internal device_pk foreign key for future numeric-key joins while keeping device_id for compatibility
• policy_id foreign key to telemetry.metric_policies(policy_id) so every stored interval remembers which ingestion policy produced it
• foreign key to telemetry.devices(device_id) with ON UPDATE/DELETE CASCADE
• non-overlapping tstzrange periods enforced with EXCLUDE USING gist
• covering index on (device_id, start_time DESC) for point/range lookups
• additional GiST index on (device_id, segment_period) for range sampling/overlap queries
• storage tuning via fillfactor and aggressive autovacuum/analyze thresholds

Generic ingestion engine

Runtime ingestion now flows through a single function:

• telemetry.ingest_segment(...)

telemetry.metrics.table_name is now stored as plain text, not regclass. Runtime code normalizes it, validates that it resolves under the telemetry schema, and only then interpolates the quoted identifier into dynamic SQL.

Dynamic SQL is used only to parameterize the target table name. All values stay bound through USING, so the only dynamic part of the hot path is table access.

The generic engine preserves the existing historian semantics:

• append-only watermark enforcement
• advisory locking per (metric, device)
• automatic device provisioning into telemetry.devices
• last_seen updates on successful ingestion
• epsilon/exact consolidation depending on policy
• lazy gap detection
• explicit NULL segments
• query-time synthetic NULL tails

Device registry

telemetry.devices is the canonical device catalog. Ingestion still accepts unseen devices without a separate provisioning step, but it inserts them into the registry before segment writes so foreign keys remain valid.

Deleting or renaming a device is now referentially safe because segment tables and watermark rows cascade from the device registry.

telemetry.devices also has an internal device_pk surrogate primary key. Segment tables keep device_id for API compatibility, but now store device_pk as the internal foreign key.

Metric registry

telemetry.metrics keeps metric_name as the external API identifier, but now uses metric_pk as the internal primary key. Text identifiers remain unique and stable for backward compatibility.

Metric policy

telemetry.metrics now carries both:

• metric_type
• comparison_mode

metric_type and comparison_mode are PostgreSQL enums, so invalid values fail with type-level errors and show up clearly in schema introspection.

Current combinations are:

• numeric -> comparison_mode = 'epsilon'
• boolean -> comparison_mode = 'exact'

Runtime ingestion policy is now versioned in telemetry.metric_policies. The active row is selected by (metric_name, valid_from <= observed_at) with the greatest valid_from, and new segments store that policy_id. Existing metrics are seeded with an initial valid_from = '-infinity' policy row so historical segments stay compatible.

This keeps historical semantics stable when policy parameters such as epsilon, rounding_precision, allow_null, or max_sampling_interval change over time. Query wrappers do not need to re-resolve policy history; they use the policy_id already attached to each segment when they need policy-specific behavior such as open-segment tail cutoff.

New policies are added through telemetry.add_metric_policy(...). telemetry.register_numeric_metric(...) and telemetry.register_boolean_metric(...) still provision the metric and ensure the initial -infinity policy exists.

If a policy changes while a segment is still open, ingestion now splits that segment at policy.valid_from before handling the incoming sample. The synthetic continuation segment starts exactly at the boundary with samples_count = 0, so no interval can silently extend past the policy that generated it.

The schema also allows a future state metric type backed by smallint with comparison_mode = 'exact', without changing the segment engine again.

Query API

The internal table readers are generic:

• telemetry.value_at_from_table(...)
• telemetry.segments_between_from_table(...)
• telemetry.samples_from_table(...)

Public wrappers stay typed because PostgreSQL function return types are static:

• numeric: telemetry.value_at(...), telemetry.metric_segments(...), telemetry.sample_metric(...)
• boolean: telemetry.boolean_value_at(...), telemetry.boolean_segments_between(...), telemetry.boolean_samples(...)

Maintenance

The schema now also includes:

• telemetry.verify_segments(...) for integrity checks
• telemetry.compact_segments(...) for manual zero-gap identical-value compaction
• telemetry.metric_retention_policies as a retention/compaction policy registry
• telemetry.inactive_devices(...) for offline-device detection