# messages_v2: Per-Row Message Storage

**Date**: 2026-03-16  
**Status**: Implemented

---

## Problem

The `workspace_messages` table (v1) stores every message for a workspace as a single JSON blob:

```sql
CREATE TABLE workspace_messages (
    workspace_id TEXT PRIMARY KEY,
    data         TEXT NOT NULL   -- full JSON array of all messages
);
```

Every write requires a **read → modify → write** cycle:

1. `GET /messages` — fetch the current array
2. Mutate the array in memory
3. `PUT /messages` — write the whole array back

Any two concurrent writers (e.g. browser tab + CLI, two browser tabs, or a React
`useEffect` race) overwrite each other. The last writer wins, and the other's
messages are silently lost.

### Observed failures

| Symptom | Root cause |
|---------|-----------|
| Sharing an example workspace clears its chat history | `WorkspaceProvider` called `setInitialized(true)` before `fetchSources`/`fetchArtifacts` resolved, triggering a save effect with empty state that won the race against the real data |
| A 3-day-old workspace loses history on reload | Same race: stale React state PUT overwrote a later CLI/browser save |
| CLI interactive session wipes browser history | CLI did one `GetMessages` at session start, then `SaveMessages` (full PUT) after each turn, overwriting anything the browser wrote during the stream |

---

## Design

### New tables

```sql
-- One row per message. Composite PK makes INSERT OR IGNORE idempotent.
CREATE TABLE IF NOT EXISTS messages_v2 (
    id           TEXT NOT NULL,
    workspace_id TEXT NOT NULL,
    role         TEXT NOT NULL,        -- 'user' | 'assistant'
    content      TEXT NOT NULL,
    parts        TEXT,                 -- optional JSON (tool calls, images, …)
    created_at   TEXT NOT NULL,
    PRIMARY KEY (workspace_id, id)
);

CREATE INDEX IF NOT EXISTS idx_messages_v2_ws_time
    ON messages_v2 (workspace_id, created_at);

-- Lightweight counters so status polling never needs COUNT(*).
-- A row existing here means "this workspace has been activated on v2".
CREATE TABLE IF NOT EXISTS messages_v2_meta (
    workspace_id TEXT PRIMARY KEY,
    updated_at   TEXT NOT NULL DEFAULT '',
    msg_count    INTEGER NOT NULL DEFAULT 0
);
```

### Key design decisions

| Decision | Rationale |
|----------|-----------|
| `PRIMARY KEY (workspace_id, id)` | `INSERT OR IGNORE` makes every append idempotent — retrying is safe |
| `messages_v2_meta` row as migration marker | Row present = already migrated; absent = needs migration. No ALTER TABLE needed. |
| `updated_at` / `msg_count` in meta | Status polling reads one row instead of scanning all messages |
| Auto-migrate at startup, not lazily | All API handlers see v2 data immediately; no fallback branches needed |

---

## Startup migration

`_db_migrate_messages_v1_to_v2()` is called once in `lifespan()`, right after
`_db_init()`, before any request is handled:

```python
# backend/investibook/main.py — lifespan()
await asyncio.to_thread(_db_init)
result = await asyncio.to_thread(_db_migrate_messages_v1_to_v2)
if result["migrated"] > 0:
    print(f"[startup] messages v1→v2 migration: {result['migrated']} workspace(s) migrated")
```

Workspaces that already have a `messages_v2_meta` row are **skipped** (idempotent).
Once every workspace is migrated the function becomes a no-op on every subsequent
restart — no performance cost, no code path to maintain.

---

## API

All endpoints read and write v2 exclusively. There is no v1 fallback.

| Method | Path | Behaviour |
|--------|------|-----------|
| `GET` | `/api/workspace/{id}/messages` | Return v2 rows ordered by `created_at` |
| `GET` | `/api/workspace/{id}/messages/status` | Return v2 meta (`updated_at`, `count`) |
| `PUT` | `/api/workspace/{id}/messages` | Atomic replace of all v2 rows (full-array clients) |
| `POST` | `/api/workspace/{id}/messages` | Append one or more messages; idempotent by ID |
| `DELETE` | `/api/workspace/{id}/messages` | Clear all v2 rows for the workspace |

### POST /messages request body

```json
{ "messages": [ { "id": "…", "role": "user", "content": "…", "created_at": "…" } ] }
```

Response: `{ "status": "ok", "appended": <count> }`

---

## Frontend changes (`ChatPanel`)

### Before — reactive full-replace (root cause)

```
setMessages(newState)
    ↓  React useEffect([messages]) fires
    PUT /messages  (entire array)   ← races with any concurrent writer
```

### After — event-driven append

```
User sends message
    → setMessages(prev => [...prev, userMsg])
    → POST /messages [userMsg]          ← saved before streaming starts

Stream completes ('complete' SSE event)
    → POST /messages [assistantMsg]     ← saved once, with final content

clearChat()
    → DELETE /messages
    → POST /messages [welcomeMsg]

issue-ready event (Jira source added)
    → POST /messages [assistantMsg]
```

The reactive `useEffect(() => saveMessagesToAPI(...), [messages])` is removed
entirely. Each logical event saves exactly the messages it produced.

---

## Message persistence contract

