[storage] Tiered storage + retention for decoded biometric streams — 6.5 GB / 70 days → ~4.5 GB at 5 years (complete feature) #1911

What happens

A WHOOP 5.0/MG worn 24/7 generates ~93 MB/day of decoded per-second biometric data in on-device SQLite. After 70 days that is ~6.5 GB; at the 5-year horizon it is ~100–170 GB — far beyond reasonable phone storage. There is no retention policy on the decoded streams today, so growth is unbounded.

This is not a leak and not a bug in any single table — it is the expected consequence of storing every per-second decoded sample forever with no tiering, downsampling, or ageing. The rawBatch outbox (50 MB cap) and v18AuxSample (7-day rolling, 604 800 rows) are already bounded; the 12 durable decoded stream tables are not.

This issue tracks the complete tiered-storage feature as one shippable unit: aggregate tables, background rollup, hourly cold tier, time-based retention, user-configurable settings, storage diagnostics, cold-archive export, and application-level compression. It is not a phased roadmap — the whole feature lands together, cross-platform, with one migration set and one acceptance bar.

Current schema — the 12 unbounded durable decoded stream tables

These tables are created in Packages/WhoopStore/Sources/WhoopStore/Database.swift (GRDB migrations) and mirrored byte-identically in android/app/src/main/java/com/noop/data/Entities.kt + WhoopDatabase.kt (Room). All have NO retention policy — they grow forever.

hrSample (v1) — Heart rate, 1 row/second

// Database.swift v1
try db.create(table: "hrSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()        // unix seconds
    t.column("bpm", .integer).notNull()
    t.primaryKey(["deviceId", "ts"])
}
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

// Entities.kt
@Entity(tableName = "hrSample", primaryKeys = ["deviceId", "ts"])
data class HrSample(val deviceId: String, val ts: Long, val bpm: Int, val synced: Int = 0)

rrInterval (v1, widened v24) — R-R intervals, ~1.16 rows/second at 70 bpm

// Database.swift v1
try db.create(table: "rrInterval") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("rrMs", .integer).notNull()
    t.primaryKey(["deviceId", "ts", "rrMs"])
}
// v24: PK widened to (deviceId, ts, rrMs, seq) — seq breaks ties for equal successive beats
// v30: added "ord" INTEGER (emission order, nullable)
// v32: added "srcChannel" INTEGER (which sensor channel, nullable)
// v35: added "tsSuspect" INTEGER (future-timestamp quarantine, nullable)
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

// Entities.kt
@Entity(tableName = "rrInterval", primaryKeys = ["deviceId", "ts", "rrMs", "seq"])
data class RrInterval(
    val deviceId: String, val ts: Long, val rrMs: Int, val seq: Int = 0,
    val synced: Int = 0, val ord: Int? = null, val srcChannel: Int? = null,
    val tsSuspect: Int? = null,
)

This is the highest-volume table — ~100 000 rows/day at 70 bpm, ~150 000 at 120 bpm.

spo2Sample (v3) — SpO₂ raw ADC, 1 row/second

try db.create(table: "spo2Sample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("red", .integer).notNull()
    t.column("ir", .integer).notNull()
    t.primaryKey(["deviceId", "ts"])
}
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

skinTempSample (v3) — Skin temp raw ADC, 1 row/second

try db.create(table: "skinTempSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("raw", .integer).notNull()
    t.primaryKey(["deviceId", "ts"])
}
// v31: added "aux1Raw" INTEGER, "aux2Raw" INTEGER (two auxiliary thermal channels, nullable)
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

respSample (v3) — Respiration raw ADC, 1 row/second

try db.create(table: "respSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("raw", .integer).notNull()
    t.primaryKey(["deviceId", "ts"])
}
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

gravitySample (v3) — Accelerometer x/y/z, 1 row/second

try db.create(table: "gravitySample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("x", .double).notNull()
    t.column("y", .double).notNull()
    t.column("z", .double).notNull()
    t.primaryKey(["deviceId", "ts"])
}
// v31: added "dynAccel" DOUBLE (strap's own gravity-removed motion magnitude, nullable)
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

stepSample (v10) — Step counter + activity class, 1 row/second

try db.create(table: "stepSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("counter", .integer).notNull()   // cumulative u16, wraps
    t.primaryKey(["deviceId", "ts"])
}
// v19: added "activityClass" INTEGER (0=still, 1=walk, 2=run, nullable)
// v5-style: added "synced" INTEGER NOT NULL DEFAULT 0

