How to Detect Delivery Delays Before They Happen

Delivery delays cost money and damage customer trust. With RisingWave, a PostgreSQL-compatible streaming database, you can continuously monitor shipment progress against expected milestones and trigger alerts within milliseconds of detecting a deviation — giving operations teams time to intervene before a delay becomes a missed delivery.

Why Proactive Delay Detection Matters

Reactive delay management — notifying customers after a delivery is already late — is the industry norm, but it is also the worst possible outcome. By the time a delay is confirmed, rerouting options are limited, penalties may already apply, and customer confidence is damaged.

Proactive detection means monitoring the gap between where a shipment is and where it should be at this moment in time, based on its route plan and historical travel patterns. This requires comparing live position data against a schedule in real time, continuously, for every active shipment across the entire fleet.

Traditional approaches rely on nightly batch jobs that flag overdue deliveries by the next morning. Some teams build alerting dashboards that refresh every few minutes. Neither approach is fast enough when a delay is developing — a truck stuck in unexpected traffic can fall 45 minutes behind schedule in less than an hour, and the optimal intervention window (reroute, notify customer, dispatch alternate vehicle) closes quickly.

A streaming-first architecture with RisingWave ingests live telematics data, computes ETA deviations continuously, and surfaces alerts with sub-second latency. Operations teams see developing delays while there is still time to act.

How Streaming Delay Detection Works

The core idea is to maintain a materialized view that joins live shipment positions with planned route milestones, computes the current estimated time of arrival (ETA), and compares it to the committed delivery window. When the deviation exceeds a configurable threshold, the row appears in an alert view.

RisingWave incrementally updates all of these views as new GPS pings arrive. No polling, no scheduled jobs — the alert view always reflects the latest data.

Key components:

• Kafka source: ingests vehicle telemetry in real time
• Planned milestones table: stores the expected waypoint times for each shipment
• Materialized view for ETA deviation: computes how far each shipment is from its schedule
• Alert view: filters shipments that exceed the acceptable delay threshold

Step-by-Step Tutorial

Step 1: Set Up the Data Source

Define the Kafka source for vehicle telemetry and the reference tables for route plans:

