Storing Chat History in Postgres

Chat history looks like a list, so the first schema is a list, and it holds up until the first time somebody edits a message or asks where the money went. Three requirements — branching, edit history and per-generation cost — are cheap to design in and expensive to retrofit.

The obvious schema and where it breaks

-- The schema everybody writes first.
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 works. Then four requirements arrive, and each is awkward against this table in a different way.

• “Regenerate that answer.” Now there are two assistant messages for one user message. Ordering by created_at shows both in sequence, as if the assistant spoke twice.
• “Edit my question and try again.” Either you overwrite content and lose what the existing answer was actually answering, or you insert a new row and the transcript contains a question asked once and 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 produce, with an assistant message, which cost a prompt’s worth of input and an answer’s worth of output.
• “Which model wrote this?” With a fallback chain the answer varies per message, and you will be asked during an incident.

Messages are a tree

The change that fixes the first two problems at once: a message points at its parent instead of relying on time ordering. A conversation is then a path from the root to a leaf, and regeneration is a second child of the same parent rather than a second row in a sequence.

create table chats (
  id           uuid primary key default gen_random_uuid(),
  account_id   uuid not null references accounts(id) on delete cascade,
  title        text,
  -- the leaf the UI is currently showing; makes "resume where I was" one read
  head_id      bigint,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

create table messages (
  id           bigserial primary key,
  chat_id      uuid not null references chats(id) on delete cascade,
  parent_id    bigint references messages(id) on delete cascade,
  role         text not null check (role in ('system','user','assistant','tool')),
  content      text not null,
  -- monotonically increasing within a chat; used only for stable tie-breaking
  seq          bigint not null,
  created_at   timestamptz not null default now()
);

alter table chats
  add constraint chats_head_fk foreign key (head_id) references messages(id)
  on delete set null;

Every operation is now natural. Regenerating means inserting a second assistant message with the same parent_id and moving head_id to it. Switching between two answers means moving head_id and nothing else. Deleting a branch cascades from its root because the self-reference is on delete cascade.

And building the prompt is a walk from the head back to the root, which is exactly the message array the API wants, reversed. That is the property which makes the tree pay for itself: the context you send is a path, and a path is what a tree is good at.

-- The conversation as the model should see it: head back to root.
with recursive thread as (
  select m.*, 0 as depth
    from messages m
   where m.id = $1                                  -- chats.head_id

  union all

  select p.*, t.depth + 1
    from messages p
    join thread t on p.id = t.parent_id
)
select id, role, content
  from thread
 order by depth desc;                               -- root first

Edits without losing what was there

Editing is easy to get wrong because the intuitive implementation — update messages set content = ... — silently rewrites history. The answer below it was generated from the old text, so after the update the stored transcript shows an answer to a question that was never asked. If you ever have to investigate a bad output, that record is lying to you.

-- Edit-as-sibling: one insert and one update, in one transaction.
begin;

insert into messages (chat_id, parent_id, role, content, seq)
select m.chat_id, m.parent_id, m.role, $2,
       (select coalesce(max(seq), 0) + 1 from messages where chat_id = m.chat_id)
  from messages m
 where m.id = $1                                   -- the message being edited
returning id;

update chats set head_id = $3, updated_at = now() where id = $4;

commit;

A deliberate consequence: nothing is destroyed by an edit, so “show me what this looked like before” is a query rather than a restore from backup. The cost is rows you keep, which is the retention question at the end of this page.

Token accounting belongs on the generation

The modelling insight that matters: tokens are a property of the generation, not of the message. One assistant message is produced by one generation, but a generation can fail, be retried, be cancelled, or fall back to a different model — and all of those cost money without producing a message you keep.

create table generations (
  id                uuid primary key default gen_random_uuid(),
  chat_id           uuid not null references chats(id) on delete cascade,
  -- null when the generation produced no kept message: failed, cancelled, or
  -- superseded by a retry. Those rows still cost money.
  message_id        bigint references messages(id) on delete set null,
  account_id        uuid not null references accounts(id),

  requested_model   text not null,   -- what you asked for
  served_model      text,            -- what actually answered, after fallback

  prompt_tokens     integer,
  completion_tokens integer,
  cost_usd          numeric(12, 6),  -- numeric, never float. See the note below.

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

  ttft_ms           integer,         -- time to first token
  total_ms          integer,

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

Splitting requested_model from served_model is what makes a fallback visible after the fact. Without it, a week where the primary provider was degraded looks like a week where costs mysteriously changed. Splitting ttft_ms from total_ms is the same argument for latency: they move for unrelated reasons, and one averaged number hides which one you have.

The three queries you will actually run

-- 1. The sidebar: recent chats with their last activity.
select c.id, c.title, c.updated_at
  from chats c
 where c.account_id = $1
 order by c.updated_at desc
 limit 30;

-- 2. What did this account spend this month, and on what?
select served_model,
       count(*)                                     as calls,
       sum(prompt_tokens)                           as tokens_in,
       sum(completion_tokens)                       as tokens_out,
       sum(cost_usd)                                as usd,
       count(*) filter (where status = 'error')     as errors,
       count(*) filter (where status = 'cancelled') as cancelled
  from generations
 where account_id = $1
   and created_at >= date_trunc('month', now())
 group by served_model
 order by usd desc;

-- 3. The p50 and p95 that a mean would have hidden.
select served_model,
       percentile_cont(0.5)  within group (order by ttft_ms)  as ttft_p50,
       percentile_cont(0.95) within group (order by ttft_ms)  as ttft_p95,
       percentile_cont(0.95) within group (order by total_ms) as total_p95
  from generations
 where status = 'complete'
   and created_at >= now() - interval '7 days'
 group by served_model;

Query two justifies the whole design. It counts errors and cancellations alongside successes, which is only possible because generations exist independently of messages. On a flat schema those rows do not exist at all, and the cost they represent appears only on the provider’s invoice — the worst place to discover it. This is the same shape as cost attribution generally, and the reason retries need their own line.

Indexes and retention

create index on messages (chat_id, seq);
create index on messages (parent_id);
create index on chats (account_id, updated_at desc);
create index on generations (account_id, created_at desc);
create index on generations (chat_id);

-- Partial index: "is anything still running" touches very few rows.
create index on generations (created_at) where status = 'streaming';

The (parent_id) index is what keeps the recursive walk cheap. Without it every step of the thread query is a sequential scan, and the cost grows with the size of the whole table rather than the depth of the conversation.

Two retention rules to decide before launch rather than after. Chat content is personal data and is often the most sensitive text in your database, so a documented retention window and a working deletion path are a legal requirement in several jurisdictions and a good idea everywhere. And generations are append-only telemetry: keep them at full fidelity for 30 to 90 days, then roll them into a daily per-model aggregate and drop the detail. What not to do is log prompt text into the generations table for debugging convenience — that puts the sensitive data in the table with the loosest retention, which is exactly the failure PII in LLM logs is about.