sleepStateSample (v21) — Band sleep state, 1 row/second

try db.create(table: "sleepStateSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("state", .integer).notNull()     // 0=wake, 1=still, 2=asleep, 3=up
    t.primaryKey(["deviceId", "ts"])
}
// v31: added "rawByte" INTEGER (the whole @81 flag byte, nullable)

ppgHrSample (v12) — PPG-derived HR, ~1 row/second during v26 optical windows

try db.create(table: "ppgHrSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("bpm", .double).notNull()        // float estimate
    t.column("conf", .double).notNull()       // 0–1 autocorrelation strength
    t.primaryKey(["deviceId", "ts"])
}

Only emitted during v26 optical records (typically sleep/SpO₂ windows, ~8 h/night = ~28 800 rows/day).

ppgWaveformSample (v27) — PPG waveform BLOB, ~1 row/second during v26

try db.create(table: "ppgWaveformSample") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("samples", .blob).notNull()      // packed i16 LE, 24 samples/row ≈50 bytes
    t.primaryKey(["deviceId", "ts"])
}
// v39: added "burstIndex" INTEGER (nullable)

The retention comment in the migration (lines 541–546) is the canonical statement that no durable per-second table is pruned:

// Retention: no pruning, matching every other durable per-second table (hrSample, spo2Sample, …
// are never pruned either) — this is decoded biometric history, not the transient raw outbox.
// `PrunePolicy`'s ~50 MB cap governs ONLY `rawBatch` (raw, pre-decode frames kept for re-decode /
// re-sync); it is untouched by and unrelated to this table.

event (v1) — Strap events, ~180 rows/day (battery every ~8 min)

try db.create(table: "event") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("kind", .text).notNull()
    t.column("payloadJSON", .text).notNull()
    t.primaryKey(["deviceId", "ts", "kind"])
}
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

battery (v1) — Battery soc/mv/charging, ~180 rows/day

try db.create(table: "battery") { t in
    t.column("deviceId", .text).notNull()
    t.column("ts", .integer).notNull()
    t.column("soc", .double)                  // state-of-charge percent, nullable
    t.column("mv", .integer)                  // millivolts, nullable
    t.primaryKey(["deviceId", "ts"])
}
// v6: added "charging" BOOLEAN (nullable)
// v5: added "synced" INTEGER NOT NULL DEFAULT 0

What IS already bounded (do not change)

rawBatch — 50 MB cap (iOS/macOS only)

// Strand/Collect/PrunePolicy.swift
enum PrunePolicy {
    static let keepWindowSeconds = 24 * 3600        // keep synced raw browsable ~24h
    static let maxUnsyncedBytes = 50 * 1024 * 1024  // total raw footprint cap; evict oldest beyond ~50MB
}

// Packages/WhoopStore/Sources/WhoopStore/RawOutbox.swift:210-245
public func pruneRaw(now: Int, keepWindowSeconds: Int, maxUnsyncedBytes: Int) async throws -> Int {
    try syncWrite { db in
        var pruned = 0
        // Policy 1: aged synced batches (DORMANT — no production caller marks syncedAt)
        let cutoff = now - keepWindowSeconds
        try db.execute(sql: "DELETE FROM rawBatch WHERE syncedAt IS NOT NULL AND syncedAt < ?", arguments: [cutoff])
        pruned += db.changesCount
        // Policy 2 (#27): size-based eviction of the OLDEST raw beyond the cap
        let rows = try Row.fetchAll(db, sql: "SELECT rowid, byteSize FROM rawBatch ORDER BY capturedAt DESC, rowid DESC")
        var cumulative = 0
        var evict: [Int64] = []
        for row in rows {
            let size: Int = row["byteSize"]
            cumulative += size
            if cumulative > maxUnsyncedBytes { evict.append(row["rowid"]) }
        }
        if !evict.isEmpty {
            let placeholders = evict.map { _ in "?" }.joined(separator: ",")
            try db.execute(sql: "DELETE FROM rawBatch WHERE rowid IN (\(placeholders))", arguments: StatementArguments(evict))
            pruned += db.changesCount
        }
        return pruned
    }
}

v18AuxSample — 604 800 rows rolling (~7 days)

// Packages/WhoopStore/Sources/WhoopStore/StreamStore.swift:61-83
public static let v18AuxRetentionRows = 604_800   // 7 × 86_400 strap-seconds
public static let v18AuxPruneEveryRows = 10_000   // sweep amortised

