appspace / appspace/kwwhat

[Audit] connectorId=0 (charge-point-wide) Faulted status notifications are silently dropped, causing undercounted downtime

Open
#146 0 comments 0 reactions 1 assignee Claimed by @SYegor View on GitHub
Dominant language
No language data
Stars
14
Forks
3
PR merge metrics
No merged PRs in 30d

Description

OCPP 1.6's StatusNotification uses connectorId = 0 to mean "this status applies to the whole charge point," not any specific connector/port. int_status_changes (models/intermediate/int_status_changes.sql) resolves port_id via a left join to int_connectors on connector_id - for connectorId = 0 rows this join has no match, so port_id comes out null.

Downstream, int_faulted_outages (models/intermediate/outages/int_faulted_outages.sql:100-112) builds fault intervals by self-joining time_points on tp1.port_id = tp2.port_id. Since NULL = NULL is never true in SQL, rows with a null port_id never form an interval at all - they vanish before the later ports_count inner join even runs. Result: a charger reporting "the whole station is faulted" currently produces zero downtime records anywhere in the pipeline. This is missing data, not just miscategorized data - it can make a charger look more reliable than it is.

This is the same shape of event as OFFLINE (charger stops communicating - also charge-point-wide, no specific port), which the pipeline already handles correctly: fact_downtime_daily's offline_outages CTE takes the charger-level OFFLINE event and fans it out to every port on that charger via dim_ports. The Faulted/connectorId=0 case should get the same treatment instead of being dropped.

Proposed approach

1. Add a new intermediate model (sibling to int_offline_outages, e.g. int_faulted_outages_charge_point) that computes merged Faulted periods for connector_id = 0 rows from int_status_changes, at charger_id grain (no port_id) - mirroring int_offline_outages's shape and merge logic.
2. In fact_downtime_daily, add a CTE parallel to offline_outages that fans this new charger-level model out to every port via dim_ports, with reason = 'FAULTED'.
3. Resolve overlap/double-counting between this new source and the existing port-scoped faulted_outages and offline_outages CTEs (see open questions below).

Open questions to resolve during implementation

- If a port has both a port-scoped FAULTED period and an overlapping fanned-out charge-point-wide FAULTED period, how should the overlapping minutes be reconciled (merge/union intervals vs. one source taking precedence)?
- Should OFFLINE continue to exclude overlap with FAULTED (as it already does for port-scoped FAULTED) when the overlapping FAULTED period is the new charge-point-wide kind too?

Acceptance criteria

- [ ] Business case confirmed: charge-point-wide Faulted (connectorId = 0) is a real distinct downtime event, not a data quality gap
- [ ] New intermediate model computes charger-grain Faulted periods for connectorId = 0, reusing/sharing the existing merge-adjacent-periods logic rather than duplicating it
- [ ] fact_downtime_daily fans these out to all ports of the charger, with no null port_id
- [ ] Overlap between charge-point-wide FAULTED, port-scoped FAULTED, and OFFLINE is reconciled with no double-counted minutes
- [ ] Unit tests cover the new merge logic and the overlap/dedup behavior
- [ ] dbt build --select int_faulted_outages_charge_point+ fact_downtime_daily+ fact_uptime+ passes

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.