SurrealDB Schema in Open-Notebook: Structure and Migration Guide

Open-Notebook stores all data in a SurrealDB graph database defined in open_notebook/database/migrations/1.surrealql, featuring SCHEMAFULL tables for sources and notes, vector embedding storage, BM25 full-text indexes, and custom SurrealQL search functions, with new migrations added by creating numbered .surrealql files and registering them in async_migrate.py.

The lfnovo/open-notebook repository implements a comprehensive SurrealDB schema that powers its knowledge management features. This schema defines everything from core content tables to vector search capabilities and automated timestamp management. Understanding this SurrealDB schema and the migration system is crucial for developers extending the data model or maintaining the database across versions.

Core Schema Tables and Relations

The initial migration file open_notebook/database/migrations/1.surrealql defines eight primary tables and relations using DEFINE TABLE IF NOT EXISTS statements. Most tables use SCHEMAFULL mode for strict type enforcement, except podcast_config which remains SCHEMALESS.

The source table stores raw extracted content with fields including title (option), topics (option<array>), full_text (option), and asset (option). The source_embedding table contains chunked text embeddings with a record<source> foreign key, order (int), content (string), and embedding (array). The source_insight table holds AI-generated insights with similar embedding storage.

The note table stores user-written content with title, summary, content, and embedding fields, while the notebook table acts as a container with name, description, and an optional archived boolean defaulting to false. Two relation tables—reference (linking sources to notebooks) and artifact (linking notes to notebooks)—enable graph traversal between entities.

All timestamp fields (created, updated) are automatically populated using time::now() as seen in lines 13-15 and 41-42 of the migration file.

Full-Text Search and Indexes

The schema includes a custom full-text analyzer named my_analyzer defined at line 66. This analyzer powers BM25 search indexes created on lines 67-73, enabling efficient text search across source.title, source.full_text, source_embedding.content, source_insight.content, and note.content/title fields.

SurrealQL Helper Functions

Two critical functions in the migration file power the application's search API:

• fn::text_search($query_text, $match_count, $sources, $show_notes) (lines 74-132): Builds a union of text-based and title-based queries for sources and notes, returning item IDs ordered by relevance.

• fn::vector_search($query, $match_count, $sources, $show_notes) (lines 138-170): Performs cosine similarity calculations on stored embeddings in source_embedding and source_insight tables.

Migration System Architecture

Open-Notebook uses an async migration system implemented in open_notebook/database/async_migrate.py. The system tracks applied versions in a hidden _sbl_migrations table managed by helper functions bump_version() and lower_version() (lines 26-44 of the initial migration).

The AsyncMigrationManager class loads migration files sequentially and compares the latest version in _sbl_migrations against available migrations using get_latest_version(). When the API starts, AsyncMigrationManager.run_migration_up() automatically applies pending migrations (see api/main.py for the startup integration).

How to Add a New Migration

Extending the SurrealDB schema requires creating a migration file and registering it in the migration manager:

1. Create the migration file
   Add a new SurrealQL file at open_notebook/database/migrations/16.surrealql (using the next sequential integer). Include DEFINE TABLE, DEFINE FIELD, or DEFINE INDEX statements as needed.

2. Create an optional down-migration
   For rollback support, create open_notebook/database/migrations/16_down.surrealql containing reverse operations (e.g., DEFINE TABLE ... DROP).

3. Update the migration manager
   Open open_notebook/database/async_migrate.py and append the new migration to the up_migrations and down_migrations lists around lines 98-124:

   self.up_migrations = [
       # … existing migrations …
   
       AsyncMigration.from_file("open_notebook/database/migrations/15.surrealql"),
       AsyncMigration.from_file("open_notebook/database/migrations/16.surrealql"),
   ]
   self.down_migrations = [
       # … existing down migrations …
   
       AsyncMigration.from_file("open_notebook/database/migrations/15_down.surrealql"),
       AsyncMigration.from_file("open_notebook/database/migrations/16_down.surrealql"),
   ]

4. Run the migration
   In development, execute synchronously:

   from open_notebook.database.migrate import MigrationManager
   MigrationManager().run_migration_up()

   In production, the API automatically runs pending migrations on startup via AsyncMigrationManager.run_migration_up() (lines 86-99).

Practical Migration Example

Below is a complete example adding an author table to the schema:

File: open_notebook/database/migrations/16.surrealql

DEFINE TABLE IF NOT EXISTS author SCHEMAFULL;
DEFINE FIELD IF NOT EXISTS name ON TABLE author TYPE option<string>;
DEFINE FIELD IF NOT EXISTS bio ON TABLE author TYPE option<string>;
DEFINE FIELD IF NOT EXISTS created ON author DEFAULT time::now() VALUE $before OR time::now();
DEFINE FIELD IF NOT EXISTS updated ON author DEFAULT time::now() VALUE time::now();

File: open_notebook/database/migrations/16_down.surrealql (optional rollback)

DEFINE TABLE author SCHEMAFULL DROP;

Summary

• The SurrealDB schema is defined in open_notebook/database/migrations/1.surrealql with SCHEMAFULL tables and automated time::now() timestamps on lines 13-15 and 41-42.
• Vector embeddings are stored in source_embedding and source_insight tables using array<float> types for semantic search.
• Full-text search leverages a custom my_analyzer with BM25 indexing defined on lines 66-73.
• Helper functions fn::text_search (lines 74-132) and fn::vector_search (lines 138-170) power the application's hybrid search capabilities.
• Migration versions are tracked in the _sbl_migrations table via bump_version() and lower_version() helpers.
• New migrations require creating numbered .surrealql files in open_notebook/database/migrations/ and registering them in open_notebook/database/async_migrate.py.

Frequently Asked Questions

How is the SurrealDB schema defined in Open-Notebook?

The complete schema is defined in the first migration file open_notebook/database/migrations/1.surrealql, which creates all tables, fields, indexes, and helper functions. This includes table definitions using DEFINE TABLE IF NOT EXISTS ... SCHEMAFULL, field definitions with type constraints like option<string> and array<float>, and automated timestamp fields using time::now().

What tables store vector embeddings for semantic search?

Vector embeddings are stored in the source_embedding and source_insight tables, both containing an embedding field of type array<float>. The source_embedding table stores chunked text embeddings belonging to sources with a record<source> reference, while source_insight stores AI-generated insights with their associated vectors and insight_type metadata.

How does the migration system track which versions are applied?

The migration system uses a hidden _sbl_migrations table to track applied versions. When migrations run, the bump_version() function inserts a version row, while lower_version() removes it during rollbacks. The AsyncMigrationManager compares the latest version in this table against the length of the up_migrations list to determine pending changes.

Can I add a new table to the Open-Notebook SurrealDB schema?

Yes, create a new SurrealQL file in open_notebook/database/migrations/ with the next sequential integer (e.g., 16.surrealql), define your table using DEFINE TABLE IF NOT EXISTS ... SCHEMAFULL, add fields with DEFINE FIELD, and register the migration in open_notebook/database/async_migrate.py by appending AsyncMigration.from_file() to the up_migrations list. Optionally create a matching 16_down.surrealql file for rollback support.

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