// StreamStore.swift:341-348 — the DELETE
DELETE FROM v18AuxSample WHERE deviceId = ? AND ts < (
    SELECT MIN(ts) FROM (
        SELECT ts FROM v18AuxSample WHERE deviceId = ? ORDER BY ts DESC LIMIT ?
    )
)

// android/.../data/WhoopRepository.kt:2140-2151
const val V18_AUX_RETENTION_ROWS = 604_800

rawImuSample — dropped in v41

// Database.swift:882-884 — migration v41 does DROP TABLE rawImuSample

Storage growth projections

All sizes are payload + PK index lower bounds; actual SQLite files run 1.3–1.5× higher (page slack, WAL, indexes). WHOOP 5.0/MG at 1 Hz = 86 400 rows/day per per-second stream; rrInterval is ~100 000/day at 70 bpm.

The user-reported 6.5 GB / 70 days matches the worst-case band (24 h v26 PPG or higher SQLite overhead). Dominant contributors: rrInterval, gravitySample, skinTempSample, spo2Sample, hrSample.

Existing read-time aggregation (no persisted aggregates today)

There are NO persisted downsampled tables. Aggregation is read-time only — GROUP BY queries over the full raw tables:

// Packages/WhoopStore/Sources/WhoopStore/Reads.swift — hrBuckets (read-time GROUP BY)
// Groups hrSample ∪ ppgHrSample by ts / bucketSeconds, returns mean bpm

// Reads.swift — hrWindowStats
// COUNT / AVG / MAX over a window of hrSample

The existing storageStats() returns logical row counts + rawBatch.byteSize only — no per-table byte estimates, no aggregate-tier visibility:

// Reads.swift:387-410
public func storageStats() async throws -> (decodedRows: Int, rawBatches: Int, rawBytes: Int) {
    let rawTables = ["hrSample", "rrInterval", "event", "battery",
                     "spo2Sample", "skinTempSample", "respSample", "gravitySample",
                     "stepSample", "ppgHrSample", "sleepStateSample", "ppgWaveformSample",
                     "v18AuxSample"]
    // ... SELECT COUNT(*) FROM each table, SUM(byteSize) FROM rawBatch
}

The existing small aggregate/cache tables (dailyMetric, sleepSession, metricSeries) are keyed by day/session and grow trivially (~365 rows/year for dailyMetric). They are the "forever" tier already — the gap is the hot/warm/cold tiers in between.

How every major wearable solves this

No major wearable keeps raw per-second sensor data on-device long-term.

• WHOOP — 14-day circular strap buffer of per-second 93-byte summary records; raw PPG/IMU is transient/live only. Cloud (Snowflake, >3 PB, ~20 TB/day) handles long-term analytics. Strain/Recovery/Sleep computed cloud-side from buffered summaries.
• Oura — 3–7 day ring buffer; scores computed on-phone (ecore engine); raw PPG/IBI not archived. Cloud retention is account-lifecycle bound.
• Garmin — circular FIT storage, overwritten when full. All-day HR stored as 2-minute averages in Connect, not per-second. The FIT protocol uses timestamp_16 (16-bit delta timestamps) for compression. Connect IQ recommends fixed-size buffer with dynamic downsampling (merge pairs into coarser min/max/avg when full).
• Apple HealthKit — condensing: workouts older than "a few months" are replaced with condensed series/cumulative samples, originals deleted. Consecutive identical HR values coalesced into continuous intervals. A 10-year export is 1.8 GB XML → 71 MB gzipped (25× compression on aggregated data).
• Bevel — interpretation layer on Apple Health; doesn't generate raw sensor data. Not applicable.

The industry pattern is universal: recent raw → older aggregates → daily summaries, with semantic compression (packing, delta timestamps, coalescing) mattering more than generic gzip.

Compression techniques applicable to SQLite/GRDB/Room

The feature — tiered storage (hot/warm/cold) as one shippable unit

Tier model

New aggregate tables to create (migrations v42+, byte-identical Swift + Room)

Minute tables (1 row per 60-second bucket, ts = bucket start unix seconds):

