A database schema defines how data is organized in tables, including columns, relationships, constraints, and indexes. In production, these choices determine how data is queried, kept consistent, and removed throughout its lifecycle. section 4.4.1 section 5.4.3
For session history, separate sessions and messages tables let one user have multiple sessions and each session contain multiple messages. A user ID index supports finding a user's sessions; a message foreign key with ON DELETE CASCADE removes associated messages when a session is deleted. section 4.4.1
Listing 4.9 PostgreSQL schema for the sessions table
CREATE TABLE sessions (
session_id VARCHAR(255) PRIMARY KEY, #A
user_id VARCHAR(255) NOT NULL, #B
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_sessions_user_id
➥ON sessions(user_id); #CThe sessions table defines the session primary key and an index for user-based lookup. section 4.4.1
Listing 4.10 PostgreSQL schema for the messages table
CREATE TABLE messages (
id SERIAL PRIMARY KEY, #A
session_id VARCHAR(255) NOT NULL, #B
role VARCHAR(50) NOT NULL,
content TEXT, #C
tool_calls JSONB, #D
tool_call_id VARCHAR(255),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (session_id) REFERENCES sessions(session_id)
ON DELETE CASCADE #E
);
CREATE INDEX idx_messages_session_id ON messages(session_id);The messages table combines variable-length text and JSONB tool calls with a cascading session relationship. section 4.4.1
For retrieval, a chunks table stores readable text, an embedding, document and index identifiers, and JSONB metadata. The vector column's dimensions must match the embedding model. Changing models requires re-embedding chunks and, if dimensions change, updating the column definition. Indexes on document and index identifiers support lifecycle operations; specialized indexes support metadata and vector searches. section 5.4.3
Listing 5.18 PostgreSQL schema for vector storage with pgvector
CREATE EXTENSION IF NOT EXISTS vector; #A
CREATE TABLE chunks (
chunk_id VARCHAR(255) PRIMARY KEY,
document_id VARCHAR(255) NOT NULL, #B
index_name VARCHAR(255) NOT NULL, #B
chunk_text TEXT NOT NULL,
embedding vector(1536), #C
metadata JSONB DEFAULT '{}', #D
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_chunks_document ON chunks(document_id);
CREATE INDEX idx_chunks_index ON chunks(index_name);
CREATE INDEX idx_chunks_metadata
➥ON chunks USING gin (metadata); #E
CREATE INDEX idx_chunks_embedding ON chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100); #FThe pgvector table definition ties chunk storage to metadata filtering, similarity search, and document lifecycle operations. section 5.4.3
Plan indexes before tables become large. Standard CREATE INDEX blocks writes while it runs; CREATE INDEX CONCURRENTLY avoids write locks but takes longer and increases system load. section 4.4.1
Schema can also mean a contract outside the database. An optional metadata schema validates ingestion keys and values, preventing inconsistent tags such as dept and department from silently undermining filters. A gRPC service schema instead defines methods and data structures and can be compiled into client code. section 5.2.2 section 5.3.4 section 2.4.2





















































