[data-veracity] Peak occupancy is systematically overestimated by intra-timestamp event ordering

gabogg commented

2026-09-21 13:43:30 +00:00

Owner

Filed from a data-veracity audit of the ingestion and aggregation pipeline on master, carried out against the KPI set that the Executive Statistics Deck (PR #20 / RFC-ARCH-2026-004) intends to publish. Each issue names the deck KPIs it corrupts.

Problem

get_cycle_peak_occupancy_async (app/db/occupancy_repository.py:1006) walks events in time order and records the running maximum:

SELECT e.direction, e.count, e.timestamp_epoch, e.timestamp_formatted
FROM people_counting_events e JOIN counting_cameras c ON ...
ORDER BY e.timestamp_epoch ASC

for r in rows:
    if direction == "IN":  cum_in  += cnt
    elif direction == "OUT": cum_out += cnt
    current_occ = calculate_proportional_occupancy(cum_in, cum_out, k_hat, guards)
    if current_occ > peak_headcount: peak_headcount = current_occ

Every event produced by a single poll carries the identical timestamp — record_counting_event_async is called with timestamp=now (app/services/occupancy_service.py:1909, 1921). So each poll writes a cluster of rows sharing one timestamp_epoch, and ORDER BY timestamp_epoch ASC leaves the order within that cluster unspecified.

In practice it is insertion order, and the sync loop writes all IN events before all OUT events (:1905-1911 then :1913-1922). The cumulative walk therefore adds the whole tick's ingress before subtracting any of its egress, creating an artificial local maximum on every single poll.

The reported peak is the maximum over ~28,800 such artificial spikes per day, so it is biased upward by roughly one poll's worth of ingress — systematically, never downward.

Why it is much worse than "one poll's worth"

Combine with poll-time stamping (separate issue): after any ingestion gap — network blip, Artemis error, the early-return paths at :1786 and :1845 — the entire accumulated backlog is written at the single timestamp of the next successful poll. All of that backlog's ingress is applied before any of its egress. A 30-minute outage over the lunch peak can inflate the reported peak by hundreds of persons, and the peak timestamp is pinned to the recovery moment rather than the real peak.

KPIs corrupted

Peak Occupancy (O_max) and its timestamp t_peak — headline KPI in all three horizons. Week "Peak Day" and "Avg Daily Peak". Month "Max Month Peak" and the capacity-utilisation percentage derived from it. The occupancy envelope's maximum in the Day chart.

Suggested fix

Aggregate tied timestamps into a single net step before the walk: GROUP BY timestamp_epoch producing SUM(IN) , SUM(OUT), then apply cum_in += in; cum_out += out once per distinct timestamp. This removes the ordering dependence entirely and is also faster.
Alternatively, bucket the walk (e.g. 60 s) and evaluate occupancy once per bucket — this is what the occupancy envelope chart needs anyway, and get_bucketed_cycle_flow_async (:1471) already does the correct join and grouping.
Return peak_timestamp_epoch alongside the formatted string. CompletePeriodMetrics declares peak_timestamp_epoch: float | None but this method returns only timestamp_formatted, so Phase 2 has nothing to populate it with.
Note that timestamp_formatted is a bare %I:%M:%S %p local-time string with no date — unusable as the peak marker for the Week and Month horizons.

> Filed from a data-veracity audit of the ingestion and aggregation pipeline on `master`, carried out against the KPI set that the Executive Statistics Deck (PR #20 / `RFC-ARCH-2026-004`) intends to publish. Each issue names the deck KPIs it corrupts. ## Problem `get_cycle_peak_occupancy_async` (`app/db/occupancy_repository.py:1006`) walks events in time order and records the running maximum: ```sql SELECT e.direction, e.count, e.timestamp_epoch, e.timestamp_formatted FROM people_counting_events e JOIN counting_cameras c ON ... ORDER BY e.timestamp_epoch ASC ``` ```python for r in rows: if direction == "IN": cum_in += cnt elif direction == "OUT": cum_out += cnt current_occ = calculate_proportional_occupancy(cum_in, cum_out, k_hat, guards) if current_occ > peak_headcount: peak_headcount = current_occ ``` Every event produced by a single poll carries the **identical** timestamp — `record_counting_event_async` is called with `timestamp=now` (`app/services/occupancy_service.py:1909, 1921`). So each poll writes a cluster of rows sharing one `timestamp_epoch`, and `ORDER BY timestamp_epoch ASC` leaves the order **within** that cluster unspecified. In practice it is insertion order, and the sync loop writes **all IN events before all OUT events** (`:1905-1911` then `:1913-1922`). The cumulative walk therefore adds the whole tick's ingress *before* subtracting any of its egress, creating an artificial local maximum on **every single poll**. The reported peak is the maximum over ~28,800 such artificial spikes per day, so it is biased upward by roughly one poll's worth of ingress — systematically, never downward. ## Why it is much worse than "one poll's worth" Combine with poll-time stamping (separate issue): after any ingestion gap — network blip, Artemis error, the early-return paths at `:1786` and `:1845` — the entire accumulated backlog is written at the single timestamp of the next successful poll. All of that backlog's ingress is applied before any of its egress. A 30-minute outage over the lunch peak can inflate the reported peak by hundreds of persons, and the peak *timestamp* is pinned to the recovery moment rather than the real peak. ## KPIs corrupted Peak Occupancy (`O_max`) and its timestamp `t_peak` — headline KPI in all three horizons. Week "Peak Day" and "Avg Daily Peak". Month "Max Month Peak" and the capacity-utilisation percentage derived from it. The occupancy envelope's maximum in the Day chart. ## Suggested fix 1. Aggregate tied timestamps into a **single net step** before the walk: `GROUP BY timestamp_epoch` producing `SUM(IN) , SUM(OUT)`, then apply `cum_in += in; cum_out += out` once per distinct timestamp. This removes the ordering dependence entirely and is also faster. 2. Alternatively, bucket the walk (e.g. 60 s) and evaluate occupancy once per bucket — this is what the occupancy envelope chart needs anyway, and `get_bucketed_cycle_flow_async` (`:1471`) already does the correct join and grouping. 3. Return `peak_timestamp_epoch` alongside the formatted string. `CompletePeriodMetrics` declares `peak_timestamp_epoch: float | None` but this method returns only `timestamp_formatted`, so Phase 2 has nothing to populate it with. 4. Note that `timestamp_formatted` is a bare `%I:%M:%S %p` local-time string with no date — unusable as the peak marker for the Week and Month horizons.

gabogg added the

ready-for-agent

label

2026-09-21 13:43:30 +00:00

gabogg referenced this issue from a commit

2026-09-23 22:41:03 +00:00

chore(wip): open draft for passenger flow ingestion honesty (#26, #31, #30)

gabogg referenced this issue from a pull request that will close it,

2026-09-23 22:41:33 +00:00

fix(occupancy): passenger flow ingestion honesty — counter resets, outage gaps, peak ordering (#26, #31, #30) #68

gabogg referenced this issue

2026-09-23 22:41:45 +00:00

[data-veracity] Passenger flow sync silently stops ingesting after any upstream counter reset #26

gabogg referenced this issue

2026-09-23 22:41:46 +00:00

[data-veracity] Event timestamps are poll time, not traversal time — outages collapse into a single bucket #31

gabogg commented

2026-09-23 22:41:47 +00:00

Author

Owner

Being addressed in draft PR #68 together with #26, #31 and #30 (passenger flow ingestion honesty). The PR description lists the settled spec plus the open design points that still need a decision (restart cold start, a floor for the adaptive cap, a shared anomaly/gap ledger table, and how trust rules see reconstructed hours).

Being addressed in draft **PR #68** together with #26, #31 and #30 (passenger flow ingestion honesty). The PR description lists the settled spec plus the open design points that still need a decision (restart cold start, a floor for the adaptive cap, a shared anomaly/gap ledger table, and how trust rules see reconstructed hours).

gabogg referenced this issue

2026-09-23 22:49:04 +00:00

fix(occupancy): business cycles in facility time (#27) #69

gabogg referenced this issue

2026-09-23 22:49:06 +00:00

fix(calibration): calibration honesty — seeded k from config, open-window dwell (#36, #32) #70

gabogg referenced this issue

2026-09-23 22:49:07 +00:00

fix(calibration): calibration audit trail and trust vocabulary (#34, #33) #71

~~gabogg referenced this issue 2026-09-24 13:13:51 +00:00~~

refactor(i18n): language-agnostic identifiers and payloads — i18n with English fallback, not a Spanish default #73

~~gabogg referenced this issue 2026-09-24 13:13:53 +00:00~~