While testing the new Activity view in 4.82, we stumbled upon something unexpected — and it resulted in three complementary PRs. Huge thanks to @Will_71 for testing on his own setup!
Episode 1 — the 2-year-old invisible bug (PR #2650)
When trying to purge the states of a chatty sensor (uncheck „Keep History“), the task responded instantly: 0 states to delete… while the Activity view displayed thousands of states for this feature.
Verdict after git archaeology:
Question
Answer
Broken since
v4.45.0 (August 2024)
Cause
The DuckDB migration (#2104) moved the states, but 2 SQLite queries were forgotten
Location 1
purgeStatesByFeatureId (the „Keep History“ toggle) counted and deleted in SQLite, now empty → total no-op
Location 2
The safeguard of device.destroy („too many states“) also counted in SQLite → dead anti-lock protection
Why invisible
Nothing displayed the history by feature… until the Activity view
The #2650 migrates both to DuckDB (same query that destroy was already using) and keeps the cleanup of SQLite leftovers for not-yet-migrated installations.
Episode 2 — how many orphaned states are lurking in your setup? (PR #2651)
Consequence of the bug: every device/feature deleted in the past 2 years could have left its „orphaned“ states in DuckDB — invisible, but scanned by every query and taking up disk space.
On @pierre-gilles‘ suggestion: an automatic one-shot task on startup (migration DuckDB pattern: system variable set only at the end of the run → if Gladys restarts in the middle, it resumes on the next boot — tested in real life by cutting the container in the middle ).
And „as slow as possible“: no prior counting (which held the read connection for 15-20 minutes on my database), processed in weekly batches with a pause of 5× the duration of each batch — the purge never consumes more than a fraction of resources and adapts itself to the machine.
Field test
Database
Orphans found
Duration
Perceived impact
Me (shared server with HA + 2nd Gladys)
448 M states
20
1 h 49 (cold cache) / 29 min (warm)
Activity view: 125 ms during purge (vs. 44 s with the first unbridled version)
Another huge thanks to @Will_71 for fully testing both PRs
Episode 3 — the Tasks page that would have detected the bug in a day (PR #2652)
If the Tasks page had displayed „0 states found“ when facing a full Activity, the purge bug wouldn’t have lasted 2 years. That’s now the case — tasks carry structured data (translated on the frontend, so in all languages):
Live steps: Waiting for the database… / Calculating number of states… / Deleting states… / Deleting aggregates… (two simultaneous purges honestly show one working and the other waiting)
Counts: States to delete: N DuckDB + N SQLite — N aggregates, then States deleted: … in persistent report
Live counter for orphaned states purge, % by batches, ticking duration for ongoing tasks
Design validated by discussions on #2528:no modification of the job wrapper — tasks are still launched by the existing event pattern, and each job attaches its own data via an optional parameter of updateProgress.
For review
@pierre-gilles the 3 PRs are ready (tests + 100% coverage on modified lines, green CI, validated in real life on two installations). Merge order: #2650 → #2651 → #2652 (the last one is stacked on the other two, its diff will shrink after their merges).
CodeRabbit was right about the critical point: the last unbounded batch was evaluating the states written during the purge against a frozen snapshot of the features — a paired device during the ~30 min purge could lose its first states. Fixed with a cutoff captured before the snapshot, applied to all batches, + regression test. The missing test for the « zero feature » branch is also added; the SQL injection warning is a false positive (placeholders only), addressed in the PR.
Shared mea culpa with Fable 5 on this one: two reasonable decisions in isolation (final open batch to cover states written during the run + feature snapshot at the start) became dangerous when combined — and I hadn’t run a dedicated review pass with Fable at the end of development. Good illustration that cross-review bot + agent + human catches different blind spots
).
