liveBook · Manning

PostgreSQL

As explained in Designing AI Systems

PostgreSQL is an open-source object-relational database management system.

PostgreSQL provides the concrete persistence layer for a session service. A storage implementation translates interface methods into database operations, handles errors, and manages connections while keeping PostgreSQL-specific details behind the service boundary. section 4.4

A session-oriented schema separates conversation metadata from message content. The sessions table anchors ownership and lifecycle data; the messages table stores roles, content, tool-call data, and timestamps, with every message linked to its session. 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

Defines the sessions table with a unique session identifier, user ownership, timestamps, and an index for user lookups. 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);

Defines message persistence with role, content, JSONB tool calls, timestamps, a session foreign key, cascading deletion, and a session index. section 4.4.1

How Designing AI Systems covers it

How deep it goes
Explains table relationships, indexes, and interface-to-database translation rather than broader database internals.
Examples
Uses sessions and messages tables, session identifiers, user-session listings, and returning-conversation lookups.
What it assumes
Assumes a storage interface with five methods and a session service that calls it without database-specific knowledge.
What you can do after
Operate/configure PostgreSQL for session persistence by creating linked sessions and messages tables with lookup indexes.
How it connects
Connects SessionStorage with session services, SQL queries, database schemas, indexes, and foreign keys.

Also explained in

Compare how 13 books explain PostgreSQL →

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.