CREATE TABLE hrMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    bpm_min INTEGER NOT NULL, bpm_mean REAL NOT NULL, bpm_max INTEGER NOT NULL, rr_count INTEGER,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE rrIntervalMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    rmssd REAL, sdnn REAL, mean_nn REAL, count INTEGER NOT NULL,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE spo2Minute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    red_min INTEGER NOT NULL, red_mean REAL NOT NULL, red_max INTEGER NOT NULL,
    ir_min INTEGER NOT NULL, ir_mean REAL NOT NULL, ir_max INTEGER NOT NULL,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE skinTempMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    raw_min INTEGER NOT NULL, raw_mean REAL NOT NULL, raw_max INTEGER NOT NULL,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE respMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    raw_min INTEGER NOT NULL, raw_mean REAL NOT NULL, raw_max INTEGER NOT NULL,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE gravityMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    x_mean REAL NOT NULL, y_mean REAL NOT NULL, z_mean REAL NOT NULL, dyn_accel_mean REAL,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE stepMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    counter_start INTEGER NOT NULL, counter_end INTEGER NOT NULL, delta INTEGER NOT NULL,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE sleepStateMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    state_mode INTEGER NOT NULL, raw_byte_mode INTEGER,
    PRIMARY KEY (deviceId, ts));

CREATE TABLE ppgHrMinute (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    bpm_min REAL NOT NULL, bpm_mean REAL NOT NULL, bpm_max REAL NOT NULL, conf_mean REAL NOT NULL,
    PRIMARY KEY (deviceId, ts));

Hourly tables (1 row per 3600-second bucket, same columns as minute but ts = hour start):

CREATE TABLE hrHourly (deviceId TEXT NOT NULL, ts INTEGER NOT NULL,
    bpm_min INTEGER NOT NULL, bpm_mean REAL NOT NULL, bpm_max INTEGER NOT NULL, rr_count INTEGER,
    PRIMARY KEY (deviceId, ts));
-- ... same pattern for rrIntervalHourly, spo2Hourly, skinTempHourly, respHourly,
--     gravityHourly, stepHourly, sleepStateHourly, ppgHrHourly

Note: ppgWaveformSample has NO minute/hourly tier — the waveform has no aggregate meaning. It is dropped after the hot window.

Per-stream retention defaults (user-configurable)

What ships in this feature (complete, not phased)

