A health-data backfill can run more than once after a reinstall, new device, interrupted sync, or manual repair. If the server treats each upload as new, step totals, energy, water, and other daily aggregates can be inflated.
Device-local row IDs are often the wrong identity. The same historical fact may receive a different local identifier on another installation.
Build identity from meaning#
For a daily aggregate mirrored into an event table, derive a stable source key from semantic coordinates:
def backfill_source_key(
user_id: str,
local_day: date,
domain: str,
metric: str,
) -> str:
return f'health_backfill:v1:{user_id}:{local_day}:{domain}:{metric}'
Convert that key into a deterministic UUID and enforce uniqueness in the database. The same user, day, domain, and metric now resolves to the same row across devices.
Include the domain because similarly named metrics can exist in different table families. Version the key format so a future semantic change is explicit.
Use natural keys for snapshots#
Some data already represents one daily state. A sleep summary may naturally upsert on (user_id, local_day):
insert into sleep_sessions (...)
values (...)
on conflict (user_id, sleep_date)
do update set duration_minutes = excluded.duration_minutes;
Event-style and snapshot-style tables need different idempotency strategies.
Decide whether newer data replaces older data#
Deduplication answers which records are the same. It does not answer which value wins. Define whether repeated imports overwrite, merge, take the maximum, or become a no-op. That decision depends on the metric's source semantics.
Test across installation identities#
Run the same semantic history twice with different device row IDs and assert one final business result. Also test overlapping windows and concurrent uploads.
Idempotency lets backfills become a repair mechanism. Users can safely retry missing history without the cure corrupting the totals it is meant to restore.