Explore

Daily ingestion volume (last 14 days)

Record counts per feed per day, across both real-time feeds.

SELECT received_at::date AS day,
       'ttc.vehicle_positions' AS feed_key,
       COUNT(*) AS record_count
FROM realtime.vehicle_positions
WHERE received_at >= NOW() - INTERVAL '14 days'
GROUP BY received_at::date
UNION ALL
SELECT received_at::date AS day,
       'ttc.trip_updates' AS feed_key,
       COUNT(*) AS record_count
FROM realtime.trip_updates
WHERE received_at >= NOW() - INTERVAL '14 days'
GROUP BY received_at::date
ORDER BY day DESC, feed_key;

Vehicles currently in service

Most recent position per vehicle, looking back at most 5 minutes.

SELECT DISTINCT ON (vehicle_id)
    vehicle_id, route_id,
    ST_Y(location::geometry) AS latitude,
    ST_X(location::geometry) AS longitude,
    speed_mps
FROM realtime.vehicle_positions
WHERE received_at > NOW() - INTERVAL '5 minutes'
  AND vehicle_id IS NOT NULL
ORDER BY vehicle_id, received_at DESC
LIMIT 20;

Vehicles on a single route (the 504 King)

Same query, filtered by route_id. This is the human-name → route_id bridge the upcoming MCP server is built around.

SELECT DISTINCT ON (vehicle_id)
    vehicle_id, route_id,
    ST_Y(location::geometry) AS latitude,
    ST_X(location::geometry) AS longitude,
    speed_mps
FROM realtime.vehicle_positions
WHERE received_at > NOW() - INTERVAL '5 minutes'
  AND vehicle_id IS NOT NULL
  AND route_id = '504'
ORDER BY vehicle_id, received_at DESC;

API: GET /v1/routes/504/vehicles

Per-feed freshness

The latest snapshot per feed — the same telemetry the Freshness page renders. Note the latest-per-key pattern (DISTINCT ON).

SELECT DISTINCT ON (feed_key)
    feed_key, status, lag_seconds,
    record_count_5min, record_count_1h, last_record_at
FROM ops.freshness_snapshot
ORDER BY feed_key, snapshot_at DESC;