1. Aggregate tables — all *Minute and *Hourly tables above, via versioned migrations (Swift GRDB + Android Room, byte-identical schema, column order matches entity field order).
2. Background rollup engine — a single engine that walks each hot-tier table, computes per-minute aggregates via GROUP BY ts / 60, inserts into minute tables (ON CONFLICT DO NOTHING — idempotent), and deletes the rolled-up raw rows in the same transaction. The same engine rolls minute → hourly when minute rows exceed the warm window, and deletes hourly rows past the cold window. Hooked into the existing Collector.prune() cadence.
3. ppgWaveformSample retention — dropped after the hot window (no aggregate meaning; the waveform is diagnostic-only).
4. Read-layer tier awareness — charts and analytics reads pick the right table by time range: raw for ≤ hot window, minute for warm range, hourly for cold range, dailyMetric for forever. No chart regression — the read layer abstracts the tier switch.
5. User-configurable retention settings (both platforms) — "Keep raw biometric data for [7/14/30] days"; "Maximum database size [1/2/5/10 GB]". When the cap is hit, prune oldest tier first (cold → warm → hot). Exposed in Settings → Storage.
6. Storage diagnostics — storageStats() enhanced to return per-table row counts and estimated bytes for every tier (hot/warm/cold/forever). Surfaced in a Settings → Storage breakdown screen.
7. First-run migration consent — on first launch after the migration, the user sees the new retention policy with an explicit choice: "Apply recommended retention (default)" vs "Keep everything (old behavior, at your own storage risk)". No data loss without consent.
8. Cold-archive export — "Export archive" action that writes a compressed .noopbak or zstd tarball of cold-tier data. The user can keep the file or push it to their own HTTPS endpoint (Scope question: Backup & one way Sync: self-hosted destination #1314 one-way export scope). The DB stays lean; the archive is user-owned.
9. Application-level compression — delta-of-delta timestamp encoding for ts in aggregate tables; Gorilla XOR for float columns (gravityMinute, skinTempMinute); coalescing for consecutive identical values (HR during sleep).

Projected impact

~95% reduction at the 5-year horizon while preserving all chart-rendering and scoring capability.

Constraints (from CLAUDE.md)

• Offline-by-design: all aggregation/retention is on-device; no server needed. NOOP has no server, no account, no cloud dependency, no telemetry.
• No raw egress: no data leaves the device; aggregation is local math. The cold-archive export is user-initiated and user-owned (Scope question: Backup & one way Sync: self-hosted destination #1314 scope — a default-off Experimental client may export data one way to an HTTP(S) endpoint the user owns).
• Cross-platform parity (Import v8.2.2 snapshot (upstream catch-up + board wave; ports PRs #1035/#1036 + BLE/readiness fixes) #1 rule): Android is an independent reimplementation of the same logic, not a port that shares code with Swift. Analytics and stored data must be byte-identical across Swift and Kotlin. If you change a decoder, an analytics formula, a migration, or a stored value on one platform, change the twin on the other in the same PR. Verify byte-identical by oracle, not by eye: extract the pure helper, compile the Swift twin standalone (swiftc -O twin.swift main.swift -o t && ./t) over the whole input space, paste that stdout verbatim as the expected literal in the Kotlin test.
• Room (Android) and GRDB (iOS) migrations must agree on the resulting schema. Column order in a Room CREATE TABLE must match the entity field order; pin migrations with tests.
• .noopbak backup whitelist is a byte-identical contract: BackupSettings.swift and BackupSettingsCodec must carry the same canonical keys + JSON kinds. New aggregate tables are NOT added to the backup whitelist in this feature — they are regenerable from raw during the hot window, and after that from the next-coarser tier. Adding them to .noopbak is a separate deliberate decision.
• Migration safety: add a versioned migration + a test; never mutate an existing migration. Watch for data-loss traps (window-wide deletes, backfill rewrites) — prefer additive/transactional changes. The rollup deletes decoded rows only AFTER aggregates are confirmed written in the same transaction (E2 invariant extended to the rollup path).
• No data loss without consent: retention defaults are conservative (7 d raw); the user is informed before any pruning runs on an upgraded install (first-run consent screen).
• Scoring window safety: the 7-day hot window must be ≥ the longest scoring window. Verified: StrandAnalytics HRV windows are ≤ 7 d (nightly sleep windows). If a future scoring window exceeds the default, the setting must clamp hotRetentionDays to at least that window.

Risks

• Rollup correctness — a bug in the aggregate computation silently corrupts historical data. Mitigation: transactional rollup (aggregate + delete in one transaction); idempotent inserts; oracle-tested aggregate math (Swift vs Kotlin byte-identical, same method as the existing cross-platform parity contract).
• Chart regression — charts that currently read raw tables must gracefully switch to aggregate tables for older ranges. Mitigation: read-layer abstraction that picks the right table by time range; test chart queries against all three tiers.
• First-run migration on large DBs — a 6.5 GB DB upgrading for the first time has 63 d of raw to rollup. Mitigation: batched rollup (1 d at a time, in a background task with progress); the migration itself only creates the empty aggregate tables, the rollup runs post-migration.
• Backup contract — adding aggregate tables to .noopbak changes the byte-identical contract. Mitigation: default NOT in backup; separate deliberate PR if wanted later.
• Setting clamp vs scoring window — if a user sets hotRetentionDays below a scoring window's needs, scoring silently degrades. Mitigation: clamp the setting to the max scoring window; surface a warning in Settings.

Acceptance (the whole feature)

A single PR (or a tightly-coupled stack merged together) is accepted when all of the following pass:

• Migrations — swift test in WhoopStoreTests: migration test for every new aggregate table (minute + hourly); Android testFullDebugUnitTest: mirror migration tests. Schema byte-identical per schema_oracle.json.
• Rollup correctness — RollupTests.swift + RollupTest.kt: insert raw rows → run rollup → assert aggregates correct (min/mean/max/count) AND raw rows deleted; idempotency (rollup twice = same result, no double-delete); transaction-rollback (aggregate insert fails → raw rows survive); minute→hourly rollup same shape; hourly→delete past cold window same shape. Aggregate values byte-identical across Swift and Kotlin (oracle test).
• Read-layer tier awareness — tests that a chart query for a ≤ 7 d range hits raw, 7–90 d hits minute, 90 d–1 y hits hourly, > 1 y hits dailyMetric. No chart returns empty for a range that has data in a coarser tier.
• Retention settings — test that changing hotRetentionDays / warmRetentionDays / coldRetentionDays / maxDatabaseBytes causes the rollup engine to prune the correct tiers; test that maxDatabaseBytes prunes oldest tier first (cold → warm → hot).
• Storage diagnostics — storageStats() returns per-table row counts + estimated bytes for all tiers; Settings → Storage screen renders the breakdown on both platforms.
• First-run consent — test that a fresh install of the new version over an old DB presents the consent screen and does NOT prune until the user chooses.
• Cold-archive export — test that "Export archive" produces a compressed file containing the cold-tier rows and that those rows are removed from the DB on success; test that a failed export leaves the DB untouched.
• Compression — test that delta-of-delta / Gorilla XOR / coalescing round-trip (encode → decode = original) for each column type they apply to.
• Manual — run the app for 8+ days on a WHOOP 5.0; verify Storage screen shows raw rows pruned after the hot window, minute aggregates populated, hourly aggregates populated after the warm window, charts render correctly from every tier, Settings → Storage reflects the breakdown, "Export archive" produces a valid file, and re-importing the archive restores the cold-tier rows.
• Cross-platform parity — the same 70-day synthetic dataset produces byte-identical aggregate tables on iOS and Android.

Files to touch

Swift (iOS/macOS):

• Packages/WhoopStore/Sources/WhoopStore/Database.swift — new migrations (v42+) for all *Minute and *Hourly tables
• Packages/WhoopStore/Sources/WhoopStore/StreamRollup.swift (new) — rollup engine: scan → aggregate → insert → delete in transaction; minute→hourly; hourly→delete past cold window
• Packages/WhoopStore/Sources/WhoopStore/StreamArchive.swift (new) — cold-archive export: select cold-tier rows → compress → write file / push to Scope question: Backup & one way Sync: self-hosted destination #1314 endpoint → delete on success
• Packages/WhoopStore/Sources/WhoopStore/Reads.swift — tier-aware chart reads; storageStats() enhancement (per-table bytes, all tiers)
• Packages/WhoopStore/Sources/WhoopStore/Compression.swift (new) — delta-of-delta, Gorilla XOR, coalescing codecs
• Strand/Collect/PrunePolicy.swift — add hotRetentionDays, warmRetentionDays, coldRetentionDays, maxDatabaseBytes (user-configurable)
• Strand/Collect/Collector.swift — prune() calls rollup before raw-batch prune
• Strand/Screens/SettingsView.swift — retention settings UI + first-run consent
• Strand/Screens/StorageView.swift (new) — per-tier storage breakdown screen
• Packages/WhoopStore/Tests/WhoopStoreTests/RollupTests.swift (new)
• Packages/WhoopStore/Tests/WhoopStoreTests/ArchiveTests.swift (new)
• Packages/WhoopStore/Tests/WhoopStoreTests/CompressionTests.swift (new)

Android (Kotlin):

• android/app/src/main/java/com/noop/data/WhoopDatabase.kt — new Room migrations
• android/app/src/main/java/com/noop/data/Entities.kt — new *Minute and *Hourly entities
• android/app/src/main/java/com/noop/data/WhoopDao.kt — rollup + archive queries
• android/app/src/main/java/com/noop/data/WhoopRepository.kt — rollup + archive orchestration
• android/app/src/main/java/com/noop/data/Compression.kt (new) — parity codecs (delta-of-delta, Gorilla XOR, coalescing)
• android/app/src/main/java/com/noop/ui/SettingsScreen.kt — retention settings UI + first-run consent
• android/app/src/main/java/com/noop/ui/StorageScreen.kt (new) — per-tier storage breakdown
• android/app/src/test/java/com/noop/data/RollupTest.kt (new)
• android/app/src/test/java/com/noop/data/ArchiveTest.kt (new)
• android/app/src/test/java/com/noop/data/CompressionTest.kt (new)

Out of scope (explicitly)

• sqlite-zstd / native SQLite compression extensions — breaks GRDB/Room parity and the .noopbak byte-identical contract. Revisit as a separate issue after this feature lands.
• Cloud sync of aggregates — violates offline-by-design. Hard no.
• Adding aggregate tables to .noopbak — separate deliberate decision; default NOT in backup for this feature.
• Changing the 1 Hz decoded stream schema — additive only (new aggregate tables); no mutation of existing tables or migrations.

Verification status

This issue is a research/PRD deliverable, not code. No source was written or compiled. All table schemas, retention policies, and growth estimates are grounded in source files read during the research session (inlined above). Implementation requires Xcode (macOS) for iOS and Gradle for Android.