Olly Docs

Appearance

Updated Aug 22, 2026

Wearable streams: model & ingest ​

Schema deep-dive · living document · #21 in the reading sequence

Tablesstreams.event_raw[1], streams.sync_state[2], streams.connection[3], streams.deletion_log[4]
Owner servicebalance (sole writer) - Python / FastAPI / asyncpg, no ORM (DDL is raw SQL in Alembic)
Locatornatural keys, no XXX-YYYY-NNNNNN: event_raw = (profile_id, source_platform, source_id); sync_state/connection = (profile_id, vendor); deletion_log = profile_id. The owning profile carries the PTY- party locator
Last updated2026-08-28
Companionopens the Phase-3 wearable-streams section · next: Streams registries · consumers: triage's Phase-2 streams sidecar (POST /streams/digest) and the member-app activity/timeline

1. Scope and usage ​

This page opens the wearable-streams section, so it frames the whole pipeline before covering its own tables. balance is the wearable-streams service: it takes raw wearable samples and turns them into the movement, sleep and recovery surfaces the member app and triage read. The pipeline is a one-way fold from many small readings to a few durable facts:

wearable sample -> event_raw -> (compaction) -> episode -> rollup -> baseline
                                             -> movement_score / shape_score
                                             -> finding / insight / knowledge_gap

Everything downstream reads episodes, never raw. event_raw is the archive at the head of that pipeline: the verbatim sample store. The other three tables on this page are its supporting cast - sync_state (the per-source push cursor), connection (a wearable-OAuth store) and deletion_log (the erasure tombstone).

How a sample lands is narrower than "the wearable ingest". There are three surfaces that look like wearable intake, and only one of them writes event_raw. The device-push endpoint POST /ingest/wearable/{vendor}/push is the sole writer[5] - the RN app calls it after the platform SDK (Health Connect on Android, HealthKit on iOS) hands it a batch. The demo file-import path (POST /wearable/load-sample) writes streams.episode directly through the Apple-Health connector and the Gate, skipping event_raw entirely[6]. The synthetic generator (streams/generator.py, read through DuckDB by streams/loader.py) produces persona Parquet files, not database rows. So event_raw's 157,070 live rows are entirely device-push; the other two lanes never touch it. That is worth stating up front, because "I loaded a sample, why is my event_raw empty" is the predictable confusion.

A single event_raw row is one metric reading over one time window for one profile from one source: (type, unit, value_num|value_text, start_at, end_at). A steps row is a step count over a ~10-minute window; an exercise_session row is active-minutes over the workout; a sleep_session row is sleep-minutes over the night. The row also carries compacted_at - NULL means "not yet folded into an episode", and that NULL is the entire compaction work queue (§5).

2. Boundaries and relationships ​

An ingest row is not…That concern lives inJoin
the episodestreams.episode - the compaction worker reads pending event_raw and derives episodes from it[7]; nothing downstream reads rawprofile_id; compaction stamps compacted_at
the rollup / baseline / scorelater streams tables (rollup, baseline, shape_score) built by recompute_profile from episode observations[8]via episode, not raw
a real vendor change-tokensync_state.cursor carries a synthetic monotonic string the server mints so the FE has a token to round-trip; the column takes a vendor change token unchanged when one arrives[9](profile_id, vendor)
a cloud-pull OAuth integrationconnection already models the vendor link and its revoke path; it activates when a connect flow ships, since today's ingest is device-push + file-import (§9)(profile_id, vendor)
the profilestreams.profile - soft-deleted in the same transaction that writes deletion_log[10]deletion_log.profile_id (no FK)
the consent recordconsent-service (Go) - balance does not own a consent store; the push gate is a stub that returns True[11]none

profile_id is the tenancy key throughout. Every table here is ON DELETE CASCADE from streams.profile(id) except deletion_log, which deliberately has no FK so the tombstone outlives the row it records[4]. The profile resolves from the caller's verified Keycloak JWT (party_locator claim), never from a request parameter[12].

3. Structure ​

DDL[1][2][3][4] · the wire shape (Pydantic, not a table) is WearablePushEnvelope[13]

event_raw ​

