Versioning Derived Metrics and Recomputing Health History

Here is the failure this prevents. You improve your active-calorie estimate on a Tuesday. On Wednesday a user opens the app and their April total is 300 kcal lower than it was. Nothing was deleted, nothing was lost, no sample changed — you changed a function and applied it retroactively to everything. But the user had screenshotted that week. They post the two screenshots side by side and the support ticket says your app is losing their data. That reaction is specific to this product category: an e-commerce dashboard's revenue rollup can be corrected quietly and nobody has an emotional relationship with the old figure, whereas a fitness number is attached to a memory of a thing the person actually did. Rewriting it rewrites the memory.

The four inputs, and why all four drift

The reason "just store the computed value" fails for health data is that every argument to the function is unstable.

The raw samples change. Apple documents that HealthKit condenses workout data at least a few months old into quantity series samples and then deletes the originals — and that it "may condense samples for a given workout more than once … if the condenser algorithm changes". Users edit and delete data in the Health app at any time, which Apple explicitly tells you to expect. Providers retro-edit: a Garmin-style backfill can deliver two-year-old data through the same webhook path as live data, so "this arrived today" tells you nothing about which day it belongs to. A day's raw input set is never final.

The formula changes. That is the whole subject of this page.

The day boundary changes. Your daily rollup is keyed on a civil date, and the civil date depends on the offset in effect at each sample's own timestamp. Health Connect's startZoneOffset exists precisely so that, in Google's words, "history aggregations results stay consistent should user travel" — and if the offset is absent, the platform assumes the current zone. Get that wrong once and a travel week silently rebuckets. See timezones and day boundaries for the storage model.

The source-resolution policy changes — sometimes without you. Google documents that only Activity and Sleep are deduped by Health Connect, only inside the Aggregate API, and that the priority order is set by the end user: "Only end users can alter these priority lists." Your backend cannot read it. So an aggregate figure you stored last March is a function of a user-mutable setting you never observed, and the same day on the same device can produce a different total after the user reorders two apps in settings, with no change event to tell you.

Put together: a bare (user, metric, day, value) row is a claim with no provenance. You cannot answer "why is this 412?" and you cannot re-derive it.

The schema: version column, digest, and a dirty queue

CREATE TABLE daily_metric (
  user_id        uuid        NOT NULL,
  metric         text        NOT NULL,   -- 'active_energy_kcal'
  local_date     date        NOT NULL,   -- civil date, written at ingest
  value          numeric,                -- NULL = unknown. Never 0-for-unknown.
  unit           text        NOT NULL,
  formula_id     text        NOT NULL,   -- 'active_energy'
  formula_version int        NOT NULL,   -- which f() produced `value`
  source_policy  text        NOT NULL,   -- 'merged_statistics' | 'single_source' | 'summed'
  input_window   tstzrange   NOT NULL,   -- the instants that fed it
  inputs_digest  bytea,                  -- hash of contributing sample ids + versions
  computed_at    timestamptz NOT NULL,
  PRIMARY KEY (user_id, metric, local_date)
);

CREATE TABLE metric_formula (
  formula_id  text NOT NULL,
  version     int  NOT NULL,
  effective   date NOT NULL,
  summary     text NOT NULL,   -- human sentence: what changed and why
  PRIMARY KEY (formula_id, version)
);

CREATE TABLE recompute_unit (        -- claimable work, not a job step counter
  user_id     uuid NOT NULL,
  metric      text NOT NULL,
  window_start date NOT NULL,
  window_end   date NOT NULL,
  target_version int NOT NULL,
  status      text NOT NULL,   -- 'pending' | 'claimed' | 'done' | 'unrecomputable'
  claimed_at  timestamptz
);

Four things in there are load-bearing and routinely omitted.

value is nullable and NULL means unknown, not zero. Apple is explicit that a denied read is indistinguishable from no data, that sumQuantity() returns nil for an empty interval, and that under limited authorization you should "treat all data before that date as unknown — not an absence of data". A recompute that writes 0 into a day it could not read is how a formula improvement turns into fabricated history.

source_policy is separate from formula_version because the two change independently and the second one is often what actually moved the number. It also makes the row self-describing when someone asks whether this figure double-counts a watch and a phone.

inputs_digest is what lets you distinguish "the number changed because the formula changed" from "the number changed because the samples changed". Without it, every post-hoc investigation is a guess.