`messages_v2` is the canonical source of truth for every chat interaction that
must survive refresh, polling, shared-view reloads, or cross-client sync.
Frontend state may optimistically render a message, but it must not become the
only durable copy of a user-visible historical interaction.

### Principles

| Principle | Contract |
|-----------|----------|
| One visible interaction has one persistence owner | Either the backend writes the user/result messages, or the frontend appends them through `POST /messages`. Do not split a single interaction across hidden local state and DB state. |
| User command and command result are persisted together | If a slash command creates a visible result that should remain in history, persist both the user command message and the result message as one logical pair. |
| Local-only messages are transient by definition | Local-only messages are allowed only for ephemeral UI affordances such as upload progress, temporary connection errors before a backend request is accepted, or in-memory debug views that may disappear on refresh. |
| Reload must never reorder durable context | A refresh or `/messages/stream` update must reconstruct the same relative order from `messages_v2.created_at`; it must not append orphan local results after the fetched history. |
| Merge logic must not hide persistence gaps | Fetch/merge code may keep optimistic state only until canonical history changes; it must not preserve orphan local messages to make durable interactions appear "saved". |
| Read paths do not repair write omissions | Loading messages, polling status, or subscribing to message updates must not synthesize missing durable messages. Missing history is fixed at the write owner. |

### Slash command rules

Local slash commands are still chat interactions when they show durable output
in the message list.

| Command type | Persistence rule |
|--------------|------------------|
| Side-effecting command, for example `/issue` or `/force-continue` | Persist the user command and the resulting status/output message. |
| Workspace status command that should be audit-visible | Persist the user command and result message, or move it out of chat history into a dedicated status panel. |
| Pure transient debug command | Mark it as transient in code and let it disappear on refresh; do not preserve it through local-only merge. |
| Normal AI chat | `/api/chat/stream` owns user message persistence and server-side assistant snapshot/finalization. The frontend only renders optimistic state. |
| Issue preparation card (`issue-ready`) | Persist the generated issue info message because it is part of workspace context. |

### Required checks for new chat interactions

Before adding a new `setMessages(...)` path, answer these questions in code
review:

1. Should this message survive refresh and shared-view reload?
2. Which layer writes it to `messages_v2`?
3. Is the user-visible cause/result pair persisted in the same relative order?
4. What happens if `/messages/stream` reloads history while the local state is present?
5. Is there a test or manual checklist that verifies refresh does not move,
   duplicate, or drop the message?

If the answer to the first question is "yes", a plain `setMessages(...)` without
a matching backend or `appendMessagesToAPI(...)` write is a bug.

---

## CLI changes

### Before

```
GetMessages()          ← full GET (failure = history wipe risk)
appendAndStream()      ← local in-memory append
SaveMessages()         ← full PUT  (races with browser)
```

### After

```
AppendMessages([userMsg])       ← POST before streaming
stream response
AppendMessages([assistantMsg])  ← POST after stream ends
```

No read, no merge, no race.

New client methods in `cli/internal/api/client.go`:

```go
func (c *Client) AppendMessages(workspaceID string, messages []Message) error
func (c *Client) ClearMessages(workspaceID string) error
```

---

## Release plan

### v1 (this release — 2026-03-16)
- `messages_v2` + `messages_v2_meta` tables added to schema
- `_db_migrate_messages_v1_to_v2()` runs automatically at every startup; workspaces already migrated are skipped
- All API handlers use v2 exclusively (no v1 fallback)
- `workspace_messages` (v1) table is **kept** but no longer written or read by any handler
- Manual migration tool available: `python -m investibook.scripts.migrate_messages`
- Admin endpoint available: `POST /api/admin/migrate/messages-v1-to-v2`

### v2 (next release)
- Remove `_db_migrate_messages_v1_to_v2()` and the startup call
- Remove `investibook/scripts/migrate_messages.py`
- Remove `POST /api/admin/migrate/messages-v1-to-v2` admin endpoint
- Drop `workspace_messages` table via a schema migration
- Remove `_db_set_messages`, `_db_get_messages_status` (v1 helpers) from `queries.py`

---

## Files changed (v1)

| File | Change |
|------|--------|
| `backend/investibook/main.py` | Call `_db_migrate_messages_v1_to_v2` in lifespan after `_db_init` |
| `backend/investibook/db/queries.py` | New tables + index in `_db_init()`; updated `_db_delete_workspace`; 7 new query functions + migration function |
| `backend/investibook/models/requests.py` | Added `AppendMessagesPayload` |
| `backend/investibook/api/workspaces.py` | All message routes use v2 only; removed v1 fallback and dual-write |
| `backend/investibook/api/admin.py` | Added migration endpoint (temporary, removed in v2) |
| `backend/investibook/scripts/migrate_messages.py` | CLI migration tool (temporary, removed in v2) |
| `frontend/components/ChatPanel/index.tsx` | Replaced reactive save effect with event-driven `appendMessagesToAPI` / `clearMessagesAPI` |
| `cli/internal/api/client.go` | Added `AppendMessages`, `ClearMessages` |
| `cli/cmd/chat.go` | Rewrote `sendChatMessage`, `runInteractiveChat`; removed `appendAndStream` |