Summary

• Problem: For any conversation with more than 200 persisted messages, the Web UI shows only the oldest 200 and silently drops everything newer. Three causes stack: DB query orders ASC + LIMIT, API has no pagination affordance, and the stuck-placeholder recovery in ChatInterface overwrites React state with the server snapshot — losing any client-only or post-snapshot messages.
• Why it matters: Long agent conversations (typical for non-trivial tasks) hit this silently — the user just stops seeing recent messages, with no error, no banner, no "load more" hint. Combined with the SSE noise from git reset --hard origin/<default_branch> on source/ every message — destroys any local state that has diverged from origin for managed clones #1516's chat-tick reset bursts, mid-session recovery could also wipe live state.
• What changed: DB query switched to ORDER BY DESC LIMIT N then reversed to chronological — listMessages now returns the newest N in oldest-first order. Stuck-placeholder hydration in ChatInterface merges by message id instead of replacing — anything in React state that the snapshot doesn't include (system banners, in-flight tool-calls, post-snapshot SSE messages) survives.
• What did not change (scope boundary): The 200/500 server-side limit (followup if pagination is desired), the ChatInterface mount-time hydration path (already had a merge-by-id branch and isn't the source of mid-session loss), getRecentWorkflowResultMessages (different ordering contract — DESC + early termination, intentionally newest-first).

UX Journey

Before

Long conversation (>200 messages):
  user opens conversation ─────▶ getMessages(limit=200)
                                  └─▶ DB: ORDER BY ASC LIMIT 200
                                       returns oldest 200
  UI scroll-to-bottom        ◀─  rendered chronologically
                                  (last visible: msg #200, latest is #283)
  user sees                      "the conversation seems to end at the typo
                                   I asked about three days ago, even though
                                   I was just chatting yesterday"

Mid-session SSE recovery (any conversation):
  agent edits, commits, replies        — accumulating in React state
  brief SSE drop or workflow_status:completed
                                  └─▶ stuck-placeholder branch fires
                                       └─▶ getMessages(limit=200) refetch
                                            └─▶ setMessages(_ => hydrated)
  React state replaced with the (older) DB snapshot.
  Live messages and any tool-call/streaming state vanish silently.

After

Long conversation (>200 messages):
  user opens conversation ─────▶ getMessages(limit=200)
                                  └─▶ DB: ORDER BY DESC LIMIT 200
                                       returns newest 200,
                                       reversed to chronological by listMessages
  UI scroll-to-bottom        ◀─  rendered chronologically
                                  (last visible: msg #283, the latest)

Mid-session SSE recovery:
  brief SSE drop or workflow_status:completed
                                  └─▶ stuck-placeholder branch fires
                                       └─▶ getMessages(limit=200) refetch
                                            └─▶ merge by id:
                                                  hydrated ∪ (client-only state)
                                                  sort by timestamp
  React state preserved — server's view + everything still in flight.

Architecture Diagram

Before

DB (PG/SQLite)
  └─▶ messages.ts:listMessages         ── ORDER BY ASC LIMIT 200 → oldest 200
       └─▶ api.ts /messages route       ── pass-through
            └─▶ web/api.ts:getMessages
                 └─▶ ChatInterface:
                      ├─ mount fetch         ── prev-empty path returns hydrated
                      └─ stuck-placeholder   ── setMessages(_ => hydrated)   [overwrite]

After

DB (PG/SQLite)
  └─▶ messages.ts:listMessages         ── ORDER BY DESC LIMIT 200, reverse(); → newest 200, chronological  [~]
       └─▶ api.ts /messages route       ── pass-through (unchanged)
            └─▶ web/api.ts:getMessages
                 └─▶ ChatInterface:
                      ├─ mount fetch         ── unchanged
                      └─ stuck-placeholder   ── merge-by-id: prev ∪ hydrated, sort by ts   [~]

Connection inventory:

Label Snapshot

• Risk: risk: low — DB query change is order/limit only (no schema, no semantics shift); React change is purely additive (anything in DB still gets through).
• Size: size: S
• Scope: core, web
• Module: core:db.messages, web:chat.ChatInterface

Change Metadata

• Change type: bug
• Primary scope: multi

Linked Issue

• Closes Web UI silently hydrates long conversations to oldest 200 messages — newest messages disappear after refresh or mid-session SSE recovery (DB is OK, UI bug) #1531
• Related git reset --hard origin/<default_branch> on source/ every message — destroys any local state that has diverged from origin for managed clones #1516 (sync-burst SSE events made the mid-session manifestation observable; this PR fixes the symptom independently of git reset --hard origin/<default_branch> on source/ every message — destroys any local state that has diverged from origin for managed clones #1516 but they overlap in user-visible behaviour)

Validation Evidence (required)

bun run type-check                                      # clean across all 10 packages
bun --filter @archon/core test                          # 815 pass / 0 fail (incl. updated listMessages test fixture)
bun --filter @archon/web test                           # 158 pass / 0 fail

Per-commit: lint-staged (eslint --max-warnings 0 + prettier --write) clean on every staged file.

messages.test.ts test renamed and updated to assert DESC + reversal explicitly. No other fixtures needed update — the API and UI contracts (oldest-first as caller-visible order) are unchanged.

Security Impact (required)

• New permissions/capabilities? No.
• New external network calls? No.
• Secrets/tokens handling changed? No.
• File system access scope changed? No.
• DB query change is read-only and bound by an integer limit argument exactly as before.

Compatibility / Migration

• Backward compatible? Yes. Caller-visible contract of listMessages unchanged: returns oldest-first up to limit rows. The change is which up-to-limit rows are returned (newest N instead of oldest N).
• Config/env changes? No.
• Database migration needed? No.
• API contract: GET /api/conversations/:id/messages?limit=N is unchanged in shape — only the row selection rule differs. Existing client code that doesn't pass limit continues to work; for conversations under 200 messages, behaviour is identical.

Human Verification (required)

• Verified scenarios:
  • bun run type-check clean.
  • messages.test.ts updated test passes (DESC ordering, reversed result).
  • Manual code-read of ChatInterface.tsx merge logic — hydratedIds set excludes DB-known ids, clientOnly filter retains the rest, sort by timestamp produces the natural chronological output.
• Edge cases checked:
  • Empty prev (mount-time): clientOnly is empty, merged === hydrated.
  • Empty hydrated (very early in fresh conversation): early return preserved (if (rows.length === 0) return;).
  • Conflicting ids (very unlikely given synthetic msg-{ts} IDs for SSE messages and DB UUIDs for persisted): hydrated wins (in the merged array first, but sort is stable and timestamps decide; the comment in ChatInterface explicitly relies on the msg-{ts} vs DB UUID disjunction, which this PR preserves).
• What was not verified: end-to-end UI test in a >200-message conversation reproducing the original symptom and confirming the latest 200 are now visible. Recommend a quick manual once the PR builds — left as a verification step for the reviewer/maintainer if desired.

Side Effects / Blast Radius (required)

• Affected subsystems: messages.ts:listMessages is called only from one server route (api.ts:/messages) — verified via grep across packages/. The change there is "what subset of rows you get" without changing types or shape. Web ChatInterface change is contained to the stuck-placeholder branch.
• Potential unintended effects:
  • getRecentWorkflowResultMessages (in the same file) is not affected — different query, different ordering contract.
  • Workflow-driven workers using the conversations layer indirectly: they consume messages via different DB helpers; the listMessages change does not alter what they see.
  • Mid-session SSE recovery: previously dropped non-DB state silently — now preserves it. No code that depended on the wholesale-replace behaviour identified.
• Guardrails: existing logging/error paths unchanged.

Rollback Plan (required)

• Fast rollback: git revert the two commits (a498e788 + 5260d6bd). One file each on the DB side, one on the UI side; no schema or persistent state to migrate.
• Feature flags: none.
• Observable failure symptoms: pre-PR symptom — long conversations show only oldest 200; SSE recovery wipes mid-session state.

Risks and Mitigations

• Risk: A consumer somewhere expects listMessages to return oldest N rather than newest N (i.e. depends on the prior bug).
  • Mitigation: only one caller (api.ts:/messages route) consumes the function. The route serves the Web UI's chat-history fetch — newest N is what users would expect. No other consumer searched up via grep.
• Risk: Adding pagination later might require revisiting the API surface; this PR doesn't add ?before=<id>.
  • Mitigation: explicitly out of scope here. Pagination is a separate, larger feature; the current change is the minimum to fix the user-visible symptom (latest messages invisible). Documented in the issue.
• Risk: ChatInterface merge-by-id depends on the invariant that SSE-only messages use synthetic msg-{timestamp} ids that never collide with DB UUIDs. The existing comment at the mount-fetch path explicitly notes this; this PR maintains it.
  • Mitigation: noted in the inline comment of the changed block; any future change that breaks the synthetic-id invariant would also break the existing merge logic, so the failure mode is shared, not new.

🤖 Investigation, write-up, and implementation produced with extensive back-and-forth using Claude — final code, naming choices, and architectural decisions reviewed and accepted by ztech-gthb.

Summary by CodeRabbit

• Bug Fixes
  • Messages now consistently display oldest-first chronological order.
  • Improved recovery for stuck/streaming messages so client-only and server-hydrated messages merge correctly.
  • Fixed visibility issues where conversations exceeding the message limit could show the wrong set of messages.