Summary

• Problem: Two defects combine to silently lose recent chat messages in long conversations (>200 messages)
• Why it matters: Users lose their most recent messages after page refresh or SSE recovery — the opposite of expected chat behavior
• What changed: DB query fetches newest N messages (DESC + reverse); stuck-placeholder recovery merges by ID instead of only keeping system messages
• What did not change (scope boundary): The limit=200 default, API max 500, cursor-based pagination (future work), and DB schema are all unchanged

UX Journey

Before

  User                        Archon DB                      UI
  ────                        ─────────                      ──
  has 300 messages
  refreshes page ──────────▶  SELECT ... ORDER BY ASC LIMIT 200
                              returns messages 1-200 (oldest)
  sees old messages ◀──────── renders messages 1-200
  ❌ messages 201-300 GONE

  During SSE reconnect:
  live SSE messages ─────────────────────────────────────────▶ in client state
  stuck-placeholder fires ───▶ rehydrate from DB
  ❌ only system msgs kept ◀── SSE-only assistant msgs DROPPED

After

  User                        Archon DB                      UI
  ────                        ─────────                      ──
  has 300 messages
  refreshes page ──────────▶  SELECT ... ORDER BY DESC LIMIT 200
                              returns messages 101-300 (newest)
                              .reverse() → chronological
  sees recent messages ◀───── renders messages 101-300
  ✅ most recent history shown

  During SSE reconnect:
  live SSE messages ─────────────────────────────────────────▶ in client state
  stuck-placeholder fires ───▶ rehydrate from DB
  ✅ merge by ID ◀───────────── client-only msgs PRESERVED

Architecture Diagram

Before

  ChatInterface.tsx                  messages.ts (DB)
  ┌──────────────────┐               ┌──────────────────┐
  │ setMessages(prev) │               │ listMessages()   │
  │  filter system    │◀──────────────│ ORDER BY ASC     │
  │  msgs only        │  hydrated     │ LIMIT 200        │
  └──────────────────┘               └──────────────────┘

After

  ChatInterface.tsx [~]              messages.ts (DB) [~]
  ┌──────────────────┐               ┌──────────────────┐
  │ setMessages(prev) │               │ listMessages()   │
  │  merge ALL client │◀──────────────│ ORDER BY DESC    │
  │  msgs by ID       │  hydrated     │ LIMIT 200        │
  └──────────────────┘               │ .reverse()       │
                                     └──────────────────┘

Connection inventory:

Label Snapshot

• Risk: risk: low
• Size: size: S
• Scope: core, web
• Module: core:messages, web:chat

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

Validation Evidence (required)

# All 14 messages.test.ts tests pass (including updated ordering test)
bun run test -- packages/core/src/db/messages.test.ts  # ✅ 14 pass
bun run type-check  # ✅ clean across all packages
# lint-staged (eslint + prettier) passed on commit

• Evidence provided: test results, type-check clean
• If any command is intentionally skipped, explain why: Full bun run validate not run locally; CI covers remaining checks

Security Impact (required)

• New permissions/capabilities? No
• New external network calls? No
• Secrets/tokens handling changed? No
• File system access scope changed? No

Compatibility / Migration

• Backward compatible? Yes — public contract ("returns messages in chronological order") is preserved
• Config/env changes? No
• Database migration needed? No

Human Verification (required)

• Verified scenarios: Messages load newest-first after refresh with >200 messages; SSE-streamed assistant messages survive stuck-placeholder recovery
• Edge cases checked: Empty conversation, exactly 200 messages, system-only messages
• What was not verified: Production database with high message volume

Side Effects / Blast Radius (required)

• Affected subsystems/workflows: listMessages() consumers now get newest-N instead of oldest-N; stuck-placeholder recovery is more permissive
• Potential unintended effects: Any code relying on getting the oldest messages from listMessages() would need updating
• Guardrails/monitoring for early detection: Existing test suite validates chronological ordering contract

Rollback Plan (required)

• Fast rollback command/path: Revert the commit. No schema changes.
• Feature flags or config toggles: None
• Observable failure symptoms: Messages appearing out of order or missing after refresh

Risks and Mitigations

• Risk: Code elsewhere assumes listMessages() returns oldest-N
  • Mitigation: The chronological ordering contract is preserved; only which N messages are returned changed. Searched codebase for callers — all expect "recent messages."