bogdan Initial commit

0c3ad8b 2 weeks ago History

# 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