Database Design for AI Apps: Embeddings, Prompts, Traces
# AI apps need new database patterns: storing embeddings alongside relational data, versioning prompts, persisting agent traces, and managing conversation history. Here's the schema design guide.
AI applications generate data types that traditional database schemas weren't designed for: high-dimensional vectors, structured conversation histories, long-running agent traces, and time-sensitive prompt versions. Getting the schema right from the start saves painful migrations later.
The Four Data Types in AI Apps
| Type | Characteristics | Storage choice | |------|---------------|----------------| | Embeddings | High-dimensional float32 arrays | pgvector or Qdrant | | Conversations | Append-only, ordered, foreign key to user | Postgres with JSONB | | Agent traces | Nested spans, variable depth | Postgres JSONB or time-series DB | | Prompts | Versioned, content-addressed | Postgres with git-like versioning |
Postgres + pgvector Setup
For apps that want one database to handle everything:
-- Enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Core document store with embeddings
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
content TEXT NOT NULL,
title VARCHAR(500),
source VARCHAR(200) NOT NULL,
source_id VARCHAR(200), -- external document ID
content_hash CHAR(64) NOT NULL, -- SHA-256, for dedup
embedding vector(1536), -- from text-embedding-3-small
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(content_hash) -- prevent duplicate documents
);
-- HNSW index for fast approximate nearest neighbor
CREATE INDEX idx_documents_embedding ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);
-- B-tree on common filter columns
CREATE INDEX idx_documents_source ON documents(source);
CREATE INDEX idx_documents_created ON documents(created_at DESC);
Vector search query:
-- Find 5 most similar documents to a query embedding
SELECT id, content, source, title,
1 - (embedding <=> $1::vector) AS similarity
FROM documents
WHERE source = $2
AND created_at > NOW() - INTERVAL '90 days'
ORDER BY embedding <=> $1::vector
LIMIT 5;
Conversation History Schema
-- Users table (your auth system probably has this)
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
tier VARCHAR(50) DEFAULT 'free',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Conversation sessions
CREATE TABLE conversations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(500),
agent_type VARCHAR(100) NOT NULL, -- "chat", "code-review", "research"
status VARCHAR(50) DEFAULT 'active',
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
last_active_at TIMESTAMPTZ DEFAULT NOW()
);
-- Individual messages
CREATE TABLE messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE,
role VARCHAR(20) NOT NULL CHECK (role IN ('user', 'assistant', 'system', 'tool')),
content TEXT,
content_blocks JSONB, -- for structured content (tool calls, images)
tool_use_id VARCHAR(200), -- for tool-result messages
model VARCHAR(100), -- which model generated this (null for user messages)
tokens_in INTEGER,
tokens_out INTEGER,
cost_usd NUMERIC(10, 8),
latency_ms INTEGER,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Optimized for fetching conversation history
CREATE INDEX idx_messages_conversation ON messages(conversation_id, created_at ASC);
CREATE INDEX idx_messages_conversation_id ON messages(conversation_id) WHERE role = 'user';
Agent Trace Schema
-- One trace per agent run
CREATE TABLE agent_traces (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
conversation_id UUID REFERENCES conversations(id),
user_id UUID REFERENCES users(id),
agent_type VARCHAR(100) NOT NULL,
goal TEXT NOT NULL,
status VARCHAR(50) DEFAULT 'running',
total_tokens_in INTEGER DEFAULT 0,
total_tokens_out INTEGER DEFAULT 0,
total_cost_usd NUMERIC(10, 8) DEFAULT 0,
result TEXT,
error TEXT,
started_at TIMESTAMPTZ DEFAULT NOW(),
completed_at TIMESTAMPTZ
);
-- Individual spans within a trace
CREATE TABLE agent_spans (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trace_id UUID REFERENCES agent_traces(id) ON DELETE CASCADE,
parent_id UUID REFERENCES agent_spans(id), -- for nested spans
span_type VARCHAR(50) NOT NULL, -- "llm_call", "tool_call", "retrieval"
name VARCHAR(200) NOT NULL,
input JSONB,
output JSONB,
error TEXT,
tokens_in INTEGER,
tokens_out INTEGER,
cost_usd NUMERIC(10, 8),
model VARCHAR(100),
started_at TIMESTAMPTZ NOT NULL,
duration_ms INTEGER
);
-- Cost analysis query
SELECT
agent_type,
DATE_TRUNC('day', started_at) as day,
COUNT(*) as runs,
SUM(total_cost_usd) as daily_cost,
AVG(total_cost_usd) as avg_cost_per_run,
SUM(total_cost_usd) / NULLIF(COUNT(*) FILTER (WHERE status = 'completed'), 0) as cost_per_success
FROM agent_traces
WHERE started_at > NOW() - INTERVAL '30 days'
GROUP BY agent_type, day
ORDER BY day DESC, daily_cost DESC;
Prompt Version Schema
CREATE TABLE prompt_versions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(200) NOT NULL,
version VARCHAR(50) NOT NULL,
content TEXT NOT NULL,
content_hash CHAR(64) NOT NULL, -- SHA-256 of content
model VARCHAR(100) NOT NULL,
parameters JSONB DEFAULT '{}', -- temperature, maxTokens, etc.
author VARCHAR(200),
status VARCHAR(50) DEFAULT 'draft',
eval_pass_rate NUMERIC(5, 4),
eval_quality_score NUMERIC(5, 4),
eval_run_at TIMESTAMPTZ,
tags TEXT[] DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(name, version)
);
-- Active prompts index
CREATE UNIQUE INDEX idx_prompt_active ON prompt_versions(name)
WHERE status = 'active'; -- only one active version per name
-- Fetch active prompt with caching hint
CREATE OR REPLACE FUNCTION get_active_prompt(prompt_name TEXT)
RETURNS TABLE(id UUID, content TEXT, model VARCHAR, parameters JSONB) AS $$
SELECT id, content, model, parameters
FROM prompt_versions
WHERE name = prompt_name AND status = 'active'
LIMIT 1;
$$ LANGUAGE SQL STABLE;
User Memory Schema
For agents that remember user preferences:
CREATE TABLE user_memories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
type VARCHAR(50) NOT NULL, -- "preference", "fact", "skill", "constraint"
content TEXT NOT NULL,
confidence NUMERIC(4, 3) DEFAULT 1.0, -- 0–1
source TEXT, -- which conversation established this
embedding vector(1536),
access_count INTEGER DEFAULT 0,
last_accessed TIMESTAMPTZ,
expires_at TIMESTAMPTZ, -- null = permanent
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Search user memories by semantic similarity
CREATE INDEX idx_user_memories_embedding ON user_memories
USING hnsw (embedding vector_cosine_ops);
CREATE INDEX idx_user_memories_user ON user_memories(user_id, confidence DESC);
Migration Pattern for Adding AI to Existing Apps
Don't rebuild your schema — extend it:
-- Add embedding to existing content table
ALTER TABLE articles ADD COLUMN embedding vector(1536);
ALTER TABLE articles ADD COLUMN embedding_model VARCHAR(100);
ALTER TABLE articles ADD COLUMN embedded_at TIMESTAMPTZ;
-- Create index after population
-- (Create index CONCURRENTLY to avoid locking in production)
CREATE INDEX CONCURRENTLY idx_articles_embedding
ON articles USING hnsw (embedding vector_cosine_ops);
-- Track which records need re-embedding (when content changes)
ALTER TABLE articles ADD COLUMN needs_reembedding BOOLEAN DEFAULT false;
CREATE OR REPLACE FUNCTION mark_needs_reembedding()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.content != NEW.content THEN
NEW.needs_reembedding := true;
NEW.embedding := NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER articles_content_changed
BEFORE UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION mark_needs_reembedding();
FAQ
Should I use pgvector or a dedicated vector DB? pgvector for: existing Postgres apps, <5M vectors, need for ACID joins with relational data. Dedicated DB (Qdrant) for: >5M vectors, complex filtering, need for horizontal scaling. See Vector Databases Compared.
How do I handle conversation history in LLM context windows? Store full history in the DB, load only recent N turns into the context window. Use Agent Memory Architecture patterns for long-running conversations.
What's the right approach for multi-tenant AI apps?
Row-level security in Postgres: CREATE POLICY user_isolation ON messages USING (user_id = current_setting('app.current_user')::uuid). Every query is automatically scoped to the current user.
How do I delete user data (GDPR right to erasure)?
Use cascading deletes: ON DELETE CASCADE from users to all user-owned tables. Run deletion asynchronously via a job queue — deleting millions of rows synchronously blocks production.
Should I store raw LLM responses or just the text? Store the full response object as JSONB alongside the extracted text. The metadata (usage, stop_reason, model version) is invaluable for debugging and billing. Storage is cheap; losing this data is expensive.