Database Schema Overview
Overview of the Scout database schema with entity relationship diagram
Database Schema Overview
The Scout database consists of interconnected tables that manage devices, events, users, and spatial data. The diagram below shows the table relationships.
Authorization (migrations 109–114): Herd access uses per-herd roles (herd_roles) and an ability matrix (herd_role_abilities → abilities). RLS and SECURITY DEFINER RPCs call has_herd_ability(user_id, herd_id, ability_key) on active users_roles_per_herd rows. The legacy public.role enum and has_good_* helpers are removed.
Each schema page lists the exact ability key checked per operation (SELECT / INSERT / UPDATE / DELETE). See Client abilities tokens for UI-only JWT gating.
System ability map
| Ability key(s) | Tables / buckets |
|---|---|
herds.read | herds (membership baseline; also the fallback for realtime topics), software_build_artifacts bucket and catalog tables when held in any herd (has_view_role_any_herd) |
herds.write | herds.document_expiry_warning_period_days only |
devices.read / .write | devices, parts, certificates, maintenance |
devices.api_keys.read | load_api_keys / load_api_keys_batch RPCs (admin-only by default) |
media.read / .write | events, artifacts, tags, embeddings_vertex_multimodal_001, segmentations_sam3, herd_media_usage, models_per_jobs_per_herd; events, artifacts, dji_flight_records buckets |
sessions.read / .write | sessions, session_incidents, operators, observations, connectivity; get_herd_connectivity_heatmap and get_connectivity_with_coordinates_by_device_and_timestamp RPCs |
telemetry.read / .write | heartbeats, health_metrics, sensor_poses, battery_cycle_counts; analyze_device_heartbeats, analyze_herd_device_heartbeats, get_herd_uptime_summary RPCs |
map.read / .write | pins, layers, zones, actions, plans |
providers.read / .write | providers except the key column, which signed-in users cannot select (admin-only by default); get_provider_key RPC is superadmin-only |
chat.binary | chat select and insert |
herd_allowed_domains.read / .write | herd_allowed_domains |
users_roles_per_herd.read / .write | users_roles_per_herd; .write is also required to invite or assign a role that grants it, and to manage teammates' user_earthranger_ids |
herd_invitations.read / .write | herd_invitations |
credentials.read / .write | credentials |
realtime_streams.read / .write | realtime stream tokens |
contacts.read / .write | contacts |
maintenance_requests.read / .write | maintenance_requests |
operating_contexts.read / .write | operating_contexts and their documents |
herd_documents.*, device_documents.*, user_documents.*, product_documents.* | documents by owner type |
user_documents.status.read | user_document_statuses view (status without file details) |
document_approve.binary | approval columns on user documents |
user_last_activity.read | user_last_activity |
Device API keys bypass abilities for rows tied to their own device or herd. content.read / content.write are deprecated grouping keys retained in the catalog; no policy checks them.
Ability-gated RPCs also pass platform superadmins and the service role. Maintenance RPCs with no caller check (ack_queue_message, expire_herd_invitations, seed_herd_default_roles, the orphaned-session and session fix/preview functions, auto_assign_session_post_approver, expired_phone_upload_paths) are executable by the service role only.
Key Relationships
- Users create Devices and Pins; Points_for_user tracks per-user performance balance; User_last_activity records last platform activity
- Herds are created by platform superadmins; contain Devices, Herd_allowed_domains, Herd_invitations, and Herd_media_usage; Herd_roles define roles, seeded from Platform_default_roles on creation; Users_roles_per_herd links users to a
herd_role_id - Devices generate Sessions, Events, Connectivity, and Health metrics data
- Zones trigger automated Actions for spatial monitoring
- Events and Artifacts can contain Tags, Segmentations, and Embeddings
- Sessions can contain Observations
- Analysis jobs contain Analysis tasks for pipeline usage tracking
- Parts are scoped to a Device or a Herd, identified by a Product number, calibrated by Calibrations, and record Sensor poses
- Manufacturers make Products, which have many Product numbers (the catalog
parts.product_numberreferences) - Localizations localize Herds, Issuers, and Document templates; Issuers and templates classify Documents (owned by a user, herd, device, or product)
- Maintenance requests target a Device or Part; Contacts are per-herd