# SurrealDB Database Migration Structure in Open Notebook: Version-Controlled Schema Management

> Learn about Open Notebook's SurrealDB migration structure for version controlled schema management. Discover its ordered SurrealQL scripts and _sbl_migrations tracking table for robust database updates.

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

---

**Open Notebook implements a self-contained, version-driven migration system using ordered SurrealQL scripts and a `_sbl_migrations` tracking table to manage SurrealDB schema changes asynchronously with automatic startup verification.**

Open Notebook uses SurrealDB as its primary data store, requiring a robust strategy for evolving database schemas without data loss. The `lfnovo/open-notebook` repository implements a lightweight yet comprehensive database migration structure that treats schema changes as version-controlled code, ensuring idempotent and ordered deployments across environments.

## Core Components of the Migration System

### Migration Files in `open_notebook/database/migrations/`

The migration layer stores plain SurrealQL scripts in the `open_notebook/database/migrations/` directory. Each file follows a numbered naming convention (e.g., `1.surrealql`, `2.surrealql`) representing incremental schema changes. Optional rollback scripts use the `*_down.surrealql` suffix (e.g., `19_down.surrealql`), containing `DROP TABLE` or `REMOVE FIELD` statements to reverse specific changes.

### The `_sbl_migrations` Version Table

SurrealDB maintains a dedicated `_sbl_migrations` table that records applied versions and timestamps. The system treats a missing table as version 0, enabling clean installations on fresh databases. The `bump_version()` function in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py) inserts rows after successful migrations, while `lower_version()` removes entries during rollbacks.

## The Async Migration Engine

### AsyncMigration Class

The `AsyncMigration` class in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) (lines 13-34) encapsulates individual migration scripts. It strips comments from SurrealQL files and executes them against the database connection. Upon successful execution, it triggers version tracking updates.

### AsyncMigrationRunner

The `AsyncMigrationRunner` class (lines 52-88 in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py)) orchestrates the execution flow. It maintains ordered lists of "up" and "down" migrations, providing methods to run all pending migrations, single up migrations, or single down migrations. This class handles the actual database transaction logic.

### AsyncMigrationManager

The high-level `AsyncMigrationManager` class (lines 90-128 in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py)) builds the migration queue and determines whether schema changes are needed. It implements `needs_migration()` to compare the current database version against available migration files, and provides a `ping()` method used during API health checks.

## Synchronous Wrapper for Legacy Code Paths

For synchronous contexts such as legacy tests or synchronous scripts, the `MigrationManager` class in [`open_notebook/database/migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/migrate.py) wraps `AsyncMigrationManager`. This facade allows non-async code to invoke the migration system without modifying existing synchronous call stacks.

## Startup Integration and API Lifecycle

The migration system integrates into the application lifespan via [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py). During API startup, the lifespan hook invokes `MigrationManager().run_migration_up()`, ensuring the database schema reaches the latest version before serving any requests. This prevents runtime errors caused by schema mismatches.

## How SurrealDB Schema Changes Are Applied

The migration process follows a strict sequence:

1. **Version Detection**: `get_latest_version()` queries the `_sbl_migrations` table. If the table does not exist, the version defaults to 0.
2. **Need Assessment**: `needs_migration()` compares the detected version against the count of available migration files in the directory.
3. **Sequential Application**: `run_migration_up()` iterates from the current version to the latest available script, executing each via `AsyncMigration.run(bump=True)`.
4. **Version Tracking**: After each successful script execution, `bump_version()` inserts a new row into `_sbl_migrations` with the current timestamp.
5. **Rollback Capability**: Calling `run_one_down()` executes the corresponding down-migration script and invokes `lower_version()` to remove the latest version entry.

## Practical Implementation Examples

### Creating a New Migration

To add a schema change, create a new numbered SurrealQL file in the migrations directory:

```python

# Create: open_notebook/database/migrations/19.surrealql

# Content:

# -- Add a new table for podcast transcripts

# CREATE TABLE podcast_transcript;

# DEFINE FIELD transcript TEXT;

# DEFINE FIELD created_at DATETIME DEFAULT time::now();

# Optional rollback: open_notebook/database/migrations/19_down.surrealql

# Content:

# DROP TABLE podcast_transcript;

```

The `AsyncMigrationManager` automatically detects new files when building the migration list, requiring no additional registration code.

### Running Migrations Manually

For maintenance scripts or CLI tools, use the synchronous wrapper:

```python
from open_notebook.database.migrate import MigrationManager

def migrate():
    mgr = MigrationManager()
    if mgr.needs_migration:
        print("Applying pending SurrealDB migrations…")
        mgr.run_migration_up()
        print("Migrations complete.")
    else:
        print("Database already up-to-date.")

if __name__ == "__main__":
    migrate()

```

### Checking the Current Database Version

To verify the current schema version programmatically:

```python
from open_notebook.database.async_migrate import get_latest_version

async def show_version():
    version = await get_latest_version()
    print(f"Current SurrealDB migration version: {version}")

```

### Rolling Back Changes

For emergency rollbacks, execute the down-migration:

```python
from open_notebook.database.async_migrate import AsyncMigrationManager

async def rollback_one():
    manager = AsyncMigrationManager()
    await manager.runner.run_one_down()
    print("Rolled back the most recent migration.")

```

## Summary

- Open Notebook stores SurrealDB schema changes as numbered `*.surrealql` files in `open_notebook/database/migrations/`.
- The `_sbl_migrations` table tracks applied versions, treating missing tables as version 0 for fresh installs.
- `AsyncMigrationManager` in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py) orchestrates the async migration pipeline, while `MigrationManager` in [`migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/migrate.py) provides a synchronous interface.
- The API startup sequence in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) automatically applies pending migrations before accepting requests.
- Each migration runs sequentially and is tracked individually, supporting both forward migrations and optional rollbacks.

## Frequently Asked Questions

### How does the migration system handle fresh database installations?

When connecting to a new SurrealDB instance, `get_latest_version()` detects the absence of the `_sbl_migrations` table and defaults to version 0. The system then applies all available migration scripts sequentially, building the schema from scratch.

### Can I skip specific migration versions when updating the schema?

No, the `AsyncMigrationRunner` enforces sequential execution. It iterates from the current database version to the latest available script number, ensuring that schema changes apply in the order they were created to prevent dependency conflicts.

### What happens if a migration script fails during execution?

The migration system applies scripts individually. If a script fails, the exception propagates before `bump_version()` is called, leaving the database at the last successful version. This prevents partial schema states and allows for retry logic upon the next startup.

### Is there a way to run migrations outside of the API startup process?

Yes, the `MigrationManager` class in [`open_notebook/database/migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/migrate.py) provides a synchronous interface suitable for CLI tools or maintenance scripts. Instantiate it and call `run_migration_up()` to apply pending changes manually without starting the full API server.