[data-veracity] Events written under a resource-group code are invisible to every KPI and re-injected every 3 seconds #29

New issue

Closed

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

gabogg commented

2026-09-21 13:43:29 +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 — two halves that combine into a runaway

Half 1: events can be written with a `camera_index_code` that is not a camera

When a resource group exposes no member resources, or when keyword matching empties a direction list, the sync falls back to the group code as though it were a camera:

# app/services/occupancy_service.py:1826-1830
group_cam_map[g_code] = {
    "in_cams":  in_cams  or [(g_code, g_name)],
    "out_cams": out_cams or [(g_code, g_name)],
}

Rows are then inserted into people_counting_events with camera_index_code = <resourceGroupIndexCode>, which has no row in counting_cameras.

Half 2: every KPI query inner-joins `counting_cameras`

-- app/db/occupancy_repository.py:863 get_counts_in_range_async, :884 get_timespan_aggregates_async,
-- :1006 get_cycle_peak_occupancy_async, :1065 get_cycle_average_occupancy_async, :1471 get_bucketed_cycle_flow_async
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

Orphan rows match nothing, so they are silently dropped from every reported figure.

The combination is a runaway

The sync's own reconciliation reads the per-camera list from get_timespan_aggregates_async, which is a LEFT JOIN from counting_cameras:

FROM counting_cameras c LEFT JOIN people_counting_events e ON ... WHERE c.is_active = 1

An orphan code has no counting_cameras row, so it never appears in that list, so:

total_local_in = sum(cam_counts_today.get(str(c_code), {}).get("in", 0) for c_code, _ in in_cams)
# -> 0, permanently
delta_in = max(0, current_artemis_in - 0)  # -> the FULL cumulative count

The entire cumulative Artemis count is re-injected as a new event on every 3-second poll, forever. At 1,200 polls per hour this inflates the events table without bound. The rows are invisible to the KPI queries, so the corruption shows up as unbounded table growth and (once someone registers that code as a camera, or removes the is_active filter) an absurd ingress figure.

The same trap fires for any camera whose counting_cameras.is_active is set to 0 while it is still a member of a live Artemis group.

KPIs corrupted

Directly: none while the orphan stays orphaned — which is the danger, since the loss is invisible. Once the code is registered or a query drops the join, Gross Ingress / Egress and everything downstream inflate by orders of magnitude. Meanwhile real traffic through a group with no mapped resources is never counted at all.

Suggested fix

Never write an event for a code that is not a registered camera. If the group→camera map is empty, log FLAG_UNMAPPED_GROUP and skip; do not invent a pseudo-camera.
If a group genuinely has no member cameras, auto-register it in counting_cameras as an explicit synthetic portal (camera_index_code = g_code, direction_type = BIDIRECTIONAL) so it is at least visible and operator-editable.
Add a startup + periodic integrity check: SELECT DISTINCT camera_index_code FROM people_counting_events WHERE camera_index_code NOT IN (SELECT camera_index_code FROM counting_cameras) — log loudly if non-empty.
Make the reconciliation baseline read from the same query shape the KPIs use, so ingestion and reporting can never diverge on what counts as a camera.
Add a test: a group whose relatedResourceInfoList is empty must produce zero events and one warning, not a growing table.

> 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 — two halves that combine into a runaway ### Half 1: events can be written with a `camera_index_code` that is not a camera When a resource group exposes no member resources, or when keyword matching empties a direction list, the sync falls back to the **group** code as though it were a camera: ```python # app/services/occupancy_service.py:1826-1830 group_cam_map[g_code] = { "in_cams": in_cams or [(g_code, g_name)], "out_cams": out_cams or [(g_code, g_name)], } ``` Rows are then inserted into `people_counting_events` with `camera_index_code = <resourceGroupIndexCode>`, which has no row in `counting_cameras`. ### Half 2: every KPI query inner-joins `counting_cameras` ```sql -- app/db/occupancy_repository.py:863 get_counts_in_range_async, :884 get_timespan_aggregates_async, -- :1006 get_cycle_peak_occupancy_async, :1065 get_cycle_average_occupancy_async, :1471 get_bucketed_cycle_flow_async 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 ``` Orphan rows match nothing, so they are **silently dropped from every reported figure**. ### The combination is a runaway The sync's own reconciliation reads the per-camera list from `get_timespan_aggregates_async`, which is a `LEFT JOIN` **from** `counting_cameras`: ```sql FROM counting_cameras c LEFT JOIN people_counting_events e ON ... WHERE c.is_active = 1 ``` An orphan code has no `counting_cameras` row, so it never appears in that list, so: ```python total_local_in = sum(cam_counts_today.get(str(c_code), {}).get("in", 0) for c_code, _ in in_cams) # -> 0, permanently delta_in = max(0, current_artemis_in - 0) # -> the FULL cumulative count ``` **The entire cumulative Artemis count is re-injected as a new event on every 3-second poll, forever.** At 1,200 polls per hour this inflates the events table without bound. The rows are invisible to the KPI queries, so the corruption shows up as unbounded table growth and (once someone registers that code as a camera, or removes the `is_active` filter) an absurd ingress figure. The same trap fires for any camera whose `counting_cameras.is_active` is set to `0` while it is still a member of a live Artemis group. ## KPIs corrupted Directly: none while the orphan stays orphaned — which is the danger, since the loss is invisible. Once the code is registered or a query drops the join, Gross Ingress / Egress and everything downstream inflate by orders of magnitude. Meanwhile real traffic through a group with no mapped resources is **never counted at all**. ## Suggested fix 1. **Never write an event for a code that is not a registered camera.** If the group→camera map is empty, log `FLAG_UNMAPPED_GROUP` and skip; do not invent a pseudo-camera. 2. If a group genuinely has no member cameras, auto-register it in `counting_cameras` as an explicit synthetic portal (`camera_index_code = g_code`, `direction_type = BIDIRECTIONAL`) so it is at least visible and operator-editable. 3. Add a startup + periodic integrity check: `SELECT DISTINCT camera_index_code FROM people_counting_events WHERE camera_index_code NOT IN (SELECT camera_index_code FROM counting_cameras)` — log loudly if non-empty. 4. Make the reconciliation baseline read from the **same** query shape the KPIs use, so ingestion and reporting can never diverge on what counts as a camera. 5. Add a test: a group whose `relatedResourceInfoList` is empty must produce zero events and one warning, not a growing table.

gabogg added the

ready-for-agent

label

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

gabogg referenced this issue

2026-09-21 13:57:02 +00:00

refactor(telemetry): deepen the backend streaming seam (Candidate 2; candidates 3 & 4 delivered) #14

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 from a pull request that will close it,

2026-09-22 15:22:42 +00:00