[Audit] connectorId=0 (charge-point-wide) Faulted status notifications are silently dropped, causing undercounted downtime
- 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.