Storing tool calls as message parts

A conversation persistence schema is the set of tables a chat or agent product uses to write a conversation down and read it back later — at minimum a table of conversations and a table of messages, and, as the rest of this topic argues, several more than that. It exists because the model API remembers nothing between calls: every request carries the whole history, and your service is the thing that owns that history, which makes your database the only surviving record of what was said. Four different readers come to that record wanting different things. The browser renders the thread for a human, the next request in the same conversation reassembles the history and sends it back to the model, an analytics job counts how often a tool failed, and a deletion job answers a legal request about a named customer. A schema that serves the first of those well can fail the other three without anybody noticing for months.

Replay is the reader this chapter turns on, and it is the least forgiving of the four. Replaying a conversation means loading a stored one out of the database, rebuilding from those rows the exact request body that produced it, and sending that body to the model again — to reproduce a customer's complaint, to check that a model upgrade has not changed an answer, or to show a reviewer what the model was actually given before it said something expensive. The other three readers tolerate approximation, since a thread that renders with slightly different whitespace is still the right thread and a dashboard that miscounts by a fraction of a percent is still useful. The API tolerates nothing, because it validates the structure of the body you send it and rejects a body whose pieces do not fit together, so a replay is a direct test of whether your rows kept the structure of the conversation or only its words.

The row that renders and cannot be sent#

The messages table is the obvious one, and it is the one most chat products start with: (id, conversation_id, role, text, created_at), one row per turn, text holding whatever the assistant said. A tool call is the model, mid-turn, asking your service to run a named function with arguments it chose, and to feed the return value back into the same conversation so it can carry on. On a schema with one text column per message there is nowhere to put such a thing, so the backend serialises the call and its result into text alongside the prose, and the stored row for a shipping question comes out like this:

I'll check that for you.

{"tool": "shipping_estimate", "args": {"sku": "TR-4410", "zip": "94110"}}
{"result": {"days": 2, "carrier": "UPS"}}

Good news — that should reach you in about 2 business days.

Everything a human needs in order to understand what happened is in there. Everything the API needs is not. A turn is assembled fresh from storage on every call, and the property worth protecting is that the assembled body comes out byte-identical to the one that ran; this row cannot participate in that, because the structure the API validates was thrown away at write time and no amount of care in the assembly step can invent it back.

Flattening a turn like that is rarely a decision anybody takes, and the shape of how it happens is worth following. A model response arrives as a stream of typed events: a text fragment, then the opening of a tool call, then the arguments as the model produces them, then the end of that block. A handler that owns a single string buffer has one operation available to it, which is to append characters, so every event has to be rendered into characters before it can be stored. That rendering runs one way only. The type of the event, the identifier attached to it and the boundary between one block and the next exist in the event and not in the characters, so they are gone at the moment of the append rather than at some later point where a more careful reader of the code might have caught them. The column being appended to is declared TEXT, which is a place to put characters and makes no promise about anything else.

What the wire actually wants back#

The request that produced that row did not contain a JSON snippet inside a string. It contained three messages with structured content blocks, and the shape matters more than it looks:

[
  {"role": "user", "content": [
    {"type": "text", "text": "When will TR-4410 get to 94110?"}
  ]},
  {"role": "assistant", "content": [
    {"type": "text", "text": "I'll check that for you."},
    {"type": "tool_use",
     "id": "toolu_01A7bK9wq2Vd8sJ1n4Yh2P",
     "name": "shipping_estimate",
     "input": {"sku": "TR-4410", "zip": "94110"}}
  ]},
  {"role": "user", "content": [
    {"type": "tool_result",
     "tool_use_id": "toolu_01A7bK9wq2Vd8sJ1n4Yh2P",
     "content": "{\"days\": 2, \"carrier\": \"UPS\"}"}
  ]}
]

Three details in there survive nowhere in the flat row. The assistant's turn is a list of two blocks in a specific order, with the text before the call rather than after it. The call carries an opaque identifier, toolu_01A7…, issued by the provider. And the result comes back in a message with role user, not assistant, referencing that identifier through tool_use_id.

The identifier is worth following back to where it came from, because that is what settles whether a flattened row can be repaired later. Your code did not choose toolu_01A7…, and the model did not write it into its answer. The provider issues it as it serialises the call, and it is opaque in the strict sense: nothing about the tool's name, the arguments, the conversation or the time it happened determines what that string will be. There is no function from a call to its identifier, so there is nothing on your side to recompute it from once it has not been stored. A result that quotes the identifier back is therefore the only place a stored conversation can carry the link between a call and the answer to it.

