How to Add New Domain Models with SurrealDB Relationships in Open-Notebook

To add new domain models with SurrealDB relationships in Open-Notebook, subclass ObjectModel from open_notebook/domain/base.py, declare fields using Pydantic types with relationship helpers like Relation or ChildOf, create a SurrealQL migration file with DEFINE TABLE and DEFINE EDGE statements, and utilize the generic repository in open_notebook/database/repository.py for CRUD operations.

Open-Notebook is an open-source knowledge management platform built on SurrealDB that employs a domain-driven design architecture. Knowing how to add new domain models with SurrealDB relationships allows you to extend the data model while maintaining type safety through Pydantic and leveraging the database's native graph capabilities.

Understand the Domain Model Architecture

The ObjectModel Base Class

Every entity in Open-Notebook inherits from ObjectModel defined in open_notebook/domain/base.py. This base class provides the foundational serialization logic and integration with SurrealDB's record IDs. When you create a new model, you extend this class and define fields using standard Pydantic annotations.

The base class handles the conversion between Python objects and SurrealDB's underlying storage format, including automatic ID generation and timestamp management. It serves as the contract that the generic repository expects when performing database operations.

Relationship Helpers and Edge Definitions

SurrealDB relationships are implemented through specialized type hints rather than traditional ORM foreign keys. Open-Notebook provides helper classes like Relation for many-to-many associations and ChildOf for hierarchical one-to-many structures. These wrappers translate to SurrealDB's DEFINE EDGE statements, creating explicit graph connections between tables.

When you declare a field as Relation["TargetModel"], the system understands this represents a graph edge that requires specific DDL statements in your migration files.

Implementing New Domain Models with SurrealDB Relationships

1. Define the Domain Model Class

Create a new Python file in open_notebook/domain/ and subclass ObjectModel. Define scalar fields with Pydantic types and relationship fields using the appropriate helper classes. The file name should match the entity name in lowercase.

# open_notebook/domain/tag.py

from datetime import datetime
from pydantic import Field
from open_notebook.domain.base import ObjectModel, Relation
from open_notebook.domain.note import Note

class Tag(ObjectModel):
    """A user-defined label attached to notes."""
    name: str = Field(..., description="Human-readable identifier")
    color: str = Field(default="#000000", description="Hex color code")
    created_at: datetime = Field(default_factory=datetime.utcnow)
    
    # Many-to-many relationship to Note

    notes: Relation[Note] = Relation(
        edge_name="tagged_note",
        direction="out",
        target="Note"
    )

2. Create SurrealDB Schema Migrations

SurrealDB requires explicit schema definitions for tables and edges. Create a new file in the migrations directory with a timestamp prefix and .surql extension. This file must define the table, all fields with types, and any edge relationships.

-- open_notebook/database/migrations/2024-09-01-create-tag.surql
DEFINE TABLE tag SCHEMAFULL;
DEFINE FIELD name       ON tag TYPE string ASSERT $value != NONE;
DEFINE FIELD color      ON tag TYPE string DEFAULT "#000000";
DEFINE FIELD created_at ON tag TYPE datetime DEFAULT time::now();

-- Define the graph edge for many-to-many relationship
DEFINE EDGE tagged_note FROM tag TO note;

3. Register with the Migration Runner

The migration system in open_notebook/database/migrate.py executes SurrealQL files during application startup. Add your new migration file to the execution sequence within the run_migrations() asynchronous function to ensure the schema updates apply automatically.

# open_notebook/database/migrate.py (excerpt)

async def run_migrations():
    """Execute all pending schema migrations."""
    await client.query_file("migrations/2024-09-01-create-tag.surql")
    # ... existing migrations

4. Utilize the Generic Repository

The Repository class in open_notebook/database/repository.py provides generic CRUD methods that work with any ObjectModel subclass. Instantiate the repository in your service layer and call methods like create(), get(), update(), and delete() with your model class as the type parameter.

from open_notebook.database.repository import Repository
from open_notebook.domain.tag import Tag

async def create_tag(repo: Repository, name: str):
    tag = Tag(name=name)
    return await repo.create(tag)

