47c86c9d7a
Initial implementation of the public trips dashboard: - db/migrations/001..005: read-only viz_anon role + thin trips_viz_v1 view + three SECURITY DEFINER RPCs (trips_for_day, trips_for_range, list_cost_centres). Builds path on demand from position_history; coalesces missing cost_centre to 'Unassigned'. Smoke-tested against staging: 982 trips / 13 cost centres for 2026-04-29. - compose/: PostgREST v12 service + trips_web Caddy service. CORS allow-listed to the web FQDN; viz_anon role is the only authorization. - web/: Vite + React + TS SPA. deck.gl TripsLayer animated over PathLayer (whole route in low opacity), Mapbox GL dark base map, Zustand store, TanStack Query for fetching. Sidebar = date controls + cost-centre multi-select + vehicle drilldown. Timebar = scrubber with 1x/10x/60x/600x speeds. tsc + vite build clean. - README + design doc updated to match the verified schema (path lives in tracksolid.position_history, vehicle key is imei, no down-sampling needed at observed volume).
353 lines
18 KiB
Markdown
353 lines
18 KiB
Markdown
# Trips Visualization — Daily TripsLayer by Cost Centre
|
||
|
||
## Context
|
||
|
||
`tracksolid_db` (PostgreSQL 16 + TimescaleDB on `stage.rahamafresh.com:5433`) already powers a Dekart instance for ad-hoc map exploration (see `compose/docker-compose.yaml`). The user wants a **purpose-built, public visualization** that animates a day's vehicle trips, colored and filtered by cost centre, with a 24-hour time scrubber and per-vehicle drill-down. Dekart's SQL-driven Kepler.gl UI is too rigid for this — they want a custom React app driving deck.gl directly.
|
||
|
||
Confirmed answers from clarifying questions:
|
||
|
||
| Decision | Answer |
|
||
| --- | --- |
|
||
| Delivery | Custom deck.gl React app (not Dekart, not raw Kepler.gl) |
|
||
| Department linkage | Via vehicle / device (`devices.imei` → `devices.cost_centre`) |
|
||
| Trip shape | Path is **not stored** on `trips` — must be reconstructed from `tracksolid.position_history` per trip window |
|
||
| Daily volume | 172 vehicles, ~900 trips/day, ~74k position pings/day (avg 82 pings/trip) |
|
||
| Backend | PostgREST auto-API on `tracksolid_db` |
|
||
| UX controls | Date picker (single day) + date range + cost-centre multi-select + vehicle drill-down |
|
||
| Auth / hosting | Public dashboard, no login (Coolify deployment) |
|
||
| Grouping | `cost_centre` only — no `department` column exists. NULL/blank coalesced to `'Unassigned'` and shown by default. |
|
||
|
||
Outcome: a public URL (e.g. `trips.rahamafresh.com`) where anyone can pick a date or range, toggle cost centres, scrub a time slider, and watch animated trip paths replay.
|
||
|
||
---
|
||
|
||
## Schema findings (verified against the live DB)
|
||
|
||
**Source tables (`tracksolid` schema):**
|
||
|
||
- `trips(id bigint, imei text, start_time tstz, end_time tstz, start_geom, end_geom, distance_km, avg_speed_kmh, …)` — 6,761 rows total, ~900/day. **No path column.**
|
||
- `position_history(imei text, gps_time tstz, geom geometry, lat, lng, speed, …)` — TimescaleDB hypertable, ~74k rows/day. This is where the actual GPS path lives.
|
||
- `devices(imei text PK, vehicle_name, vehicle_number, cost_centre, device_group, depot_geom, …)` — 172 rows. `cost_centre` (British spelling) has 17 distinct values: `isp` (42), `osp` (39), `fds` (22), `roll out`, `general`, `regional`, `osp patrol`, `personal`, `mtn`, `planning`, `deliveries`, `management`, `airtel`, `qehs`, plus ~22 NULL/blank.
|
||
- `v_fleet_trace` — existing view that segments `position_history` into trips by speed + 5-min idle gap. Useful as a reference for window functions; not used directly.
|
||
- `dwh_gold.dim_vehicles` — only `(vehicle_key, imei, vehicle_number, is_active)`. **Not useful** for cost-centre data; we use `tracksolid.devices` directly.
|
||
|
||
**Implication for the design:** the path for each trip is built on-the-fly by joining `position_history` to `trips` on `imei` between `start_time` and `end_time`. Wire payload at full GPS resolution is ~1.8 MB raw / ~600 KB gzipped per day — small enough that **no `ST_SimplifyPreserveTopology` down-sampling is needed** at this volume.
|
||
|
||
---
|
||
|
||
## Architecture
|
||
|
||
```
|
||
┌─────────────────────┐ ┌────────────────────┐ ┌──────────────────────┐
|
||
│ React + deck.gl SPA │ HTTPS │ PostgREST (read) │ SQL │ tracksolid_db │
|
||
│ (Vite, TS) │ ────► │ Coolify service │ ───► │ • new view + │
|
||
│ • TripsLayer │ │ role: viz_anon │ │ RPCs in public. │
|
||
│ • PathLayer │ │ /rpc/trips_for_day │ │ • untouched: │
|
||
│ • Filters / slider │ │ │ │ tracksolid.* │
|
||
└─────────────────────┘ └────────────────────┘ └──────────────────────┘
|
||
▲ ▲
|
||
│ Mapbox GL base tiles │ host.docker.internal:5433
|
||
▼ │ (read-only role on public schema)
|
||
┌─────────────────────┐ │
|
||
│ Mapbox tiles (pk.*) │ │
|
||
└─────────────────────┘ ▼
|
||
(no schema changes to
|
||
tracksolid.*; only new
|
||
public.* view + RPC + role)
|
||
```
|
||
|
||
Three deployable units, all in the same Coolify project alongside the existing Dekart stack:
|
||
|
||
1. **DB layer (additive)** — new `public.trips_viz_v1` view + RPCs + `viz_anon` role inside `tracksolid_db`. **No mutations to existing `tracksolid.*` tables.**
|
||
2. **API layer** — PostgREST as a tiny container, read-only against the new view + RPCs.
|
||
3. **Web layer** — static React build served by Caddy under a public FQDN.
|
||
|
||
---
|
||
|
||
## Component design
|
||
|
||
### 1. Database layer (additive, in `tracksolid_db`)
|
||
|
||
Three migrations under `db/migrations/`:
|
||
|
||
```sql
|
||
-- 001_viz_anon_role.sql
|
||
CREATE ROLE viz_anon NOLOGIN;
|
||
GRANT USAGE ON SCHEMA public TO viz_anon;
|
||
-- We never grant on schema tracksolid; the role only sees what we expose in public.*
|
||
|
||
-- 002_trips_viz_view.sql
|
||
-- Thin metadata view (no path). Used as a stable contract + perm boundary.
|
||
CREATE OR REPLACE VIEW public.trips_viz_v1 AS
|
||
SELECT
|
||
t.id AS trip_id,
|
||
t.imei,
|
||
d.vehicle_name,
|
||
d.vehicle_number,
|
||
COALESCE(NULLIF(TRIM(d.cost_centre), ''), 'Unassigned') AS cost_centre,
|
||
t.start_time,
|
||
t.end_time,
|
||
t.distance_km,
|
||
t.avg_speed_kmh,
|
||
t.max_speed_kmh
|
||
FROM tracksolid.trips t
|
||
JOIN tracksolid.devices d ON d.imei = t.imei
|
||
WHERE t.start_time IS NOT NULL
|
||
AND t.end_time IS NOT NULL;
|
||
|
||
GRANT SELECT ON public.trips_viz_v1 TO viz_anon;
|
||
```
|
||
|
||
```sql
|
||
-- 003_trips_for_day_rpc.sql
|
||
-- RPC that returns trips + reconstructed path for a single date.
|
||
CREATE OR REPLACE FUNCTION public.trips_for_day(
|
||
p_date date,
|
||
p_cost_centres text[] DEFAULT NULL
|
||
) RETURNS TABLE (
|
||
trip_id bigint,
|
||
imei text,
|
||
vehicle_name text,
|
||
vehicle_number text,
|
||
cost_centre text,
|
||
start_time timestamptz,
|
||
end_time timestamptz,
|
||
distance_km numeric,
|
||
path_geojson json,
|
||
timestamps_rel int[] -- seconds since trip start, per vertex (for TripsLayer)
|
||
)
|
||
LANGUAGE sql STABLE AS $$
|
||
WITH day_trips AS (
|
||
SELECT v.* FROM public.trips_viz_v1 v
|
||
WHERE v.start_time::date = p_date
|
||
AND (p_cost_centres IS NULL OR v.cost_centre = ANY(p_cost_centres))
|
||
)
|
||
SELECT
|
||
dt.trip_id,
|
||
dt.imei,
|
||
dt.vehicle_name,
|
||
dt.vehicle_number,
|
||
dt.cost_centre,
|
||
dt.start_time,
|
||
dt.end_time,
|
||
dt.distance_km,
|
||
ST_AsGeoJSON(ST_MakeLine(ph.geom ORDER BY ph.gps_time))::json AS path_geojson,
|
||
array_agg(EXTRACT(EPOCH FROM ph.gps_time - dt.start_time)::int
|
||
ORDER BY ph.gps_time) AS timestamps_rel
|
||
FROM day_trips dt
|
||
JOIN tracksolid.position_history ph
|
||
ON ph.imei = dt.imei
|
||
AND ph.gps_time BETWEEN dt.start_time AND dt.end_time
|
||
AND ph.geom IS NOT NULL
|
||
GROUP BY dt.trip_id, dt.imei, dt.vehicle_name, dt.vehicle_number,
|
||
dt.cost_centre, dt.start_time, dt.end_time, dt.distance_km
|
||
HAVING count(ph.geom) >= 2; -- need at least 2 points for a LineString
|
||
$$;
|
||
|
||
GRANT EXECUTE ON FUNCTION public.trips_for_day(date, text[]) TO viz_anon;
|
||
```
|
||
|
||
```sql
|
||
-- 004_trips_for_range_rpc.sql
|
||
-- Multi-day variant; capped at 14 days at the API layer to bound payload size.
|
||
CREATE OR REPLACE FUNCTION public.trips_for_range(
|
||
p_start date, p_end date, p_cost_centres text[] DEFAULT NULL
|
||
) RETURNS TABLE (...same shape...)
|
||
LANGUAGE sql STABLE AS $$
|
||
-- identical body, with WHERE start_time::date BETWEEN p_start AND p_end
|
||
$$;
|
||
|
||
-- 005_list_cost_centres_rpc.sql
|
||
CREATE OR REPLACE FUNCTION public.list_cost_centres()
|
||
RETURNS TABLE (cost_centre text, vehicle_count bigint)
|
||
LANGUAGE sql STABLE AS $$
|
||
SELECT COALESCE(NULLIF(TRIM(d.cost_centre), ''), 'Unassigned') AS cost_centre,
|
||
count(*) AS vehicle_count
|
||
FROM tracksolid.devices d
|
||
GROUP BY 1
|
||
ORDER BY 2 DESC;
|
||
$$;
|
||
GRANT EXECUTE ON FUNCTION public.list_cost_centres() TO viz_anon;
|
||
```
|
||
|
||
**Why thin view + path-building RPC instead of one fat view?** The path build is expensive (`ST_MakeLine` + `array_agg` over `position_history`) — we don't want it firing on every metadata-only query. The view is the perm/contract surface; RPCs do the heavy lifting only when filtered to a date.
|
||
|
||
**Why `cost_centre` (British) and not `cost_center`?** That's the actual column name in `tracksolid.devices`. We mirror it verbatim throughout — view, RPC params, frontend types — so there's never a translation layer to debug.
|
||
|
||
**Why no `ST_SimplifyPreserveTopology`?** A full day at full GPS resolution is ~1.8 MB / ~600 KB gzipped. Simplification would save bytes we can spend without thinking, and risks visual artifacts when TripsLayer interpolates between vertices. Keep raw.
|
||
|
||
### 2. API layer
|
||
|
||
Add to `compose/docker-compose.yaml` (alongside existing `dekart` service):
|
||
|
||
```yaml
|
||
postgrest:
|
||
image: postgrest/postgrest:v12.2.0
|
||
restart: always
|
||
expose: ["3000"]
|
||
extra_hosts:
|
||
- "host.docker.internal:host-gateway"
|
||
environment:
|
||
PGRST_DB_URI: postgres://${VIZ_DATA_USER:-viz_anon}:${VIZ_DATA_PASSWORD}@host.docker.internal:5433/${DEKART_DATA_DB:-tracksolid_db}?sslmode=disable
|
||
PGRST_DB_SCHEMAS: public
|
||
PGRST_DB_ANON_ROLE: viz_anon
|
||
PGRST_OPENAPI_SERVER_PROXY_URI: https://api.trips.rahamafresh.com
|
||
PGRST_DB_MAX_ROWS: 10000 # safety cap
|
||
PGRST_SERVER_CORS_ALLOWED_ORIGINS: https://trips.rahamafresh.com
|
||
```
|
||
|
||
Coolify points the public FQDN `api.trips.rahamafresh.com` → `postgrest:3000`. No auth — the `viz_anon` role itself is the authorization (it can only see `public.trips_viz_v1` + the three RPCs).
|
||
|
||
`viz_anon` needs a login password set with `ALTER ROLE viz_anon LOGIN PASSWORD '…'` separately (`NOLOGIN` was shown in the migration to make grants explicit; PostgREST needs `LOGIN`).
|
||
|
||
### 3. Web layer (custom React + deck.gl SPA)
|
||
|
||
Tech stack:
|
||
|
||
- **Vite + React 18 + TypeScript**.
|
||
- **deck.gl** (`@deck.gl/react`, `@deck.gl/layers`, `@deck.gl/geo-layers`) for `TripsLayer` and `PathLayer`.
|
||
- **react-map-gl** + Mapbox GL JS — base map (reuse existing `DEKART_MAPBOX_TOKEN`).
|
||
- **TanStack Query** — fetch + cache; essential when toggling cost centres fires repeated calls.
|
||
- **Zustand** — cross-component UI state (date, selected cost centres, currentTime, focused imei).
|
||
- **date-fns** for date math; **shadcn/ui** for picker / multi-select / slider primitives.
|
||
|
||
Component tree:
|
||
|
||
```
|
||
src/
|
||
App.tsx — layout shell: <MapView /> + <Sidebar /> + <Timebar />
|
||
store/
|
||
useVizStore.ts — Zustand: { date, range, costCentres[], focusedImei, currentTime, playing, speed }
|
||
hooks/
|
||
useTripsForDay.ts — TanStack Query → /rpc/trips_for_day
|
||
useCostCentres.ts — TanStack Query → /rpc/list_cost_centres
|
||
useAnimationLoop.ts — requestAnimationFrame → store.setCurrentTime
|
||
components/
|
||
MapView.tsx — DeckGL + react-map-gl; builds [PathLayer, TripsLayer]
|
||
Sidebar/
|
||
DateControls.tsx — single-day vs range toggle
|
||
CostCentreFilter.tsx — multi-select with color swatches + per-bucket vehicle count
|
||
VehicleDrilldown.tsx — list of vehicles inside current filter; click = isolate that imei
|
||
Legend.tsx — cost_centre → color
|
||
Timebar/
|
||
TimeSlider.tsx — 0–86400s slider, play/pause, 1×/10×/60×/600× speed
|
||
layers/
|
||
buildLayers.ts — pure fn: (trips, currentTime, focusedImei) → Layer[]
|
||
lib/
|
||
api.ts — typed PostgREST client (no auth header)
|
||
color.ts — stable hash(cost_centre) → palette index
|
||
decode.ts — GeoJSON LineString → Float32Array of coords for TripsLayer
|
||
types/
|
||
Trip.ts — { trip_id, imei, vehicle_name, vehicle_number, cost_centre,
|
||
start_time, end_time, distance_km,
|
||
path_geojson: { type:'LineString', coordinates:[[lng,lat],…] },
|
||
timestamps_rel: number[] }
|
||
```
|
||
|
||
**Layer composition:** at any moment two layers stack:
|
||
|
||
1. `PathLayer` — full route at 30% opacity for the *focused* set (selected cost centres + drilled imei). Users see the whole route, not just the comet head.
|
||
2. `TripsLayer` — animated comets driven by `currentTime` (relative seconds since 00:00 of the selected date, **not** since trip start — so we offset each trip's `timestamps_rel` by `EXTRACT(EPOCH FROM start_time - midnight)` on the client). `trailLength` defaults to 600 s (~10 min tail), exposed as a slider.
|
||
|
||
`getColor` maps `cost_centre` → 12-color categorical palette; `'Unassigned'` always maps to a neutral grey (`#888`) so the gap is visually obvious without yelling.
|
||
|
||
**Performance budget at observed scale (172 vehicles × ~900 trips × ~82 pts):**
|
||
|
||
- Wire payload: ~600 KB gzipped per day — load once per (date, costCentres) tuple, cache via TanStack Query.
|
||
- GPU memory: TripsLayer holds positions + timestamps buffers, ~600 KB each — trivial on any laptop.
|
||
- Fetch strategy: prefetch tomorrow's date on idle.
|
||
|
||
### 4. Deployment
|
||
|
||
- New service `postgrest` in `compose/docker-compose.yaml`.
|
||
- New service `trips-web` — static Caddy container serving `dist/` from CI build.
|
||
- Two new FQDNs in Coolify: `trips.rahamafresh.com` (web), `api.trips.rahamafresh.com` (PostgREST).
|
||
- New env vars: `VIZ_DATA_USER`, `VIZ_DATA_PASSWORD`, `VITE_API_URL`, `VITE_MAPBOX_TOKEN`.
|
||
|
||
---
|
||
|
||
## Files to create / modify
|
||
|
||
### New
|
||
- `db/migrations/001_viz_anon_role.sql`
|
||
- `db/migrations/002_trips_viz_view.sql`
|
||
- `db/migrations/003_trips_for_day_rpc.sql`
|
||
- `db/migrations/004_trips_for_range_rpc.sql`
|
||
- `db/migrations/005_list_cost_centres_rpc.sql`
|
||
- `web/` — full Vite + React + TS scaffold (package.json, vite.config.ts, tsconfig.json, src/*)
|
||
- `web/Dockerfile` — multi-stage build → Caddy
|
||
- `web/Caddyfile`
|
||
- `compose/postgrest.env.example`
|
||
|
||
### Modify
|
||
- `compose/docker-compose.yaml` — add `postgrest` and `trips_web` services.
|
||
- `compose/.env.example` — add `VIZ_DATA_USER`, `VIZ_DATA_PASSWORD`, `VITE_API_URL`, `VITE_MAPBOX_TOKEN`.
|
||
|
||
### Reuse (do not modify)
|
||
- Existing `dekart` service in `compose/docker-compose.yaml` — runs alongside, untouched.
|
||
- Existing `DEKART_MAPBOX_TOKEN` — same token works for the web app's Mapbox GL.
|
||
- Existing `grafana_ro` role — *not* reused; we create a stricter `viz_anon` role to limit the public API's surface.
|
||
|
||
---
|
||
|
||
## Verification
|
||
|
||
### 1. Database smoke test (run after migrations)
|
||
|
||
```sql
|
||
-- pick a recent date with traffic
|
||
SELECT count(*) AS trip_rows,
|
||
count(DISTINCT cost_centre) AS cost_centres,
|
||
count(DISTINCT imei) AS vehicles,
|
||
sum(jsonb_array_length(path_geojson->'coordinates')) AS total_vertices
|
||
FROM public.trips_for_day(current_date - 1);
|
||
-- expect: trip_rows ~900, cost_centres ≥ 10, vehicles 50–150, total_vertices ~70k
|
||
|
||
SELECT cost_centre, vehicle_count FROM public.list_cost_centres();
|
||
-- expect 'Unassigned' present plus the 17 known buckets
|
||
```
|
||
|
||
### 2. PostgREST smoke test
|
||
|
||
```bash
|
||
docker compose up -d postgrest
|
||
curl -s "http://localhost:3000/rpc/list_cost_centres" | jq '.[].cost_centre'
|
||
curl -s "http://localhost:3000/rpc/trips_for_day?p_date=2026-04-29" | jq '.[0]'
|
||
# expect a JSON object with trip_id, imei, cost_centre, path_geojson, timestamps_rel
|
||
|
||
curl -s -X POST "http://localhost:3000/rpc/trips_for_day" \
|
||
-H 'content-type: application/json' \
|
||
-d '{"p_date":"2026-04-29","p_cost_centres":["isp","osp"]}' | jq 'length'
|
||
# expect a non-zero count, less than the unfiltered total
|
||
```
|
||
|
||
### 3. Web app end-to-end (dev)
|
||
|
||
```bash
|
||
cd web && pnpm dev
|
||
# Visit http://localhost:5173, then verify:
|
||
# – Cost-centre legend lists all 17 buckets + 'Unassigned' (greyed)
|
||
# – Date picker defaults to yesterday; switching dates triggers fresh fetch
|
||
# – Toggling 'isp' alone narrows the map to ~42 vehicles' trips
|
||
# – Clicking a vehicle in drill-down isolates only that imei's path (Path + Trips layers both filter)
|
||
# – Time slider scrubs 00:00–24:00; play button animates at 60× by default
|
||
# – Range mode toggle switches the slider to multi-day and calls /rpc/trips_for_range
|
||
# – No console errors; Mapbox base map renders; deck.gl canvas overlays cleanly
|
||
```
|
||
|
||
### 4. Production deploy check
|
||
|
||
```bash
|
||
curl -I https://api.trips.rahamafresh.com/ # 200 + CORS allow trips.rahamafresh.com
|
||
# Visit https://trips.rahamafresh.com — verify the same checks as §3.
|
||
```
|
||
|
||
---
|
||
|
||
## Open items deferred to implementation
|
||
|
||
- **Trail length default** — currently 600 s; expose as a slider in case 10-minute tails feel too long for short urban trips.
|
||
- **Color palette** — start with deck.gl's 12-color categorical palette + neutral grey for `'Unassigned'`; allow `config/cost-centre-colors.json` to lock specific brand colors later.
|
||
- **Range cap** — `trips_for_range` capped at 14 days at the API layer; raise once real usage shows we need more.
|
||
- **Index check** — confirm `position_history` has an index on `(imei, gps_time)`; if not, the per-trip path subquery will be slow on cold cache. (Likely already present since it's a Timescale hypertable, but verify with `\d+ tracksolid.position_history`.)
|
||
- **`viz_anon` LOGIN** — set its password via `ALTER ROLE viz_anon LOGIN PASSWORD '…'` after migrations; not committed to git.
|