Building a Real-Time EV Fleet Dashboard

A real-time EV fleet dashboard shows dispatchers the charge level, location, session status, and energy cost for every vehicle right now — not ten minutes ago. Building one with RisingWave means writing SQL views that update continuously as OCPP events and vehicle telemetry arrive, then connecting Grafana or any BI tool directly to the PostgreSQL-compatible endpoint.

Why a Real-Time Fleet Dashboard Matters

Fleet operators managing electric vans, buses, or delivery vehicles face a constant planning challenge: which vehicles can take the next dispatch assignment, and which need to stay at the charger? The answer depends on current State of Charge (SoC), not the SoC reported at the last polling interval.

When a dispatcher relies on stale data, they risk dispatching a vehicle with 15% SoC on a 200 km route. They also miss the opportunity to route a vehicle with 90% SoC that finished charging early. The cost of these mismatches adds up — in emergency recharges, missed delivery windows, and driver overtime.

Streaming SQL solves this by maintaining a continuously updated fleet state table. Every MeterValues event from every OCPP-connected charger triggers an incremental update to the view. The dashboard always shows the latest known state, with typical latency under one second.

The Streaming SQL Solution

The architecture uses RisingWave as the single source of truth for fleet state. OCPP events and vehicle telemetry flow into Kafka; RisingWave maintains materialized views for current SoC, active session status, energy cost, and connector availability. Grafana connects via the PostgreSQL data source plugin.

Because the views are maintained incrementally rather than recomputed on every query, dashboard refresh rates as low as five seconds are practical even with hundreds of vehicles and dozens of charging stations.

Tutorial: Building It Step by Step

Step 1: Set Up the Data Source