CREATE SOURCE vehicle_telemetry (
    vehicle_id   VARCHAR,
    shipment_id  VARCHAR,
    event_time   TIMESTAMPTZ,
    latitude     DOUBLE PRECISION,
    longitude    DOUBLE PRECISION,
    speed_kmh    DOUBLE PRECISION,
    status       VARCHAR
)
WITH (
    connector    = 'kafka',
    topic        = 'vehicle-telemetry',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

CREATE TABLE route_milestones (
    shipment_id        VARCHAR,
    milestone_seq      INTEGER,
    waypoint_name      VARCHAR,
    planned_arrival    TIMESTAMPTZ,
    planned_departure  TIMESTAMPTZ,
    latitude           DOUBLE PRECISION,
    longitude          DOUBLE PRECISION,
    PRIMARY KEY (shipment_id, milestone_seq)
);

CREATE TABLE shipment_metadata (
    shipment_id          VARCHAR PRIMARY KEY,
    committed_delivery   TIMESTAMPTZ,
    customer_id          VARCHAR,
    priority_tier        VARCHAR,   -- 'STANDARD', 'EXPRESS', 'SAME_DAY'
    sla_delay_minutes    INTEGER    -- acceptable delay buffer
);

Step 2: Build the Core Materialized View

Create a materialized view that tracks the next planned milestone for each active shipment and calculates the distance remaining. RisingWave keeps this up to date as GPS pings arrive:

CREATE MATERIALIZED VIEW shipment_progress AS
SELECT
    t.shipment_id,
    t.latitude          AS current_lat,
    t.longitude         AS current_lon,
    t.speed_kmh,
    t.event_time        AS last_ping,
    m.waypoint_name     AS next_waypoint,
    m.planned_arrival   AS next_waypoint_eta_planned,
    m.latitude          AS waypoint_lat,
    m.longitude         AS waypoint_lon,
    -- Haversine approximation for distance (degrees to km)
    111.0 * SQRT(
        POWER(t.latitude  - m.latitude,  2) +
        POWER(t.longitude - m.longitude, 2) * POWER(COS(RADIANS(t.latitude)), 2)
    )                   AS km_to_next_waypoint,
    sm.committed_delivery,
    sm.priority_tier,
    sm.sla_delay_minutes,
    sm.customer_id
FROM (
    SELECT DISTINCT ON (shipment_id)
        shipment_id, latitude, longitude, speed_kmh, event_time
    FROM vehicle_telemetry
    WHERE status NOT IN ('DELIVERED', 'CANCELLED')
    ORDER BY shipment_id, event_time DESC
) t
JOIN (
    SELECT DISTINCT ON (shipment_id)
        shipment_id, milestone_seq, waypoint_name,
        planned_arrival, latitude, longitude
    FROM route_milestones
    ORDER BY shipment_id, milestone_seq
) m ON t.shipment_id = m.shipment_id
JOIN shipment_metadata sm ON t.shipment_id = sm.shipment_id;

Step 3: Add Delay Alerts

Build the alert view that surfaces shipments at risk of missing their delivery window. The estimated time to next waypoint is computed from current speed and remaining distance:

CREATE MATERIALIZED VIEW delivery_delay_alerts AS
SELECT
    shipment_id,
    customer_id,
    priority_tier,
    committed_delivery,
    last_ping,
    next_waypoint,
    next_waypoint_eta_planned,
    km_to_next_waypoint,
    speed_kmh,
    -- Estimated minutes to reach next waypoint at current speed
    CASE
        WHEN speed_kmh > 5
        THEN (km_to_next_waypoint / speed_kmh) * 60
        ELSE NULL
    END                                       AS est_minutes_to_waypoint,
    -- Projected delay at current waypoint
    CASE
        WHEN speed_kmh > 5
        THEN EXTRACT(EPOCH FROM (
            now() + (km_to_next_waypoint / speed_kmh) * INTERVAL '1 hour'
            - next_waypoint_eta_planned
        )) / 60
        ELSE NULL
    END                                       AS projected_delay_minutes,
    sla_delay_minutes
FROM shipment_progress
WHERE
    -- Only flag if projected delay exceeds the SLA buffer
    speed_kmh > 5
    AND EXTRACT(EPOCH FROM (
        now() + (km_to_next_waypoint / speed_kmh) * INTERVAL '1 hour'
        - next_waypoint_eta_planned
    )) / 60 > sla_delay_minutes;

Downstream systems can query delivery_delay_alerts at any time and receive only shipments currently developing a delay, with the projected magnitude in minutes.

How This Compares to Traditional Approaches

The key difference is that traditional approaches check status at intervals, while RisingWave evaluates every incoming GPS ping. Alerts surface when a delay is developing, not after it has already materialized.

FAQ

How do I send alerts to Slack or PagerDuty from RisingWave?

RisingWave supports a Kafka sink, so you can stream alert rows from delivery_delay_alerts into a Kafka topic and consume it with any notification service. Many teams use a small consumer microservice that reads from Kafka and calls webhook APIs for Slack, PagerDuty, or SMS gateways.

What if a vehicle is temporarily stopped (e.g., at a rest stop)?

The alert query filters out vehicles with speed_kmh <= 5, which suppresses spurious alerts while a truck is legally parked. For longer stops, you can add logic to check whether the stopped duration itself exceeds the route plan's scheduled dwell time at that waypoint.

Can I tune different delay thresholds per customer tier?

Yes. The sla_delay_minutes column in shipment_metadata is per-shipment and can reflect your tiered SLA structure — for example, 0 minutes for same-day deliveries and 30 minutes for standard. The alert view uses this column dynamically, so thresholds apply without code changes.

Key Takeaways

• RisingWave continuously compares live GPS positions against planned route milestones, surfacing delay alerts with sub-second latency rather than waiting for the next batch run or dashboard refresh.
• The materialized view approach means the computation happens incrementally as data arrives — queries against the alert view are instant reads, not expensive recomputations.
• Configurable per-shipment SLA thresholds let operations teams apply different sensitivity levels to express, standard, and same-day delivery tiers without changing any SQL logic.

Ready to try this yourself? Get started with RisingWave in minutes. Join our Slack community.