[data-veracity] Trust rules and KPI queries read different datasets (missing camera join) #35

New issue

Closed

opened 2026-09-21 13:43:36 +00:00 by gabogg · 0 comments

gabogg commented

2026-09-21 13:43:36 +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

Every aggregate that feeds a published KPI joins counting_cameras and filters excluded/inactive cameras:

-- get_counts_in_range_async (:863), get_timespan_aggregates_async (:884),
-- get_cycle_peak_occupancy_async (:1006), get_cycle_average_occupancy_async (:1065),
-- get_bucketed_cycle_flow_async (:1471)
FROM people_counting_events e JOIN counting_cameras c ON e.camera_index_code = c.camera_index_code
WHERE ... AND c.is_excluded = 0 AND c.is_active = 1

get_hourly_flow_distribution_async (app/db/occupancy_repository.py:1391) does not:

SELECT CAST((timestamp_epoch - ?) / 3600 AS INTEGER) AS hour_idx, SUM(count) AS hourly_vol
FROM people_counting_events
WHERE timestamp_epoch >= ? AND timestamp_epoch < ?
GROUP BY hour_idx

No join, no exclusion filter. It therefore sums events from excluded cameras, inactive cameras, and orphan resource-group codes that no KPI counts.

Why it matters

This query is the sole input to trust rules R1 (temporal dispersion) and R2 (burst concentration) in evaluate_cycle_integrity_async. So the engine that decides whether a cycle is trustworthy is looking at a different dataset from the one whose numbers get published. Excluding a malfunctioning camera removes it from the KPIs but leaves it driving the trust verdict — which is the opposite of the intent, since exclusion exists precisely to quarantine a bad sensor.

It also means the hourly volumes used for trust evaluation will not reconcile against the Gross Ingress/Egress the deck shows for the same cycle, which will look like a bug to whoever first compares them.

Also: it cannot be reused for the deck

The query returns a single SUM(count) with no direction split, and it skips hours with no events entirely (so len(rows) is "active hours", not 24). The deck's diurnal series needs 24 dense buckets split by direction with occupancy and CI bounds per bucket — DiurnalTimeseriesBucket in app/schemas/occupancy_models.py.

get_bucketed_cycle_flow_async (:1471) already has the correct join, filter, direction split and bucket parameterisation. Phase 2 should build the diurnal series on that, and this issue should not be worked around by extending the unfiltered query.

Suggested fix

Add the JOIN counting_cameras ... AND c.is_excluded = 0 AND c.is_active = 1 to get_hourly_flow_distribution_async.
Add a test asserting that excluding a camera changes both the KPI totals and the trust-rule input identically.
Consider deleting the function and reimplementing R1/R2 on top of get_bucketed_cycle_flow_async(bucket_seconds=3600), so there is exactly one query shape for "counts over time" and it cannot drift again.

> 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 Every aggregate that feeds a published KPI joins `counting_cameras` and filters excluded/inactive cameras: ```sql -- get_counts_in_range_async (:863), get_timespan_aggregates_async (:884), -- get_cycle_peak_occupancy_async (:1006), get_cycle_average_occupancy_async (:1065), -- get_bucketed_cycle_flow_async (:1471) FROM people_counting_events e JOIN counting_cameras c ON e.camera_index_code = c.camera_index_code WHERE ... AND c.is_excluded = 0 AND c.is_active = 1 ``` `get_hourly_flow_distribution_async` (`app/db/occupancy_repository.py:1391`) does not: ```sql SELECT CAST((timestamp_epoch - ?) / 3600 AS INTEGER) AS hour_idx, SUM(count) AS hourly_vol FROM people_counting_events WHERE timestamp_epoch >= ? AND timestamp_epoch < ? GROUP BY hour_idx ``` No join, no exclusion filter. It therefore sums events from **excluded cameras, inactive cameras, and orphan resource-group codes** that no KPI counts. ## Why it matters This query is the sole input to trust rules R1 (temporal dispersion) and R2 (burst concentration) in `evaluate_cycle_integrity_async`. So the engine that decides whether a cycle is trustworthy is looking at a **different dataset** from the one whose numbers get published. Excluding a malfunctioning camera removes it from the KPIs but leaves it driving the trust verdict — which is the opposite of the intent, since exclusion exists precisely to quarantine a bad sensor. It also means the hourly volumes used for trust evaluation will not reconcile against the Gross Ingress/Egress the deck shows for the same cycle, which will look like a bug to whoever first compares them. ## Also: it cannot be reused for the deck The query returns a single `SUM(count)` with no direction split, and it skips hours with no events entirely (so `len(rows)` is "active hours", not 24). The deck's diurnal series needs 24 dense buckets split by direction with occupancy and CI bounds per bucket — `DiurnalTimeseriesBucket` in `app/schemas/occupancy_models.py`. `get_bucketed_cycle_flow_async` (`:1471`) already has the correct join, filter, direction split and bucket parameterisation. **Phase 2 should build the diurnal series on that**, and this issue should not be worked around by extending the unfiltered query. ## Suggested fix 1. Add the `JOIN counting_cameras ... AND c.is_excluded = 0 AND c.is_active = 1` to `get_hourly_flow_distribution_async`. 2. Add a test asserting that excluding a camera changes both the KPI totals and the trust-rule input identically. 3. Consider deleting the function and reimplementing R1/R2 on top of `get_bucketed_cycle_flow_async(bucket_seconds=3600)`, so there is exactly one query shape for "counts over time" and it cannot drift again.

gabogg added the

ready-for-agent

label

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

gabogg referenced this issue from a commit

2026-09-22 15:21:46 +00:00

chore(wip): open draft for counting-events/camera integrity (#28, #29, #35)

~~gabogg referenced this issue 2026-09-22 15:22:42 +00:00~~

fix(occupancy): camera-group schema, orphan guard and migration — phase 1 (#29) #53

gabogg referenced this issue

2026-09-22 16:31:06 +00:00

fix(occupancy): camera-group schema, orphan guard and migration — phase 1 (#29) #53