DATABASE_SCHEMA.md

Database Schema

This document reflects the active SQLAlchemy models in data/models.py.

Engine and Initialization

• ORM: SQLAlchemy 2.x
• Base class: data.db.Base
• Default DB: sqlite:///./notebooklm.db
• Initialization: data.db.init_db()

Relationship Overview

• users 1:N notebooks
• notebooks 1:N sources
• notebooks 1:N chat_threads
• chat_threads 1:N messages
• messages 1:N message_citations
• sources 1:N message_citations
• notebooks 1:N artifacts

Tables

users

Columns:

• id INTEGER PK
• email VARCHAR(255) NOT NULL UNIQUE INDEX
• display_name VARCHAR(255) NULL
• avatar_url VARCHAR(1024) NULL
• created_at DATETIME(timezone=True) NOT NULL

notebooks

Columns:

• id INTEGER PK
• owner_user_id INTEGER NOT NULL FK -> users.id ON DELETE CASCADE INDEX
• title VARCHAR(255) NOT NULL
• created_at DATETIME(timezone=True) NOT NULL
• updated_at DATETIME(timezone=True) NOT NULL

sources

Columns:

• id INTEGER PK
• notebook_id INTEGER NOT NULL FK -> notebooks.id ON DELETE CASCADE INDEX
• type VARCHAR(50) NOT NULL
• title VARCHAR(255) NULL
• original_name VARCHAR(1024) NULL
• url VARCHAR(2048) NULL
• storage_path VARCHAR(1024) NULL
• status VARCHAR(50) NOT NULL
• ingested_at DATETIME(timezone=True) NULL

chat_threads

Columns:

• id INTEGER PK
• notebook_id INTEGER NOT NULL FK -> notebooks.id ON DELETE CASCADE INDEX
• title VARCHAR(255) NULL
• created_at DATETIME(timezone=True) NOT NULL

messages

Columns:

• id INTEGER PK
• thread_id INTEGER NOT NULL FK -> chat_threads.id ON DELETE CASCADE INDEX
• role VARCHAR(20) NOT NULL
• content TEXT NOT NULL
• created_at DATETIME(timezone=True) NOT NULL

message_citations

Columns:

• id INTEGER PK
• message_id INTEGER NOT NULL FK -> messages.id ON DELETE CASCADE INDEX
• source_id INTEGER NOT NULL FK -> sources.id ON DELETE CASCADE INDEX
• chunk_ref VARCHAR(255) NULL
• quote TEXT NULL
• score FLOAT NULL

artifacts

Columns:

• id INTEGER PK
• notebook_id INTEGER NOT NULL FK -> notebooks.id ON DELETE CASCADE INDEX
• type VARCHAR(50) NOT NULL
• title VARCHAR(255) NULL
• status VARCHAR(50) NOT NULL
• file_path VARCHAR(1024) NULL
• metadata JSON NULL (mapped as artifact_metadata)
• content TEXT NULL
• error_message TEXT NULL
• created_at DATETIME(timezone=True) NOT NULL
• generated_at DATETIME(timezone=True) NULL

Notes

• Ownership and isolation are anchored by notebooks.owner_user_id.
• Child records are deleted via ON DELETE CASCADE.
• Schema creation is currently handled with Base.metadata.create_all(...) (no Alembic yet).