Connectivity
Table for recording network connectivity metrics and location data from devices
| Column Name | Data Type | Constraints |
|---|---|---|
| id | bigint | NOT NULL |
| session_id | bigint | |
| inserted_at | timestamp with time zone | DEFAULT now() NOT NULL |
| timestamp_start | timestamp with time zone | NOT NULL |
| signal | double precision | NOT NULL |
| noise | double precision | NOT NULL |
| altitude | double precision | NOT NULL |
| heading | double precision | NOT NULL |
| location | extensions.geography | NOT NULL |
| h14_index | text | NOT NULL |
| h13_index | text | NOT NULL |
| h12_index | text | NOT NULL |
| h11_index | text | NOT NULL |
| battery_percentage | real | |
| battery_volts | real | |
| secondary_battery_percentage | real | |
| secondary_battery_volts | real | |
| device_id | bigint | |
| frequency_hz | real | |
| bandwidth_hz | real | |
| associated_station | text | |
| mode | text | |
| frame_center_location | extensions.geography(Point, 4326) | |
| frame_center_altitude | double precision | |
| frame_center_h14_index | text | |
| frame_center_h13_index | text | |
| frame_center_h12_index | text | |
| frame_center_h11_index | text | |
| base_device_online | boolean |
frame_center_location and frame_center_altitude describe the center of the
camera frame, while location and altitude continue to describe the primary
device. frame_center_h14_index..frame_center_h11_index are the producer-set H3 cells of the frame center, mirroring h14_index..h11_index for location. base_device_online is nullable so older or unavailable telemetry is
distinguishable from an explicit offline state.
Security Policies (RLS)
RLS enabled. Herd scope: Herd from device_id or session's device (CASE in policy).
| Operation | has_herd_ability ability key | Other paths |
|---|---|---|
| SELECT | sessions.read | Device/session API key paths; approved gateway |
| INSERT | sessions.write | Device/session API key paths; approved gateway |
| UPDATE | sessions.write | Device/session API key paths; approved gateway |
| DELETE | sessions.write | Device/session API key paths |
Platform superadmin: FOR ALL bypass where defined.
get_herd_connectivity_heatmap(herd_id, params) requires sessions.read in the herd or platform superadmin. get_connectivity_with_coordinates_by_device_and_timestamp(device_id, timestamp) requires sessions.read in the device's herd, the device's own API key (or its approved gateway), or platform superadmin.