En testant la nouvelle vue Activité de la 4.82, on est tombé sur quelque chose d’inattendu — et ça a donné trois PRs qui se complètent. Merci énorme à @Will_71 pour ses tests sur sa propre base !
#2650 )En voulant purger les états d’un capteur bavard (décocher « Conserver l’historique »), la tâche répondait instantanément : 0 states to delete… alors que la vue Activité affichait des milliers d’états pour cette feature.
Verdict après archéologie git :
Question
Réponse
Cassé depuis
v4.45.0 (août 2024)
Cause
La migration DuckDB (#2104 ) a déplacé les états, mais 2 requêtes SQLite ont été oubliées
Endroit 1
purgeStatesByFeatureId (le toggle « Conserver l’historique ») comptait et supprimait dans SQLite, désormais vide → no-op total
Endroit 2
Le garde-fou de device.destroy (« too much states ») comptait aussi SQLite → protection anti-blocage morte
Pourquoi invisible
Rien n’affichait l’historique par feature… jusqu’à la vue Activité
La #2650 migre les deux vers DuckDB (même requête que celle que destroy utilisait déjà) et garde le nettoyage des restes SQLite pour les installations pas encore migrées.
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 )Conséquence du bug : chaque device/feature supprimé depuis 2 ans a pu laisser ses états « orphelins » dans DuckDB — invisibles, mais scannés par chaque requête et occupant du disque.
Sur proposition de @pierre-gilles : une tâche automatique one-shot au démarrage (pattern de la migration DuckDB : Variable système posée uniquement en fin de run → si Gladys redémarre en plein milieu, elle repart seule au boot suivant — testé en vrai en coupant le container au milieu
Et « la plus lente possible » : pas de comptage préalable (qui tenait la connexion lecture 15-20 min sur ma base), parcours en tranches hebdomadaires avec une pause de 5× la durée de chaque tranche — la purge ne consomme jamais qu’une fraction des ressources et s’adapte toute seule à la machine.
Test terrain
Base
Orphelins trouvés
Durée
Impact ressenti
Moi (serveur partagé avec HA + 2ᵉ Gladys)
448 M d’états
20
1 h 49 (cache froid) / 29 min (chaud)
Vue Activité : 125 ms pendant la purge (contre 44 s avec la 1ʳᵉ version non bridée)
@Will_71 (laptop Fedora)228 M d’états
45 400 000 (~20 % de sa base !)~29 min
« Aucun ralentissement, peut-être +2 s sur l’Activité »
Les 45 M de Will confirment que ce nettoyage automatique était nécessaire pour tout le monde.
Avant/Après
Encore un grand merci à @Will_71 pour les tests complets des 2 PR
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 )Si la page Tâches avait affiché « 0 état trouvé » face à une Activité pleine, le bug de purge n’aurait pas survécu 2 ans. C’est maintenant le cas — les tâches portent des données structurées (traduites côté front, donc dans toutes les langues) :
Cible : Détecteur de présence cuisine › Détection mouvementÉtapes en direct : En attente de la base de données… / Calcul du nombre d’états… / Suppression des états… / Suppression des agrégats… (deux purges simultanées affichent honnêtement l’une qui travaille et l’autre qui attend)Comptages : États à supprimer : N DuckDB + N SQLite — N agrégats, puis États supprimés : … en compte-rendu persistantCompteur live pour la purge d’orphelins, % par tranches , durée qui tique pour les tâches en cours
Conception validée par les échanges sur la #2528 : aucune modification du wrapper de jobs — les tâches restent lancées par le pattern événement existant, et chaque job attache lui-même ses données via un paramètre optionnel d’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 les 3 PRs sont prêtes (tests + coverage 100 % sur les lignes modifiées, CI verte, validées en réel sur deux installations). Ordre de merge : #2650 → #2651 → #2652 (la dernière est empilée sur les deux autres, son diff rétrécira après leurs merges).
).