async def get_tag_with_notes(repo: Repository, tag_id: str):
    return await repo.get(Tag, tag_id, fetch=["notes"])

5. Expose via API Router

To expose the new entity via REST endpoints, create a router in api/routers/ following the existing FastAPI patterns. Inject the repository dependency and implement standard HTTP methods for CREATE, READ, UPDATE, and DELETE operations.

# api/routers/tag.py

from fastapi import APIRouter, Depends, HTTPException
from open_notebook.database.repository import Repository
from open_notebook.domain.tag import Tag

router = APIRouter(prefix="/tags", tags=["Tags"])

@router.post("/", response_model=Tag)
async def create_tag(tag: Tag, repo: Repository = Depends()):
    return await repo.create(tag)

@router.get("/{tag_id}", response_model=Tag)
async def get_tag(tag_id: str, repo: Repository = Depends()):
    obj = await repo.get(Tag, tag_id)
    if not obj:
        raise HTTPException(status_code=404, detail="Tag not found")
    return obj

Complete Working Example: Tag-to-Note Relationships

Model Implementation Details

The Tag model demonstrates a complete implementation including the inverse relationship. When defining Relation[Note] with direction="out", you indicate that Tag records connect outward to Note records via the tagged_note edge. This directionality matches the SurrealQL DEFINE EDGE tagged_note FROM tag TO note statement.

Migration Execution Order

Place migration files in open_notebook/database/migrations/ with chronological prefixes (e.g., 2024-09-01-, 2024-10-15-). The run_migrations() function in open_notebook/database/migrate.py loads these files in alphabetical order, so date prefixes ensure dependencies exist before creating edges that reference them.

Repository Query Patterns with Graph Traversal

To fetch related data across edges, use the fetch parameter in repository methods. This translates to SurrealDB's FETCH clause, performing graph traversal at the database level rather than application-side joins.

# Fetch a tag with all connected notes and their sources resolved

tag = await repo.get(Tag, tag_id, fetch=["notes", "notes->source"])

Summary

• Inherit from ObjectModel in open_notebook/domain/base.py to leverage existing serialization and validation logic.
• Use Relation or ChildOf type hints to declare SurrealDB graph relationships explicitly in your Python models.
• Write .surql migrations with DEFINE TABLE, DEFINE FIELD, and DEFINE EDGE statements to create schemaful structures.
• Register migrations in open_notebook/database/migrate.py to ensure automatic schema updates on deployment.
• Leverage the generic Repository class in open_notebook/database/repository.py for type-safe CRUD operations without writing custom SurrealQL.
• Follow project conventions by placing domain models in open_notebook/domain/, migrations in open_notebook/database/migrations/, and API routes in api/routers/.

Frequently Asked Questions

How do I define a many-to-many relationship between two domain models?

Use the Relation helper class with edge_name, direction, and target parameters. In your SurrealQL migration, create the edge using DEFINE EDGE edge_name FROM source_table TO target_table. This creates a graph relationship that the repository can traverse using the fetch parameter to resolve connections in a single query.

What is the difference between the Relation and ChildOf helpers?

Relation creates a generic many-to-many graph edge between two tables, suitable for associative relationships like tags or categories. ChildOf establishes a hierarchical one-to-many relationship where records reference a parent record in the same or different table, enforcing tree-like structures for organizational hierarchies or threaded comments.

Where should I place migration files for new domain models?

Store all schema migration files in open_notebook/database/migrations/ using the naming convention YYYY-MM-DD-description.surql. The run_migrations() function in open_notebook/database/migrate.py (or the legacy async_migrate.py) loads these files in alphabetical order, so use chronological date prefixes to maintain dependency order and prevent foreign key violations.

How does the repository handle SurrealDB record IDs?

The Repository class in open_notebook/database/repository.py automatically manages the conversion between SurrealDB's record ID format (table:id) and Python string attributes. When calling repo.get(Model, id), the method constructs the full record ID and deserializes the response back into your Pydantic model instance, handling the ObjectModel instantiation transparently.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}