Message Tree

Canonical reference for the chat message-tree model: how a topic's messages are structured, the invariants that hold, and the contract consumers (read paths, the flow canvas) rely on. Schema: src/main/data/db/schemas/message.ts. Service: src/main/data/services/MessageService.ts.

Scope: topic chat messages (message table). Agent-session messages (agent_session_message) are a separate, flat model and are not covered here.

Structure

A topic's messages form a tree stored as an adjacency list — each row points at its parent via parentId. Multi-model responses (one user turn, N assistant replies) are sibling groups: rows that share a parentId and a non-zero siblingsGroupId.

Virtual root

Every topic owns exactly one content-less virtual root: role = 'root', parentId = NULL, data = { parts: [] }. Every real message hangs below it. The first user turn and its resends are ordinary siblings under this shared parent, so "resend the first message" is structurally identical to any other sibling creation — no multiple physical roots.

root            (role='root', parentId=NULL, no content, never rendered)
 ├─ user "v1"  ┐
 ├─ user "v2"  ├─ one siblingsGroup — "resend first message" = a normal sibling
 └─ user "v3"  ┘
       └─ assistant → user → assistant → …

The dedicated role = 'root' makes the row self-identifying: role-filtered content queries (WHERE role = 'system', etc.) exclude it for free — no parentId IS NOT NULL caveat. role = 'root' and parentId IS NULL are equivalent; parentId IS NULL stays the indexed root lookup key.

Invariants

The virtual root is created eagerly, in the same transaction that creates the topic — so every topic has its root from birth. Writers:

• Runtime: MessageService.createRootMessageTx(tx, topicId) — called by TopicService.create, TopicService.duplicate, and TemporaryChatService persist.
• Migration: ChatMigrator builds the same row inline per topic and reparents former v1 physical roots onto it, so migrated topics match freshly created ones.

Message-creation paths never create the root — they read it via getRootMessageIdTx(tx, topicId) (throws if absent; a missing root is a loud bug, never papered over).

Delete semantics

The self-FK (parentId → message.id) is ON DELETE CASCADE. Deleting a node removes its whole subtree in one statement — no leaf-first ordering, and no SET NULL to manufacture a colliding parentId = NULL row. This is why cascade = true, clearTopicMessages, purgeByTopicIdsTx (topic delete), and the topic FK cascade are all single unordered deletes that stay correct. cascade = false reparents children before deleting the node, so the cascade fires on nothing. (A cascade = false delete of a first-turn message reparents its children onto the virtual root — structurally valid; they become first-turn nodes.)

SET NULL was actively wrong under message_topic_root_uniq: it nulls a surviving in-set child's parentId mid-delete, transiently creating a second parentId = NULL row that violates the index (a reachable crash when deleting any multi-model topic). PRAGMA defer_foreign_keys does not help — it defers FK checking, not the action.

Consumer contract

• rootId is the authoritative first-turn signal. getBranchMessages and getTree return rootId: string | null (the topic's virtual-root id) on every page, alongside activeNodeId. A message is a first turn iff message.parentId === rootId — the only reliable check. Do not infer "first turn" from "parent not in the loaded list" (the branch is paginated, the root is never in the response) nor from the v1 askId field (role-coupled, undefined for user messages). When rootId is unknown, treat nothing as a first turn (fail-safe). See #16120.
• getPathRowsToNodeTx walks from a node up to the virtual root and excludes the root — the displayed conversation starts at the first user message.
• getTree finds the virtual root (parentId IS NULL), drops it from the active path, and treats its children as the logical roots. First-turn nodes keep their real parent (the virtual root id) in the response; the virtual root is never returned as a node. Hence TreeNode.parentId and SiblingsGroup.parentId are non-null string.
• Flow canvas (forward reference — the renderer flow-canvas work lives on the feat/chat-page integration branch, not this PR branch): the edge builder will skip edges whose parent isn't a rendered node — the virtual root, which first turns hang off but which is never a node — so first turns still render as graph roots.
• Role-based content queries need no special root handling: the root is role = 'root', so it is excluded by construction.

Related

• Database Patterns, DataApi in Main.