Health metrics
Table for device health metrics (CPU, memory, storage, etc.); one row per metric per timestamp
| Column Name | Data Type | Constraints |
|---|---|---|
| id | bigint | NOT NULL (generated) |
| timestamp | timestamp with time zone | NOT NULL |
| device_id | bigint | NOT NULL |
| source | text | |
| metric_name | text | NOT NULL |
| value | double precision | NOT NULL |
| unit | text | |
| created_at | timestamp with time zone | DEFAULT clock_timestamp() NOT NULL |
Indexes
idx_health_metrics_lookupon(device_id, metric_name, timestamp DESC)for device lookups and time-ordered queries.
Foreign keys
device_id→devices(id)ON UPDATE CASCADE ON DELETE CASCADE
Security Policies (RLS)
RLS enabled. Herd scope: Herd from health_metrics.device_id → get_herd_id_by_device_id.
| Operation | has_herd_ability ability key | Other paths |
|---|---|---|
| SELECT | telemetry.read | Owning device API key; approved gateway in herd |
| INSERT | telemetry.write | Owning device API key; approved gateway in herd |
| UPDATE | telemetry.write | Owning device API key; approved gateway in herd |
| DELETE | telemetry.write | Owning device API key; approved gateway in herd |
Platform superadmin: FOR ALL bypass where defined.
Related function
get_health_metrics_summary(p_device_id bigint, p_lookback_minutes integer DEFAULT 60)— returns per-metric min, max, avg, and count over the lookback window. Uses SECURITY INVOKER (RLS applies).
Metric names
Use snake_case; add new metrics by using new metric_name values (no migration). Examples: cpu_usage_percent, memory_usage_percent, storage_used_gb, disk_usage_percent, cpu_temperature_c, uptime_seconds.