# SurrealDB Schema in Open-Notebook: Structure and Migration Guide

> Explore the SurrealDB schema in Open-Notebook. Learn to add new migrations and manage your graph database structure for notes and sources. Guide to database structuring and migration.

- Repository: [Luis Novo/open-notebook](https://github.com/lfnovo/open-notebook)
- Tags: how-to-guide
- Published: 2026-06-29

---

**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`](https://github.com/lfnovo/open-notebook/blob/main/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<string>), `topics` (option<array<string>>), `full_text` (option<string>), and `asset` (option<object>). The **source_embedding** table contains chunked text embeddings with a `record<source>` foreign key, `order` (int), `content` (string), and `embedding` (array<float>). 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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) and append the new migration to the `up_migrations` and `down_migrations` lists around lines 98-124:

   ```python
   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:
   
   ```python
   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`

```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)

```surrealql
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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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.