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!
#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.
master ← Terdious:fix/purge-feature-states-duckdb
ouvert 04:47PM - 10 Jul 26 UTC
### Pull Request check-list
To ensure your Pull Request can be accepted as fast… as possible, make sure to review and check all of these items:
- [x] If your changes affect the code, did you write the tests? *(both paths rewritten with real DuckDB states — the previous tests seeded SQLite only, which is why they kept passing despite the bug; new assertions fail on the old code)*
- [x] Are server tests passing with coverage? (`npm run coverage` green locally; changed lines at 100%)
- ~~Did Cypress E2E tests pass?~~ *(server-only change)*
- [x] Is the linter passing? (`npm run eslint` on server)
- [x] Did you run prettier?
- ~~If you are adding a new feature/service, did you run the integration comparator?~~ *(no i18n change)*
- [x] Did you test this pull request in real life? *(in progress on a 448M-state installation — [WIP] until validated)*
- ~~If your changes modify the API (REST or Node.js), did you modify the API documentation?~~ *(no API change)*
- ~~If you are adding a new features/services which needs explanation, did you modify the user documentation?~~ *(bug fix)*
- ~~Did you add fake requests data for the demo mode?~~ *(no request change)*
### Description of change
#### Problem
PR #2104 (v4.45.0, Aug 2024) moved device states to DuckDB but left two SQLite queries behind:
1. **`purgeStatesByFeatureId`** — triggered when "keep history" is toggled off on a device feature — counted and deleted states in **SQLite**, which is empty on migrated installations. It logs `Purging "<id>": 0 states & 0 aggregates to delete.` and returns instantly: **the DuckDB states are never purged**. The bug stayed invisible for 2 years because right after the migration the SQLite leftovers made the purge "delete something", and nothing visibly read per-feature history until the new Activity view.
2. **The `device.destroy` guard** ("too much states, cleaning first") also counted **SQLite** states, so on migrated installations it never triggers, defeating its purpose of avoiding a long blocking delete (the actual DuckDB deletion in `destroy` was correct).
#### Fix
- `purgeStatesByFeatureId`: count and delete the states in **DuckDB** — the exact query `device.destroy` already uses. The DuckDB table has no `id` column, so the old `LIMIT`-chunked delete cannot be transposed: it is a single `DELETE`, like `destroy` does. SQLite states and aggregates are still purged afterwards as leftovers of installations that have not run the migration yet — otherwise the migration would re-import states of an already-purged feature.
- `device.destroy` guard: add the DuckDB state count of the device's features to the total (SQLite counts kept for non-migrated installations).
#### Tests
- `purgeStatesByFeatureId`: seeded with 110 DuckDB states + 5 SQLite leftovers + 3 aggregates → all purged, counts returned; plus the no-state path.
- `destroy`: the "too much states" guard now counts DuckDB states (the updated assertion fails on the old code); plus destroying a device without features.
Follow-up (separate discussion): a one-shot cleanup of the states accumulated because of this bug ("zombie" states of `keep_history = false` features, and orphaned states whose feature was deleted) — could be attached to the existing database-cleaning action.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
## Summary by CodeRabbit
* **Bug Fixes**
* Device deletion checks now include states stored across both database systems, preventing deletion when states remain.
* Device state purging now removes matching records from all supported storage layers.
* Purge progress and deletion counts now accurately reflect all removed states.
* Devices without features can now be deleted successfully.
#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)
@Will_71 (Fedora laptop)228 M states
45,400,000 (~20% of his database!)~29 min
„No slowdown, maybe +2 s on Activity“
Will’s 45 M confirm that this automatic cleanup was necessary for everyone.
Before/After
Another huge thanks to @Will_71 for fully testing both PRs
master ← Terdious:feat/purge-orphaned-duckdb-states
ouvert 05:04PM - 10 Jul 26 UTC
### Pull Request check-list
To ensure your Pull Request can be accepted as fast… as possible, make sure to review and check all of these items:
- [x] If your changes affect the code, did you write the tests? *(monthly-sliced purge over real DuckDB states, run-once flag semantics, empty-table path, startup trigger — new file at 100% lines)*
- [x] Are server tests passing with coverage? (full `npm run coverage` green locally)
- ~~Did Cypress E2E tests pass?~~ *(background job, no UI)*
- [x] Is the linter passing? (`npm run eslint` on front and server)
- [x] Did you run prettier?
- [x] If you are adding a new feature/service, did you run the integration comparator? (`npm run compare-translations` — the job type label is added to en/fr/de)
- [x] Did you test this pull request in real life? *(validated on a 448M-state installation and by a second tester who purged **45.4M orphaned states** — details in the comments)*
- ~~If your changes modify the API (REST or Node.js), did you modify the API documentation?~~ *(no API change — the earlier manual route/button of this PR was removed after discussion)*
- ~~If you are adding a new features/services which needs explanation, did you modify the user documentation?~~ *(automatic maintenance, visible in the jobs page)*
- ~~Did you add fake requests data for the demo mode?~~ *(no request)*
### Description of change
Follow-up of #2650, reworked after the discussion on the dev channel: instead of a manual button, the cleanup of **orphaned DuckDB states** (states whose `device_feature_id` no longer exists — leftovers of devices/features deleted while the per-feature purge counted the wrong database) is a **one-shot background job started automatically at startup**.
- **Run-once via a system variable** (`DUCKDB_ORPHANED_STATES_PURGED`, same pattern as the DuckDB migration), set **only after a complete run**: if Gladys restarts mid-purge, the job simply restarts at the next boot — deletes are idempotent, it only redoes the remaining work. No manual action ever needed.
- **As slow and gentle as possible**: no upfront count — counting orphans over 448M states held the read connection for **15-20 minutes** during testing, blocking every other read (charts, activity). Instead the history is walked in **weekly `created_at` slices**, and after each slice the purge **sleeps 5x the time the slice took** (duty cycle, capped at 60s): no DuckDB connection is ever held more than ~1-2 seconds, the average CPU/disk load stays around 1/6th, and Gladys stays responsive — field-tested on a 448M-state installation where fixed short pauses saturated the CPU for 17 minutes. Per-slice logs are greppable with the `purge-orphaned-duckdb-states` prefix. The parameters are cast to UUID explicitly to avoid a per-row VARCHAR cast.
- **Live progress**: DuckDB returns the number of deleted rows per statement, so the job accumulates the purged count on the fly and reports the progress as a percentage of the time range walked. Fully visible in the "background jobs" page (nicely, with #2652).
Measured on a 448M-state installation: the previous count-first approach took ~30 minutes with long connection holds; the sliced walk holds each connection a few seconds at most.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
## Summary by CodeRabbit
* **New Features**
* Added an idempotent one-time background cleanup task to purge orphaned DuckDB device state records, with execution progress reporting.
* The task runs automatically during device initialization and safely no-ops after it completes once.
* Added localized job labels for the new background task in English, German, and French.
* **Bug Fixes**
* Prevents orphaned DuckDB state entries from accumulating over time while preserving valid device states.
* **Tests**
* Added coverage for successful purge, already-completed runs, and empty-database scenarios.
#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):
Target : Kitchen presence detector › Motion detectionLive 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 reportLive 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.
master ← Terdious:feat/job-structured-data
ouvert 05:33PM - 10 Jul 26 UTC
> **Stacked on #2650 and #2651** — this branch contains their commits (the purge… jobs are the first consumers of the new job data). Once they are merged, this diff shrinks to the jobs part only (13 files, +166/-7).
### Pull Request check-list
To ensure your Pull Request can be accepted as fast as possible, make sure to review and check all of these items:
- [x] If your changes affect the code, did you write the tests? *(dataPatch merge, finish merge preserving context, invalid key rejected, job-not-found; each purge test asserts the job.data facts)*
- [x] Are server tests passing with coverage? (full `npm run coverage` running, all touched files at 100% lines)
- ~~Did Cypress E2E tests pass?~~ *(display-only addition on the jobs page)*
- [x] Is the linter passing? (`npm run eslint` on front and server)
- [x] Did you run prettier?
- [x] If you are adding a new feature/service, did you run the integration comparator? (`npm run compare-translations` — `jobsSettings.jobData.*` added to en/fr/de)
- [x] Did you test this pull request in real life? *(validated on a 448M-state installation: live steps, counts, durations and progress observed on real purges — details in the comments)*
- [x] If your changes modify the API (REST or Node.js), did you modify the API documentation? *(JSDoc on `job.updateProgress`; REST API unchanged)*
- ~~If you are adding a new features/services which needs explanation, did you modify the user documentation?~~ *(self-describing UI)*
- ~~Did you add fake requests data for the demo mode?~~ *(no new request)*
`npm run build` (Vite) passes locally.
### Description of change
#### Problem
The jobs page only shows a type, a progress percent and an error. For a purge job there is no way to know **what** is being purged, **how many** states were found, nor any **report** once finished. Concretely: the per-feature purge was a no-op for 2 years (#2650) and nobody could see it — a job displaying "0 states found (0 DuckDB + 0 SQLite)" would have exposed the bug on day one.
#### Design — extracted from #2412, simplified after the feedback on #2528
Following the review discussion on #2528: **no wrapper change and no detached variant**. Jobs are already fire-and-forget through the existing pattern (controller emits an event and answers immediately), and the job knows its own context better than any wrapper — so the job attaches its facts itself:
- **`job.updateProgress(id, progress, dataPatch)`**: optional third parameter, merged into `job.data`.
- **`job.finish` merges `data`** instead of overwriting it: a failure report no longer erases the context attached while running, and the report stays visible on completed jobs.
- **Validated schema, raw facts only**: `job.data` accepts a known set of keys (`device_name`, `device_feature_name`, `duckdb_states_count`, `sqlite_states_count`, `aggregates_count`, `orphaned_states_count`) holding names and counts — never sentences. The **front translates** them (`jobsSettings.jobData.*`, en/fr/de), so job reports work in every language and older jobs re-render correctly if wordings change.
#### First consumers — the three purge jobs
The jobs page now shows, live and after completion:
- *Target: Presence sensor › Motion detection*
- *States: 1,234,567 DuckDB + 0 SQLite — 3 aggregates*
- *Orphaned states: 456,789*
Any future job (energy recalculation, backups…) can reuse the same mechanism by adding its keys to the schema and its translations.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
## Summary by CodeRabbit
* **New Features**
* Added automatic cleanup of orphaned device states.
* Added detailed background-job progress, including targets, processing steps, state counts, orphaned counts, and duration.
* Added localized job labels and progress messages in English, German, and French.
* **Bug Fixes**
* Device deletion limits now include states stored across supported databases.
* Job progress and completion data now preserve previously recorded details.
@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).
).
