Adds migrations 006/007/008 that wrap tracksolid.v_trips_enriched
(introduced upstream in tracksolid_timescale_grafana_prod migration 09)
and DROP+CREATE the trips_for_day / trips_for_range RPCs to return the
new metadata columns: vehicle_plate, start_address, end_address,
driving_time_s, idle_time_s, fuel_consumed_l, and daily_seq.
Path/timestamps logic is unchanged — position_history is still scanned
to produce the per-vertex timestamps_rel that drive deck.gl TripsLayer
animation. route_geom can't replace this since it carries no per-vertex
time, but the GIST index on it is available for future area filters.
Day filter switches from start_time::date to v.trip_date_eat (the
Africa/Nairobi local date already computed by v_trips_enriched), which
fixes the latent bug where trips spanning midnight UTC could be
misbucketed.
Frontend Trip type still receives all original fields in compatible
positions; the new fields are additive and ignored by the existing
TripsLayer code.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
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).
