core: sessions written only to the V1 tables are invisible in V2 (404, missing from tabs) #55312

Summary

Sessions whose rows only exist in the V1 session table are invisible in V2: they
are missing from the session/tab list, GET /api/session/<id> returns 404,
opencode session export <id> answers Session not found, and the desktop app
shows "This tab points to a session that no longer exists on this server". The
session data itself (messages and parts) is intact in the V1 tables — it is a
read-path/projection problem, not data loss.

Environment

• opencode version: 2.0.28 (desktop app + its bundled CLI cli/2.0.28-arm64/opencode-cli serve --service); homebrew CLI on the same machine is 2.0.25
• OS: Darwin 25.6.0 (darwin arm64), macOS desktop app
• Terminal: Unavailable: desktop app (no $TERM_PROGRAM); reproduced with the CLI too
• Shell: /bin/zsh
• Install/channel: release/latest (desktop app 2.0.28 + homebrew 2.0.25)
• Active plugins: none

Reproduction

1. On a profile that has been writing sessions with an older version (rows present in
   the V1 session, message, part tables), let some sessions be written only to
   the V1 tables (see Additional Context for the observed boundary).
2. Start opencode 2.0.28 against that same opencode.db.
3. Observe GET /api/session/<id> → 404 for those ids (4 ids requested on app start,
   in the restored tabs), and opencode session list / the tab list omitting them.

Verified in SQLite:

select count(*) from session;                                      -- 89
select count(*) from session_v2;                                   -- 81
select id, title, time_created from session
 where id not in (select id from session_v2);                       -- 9 rows, all invisible
select count(*) from message where session_id = '<one of those>';   -- 451 messages, intact
select count(*) from session_message where session_id = '<same>';   -- 0

Log evidence (server role):

message="Sent HTTP response" role=server http.method=GET http.url=/api/session/ses_ec98b257... http.status=404
message="Sent HTTP response" role=server http.method=GET http.url=/api/session/ses_ec98b257.../message http.status=404
message="Sent HTTP response" role=server http.method=GET http.url=/api/experimental/session/ses_ec98b257.../export http.status=404

Expected Behavior

Sessions that exist in the database should remain visible and openable after an
upgrade, either because the V1 rows are projected into session_v2 (fully), or
because the read path still falls back to the V1 tables. session export should
work for any session that exists in the database.

Actual Behavior

• The 9 sessions appear neither in the session list nor in tabs; the desktop UI shows
  "This tab points to a session that no longer exists on this server" with the session id.
• Every API call for them returns 404 (session, message, inbox, export).
• opencode session export <id> prints Session not found, so the documented
  export/import route cannot be used to rescue them.
• The V1→V2 projection appears to have stopped partway: 80 sessions have rows in both
  tables, but a contiguous range of later sessions (a ~2-day window) has V1 rows only.

Additional Context

• Observed boundary on this machine: the last session projected into session_v2 was
  created 2026-10-11 15:37; the first session existing only in session was created
  2026-10-11 20:06. Everything created up to the migration boundary is dual-present.
• The orphan rows carry version = 1.18.35; the app was updated to 2.0.28 afterwards
  (the first 2.0.28 session was written into session_v2 correctly). So this looks like
  a partial/failed projection for rows written in between.
• For a session present in both tables, the V1 tables turned out to be a superset of
  what the projection stored: 293 V1 messages vs 213 exported, 81 messages never reached
  session_v2. That suggests the projection also drops content, not only whole sessions.
• Workaround used successfully: rebuild the export format from the V1 tables and import
  it (opencode session import <file> --directory <dir>), which restores the sessions
  with their original ids. Notes from doing that, in case they help fix the projection:
  • session import preserves info.id and info.time.created, and it creates
    session_message rows keyed by the ids in the JSON (a collision on
    session_message.id fails with UNIQUE constraint failed: session_message.id,
    surfaced as HTTP 500).
  • info requires projectID; content parts need time.created (at least for
    reasoning/tool); a tool state.error must be structured
    ({"type":"tool.execution","message":"..."}), not the string the V1 data holds.
  • With those applied, 159/212 common messages came out byte-identical to the official
    export of a dual-present session; the only remaining differences were ±1 ms on
    time.completed and cosmetic metadata (snapshot.files, state.attachments).
• Frequency: reproducible and permanent for the affected ids (not intermittent).