Repository Public License
1<!--2ORIX Monitor3File: docs/architecture/storage.md4Author: Simon-Pierre Boucher5Contact: contact@spboucher.ai6-->78# Storage910Source: `Packages/ORIXMonitorCore/Sources/ORIXMonitorCore/Storage/TimeSeriesStore.swift`.1112`TimeSeriesStore` is an actor around one SQLite database. It keeps numeric history in three retention tiers, answers downsampled range queries, reports gaps, and holds an event log (alerts, bookmarks, system and network events, process spikes).1314## Location1516| Case | Path |17|---|---|18| Normal run | `~/Library/Application Support/ORIX Monitor/telemetry.sqlite` (resolved with `FileManager.urls(for: .applicationSupportDirectory, in: .userDomainMask)`) |19| `-ORIXDataDir <dir>` | `<dir>/telemetry.sqlite` |20| Tests, benchmarks | `TimeSeriesStore(url: nil)` opens a private `:memory:` database |2122The directory is created if missing. With WAL mode SQLite also creates `telemetry.sqlite-wal` and `telemetry.sqlite-shm`. `.gitignore` excludes all three patterns.2324If the database cannot be opened, `MonitorModel` keeps running without a store (`storeError` is shown in Settings › History).2526## Connection settings2728Applied at open (`sqlite3_open_v2` with `SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX`):2930```sql31PRAGMA journal_mode=WAL;32PRAGMA synchronous=NORMAL;33PRAGMA auto_vacuum=INCREMENTAL;34```3536## Schema3738```sql39CREATE TABLE IF NOT EXISTS raw(40 metric INTEGER NOT NULL, ts INTEGER NOT NULL, v REAL NOT NULL,41 PRIMARY KEY(metric, ts)) WITHOUT ROWID;4243CREATE TABLE IF NOT EXISTS minute(44 metric INTEGER NOT NULL, ts INTEGER NOT NULL,45 mn REAL, mx REAL, av REAL, n INTEGER,46 PRIMARY KEY(metric, ts)) WITHOUT ROWID;4748CREATE TABLE IF NOT EXISTS hour(49 metric INTEGER NOT NULL, ts INTEGER NOT NULL,50 mn REAL, mx REAL, av REAL, n INTEGER,51 PRIMARY KEY(metric, ts)) WITHOUT ROWID;5253CREATE TABLE IF NOT EXISTS events(54 id INTEGER PRIMARY KEY AUTOINCREMENT, ts REAL NOT NULL, kind TEXT NOT NULL,55 severity INTEGER NOT NULL, metric INTEGER, code TEXT NOT NULL, payload TEXT NOT NULL);5657CREATE INDEX IF NOT EXISTS events_ts ON events(ts);58```5960| Column | Meaning |61|---|---|62| `metric` | `MetricID.rawValue` (stable integer key: 1 = `cpu.total`, 10 = `memory.used`, …, 90 = `self.cpu`, 91 = `self.memory`) |63| `raw.ts` | Unix time in **milliseconds** (rounded) |64| `minute.ts`, `hour.ts` | Unix time in **seconds** of the start of the bucket (floored to 60 s / 3600 s) |65| `v` | The value in the metric's stored unit |66| `mn`, `mx`, `av`, `n` | Minimum, maximum, arithmetic mean and number of raw samples in the bucket |67| `events.ts` | Unix time in seconds (floating point) |68| `events.kind` | `EventKind`: `alert`, `finding`, `bookmark`, `system`, `network`, `process` |69| `events.severity` | `Severity`: 0 info, 1 warning, 2 critical |70| `events.payload` | JSON object of string → string |7172There is no schema-version table and no migration code: the schema is the first and only version.7374## Retention tiers7576`RetentionPolicy` (seconds each tier is kept):7778| Tier (`StoreTier`) | Content | Default retention | Settings choices |79|---|---|---|---|80| `raw` | Every sample as produced by the scheduler | 6 hours | 1 h, 6 h, 24 h, 3 days |81| `minute` | One row per metric per minute: min, avg, max, count | 14 days | 7, 14, 30, 90 days |82| `hour` | One row per metric per hour: min, avg, max, count | 730 days (2 years) | 90 days, 1 year, 2 years, 5 years |8384Events are pruned with the **hour** retention.8586## Write path8788```mermaid89flowchart TD90 A["append(values, date)"] --> F{"value is finite?"}91 F -- "no" --> X["dropped"]92 F -- "yes" --> P["pendingRaw buffer"]93 F -- "yes" --> M["roll minute bucket"]94 F -- "yes" --> H["roll hour bucket"]95 M --> C["closedBuckets when the bucket start changes"]96 H --> C97 P --> FL["flush()"]98 C --> FL99 M --> FL100 H --> FL101 FL --> T["one BEGIN IMMEDIATE transaction"]102 T --> R["INSERT OR REPLACE raw"]103 T --> AG["INSERT OR REPLACE minute and hour"]104 T --> PR{"more than 1 h since last prune?"}105 PR -- "yes" --> PN["prune() and incremental_vacuum"]106```107108- **`append(_:at:)`** touches no disk. Each finite value is added to `pendingRaw` and to the running `Bucket` (start, min, max, sum, count) of its metric in both the minute and the hour tier. NaN and infinite values are dropped (`nonFiniteValuesAreDropped`).109- **`roll`** computes the bucket start as `floor(time / step) × step`. If the metric's current bucket has a different start, it is moved to `closedBuckets` and a new one begins.110- **Relaunch inside a bucket.** When there is no in-memory bucket for a metric (first value after launch), `loadBucket` reads the row already stored for that bucket start and continues it, so a relaunch within the same minute or hour merges with the earlier partial aggregate instead of overwriting it (`historySurvivesRelaunchAndPartialBucketsMerge`).111- **`flush(now:)`** writes, in one transaction, all pending raw rows, all closed buckets, **and the current open buckets** (`INSERT OR REPLACE`, so an open bucket is rewritten with its latest aggregate at each flush). The stored average is `sum / count`.112- The engine calls `flush` every `flushInterval` (30 s) and on `stop()`. `series(...)` and `statistics()` also flush first, so a query never misses buffered samples.113114### Durability115116At most one flush period of raw samples (30 s by default) is held in memory. A normal quit flushes through `AppDelegate.applicationShouldTerminate` → `MonitorModel.shutdown()` → `TelemetryEngine.stop()`. A crash or `kill` loses the unflushed samples; `Scripts/bench.sh` notes this explicitly.117118## Pruning119120`prune(now:)` runs at most once an hour (checked at the end of `flush`), and immediately after `setRetention(_:)` resets the timer:121122```sql123DELETE FROM raw WHERE ts < (now − retention.raw) × 1000;124DELETE FROM minute WHERE ts < now − retention.minute;125DELETE FROM hour WHERE ts < now − retention.hour;126DELETE FROM events WHERE ts < now − retention.hour;127PRAGMA incremental_vacuum;128```129130`eraseAll()` clears the in-memory buffers, deletes every row of the four tables and runs `VACUUM`. It is reached from Settings › History › "Erase All History…" behind a confirmation dialog.131132## Read path and downsampling133134`series(_:from:to:maxPoints:tier:now:)` returns a `Series { metric, tier, step, points, gaps }` where each `SeriesPoint` is `{ time, min, max, avg, count }` and `time` is the start of the bucket.135136### Tier selection137138`SeriesMath.tier(from:to:now:retention:)`, unless the caller forces a tier:139140| Condition | Tier |141|---|---|142| span ≤ 2 hours **and** `from` is within raw retention | `raw` |143| span ≤ 7 days **and** `from` is within minute retention | `minute` |144| otherwise | `hour` |145146So a one-hour range from two days ago is answered from the minute tier (raw data that old no longer exists).147148### Bucket width149150`SeriesMath.step(span:tier:maxPoints:rawInterval:)`:151152```153native = (tier == raw) ? max(rawInterval, 1) : tier.step // 60 or 3600154wanted = span / maxPoints155step = wanted > native ? ceil(wanted / native) × native : native156```157158The step is never finer than the tier and always a whole multiple of the tier's native resolution. `rawInterval` is kept in sync with the configured sampling interval by `TelemetryEngine.update(_:)`. Raw buckets are never narrower than one second.159160Examples (from `stepNeverFinerThanTier`): 1 h on the minute tier at 600 points → 60 s; 24 h on the minute tier at 600 points → 180 s; 2 h raw at a 2 s interval and 600 points → 12 s.161162### Aggregation163164Raw tier:165166```sql167SELECT (ts / :stepMs) * :stepMs, MIN(v), MAX(v), AVG(v), COUNT(*)168FROM raw WHERE metric=? AND ts>=? AND ts<? GROUP BY 1 ORDER BY 1;169```170171Minute and hour tiers:172173```sql174SELECT (ts / :step) * :step, MIN(mn), MAX(mx), SUM(av * n) / SUM(n), SUM(n)175FROM <tier> WHERE metric=? AND ts>=? AND ts<? GROUP BY 1 ORDER BY 1;176```177178Properties that follow, and that `downsamplingPreservesExtremesAndWeightedMean` checks:179180- **Extremes survive.** The min of mins and the max of maxes are exact, so a one-sample spike is still visible at any zoom level.181- **Means are sample-weighted.** A bucket with 30 samples weighs 30 times a bucket with one. `Series.average` applies the same weighting across returned points.182- **Sample counts are preserved**, which lets reports state how many measurements a figure rests on.183184Callers and their point budgets: History page 480, reports 360, findings 2000, default 600.185186## Gap detection187188`SeriesMath.gaps(points:from:to:step:tolerance:)` with `tolerance = 2.5`:189190- No point at all → one gap covering the whole range.191- `first.time − from > 2.5 × step` → a leading gap `[from, first.time]`.192- For consecutive points with `b.time − a.time > 2.5 × step` → a gap `[a.time + step, b.time]`.193- `to − (last.time + step) > 2.5 × step` → a trailing gap. The store passes `min(to, now)` so the future part of a range is not reported as missing.194195Gaps are **reported, never filled**. Their consumers:196197| Consumer | Behaviour |198|---|---|199| `HistoryChart` | Shades each gap with a `RectangleMark`; splits the line and the min–max band into segments at holes wider than 2.5 steps |200| History page statistics | "Gaps: n · total duration" |201| `PDFReportRenderer.chart` | Shades gaps and breaks the line with the same 2.5-step rule |202| Markdown / PDF reports | "Data gaps" section: count, total missing, longest, per metric |203| JSON / CSV | JSON includes `gaps` per series; CSV simply has no rows for missing buckets |204| `Detectors.sustained` | Ends an episode when consecutive points are more than 3 steps apart |205206A gap means "no sample was stored": the app was not running, the Mac was asleep, sampling was paused, history recording was off, or the metric could not be measured (for example a rate just after a discontinuity).207208## Events209210| Method | Purpose |211|---|---|212| `record(_:)` | Inserts a `StoredEvent`; returns the row id |213| `events(kinds:from:to:limit:)` | Newest first, filtered by kind and time, default limit 500 |214| `deleteEvent(id:)` | Removes one event (bookmark deletion) |215| `deleteEvents(kinds:)` | Removes all events of the given kinds (alert history "Clear History") |216217Event codes written by the app are listed in [telemetry](telemetry.md). The `finding` kind exists in `EventKind` but nothing writes events of that kind: findings are recomputed from history on demand and are not persisted.218219## Statistics220221`statistics()` returns `StoreStatistics { rawRows, minuteRows, hourRows, events, fileBytes, earliest }`. `fileBytes` is the size of the database file plus its `-wal` file; `earliest` is the oldest hour bucket. Shown in Settings › History and used for the History page subtitle ("Collected locally since …").222223## Limitations224225- **No encryption.** The database is a plain SQLite file protected by the user account's file permissions. It contains numeric series and event payloads (process names in `process.spike`, interface names in network events, bookmark notes); it contains no IP addresses and no per-process history.226- **SQL errors are not surfaced.** `exec`, `run` and `prepare` ignore failures after open; a failed write is silent.227- **Timestamps are wall-clock.** A manual clock change moves where subsequent rows land. Rates are unaffected (they use the monotonic clock), but a backwards clock jump can overwrite rows with identical `(metric, ts)` keys.228- **No migration path** exists yet for future schema changes.229- **Per-process data is not stored**, only the process count and `process.spike` events.230- **Minute and hour buckets are aligned to Unix time**, not to the local time zone; hour buckets therefore start on UTC hour boundaries.231