[data-veracity] Calibration log dedup destroys audit history and backfills the multiplier it later reports

gabogg commented

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

quarantine_and_deduplicate_calibration_logs_async (app/db/occupancy_repository.py:1410) performs three destructive or fabricating writes on the table that backs the deck's Nocturnal Calibration Ledger and the k̂ drift chart.

1. Hard delete of all but the newest log per cycle

DELETE FROM occupancy_calibration_logs
WHERE id NOT IN (
    SELECT MAX(id) FROM occupancy_calibration_logs
    GROUP BY CASE WHEN cycle_date IS NOT NULL AND cycle_date != '' THEN cycle_date ELSE timestamp_epoch END
)

Only the highest id per cycle_date survives. An earlier MANUAL_OVERRIDE recorded by an operator is silently destroyed by a later automatic run on the same cycle. CONTEXT.md §3 lists MANUAL_OVERRIDE as a first-class trust status, and performed_by / reason exist precisely to record who intervened — those rows are being deleted.

The deck presents this table as an audit trail. An audit trail that is pruned to one row per day, keeping whichever write happened last, is not one.

2. Cycle dates backfilled by blind subtraction

UPDATE occupancy_calibration_logs
SET cycle_date = DATE(timestamp_epoch - 86400, 'unixepoch', 'localtime')
WHERE cycle_date IS NULL OR cycle_date = ''

This assumes every log was written roughly 24 h after the cycle it describes. True for the 04:15 nocturnal run; false for a manual calibration executed at 23:00, which gets attributed to the wrong cycle — and then, per (1), can delete that cycle's real log. It also uses SQLite 'localtime', i.e. server timezone (see the separate timezone issue).

3. The multiplier is invented where it was not recorded

UPDATE occupancy_calibration_logs
SET computed_exit_multiplier = ROUND(CAST(total_in AS REAL) / MAX(1, total_out), 5)
WHERE (computed_exit_multiplier IS NULL OR computed_exit_multiplier = 1.0) AND total_out > 0

Two problems:

A cycle whose multiplier genuinely converged to exactly 1.000 is indistinguishable from an unset one, and gets overwritten with the raw I/E ratio.
The raw ratio is not the quiet-window convergence result. It is a different quantity, written into the same column, and then consumed by compute_ewma_multiplier and compute_multiplier_variance as though it were a measurement.

The deck plots this column as "Exit multiplier k̂ drift — nightly convergence". Some of those points are not convergence results; they are ratios backfilled by a maintenance job.

KPIs corrupted

Nocturnal Calibration Ledger (Month) · k̂ drift chart and its σ band · multiplier_variance and therefore the width of the 95% CI on the Day occupancy envelope · Trust Index.

Suggested fix

Never delete. Replace the dedup DELETE with a superseded_by / is_current column; keep every row. Retention already prunes at 365 days (prune_retention_data_async), which is the right place for deletion.
Never overwrite a MANUAL_OVERRIDE. If dedup must collapse rows, it must skip operator-authored ones.
Resolve cycle_date through the real business-cycle boundary function, not timestamp - 86400.
Distinguish "unset" from 1.0: make computed_exit_multiplier nullable and stop backfilling. If a ratio is wanted, store it in its own column (raw_io_ratio) so the drift chart plots only genuine convergence results.
Add a test asserting that a MANUAL_OVERRIDE row survives a dedup pass.

