Build a fleet tracking dashboard production guide

Enable PostGIS, create a current-state table and an append-only observation table, and add indexes for vehicle identity, timestamps, and spatial predicates. The exact schema should reflect your source’s identifiers and tenancy model.

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE vehicle_current (
  vehicle_id text PRIMARY KEY,
  observed_at timestamptz NOT NULL,
  received_at timestamptz NOT NULL,
  position geography(Point, 4326) NOT NULL,
  speed_mps double precision,
  heading_deg double precision,
  status text NOT NULL DEFAULT 'unknown',
  source_version bigint NOT NULL DEFAULT 0
);

CREATE INDEX vehicle_current_position_gix
  ON vehicle_current USING GIST (position);

CREATE TABLE vehicle_observation (
  event_id text PRIMARY KEY,
  vehicle_id text NOT NULL,
  observed_at timestamptz NOT NULL,
  received_at timestamptz NOT NULL,
  position geography(Point, 4326) NOT NULL,
  speed_mps double precision,
  heading_deg double precision,
  status text NOT NULL DEFAULT 'unknown'
);

CREATE INDEX vehicle_observation_vehicle_time_idx
  ON vehicle_observation (vehicle_id, observed_at DESC);

CREATE INDEX vehicle_observation_position_gix
  ON vehicle_observation USING GIST (position);

Validate at the ingestion boundary. Convert source-specific coordinate formats to longitude-latitude order, normalize timestamps to UTC, reject impossible values, and preserve the raw event for debugging only when policy permits. Do not silently replace missing timestamps with the current time without recording that the timestamp was generated by the server.

function normalizeEvent(input) {
  const longitude = Number(input.longitude);
  const latitude = Number(input.latitude);

  if (!Number.isFinite(longitude) || longitude < -180 || longitude > 180) {
    throw new Error('invalid longitude');
  }
  if (!Number.isFinite(latitude) || latitude < -90 || latitude > 90) {
    throw new Error('invalid latitude');
  }
  if (!input.vehicle_id || !input.event_id || !input.observed_at) {
    throw new Error('missing required event field');
  }

  return {
    vehicle_id: String(input.vehicle_id),
    event_id: String(input.event_id),
    observed_at: new Date(input.observed_at).toISOString(),
    received_at: new Date().toISOString(),
    longitude,
    latitude,
    speed_mps: input.speed_mps == null ? null : Number(input.speed_mps),
    heading_deg: input.heading_deg == null ? null : Number(input.heading_deg),
    status: input.status || 'unknown'
  };
}

Represent the depot or other fixed feature as a geography value and use ST_DWithin for the radius filter. Add a freshness condition when the operational question concerns active vehicles rather than last-known positions.

SELECT
  v.vehicle_id,
  v.observed_at,
  v.received_at,
  ST_Distance(v.position, d.position) AS distance_meters
FROM vehicle_current AS v
CROSS JOIN (
  SELECT ST_SetSRID(ST_Point(-73.9857, 40.7484), 4326)::geography AS position
) AS d
WHERE ST_DWithin(v.position, d.position, 500)
  AND v.received_at >= now() - interval '10 minutes'
ORDER BY distance_meters;

For a small or moderate fleet, expose a FeatureCollection so the browser can update one map source. Keep operational metadata in properties and preserve the coordinate order expected by GeoJSON.

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', COALESCE(json_agg(json_build_object(
    'type', 'Feature',
    'id', vehicle_id,
    'geometry', ST_AsGeoJSON(position::geometry)::json,
    'properties', json_build_object(
      'vehicle_id', vehicle_id,
      'observed_at', observed_at,
      'received_at', received_at,
      'status', status,
      'speed_mps', speed_mps,
      'heading_deg', heading_deg
    )
  )), '[]'::json)
)
FROM vehicle_current;

Create the source once and replace its data when the API returns a new snapshot. For frequent updates, batch changes and avoid rebuilding large property objects unnecessarily. Add a status-aware layer and a visible stale state rather than relying on marker position alone.

map.addSource('vehicles', {
  type: 'geojson',
  data: { type: 'FeatureCollection', features: [] },
  cluster: true,
  clusterMaxZoom: 14,
  clusterRadius: 50
});

map.addLayer({
  id: 'vehicle-points',
  type: 'circle',
  source: 'vehicles',
  filter: ['!', ['has', 'point_count']],
  paint: {
    'circle-radius': 6,
    'circle-color': [
      'match', ['get', 'status'],
      'moving', '#16a34a',
      'stopped', '#f59e0b',
      'offline', '#6b7280',
      '#2563eb'
    ],
    'circle-stroke-color': '#ffffff',
    'circle-stroke-width': 1
  }
});

function renderVehicles(featureCollection) {
  map.getSource('vehicles').setData(featureCollection);
}

When a WebSocket closes, reconnect with bounded exponential backoff. On successful reconnection, request a fresh current-state snapshot before applying incremental messages. Use a version or timestamp check to prevent an older message from overwriting a newer marker.

let retry = 0;

function connect() {
  const socket = new WebSocket('/api/live/vehicles');

  socket.onopen = () => {
    retry = 0;
    fetch('/api/vehicles/current')
      .then(response => response.json())
      .then(renderVehicles);
  };

  socket.onmessage = event => {
    const update = JSON.parse(event.data);
    applyIfNewer(update);
  };

  socket.onclose = () => {
    const delay = Math.min(30000, 500 * (2 ** retry++));
    setTimeout(connect, delay);
  };
}

connect();