sic/docs/reliable-history.md

Reliable History

Goal

Guarantee that the chat history is reconstructible, isolated by user, and consistent even if the backend restarts.

Mandatory rules

1. Persist the user message before calling the LLM.
2. Persist the assistant response when the stream finishes.
3. If the LLM fails, record the failure in metadata or as a controlled error message.
4. Do not keep critical conversational state in memory.
5. All session and message queries must filter by session_id AND user_id.
6. Webhooks must be audited even when they fail.

Base tables

CREATE TABLE IF NOT EXISTS chat_sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  title TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS chat_messages (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  role TEXT NOT NULL,
  content TEXT NOT NULL,
  metadata TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY (session_id) REFERENCES chat_sessions(id)
);

CREATE TABLE IF NOT EXISTS webhook_runs (
  id TEXT PRIMARY KEY,
  webhook_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  status TEXT NOT NULL,
  request_payload TEXT,
  response_status INTEGER,
  created_at TEXT NOT NULL
);

Security invariant

WHERE session_id = ?
AND user_id = ?

Without this filter, the query is incorrectly designed.