[Proposal] Add OCPP data coverage layer for reliability metrics

[VoxelPrincess]

Problem

Reliability and uptime metrics are only as trustworthy as the underlying OCPP event coverage. Today fact_uptime and related semantic metrics can be queried without explicit context about whether telemetry was complete, partial, or missing for a given day and port.

Before these metrics are consumed by dashboards or ChatBI, it would be useful to add a lightweight coverage layer that makes data gaps visible and debuggable.

Proposal

Add a daily coverage model at the same grain as fact_uptime:

Grain: one row per date_id + charge_point_id + port_id

Suggested name: int_ocpp_coverage_daily (mart exposure later if BI needs it directly)

Core fields (Slice 1)

Presence flags

• has_port_metadata — port exists in stg_ports with a valid commissioned window for the day
• has_heartbeat — at least one Heartbeat on the charge point that day
• has_status_events — at least one StatusNotification relevant to the port/connector that day
• has_transaction_events — at least one StartTransaction / StopTransaction that day

Evidence / diagnostics

• message_count — total charge-point-initiated messages that day (align with offline detection logic in int_offline_outages)
• heartbeat_count
• status_notification_count
• transaction_event_count
• first_message_ts
• last_message_ts
• max_message_gap_minutes — max gap between consecutive CP messages (charger-level; primary signal for connectivity coverage)
• max_heartbeat_gap_minutes — optional; heartbeat-specific SLA signal

Status

• data_quality_status — e.g. complete | partial | missing (deterministic CASE over flags; no confidence score in v1)
• coverage_ambiguous — true when signals disagree (e.g. commissioned but zero messages; heartbeat present but no status events)

Join contract: left join to fact_uptime on (date_id, charge_point_id, port_id) so consumers can filter or flag rows before aggregating.

Slice 2 — metric readiness (follow-up)

• is_complete_for_uptime — metadata + CP messages + status events
• is_complete_for_attempts — uptime requirements + transaction events
• metadata vs telemetry mismatch flags (e.g. commissioned day with zero events)

Slice 3 — evals hook (follow-up, not blocking)

• small golden coverage dataset with known complete / partial / missing cases
• coverage-aware eval cases for ChatBI (e.g. caveat when reporting uptime on partial data)
• no automated caveat text generation in SQL

Why it matters

• Makes reliability metrics easier to trust and debug
• Helps explain gaps before metrics surface in dashboards or ChatBI
• Creates a simple data-quality contract for downstream analytics
• Can support future evals by making data caveats explicit

Non-goals

• No confidence scoring
• No ChatBI caveat generation in the model layer
• No broad data quality platform
• No changes to blocking dims / FK / SCD work

Open questions

• Intermediate vs mart for v1? (leaning: int_ first, promote to fact_ when semantic/BI consumption is clear)
• Should grain include connector_id in addition to port_id? (leaning: port_id to match fact_uptime; connector-level coverage is a separate slice if needed for visit/attempt metrics)
• Which OCPP event types are required for "complete enough" per metric family? (uptime vs charge attempts may differ)

Related work

• Related to #83 (chat evals) — complementary data layer; does not block eval work
• Upstream refs: stg_ocpp_logs, stg_ports, charge_point_span_daily, int_offline_outages, fact_uptime

Acceptance criteria (Slice 1 only)

• Model runs incrementally or as view (cost-aware choice documented)
• Grain tested: not_null + unique on surrogate key
• accepted_values on data_quality_status
• Column docs in schema YAML
• Documented join pattern to fact_uptime

[VoxelPrincess]

Assigning myself to keep ownership of the proposal. I’m not planning to start implementation until current #83 (chat evals) work is unblocked / wrapped.