Conversation persistence schema design

Read these first

• Conversation state

What you will know

• Design a schema that stores tool calls and their results, not just text

  A normalized event model must preserve ordering, identifiers, status, and raw payloads so replay does not silently turn a tool interaction into plain prose.

• Round-trip attachments, thinking blocks, and multi-part content through storage and retrieval

  Binary references, provider-specific blocks, and content-part order create compatibility problems when a later model or renderer reads old records.

• Store which prompt version produced a message, and say why that matters

  Without immutable version metadata and deployment linkage, regression analysis cannot distinguish prompt changes from model, tool, or data changes.

• Support branch and regenerate without losing history

  A parent or root plus immutable revisions lets regeneration create an alternate future while preserving auditability and references to prior tool work.

• State a retention policy and where the deletion actually happens

  TTL alone is insufficient when copies persist in backups, object stores, analytics, provider logs, or traces; deletion needs an auditable propagation path.

Chapters

1. 1

   Storing tool calls as message parts

   A tool call is structure rather than prose, and flattening it into an assistant message's text column destroys the identifiers that pair a call with its result, so the rows still render a thread and can no longer rebuild the request that produced it. This chapter works through the wire format the API validates, a message_parts child table carrying ordinal, type, tool_call_id, tool name, status and raw payload, the JSON-blob shortcut that gives you the shape without the constraints, the join the parts table costs on every read, and the products that are genuinely fine without one.

   Read chapter
2. 2

   Content blocks and structural validation

   Before designing tables, inventory what the wire actually carries: a text block, a tool_use block with its identifier, a tool_result block referencing it, a thinking block carrying a provider-issued signature, and image or document references. The API validates that set structurally rather than semantically, which is why an unpaired tool result is rejected and why a modified thinking block fails a signature check. This chapter enumerates the rules, sorts the block kinds into those that must round-trip byte-for-byte and those you may safely drop, and shows why a green replay still is not fidelity.

   Read chapter
3. 3

   Attachments and thinking blocks

   A provider's uploaded-file id is an expiring cache key, not storage: the durable record is your own object-store key with a checksum and a media type, or the eight-month-old PDF is simply gone when compliance needs it. Thinking blocks are the mirror image — drop them at write time and an agent loop that reloads history between a tool call and its follow-up rebuilds a request the provider rejects, or one where the model has lost the plan it formed. This chapter covers the attachment tables, why extracted text is not a backup, and what retaining thinking actually costs.

   Read chapter
4. 4

   Prompt versions and generation records

   An immutable pointer to the exact prompt text that produced a message lets you partition Monday and Tuesday traffic by version and separate a prompt change from a model or retrieval change without trusting the release timeline. A git SHA is not that pointer: it moves on deploys that touched no prompt, and stays still when a model alias is repointed, an index is rebuilt overnight, or a flag is flipped in a dashboard. This chapter builds the content-hashed prompt table and the per-message generation record that replace it, and is precise about what they still cannot prove.

   Read chapter
5. 5

   Branching on regenerate

   Hitting regenerate on a reply that made a pricing tool call should insert a new assistant message sharing the original's parent, leave the original row and its tool parts untouched, and move only the selected-leaf pointer. Modelling the conversation as a parent-linked tree of immutable revisions with one chosen path keeps both alternatives readable, comparable and attributable — at the cost of a recursive read on every conversation load and a new class of question about which branch a fact belongs to.

   Read chapter
6. 6

   Message edits and tool effects

   Edit-and-resend on a flat ordered list pushes you to soft-delete the rows after the edit point, which produces the right-looking UI and erases the alternative — you cannot show the user what they had, and you cannot compare the two outcomes. Worse, some of those turns made tool calls that charged the customer, so the charges survive on a conversation now marked as never having happened. Branching records which branch an effect belongs to; the effect itself belongs in a ledger keyed by tool_call_id, with its own lifecycle and its own reconciliation path.

   Read chapter
7. 7

   Retention and erasure across sinks

   Conversation content lives in Postgres rows, attachment blobs, a nightly warehouse sync, trace spans holding full prompts and completions, and point-in-time backups whose window can outlast the retention you promised. Each sink needs its own deletion path and its own audit record, deletes do not propagate through append-oriented ELT unless you model them, and the schema — not a runbook somebody follows by hand — is where the propagation should be enforceable.

   Read chapter