That link is the load-bearing part. The API checks it structurally: a tool_result whose tool_use_id does not match a tool_use in the immediately preceding assistant message is a 400, and an assistant message ending in a tool_use that is never answered is also a 400.

The check is cheap, and the reason it is cheap decides how much you can lean on it. The request body is JSON, and the first thing the provider does with it is parse it into a list of messages, each holding a list of typed blocks. Matching a tool_use_id against the identifiers in the message before it is then a walk over that parsed structure. It needs no knowledge of what shipping_estimate does, whether the arguments are sensible, or whether the result is true. Because the walk needs nothing from the model, it runs before any generation, which is why a mispaired body comes back as a rejection rather than as a worse answer. The rule itself follows from what the transcript would otherwise be saying: an assistant turn ending in a call is a turn in which the model asked for a value, and continuing from there without the answer would be asking it to carry on as though it had received something it never got.

So a replay script parsing the stored text has two options and both are wrong. It can mint a fresh identifier for the call and staple the result to it, which produces a request that is structurally valid but is not the request that ran, since the ordering of text and call within the turn was guesswork and the result payload was reformatted by whatever pretty-printer wrote the row. Or it can send the whole thing as text — in which case the model on the other end reads a paragraph describing a tool call rather than a tool interaction it participated in. It never called anything, so it has no reason to stand behind a number it was merely shown, and it hedges.

The hedge has a mechanical account behind it. Roles and block types are markers in the byte sequence the request is turned into, and the continuation a model produces is a function of that sequence. A paragraph of prose describing a call is a different sequence from an assistant block that made the call followed by a result block that answered it. Different sequences have different continuations, and the hedge is that difference arriving. The tempting reading is that the same information is present either way, since a person reading the flat row learns everything the structured version carries. That reading holds for the person and not for the model: a person is extracting facts, a model is extending a string, and those two coincide only when the bytes agree.

What keeps an arrangement like this alive through review is the belief that the structure is still in there and can be parsed back out on the day somebody needs it. That belief is reasonable on its face: the row does contain the call and the result as JSON, and writing a parser for the format above is an afternoon's work. It fails on three counts, and the first is fatal on its own, since the identifier was never written into the text and no parser can recover what is not there. The second is that there is no single format to parse, only whatever each version of the write path happened to emit, edited over the years by people who had no reason to think anything downstream depended on its output. The third is that nothing marks where prose ends and payload begins, so a customer who pastes a JSON object into their question is indistinguishable from a tool call.

There is a second casualty that only shows up later. Because tool_call_id exists nowhere as a column, there is no way to ask the database how many times shipping_estimate was called last week, what fraction of those calls returned an error, or which conversation a given call identifier belongs to when the tool's own service log surfaces it during an outage. Every one of those questions becomes a regular-expression scan over a text column.

One row per message, one row per part#

The alternative is to stop pretending a message is a string. A message is a container with an ordered list of typed parts inside it, so the schema gets a child table:

