Wearable streams: findings & profile
Schema deep-dive · living document · #25 in the reading sequence
| Tables | streams.finding[1], streams.finding_evidence[2], streams.insight[3][4], streams.knowledge_gap[5], streams.profile[6], streams.onboarding_state[7], streams.self_assessment[8] |
| Owner service | balance (sole writer) |
| Locator | natural key - the chain tables key on profile_id (uuid, FK to streams.profile); the profile itself keys on party_locator (a PTY- alias or a raw persona uuid) |
| Last updated | 2026-08-21 |
| Companion | previous: Scores (#24) · reading sequence · this page closes the Phase-3 streams section |
1. Scope and usage
This page closes the Phase-3 streams section by joining its two ends: the analysis layer the engine emits, and the member state it runs against. Everything upstream - event_raw samples compacted into episodes, rolled up into rollup/baseline, scored into movement_score/shape_score
exists so that a nightly engine pass can say something true about a member. The four finding-chain tables are where it says it:
finding- one detected pattern (a rising trend, a z-score anomaly, a sustained low run). The machine fact: which detector fired, on which metric, with what statistic and confidence.[1]finding_evidence- the episodes that back a finding. 39,512 rows for 539 findings (the second-biggest table in the domain, afterevent_raw): every finding fans out to every episode that carried its metric across the detector's window.[2]insight- the member-facing object. History lives infinding; the insight table holds the one current object per(profile, detector, metric), discretized intostatus/magnitudebands and stamped with the coverage gate's verdict, itsstatementfilled later by the narration LLM.[4]knowledge_gap- a shape the system cannot yet speak to because its data is still below floor. What is missing, tracked as an open/resolved record.[5]
The three member-state tables are what the engine runs for:
profile- the streams identity, one per member, keyed byparty_locator. Every otherstreamstable cascades off it. 35 live rows.[6]onboarding_state- the server-tracked onboarding walk: a step machine, a per-stepstep_datajsonb, a completion stamp. 1 live row; the member app runs onboarding through its own flow today, and this table is the balance-side walk that serves it when onboarding routes here (§9).[7]self_assessment- a member's subjective rating of a whole pillar ("I think I'm moderately active"), with the transcript snippet it came from. 49 live rows, captured by the chat/onboarding ingest and held for the analysis reader that folds self-report against the computed picture (§9).[8]
Two properties shape how the rest of the page reads. The analysis chain is detector-shaped and open at the top: a detector is a pure function returning a shared DetectorFinding, and the finding row absorbs whatever it returns - detector and method are free text, evidence_window is jsonb - so a new primitive lands with no migration (§9). The member-state tables are modelled ahead of the surfaces that consume them: the onboarding step machine and the self-report ratings are in place and populated, ready for the client and the reader that use them. One genuine defect runs through the chain and is stated in §9: finding carries no unique key, so each recompute re-inserts and the 539 live rows collapse to 144 distinct (profile, detector, metric, value, window) signatures across 11 profiles.
2. Boundaries and relationships
| A finding is not… | That concern lives in | Join |
|---|---|---|
| the raw sample | event_raw (page #22) - the finding never cites a sample, only the episodes compacted from them | finding_evidence.episode_id → episode |
| the rollup / baseline the detector reads | rollup / baseline (page #23) - the finding is the detector's output over that series, not the series | none stored; recomputed each pass[10] |
| the score | movement_score / shape_score (page #24) - a score is the continuous number; a finding is a discrete detected pattern | both hang off profile_id; no direct FK |
| the member-facing sentence | insight.statement, filled by the narration LLM (#1505) - the finding is the machine fact the LLM translates, never interprets | insight.finding_id → finding |
| a clinical action | triage / care - balance only observes (insight.posture = 'observation'); it routes nothing and diagnoses nothing | none; triage calls balance's POST /streams/digest for context |
| the member's identity | parties (party service) - profile.party_locator is a soft, un-FK'd PTY- reference | text locator, no cross-DB FK |
Everything cascades off profile. Nineteen streams tables carry a profile_id FK with ON DELETE CASCADE back to streams.profile[6], so a profile delete tears down the member's whole streams footprint in one row drop. The chain is deeper still: insight.finding_id and finding_evidence.finding_id both cascade off finding, so deleting a finding takes its insight and evidence with it (§5).
3. Structure
DDL: derived tables[1][2][3][5], insight object columns[4], profile[6], onboarding_state[7], self_assessment[8]. Tables are raw SQL inside alembic migrations - there is no ORM; the Pydantic models under balance/streams/models.py are API shapes, not table definitions (P3 house rule).
streams.finding
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK, gen_random_uuid() |
profile_id | uuid | ✓ | FK → profile, ON DELETE CASCADE |
detector | text | ✓ | trend / anomaly / period / correlation / activity_level / hrv_balance / rhr_trend / sleep_regularity / sleep_debt - which primitive fired |
metric | text | The metric analysed (active_minutes, steps, hrv, sleep_duration…); nullable in DB, always set in practice | |
shape | text | The pillar the metric belongs to (movement / sleep / recovery); indexed with profile_id | |
method | text | ✓ | The statistic used - ols_slope_ttest, z_score, descriptive_run, spearman, rmssd_ratio_28d, week_mean_ratio_28d… |
value | double precision | The statistic's value (slope, z, run-length, ratio); nullable | |
evidence_window | jsonb | ✓ | {n, p, r2} / {baseline_mean, baseline_n, value} etc. - the numbers behind the finding |
confidence | double precision | ✓ | Default 1.0; 1 - pvalue for stats detectors, 1.0 for descriptive |
computed_at | timestamptz | ✓ | Default now(); the pass that produced this row |
streams.finding_evidence
| Field | Type | Req | Notes |
|---|---|---|---|
finding_id | uuid | ✓ | FK → finding, ON DELETE CASCADE; half of the PK |
episode_id | uuid | ✓ | FK → episode, ON DELETE CASCADE; other half of the PK, and separately indexed |
Two columns, no payload - a pure join table. The composite PK (finding_id, episode_id) makes a finding→episode link idempotent, and the standalone episode_id index is what the delete cascade walks backwards (§5).
streams.insight
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK |
profile_id | uuid | ✓ | FK → profile |
finding_id | uuid | FK → finding; NULL for affirmative-state and placeholder objects (no signal behind them) | |
posture | text | ✓ | Default 'observation' - the only posture; balance observes, never advises |
statement | text | ✓ | The member-facing sentence; '' until the W3 narrator fills it |
detector / metric | text | The upsert key with profile_id; NULL only on a pre-0017 legacy row | |
status | text | Discretized band (rising, below_baseline, on_baseline, sustained_low…) | |
magnitude | text | moderate / marked for signals; steady for affirmative states | |
coverage_ok | boolean | ✓ | Default true; the coverage gate's verdict, stamped at compute time |
gate_outcome | text | ✓ | Default 'pass'; pass / insufficient / stale / source_missing |
formula_version | text | e.g. hrv_balance@1.0 - auditability + backfill | |
sources | jsonb | ✓ | Default []; distinct episode attributions behind the evidence |
window_days / n_observations | integer | The design-doc §2.2 contract fields | |
value | double precision | The finding's raw statistic, carried for the card | |
computed_at | timestamptz | ✓ | Default now() |
streams.knowledge_gap
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK |
profile_id | uuid | ✓ | FK → profile |
shape | text | ✓ | The pillar that is under-covered (movement, sleep…) |
gap_type | text | ✓ | The DDL comment lists insufficient_data / deficit; v1 writes insufficient_data, deficit is the reserved second type (§9) |
evidence | jsonb | ✓ | {reason, window_days} - e.g. {"reason":"below_floor","window_days":30} |
computed_at | timestamptz | ✓ | Default now() |
resolved_at | timestamptz | Set when the shape leaves calibrating; NULL = still open |
streams.profile
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK; the profile_id every other streams table joins on |
party_locator | text | ✓ | UNIQUE; the member's identity (a PTY- alias or a raw persona uuid); separately indexed |
display_name | text | Optional friendly name (persona fixtures carry one) | |
created_at | timestamptz | ✓ | Default now() |
deleted_at | timestamptz | Soft-delete tombstone; hard purge sweeps 30 days later |
streams.onboarding_state
| Field | Type | Req | Notes |
|---|---|---|---|
profile_id | uuid | ✓ | PK and FK - exactly one state row per profile |
current_step | text | ✓ | Default 'splash'; a value from the linear STEPS tuple (splash→…→pillars.movement→…→complete) |
started_at | timestamptz | ✓ | Default now() |
completed_at | timestamptz | Set when the walk reaches the terminal step; NULL = in progress | |
step_data | jsonb | ✓ | Default {}; accretes {step: payload} as each step completes |
streams.self_assessment
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK |
profile_id | uuid | ✓ | FK → profile |
pillar | text | ✓ | CHECK in ('movement','sleep','joy','eating','mind') - the only DB CHECK in this page's tables |
rating_text | text | ✓ | The subjective rating ("moderately active", "feeling OK lately") |
source_text | text | The original transcript snippet the rating came from (free-text; PII) | |
created_at | timestamptz | ✓ | Default now(); indexed with (profile_id, pillar) |
Field-by-field: what and why
finding.evidence_window is the finding, not a footnote. The row's detector/method/value say that a pattern was found; evidence_window carries the numbers that make it auditable - for a trend, {n, p, r2}; for an anomaly, {baseline_mean, baseline_stddev, baseline_n, value}; for a period run, {threshold, min_run, longest_run, n}.[13][14][15] The in-memory shape is DetectorFinding, a frozen dataclass every detector returns; persist_findings copies its fields straight onto the row.[12]
The finding→evidence fan-out. persist_findings inserts the finding, then loops the same episode_ids list into finding_evidence for every finding in the batch.[9] Those episode ids are collected in recompute as every non-deleted episode carrying the finding's metric across the detector's window.[10] So a finding's evidence count is window_length × episode_density: a 7-day movement trend on an active persona links ~8 episodes; a 60-day RHR window links dozens. Live, the 539 findings carry 73.3 evidence rows on average, min 1, max 610 - which is how 539 findings become 39,512 evidence rows. Note the distinction the numbers reveal: the finding's statistic runs on daily rollups (a trend's evidence_window.n is a count of days), while its evidence links the episodes underneath (more numerous, because a day can hold several) - §6 walks a concrete case.
insight is a current-state upsert; finding is append-only history. The 0017 migration turned the insight row into a self-describing object and added the partial unique index insight_current_object_idx (profile_id, detector, metric) WHERE detector IS NOT NULL.[4] Every emitter writes INSERT … ON CONFLICT (profile_id, detector, metric) WHERE detector IS NOT NULL DO UPDATE, so a member holds at most one live object per detector-metric - the status flips (e.g. below_baseline ↔ on_baseline) as their data changes.[20] That is why 539 findings project down to 60 insight rows live.
status/magnitude are decided deterministically, before any LLM sees the row. discretize maps a finding to a (status, magnitude) band by fixed thresholds - hrv_balance below 1.0 is below_baseline, an anomaly with |z| ≥ 3.0 is marked.[18] The narration LLM later fills statement from an already-decided fact; it never interprets value. This is the single guardrail against numeric hallucination, and it is why statement ships '' from day one and the API contract is stable before the narrator exists.
coverage_ok + gate_outcome freeze the four-condition coverage gate.gate_finding evaluates the design-doc §5.1 conditions - density (re-check the detector floor), recency (last observation inside a per-metric freshness bound), and baseline maturity (trend/z detectors need ≥ 21 baseline days) - and returns pass / insufficient / stale.[19] The verdict is stamped onto the row so consumers must not re-derive it: a coverage_ok=false object still ships, carrying its outcome, so the card renders a "need more data" / "stale" state instead of vanishing.
finding_id NULL is a feature, not a defect. Two emitters write insight objects with no finding behind them: emit_state_object writes the affirmative state ("your resting heart rate is steady") when a detector is silent but the data passed its floor[21], and emit_placeholder_object writes a gated placeholder (source_missing / insufficient) so the bundle can show why a kind has nothing to report rather than silently omitting it.[22] Both use the same (profile, detector, metric) upsert key as signal objects, so a member's current object per kind is exactly one row whether the news is good, bad, or absent. Live, 10 of 60 insights have finding_id set with gate_outcome = pass; 16 are insufficient, 13 source_missing, 9 stale - the honest majority of the surface is "we can't say yet".
knowledge_gap.gap_type carries a second type ahead of its emitter.emit_gaps reads each shape_score for the profile and, for every shape still calibrating, opens an insufficient_data gap if none is open; when the shape leaves calibration it stamps resolved_at.[23] The docstring is explicit that "v1 only emits insufficient_data"; deficit - the shape whose data is sufficient but below where it should be - is named in the DDL comment and reserved for the emitter branch that writes it (§9). All 95 live gaps are insufficient_data, 22 of them resolved.
profile is auto-provisioned on first authenticated call.find_or_create_profile does an INSERT … ON CONFLICT (party_locator) DO UPDATE … RETURNING id, so the profile row appears the first time a member's JWT reaches balance and is idempotent thereafter.[25] party_locator is a soft reference to the party service - no cross-DB FK - and live it is a mix: some rows carry PTY- aliases, others raw persona uuids from the fixture seeder.
4. Invariants
| Invariant | Enforced by |
|---|---|
One profile per party_locator | DB UNIQUE on profile.party_locator[6] + the ON CONFLICT upsert[25] |
One current insight object per (profile, detector, metric) | DB partial UNIQUE index insight_current_object_idx[4] + ON CONFLICT … DO UPDATE on every emitter[20] |
| One onboarding_state row per profile | DB PK on profile_id[7] + INSERT … ON CONFLICT DO UPDATE in get_or_init_state[27] |
| A finding→episode link is unique | DB composite PK (finding_id, episode_id)[2] |
| Evidence/insight cannot outlive its finding; nothing outlives its profile | DB FKs ON DELETE CASCADE throughout[1][2][3] |
self_assessment.pillar is one of five | DB CHECK constraint[8] |
| A finding is only emitted when its detector clears its floor | Application - every detector calls has_floor_data(shape, detector, n_days) and returns None (silence) below it[13] |
status/magnitude are deterministic, not LLM-decided | Application - discretize maps value→band by fixed thresholds before narration[18] |
| A gated object still ships (never silently vanishes) | Application - coverage_ok=false objects are inserted with their gate_outcome, not skipped[19] |
| A finding survives only while it has live evidence | Application - the delete cascade prunes a finding once no non-deleted episode remains[33] |
finding rows are unique per pass | Nothing - finding has no unique key; each recompute re-inserts, so duplicates accumulate (§9) |
posture is always observation | Convention - the column defaults to 'observation' and no writer sets anything else; no CHECK forbids it |
| Row changes captured to CDC | Debezium publication dbz_balance on all seven tables (live \d) |
5. Lifecycle
There is no single status machine here - the seven tables have three different shapes of life. The shared spine is how a finding is produced and projected; the three member-state tables each have their own simpler arc.
How a finding is produced (the shared flow)
The pass is driven by an in-process APScheduler: a nightly cron (default 0 4 * * *, overridable by GrowthBook flag balance.engine.daily.cron) recomputes every active profile[34][35], and a debounced on-ingest hook fires a fast compact+recompute ~15 s after the last wearable push (narration skipped on that path).[36] Inside a pass, recompute_profile runs the primary-shape detectors (trend, anomaly, period) and then the W1 sleep/recovery/activity primitives; each signal finding is persisted and its insight object emitted via _persist_and_emit, while a silent detector with floor-passing data emits the affirmative state instead.[11] The coverage gate judges freshness against as_of, not wall-clock, so a backdated pass gates sanely.
finding itself has no lifecycle. A finding row is born once in a persist_findings insert and is immutable - never updated, never given a status. It dies only by cascade: when an episode is soft-deleted, cascade_on_episode_delete walks finding_evidence backwards, removes the link, and if the finding has no remaining live evidence it deletes the finding - taking its insight and remaining evidence with it on the ON DELETE CASCADE.[33] This is the one place finding_evidence earns its 39,512 rows as more than support: it is the referential index that keeps the analysis layer honest when a member deletes data.
insight and knowledge_gap arcs
insight is a perpetual upsert: the current object per detector-metric, whose status/coverage_ok/gate_outcome are overwritten every pass and whose statement transitions '' → narrated text when the W3 narrator runs.[20]knowledge_gap is the only table here with an explicit open → resolved transition: opened while a shape calibrates, resolved_at stamped when it leaves.[23]
The member-state arcs
profile: created on first authenticated call, soft-deleted (deleted_at) by DELETE /profile, hard-purged 30 days later by the sweeper.[30]onboarding_state: init-on-first-GET, one current_step walked linearly through the STEPS tuple, completed_at stamped at the terminal step.[28]self_assessment: insert-only - a rating is captured, never updated or deleted, and no surface reads it back today (§9).
6. Populated example: a movement trend finding, walked to its evidence
A live finding from persona Clare Otter (profile_id2bec3a1e-…6913, party_locator dfa4c3b4-… - a fixture profile, not a real member), showing the detector→finding→evidence chain concretely.
The finding row
{
"id": "dd64e8aa-7532-44db-bed2-13820c1b1a8f",
"profile_id": "2bec3a1e-59a5-4bcc-ba85-94e01dae6913",
"detector": "trend",
"metric": "active_minutes",
"shape": "movement",
"method": "ols_slope_ttest",
"value": 7.2857,
"evidence_window": {"n": 7, "p": 0.0493, "r2": 0.5715},
"confidence": 0.9507,
"computed_at": "2026-06-02T18:23:20Z"
}| Key | Read by | What actually happens |
|---|---|---|
detector: trend, method: ols_slope_ttest | detect_trend[13] | an OLS slope + t-test over Clare's daily active_minutes rollups fired because p = 0.049 < 0.05 |
value: 7.2857 | the insight card | the slope: active minutes rising ~7.3 min/day over the window |
evidence_window.n: 7 | discretize / the card | the statistic ran on 7 daily rollups - not 7 episodes |
confidence: 0.9507 | discretize (trend → marked when ≥ 0.99) | 1 - pvalue; here marked-adjacent but moderate |
computed_at: 2026-06-02 | nothing on read | one of many passes - this exact finding recurs across later passes (§9) |
Its evidence (8 rows for a 7-day statistic)
finding_evidence links this one finding to 8 episodes, spanning 2026-05-27 to 2026-06-02:
| episode_id | episode_type | day | observations |
|---|---|---|---|
cc52d11d-… | fitness_classes | 2026-05-27 | 1 |
b37c40c4-… | strength_training | 2026-05-29 | 1 |
6042163b-… | walking | 2026-05-31 | 2 |
42cebe34-… | running | 2026-06-01 | 2 |
74269121-… | walking | 2026-06-02 | 1 |
ab198088-… | biking | 2026-06-02 | 1 |
569aeaab-… | walking | 2026-06-02 | 1 |
5432f576-… | running | 2026-06-02 | 1 |
This is the fan-out made concrete: the finding's statistic ran on 7 daily rollup values, but its evidence links the 8 episodes underneath, because 2026-06-02 alone holds four (two walks, a bike, a run). persist_findings took the full metric-carrying episode list for the window and wrote one finding_evidence row per episode.[9] Scale that up - a 60-day recovery window on a dense wearable persona - and one finding links hundreds of episodes, which is how 539 findings reach 39,512 evidence rows (max 610 on a single finding).
The insight object it emits
A trend-on-active_minutes finding upserts one current insight object. A live example (a different member, whose narrator has already run):
{
"detector": "trend", "metric": "active_minutes",
"status": "rising", "magnitude": "moderate",
"coverage_ok": true, "gate_outcome": "pass",
"formula_version": "trend@1.0", "window_days": 30, "n_observations": 14,
"value": 4.0813,
"sources": ["com.hevy","com.ouraring.oura","com.sec.android.app.shealth","movement_chat"],
"statement": "You are building some great momentum with your active minute…"
}discretize set status: rising / magnitude: moderate deterministically from value and confidence; gate_finding passed all four conditions (coverage_ok: true); sources records the distinct apps behind the evidence episodes; and the narrator filled statement from those already-decided facts.[18][20]
The member-state rows
A profile row (persona fixture; no PII beyond the display name):
{
"id": "2bec3a1e-59a5-4bcc-ba85-94e01dae6913",
"party_locator": "dfa4c3b4-a577-4d4b-99b8-c083188f4d78",
"display_name": "Clare Otter",
"created_at": "2026-06-02T00:02:49Z",
"deleted_at": null
}The single onboarding_state row is stuck mid-walk - it reached pillars.joy and never completed, its step_data accreting one captured activity per pillar (a meditation habit, a walk, an 8-hour sleep, one dropped "typical daily meals"):
{
"profile_id": "664aa42c-…e2d8",
"current_step": "pillars.joy",
"completed_at": null,
"step_data": {"pillars.sleep": {"captured": [{"kind":"episode","summary":"Slept 8 hours",…}]}, …}
}A self_assessment row (source_text redacted - it holds the raw member transcript):
{ "pillar": "joy", "rating_text": "Social life is good", "source_text": "[redacted]" }Live population for context (2026-08-21): 539 findings (144 distinct signatures) across 11 profiles - 209 trend, 144 period, 116 activity_level, 55 anomaly, 15 hrv_balance, by shape 317 sleep / 207 movement / 15 recovery; 39,512 finding_evidence; 60 insights (46 with a narrated statement, 14 still ''); 95 knowledge_gaps (all insufficient_data, 22 resolved); 35 profiles; 1 onboarding_state; 49 self_assessments (20 sleep, 13 mind, 7 joy, 5 movement, 4 eating).
7. Who references these tables
| Where | Column / mechanism | Meaning there |
|---|---|---|
insight.finding_id | FK → finding | the current object points back at the finding it discretized |
finding_evidence.finding_id / .episode_id | composite FK | the evidence join; walked backwards by the delete cascade[33] |
streams.alert.finding_id | FK ON DELETE SET NULL | an alert cites the finding that raised it; cascade annotates + nulls it on delete[33] |
streams.plan.gap_id | FK → knowledge_gap ON DELETE SET NULL | a training plan may cite the gap it addresses (schema link; not populated by the emitter) |
Insights API - GET /insights/bundle, GET /insights | reads insight LEFT JOIN finding | the gated fact bundle; the only input surface for the narrator + cards[31] |
Insight detail - GET /insights/{id} | reads insight JOIN finding, then finding_evidence | the only query-time reader of finding_evidence - returns the evidence episode ids[32] |
| QA / movement-chat agents | query_findings(shape, detector, limit) | the agent cites raw findings in chat; newest-first, capped at 50[24] |
Visualise - GET /shapes/{shape} drill-down | reads finding WHERE profile_id AND shape | the shape's detected patterns behind its score[37] |
profile | every streams table's profile_id FK | the tenancy root; 19 tables cascade off it[6] |
Onboarding routes - GET/POST /onboarding/* | get_or_init_state / advance_state | the balance-native onboarding walk (1 live row)[27][29] |
self_assessment | no reader today | written by the chat/onboarding ingest[26]; the reader that folds self-report into the analysis is the extension point in §9 |
Only insight.finding_id, finding_evidence, alert.finding_id and plan.gap_id are true in-schema FKs; profile.party_locator is a soft cross-service reference. Nothing outside balance reads these tables directly - the member app reaches them only through the Insights API.
8. Design determinations
- Detector contract is signal-or-silence; history lives in
finding. Each detector is a pure function returning aDetectorFindingorNone, floor-gated, with no DB access -trend/anomaly/period/correlation(DR-4.9, #1502) plus the W1 sleep/recovery/activity primitives.[12][13] - The insight row is a self-describing object. #1503 (design doc #1491, the D-50 ingest ADR) made the insight the ONLY thing the narrator and cards ever see: discretized bands + coverage-gate verdict, decided deterministically.[4][18]
- Bands decided in code, prose by LLM.
discretizefixesstatus/magnitudebefore any model runs; the narrator (#1505) translates an already-decided fact and never interprets a number - the guardrail against numeric hallucination.[18] - An insight kind with nothing to report ships its reason, it does not vanish.
emit_state_objectandemit_placeholder_objectcover the "member is doing well" and "no data yet" cases under the same upsert key, so the bundle always shows one object per kind with itsgate_outcome.[21][22] - Evidence is episode-level, and it is what keeps the analysis honest on delete.
finding_evidenceis a bare join table, but the delete cascade uses it to prune findings that lose their evidence when a member deletes an episode.[33] - Knowledge gaps ship v1-minimal with the type space already open.
emit_gapswritesinsufficient_data(DR-4.17);gap_typeis free text and the DDL comment reservesdeficit, so the richer gap is an added branch in one function rather than a migration.[23][5] - Batch, not real-time. Findings are produced by a nightly cron pass plus a debounced on-ingest hook, never synchronously on a member request - the engine is a scheduled recompute, by design.[34][36]
- Profile is provision-on-first-call, party is a soft reference. No sign-up step writes a profile; the first authenticated JWT does, idempotently, and
party_locatoris never FK-checked against the party service.[25]
9. Caveats and extensibility
Group and individual. These tables are strictly member-level. A profile is one person's streams identity keyed by party_locator; findings, insights, gaps and self-assessments all hang off profile_id. There is no scheme, no employer, no group concept anywhere in the analysis chain - a scheme member and a direct-to-consumer member produce identical rows, and group-ness lives entirely in the party/scheme services upstream. Wearable analysis is personal by construction, so the design serves both cohorts with no schema change.
Where to make the change, when the need lands. The analysis chain is deliberately open at both ends: a detector is a pure function behind one shared dataclass, and the finding row absorbs any detector's output because detector / method are free text and evidence_window is jsonb. The member-state tables carry capabilities the product has not yet routed through them. Each row below names the surface to touch.
| When we need ... | What to add | Where |
|---|---|---|
| a new detector (a new primitive over the same rollups) | a module returning a DetectorFinding or None, a floor entry for it, a call site in the pass, and a discretize band | engine/detectors/[12], engine/floors.py[38], _persist_and_emit in engine/recompute.py[11], discretize[18]. No schema change - finding + finding_evidence take the rows as they are[1][2], and discretize falls through to status='observed' for a detector it does not know, so the insight object ships before its band is written[18] |
correlation and rhr_trend to start producing findings | data, not code: curated metric pairs for correlation, and a 60-day HR baseline matured by the ingest path for RHR | both detectors are built and called[16][17]; their floors (correlation 14 days, rhr_trend 21) keep them silent until coverage arrives, and balance.floors.<shape>.<detector> retunes that without a deploy[38]. This is a data-maturity threshold, and it clears as members accumulate history |
a below-baseline deficit gap ("enough data, and it is short") | a second branch in emit_gaps writing gap_type='deficit' with its own evidence payload | engine/gaps.py[23]; gap_type is free text and the DDL comment already reserves the value[5], so the change is additive |
| self-reported ratings to influence the analysis | a reader over self_assessment - the member's own pillar rating set beside the computed shape_score, either as a card field or as a gate input | rows are already captured per (profile, pillar) by _store_self_assessment[26], with the CHECK fixing the five pillars and source_text carrying the transcript for provenance[8] |
| members onboarded through balance itself | nothing in the schema - point the client at GET/POST /onboarding/* | onboarding/state.py[27][28] + onboarding/routes.py[29]. The step machine, the per-step step_data jsonb and the completion stamp are modelled and exercised; a new step is a STEPS entry, not a column. Today the member app drives onboarding in its own flow, so the table holds 1 live row |
| a different narrator, voice or model | swap the agent - the row contract does not move | agents/insight_narrator.py[39] writes only statement; status, magnitude, coverage_ok and value are decided before it runs[18][19] |
a second posture (advice, not observation) | a value + the routing rules behind it | insight.posture is free text defaulting 'observation'[3]; the column exists to make "balance observes" explicit and to leave room for the determination that changes it |
Facts a reader should carry:
finding_evidenceis the second-biggest table in the domain and mostly referential. 39,512 rows; the one query-time reader is the single-insight detail endpoint[32], and the rest of its work is being walked backwards by the delete cascade[33]. Write amplification is real (finding × full evidence set × every pass) and follows from the append-onlyfindingdefect below.finding.metric/shapeare nullable and always set. The DDL allows NULL; no live row carries one. ANOT NULLwould tighten it at any time.postureholds one value. Every insight is'observation'; no writer sets anything else and no CHECK forbids one.- One legacy insight row predates the object model. 60 insights, 59 keyed by
(profile, detector, metric); one pre-0017 row carries a NULLdetectorand is excluded from the bundle by thedetector IS NOT NULLfilter - harmless, and a reminder the object columns were added to an existing table.[3]
Known defects, and the fixes:
findinghas no unique key, so every recompute re-inserts. 539 live rows collapse to 144 distinct(profile, detector, metric, value, window)signatures - one signature repeats many times, seconds apart across passes. The insight upsert hides it downstream (60 clean rows) and the delete cascade copes, but the raw table grows with pass count rather than with signal, andquery_findings[24] hands an agent the same fact repeatedly. Fix: givefindingthe dedup key it lacks - a UNIQUE index on(profile_id, detector, metric, method, value, evidence_window)(or a hashed signature column) in a new migration alongside0007_derived.py[1], and turnpersist_findings's INSERT intoON CONFLICT DO UPDATE SET computed_at=now()inengine/findings.py[9], so a repeat pass refreshes the row instead of minting one. The evidence PK is already idempotent[2], so the fan-out shrinks with it.- 14 of 60 insight rows carry
statement = ''with no record of why.narrate_profileis fail-soft by design - a missing API key, an agent error, or a statement dropped by the foreign-numeral validator all return without writing, and nothing on the row says which happened[39]. The card then renders its bands with no sentence, and the reason lives only in a log line. Fix: stamp the narration outcome on the row (anarration_status/narrated_atpair onstreams.insight) and re-narrate rows that hold''on the next pass, inagents/insight_narrator.py[39]. Gated objects legitimately stay empty - the narrator only loads pass-gate objects - so the fix must distinguish "not narrated because gated" from "narration failed".
References
Code links are pinned to commit b61c5802 on main (2026-08-21); the file is the anchor if lines drift. Pins are checked mechanically by docs/site/scripts/check-code-refs.py.
migrations/versions/0007_derived.py#L43-findingCREATE +finding_profile_shape_idx0007_derived.py#L58-finding_evidenceCREATE (composite PK + episode index)0007_derived.py#L67-insightoriginal CREATE (pre-object model)0017_insight_objects.py#L36- insight object columns +insight_current_object_idx(#1503)0007_derived.py#L91-knowledge_gapCREATE0002_profile.py#L16-profileCREATE + UNIQUEparty_locator0006_supporting.py#L42-onboarding_stateCREATE (PK = profile_id)0010_self_assessment.py#L20-self_assessmentCREATE + pillar CHECKengine/findings.py#L11-persist_findings: finding insert + evidence fan-outengine/recompute.py#L72- detector loop + evidence-episode collectionengine/recompute.py#L160-_persist_and_emit: persist then upsert insight objectengine/detectors/__init__.py#L7-DetectorFindingshared dataclassengine/detectors/trend.py#L11-detect_trend(OLS slope + t-test)engine/detectors/anomaly.py#L8-detect_anomaly(z-score)engine/detectors/period.py#L8-detect_period(descriptive run)engine/detectors/correlation.py#L10-detect_correlation(Spearman; 0 live rows)engine/detectors/recovery.py#L49-detect_rhr_trend(sustained z over 60d; 0 live rows)engine/insight_objects.py#L59-discretize: value → (status, magnitude)engine/insight_objects.py#L105-gate_finding: the four coverage conditionsengine/insight_objects.py#L185-emit_insight_object: current-object upsertengine/insight_objects.py#L137-emit_state_object: affirmative state (finding_id NULL)engine/insight_objects.py#L227-emit_placeholder_object: source_missing / insufficientengine/gaps.py#L9-emit_gaps: open/resolve knowledge_gap (insufficient_data only)read/findings.py#L12-query_findings: the agent-facing readprofile.py#L16-find_or_create_profile: provision-on-first-callingest/multi.py#L68-_store_self_assessment: the sole writeronboarding/state.py#L31-get_or_init_state: init-on-first-GETonboarding/state.py#L56-advance_state: step-machine advanceonboarding/routes.py#L78-POST /onboarding/answersurfaces/profile_routes.py#L48-DELETE /profile: soft-delete + deletion_logsurfaces/insights_routes.py#L45-GET /insights/bundle: the gated fact bundlesurfaces/insights_routes.py#L189-GET /insights/{id}: the only finding_evidence readercascade.py#L12-cascade_on_episode_delete: prune findings that lose live evidenceengine/scheduler.py#L25-_daily_pass: nightly recompute of active profilesengine/scheduler.py#L91- the cron registration (balance.engine.daily.cron)engine/scheduler.py#L126-schedule_on_ingest: debounced fast pathsurfaces/visualise_routes.py#L56- shape drill-down readsfindingby shapeengine/floors.py#L6-_BASE+_OVR: the per-detector data floors, GrowthBook-overridableagents/insight_narrator.py#L148-narrate_profile: fail-soft agent call + the three statement-rejection branches
Live-schema facts (row counts, detector/shape/method breakdowns, evidence fan-out min/avg/max, distinct finding signatures, insight gate-outcome census, knowledge-gap resolution, the worked Clare Otter finding + its evidence rows, the profile / onboarding_state / self_assessment samples, Debezium publication) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d balance · \d streams.finding, \d streams.finding_evidence, \d streams.insight, \d streams.knowledge_gap, \d streams.profile, \d streams.onboarding_state, \d streams.self_assessment, 2026-08-21.