Persist persona chat history in character workspace and rebuild SQLite as a projection

[whbzju]

Problem

Persona character identity and long-term memory already live in the character-owned workspace, but private-chat messages and reply cursors currently live only in Drift/SQLite (persona_chat_messages and persona_chat_reply_cursors).

This creates an inconsistent durability model:

• restoring or retaining a workspace does not restore the character conversation;
• losing/recreating the app database loses the relationship's visible chat history;
• the character workspace cannot serve as a complete, portable record of the relationship;
• SQLite is acting as both the durable source and the query index, so it cannot be rebuilt from workspace data.

Storing chat in workspace alone does not make local app storage survive an iOS uninstall. Cross-uninstall recovery still requires an iCloud-backed workspace or a .memex backup. This issue is about making character chat part of the workspace's durable data model and making SQLite recoverable.

Desired architecture

Add a canonical conversation store under each private character workspace, for example:

workspace/_<userId>/_System/character_workspaces/<characterId>/Conversation/
  messages.jsonl
  state.json

Responsibilities:

• Workspace conversation files are the durable source of truth.
• SQLite remains the runtime projection for pagination, unread counts, pending-user-message queries, task coordination, and UI observation.
• Messages use stable IDs independent of SQLite auto-increment IDs.
• Conversation state stores durable reply/read boundaries using stable message IDs or conversation sequence numbers.
• Startup reconciliation can rebuild a missing or stale SQLite projection from workspace files.

Message data requirements

Preserve at least:

• stable message ID;
• character ID;
• speaker (user or character);
• content;
• message type (text, emoji, action);
• timestamp;
• origin (conversation, initiative);
• contact/decision episode ID;
• optional fact ID;
• stable ordering/sequence.

Multi-bubble character replies must remain one idempotent contact episode while still rendering as separate bubbles.

Implementation scope

• Add all conversation paths to FileSystemService.
• Add a dedicated character conversation storage/reconciliation service rather than writing files directly from UI or task handlers.
• Thread userId through persona-chat persistence boundaries where it is currently unavailable.
• Persist all message entry paths:
  • user private-chat sends;
  • conversation replies;
  • initiative messages;
  • initial greeting and action messages.
• Make workspace persistence and SQLite projection idempotent across task retries and app restarts.
• Add a one-time migration that exports existing SQLite persona-chat rows into workspace conversation storage.
• Rebuild SQLite from workspace when the database is empty or behind the workspace.
• Reconstruct a safe reply cursor during recovery so historical user messages are not treated as newly pending and answered again.
• Keep migration/reconciliation in the data layer; do not add legacy-data special cases to widgets or ViewModels.
• Ensure backup/restore naturally includes the new conversation files.

Failure handling

The filesystem and SQLite cannot share one transaction. Define and test an explicit recovery protocol:

• stable IDs make repeated writes idempotent;
• a crash between workspace persistence and SQLite projection is repaired on startup;
• malformed or partially written JSONL tails do not discard earlier valid history;
• projection failures do not silently report a message as durably saved unless the workspace record exists.

Acceptance criteria

• Existing persona-chat history is migrated once without duplicates.
• New user and character messages appear in the character workspace immediately.
• Deleting only the SQLite database and reopening the app restores the complete persona chat from workspace files.
• Restored historical messages do not trigger duplicate Agent replies.
• Speak, ThinkLater, StayQuiet, initiative/conversation ownership, multi-bubble episodes, emoji/action rendering, unread state, and pagination continue to behave correctly.
• Retried persistent tasks do not duplicate workspace or SQLite messages.
• Clearing a conversation has defined durable semantics and remains consistent after rebuilding the projection.
• Targeted unit tests and a full-chain recovery integration test cover migration, crash reconciliation, and database rebuild.

Follow-up (not required for phase 1)

Expose the character's own conversation archive through a progressive search/recollection tool. Raw history should not be injected wholesale into every Agent turn; the character should retrieve relevant conversation experiences and continue digesting them into its PKM/Journal.