tracksolid_timescale_grafana_prod/tracksolid_ingestion_pipeline.txt

### Pipeline health — last hour (key check)

SELECT
  endpoint,
  COUNT(*)                                          AS calls,
  SUM(rows_upserted)                               AS upserted,
  SUM(rows_inserted)                               AS inserted,
  ROUND(AVG(duration_ms)::numeric, 0)              AS avg_ms,
  COUNT(*) FILTER (WHERE success = false)          AS failures,
  MAX(run_at AT TIME ZONE 'Africa/Nairobi')        AS last_call_eat
FROM tracksolid.ingestion_log
WHERE run_at > now() - interval '1 hour'
GROUP BY endpoint
ORDER BY calls DESC;

-------- Ingestion Pipeline Health

SELECT
  endpoint,
  COUNT(*)                                          AS total_calls,
  SUM(rows_upserted)                               AS total_upserted,
  SUM(rows_inserted)                               AS total_inserted,
  ROUND(AVG(duration_ms)::numeric, 0)              AS avg_ms,
  COUNT(*) FILTER (WHERE success = false)          AS failures,
  MIN(run_at AT TIME ZONE 'Africa/Nairobi')        AS first_call,
  MAX(run_at AT TIME ZONE 'Africa/Nairobi')        AS last_call
FROM tracksolid.ingestion_log
GROUP BY endpoint
ORDER BY total_calls DESC;

### Recent calls — last 20 entries

SELECT
  run_at AT TIME ZONE 'Africa/Nairobi' AS run_eat,
  endpoint,
  imei_count,
  rows_upserted,
  rows_inserted,
  duration_ms,
  success,
  error_message
FROM tracksolid.ingestion_log
ORDER BY run_at DESC
LIMIT 20;

### Recent calls — FAILED CALLS entries

SELECT
  run_at AT TIME ZONE 'Africa/Nairobi' AS run_eat,
  endpoint,
  error_code,
  error_message
FROM tracksolid.ingestion_log
WHERE success = false
ORDER BY run_at DESC;