Is it safe to un-delete the raw heart-rate samples that the dense-intraday fold soft-deleted?

[BernhardBuckel]

This is a question, not a bug report. I am not asking for a change. I would like to know whether something I am considering on my own instance is safe, before I run it.

The question

Is it safe to un-delete (deleted_at = NULL) the raw heart-rate, SpO₂ and HRV samples that the dense-intraday fold soft-deleted, and to hide the hourly means that replaced them, directly in the database? If not, what would break?

Context

• HealthLog v1.39.9, self-hosted (Docker). I imported an Apple Health export.xml on 29 September.
• The fold then ran. It soft-deleted 794,986 raw PULSE rows (also 31,002 OXYGEN_SATURATION and 15,271 HEART_RATE_VARIABILITY) and replaced them with hourly stats:<HK>:<day>T<HH> rows (38,600, 20,917 and 14,975). That is the intended behaviour.
• On 3 October I set DENSE_INTRADAY_RETENTION_ENABLED=false so no further rows are folded. I would also like the per-sample history from before the window back (see why below).
• Re-importing does not look like a route: both unique indexes on measurements are not partial, so tombstoned rows still occupy their identities, and the import's update path does not clear deletedAt (import-apple-health-export.ts). Is that reading right?

What I tested (on a copy, not on the live database)

1. Took a pg_dump of the database and restored it into a separate throwaway Postgres. Row counts match the live database on measurements, deleted rows, rollups, workouts and users.
2. On that copy I ran the statement below. It un-hid 841,259 raw rows and hid 74,492 hourly rows, with no unique-index violation. There are no triggers on measurements.
3. The fold changed only deleted_at and updated_at on those rows (sync_version is still 1 on both the hidden and the visible ones).
4. The daily rollups for old days (DAY tier) have counts matching the hidden raw rows, so they appear to have been built from the raw rows and not from the hourly ones. Recomputing daily means from the restored rows agrees with the rollups closely (a fraction of a bpm on average).

begin;
update measurements
   set deleted_at = null, updated_at = now()
 where source = 'APPLE_HEALTH'
   and type in ('PULSE','HEART_RATE_VARIABILITY','OXYGEN_SATURATION')
   and external_id like 'sample:%' and deleted_at is not null;
update measurements
   set deleted_at = now(), updated_at = now()
 where source = 'APPLE_HEALTH'
   and type in ('PULSE','HEART_RATE_VARIABILITY','OXYGEN_SATURATION')
   and external_id like 'stats:%' and external_id ~ 'T[0-9]{2}$' and deleted_at is null;
commit;

It took about four to five minutes on a small container.

What I would like to know

1. Is this safe, and is anything else coupled to the fold? Besides deleted_at and updated_at, do any other tables, caches or derived data (rollups, stored series, insights) assume the raw rows are gone?
2. The sync feed. /api/sync/changes pages on an (updatedAt, id) watermark per domain (route.ts). Bumping updated_at on about 841,000 rows would present them as changed again. Does the iPhone app mirror PULSE, OXYGEN_SATURATION and HEART_RATE_VARIABILITY history, so that it would page through all of them? Would you rather I leave updated_at untouched, and what happens to a client that already received the fold's tombstones?
3. The purge. The daily measurement-tombstone-cleanup job hard-deletes soft-deleted rows older than TOMBSTONE_RETENTION_DAYS (native-client.ts, 75 days, as you mentioned on fix(labs): refuse a reading whose unit differs from its marker's #1096). I read that as covering the fold's tombstones too, so mine would be gone for good around 13 to 17 December. Is that right, and is there a supported way to keep them?
4. Who reads raw rows older than the window? The workout heart-rate curve only reconstructs for workouts inside the 90-day window (hr-series.ts), and the intraday pulse view has a "coarser fallback" for old days. Would restored raw rows be used by anything, or would I mainly gain raw fidelity for exports? I would like to know whether it is worth doing.
5. Rollups. If a rollup rebuild ran over those days while the raw rows are hidden, which result would it produce? Would I need to trigger a rebuild after un-hiding?
6. Is there a better route than raw SQL, for instance a setting that keeps raw rows beyond 90 days or an admin action that restores folded rows? I am happy to send a PR for something like that if you think it is useful; I have not proposed it as an issue yet.

State of my instance right now

Nothing from the statement above has been run on the live database. I hold a verified backup (checksummed) and have DENSE_INTRADAY_RETENTION_ENABLED=false set. I will not run anything until I hear back.

Related: #1107 (how the pulse chart averages), #1096.

[MBombeck]

Thanks for asking before running it, and for testing on a copy first. I went through each point in the code and then reproduced the whole sequence on a test database (fold, your swap, rollup rebuild, fold switched back on, purge), and checked the current iPhone app build for the sync questions, so the answers below are observed, not just read.

Short answer: yes, it is safe, with four changes to what you posted.

1. Narrow the un-delete to the fold's own tombstones. external_id like 'sample:%' also matches samples deleted for other reasons: by you, by a delete-by-source, or by a cross-source dedup. In the reproduction, a reading I had deleted by hand before the fold came back with the rest and went straight into every rollup. Restrict the first update to the fold run, for example by its deleted_at window (select date_trunc('minute', deleted_at), count(*) … group by 1 shows the run clearly) and to measured_at older than 90 days before that run.

2. Bump sync_version as well as updated_at: sync_version = sync_version + 1 on both updates, which is what the server itself does when it restores a measurement (reconcile-external-measurement.ts). On the iPhone side this costs nothing: the current app build does not consume /api/sync/changes at all (it reads charts and values from the server), so no 915k-row catch-up happens on the phone. Its Apple Health mirror only writes Withings, imported and manual readings, so restored Apple Health samples and the derived resting heart rate rows are never written back into Health.

3. Rebuild the rollups afterwards. The fold recomputes the DAY bucket from the raw rows before it runs, which is why your DAY counts match. WEEK, MONTH and YEAR are rebuilt afterwards from whatever is live, so today they are means of the hourly rows; in the reproduction a day with a workout hour read 91 bpm at the week level against 137 at the day level. After the swap, run POST /api/admin/rollups/recompute with your userId (admin, cookie session), then restart the container so nothing serves a cached series. Hiding the hourly rows, as you do, is required: with both visible every tier counts twice.

4. Keep DENSE_INTRADAY_RETENTION_ENABLED=false for good. With it on, the next run folds everything back. That converges cleanly (it adopts your hidden hourly rows instead of creating duplicates), but your raw rows would be tombstoned again.

To your other questions:

• Purge: yes, measurement-tombstone-cleanup hard-deletes any measurement tombstone older than 75 days, with no exclusion and no setting. Rows you restore are live and safe; the hidden hourly rows will be purged later, which is fine. Anything not restored by mid-December is gone for good.
• Re-import: your reading is right. Both unique indexes are full, and the import's update path does not clear deletedAt, so re-importing would not bring them back.
• What you gain: for restored days the intraday pulse view goes back from hourly means to 10-minute detail, the doctor report, FHIR and Coach read raw samples instead of hourly means, and exports carry the full history. The workout heart-rate curve does not change; it is limited to the last 90 days by design.
• Other side effects: the fold also wrote one derived resting heart rate row per day where no native one existed. It stays and is harmless.

There is no supported route for this today, only SQL. A setting that keeps raw rows (the switch you already use does that from now on) plus an admin action to restore folded samples for a date range would be worth having. If you'd like to send that as a PR, I'd welcome it; it would also take care of points 1 to 3 for whoever needs this next.