Storing AI conversation history without turning your database into a swamp

The first schema works. It usually looks like this:

create table messages (
  id bigserial primary key,
  chat_id uuid not null,
  role text not null,
  content text not null,
  created_at timestamptz not null default now()
);

It stores a conversation. It renders a transcript. It survives a demo. Then four requirements arrive, and each one is awkward:

• "Regenerate that answer." Now there are two assistant messages after one user message, and ordering by created_at shows both as if the assistant spoke twice.
• "Edit my question and try again." Either you overwrite the old content and lose what the previous answer was responding to, or you insert a new row and the transcript contains one question answered twice.
• "What did this conversation cost?" There is nowhere to put it. Adding a tokens column conflates a user message, which cost nothing to generate, with an assistant message, which consumed a prompt and produced output.
• "Which model wrote this?" With a fallback chain, the answer varies per message, and during an incident someone will ask.

None of these are exotic. They are normal product requirements. The simple table isn't wrong because it lacks columns. It's wrong because it models a conversation as a flat sequence of messages, when a conversation is a tree of attempts with side effects, and the cost belongs to the attempt rather than the visible text.

This article is about building a conversation store that survives edits, regenerations, retries, audits, and retention requirements without becoming a swamp of duplicated JSON blobs and unindexed text.

What a swamp actually looks like

A database swamp is not just "a lot of rows." It has a recognizable shape:

• every turn stored three times: once in the application log, once in a conversations.messages JSONB column, and once in the provider's hosted state;
• a single row per conversation containing the entire transcript as JSON, updated on every turn, causing write amplification and lock contention;
• message text mixed with analytics, debug payloads, and PII in one untyped metadata column;
• no retention policy, so deleted accounts still have conversations from two years ago;
• no stable ordering, so transcripts occasionally interleave;
• no record of which prompt, model, or tool produced an answer;
• full-text search bolted on later, with no index strategy;
• regeneration implemented by deleting the old message, which destroys the audit trail.

The fix is not a different database. PostgreSQL, MySQL, SQLite, and the hosted options can all store conversations well. The fix is deciding what the atomic units are, what is append-only, what is derived, and what must be deleted.

Model the conversation as a tree

A conversation is a path through a tree of messages. Each message points to its parent. Regeneration adds a sibling. Editing creates a new branch. Switching between branches moves the head pointer.

