# SurrealDB Schema Design for Open Notebook: Complete Architecture Guide

> Learn SurrealDB schema design for Open Notebook. This guide details architecture using SurrealDB graph records and Python ObjectModel for efficient data management.

- Repository: [Luis Novo/open-notebook](https://github.com/lfnovo/open-notebook)
- Tags: architecture
- Published: 2026-06-17

---

**Open Notebook stores all domain objects—including notebooks, sources, notes, and chat sessions—as graph records in SurrealDB, using Python `ObjectModel` subclasses to abstract CRUD operations and `reference` edges to manage many-to-many relationships between entities.**

Open Notebook leverages SurrealDB's multi-model capabilities to power its knowledge management system. The schema design centers on graph relationships that connect research artifacts, implemented through a clean Python domain layer in `open_notebook/domain/`. This architecture enables flexible querying across interconnected notebooks while maintaining type safety through the repository pattern in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py).

## Core Tables and Record Types

All domain objects in Open Notebook inherit from the `ObjectModel` base class and map directly to SurrealDB tables. The schema supports content management, AI processing, and podcast generation through distinct record types.

### The ObjectModel Base Class

The foundation of the schema resides in [`open_notebook/domain/base.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/base.py), where the `ObjectModel` class provides generic methods including `get`, `get_all`, `save`, `delete`, and `upsert`. These methods translate Python operations into SurrealQL queries via the repository layer, automatically handling `surrealdb.RecordID` conversion and error logging as implemented in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py) lines 68-82.

### Domain-Specific Tables

The following tables define the core entities in Open Notebook's SurrealDB instance:

| Table | Python Model | File | Key Fields |
|-------|-------------|------|------------|
| `notebook` | `Notebook` | [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py) | `id`, `name`, `description`, `archived`, `created`, `updated` |
| `source` | `Source` | [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py) | `id`, `asset`, `title`, `topics`, `full_text`, `command` |
| `note` | `Note` | [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py) | `id`, `title`, `content`, `created`, `updated` |
| `chat_session` | `ChatSession` | [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py) | `id`, `title`, `messages`, `created`, `updated` |
| `source_embedding` | `SourceEmbedding` | [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py) | `id`, `content` |
| `source_insight` | `SourceInsight` | [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py) | `id`, `insight_type`, `content` |
| `credential` | `Credential` | [`open_notebook/domain/credential.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/credential.py) | `id`, `provider`, `token`, `created`, `updated` |
| `episode` / `speaker_profile` | Podcast models | [`open_notebook/podcasts/models.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/podcasts/models.py) | Podcast-specific fields |

The `source_embedding` table stores individual vector chunks derived from a source's `full_text`, while `source_insight` holds AI-generated analysis pieces attached to specific sources.

## Graph Relationships and Edge Design

Open Notebook models many-to-many relationships using **graph edges** stored in dedicated tables. The edges are unidirectional in storage but support bidirectional queries through SurrealQL traversal.

### The Reference Edge for Notebook-Source Relations

The `reference` edge serves as the backbone relationship connecting sources to notebooks. Stored as a graph edge table, it enables the many-to-many mapping where a single source can belong to multiple notebooks.

Creating a link executes a `RELATE` command as seen in [`api/routers/notebooks.py`](https://github.com/lfnovo/open-notebook/blob/main/api/routers/notebooks.py) lines 260-271:

```python

# Internal implementation from the repository layer

await repo_relate(source_id, "reference", notebook_id, data)

```

Deleting a source removes the edge in lines 299-301. Querying traversals use directional syntax such as `SELECT VALUE out FROM reference WHERE in = $source_id` to find all notebooks for a source, or `SELECT VALUE out FROM reference WHERE out = $notebook_id` to list sources attached to a notebook.

### Artifact and Chat Session Connections

Additional edges support content organization:

- **`artifact`**: Connects `note` records to `notebook` records using queries like `SELECT in AS note FROM artifact WHERE out=$id` in [`domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/domain/notebook.py)
- **`refers_to`**: Links `chat_session` records to notebooks via `SELECT * FROM (SELECT <-chat_session AS chat_session FROM refers_to WHERE out=$id)`

## Repository Layer and SurrealQL Mapping

All database interactions funnel through [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py), which provides low-level wrappers that generate SurrealQL.

### Record Creation and Upserting

The repository exposes several mutation functions:

- **`repo_create(table, data)`**: Generates `INSERT <table> CONTENT $data` for new records
- **`repo_upsert(table, id, data)`**: Generates `UPSERT <id or table> MERGE $data` for idempotent inserts or updates
- **`repo_update(table, id, data)`**: Generates `UPDATE <record_id> MERGE $data` for existing records

### Graph Edge Management

The **`repo_relate(source, relationship, target, data)`** function creates graph edges using the syntax `RELATE <source>-><relationship>-><target> CONTENT $data`. This powers the `add_to_notebook` method in [`domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/domain/notebook.py) line 475, where `source.relate("reference", notebook_id)` establishes connections between entities.

## Practical Implementation Workflow

The following example demonstrates creating entities, persisting them to SurrealDB, and establishing graph relationships:

```python
from open_notebook.domain.notebook import Notebook, Source, Asset

# 1️⃣ Create a notebook

nb = Notebook(name="AI Safety Research", description="Technical reports")
await nb.save()                     # → repo_create("notebook", …)

# 2️⃣ Create a source (file URL is optional; full_text will be filled later)

src = Source(
    title="GPT‑4 Technical Report",
    asset=Asset(url="https://example.com/gpt4.pdf")
)
await src.save()                    # → repo_create("source", …)

# 3️⃣ Link source to notebook (creates the `reference` edge)

await src.add_to_notebook(nb.id)    # → repo_relate(source.id, "reference", nb.id)

# 4️⃣ Fetch all sources for the notebook (uses the `reference` edge)

sources = await nb.get_sources(include_full_text=True)
for s in sources:
    print(s.title, s.full_text[:100])

# 5️⃣ Count exclusive vs. shared sources (used for delete‑preview)

preview = await nb.get_delete_preview()
print(preview)   # {'note_count': 0, 'exclusive_source_count': 1, 'shared_source_count': 0}

```

The `get_sources` method executes edge traversal queries defined in [`domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/domain/notebook.py) lines 33-38, while deletion preview logic calculates exclusive versus shared source counts.

## Summary

- Open Notebook uses **SurrealDB** as a graph database where all entities are records connected by edges.
- The **`reference`** edge table implements many-to-many relationships between notebooks and sources.
- **`ObjectModel`** in [`open_notebook/domain/base.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/base.py) abstracts CRUD operations, delegating to repository functions in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py).
- Key mutation functions include **`repo_create`**, **`repo_upsert`**, and **`repo_relate`** for graph connections.
- Query patterns leverage SurrealQL directional syntax (`<-reference`, `->reference`) to traverse relationships efficiently.

## Frequently Asked Questions

### How does Open Notebook handle many-to-many relationships between notebooks and sources?

Open Notebook uses the **`reference` graph edge** in SurrealDB to connect `source` and `notebook` records. When adding a source to a notebook, the system executes a `RELATE` command via `repo_relate()` to create a `source → reference → notebook` edge. This allows sources to exist in multiple notebooks while supporting efficient traversal queries in both directions.

### What is the purpose of the ObjectModel base class in Open Notebook's SurrealDB schema?

The **`ObjectModel`** class in [`open_notebook/domain/base.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/base.py) provides a generic abstraction over SurrealDB operations. It implements methods like `save()`, `delete()`, and `get_all()` that automatically translate to SurrealQL via the repository layer, handling record ID parsing and error management so domain models remain storage-agnostic.

### How are vector embeddings stored in the Open Notebook SurrealDB schema?

Vector embeddings are stored in the **`source_embedding`** table, with each record representing an individual chunk derived from a source's `full_text`. These records link back to their parent source through the domain model defined in [`open_notebook/domain/notebook.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/domain/notebook.py), enabling semantic search across processed documents.

### Where are the CRUD operations for SurrealDB implemented in Open Notebook?

All low-level SurrealDB operations are encapsulated in **[`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py)**. This file contains functions like `repo_create()`, `repo_query()`, `repo_upsert()`, and `repo_relate()` that generate and execute SurrealQL statements, providing a clean separation between the domain models and the database client.