Building a Live Last-Mile Delivery Dashboard

Last-mile delivery is the most expensive, most visible, and most failure-prone segment of the logistics chain. A live dashboard that reflects the real state of deliveries — right now, not five minutes ago — gives dispatchers the situational awareness they need to keep drivers on schedule and customers informed. RisingWave, a PostgreSQL-compatible streaming database, makes this straightforward using SQL alone.

Why Last-Mile Visibility Is Hard

The last mile encompasses the final leg of delivery, from a local depot to the customer's door. It involves dozens or hundreds of concurrent drivers, dynamic stop sequences, unpredictable traffic, and customer availability windows. The data that describes this reality — GPS pings, scan events, driver status updates, customer feedback — is inherently streaming.

Traditional dashboard architectures poll a relational database every 30 to 60 seconds, re-running aggregation queries each time. At scale, this is both slow and expensive: hundreds of driver feeds, each updating every few seconds, translate into enormous query load on an OLTP database that was never designed for this pattern.

The alternative is a streaming database that maintains pre-computed, incrementally updated views. Instead of re-aggregating on every dashboard refresh, RisingWave updates the result of each query incrementally as new events arrive. Dashboard queries become instant reads against materialized views that are always current.

How a Streaming Last-Mile Dashboard Works

The architecture has three layers:

Event ingestion: Kafka topics carry driver GPS pings, parcel scan events, and delivery confirmations. RisingWave consumes these as streaming sources.
Materialized views: SQL-defined views that RisingWave maintains continuously. Each view answers a specific dashboard question: active drivers, deliveries by status, exceptions, on-time rate.
Dashboard layer: Any PostgreSQL-compatible BI tool (Grafana, Metabase, Retool) queries the materialized views directly via the standard PostgreSQL wire protocol.

No separate stream processor, no ETL pipeline, no cache invalidation logic. One streaming database serves the entire dashboard.

Step-by-Step Tutorial

Step 1: Set Up the Data Source

Define Kafka sources for the two primary event streams: driver GPS updates and parcel scan events.

