Events
Table for storing individual events sent by devices with media and location data
| Column Name | Data Type | Constraints |
|---|---|---|
| id | bigint | NOT NULL |
| inserted_at | timestamp with time zone | DEFAULT timezone('utc'::text, now()) NOT NULL |
| updated_at | timestamp with time zone | DEFAULT now() NOT NULL |
| message | text | |
| media_url | text | |
| file_path | text | |
| altitude | double precision | DEFAULT 0 NOT NULL |
| heading | double precision | DEFAULT 0 NOT NULL |
| media_type | public.media_type | DEFAULT 'image' NOT NULL |
| device_id | bigint | NOT NULL |
| session_id | bigint | |
| timestamp_observation | timestamp with time zone | DEFAULT timezone('utc'::text, now()) NOT NULL |
| is_public | boolean | DEFAULT false NOT NULL |
| location | extensions.geography(Point,4326) | DEFAULT '0101000020E610000000000000000000000000000000000000' |
| earthranger_url | text | |
| embedded_at | timestamp with time zone | |
| segmented_at | timestamp with time zone | |
| tagged_at | timestamp with time zone | |
| tracked_at | timestamp with time zone | |
| thumbnail_file_path | text | |
| thumbnail_generated_at | timestamp with time zone | |
| proxy_file_path | text | |
| proxy_generated_at | timestamp with time zone | |
| processing_blocked_at | timestamp with time zone | |
| processing_blocked_reason | text | |
| uploaded_by | uuid | FK -> users; stamped from auth.uid() on insert by events_uploaded_by, NULL for device and service uploads, immutable afterwards |
| connectivity_id | bigint | FK -> connectivity, ON DELETE SET NULL |
| sensor_pose_id | bigint | FK -> sensor_poses, ON DELETE SET NULL |
Optional producer-set FKs to the nearest connectivity row and sensor pose; not inferred in the database.
Security Policies (RLS)
RLS enabled. Herd scope: Herd from events.device_id → get_herd_id_by_device_id.
| Operation | has_herd_ability ability key | Other paths |
|---|---|---|
| SELECT | media.read | Owning device API key |
| INSERT | media.write | Owning device API key; approved gateway in herd |
| UPDATE | media.write | Owning device API key; approved gateway in herd |
| DELETE | media.write | Owning device API key |
Platform superadmin: FOR ALL bypass where defined.