The Apple Health ZIP import upserted each spot reading on the
(userId, type, source, externalId) index only. When two readings shared the
natural key (userId, type, measuredAt, source, sleepStage) with different
externalIds — a re-import of overlapping data, or two same-instant Apple
samples from different source apps — the externalId upsert missed and the
create collided on the second unique index (migration 0055), raising P2002
and aborting the entire import (issue #486, after the v1.28.36 dedup fix let
the upload run).
flushSpotBatch now wraps the per-row upsert: on P2002 it probes the occupying
row by the natural key (tombstone-inclusive) and adopts it in place — re-keys
to the incoming externalId, takes the new value/unit, resurrects if
soft-deleted — mirroring the v1.28.39 provider natural-key rescues (closest to
withings/sync-sleep). If no twin resolves it, the one reading is skipped and
the run continues; the catch sits in the per-row loop outside any transaction,
so a collision can never abort the batch or the import. Adopted rows count as
updated, skipped ones surface honestly. Workout + stats: paths verified unable
to hit this (no second unique index / deterministic externalId). Repro +
real-Postgres integration tests added.
From the campaign's security re-verification pass (posture otherwise held
across the 10-release week — no Critical/High):
- MEDIUM: the OIDC auto-provision branch re-introduced the first-admin race
the password-register route fixed in v1.28.42 — count-then-create with no
lock, so two concurrent OIDC first-logins (or an OIDC first-login racing a
password first-registration) in the empty-DB window could both mint ADMIN.
Now wraps count+role+create in a $transaction taking the SAME advisory lock
key ('register:first-admin') and re-counts inside, so the two paths
serialise; every provisioned field preserved.
- LOW: the nutrients batch catch echoed the raw exception message into the
client-facing reason (violating its closed reason-set). Now returns the
fixed 'upsert_failed' member and logs the detail server-side only.
No migration (advisory lock is runtime).
Bumps pg-boss 12.20 → 12.26 (schema-drift detection, self-healing background
index builds, correctness fixes). Some of those fixes surface errors earlier
versions silently swallowed. Verified safe: the app attaches an error listener
to the boss instance (reminder-worker.ts) and uses none of the direct calls the
change affects (insert/fetch/deleteQueue) — only send/work/start/stop/schedule/
getJobById, and the one getJobById is already wrapped in try/catch. The full
integration suite (101 files, 497 tests) exercising the queue against real
Postgres passes unchanged under 12.26. No config change.
Deferred perf fixes from the campaign's verified perf-correctness audit,
correctness-first (each with an output-equivalence test):
- comprehensive-generate fan-out: replaced per-type 400d findMany+JS-average
with DB prisma.aggregate(_avg) over the exact half-open windows the JS
reducer used (current 30d, lastMonth (60d,30d], lastYear (395d,365d]), same
rounding + null-on-empty, pLimit(4). SLEEP_DURATION stays on the raw path
(per-night reconstruction dedup). Byte-equality test vs the old algorithm.
- boot-discovery: replaced Prisma in-memory distinct with DB SELECT DISTINCT
in the 4 backfill discovery scans + migration 0243 (7 additive
partial indexes matching each predicate). Integration test asserts identical
discovered set against real Postgres.
- ChatBubble wrapped in React.memo with a comparator (onRegenerate compared by
presence — the thread rebuilds it each render), so only the streaming bubble
re-renders per token.
- doctor-report mood read narrowed to {score, tags} (the only fields the
collector uses); the 730d main measurement read left as-is — a blind bound
would corrupt clinical stats (documented deferred follow-up).
Migration 0243 additive-only.
Three CONFIRMED findings from the campaign's feature-wiring audit + structural
verification:
- Sleep rendered nothing in the doctor-report PDF: the sleep section is
default-on and the data layer reconstructs per-night SLEEP_DURATION rows, but
DOCTOR_REPORT_VITAL_TYPES omitted SLEEP_DURATION, so the vitals table showed
nothing (FHIR already emitted it). Added it to the allowlist with a
minutes-to-hours conversion; corrected the stale 'intentionally excluded'
comment.
- Glucose had no doctor-report section toggle — it rendered whenever the global
glucose module was on, with no report-level privacy control. The toggle
actually already existed in the UI (with i18n) but was dead because the prefs
fold dropped the key. Wired it end to end (glucose added to the prefs Zod
schema + defaults, default-on to preserve behaviour; gated the render loop +
the query). Also fixed a grouped-vs-flat prefs mis-detection: 'glucose' in
GROUPED_SECTION_KEYS would have flagged flat blobs (incl. EMPTY prefs) as
grouped and defaulted them all on. No migration (prefs is a JSON blob).
- environment fed the coach with no exclusion: MODULE_EXCLUDED_SOURCES omitted
it, so module-off still leaked audio_env/audio_headphone/audio_event/daylight/
skin_temp to the snapshot. Added, scoped exactly to the environment cluster's
five sources.
Regression test per fix.
From the campaign's dead-code + code-health audits (grep-verified; the
custom-metrics query-key finding was verified-false and excluded):
- sleep-overview empty-state CTA linked to /settings/devices, not a valid
settings slug (dynamicParams=false → 404). Repointed to
/settings/integrations (where every device OAuth callback already lands).
- six live, iOS-consumed routes were absent from docs/api/openapi.yaml —
auth/check-user, insights/cards, integrations/healthkit (GET+PATCH),
measurement-categories, medications/intake/bulk, mood-entries/bulk —
registered in the Zod OpenAPI registry mirroring each handler's real shape.
- mood/labs/medications module pages lacked the redirect-when-off self-guard
their siblings have; added, mirroring cycle/achievements exactly.
No migration. Verifier REJECTED the custom-metrics bare-key finding (they are
invalidateQueries prefix filters, not colliding useQuery keys).
Verified UI-consistency findings from the campaign's design audit, each
pixel-faithful except the intended correction:
- insights hero band: off-scale py-5/gap-5 → the standard scale; H1
font-semibold → font-bold; sm:text-[28px] → sm:text-3xl.
- medication-compliance-chart + recent-achievements-card: hand-rolled
muted h-4 icon + text-sm headers → the standard TileHeader primitive so
they match sibling tiles.
- achievements stat tiles + clinician-view shells: p-5 → p-4 md:p-6.
- two text-primary header glyphs (research-mode dialog, enroll-mfa) →
text-foreground (never accent on a header glyph); the deliberate
badge-boxed icons left untouched.
- SheetHeader now reserves the trailing close-X gutter like DialogHeader,
closing the latent overlap class for future raw-Sheet consumers.
- five raw Dialogs on phone-reachable surfaces → ResponsiveSheet.
- new bg-card-scoped healthlog/spacing-scale lint (flags p-5/gap-5 etc.
only on card shells; list-marker insets + half-steps excluded) at error,
with the five offenders it flagged swept.
- dropped the stale CLAUDE.md '18 PascalCase outliers' line (0 remain).
Every data-slot preserved. No migration.
Performance + correctness fixes from the campaign's verified perf-correctness
audit (the two server-query findings — comprehensive-generate + boot-discovery
distinct — are deferred to a careful separate perf release):
- format-locale built a fresh Intl.DateTimeFormat/NumberFormat per call
(main-thread stall across every history list). Routed through the existing
process-wide formatter-cache keyed on locale + options (timeZone + hour-cycle
in the key); output byte-identical.
- measurement-list rendered every row twice (desktop table + mobile cards, one
CSS-hidden), unvirtualized. Now renders one layout via the SSR-safe
useIsMobile(md) hook; row markup + every data-slot unchanged.
- moodDependentKeys omitted dashboardSnapshot(), so a mood write left the
dashboard tile/score stale ~120s (server cache was already correct). Added it.
- register first-user admin was a count-then-create race (two concurrent
first sign-ups could both become ADMIN). Now an advisory lock + in-transaction
re-count makes first-admin atomic; P2002 maps to 409 instead of 500.
Regression test per fix. No migration (0243 still free).
illness.vital.FUNCTIONAL_IMPACT (the recovery-driver track that wins ties
in the illness insights card's driver selection) was referenced but absent
from all six locale bundles, rendering its raw dotted key on a live
surface — the same dynamic-key seam that let the WHO-5/SCI reminder labels
ship untranslated. Added the label in de/en/es/fr/it/pl.
Closed the class structurally: a new enum-driven
dynamic-key-exhaustiveness test imports the source enum/const for each
high-value dynamic key space (illness scan types + functional-impact,
illness type/lifecycle, allergy + family-relationship records enums, the
mental-health instrument slugs — the WHO-5/SCI seam) and asserts every
member's built key resolves non-empty in all six locales, so a missing
translation fails CI instead of reaching a user. Fallback-protected spaces
(Strava raw sport_type) and structurally-unreachable keys are documented
as excluded. Removed one confirmed-orphan key (illness.vital.SKIN_TEMPERATURE,
zero call sites).
The per-metric insight assessments opened on the current value and read as
a data readout, while the overview texts lead with the day's verdict and
land the warm, motivating tone. Diagnosis (campaign insights-copy audit +
verification): identical warm persona/contract fragments, but the
per-metric USER prompt instructed number-first while the overview
instructed verdict-first, and the opener-hint rotation was wired into
briefing/scores/Coach but never the per-metric path (so the base prompt's
'do not open with the number' clause stayed dormant there).
Rewrote the seven per-metric assessment user prompts (generic archetype +
six bespoke: BP/weight/pulse/BMI/mood/compliance + overall) verdict-first
in EN+DE — meaning first, the number right after as support; grounding,
non-diagnostic framing, no-vendor and no-markdown all preserved (only
clause order changed). Wired openerArchetypeHint into metric-status and
the bespoke generators. Factored a shared openingShape contract into
shared-contracts so overview/per-metric/scores can't drift again, with a
coverage test. Lifted the three deterministic first-paint fallbacks to
verdict-led. Prompt-contract regression tests added. No new model calls,
token budget flat.
Four verified HIGH data-integrity fixes, each in-place per-provider (no
shared helper), from the campaign's data-integrity audit + adversarial
verification:
F1 — drain-per-sample-cumulative buildScanWhere was the only one of four
sibling drains missing deletedAt: null; the nightly cumulative fold
re-summed soft-deleted Apple samples and hard-deleted the legacy-steps
audit tombstones. Added the filter (mirrors consolidate-daily-mean,
dense-intraday-retention, consolidate-legacy-steps).
F2 — the additive late-fold merge had no single-flight guard, so two
overlapping runs (deploy overlap, or the admin endpoint racing the cron)
produced a permanent 2x daily total. Added a pg_advisory_xact_lock per
(user, externalId) plus an in-transaction re-read of the live
contributors so the serialised-second fold contributes zero (a bare lock
alone would still re-add its stale scanned sum). Mirrors the documents
quota advisory-lock pattern.
F3 — Withings sleep externalId keyed on segment START while measuredAt +
the natural key use END; a re-scored segment missed the externalId probe,
the sweep tombstoned the old row, and the re-keyed insert collided on the
natural key and wedged. Added the natural-key rescue (re-key in place),
mirroring the same-file measure path.
F4 — Withings measure sync stamped lastSyncedAt + recordSyncSuccess
unconditionally after a warn-only per-row loop; a transient write failure
stranded the reading behind the 10-min overlap forever. Now holds the
watermark and records failure on a hard row error (mirrors
google-health/fitbit), while a benign P2002 still advances.
Regression test per finding (reverting the three source files fails
exactly four tests). Integration suite green against real Postgres.
v1.28.38 removed the purple AI-read glyph from the vault card (it looked
wrong there), but that glyph had been the only visible surface for the
'read by AI' provenance since v1.28.22 — the detail sheet's AI section
shows no status and collapses entirely under auto-read. Add a discreet
muted 'Read by AI' line under the document's uploaded-metadata in the
detail view (data-slot document-detail-ai-read), independent of the AI
section's collapse logic; reinstate documents.ai.statusAiRead in all six
locales. Card glyph stays removed. The e2e that asserted the card marker
now deep-links the document and asserts the detail-view line.
Document vault: card list drops a stray glyph and gives the real filename
its own line (was truncating on the meta row); the detail sheet's close
button aligns correctly — ResponsiveSheet reserved the right gutter for a
close button it wasn't rendering, now conditional on showCloseButton. The
open-in-coach action reads 'Coach fragen'.
Bulk share: a multi-selection in the overview shares several documents in
ONE link (documents-only privacy preserved, owner-scoped, up to 50 per
link; >50 refuses with a toast rather than silently truncating).
Reminders can link to WAIST_CIRCUMFERENCE (additive MeasurementReminderType
enum value, OpenAPI regenerated). The WHO-5 and SCI well-being reminder
labels were absent in all six locales and rendered their raw dotted keys
(the shared 'measurementReminders.types' prefix is what showed as the stray
'Measurement Reminders Types'); added in every language. A new
type-labels-i18n test asserts every dynamic reminder-type key resolves
non-empty in all six bundles — the static coverage guard cannot see the
dynamic key that let this through.
The sessions card toggle's onClick could be dropped when Playwright
clicked before React hydrated the handler, leaving the region hidden for
the whole wait and failing intermittently in CI. Retry the click until
aria-expanded actually flips.
The v1.28.33 rollup + staging fixes were correct but never reached the
reporter's case: the upload dedup keys on the content SHA-256 and the
lookup matched a previously FAILED job too, so re-uploading the same
export.zip returned the dead job's id and replayed its stale
failureReason without ever staging or sending again. The dedup now
excludes failed rows (short-circuits only on queued/unpacking/parsing/
upserting/done); the same bytes retry cleanly. A redundant staged upload
is unlinked on a dedup hit instead of leaking in the staging dir.
reconcileOrphanImportJobs no longer unconditionally fails every
non-terminal ImportJob at boot: it now checks pg-boss job state plus a
progress heartbeat (new import_jobs.updated_at, migration 0242), so a
split or multi-worker deployment cannot kill an import running in
another worker. The missing-staging message names the separate-container
cause explicitly, with a matching note in docs/self-hosting/scaling.md.
Client-rendered times fell back to the hardcoded display zone
(Europe/Berlin) because neither useFormatters() nor the legacy format.ts
helpers ever received users.timezone. A third localStorage mirror
(timezone-mirror.ts, following the time-format/date-format pattern)
carries the validated profile zone from /api/auth/me into both read
paths; the fallback chain is strictly profile → Europe/Berlin — never
the browser zone, so screen, PDF, and export cannot disagree.
makeFormatters validates its zone before Intl (a poison value falls back
instead of throwing inside every render).
Same pass, per the two-document audit: formatUpdatedLabel boundary and
clock now resolve one zone in every state (the undefined-tz browser hole
is closed); @db.Date calendar fields render UTC-pinned so profiles west
of UTC cannot shift a day; dose-history day headings and GLP-1 weekday
labels derive from the display zone; chart day/month labels are
encoding-derived and UTC-pinned (bucket math untouched, byte-identical
for Berlin profiles); the clinician share view renders in the patient's
timezone instead of the container's; Playwright pins Europe/Berlin.
Server day-key semantics, streak anchoring, insight cache keys, and
verbatim wall-clock strings are untouched by construction and pinned by
the new Auckland/New_York/Manila + DST test matrix.
Supplement-style intake written to Apple Health by nutrition apps —
vitamins, minerals, water, caffeine — can now sync as daily totals into a
dedicated NutrientIntakeDay store (migration 0241): composite (user, day,
nutrient) identity, re-post replaces, no soft delete, no rollup coupling.
A code-side catalog carries the 26 nutrient codes with canonical units,
plausibility caps, and EFSA dietary reference values (direction-aware;
chromium cites NIH ODS where EFSA declined a DRV).
Deliberately not a nutrition feature: no diary, no calories, no dashboard
surface. POST /api/nutrients/batch is gated behind the new opt-in
nutrients module (default off — with the module off the server refuses
ingest entirely), rate-limited, idempotency-wrapped, with per-entry
unit-mismatch and out-of-range skips. GET /api/nutrients serves a
catalog-ordered summary; the only visible surface is a read-only card
under Settings → Sources. Coach/correlation access is a later slice; the
iOS reading side is coordinated in the companion-app tracker.
Rollup writes were delete-then-createMany inside a transaction; under READ
COMMITTED a concurrent recompute racing the import's full fold on the same
(user, type, granularity, bucket, source) partition tripped the composite
PK — and the P2002 swallow dropped the loser's freshly computed aggregate,
leaving a stale bucket. persistRollupRows now writes chunked
INSERT … ON CONFLICT DO UPDATE with deterministic (type, source, bucket)
lock ordering inside one transaction: a race can neither fail the import
nor lose an aggregate, and previously-staled buckets self-heal on their
next recompute. The vanished-source delete and in-transaction guarantees
are preserved; the now-unreachable P2002 swallow is removed.
The masked ENOENT is gone with it: long imports outlived the queue's
15-minute expiry, pg-boss redelivered the still-running job, and the
redelivery re-opened the consumed staging file — overwriting the real
terminal state with a raw ENOENT. Import sends now carry retryLimit 0 +
6h expiry (a retry after the staging file is consumed can only mask),
deliveries for terminal ImportJob rows are no-ops, a genuinely missing
staging file reports an honest re-upload message, and the extracted XML
is cleaned up on failure too.
Setup walk (byte-exact redirect URI), client-registration essentials
for Authentik / Keycloak / Authelia / Pocket-ID, the security model
(identity pinned to (issuer, sub) after a one-time verified-email link;
SSO sessions never satisfy step-up; native-2FA accounts still get their
second-factor step; MFA policy belongs at the IdP), the OIDC_ONLY
consequences incl. the iOS lockout and the break-glass runbook, and a
troubleshooting table for the recurring failure shapes (issuer
trailing slash, unfollowed discovery redirects, clock skew,
IdP-side redirect_uri mismatch).
Token verification: jwtVerify now carries a closed asymmetric algorithm
allowlist (RS256/PS256/ES256/EdDSA — an HMAC alg would let anyone with
the client secret forge identities) and a 60 s clock tolerance for the
usual self-host drift between IdP and app box. The JWKS fetch — jose's
own, outside safeFetch by design — is disciplined instead by pinning
the jwks_uri host to the configured issuer's host byte-exact and
passing the same 15 s timeout safeFetch defaults to. requirePublicHost
stays deliberately unused on this surface: LAN/Tailscale-only IdPs are
a legitimate topology and the issuer is operator config, not user
input.
Discovery: the issuer comparison now tolerates exactly one trailing
slash on either side (a recurring IdP/operator mismatch); token
verification still matches the provider-sent issuer byte-exact.
State cookie: the cookie is set under Path=/api/auth/oidc but was
deleted without a path — RFC 6265 keys cookies by (name, domain,
path), so the deletion never matched and the single-use blob survived
its whole TTL. Every delete now repeats the exact path, via a shared
constant.
Also: the IdP error code recorded in the wide event is whitelisted
against the closed OAuth error-code set (unknown values collapse to
"other"); the status route drops an unreachable try/catch and a
duplicated annotate; the passkey conditional-UI autofill stays unarmed
under OIDC_ONLY (passkey login is a server-enforced dead end there);
env-manifest gains the three optional OIDC_* tuning vars; the env
example notes why the issuer should be HTTPS.
Two second-factor gaps in the OIDC callback:
The session was minted with mfaVerifiedAt stamped to the login instant,
letting a bare SSO login satisfy requireFreshMfa step-up (MFA disable,
encryption-key rotation, encrypted export, account deletion). OIDC is a
delegated factor — whatever the IdP checked, this app did not see a
second factor — so the stamp is now null, the same treatment a
trusted-device login gets.
An account with a confirmed TOTP secret or a registered security key
was logged straight in, silently downgrading its native 2FA to
IdP-only. The callback now mints the same single-use MFA ticket
password login uses and hands it to the login page through a
short-lived, login-page-scoped cookie (the callback ends on a browser
redirect, so the ticket cannot ride an envelope meta like the password
flow). The login page picks it up once, clears it, and renders the
same challenge step; the login completes at /api/auth/mfa/verify with
identical continuation semantics. The cookie is deliberately not
httpOnly — the login page script must read the ticket, exactly the
exposure the password flow already has — and the ticket alone carries
no session authority (single-use, hashed at rest, 5-minute TTL,
attempt-capped).
Direct (factor-less) OIDC logins now also sync the admin-enforced-MFA
enrollment hint cookie, matching password login.
An OIDC login now resolves the account by the durable (issuer, sub)
pair instead of the email claim. First contact stamps the pair — by
auto-provision or a one-time link against an existing account matched
by verified email — and every later login matches on it alone, so an
IdP-side email change refreshes the display email but can never
re-point the login at a different account. An email that matches an
account already bound to a different identity is rejected and audited
(auth.oidc.link_conflict), never silently re-bound.
The email gate tightens with it: linking and provisioning now require
an explicit email_verified: true — an absent claim is a reject
(oidc_email_unverified) rather than a benefit of the doubt, since an
IdP that does not assert verification cannot anchor the account match.
Provisioned accounts get a lowercased email and the same server-default
timezone resolution the register route uses.
Migration 0240 adds the nullable users.oidc_issuer / users.oidc_sub
columns with a composite unique index.
Adds an OpenID Connect relying-party login path (PKCE authorization-code
flow) against a single env-configured identity provider — additive to
password/passkey login by default, with an operator opt-in (OIDC_ONLY,
enforced server-side on every auth route) to disable them entirely.
First-time sign-in auto-provisions or links an account by verified email.
Closes an open redirect in the post-login `next` parameter (both the new
OIDC path and the pre-existing password/passkey flow shared the same
backslash-normalization bypass) by resolving the target through a real
URL parse and comparing origins instead of a prefix check.
Marc-André Bombeck