Database

Overview

The AI part of an AI product is usually a few hundred lines. The database schema underneath it is what decides whether the thing still works at ten thousand users.

This lesson is the boring, load-bearing half: how conversations and messages are actually stored, why tool-call logs are worth keeping from day one, how pgvector lets embeddings live on the same row as the text they came from, and why row-level security — not a WHERE clause you remembered to write — is what keeps one user's data away from another.

Theory

The shape is boring, and that is good. users → conversations → messages, each referencing its parent. Almost every chat product is this, and the boring version scales further than people expect.

Log tool calls. Store what was called, with what arguments, and what came back. It is your audit trail when something goes wrong and — more valuable — it becomes the eval set you will wish you had started collecting six months earlier.

pgvector means one database, not two. An embedding column sits on the same row as the text it came from. No second store to keep in sync, no distributed transaction, and a join against your ordinary tables still works.

Migrate additively while things are still changing. Add a nullable column, backfill it, then start reading it. Renaming or dropping in a single step breaks whatever is still running against the old shape, and something always is.

Index for the two queries you actually run. Messages by conversation ordered by time, and vectors by similarity. Everything else is speculative until a slow query proves otherwise.

Visual Diagram

The diagram groups three tables into one database: conversations (owned by a user), messages (owned by a conversation, each tagged with the model and token counts that produced it), and a document_chunks table carrying both content and an embedding column side by side. The point of the picture is that all of this lives in one Postgres instance — there's no separate "AI database" bolted on next to your "real" database until you have a specific, measured reason to split them out.

Examples

• A conversations / messages pair as described above, the backbone of any chat product.
• A document_chunks table with a vector column, used for retrieval-augmented generation once a knowledge base needs to be searchable by meaning, not just keyword.
• A usage_events table logging every model call's token counts and estimated cost per user — the same table Day 10's per-user quota checks read from.
• A tool_calls table (or a tool role on messages with structured content) recording every function a model invoked, its arguments, and its result.

Code

-- migration: 0001_init_conversations.sql
create table conversations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id),
  title text,
  created_at timestamptz not null default now()
);

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

-- Keeps "load this conversation's messages in order" fast as history grows.
create index messages_conversation_id_idx on messages (conversation_id, created_at);

-- migration: 0002_add_embeddings.sql — purely additive, safe to deploy
-- before any application code that populates it exists.
create extension if not exists vector;

create table document_chunks (
  id uuid primary key default gen_random_uuid(),
  content text not null,
  embedding vector(1536), -- must match the embedding model's output dimension
  created_at timestamptz not null default now()
);

-- Approximate nearest-neighbor index — exact search over large tables
-- doesn't stay fast, so this trades a small amount of recall for speed.
create index document_chunks_embedding_idx on document_chunks
  using hnsw (embedding vector_cosine_ops);

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // server-only — never in client code
);

/**
 * Loads the last N messages for a conversation, oldest first, in the shape
 * a model provider expects. This runs on every chat request, so the
 * (conversation_id, created_at) index above is what keeps it fast even
 * once a conversation has hundreds of turns.
 */
export async function loadRecentMessages(conversationId: string, limit = 20) {
  const { data, error } = await supabase
    .from("messages")
    .select("role, content")
    .eq("conversation_id", conversationId)
    .order("created_at", { ascending: false })
    .limit(limit);

  if (error) throw error;
  return data.reverse(); // back to chronological order for the model
}

Real Product Example

Markprompt is a public Supabase customer case study and a clean illustration of pgvector in a shipped product: an AI chatbot platform that lets teams turn their documentation into a natural-language Q&A widget. Markprompt's retrieval pipeline chunks a customer's docs, embeds each chunk, and stores the embeddings directly in Postgres via pgvector rather than a separate vector database — the case study specifically credits having "the full features of Postgres, colocated with the embeddings" (row-level security, transactions, joins against normal relational data) as the reason they chose it over a dedicated vector store. That's the same document_chunks pattern from the Code section above, running at production scale with real customer traffic and GDPR requirements layered on top through Postgres's existing row-level security rather than a bolted-on system.

Common Mistakes

Senior Tips

Exercises

Mini Project

Conversation store. Build the schema this lesson describes, end to end.

1. Write and apply the users → conversations → messages migration from the Code section (Supabase or any local Postgres works).
2. Seed one conversation with five or six messages, alternating user and assistant roles, including plausible model, input_tokens, and output_tokens values on the assistant messages.
3. Implement loadRecentMessages from the Code section and confirm it returns messages in chronological order.
4. Write one additional query: total tokens used, grouped by conversation, ordered highest-cost first.

Resources

• Supabase — Vector columns (pgvector): setting up pgvector, storing embeddings in a Postgres column, and querying by similarity — the basis for the document_chunks table above.
• pgvector: the extension itself — distance metrics, index types (ivfflat, hnsw), and supported vector formats, straight from the source.
• Supabase — Database Migrations: how to write, apply, and roll forward schema changes safely, relevant to the additive-migration pattern described above.