Database-backed conversation history with Supabase and Claude

Every Claude integration starts with an in-memory array of messages. This works for demos. It breaks the moment a user refreshes the page, closes a tab, or comes back the next day.

Proper conversation persistence means:

• Messages survive page reloads and deployments
• Users can resume where they left off
• You can inspect conversations for debugging
• You can build features like conversation search, sharing, or branching

Here is the complete implementation with Supabase.

Schema design

Two tables: conversations (the session) and messages (the turns).

-- Conversations table
create table conversations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade,
  title text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Messages table
create table messages (
  id uuid primary key default gen_random_uuid(),
  conversation_id uuid references conversations(id) on delete cascade,
  role text not null check (role in ('user', 'assistant')),
  content text not null,
  tokens_used integer,
  created_at timestamptz default now()
);

-- Indexes for fast lookups
create index on conversations(user_id, updated_at desc);
create index on messages(conversation_id, created_at asc);

-- Row-level security
alter table conversations enable row level security;
alter table messages enable row level security;

create policy "Users see own conversations"
  on conversations for all using (auth.uid() = user_id);

create policy "Users see own messages"
  on messages for all
  using (
    exists (
      select 1 from conversations
      where conversations.id = messages.conversation_id
      and conversations.user_id = auth.uid()
    )
  );

Loading a conversation

// lib/conversations.ts
import { createClient } from '@/lib/supabase/server'

export type Message = {
  id: string
  role: 'user' | 'assistant'
  content: string
  created_at: string
}

export async function getConversation(conversationId: string): Promise<Message[]> {
  const supabase = await createClient()

  const { data, error } = await supabase
    .from('messages')
    .select('id, role, content, created_at')
    .eq('conversation_id', conversationId)
    .order('created_at', { ascending: true })

  if (error) {
    console.error('getConversation error:', error)
    return []
  }

  return data ?? []
}

export async function getUserConversations(userId: string) {
  const supabase = await createClient()

  const { data } = await supabase
    .from('conversations')
    .select('id, title, created_at, updated_at')
    .eq('user_id', userId)
    .order('updated_at', { ascending: false })
    .limit(20)

  return data ?? []
}

Saving messages

export async function saveMessage(
  conversationId: string,
  role: 'user' | 'assistant',
  content: string,
  tokensUsed?: number
) {
  const supabase = await createClient()

  const [_, updateResult] = await Promise.all([
    supabase.from('messages').insert({
      conversation_id: conversationId,
      role,
      content,
      tokens_used: tokensUsed ?? null,
    }),
    supabase
      .from('conversations')
      .update({ updated_at: new Date().toISOString() })
      .eq('id', conversationId),
  ])

  return updateResult
}

export async function createConversation(userId: string, firstMessage: string) {
  const supabase = await createClient()

  // Auto-generate title from first message (truncated)
  const title = firstMessage.length > 60
    ? firstMessage.slice(0, 57) + '...'
    : firstMessage

  const { data } = await supabase
    .from('conversations')
    .insert({ user_id: userId, title })
    .select('id')
    .single()

  return data?.id ?? null
}

Wiring it into your API route

// app/api/chat/route.ts
import { getServerSession } from 'next-auth'
import { authOptions } from '@/lib/auth'
import { getConversation, saveMessage, createConversation } from '@/lib/conversations'
import Anthropic from '@anthropic-ai/sdk'

export async function POST(request: Request) {
  const session = await getServerSession(authOptions)
  if (!session?.user?.id) {
    return Response.json({ error: 'Unauthorized' }, { status: 401 })
  }

  const { userMessage, conversationId: existingId } = await request.json()
  const userId = session.user.id

  // Get or create conversation
  let conversationId = existingId
  if (!conversationId) {
    conversationId = await createConversation(userId, userMessage)
    if (!conversationId) {
      return Response.json({ error: 'Failed to create conversation' }, { status: 500 })
    }
  }

  // Load message history
  const history = await getConversation(conversationId)

  // Build messages array for Claude
  const messages = [
    ...history.map(m => ({ role: m.role as 'user' | 'assistant', content: m.content })),
    { role: 'user' as const, content: userMessage },
  ]

  // Save user message before calling Claude
  await saveMessage(conversationId, 'user', userMessage)

  const anthropic = new Anthropic()
  const response = await anthropic.messages.create({
    model: 'claude-opus-4-5',
    max_tokens: 1024,
    messages,
  })

  const assistantContent = response.content[0].type === 'text'
    ? response.content[0].text
    : ''

  // Save assistant response with token count
  const totalTokens = response.usage.input_tokens + response.usage.output_tokens
  await saveMessage(conversationId, 'assistant', assistantContent, totalTokens)

  return Response.json({
    content: assistantContent,
    conversationId,
  })
}

Context window management

Claude has a finite context window. Long conversations eventually exceed it. Prune old messages when the conversation grows:

const MAX_MESSAGES_IN_CONTEXT = 20

function pruneHistory(history: Message[]): Message[] {
  if (history.length <= MAX_MESSAGES_IN_CONTEXT) return history

  // Always keep the first message (often sets important context)
  // Then keep the most recent N-1 messages
  const first = history[0]
  const recent = history.slice(-(MAX_MESSAGES_IN_CONTEXT - 1))
  return [first, ...recent]
}

// Use in route handler:
const prunedHistory = pruneHistory(history)
const messages = [
  ...prunedHistory.map(m => ({ role: m.role, content: m.content })),
  { role: 'user', content: userMessage },
]

A more sophisticated approach counts tokens instead of message count. The usage field returned by Claude tells you how many tokens were used — track this to build a soft limit.

Loading conversation in the UI

// app/chat/[conversationId]/page.tsx
import { getConversation } from '@/lib/conversations'

export default async function ChatPage({ params }: { params: { conversationId: string } }) {
  const messages = await getConversation(params.conversationId)

  return (
    <div>
      {messages.map(msg => (
        <div key={msg.id} className={`message message--${msg.role}`}>
          {msg.content}
        </div>
      ))}
      {/* ChatInput component here */}
    </div>
  )
}

The URL-based approach (/chat/[conversationId]) means users can bookmark, share, or return to any conversation. It is also debuggable — you can look up the conversation ID in Supabase directly.

What not to do

• Do not store raw HTML in the content column — store markdown and render it on the client
• Do not lazy-load message history — load it server-side before rendering to avoid layout shift
• Do not skip the updated_at update — it is how you sort conversations by recency
• Do not trust the client to pass conversationId without verifying ownership — always check the conversation belongs to the authenticated user

Further reading

• Messages API reference — current Claude model IDs and context window limits
• Prompt caching — how to cache conversation history to cut costs on long persistent conversations
• Token counting — measuring context window usage precisely before you hit limits