Integration — Database¶
Public · Status: 🔄 proposed
1. Purpose¶
Query historical measurements with SQL — for BI tools (Power BI, Metabase, Looker) and ad-hoc analytics. Best when your team already lives in SQL and wants to join measurements with your own data.
2. When to use it¶
- You want to build dashboards/reports over months of measurements.
- You prefer SQL joins over API pagination.
3. How it works (🔄 proposed)¶
You connect a read-only PostgreSQL role against a read replica — no impact on production ingest. Access is provisioned per organization (request it with your API key from the dashboard / team); you receive a host, database, username, and password.
- Scoping — you only see your organization's rows. This is enforced by row-level security bound to your role, surfaced through a stable per-org schema of views (you query views, not the raw internal tables).
- Stable public views — the measurement entities follow the published data model:
| View | One row per | Key columns |
|---|---|---|
convoys |
convoy passage | convoy_id, segment_id, direction, started_at, ended_at, number_of_cars |
car_measurements |
car within a convoy | convoy_id, position, car_id, volume_m3, length_m, confidence, rfid_id, measured_at |
car_history |
reconstructed car pairing | car_id, segment_id, loaded_volume_m3, empty_volume_m3, net_volume_m3, paired_at |
View names/columns are versioned and kept stable; the underlying internal schema may change beneath them. - Connection — standard PostgreSQL over TLS; point any BI tool (Power BI, Metabase, Looker, dbt) at it.
For a warehouse-native pattern, pair this with S3 export and load the bundles into your own lake.