-- Driver GPS pings
CREATE SOURCE driver_gps (
    driver_id    VARCHAR,
    route_id     VARCHAR,
    event_time   TIMESTAMPTZ,
    latitude     DOUBLE PRECISION,
    longitude    DOUBLE PRECISION,
    speed_kmh    DOUBLE PRECISION,
    status       VARCHAR  -- 'EN_ROUTE', 'AT_STOP', 'RETURNING'
)
WITH (
    connector    = 'kafka',
    topic        = 'driver-gps',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Parcel scan events
CREATE SOURCE parcel_scans (
    parcel_id    VARCHAR,
    driver_id    VARCHAR,
    scan_type    VARCHAR,  -- 'PICKED_UP','OUT_FOR_DELIVERY','DELIVERED','ATTEMPTED','EXCEPTION'
    event_time   TIMESTAMPTZ,
    stop_seq     INTEGER,
    latitude     DOUBLE PRECISION,
    longitude    DOUBLE PRECISION,
    notes        VARCHAR
)
WITH (
    connector    = 'kafka',
    topic        = 'parcel-scans',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Reference: route plans loaded from operational system
CREATE TABLE route_plans (
    route_id             VARCHAR PRIMARY KEY,
    driver_id            VARCHAR,
    planned_start        TIMESTAMPTZ,
    planned_end          TIMESTAMPTZ,
    total_stops          INTEGER,
    depot_lat            DOUBLE PRECISION,
    depot_lon            DOUBLE PRECISION
);

Step 2: Build the Core Materialized View

Create the live driver status view — the centerpiece of the dashboard map layer:

CREATE MATERIALIZED VIEW live_driver_positions AS
SELECT
    g.driver_id,
    g.route_id,
    g.latitude,
    g.longitude,
    g.speed_kmh,
    g.status         AS driver_status,
    g.event_time     AS last_update,
    NOW() - g.event_time AS data_age,
    r.planned_end    AS route_planned_end,
    r.total_stops
FROM (
    SELECT DISTINCT ON (driver_id)
        driver_id, route_id, latitude, longitude,
        speed_kmh, status, event_time
    FROM driver_gps
    ORDER BY driver_id, event_time DESC
) g
LEFT JOIN route_plans r ON g.route_id = r.route_id;

Build the parcel status summary — aggregated counts per driver for the progress bars in the dashboard:

CREATE MATERIALIZED VIEW driver_delivery_progress AS
SELECT
    driver_id,
    COUNT(*) FILTER (WHERE scan_type = 'DELIVERED')   AS delivered_count,
    COUNT(*) FILTER (WHERE scan_type = 'ATTEMPTED')   AS attempted_count,
    COUNT(*) FILTER (WHERE scan_type = 'EXCEPTION')   AS exception_count,
    COUNT(*) FILTER (WHERE scan_type = 'OUT_FOR_DELIVERY') AS remaining_count,
    MAX(event_time)                                    AS last_scan_time
FROM (
    SELECT DISTINCT ON (parcel_id)
        parcel_id, driver_id, scan_type, event_time
    FROM parcel_scans
    ORDER BY parcel_id, event_time DESC
) latest_scans
GROUP BY driver_id;

Step 3: Add Aggregations for the Summary Panel

The dashboard header typically shows fleet-level KPIs. Build these as materialized views that aggregate across all active routes:

-- Fleet summary: one row, refreshed continuously
CREATE MATERIALIZED VIEW fleet_summary AS
SELECT
    COUNT(DISTINCT d.driver_id)                   AS active_drivers,
    SUM(p.delivered_count)                        AS total_delivered_today,
    SUM(p.attempted_count)                        AS total_attempted_today,
    SUM(p.exception_count)                        AS total_exceptions_today,
    SUM(p.remaining_count)                        AS total_remaining,
    ROUND(
        100.0 * SUM(p.delivered_count)::NUMERIC /
        NULLIF(SUM(p.delivered_count + p.attempted_count + p.exception_count), 0),
    1)                                            AS first_attempt_success_rate
FROM live_driver_positions d
LEFT JOIN driver_delivery_progress p ON d.driver_id = p.driver_id
WHERE d.driver_status != 'RETURNING';

-- Per-hour delivery throughput using tumbling windows
CREATE MATERIALIZED VIEW deliveries_per_hour AS
SELECT
    window_start,
    window_end,
    COUNT(*) AS deliveries_completed
FROM TUMBLE(parcel_scans, event_time, INTERVAL '1 hour')
WHERE scan_type = 'DELIVERED'
GROUP BY window_start, window_end;

-- Exception reasons breakdown
CREATE MATERIALIZED VIEW exception_breakdown AS
SELECT
    notes                          AS exception_reason,
    COUNT(*)                       AS occurrence_count,
    MAX(event_time)                AS most_recent
FROM parcel_scans
WHERE scan_type = 'EXCEPTION'
GROUP BY notes;

How This Compares to Traditional Approaches

Aspect	Polling OLTP Database	Message Queue + Cache	RisingWave Streaming SQL
Dashboard freshness	30–60 seconds	Near-real-time but complex	Sub-second
Aggregation cost	High (full re-scan each poll)	Moderate (manual cache logic)	Low (incremental updates)
SQL interface	Yes	No (custom API)	Yes (PostgreSQL-compatible)
Operational complexity	Low initially, grows fast	High (multiple systems)	Low (one system)
Historical analytics	Good	Limited	Good (same SQL engine)
Alert integration	Separate system needed	Custom consumers	Native via Kafka sink

FAQ

How do I connect Grafana to RisingWave for the dashboard?

Use Grafana's built-in PostgreSQL data source. Point it at RisingWave's hostname, port (default 4566), and the database you created. Query materialized views like live_driver_positions and fleet_summary directly in Grafana panels. Enable auto-refresh intervals of 5–10 seconds for a live map experience.

Can I handle driver route reassignments mid-shift?

Yes. The route_plans table is a regular RisingWave table you can update at any time with INSERT or UPDATE. Because live_driver_positions joins against it, a reassignment is reflected in the dashboard as soon as the next GPS ping arrives from that driver — no cache invalidation required.

What is the retention policy for GPS pings?

RisingWave stores the raw source data in its internal log. You can configure retention at the source level or route raw events to an Iceberg sink for long-term archival. Materialized views maintain only the derived state (latest positions, aggregated counts), which stays small regardless of raw event volume.

Key Takeaways

RisingWave replaces the polling-OLTP-database pattern with incrementally maintained materialized views, giving dashboards sub-second data freshness without added query load.
Three SQL objects — a GPS source, a parcel scan source, and a handful of materialized views — are all you need to power a full last-mile delivery dashboard.
Because RisingWave speaks the PostgreSQL wire protocol, any existing BI tool connects without drivers or adapters, and dashboard teams can use the SQL they already know.

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