737ca67712
Two read-only views in the tracksolid schema feeding n8n's working-hours checks: per-IMEI per-Nairobi-day reporting/closing times, start/end locations + Nominatim addresses, and trip-count/km/drive-hours context. No policy embedded; cost-centre filtering and tardiness thresholds live in n8n. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
111 lines
5.6 KiB
PL/PgSQL
111 lines
5.6 KiB
PL/PgSQL
-- 10_driver_clock_views.sql
|
|
-- Driver clock-in / clock-out views for n8n tardiness + working-hours monitoring.
|
|
--
|
|
-- Two views, both keyed by IMEI per local Africa/Nairobi date:
|
|
-- • v_driver_clock_daily — historical, one row per IMEI per day with activity
|
|
-- • v_driver_clock_today — thin pass-through filtered to today (EAT)
|
|
--
|
|
-- The view exposes raw times, locations, and addresses; n8n owns tardiness
|
|
-- thresholds and cost-centre filtering. Closing time is the latest end_time
|
|
-- of any trip whose start_time falls on the local Nairobi date — kept lossless
|
|
-- so n8n can detect midnight crossings via reporting_ts/closing_ts.
|
|
--
|
|
-- Reuses:
|
|
-- • tracksolid.trips.distance_km (renamed in migration 04)
|
|
-- • tracksolid.trips.driving_time_s (referenced in migration 05)
|
|
-- • tracksolid.trips.start_geom/end_geom (migration 02 §3.06)
|
|
-- • tracksolid.trips.start_address/end_address (migration 09 — Nominatim)
|
|
--
|
|
-- Pattern follows 07_analytics_views.sql: regular views (not materialised),
|
|
-- COMMENT ON VIEW with provenance, GRANT SELECT TO grafana_ro.
|
|
|
|
BEGIN;
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- v_driver_clock_daily
|
|
-- One row per IMEI per local Africa/Nairobi date with at least one trip.
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE OR REPLACE VIEW tracksolid.v_driver_clock_daily AS
|
|
WITH daily_agg AS (
|
|
SELECT
|
|
t.imei,
|
|
(t.start_time AT TIME ZONE 'Africa/Nairobi')::date AS report_date,
|
|
MIN(t.start_time) AS reporting_ts,
|
|
MAX(t.end_time) AS closing_ts,
|
|
COUNT(*) AS trips_count,
|
|
SUM(t.distance_km) AS total_km,
|
|
SUM(t.driving_time_s)::numeric / 3600 AS drive_hours
|
|
FROM tracksolid.trips t
|
|
GROUP BY t.imei, (t.start_time AT TIME ZONE 'Africa/Nairobi')::date
|
|
),
|
|
start_row AS (
|
|
SELECT DISTINCT ON (t.imei, (t.start_time AT TIME ZONE 'Africa/Nairobi')::date)
|
|
t.imei,
|
|
(t.start_time AT TIME ZONE 'Africa/Nairobi')::date AS report_date,
|
|
ST_Y(t.start_geom::geometry) AS start_lat,
|
|
ST_X(t.start_geom::geometry) AS start_lng,
|
|
t.start_address AS start_address
|
|
FROM tracksolid.trips t
|
|
ORDER BY t.imei, (t.start_time AT TIME ZONE 'Africa/Nairobi')::date, t.start_time ASC
|
|
),
|
|
end_row AS (
|
|
SELECT DISTINCT ON (t.imei, (t.start_time AT TIME ZONE 'Africa/Nairobi')::date)
|
|
t.imei,
|
|
(t.start_time AT TIME ZONE 'Africa/Nairobi')::date AS report_date,
|
|
ST_Y(t.end_geom::geometry) AS end_lat,
|
|
ST_X(t.end_geom::geometry) AS end_lng,
|
|
t.end_address AS end_address
|
|
FROM tracksolid.trips t
|
|
ORDER BY t.imei, (t.start_time AT TIME ZONE 'Africa/Nairobi')::date, t.end_time DESC NULLS LAST
|
|
)
|
|
SELECT
|
|
a.imei,
|
|
d.driver_name,
|
|
d.vehicle_number,
|
|
d.cost_centre,
|
|
COALESCE(d.assigned_city, d.city, 'unassigned') AS assigned_city,
|
|
a.report_date,
|
|
(a.reporting_ts AT TIME ZONE 'Africa/Nairobi')::time AS reporting_time,
|
|
(a.closing_ts AT TIME ZONE 'Africa/Nairobi')::time AS closing_time,
|
|
a.reporting_ts,
|
|
a.closing_ts,
|
|
s.start_lat,
|
|
s.start_lng,
|
|
s.start_address,
|
|
e.end_lat,
|
|
e.end_lng,
|
|
e.end_address,
|
|
a.trips_count,
|
|
a.total_km,
|
|
a.drive_hours
|
|
FROM daily_agg a
|
|
LEFT JOIN start_row s ON s.imei = a.imei AND s.report_date = a.report_date
|
|
LEFT JOIN end_row e ON e.imei = a.imei AND e.report_date = a.report_date
|
|
LEFT JOIN tracksolid.devices d ON d.imei = a.imei;
|
|
|
|
COMMENT ON VIEW tracksolid.v_driver_clock_daily IS
|
|
'Driver clock-in / clock-out daily series. One row per IMEI per Africa/Nairobi '
|
|
'date with at least one trip. Reporting/closing times are derived from trip '
|
|
'start_time/end_time bounded by local-date start; closing_ts may cross '
|
|
'midnight UTC. No policy embedded — n8n applies tardiness rules and cost-centre '
|
|
'filtering. See plan i-would-like-to-wobbly-volcano (2026-05-04).';
|
|
|
|
GRANT SELECT ON tracksolid.v_driver_clock_daily TO grafana_ro;
|
|
|
|
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- v_driver_clock_today
|
|
-- Pass-through filter: today's row from v_driver_clock_daily.
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
CREATE OR REPLACE VIEW tracksolid.v_driver_clock_today AS
|
|
SELECT *
|
|
FROM tracksolid.v_driver_clock_daily
|
|
WHERE report_date = (NOW() AT TIME ZONE 'Africa/Nairobi')::date;
|
|
|
|
COMMENT ON VIEW tracksolid.v_driver_clock_today IS
|
|
'Today snapshot of v_driver_clock_daily, filtered to (NOW() AT TIME ZONE '
|
|
'''Africa/Nairobi'')::date. Refreshes as trips land throughout the day.';
|
|
|
|
GRANT SELECT ON tracksolid.v_driver_clock_today TO grafana_ro;
|
|
|
|
COMMIT;
|