[oura] 12 of every 13 SpO2 samples are discarded on a shared primary key #1070

Labels: bug, oura, data-loss, cross-platform
Affects: main @ 3b86b6ef (Release 9.3.1). Not branch-specific.

Summary

The Oura 0x6F spo2_event record carries 13 per-second samples. All 13 are written to
spo2Sample with the same timestamp, and the table's primary key is (deviceId, ts), so the
first sample wins and the other twelve are silently dropped on insert.

The night is therefore stored at 1/13 resolution, and the loss is permanent — the ring trims its
banked history once the offload is acked, so these seconds are not re-fetchable.

Evidence

From one overnight capture (Gen 3, BLB_03, app 9.3.1, 488-minute sleep window):

The survivor is always values[0], which is what an ON CONFLICT DO NOTHING on (deviceId, ts)
produces when the whole record is written at one second.

Why this is worse than "1/13 of the detail"

Packets arrive every ~13 s, so the surviving series has no temporal gaps — which makes the loss easy
to mistake for harmless decimation. It is not, because the events of interest are shorter than the
sampling interval. Recall of dip runs, measured on the same night against the full-resolution series
reconstructed from the diagnostic sidecar:

Run lengths are 1–60 s with a median near 10 s, i.e. shorter than the 13 s spacing of the survivors.

Measurement trap worth recording. Measured instead as dips ≥4 points below a 10-minute rolling
baseline, recall is 96–100 %, because a baseline-relative dip is a slow, wide feature spanning
several survivors. A health check written that way reports the stream as fine while every short
event is already gone. Use absolute-threshold runs.

Thresholds above are on the ring's uncalibrated scale and are not saturation readings (see
"Out of scope"); they stand in for short, sharp events regardless of what they correspond to.

Root cause

1. Packages/OuraProtocol/Sources/OuraProtocol/Decoders.swift:239 — decodeSpO2PerSample stamps
   every sample with the record's own time:

   out.append(OuraSpO2(ringTimestamp: rec.ringTimestamp, value: raw))   // :250

   OuraSpO2 (OuraEvents.swift:47) has no position field, so the sample's offset within the record
   is lost at decode time and cannot be recovered downstream.
2. Packages/WhoopStore/Sources/WhoopStore/OuraStreamMapping.swift:89 writes them all at the one
   record ts:

   out.spo2.append(SpO2Sample(ts: ts, red: v.value, ir: 0, unit: v.unit))

3. spo2Sample is keyed PRIMARY KEY (deviceId, ts) → twelve collisions per record.

decodeSpO2DC (0x77, Decoders.swift:272) has the same pattern. It is filtered out before the store
today (unit == "raw" guard), so it loses nothing durable, but the decoder should be made consistent
so the channel is usable later.

Proposed fix

Give each sample its own second, using the convention the codebase already applies to the other
multi-sample Oura record.

Precedent: OuraHypnogramAssembler lays a burst's sleep codes backward at the documented 30 s
epoch from the anchored burst end, and OuraStreamMapping's .sleepPhase arm states the reason
explicitly — "30 s spacing makes every code a distinct (deviceId, ts, kind) row". SpO2 is the same
problem at a 1 s cadence.

1. OuraEvents.swift — add index: Int to OuraSpO2, mirroring the existing index on the
   hypnogram sample. Default it in the memberwise init to keep call sites source-compatible.
2. Decoders.swift — decodeSpO2PerSample and decodeSpO2DC stamp index: as they walk the body.
   ringTimestamp stays the record's; only the position is added.
3. OuraStreamMapping.swift — lay the samples backward from the record time at 1 s:
   SpO2Sample(ts: ts - (count - 1 - v.index), …), so the last sample keeps the record's own ts
   and the record's anchor semantics are unchanged.
4. Kotlin twin: android/…/protocol decoder + android/…/data/OuraStreamMapping.kt.

Cadence: derived, not guessed

Measured over the same capture: packet interval median 13 s (p10 12, p90 14), 13 values per
packet. The values therefore tile the interval at exactly 1 Hz, and back-laying reproduces a
gapless series across the whole night with no overlap between adjacent packets. This is the same
standard the hypnogram assembler is held to — a documented cadence plus an anchor, not an assumed one.

No migration is needed: the change is forward-only. Nights already recorded stay at 1/13 and cannot be
repaired.

Tests

• A 13-value 0x6F record yields 13 rows at 13 distinct seconds, the last equal to the record ts.
• Round-trip through OuraStreamMapping asserts no collisions and the exact second sequence.
• Kotlin twin asserts the identical seconds (parity contract).
• Files: Packages/WhoopStore/Tests/WhoopStoreTests/OuraStreamMappingTests.swift,
  android/app/src/test/java/com/noop/data/OuraStreamMappingTest.kt.

Verification

• cd Packages/OuraProtocol && swift test; cd Packages/WhoopStore && swift test
• cd android && ./gradlew testFullDebugUnitTest
• Both covered by swift-packages.yml; no app-target Swift is touched.
• One post-fix overnight capture to confirm 13 rows per packet at 13 distinct seconds in a real DB.

Out of scope

The >100 % calibration question. 23.2 % of values on this night exceed 100 (max 107), so the stream is
an uncalibrated index, not a saturation percentage. It stays in SpO2Sample.red and is never written
to spo2Pct, exactly as the current decoder comment says. Do not add an offset here: the excess
rate swings 7.6 %–52 % between nights, so no single-offset correction is defensible. That belongs in a
separate issue.