Importing real devices without corrupting yesterday

Five devices, five ideas of what a day is, five export formats, and none of them asked your opinion. This is the messiest lesson in the level and the one whose habits transfer most directly to work — bank statements and order exports fail in exactly these ways.

Raw first, always

Never parse straight into your clean tables. Land the untouched payload first, then derive:

CREATE TABLE health_raw (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  source       TEXT NOT NULL,        -- apple | withings | cpap | manual
  received_utc TEXT NOT NULL,
  payload      TEXT NOT NULL,        -- exactly what arrived, untouched
  processed    INTEGER NOT NULL DEFAULT 0,
  note         TEXT
);Copy

The reason is not tidiness, it is recovery. Your parser will be wrong about something — a unit, a timezone, a field that means something else than you assumed. If you kept the raw payload, that is an afternoon of reprocessing. If you did not, it is data you no longer have.

The three rules, together this time

Lesson 8.3 met these separately. Importing is where they combine, and where getting one right while missing another still ruins the data.

Aggregate, never replace

A day's steps arrive in fragments. steps = ?2 keeps the last fragment; steps = steps + ?2 keeps them all. The second is correct.

And therefore: make a repeat a no-op

Aggregation without idempotency is a loaded gun. Every source record needs a stable key — the source's own id, or a hash of the payload if it has none:

const key = src.uuid ?? await sha256(`${src.type}|${src.start}|${src.end}|${src.value}`);Copy

with the partial unique index from lesson 9.1 doing the enforcing. Then re-running an import is boring, which is exactly what you want at 2am when you are not sure whether the first run finished.

And compare on the date prefix

substr(ts,1,10) = '2026-09-16', never a full-timestamp range. Multiple pipelines will write multiple formats into one column across the years. Assume it, and be unaffected.

What “a day” means to five devices

Pick one convention, write it down in the schema comments, and apply it at the boundary. A workable one: a sleep session belongs to the day it ends on — last night's sleep is this morning's row — and everything else belongs to the UTC day it occurred on. Any convention beats an implicit one that differs per importer.

When two devices disagree

Your scale says 78.4 and your watch says 79.1. Both are “correct”. Do not average them and do not let the last writer win.

Keep both rows — that is what source is for — and resolve at read time with a stated precedence:

const PRECEDENCE = { scale: 3, manual: 2, watch: 1 };   // for 'weight'

SELECT value, source FROM measurements
 WHERE kind = 'weight' AND day = ?1
 ORDER BY CASE source WHEN 'scale' THEN 3 WHEN 'manual' THEN 2 ELSE 1 END DESC,
          taken_utc DESC
 LIMIT 1;Copy

Resolving at read time means the rule is visible, changeable, and never destroys data. Resolving at write time means a decision you made once, cannot see, and cannot undo.

A pipeline that tells you when it did nothing

The failure that costs weeks is not a crashing import — it is one that runs, succeeds, and processes zero records. Make that loud:

const summary = { source, received: records.length, inserted: 0, duplicates: 0, rejected: [] };
…
if (summary.inserted === 0 && summary.duplicates === 0) {
  // nothing arrived AND nothing was a known repeat — that is not "no news"
  throw new Error(`Import from ${source} produced nothing from ${records.length} records`);
}Copy

Note the distinction: zero inserts with many duplicates is a healthy re-run. Zero of both, from a non-empty file, means your parser stopped understanding the format — which is what happens after a vendor's app update, silently, on a Tuesday.

After every import, look at the rows

-- did today actually gain anything?
SELECT source, COUNT(*) FROM measurements WHERE day = date('now') GROUP BY source;

-- any day suspiciously poorer than its neighbours?
SELECT day, steps FROM activity_daily ORDER BY day DESC LIMIT 14;

-- the format-drift canary
SELECT DISTINCT length(taken_utc) FROM measurements;Copy

That last query is worth keeping. The day it returns more than one row is the day a new pipeline started writing a different timestamp shape — and you will find out in a second instead of in six months.

Where next