FieldTypeReqNotes
iduuid✓PK, gen_random_uuid()
profile_iduuid✓FK → streams.profile ON DELETE CASCADE; tenancy key
source_platformtext✓android | ios
source_sdktext✓health-connect | healthkit
source_apptextenvelope-level app that invoked the SDK (com.hiolly.olly)
source_devicetextenvelope-level device string (iOS 26.6)
source_idtext✓HC/HK stable record id - the idempotency anchor
typetext✓canonical (post-alias) metric name: steps, heart_rate, sleep_session, …
unittext✓count, bpm, minutes, m, …
value_numdouble precisionnumeric reading; NULL for enum-typed metrics
value_texttextenum reading (sleep stages); booleans coerced to text (§field-by-field)
start_attimestamptz✓window start - the reading's clock
end_attimestamptzwindow end (NULL for instantaneous)
origin_apptextsource app inside the platform (Samsung Health, com.hevy); drives dedup + attribution
detailjsonbworkout exercise_type, sleep stage list, …
collected_attimestamptz✓envelope-level: when the FE gathered the batch
compacted_attimestamptzNULL = pending compaction - the work-queue signal
created_attimestamptz✓server insert time, now()
source_measured_device_modeltextphysical device (added by 0016, #1515) - 100% NULL live (§9)
source_measured_device_typetextwatch|phone|ring|… free-text, no CHECK[14]
source_measured_device_manufacturertextphysical device manufacturer

Indexes: event_raw_pending_idx btree (compacted_at) WHERE compacted_at IS NULL (the compaction queue), event_raw_profile_type_start_idx (profile_id, type, start_at) (read path), and UNIQUE (profile_id, source_platform, source_id) (idempotency).

sync_state ​

FieldTypeReqNotes
profile_iduuid✓PK part 1; FK → streams.profile ON DELETE CASCADE
vendortext✓PK part 2: health-connect | healthkit
cursortextsynthetic monotonic cursor {profile}:{vendor}:{max_start}, not a real HC change token
last_pushed_attimestamptzbumped on every push
last_full_window_attimestamptzset only when the FE sends a full-window sync (cursor.in is null)
FieldTypeReqNotes
iduuid✓PK, gen_random_uuid()
profile_iduuid✓FK → streams.profile ON DELETE CASCADE
vendortext✓modelled for oura/apple/amazfit (cloud-pull vendors)
access_tokentext"encrypted at rest in v1.1; plaintext OK in v1" per the migration
refresh_tokentextOAuth refresh token
expires_attimestamptztoken expiry
scopestext[]✓default '{}'
created_attimestamptz✓now()
revoked_attimestamptzset by DELETE /profile - the revoke leg already ships (§field-by-field)

Live: 0 rows, since the create leg arrives with the connect flow (§6, §9).

Constraint: UNIQUE (profile_id, vendor).

deletion_log ​

FieldTypeReqNotes
profile_iduuid✓PK, no FK - outlives the profile it records
deleted_attimestamptz✓soft-delete time, now()
hard_purge_attimestamptz✓deleted_at + 30 days - the grace deadline
purged_attimestamptzset by the sweep when the profile is hard-deleted

Field-by-field: what and why ​

source_id + UNIQUE (profile_id, source_platform, source_id) - the idempotency anchor. source_id is Health Connect / HealthKit's own stable record id, so the FE can retry a whole batch and the insert is ON CONFLICT (profile_id, source_platform, source_id) DO NOTHING[15]: a duplicate returns accepted:0, duplicates:N instead of a second row. The FE never has to reason about what it already sent - retry the batch, get the duplicates back as a count.

type / unit / the value_num ⨯ value_text split - type is the canonical metric name after alias resolution[16]. The value is split by kind: numeric readings go in value_num, enum-typed metrics (sleep stages) in value_text. _coerce_value also routes Python booleans to value_text - because bool is a subclass of int, storing them numeric would put 1.0/0.0 in a column meant for counts, so the DDL's numeric column is kept honest[17]. Live, all 157,070 rows are numeric - value_text has never been used (§9).

compacted_at - the NULL-is-pending contract. A fresh push writes compacted_at = NULL. The partial index event_raw_pending_idx ... WHERE compacted_at IS NULL makes "find the pending rows" an index-only scan, and that is exactly what the compaction worker's fetch does (WHERE compacted_at IS NULL AND type = ANY(_HANDLED_TYPES))[18]. After the worker handles a row - whether it produced an episode or deliberately skipped it - it stamps compacted_at = now() in the same commit[19]. The skip-stamp is the fix for the #1511 stall: before it, an unmappable type kept compacted_at NULL forever and the worker re-loaded it every batch.

origin_app - the app inside the platform that produced the reading (com.apple.health.…, com.hevy, Samsung Health), distinct from source_app (what invoked the SDK). It carries real weight downstream: the compaction worker uses it for origin-priority dedup (Health Connect can hold the same step count from HC-native, Google Fit and a third-party app at once), keeping only the winning source per (day, type) group[20], and the movement-history read resolves device attribution through it[21].

sync_state.cursor is synthetic. The server does not hold a Health Connect change token, so after each push it mints f"{profile_id}:{vendor}:{max_start.isoformat()}" from the newest accepted sample and upserts it; if nothing was accepted it keeps the incoming cursor so a dup-only retry does not reset FE state[9]. last_full_window_at is stamped only when the FE declares a full-window sync (its inbound cursor is null). The cursor is a round-trip token for the FE, not a server-side resume point.

connection's teardown legs already ship; the create leg lands with the connect flow. DELETE /profile issues UPDATE streams.connection SET revoked_at = now() WHERE profile_id = %s[22], and the hard-purge sweep lists the table among those it DELETEs[23]. So erasure and revocation are handled in advance of the OAuth cloud-pull connect flow that will INSERT the row: the shipped ingest arrives by device-push and file-import, and the connect flow is a SOURCE_REGISTRY entry plus one INSERT away (§9).

deletion_log has no FK on purpose. The tombstone must survive the profile's own deletion, so profile_id is a bare PK, not a reference[4]. DELETE /profile writes it (hard_purge_at = now() + 30 days) as step 4 of a single transaction that soft-deletes episodes, medical records and the profile itself[10]; the nightly sweep reads it and stamps purged_at once the profile is physically gone[24].

4. Invariants ​

InvariantEnforced by
One raw row per (profile, platform, record-id)DB UNIQUE (profile_id, source_platform, source_id)[1] + application ON CONFLICT DO NOTHING[15]
Raw rows belong to a real profileDB FK event_raw_profile_id_fkey ... ON DELETE CASCADE (in-schema)
A pending row is eventually compacted or skip-stampedApplication: run_compaction stamps every fetched row, mapped or not[19] - the #1511 stall guard
Booleans never land in value_numApplication: _coerce_value routes bool to value_text[17]; no DB CHECK
Batch size ≤ 50,000; SDK matches vendor; no future / pre-epoch timestampsApplication: handler guards (413 / 400 / per-record rejection)[25]
Push requires WEARABLE_SYNC consentStub - check_wearable_sync_consent returns True unconditionally; the real consent-service call is a documented TODO[11]
One sync cursor per (profile, vendor)DB PK (profile_id, vendor)[2]; upserted ON CONFLICT[9]
A deleted profile is hard-purged after 30 daysApplication: hard_purge_at = deleted_at + 30d[10]; nightly sweep enforces[24]; nothing in the DB forbids an early purge
deletion_log survives its profileDB: no FK on profile_id (deliberate)[4]
One vendor link per (profile, vendor)DB UNIQUE (profile_id, vendor)[3]; the INSERT that exercises it arrives with the connect flow (§3, §9)
Row changes captured to CDCDebezium publication dbz_balance covers all four tables (live \d)

5. Lifecycle ​

A raw row has no status column, but compacted_at is a two-state machine that governs whether the pipeline has consumed it:

Every transition is a real method. The insert happens in post_wearable_push, one INSERT ... ON CONFLICT DO NOTHING per record[15]. The compaction transition is run_compaction: it selects pending rows of a handled type, groups them (per-day movement/vitals/body aggregators; session-shaped sleep/exercise handlers), inserts the derived episode marked /* d50 compaction */[26], and stamps compacted_at on both the materialised and the skip-stamped rows[19].

What triggers compaction. Two paths, both landing in run_compaction:

  1. On-ingest, debounced (Temporal since 2026-08-27). A push that accepted ≥1 new row calls dispatch_ingest_compaction, which signal-with-starts the Temporal CompactionWorkflow when balance.compaction.via_temporal is on and the server is reachable, and otherwise falls back to the in-process schedule_on_ingest[28][38]. Either way the pass is coalesced per profile so a chunked sync fires one compact+recompute ~15s after the last chunk[27]; on the Temporal path that debounce is a durable workflow timer run by a separate worker process, so an API reload cannot silently un-arm it (the failure that stranded syncs in August, compaction page §5). A dup-only retry still triggers nothing. Wiring a trigger at all was the 2026-08-13 fix - before it the push wrote raw and triggered nothing, stranding samples as permanently-pending.
  2. Nightly. The 04:00 _daily_pass recomputes every active profile, and its "active" set explicitly UNIONs profiles that have pending event_raw (not just those with a recent episode), so a raw-only profile is still caught up[29]. This pass is still APScheduler. recompute_profile runs run_compaction as step 0, before rollup, and loops it until the backlog drains (one call handles 500 rows)[8].

There is also an on-demand POST /admin/compaction/run (any authenticated profile compacts its own pending rows - no admin RBAC yet)[30].

sync_state is upserted on the same push transaction as the raw rows. connection's revoke and purge legs run today (both on profile deletion); its create leg switches on with the connect flow. deletion_log runs a separate soft-delete → 30-day-grace → hard-purge machine: DELETE /profile writes the row; the nightly sweep (hard_purge.py, scheduled 04:30 by _nightly_purge[31]) deletes the profile's rows across every streams table once hard_purge_at has passed and stamps purged_at[24].

6. Populated examples, walked ​

Live rows from balance on dev-2, 2026-08-21. Wearable values are shown as-is (steps, minutes are not PII); profile uuids are real.

An event_raw row - one steps reading ​

json
{
  "id": "4d5464bb-8970-4c3a-b2f6-8f6501c87e7a",
  "profile_id": "e8a1f488-d322-4e8d-a2a2-6d230714916f",
  "source_platform": "ios", "source_sdk": "healthkit",
  "source_app": "com.hiolly.olly", "source_device": "iOS 26.6",
  "source_id": "hk_steps_7ABD98CA-FB5A-4B09-87BB-3ACAA7ED251C",
  "type": "steps", "unit": "count",
  "value_num": 73, "value_text": null,
  "start_at": "2026-08-21T13:30:38.889Z",
  "end_at":   "2026-08-21T13:40:13.488Z",
  "origin_app": "com.apple.health.0BE8733D-02A7-4A72-A01D-0B54AE705638",
  "detail": null,
  "collected_at": "2026-08-21T16:19:04.649Z",
  "compacted_at": "2026-08-21T16:19:22.431Z",
  "created_at":   "2026-08-21T16:19:06.463Z",
  "source_measured_device_model": null
}
KeyRead byWhat actually happens
source_id + (profile, platform, source_id)the push INSERT's ON CONFLICT[15]re-pushing this HealthKit record is a no-op; the FE can retry freely
type: steps, value_num: 73, [start_at, end_at]compaction's per-day movement aggregator[7]73 steps over a ~10-minute window folds into that day's everyday_movement episode
origin_app: com.apple.health.…origin-priority + attribution[20]on iOS/HealthKit there is one source, so this row wins its group
compacted_at set (16:19:22, ~16s after created_at)the pending index[18]already folded - the debounced on-ingest pass ran; it will not be revisited
source_measured_device_model: null/wearable/status device list[32]this push carried no device block - true of all 157,070 rows (§9)

A second real row shows the session shape and the detail payload - 857bea13-…: type = exercise_session, value_num = 55 (active minutes), start_at 2026-08-18 17:59 → end_at 18:54, origin_app = com.hevy, detail = {"exercise_type": "strength_training"}. Compaction routes it to a strength_training movement episode because that exercise_type is a known movement type[7].

The sync_state row for that profile ​

json
{
  "profile_id": "e8a1f488-d322-4e8d-a2a2-6d230714916f",
  "vendor": "healthkit",
  "cursor": "e8a1f488-d322-4e8d-a2a2-6d230714916f:healthkit:2026-08-21T13:30:38.889000+00:00",
  "last_pushed_at": "2026-08-21T16:19:06.463Z",
  "last_full_window_at": null
}

Same profile as the steps row, and the cursor's timestamp (…13:30:38.889) is exactly that row's start_at - the newest accepted sample in the batch[9]. last_full_window_at is null, so this was an incremental push (the FE sent a non-null inbound cursor). Live there are 12 sync_state rows - 7 healthkit, 5 health-connect, matching the 9 distinct profiles that have ever pushed.

The deletion_log row (right-to-erasure tombstone) ​

json
{
  "profile_id": "359a8689-43e1-420f-937c-56a50af58998",
  "deleted_at":    "2026-06-19T10:40:40.632Z",
  "hard_purge_at": "2026-07-19T10:40:40.632Z",
  "purged_at":     "2026-07-29T10:40:41.267Z"
}

deleted_at + 30d = hard_purge_at, and purged_at (2026-07-29) is when the first nightly sweep after the deadline ran[24]. Live population: 42 rows, all 42 purged, deleted across seven days between 2026-04-22 and 2026-06-19 (test-teardown batches). There are no in-grace deletions right now.

connection - no live rows, modelled ahead of a connect flow ​

connection holds 0 live rows as of 2026-08-21: the connect flow that creates them is a later stage than the device-push ingest that ships. The row below is constructed from the DDL[3] to show the shape an OAuth cloud-pull connect flow writes - it is not a live row:

json
{
  "id": "gen_random_uuid()",
  "profile_id": "<profile>",
  "vendor": "oura",
  "access_token": "<oauth access token>",
  "refresh_token": "<oauth refresh token>",
  "expires_at": "<token expiry>",
  "scopes": ["daily", "heartrate", "workout"],
  "created_at": "now()",
  "revoked_at": null
}

The statements that name connection today are the revoke UPDATE in DELETE /profile[22] and the DELETE in the hard-purge sweep[23] - the erasure obligations, in place before the data they cover exists. The wearable data that ships today arrives by device-push (event_raw) and demo file-import (episodes); a vendor-OAuth connection is established when the connect flow that links real devices lands, and §9 names the two call sites that create it.

7. Who references these tables ​

WhereColumn / mechanismMeaning there
compaction workerevent_raw pending scan → streams.episode[7]the fold from raw samples to episodes - the sole consumer of raw
nightly _daily_passevent_raw WHERE compacted_at IS NULL[29]adds raw-only profiles to the active set so they never strand
GET /wearable/statusevent_raw counts + sync_state[32]"you've synced N records", per-type + per-device + pending-compaction telemetry
movement_history readevent_raw.origin_app DISTINCT[21]device attribution for the member timeline's movement lane
backfill_wearable_episodes.pyreads event_raw, writes episodes[33]disaster tool - rebuilds episodes from the raw archive when an import stranded
DELETE /profilewrites deletion_log, revokes connection[10]right-to-erasure entry point
hard_purge.pyreads deletion_log, deletes connection + all rows[24]the 30-day sweep
Debezium dbz_balancelogical replication of all four tables (live \d)CDC → analytics; raw samples are on the wire even before compaction

None are cross-service FKs; event_raw / sync_state / connection are in-schema FK children of streams.profile, and deletion_log is a bare profile_id tombstone.

8. Design determinations ​

  1. Device-push, not server-side polling. The RN app pushes HC/HealthKit batches to POST /ingest/wearable/{vendor}/push; balance never calls a vendor cloud. D-50 (#1491, #1493). This is why event_raw is the raw store, and why connection is modelled ahead of the cloud-pull connect flow that populates it - the vendor-link, revoke and purge legs are in place before the first product that needs them.
  2. FE idempotency is the schema's job. UNIQUE (profile_id, source_platform, source_id) + ON CONFLICT DO NOTHING lets the FE retry a whole batch and get accepted:0, duplicates:N back (#1492)[15].
  3. compacted_at separates archive from derivation. NULL = pending, a partial index is the work queue, and a skip-stamp guarantees progress even for unmapped types (#1494, #1511)[19].
  4. Raw is a durable archive, kept past compaction. Nothing deletes a compacted row except hard-purge; backfill_wearable_episodes.py exists precisely because raw outlives episodes and can rebuild them[33].
  5. The alias table was the wrong fix and was reversed. 0013 shipped a metric_alias trampoline (activity_steps → steps); 0014 flipped the taxonomy server-side and dropped the alias rows, because downstream rollups/detectors still looked for the legacy ids (#1510)[34]. The resolver survives as a live no-op (§9).
  6. Batch cap raised 1,000 → 50,000. Real-device first-syncs returned tens of thousands of records; the cap and nginx client_max_body_size were bumped together (2026-07-19)[25].
  7. Device metadata: nullable, no CHECK, no index. HC/HK emit an open-ended device.type set; V1 accepts whatever comes and logs unknowns rather than rejecting; cardinality is low so a DISTINCT scan needs no index (#1515)[14].
  8. Consent is not balance's to own. The push gate is a stub returning True; the canonical ConsentTypeWearableSync enum lives on the Go side and the real check is a documented follow-up[11].
  9. 30-day erasure grace via a tombstone. Soft-delete writes deletion_log; the nightly sweep hard-deletes after hard_purge_at (DR-10.6)[10][24].

9. Caveats and extensibility ​

Group and individual. Wearable data is inherently individual: every row here is scoped by profile_id, which resolves 1:1 to a member's PTY- party locator. A scheme member and a direct-to-consumer member push to the same per-profile store through the same endpoint; the group-ness lives entirely in authorisation upstream (whose JWT resolves to whose profile), not in the streams schema. No schema change distinguishes the two.

Where to extend. The ingest model is deliberately generic: a raw row is (type, unit, value, window) for a profile, so most of what a new wearable product needs is registry data and a connector, not a migration.

When we need …What to addWhere
members to link real devices through a connect flow (Oura, Apple, Amazfit)the vendor OAuth handshake plus the INSERT INTO streams.connection, and a factory entry so POST /ingest/wearable/{vendor}/sync dispatches to that vendor's connectorSOURCE_REGISTRY and the /wearable/{vendor}/sync route it feeds[35], in services/balance/src/balance/surfaces/ingest_routes.py. Connector shells for oura/apple/amazfit already sit under src/balance/sources/ behind the SourceConnector ABC[36]; the connection row's unique key, revoke[22] and purge[23] legs are already written
a new vendor or metric to land in the raw archivenothing on event_raw - the push INSERT is generic over (type, unit, value_num/value_text, start_at, end_at)[15]. Add the metric id to the registry seed and the wire type to compaction's spec map so it folds onwarddata/registries/metric.json (seed, registries page) + _PERDAY_OBSERVATION_SPEC in engine/compaction.py[37]. Configuration and registry data, not a migration
device provenance on a reading (which watch, which ring)populate the envelope's device block on the FE push; the columns land it as-is and /wearable/status already groups its device list by themthe three source_measured_device_* columns shipped in migration 0016[14]; wire shape WearablePushEnvelope[13], read at GET /wearable/status[32]
a real server-side resume pointswap the synthetic string for the vendor's own change token; sync_state.cursor stores it unchanged and the FE round-trip is already in placethe cursor mint in ingest_routes.py[9]
enum-valued metrics (sleep stage, boolean flags)nothing - _coerce_value already routes non-numeric readings to value_text[17]event_raw.value_text, live the first time a push carries one
a taxonomy renameseed metric_alias and let the already-wired resolver rewrite at ingest, instead of a data migration_resolve_alias, called per record[16]

Stated plainly (live facts a reader will hit; none is a defect):

  • sync_state.cursor is a synthetic token, not a vendor change token. It is minted from the batch's newest sample, so a retry resumes from what the FE echoes back rather than from server-held vendor state[9].
  • The three device columns are 100% NULL across 157,070 rows. No push has yet carried per-record device metadata, so /wearable/status's device list is empty in practice.
  • value_text is 100% NULL; every live sample is numeric. The enum/boolean coercion path[17] is not yet exercised on real data; sleep sessions carry their stage list in detail instead.
  • metric_alias is an emptied trampoline. 0014 dropped both seed rows; the resolver runs on every record and returns the input untouched, a deliberate live no-op retained for the next rename[16][34].
  • deletion_log is fully swept. All 42 rows are purged; there is no in-grace deletion to observe today.
  • ~1,865 pending event_raw rows (1.2%) - all per-day movement metrics (steps/distance/active_calories/floors_climbed) sitting between the push and the next compaction pass. This is the steady-state lag of the ~15s on-ingest debounce[27] plus profiles not recomputed since their last push, not the #1511 stall (fixed); sessions and vitals were all compacted.
  • Two of the three "wearable ingest" surfaces bypass event_raw./wearable/load-sample writes episodes directly, and the synthetic generator writes persona Parquet files read via DuckDB - only device-push populates the raw store[6]. The raw archive is "every sample the push endpoint has seen", not "every sample the service has seen".

References ​

Code links are pinned to commit 08e65216 on main (2026-08-28); the file is the anchor if lines drift. Pins are checked mechanically by docs/site/scripts/check-code-refs.py.

  1. 0013_wearable_push.py#L31 - event_raw CREATE TABLE + UNIQUE + pending index
  2. 0013_wearable_push.py#L60 - sync_state CREATE TABLE + PK
  3. 0006_supporting.py#L29 - connection CREATE TABLE + UNIQUE
  4. 0006_supporting.py#L52 - deletion_log CREATE TABLE; "no FK; row outlives the profile"
  5. surfaces/ingest_routes.py#L304 - post_wearable_push, the sole event_raw writer
  6. surfaces/wearable_routes.py#L113 - load_sample writes episodes, not raw
  7. engine/compaction.py#L243 - run_compaction: pending scan → episode fold
  8. engine/recompute.py#L20 - recompute_profile runs compaction as step 0
  9. surfaces/ingest_routes.py#L399 - synthetic cursor + sync_state upsert
  10. surfaces/profile_routes.py#L48 - DELETE /profile: soft-delete + deletion_log + revoke
  11. auth.py#L47 - check_wearable_sync_consent stub (returns True)
  12. auth.py#L37 - profile from verified JWT party_locator
  13. models.py#L321 - WearablePushEnvelope wire shape (Pydantic, not a table)
  14. 0016_wearable_device.py#L32 - device columns ALTER (nullable, no CHECK, no index)
  15. ingest_routes.py#L374 - INSERT ... ON CONFLICT DO NOTHING
  16. ingest_routes.py#L297 - _resolve_alias (live no-op)
  17. ingest_routes.py#L285 - _coerce_value: bool → value_text
  18. compaction.py#L262 - pending fetch WHERE compacted_at IS NULL AND type = ANY(...)
  19. compaction.py#L390 - stamp compacted_at = now() (mapped + skip-stamped)
  20. compaction.py#L146 - _ORIGIN_PRIORITY cross-app dedup (#1512)
  21. read/movement_history.py#L85 - device attribution via event_raw.origin_app
  22. profile_routes.py#L61 - the only write that names connection (revoke)
  23. hard_purge.py#L24 - purge table list including streams.connection
  24. hard_purge.py#L14 - the 30-day sweep: read deletion_log, delete rows, stamp purged_at
  25. ingest_routes.py#L314 - batch cap / SDK match / timestamp guards
  26. compaction.py#L593 - _insert_episode marked /* d50 compaction */
  27. engine/scheduler.py#L126 - schedule_on_ingest debounce/coalesce
  28. ingest_routes.py#L422 - trigger compaction only when accepted (2026-08-13 fix)
  29. scheduler.py#L25 - _daily_pass active set UNIONs pending-raw profiles
  30. ingest_routes.py#L446 - on-demand POST /admin/compaction/run
  31. scheduler.py#L99 - _nightly_purge scheduled 04:30
  32. surfaces/wearable_routes.py#L279 - GET /wearable/status: event_raw + sync_state telemetry
  33. scripts/backfill_wearable_episodes.py#L6 - "write-only archive" rationale + raw→episode rebuild
  34. 0014_metric_rename.py#L40 - server-side taxonomy flip; drop the alias rows (#1510)
  35. ingest_routes.py#L98 - SOURCE_REGISTRY vendor → connector factory + the /wearable/{vendor}/sync route that dispatches on it
  36. sources/connector.py#L1 - SourceConnector ABC (DR-7.1: nothing downstream knows which connector produced an episode)
  37. compaction.py#L85 - _PERDAY_OBSERVATION_SPEC: wire type → (metric, unit, origin), the add-a-metric surface
  38. temporal/trigger.py#L52 - dispatch_ingest_compaction: Temporal CompactionWorkflow when the flag is on and the server reachable, else the APScheduler fallback

Live-schema facts (row counts, compacted/pending split, per-platform and per-type breakdowns, device-column and value_text NULL census, the sync_state / deletion_log / connection populations, and the worked example rows) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d balance · \d streams.event_raw, \d streams.sync_state, \d streams.connection, \d streams.deletion_log, and the counts/example queries, 2026-08-21.

Olly Health Insurance Platform

© 2026 Olly