# 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: + +
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.