liveBook · Manning

Schema

As explained in Designing AI Systems

A database schema is the formal organization of a database's tables, columns, relationships, and constraints.

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);    #C

The 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);    #F

The 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

How Designing AI Systems covers it

How deep it goes
Explains table definitions, column types, foreign-key deletion, vector dimensions, and indexing tradeoffs.
Examples
Uses session and message tables, a vector-backed chunks table, and inconsistent department tags as concrete examples.
What you can do after
Evaluate schema designs for session storage and vector retrieval, including their operational tradeoffs.
How it connects
Connects schemas to foreign keys, indexes, JSONB, embeddings, and metadata filtering.

Also explained in

Compare how 54 books explain schema →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Designing AI Systems. The section links point to the parts of the book it draws on.