metric_formula matters more than it looks. An integer in a column tells nobody anything in eighteen months. A one-sentence human description of what v3 did, stored next to the version, is the difference between an auditable system and a mystery.

The two recompute triggers

They are genuinely different problems and should not share a policy.

The dirty-input trigger is downstream of incremental sync, and its output is exactly the input to this page. The correct shape is Apple's own, from WWDC20's synchronization session: the anchored query tells you which days are dirty, and a statistics query then tells you what those days now total. Never sum the anchored query's samples into a running counter — a replayed anchor, a reinstall, or a condensing pass will double it. On Android the mirror is getChanges: each UpsertionChange and DeletionChange maps to one or more (user, metric, local_date) cells, which you push onto the dirty queue. Note that a DeletionChange carries only the record id and not the type, so unless you stored the Health Connect metadata.id at ingest you cannot even work out which cell went dirty.

The formula trigger is the interesting one, because it is a product decision wearing an engineering costume.

Does last year's number change? Both answers are defensible

Recompute everything. The series is internally consistent end to end. A year-over-year comparison compares like with like. Trends are real rather than artifacts of a release. Cost: numbers your users remember will move, and the ones that move most are the ones belonging to your most-engaged users, because they are the ones with the most data sources.

Version forward from today. History is preserved exactly as the user saw it. Cost: your chart now contains a discontinuity at the version boundary, and any "vs last year" figure silently compares the output of two different functions. That is arguably the worse lie, because it is invisible.

What is not defensible is picking either one silently. Users notice, and in fitness they notice with unusual precision, because the numbers are attached to events — the marathon, the 90-day streak, the week they hit a PR.

A useful heuristic for choosing per metric: is this value a record of what happened, or an estimate of how the user was?

• Records — distance, duration, workout calories, a personal best, anything the user would describe as an achievement. Lean toward preserving history. Recomputing these rewrites something the person earned.
• Estimates — readiness, recovery, a fitness/fatigue balance, a trend score, a sleep-quality index. Lean toward full recompute. These are claims about a state, not records of an event, and a series containing two generations of a scoring model is not a series at all. Nobody has an emotional attachment to their readiness score from fourteen months ago.

The platform under you has already made this call, which is worth noticing. When HealthKit condenses old workouts it deletes the original samples, and Apple asserts that statistics are preserved across condensing — it makes no such promise about sample counts, UUIDs, or boundaries. Apple decided the aggregate is the contract and the raw sample shape is not. Decide the same thing explicitly for each of your metrics, write it down, and then version whatever is not the contract.

What we would actually build

Our position for a general fitness product: recompute, but make the change visible and reversible. Announce it in-app before it lands, keep the superseded values for a stated retention window so support can answer "what did it say before", and annotate the chart at the version boundary. That is a middle path with a real cost — you are storing two generations of values — and it is the one we would pay for.

Concretely, that is the four columns above on every derived row, a metric_formula table carrying one human sentence per version, a queue of claimable recompute units rather than a job with a step counter, and a retention window for superseded values long enough to outlast a support conversation. None of it is expensive. All of it has to exist before the first formula change, because the thing you cannot add retroactively is a record of which function produced the numbers you already shipped.

A worked formula change: calories, v1 to v2

v1: daily active energy = sum of every activeEnergyBurned sample landing in the local date, across all sources.

The bug: neither platform makes that safe. A plain HKSampleQuery performs no merging at all, and Apple's own framework engineers have said on the developer forums that an app using the non-statistics queries has to arbitrate between overlapping samples itself and is unlikely to reproduce HealthKit's merge correctly. On Android, everything outside Activity and Sleep is combined rather than deduped. So a user with a watch, a phone, and a third-party running app was being over-counted for years. Details of the resolution itself belong to deduplicating health data; what matters here is that fixing it is a formula change.

v2: take the merged figure from a statistics query on iOS rather than summing samples yourself, and on Android aggregate per DataOrigin and apply your own explicit per-metric source priority.

The shape of the delta is what makes this migration painful: v2 is identical for single-source users and materially lower for multi-source users. It does not look like a release. It looks like a bug in one person's account.

The migration we would run:

1. Shadow-write. Compute v2 alongside v1, store it, display nothing. No user-visible change yet.
2. Measure the delta distribution. Per user, per metric: how many days move, and by how much. You now know exactly who is affected before anyone is affected. This is the step teams skip, and it is the only one that turns the launch decision into a decision rather than a gamble.
3. Choose per the record-vs-estimate heuristic above. Calories burned in a specific logged workout is closer to a record; a daily active-energy total feeding a trend chart is closer to an estimate.
4. Announce, then recompute newest-first, with the pre-change values retained.
5. Suppress derived-of-derived until the job finishes — streaks, PRs, "best ever", weekly comparisons. A half-recomputed history produces a wrong personal best, and the user will see it and remember it.

