deckgl_tracksolid/trips_deckgl_tracksolid.md

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/:

-- 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;

-- 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;

-- 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):

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)

-- 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

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)

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

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.