-- OCPP charging session events
CREATE SOURCE ocpp_events (
    event_id        VARCHAR,
    station_id      VARCHAR,
    connector_id    INT,
    session_id      VARCHAR,
    vehicle_id      VARCHAR,
    event_type      VARCHAR,   -- StartTransaction | StopTransaction | MeterValues | StatusNotification
    connector_type  VARCHAR,   -- CCS | CHAdeMO | Type2
    power_kw        DOUBLE PRECISION,
    energy_kwh      DOUBLE PRECISION,
    soc_percent     INT,
    status          VARCHAR,
    tariff_rate     DOUBLE PRECISION,  -- cost per kWh
    event_ts        TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'ocpp.events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Vehicle registry (metadata: make, model, rated range)
CREATE SOURCE vehicle_registry (
    vehicle_id          VARCHAR,
    license_plate       VARCHAR,
    driver_id           VARCHAR,
    vehicle_type        VARCHAR,  -- van | bus | truck
    rated_range_km      DOUBLE PRECISION,
    battery_capacity_kwh DOUBLE PRECISION,
    depot_id            VARCHAR
)
WITH (
    connector = 'kafka',
    topic = 'fleet.vehicle_registry',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build Real-Time Aggregations

-- Current fleet state: one row per vehicle, latest known SoC
CREATE MATERIALIZED VIEW fleet_current_state AS
SELECT DISTINCT ON (vehicle_id)
    vehicle_id,
    station_id,
    session_id,
    connector_type,
    soc_percent,
    power_kw,
    energy_kwh,
    status,
    event_ts AS last_updated
FROM ocpp_events
WHERE event_type IN ('MeterValues', 'StartTransaction', 'StatusNotification')
ORDER BY vehicle_id, event_ts DESC;

-- Enrich fleet state with vehicle registry metadata
CREATE MATERIALIZED VIEW fleet_dashboard AS
SELECT
    s.vehicle_id,
    r.license_plate,
    r.driver_id,
    r.vehicle_type,
    r.depot_id,
    s.station_id,
    s.session_id,
    s.connector_type,
    s.soc_percent,
    s.power_kw,
    s.energy_kwh,
    s.status,
    -- Estimated remaining range based on current SoC
    ROUND(r.rated_range_km * s.soc_percent / 100.0, 1) AS estimated_range_km,
    -- Flag vehicles available for dispatch (not charging, SoC >= 80%)
    CASE
        WHEN s.status = 'Available' AND s.soc_percent >= 80 THEN true
        ELSE false
    END AS dispatch_ready,
    s.last_updated
FROM fleet_current_state s
JOIN vehicle_registry r ON s.vehicle_id = r.vehicle_id;

-- Per-session cost tracking
CREATE MATERIALIZED VIEW session_cost_tracking AS
SELECT
    session_id,
    vehicle_id,
    station_id,
    connector_type,
    MIN(event_ts)                         AS session_start,
    MAX(event_ts)                         AS last_update,
    MAX(energy_kwh)                       AS energy_delivered_kwh,
    MAX(tariff_rate)                      AS tariff_rate,
    ROUND(MAX(energy_kwh) * MAX(tariff_rate), 4) AS estimated_cost,
    MAX(soc_percent)                      AS latest_soc,
    MAX(power_kw)                         AS peak_power_kw
FROM ocpp_events
WHERE event_type IN ('StartTransaction', 'MeterValues')
GROUP BY session_id, vehicle_id, station_id, connector_type;

-- Depot-level utilization: how many vehicles are charging right now
CREATE MATERIALIZED VIEW depot_utilization AS
SELECT
    r.depot_id,
    window_start,
    window_end,
    COUNT(DISTINCT e.vehicle_id)          AS vehicles_charging,
    SUM(e.power_kw)                       AS total_power_kw,
    AVG(e.soc_percent)                    AS avg_fleet_soc,
    COUNT(*) FILTER (WHERE e.soc_percent >= 80) AS vehicles_charge_complete
FROM TUMBLE(ocpp_events e, event_ts, INTERVAL '5 MINUTES')
JOIN vehicle_registry r ON e.vehicle_id = r.vehicle_id
WHERE e.event_type = 'MeterValues'
GROUP BY r.depot_id, window_start, window_end;

Step 3: Detect Anomalies or Generate Alerts

-- Alert: vehicle SoC below 20% and not currently charging
CREATE MATERIALIZED VIEW low_soc_alerts AS
SELECT
    d.vehicle_id,
    d.license_plate,
    d.driver_id,
    d.depot_id,
    d.soc_percent,
    d.estimated_range_km,
    d.last_updated,
    'LOW_SOC' AS alert_type
FROM fleet_dashboard d
WHERE d.soc_percent < 20
  AND d.status = 'Available'  -- not currently at a charger
  AND d.last_updated > NOW() - INTERVAL '15 MINUTES';

-- Alert: session not progressing (energy not increasing after 15 minutes)
CREATE MATERIALIZED VIEW stuck_session_alerts AS
SELECT
    session_id,
    vehicle_id,
    station_id,
    connector_type,
    energy_delivered_kwh,
    last_update,
    EXTRACT(EPOCH FROM (NOW() - last_update)) / 60 AS minutes_since_update,
    'STUCK_SESSION' AS alert_type
FROM session_cost_tracking
WHERE last_update < NOW() - INTERVAL '15 MINUTES'
  AND session_id NOT IN (
      SELECT session_id FROM ocpp_events WHERE event_type = 'StopTransaction'
  );

-- Sink: push low SoC alerts to fleet ops Kafka topic
CREATE SINK low_soc_alert_sink
FROM low_soc_alerts
WITH (
    connector = 'kafka',
    topic = 'alerts.fleet.low_soc',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

Can Grafana query RisingWave materialized views directly? Yes. Grafana's PostgreSQL data source plugin connects to RisingWave on port 4566. Point the data source at your RisingWave endpoint, and query fleet_dashboard, depot_utilization, or any other materialized view like a regular table.

How do I handle vehicles that are not currently charging? The fleet_current_state view uses DISTINCT ON (vehicle_id) ... ORDER BY event_ts DESC, so it always shows the latest known event regardless of whether the vehicle is actively charging. If a vehicle has been offline for more than 15 minutes, the last_updated column shows the last known event time.

Can I add GPS location to the dashboard? Yes. Add a GPS telemetry source (from a vehicle telematics Kafka topic) and join it with fleet_current_state on vehicle_id. Include latitude, longitude columns in fleet_dashboard, and use Grafana's Geomap panel to display vehicle positions on a map.

What if multiple vehicles share the same charging station? Each session has a unique session_id. The fleet_current_state view is keyed by vehicle_id, so each vehicle gets one row regardless of how many sessions have occurred at the same station.

Key Takeaways

• RisingWave materialized views provide sub-second fleet state updates by processing each OCPP event incrementally rather than on a polling schedule.
• A DISTINCT ON pattern keyed by vehicle_id with ORDER BY event_ts DESC gives each vehicle a single current-state row in the dashboard view.
• Joining the streaming state with a static vehicle registry enriches the dashboard with license plate, driver, and depot metadata without any ETL.
• Low-SoC and stuck-session alerts fire automatically through Kafka sinks, reducing the monitoring burden on fleet dispatchers.

• RisingWave documentation: DISTINCT ON queries
• Grafana PostgreSQL data source documentation
• OCPP 1.6 MeterValues message reference