create table conversations (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references accounts(id) on delete cascade,
  title text,
  head_message_id bigint,
  next_seq bigint not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table messages (
  id bigserial primary key,
  conversation_id uuid not null references conversations(id) on delete cascade,
  parent_id bigint references messages(id) on delete cascade,
  client_message_id text,
  role text not null check (role in ('system', 'user', 'assistant', 'tool')),
  content text not null,
  parts jsonb,
  token_count integer,
  seq bigint not null,
  created_at timestamptz not null default now()
);

alter table conversations
  add constraint conversations_head_message_fk
  foreign key (head_message_id) references messages(id)
  on delete set null;

The circular reference between conversations.head_message_id and messages.conversation_id is intentional. The conversation knows which leaf is active; the message knows which conversation it belongs to. Add the head foreign key after both tables exist, which is why it lives in an alter table statement.

The important properties:

• Regeneration is a sibling. Insert a second assistant message with the same parent_id and move head_message_id to it.
• Editing is a branch. Insert a new user message with the original message's parent, then generate from there.
• The active path is a walk from the head to the root. That walk produces exactly the ordered message array the model API expects.
• Deleting a branch is a cascade. The self-referential on delete cascade removes descendants without touching other branches.

A flat messages table with a chat_id can be migrated into this model by treating each existing message as a child of the previous one. The migration is mechanical, but it is easier to do before launch than after a few million rows.

Separate the message from the generation

Tokens and cost are properties of a generation attempt, not of a message. One assistant message may have been produced by several attempts: a failed call, a retry, a fallback to another model, and a final success. If the only record is the message, those failed attempts disappear from your cost accounting, even though the provider billed for them.

Keep a generations table:

create table generations (
  id uuid primary key default gen_random_uuid(),
  conversation_id uuid not null references conversations(id) on delete cascade,
  message_id bigint references messages(id) on delete set null,
  request_id text not null,

  requested_model text not null,
  served_model text,
  prompt_version text,

  prompt_tokens integer,
  completion_tokens integer,
  cached_input_tokens integer not null default 0,
  cost_usd numeric(12, 6),

  status text not null check (status in ('streaming', 'complete', 'error', 'cancelled')),
  error_code text,
  ttft_ms integer,
  total_ms integer,

  created_at timestamptz not null default now(),
  finished_at timestamptz,

  unique (conversation_id, request_id)
);

message_id is nullable because a failed or cancelled generation may produce no kept message. Those rows still matter: they explain why the bill is higher than the number of visible answers suggests.

requested_model and served_model are separate because fallbacks are invisible otherwise. A week where the primary model was degraded looks like a week where the cost mysteriously changed, unless you can group by what actually served the request.

cost_usd should be numeric, not float or real. Per-request costs often land in the fifth or sixth decimal place. Summing a million binary floating-point values that cannot represent themselves exactly produces a total that disagrees with the invoice, and nobody can explain the difference.

request_id is the idempotency handle. Generate it once per logical user action, reuse it across retries, and enforce uniqueness per conversation. That prevents a network retry from creating two visible answers or two billing records for one request. It does not replace the provider's idempotency mechanism if one exists; it gives your application a stable identity for the attempt.

Store content parts without turning the row into a document store

Modern messages are not just text. They contain images, file references, tool calls, tool results, and structured output. A single content text column forces you to serialize everything into an opaque string, which makes search and rendering harder.

A reasonable compromise is a text column for the primary textual content and a parts JSONB column for structured pieces:

{
  "parts": [
    {"type": "text", "text": "Summarize the attached invoice."},
    {"type": "image", "artifact_id": "art_01J8Z9K3", "media_type": "image/png"},
    {"type": "tool_call", "tool_call_id": "call_77", "name": "lookup_invoice", "arguments": {"id": "inv_8812"}}
  ]
}

Store large binaries in object storage, not in the database. Put the object key and checksum in parts or in a separate artifacts table. A 4 MB image in a JSONB column will be copied by backups, replicated to read replicas, and loaded every time someone selects the row. That is how a conversation table becomes a storage problem.

If you need to search message text, use a generated tsvector column and a GIN index, or a dedicated search system. Don't run LIKE '%term%' over millions of rows and call it search.

PostgreSQL's JSONB is a good fit for semi-structured parts and metadata, but it is a poor fit for the entire conversation. Updating a large JSONB document rewrites the whole value and can trigger TOAST write amplification. An append-only message table avoids that: each turn is an insert, not an update of a growing blob (PostgreSQL, JSON types).

Rebuild the prompt by walking the tree

The query that makes the tree worth the extra complexity is the path from the active head back to the root:

with recursive thread as (
  select m.id, m.parent_id, m.role, m.content, m.parts, m.created_at, 0 as depth
  from messages m
  where m.id = $1

  union all

  select p.id, p.parent_id, p.role, p.content, p.parts, p.created_at, t.depth + 1
  from messages p
  join thread t on p.id = t.parent_id
)
select id, role, content, parts
from thread
order by depth desc;

The order by depth desc returns the root first, which is the order most chat APIs expect. The recursive step follows parent_id, so it naturally excludes sibling branches that aren't on the active path.

Index messages(parent_id) and messages(conversation_id, id). Without the parent index, every step of the recursion can become a sequential scan, and the cost grows with the size of the whole table rather than the depth of the conversation.

The tree query returns the full path, but you should not send the full path to the model forever. Context assembly is a separate step with a token budget:

def select_context(messages: list[dict], max_tokens: int) -> list[dict]:
    selected = []
    used = 0
    for message in reversed(messages):
        cost = message["token_count"] or estimate_tokens(message["content"])
        if used + cost > max_tokens:
            break
        selected.append(message)
        used += cost
    return list(reversed(selected))

This keeps the most recent messages and drops the oldest. That is the simplest policy, and it is often wrong for long projects where an early decision still matters. A better policy keeps a pinned summary or canonical state at the front, then fills the remaining budget with recent turns. The summary is derived data; the raw messages remain available for audit.

Do not store the assembled prompt as the source of truth. Store the inputs: the active head, the prompt version, the summary version, and the message IDs sent. If you store only the final prompt string, you lose the ability to reconstruct which branch produced it.

Ordering needs a sequence, not a timestamp

created_at is not a reliable ordering key. Two messages can share a timestamp at millisecond precision, clocks can move backward across replicas, and backfilled data can arrive out of order.

The messages table above includes a per-conversation sequence number from the start. If you are migrating a flat table, add it as nullable, backfill it in order, then enforce not null; adding a not null column to a populated table without a default will fail. Either way, the unique indexes are what protect ordering:

create unique index messages_conversation_seq_idx
  on messages (conversation_id, seq);

create unique index messages_client_id_idx
  on messages (conversation_id, client_message_id);

Assign seq from a counter on the conversation row, incremented in the same transaction that inserts the message. A subquery like select coalesce(max(seq), 0) + 1 ... for update looks appealing, but PostgreSQL does not allow FOR UPDATE with an aggregate, so it cannot lock the gap reliably. The counter is simpler and the unique index turns a race into an error instead of a silently interleaved transcript.

For new tables at high write volume, consider UUIDv7 for primary keys instead of random UUIDv4. UUIDv7 embeds a timestamp in the high bits, which preserves B-tree locality for inserts (RFC 9562). That is an optimization, not a correctness fix; the sequence still governs conversation order.

Branching, regeneration, and the UI contract

The database model is only half of branching. The API and UI have to agree on what "current" means.

A workable contract:

• POST /conversations/{id}/messages appends a user message and creates a generation.
• POST /generations/{id}/regenerate creates a sibling assistant message with the same parent and returns the new head.
• PUT /conversations/{id}/head switches the active branch after validating that the target message belongs to the conversation.
• GET /conversations/{id} returns the active path, plus metadata about sibling branches if the UI supports branch switching.

Validate ownership on every operation. A head_message_id that points to another user's message is a data leak, not just a bug. Use a composite foreign key or an explicit check that the message's conversation_id matches the conversation.

Deletion needs a policy too. Deleting a conversation should cascade to messages and generations, but backups and derived analytics may lag. If you need an audit trail that survives user deletion, store a de-identified record separately rather than keeping the raw transcript forever.

Provider-hosted state is not a substitute for your own store

Some providers now offer durable conversation objects. OpenAI's Conversations API persists items with a durable identifier, and previous_response_id chains responses across turns. Responses are stored for 30 days by default, while conversation objects and their items are not subject to that 30-day TTL. Even when using previous_response_id, all previous input tokens in the chain are billed as input tokens (OpenAI, conversation state).

That is convenient, and for a prototype it may be enough. For a product, ask:

• Can users edit and branch conversations in your UI? The provider's object may not model your branch semantics.
• Do you need per-message cost, model, and tool-call attribution? That is your analytics, not theirs.
• What happens when the provider retires a model or changes a retention policy?
• Can you satisfy deletion requests across every copy, including hosted state?
• Do you need the transcript for search, moderation, or support?

A common architecture uses the provider for inference and your database for the canonical record. Send the context you assembled from your store; store the response and its usage metadata when it returns. The provider may cache prefixes for performance, but the system of record stays under your control.

If you do use hosted state, record the provider's conversation or response ID on your generation row. That gives you a link for debugging without making the provider the only copy.

Retention, privacy, and the tables you forget

Conversation history is often the most sensitive text in the database. It can contain names, addresses, health details, credentials pasted by mistake, and proprietary documents. A retention policy is not a nice-to-have.

Decide four things before launch:

1. How long is content retained? A default of 30 to 90 days is common for support transcripts; regulated products may need longer or shorter. Write it down and enforce it with a scheduled job.
2. What is deleted on account deletion? Conversations, messages, generations, artifacts, derived summaries, search indexes, and caches. Test the deletion path, including object storage.
3. What is redacted before storage? If you don't need raw payment card numbers or government IDs, detect and redact them at ingest. Redaction is cheaper than a breach.
4. Who can read it? Row-level security, per-tenant access checks, and audit logs for support access. A metadata JSONB column is not an access control.

Generations are telemetry and should have a shorter retention than the conversation itself. Keep full fidelity for 30 to 90 days, then roll up counts and costs by day and model, and drop the detail. Do not put prompt text in the telemetry table "for debugging" and then give that table a looser retention policy; that is how sensitive data ends up in the place with the weakest controls.

Backups complicate deletion. If a user deletes a conversation and it remains in a weekly backup for a year, your deletion story is incomplete. Either shorten backup retention, support restore-time deletion, or document the exception honestly.

Scaling: partition for the query you actually run

A conversation store has two very different workloads:

• Point reads and appends for an active conversation, usually by conversation ID.
• Analytics and retention scans over time ranges, often by account or date.

Indexes handle the first. Partitioning handles the second.

Range partitioning messages by created_at makes retention cheap: detach or drop an old partition instead of running a million-row delete. The trade-off is that a long conversation spans partitions, and the recursive path query may touch several of them. That is usually acceptable because the path is short and the indexes are local to each partition.

Hash partitioning by conversation_id keeps a conversation in one partition and distributes writes, but it makes time-based retention harder. Choose based on which operation hurts more. If deletion and compliance are the dominant requirement, range by time. If write throughput and conversation locality dominate, hash by conversation.

Whichever you choose, test the query plans with realistic data. A recursive CTE that is fast on 10,000 rows can behave differently at 100 million. Add EXPLAIN (ANALYZE, BUFFERS) to your load tests, and watch for sequential scans on parent_id.

Archive cold conversations to object storage or a cheaper table. Keep the metadata row so the conversation still appears in the user's list, and fetch the content on demand. Do not keep every transcript in the hot OLTP database just because it is convenient.

A write path that doesn't duplicate work

A transaction that appends a user message and creates a generation should be atomic and idempotent:

begin;

with next as (
  update conversations
     set next_seq = next_seq + 1, updated_at = now()
   where id = $1
   returning next_seq
),
inserted as (
  insert into messages
    (conversation_id, parent_id, client_message_id, role, content, token_count, seq)
  select $1, $2, $3, 'user', $4, $5, next_seq
  from next
  on conflict (conversation_id, client_message_id) do nothing
  returning id
)
insert into generations (conversation_id, message_id, request_id, requested_model, status)
select $1, id, $6, $7, 'streaming' from inserted
on conflict (conversation_id, request_id) do nothing
returning id;

commit;

If the message insert conflicts, the request is a retry: fetch the existing message and its generation, and return the stored state instead of starting a second model call. If the generation insert conflicts, the same rule applies at the generation level. The unique index on (conversation_id, seq) is a second guard against concurrent writers; the counter increment may leave gaps when a request loses that race, which is harmless for ordering.

When the model responds, update the generation row and insert the assistant message in one transaction. If the response fails, mark the generation error and leave message_id null. The transcript doesn't show a failed answer, but the cost and error are still accounted for.

Tool calls deserve their own table

If the feature uses tools, the message table is the wrong place to store the full tool lifecycle. A single assistant turn can contain several tool calls, each with arguments, a result, an error, a duration, and a retry. Stuffing all of that into messages.parts makes it hard to query failure rates or join a tool result to the generation that requested it.

A separate table pays for itself quickly:

create table tool_calls (
  id uuid primary key default gen_random_uuid(),
  generation_id uuid not null references generations(id) on delete cascade,
  tool_call_id text not null,
  name text not null,
  arguments jsonb not null,
  result jsonb,
  status text not null check (status in ('requested', 'succeeded', 'failed')),
  error_code text,
  duration_ms integer,
  created_at timestamptz not null default now(),
  unique (generation_id, tool_call_id)
);

Now you can answer operational questions with a query instead of parsing transcripts: which tools fail most often, which arguments cause validation errors, how long a tool takes at p95, and whether a failed tool call was followed by a retry that succeeded. Those are the questions that turn an agent from a black box into an operable system.

Store the tool result in the table if it is small and structured. If it is a large document or binary, store a reference to object storage and keep a hash. Never store credentials or full request headers in arguments or result; tool payloads are a common place for secrets to leak into logs.

Derived data is not the source of truth

Summaries, embeddings, search indexes, and analytics are derived from messages. They will get out of sync eventually. Design for that instead of pretending it won't happen.

• Summaries should record the message range they cover and the prompt version that produced them. When messages are deleted, invalidate the summary rather than leaving it to summarize content that no longer exists.
• Embeddings should record the embedding model and chunking version. Changing the model invalidates the vectors; a mixed index produces unreliable retrieval.
• Search indexes should be rebuildable from the message table. If the index is the only copy of a field, a rebuild cannot recover it.
• Analytics should be append-only aggregates, not updates to message rows. A dashboard query should not take a lock on the conversation the user is actively typing into.

A useful rule: if a derived artifact cannot be rebuilt from raw messages plus versioned code, it is not derived. It is a second source of truth, and it needs its own consistency and retention plan.

For summaries specifically, store them in a conversation_summaries table with from_message_id, to_message_id, summary, prompt_version, and created_at. The prompt builder can then select the latest summary that covers the messages before the recent window, instead of trusting a freeform blob attached to the conversation.

Common anti-patterns and their symptoms

Anti-pattern	Symptom	Better approach
One JSONB blob per conversation	Slow updates, lock contention, huge backups	Append-only message rows
created_at as the only order	Interleaved or duplicated turns	Per-conversation sequence
Regenerating by deleting	Lost audit trail, broken references	Sibling message plus head pointer
Cost on the message	Failed attempts vanish from the bill	Separate generation records
Prompt text in telemetry only	Cannot reproduce a bad answer	Store inputs and versions
No retention job	Data lives forever; deletion requests fail	TTL, partitioning, tested deletion
Raw binaries in the row	Bloated tables, slow reads	Object storage plus artifact IDs
Search via LIKE	Full scans, bad relevance	tsvector or external search
No tenant check on head/branch	Cross-account data exposure	Composite keys and authorization checks

The checklist

Before you ship the conversation store, make sure you can answer these:

• A conversation is a tree with an explicit head, and the active path is reconstructible.
• Regeneration and editing create new messages rather than overwriting history.
• Cost, latency, model, and status live on generation attempts, not on visible messages.
• Prompt assembly is deterministic and records the message IDs, prompt version, and summary version used.
• Ordering uses a sequence, not a timestamp alone.
• Attachments live in object storage; the database stores references and checksums.
• Retention, deletion, and redaction are implemented and tested end to end.
• Access checks are enforced in queries, not just in the UI.
• Partitioning and indexes match the actual read and write patterns.
• The provider's hosted state, if used, is a convenience layer, not the only copy.

A conversation store is not glamorous infrastructure. It is the memory of the product, and memory is what makes an AI feature feel coherent rather than amnesiac. Get the tree, the attempts, and the retention boundaries right, and the database stays a database instead of turning into a swamp.