Infrastructure & DeploymentHow It Works

Database-backed conversation history with Supabase and Claude

In brief

In-memory arrays disappear on page reload. How to persist conversation history to Supabase, load it back on session resume, and prune context intelligently.

8 min read·

Contents

♡Sign in to save

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