> 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 `quarantine_and_deduplicate_calibration_logs_async` (`app/db/occupancy_repository.py:1410`) performs three destructive or fabricating writes on the table that backs the deck's **Nocturnal Calibration Ledger** and the **k̂ drift** chart. ### 1. Hard delete of all but the newest log per cycle ```sql DELETE FROM occupancy_calibration_logs WHERE id NOT IN ( SELECT MAX(id) FROM occupancy_calibration_logs GROUP BY CASE WHEN cycle_date IS NOT NULL AND cycle_date != '' THEN cycle_date ELSE timestamp_epoch END ) ``` Only the highest `id` per `cycle_date` survives. An earlier `MANUAL_OVERRIDE` recorded by an operator is silently destroyed by a later automatic run on the same cycle. `CONTEXT.md` §3 lists `MANUAL_OVERRIDE` as a first-class trust status, and `performed_by` / `reason` exist precisely to record who intervened — those rows are being deleted. The deck presents this table as an **audit trail**. An audit trail that is pruned to one row per day, keeping whichever write happened last, is not one. ### 2. Cycle dates backfilled by blind subtraction ```sql UPDATE occupancy_calibration_logs SET cycle_date = DATE(timestamp_epoch - 86400, 'unixepoch', 'localtime') WHERE cycle_date IS NULL OR cycle_date = '' ``` This assumes every log was written roughly 24 h after the cycle it describes. True for the 04:15 nocturnal run; false for a manual calibration executed at 23:00, which gets attributed to the wrong cycle — and then, per (1), can delete that cycle's real log. It also uses SQLite `'localtime'`, i.e. server timezone (see the separate timezone issue). ### 3. The multiplier is invented where it was not recorded ```sql UPDATE occupancy_calibration_logs SET computed_exit_multiplier = ROUND(CAST(total_in AS REAL) / MAX(1, total_out), 5) WHERE (computed_exit_multiplier IS NULL OR computed_exit_multiplier = 1.0) AND total_out > 0 ``` Two problems: - A cycle whose multiplier genuinely converged to exactly `1.000` is indistinguishable from an unset one, and gets overwritten with the raw `I/E` ratio. - The raw ratio is **not** the quiet-window convergence result. It is a different quantity, written into the same column, and then consumed by `compute_ewma_multiplier` and `compute_multiplier_variance` as though it were a measurement. The deck plots this column as *"Exit multiplier k̂ drift — nightly convergence"*. Some of those points are not convergence results; they are ratios backfilled by a maintenance job. ## KPIs corrupted Nocturnal Calibration Ledger (Month) · k̂ drift chart and its σ band · `multiplier_variance` and therefore the width of the 95% CI on the Day occupancy envelope · Trust Index. ## Suggested fix 1. **Never delete.** Replace the dedup `DELETE` with a `superseded_by` / `is_current` column; keep every row. Retention already prunes at 365 days (`prune_retention_data_async`), which is the right place for deletion. 2. Never overwrite a `MANUAL_OVERRIDE`. If dedup must collapse rows, it must skip operator-authored ones. 3. Resolve `cycle_date` through the real business-cycle boundary function, not `timestamp - 86400`. 4. Distinguish "unset" from `1.0`: make `computed_exit_multiplier` nullable and stop backfilling. If a ratio is wanted, store it in its own column (`raw_io_ratio`) so the drift chart plots only genuine convergence results. 5. Add a test asserting that a `MANUAL_OVERRIDE` row survives a dedup pass.

gabogg added the

ready-for-agent

label

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

gabogg referenced this issue

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 from a commit

2026-09-23 22:48:09 +00:00

chore(wip): open draft for calibration audit trail and trust vocabulary (#34, #33)

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

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

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

gabogg commented

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

Author

Owner

Being addressed in draft PR #71, one of four [data-veracity] drafts declared on 2026-09-23 (#68, #69, #70, #71). Each will be triaged, reviewed and implemented in order; the PR description lists the open design points to settle first.

Being addressed in draft **PR #71**, one of four [data-veracity] drafts declared on 2026-09-23 (#68, #69, #70, #71). Each will be triaged, reviewed and implemented in order; the PR description lists the open design points to settle first.

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~~

refactor(config): replace inline production values with named config keys and constants #74

gabogg referenced this issue

2026-09-24 14:45:29 +00:00

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

gabogg referenced this issue

2026-09-24 16:08:00 +00:00

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

gabogg referenced this issue