CREATE TABLE messages (
  id              uuid PRIMARY KEY,
  conversation_id uuid NOT NULL REFERENCES conversations(id),
  role            text NOT NULL CHECK (role IN ('user','assistant','system')),
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE message_parts (
  message_id   uuid     NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
  ordinal      smallint NOT NULL,
  type         text     NOT NULL CHECK (type IN
                 ('text','tool_use','tool_result','thinking','image','document')),
  text         text,
  tool_call_id text,
  tool_name    text,
  status       text CHECK (status IN ('ok','error')),
  payload      jsonb,
  blob_key     text,
  PRIMARY KEY (message_id, ordinal),

  CONSTRAINT tool_use_is_identified CHECK (
    type <> 'tool_use' OR (tool_call_id IS NOT NULL AND tool_name IS NOT NULL)),
  CONSTRAINT tool_result_is_paired CHECK (
    type <> 'tool_result' OR tool_call_id IS NOT NULL)
);

CREATE INDEX message_parts_call_idx
  ON message_parts (tool_call_id) WHERE tool_call_id IS NOT NULL;

The composite primary key on (message_id, ordinal) is doing real work: it makes "two parts at position 2 of the same message" impossible to write, which means the read side never has to break a tie and the order you send is the order you stored. The check constraints make an anonymous tool call impossible to write. The partial index makes tool_call_id a first-class lookup key, so correlating a provider identifier from a support ticket back to a conversation is a single index probe rather than a table scan.

The word carrying the weight in the first two of those sentences is impossible. A CHECK constraint is evaluated by the database on every insert and every update, whatever client issued it, so the guarantee covers the backfill script, the admin console, the second service somebody stood up last quarter and the migration that runs unattended. Validation written in the application covers the code paths that call it, which is a set that shrinks under deadline pressure and cannot be read off the schema by anybody auditing it later. The difference shows up as timing rather than as correctness in the abstract: a constraint turns a malformed conversation into a transaction that fails in front of the engineer who caused it, instead of into a request the provider rejects weeks later in front of a customer.

The definition makes two smaller choices that repay reading rather than skimming. The index is declared WHERE tool_call_id IS NOT NULL, so it holds entries only for the parts that carry an identifier — the calls and the results — and an ordinary text part neither takes up room in it nor pays anything to maintain it on insert. And ordinal is stored rather than inferred because a table is a set of rows with no order of its own: a query that does not sort gets whatever order the plan happened to produce, which stays stable enough in testing to look like a guarantee and is not one. Sorting on a column the primary key already forces to be unique within a message gives a total order with no ties to break.

One thing the schema deliberately does not do is give tool_result a foreign key to the tool_use row it answers. A self-referencing FK on tool_call_id would need that column to be unique across the table, and it is not: the identifier appears twice by design, once on the call and once on the result. You can get the constraint by splitting the two into separate tables, or by adding a partial unique index on tool_call_id WHERE type = 'tool_use' and pointing the result at it, and both are defensible. What you cannot do is skip the question, because "every tool result has a matching call in the preceding assistant message" is the invariant the provider enforces on your behalf at request time, and enforcing it at write time is the difference between a bad row and a bad request.

Two of those columns are worth naming explicitly because they are easy to omit and expensive to add later. status records whether the tool result was a success or an error, which matters because an error result is a legitimate part of the conversation — the model saw the failure and reacted to it, and a replay that quietly converts an error into a success is a different conversation. payload holds the raw JSON exactly as it crossed the wire, unnormalised: the arguments the model produced, including the ones your tool ignored, and the result body before your renderer touched it. When a provider adds a field to its blocks next quarter, payload is what lets a six-month-old row still round-trip; the typed columns are the ones you query on, and the payload is the one you replay from.

The shortcut that gives you the shape without the constraints#

There is a middle path that almost every team reaches for first, which is to keep the single messages row and put a JSON array of parts into the text column, or into a content jsonb column, and be done. It is tempting for good reasons: the migration is one column, the write path is a json.dumps, and the read path hands the array straight to the assembly step, so it looks like the parts model with none of the joins.

What it gives up is everything the constraints were doing. A tool result with no matching call is now perfectly writable, and you find out when the provider rejects the request rather than when the row is inserted. Two parts can claim the same ordinal, or the array can be reordered by a serialiser that does not promise stability. tool_call_id cannot be indexed usefully — a GIN index on the document gets you containment queries with a cost profile nothing like a B-tree probe, and only if every writer agrees on the key name forever. Nothing stops one service writing tool_id while another writes tool_call_id, because there is no column to disagree about.

The indexing sentence in that paragraph is the one to take apart, because the reason is not that documents are slow to read. A B-tree index keeps a sorted copy of one value per row and finds rows by comparing a search key against that copy, which requires the value to be a single declared expression the database can evaluate at the moment the row is written. A column holding a document offers no such position. The identifier may sit at any depth, under any key, or not be present at all, so there is nothing fixed for the index to sort by. A GIN index answers a different question instead: it maps each key and value found anywhere inside the document to the rows containing it, which supports asking whether a document contains a thing rather than looking one up in sorted order, and it is larger and more expensive to maintain because a single row contributes an entry for every key it holds.

The B-tree can be recovered, and knowing how turns this into a trade made deliberately. Postgres will index an expression, so a path such as (content->>'tool_call_id') can be indexed and probed exactly like a column. What an expression index cannot supply is agreement. It names one path, every writer has to keep putting the identifier at that path forever, and a writer that spells the key differently produces rows the index cannot see, with nothing raised anywhere to say so. A column has that agreement written into the schema and checked by the database, and the agreement rather than the speed is what the child table is bought for.

The blob column is not useless — it is a reasonable staging point during a migration, and it is the right answer inside payload where you genuinely want an opaque bag. As the storage model for the whole message it is the worst of both, because it carries the read cost of nothing and the correctness guarantees of nothing, while looking finished.

What the join costs#

The parts table is not free and it is worth being concrete about the bill. Reading a conversation goes from one indexed range scan to a join plus an ORDER BY (created_at, ordinal). On a 40-turn conversation with roughly 110 parts — most turns are a single text part, tool turns are two or three — a warm read on Postgres with a covering index on (message_id, ordinal) runs around 1.2 ms at p50 against roughly 0.4 ms for the flat read, and the gap widens on cold cache because you are now touching two heaps. Row count in the parts table lands near 2.7× the message count for a tool-using agent, which is the number that drives your index size and your vacuum load, not your bytes.

That multiplier is worth deriving rather than accepting, since it is the number every later capacity question gets asked in. One hundred and ten parts spread over forty messages is a little under three parts a message, which is the same 2.7 arrived at from the other direction. It is a ratio of rows and not of bytes, and that is what makes it the figure to hold: an index stores one entry per row whatever the row weighs, and a vacuum has to visit each dead row individually when a conversation is deleted. A conversation that reaches that ratio is one whose turns are mostly tool turns rather than prose, so a product whose assistant mostly writes sentences will sit closer to one part a message and should measure its own traffic before sizing anything from this figure.

The widening on a cold cache has a mechanical cause, and it is the same one in any database. A warm read finds the pages it needs already in memory, so the second lookup costs some pointer chasing on top of the first and very little else. A cold read has to fetch pages from storage, and the parts table is a separate relation with pages of its own, so the read waits on two sets of fetches instead of one. The gap between those two millisecond figures is therefore not a fixed tax. It is small while everything is resident, and it tracks your storage latency once it is not.

For an interactive chat that is noise, since the model call that follows takes two to twenty seconds. It stops being noise in the places where you read history at high fan-out: an evaluation harness replaying 50,000 stored conversations, or an analytics job that walks every conversation nightly. There the extra millisecond and the extra heap are a real line on the bill, and the usual answer is a denormalised read model rather than a retreat to the flat column.

Where the flat column is still the right answer#

The honest case for the flat column exists and is worth stating, because a team that adopts the parts table without needing it has bought a join with no expressiveness in return. A product where the assistant only ever emits text — no tools, no file uploads, no thinking blocks retained, no regenerate button, one linear path per conversation — loses nothing by storing that text in a column. Support-macro generators, single-shot rewriters and classification endpoints live here quite happily, and the four readers named at the start of this chapter all get what they need from one row per turn.

What ends that arrangement is not a code review, it is a feature. The first tool call, the first attachment, or the first branching write forces the migration, and each of them forces it for the same reason: the message stops being one thing and becomes several things in an order that matters. Knowing that gives you a cheap decision rule. If the roadmap has any of those three on it within the year, build the parts table now, because migrating a live conversation store means backfilling parsed structure out of prose, and prose is exactly what will not parse.

The rule has one awkward edge, which is a product that has tools but never replays. An internal tool that logs its own calls elsewhere and never rebuilds a request can flatten with a clear conscience for as long as that stays true, and the risk is that it stops being true quietly — the first time an evaluation harness is pointed at production history, or the first time a regulator asks what the assistant was told. Neither of those arrives with notice, and both arrive at the point where the stored prose is already years deep.

Stored conversations are only as replayable as the structure kept at write time, and prose keeps none of it. A tool call has an identifier, an argument object, a result that quotes the identifier back, and a position within its turn, and every one of those is a column somebody either wrote or did not. The parts table costs a join on every conversation read and roughly 2.7 rows per message; what it buys is that the invariant the API enforces at request time is also enforced at insert time, which converts a class of failure that surfaces six weeks later in front of a customer into a constraint violation in a transaction that never committed.

Having the table is only half of it. The columns are typed, but nothing so far says which part kinds exist, which of them the provider validates, or which ones you are allowed to normalise on the way in and which have to come back out byte-for-byte. Get that division wrong and the schema is well-shaped and still produces rejected requests.