AI Coach mis-dates workouts: v_sessions has no local-day column, so 'today's session' resolves in UTC

[dannymcc]

Symptom

The AI Coach insists there is no workout session for "today" and reports that the latest row in v_sessions is yesterday's, even when a workout was completed today. Reported by a user 22 Jul 2026 (their session did not appear; coach said the newest session was 21 Jul).

Verified against current main (post workout/health refactor); file:line references below are re-derived from the current tree.

Root cause: v_sessions has no local-day column, and the coach is never told to convert epoch → local

Every other view the coach can read is already keyed by a local day label:

• v_daily(date, …), v_metric(date, …), v_series(date, …), v_hypnogram(date, …), v_insights(…, date, …) — date is a device-local YYYY-MM-DD label. The coach filters WHERE date='YYYY-MM-DD' and gets correct local-day data.

v_sessions is the exception. It is a raw passthrough of the sessions table and exposes only epoch timestamps, no day label:

-- lib/data/db.dart:1171-1175
CREATE VIEW v_sessions AS
SELECT id, start_ts, end_ts, type, status, calories, strain, max_hr,
       duration_min, steps, hrr_bpm, source, zone_min_json
FROM sessions

The coach system prompt (lib/coach/coach_prompt.dart:51-52, mirrored in lib/coach/coach_engine.dart:711) documents these as "timestamps are epoch SECONDS" and injects "Today is " (lib/coach/coach_engine.dart:396, via _today() → the local todayLabel() helper at :377). But it gives the model no instruction to convert a session's epoch to the local calendar day, and there is no local-day column to filter on. So to answer "did I train today?" the model must write something like:

SELECT * FROM v_sessions WHERE date(start_ts, 'unixepoch') = '2026-07-22'

SQLite's date(ts,'unixepoch') evaluates in UTC — the 'localtime' modifier is required for a local day, and the app's own code uses it everywhere it buckets sessions by day (e.g. lib/data/db.dart:2445: strftime('%Y-%m-%d', start_ts, 'unixepoch', 'localtime'); and lib/data/local_repository_impl.dart:1585 — the local _dayLabelOf helper, used at :1536). The coach's surface omits it, so a session lands under the wrong local day whenever the local date differs from the UTC date at the session's timestamp. For a user east of UTC an early-local-day session maps to the previous UTC date → "latest session is yesterday", exactly the reported symptom.

This is the same UTC-vs-local day-label class as #26 (sleep), fixed by routing day labels through the local dayLabelOf()/todayLabel() helper (lib/data/day_label.dart).

The session write path is fine — sessions store absolute epoch and are persisted with status='done' on stop and on confirm/import:

• manual/live stop: lib/state/app_state.dart:3088-3104 (stopWorkout → putSession({… 'status':'done' …}));
• a confirmed auto-detected suggestion: lib/ui/workouts/workouts_screen.dart:507-521 (_logDetectedSession → putSession({… 'status':'done', 'source':'auto' …}), reached from _confirmSuggestion at :200).

So if the row exists, it exists with a correct epoch; the coach just can't reliably ask for it by local day.

Recommended fix (confidence: medium)

Give the coach a local-day column so it never has to convert:

CREATE VIEW v_sessions AS
SELECT id, start_ts, end_ts,
       strftime('%Y-%m-%d', start_ts, 'unixepoch', 'localtime') AS date,
       type, status, calories, strain, max_hr,
       duration_min, steps, hrr_bpm, source, zone_min_json
FROM sessions

and document v_sessions(date, …) in coach_prompt.dart / coach_engine.dart so the model filters WHERE date='YYYY-MM-DD' like every other view. (Alternatively/additionally, instruct the prompt to always append 'localtime' when converting start_ts, but a date column is the consistent, less error-prone fix.)

Confidence medium: the schema/prompt gap is real and deterministic, but the exact SQL is emitted by the LLM, so which query it writes (and thus the off-by-one direction) depends on the model and the user's timezone.

Needs on-device / real-timezone confirmation — and rule out the more likely cause first

Most likely for this reporter: the workout was auto-detected and never confirmed. Detected bouts are opt-in — the engine writes them to the workout_suggestions table (lib/compute/derivation_engine.dart:1575-1592, :3076) and shows a "did you work out?" card; nothing becomes a sessions row until the user taps "Log it" (workouts_screen.dart:200 → :507, comment at :523-524: "We never auto-log"). If RR never confirmed, v_sessions genuinely has no row for today and the coach's "latest is yesterday" is literally correct — this UTC-mis-dating mechanism is then moot, and the real gap is the confirmation UX (shared with the Apple Health symptom in #130). So before treating this as the day-label bug, confirm a sessions row for today actually exists.

• Query SELECT id, source, status, start_ts FROM sessions ORDER BY start_ts DESC LIMIT 5 — is there a row whose start_ts is today (local)? If no row → this is the unconfirmed-suggestion gap, not the day-label bug.
• If a row does exist: capture the actual run_sql the coach emitted for "today's session" — does it use date(start_ts,'unixepoch') without 'localtime'? Plus the reporter's timezone and the session's local start time (to confirm the UTC boundary flips it to "yesterday").

Relationship to the Apple Health symptom (#130)

Two independent code roots when a session row exists: this coach path reads the sessions table via v_sessions and mis-buckets by UTC; #130's HealthKit path is gated on today's day_result derivation. They converge only in the unconfirmed-detected-workout case, where the single shared root is "no sessions row was ever persisted" — see the confirmation-gate note above and in #130.

[dannymcc]

Related: #130 (today's workout not written to Apple Health — independent root cause, same user report).

[dannymcc]

Filed on a stale fork base — correcting against current main. On current code the coach's "today" correctly uses the local todayLabel() helper (coach_engine.dart:377), so the UTC-"today" premise here doesn't hold. The real root cause is #131: auto/detected workouts are never promoted into the sessions table that v_sessions reads (they're only merged at read-time in getSessions() for the Workouts screen), so the coach genuinely sees no row for today. Superseded by #131 — suggest closing this in favour of it.

[abdulsaheel]

alright fixed this one. the issue was exactly what the title says - v_sessions only had raw start_ts/end_ts epoch columns, so when you asked the coach about "today's workout" it had to somehow turn today's local date into a utc epoch range itself, and that's exactly where it went sideways near midnight-ish boundaries.

fix was pretty boring honestly - just added a real date column to the view (device-local, same trick dataHistoryDays() already used elsewhere in the codebase for the same problem), and told the coach in its system prompt + tool schema to filter sessions by that column instead of doing the epoch math on its own.

up in #150, not merged yet but wanted to close this out. will reopen if anything's still off once it's actually in a build.