Because calorie estimation is a modelled quantity rather than a measurement in the first place (how fitness apps estimate calories), this will not be the last time you change it. Build the machinery once.

Recompute is a background job with the same discipline as backfill

Treat it exactly like historical backfill: checkpointed, resumable, budgeted, newest-first. Claim work units from a table rather than tracking a step counter, so a crashed worker costs one window and "resume" and "repair" are the same code path. Order newest-first for the same reason Timescale's refresh_continuous_aggregate() defaults refresh_newest_first to TRUE, only more so — the user is looking at this week.

There is one health-specific trap that makes recompute more dangerous than backfill, not less. Backfill is throttled for you: provider rate limits are per user, so a two-year import is slow and there is nothing you can do about it. Recompute has no such governor, because the data is already yours. A single command can queue every user's entire history against your own database at full speed. Teams that survived backfill fine melt their primary on their first fleet-wide recompute for exactly this reason. Give recompute a rate budget that loses to live ingest, always.

Two storage-layer notes if you materialize rollups. TimescaleDB continuous aggregates do not repair themselves reliably after history is edited: the docs state that on a refresh "it's not guaranteed that all buckets will be updated", and force => TRUE is the documented repair tool, with the docs' own warning that it "can be very expensive". And if you use ClickHouse, its deduplication is documented as eventual — "at any moment in time your table can still have duplicates" — so a ReplacingMergeTree can serve a doubled step count for an unbounded interval. That is noise in an analytics query and a support ticket on a home screen. Keep the user-facing daily number in a transactional store.

The honest limit: not all history is recomputable

"We'll just recompute" is a promise you can only keep if you still have the raw samples. Four things routinely take that away, and only the first exists outside health:

1. You dropped the raw data to save storage after computing the rollup. The derived value is now the only artifact, permanently frozen at whatever version produced it.
2. Your retention policy or a deletion request removed it. Health data attracts shorter retention commitments and stronger erasure rights than most datasets, and your own published schedule is therefore a hard ceiling on your recompute reach — see health data retention and deletion. This is a case where two good policies genuinely conflict, and you should resolve it on purpose rather than discovering it during a migration.
3. Re-pulling from the source does not work either. Apple documents that HKDeletedObject records are temporary and may be purged at any time, and that condensing deletes original samples. Health Connect grants only 30 days of history by default without PERMISSION_READ_HEALTH_DATA_HISTORY, and that window is anchored to first permission grant and resets on reinstall. The provider is not a backup.
4. Some inputs were never observable by you. A Health Connect aggregate depends on a user-controlled priority list your backend cannot read, so a figure you stored last year is not reproducible even with perfect raw data — unless you aggregated per DataOrigin and did your own merge, which is precisely why we would.

So keep a per-(user, metric) recomputable_from date, maintained as a fact rather than an assumption. When someone proposes improving a formula and fixing history, that column is the answer to "how far back can we actually go?" — and where you cannot recompute, label the boundary in the UI instead of drawing one continuous line across two definitions.

One more input people forget: for any metric that consumes a profile field — body weight, age, max heart rate — that field is a fourth argument to the function, and it changes with no sample changing at all. A user correcting their weight invalidates every calorie estimate you ever made for them. Decide now whether that is a recompute trigger. We would say yes for calories, which means it is the same product dilemma, arriving one user at a time.

When to buy this instead

If your derived metrics are table stakes rather than your differentiator, an aggregator that normalizes and versions them is often the better trade — health data aggregator APIs exist substantially to absorb this work. Be clear about what you are buying, though: you are not removing the versioning problem, you are inheriting someone else's. Ask a prospective vendor three questions before you sign. Do you version derived metrics? Can I pin a version? What happens to my historical values when you change a formula? A vendor without good answers has handed you the same problem with less control over the timing.

Build it yourself when the derived metric is the product — a proprietary readiness or recovery score you market by name. In that case you cannot afford to have its definition change under you on someone else's release schedule, and the versioning machinery on this page is not overhead, it is the product's spine.

In short

The version column costs one integer per row. Not having it costs you the ability to answer the only question that matters when a user says their history changed: what did we compute, with which formula, from which samples?