# How SurrealDB Schema Migration Works in Open Notebook: AsyncMigrationManager Explained

> Discover how Open Notebook's AsyncMigrationManager handles SurrealDB schema migration using versioned files and tracks state in the _sbl_migrations table before API requests are processed.

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

---

**The Open Notebook application uses an async-first `AsyncMigrationManager` to automatically apply SurrealQL schema changes from versioned files, tracking state in a hidden `_sbl_migrations` table before the API starts handling requests.**

Open Notebook stores its data in **SurrealDB**, a distributed NewSQL database. As the application evolves, the database schema must change in lockstep with code updates. The repository implements a robust migration framework in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) that guarantees every deployment runs required schema changes before serving requests.

## Core Components of the SurrealDB Migration Framework

### AsyncMigration (Single Script Execution)

The `AsyncMigration` class serves as a lightweight wrapper around individual SurrealQL migration files. According to the source code in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py), it loads a `.surrealql` file, strips comment lines beginning with `--`, and joins the remaining statements into one executable string (lines 13-34).

Its `run()` method opens an async SurrealDB connection via `db_connection` and executes the SQL. After successful execution, it either **bumps** the version by adding a row to the hidden `_sbl_migrations` table or **lowers** it by removing the latest row (lines 36-46).

### AsyncMigrationRunner (Orchestration Engine)

The `AsyncMigrationRunner` class manages ordered lists of `AsyncMigration` objects. It maintains two sequences: an *up* list for applying migrations and a *down* list for rollbacks (lines 58-65).

The `run_all()` method iterates from the current database version up to the newest migration, invoking `run(bump=True)` for each step (lines 66-73). For granular control, helper methods `run_one_up()` and `run_one_down()` allow single-step upgrades or rollbacks without processing the entire queue (lines 74-88).

### AsyncMigrationManager (Public API)

`AsyncMigrationManager` provides the primary interface used by the rest of the Open Notebook codebase. On construction, it loads all migration files—currently 15 up-migrations and 15 down-migrations—via `AsyncMigration.from_file()` (lines 96-124).

The manager exposes several async helpers:

- **`get_current_version()`** reads the highest entry in `_sbl_migrations`, returning `0` if the table does not exist (lines 77-84).
- **`needs_migration()`** compares the current version against the length of the *up* list to determine if updates are required (lines 81-84).
- **`run_migration_up()`** logs the current version, checks `needs_migration()`, then delegates to the runner's `run_all()`. Errors are logged and re-raised to prevent silent failures (lines 86-99).

Version bookkeeping operations—including `get_latest_version`, `bump_version`, and `lower_version`—execute simple SurrealQL queries against the internal `_sbl_migrations` table (lines 104-145).

## Synchronous MigrationManager for Legacy Code

For non-async contexts, a synchronous wrapper exists in [`open_notebook/database/migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/migrate.py). The `MigrationManager` class forwards all calls to an internal `AsyncMigrationManager` instance using `asyncio.run()`, allowing legacy scripts and CLI tools to trigger migrations without async/await syntax (lines 6-27).

## Migration Execution During API Startup

When the FastAPI application starts in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), it instantiates `AsyncMigrationManager` and calls `run_migration_up()`. If the database already matches the latest schema version, the manager logs "Database is already at the latest version" and skips execution (lines 118-122). This ensures the schema is always synchronized before the API handles requests.

## Running SurrealDB Migrations Manually

Developers can trigger migrations programmatically or inspect version state using the following patterns.

**Running migrations synchronously (e.g., from CLI scripts):**

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

mgr = MigrationManager()
print("Current DB version:", mgr.get_current_version())
if mgr.needs_migration:
    print("Applying pending migrations …")
    mgr.run_migration_up()
else:
    print("Database schema is up‑to‑date.")

```

**Running migrations asynchronously (e.g., inside FastAPI startup events):**

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

async def run_migrations() -> None:
    mgr = AsyncMigrationManager()
    if await mgr.needs_migration():
        await mgr.run_migration_up()

```

**Inspecting the current migration version:**

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

# Direct helper function

version = await get_latest_version()

# Or via manager instance

version = await AsyncMigrationManager().get_current_version()

```

## Summary

- **AsyncMigration** wraps individual SurrealQL files, parsing and executing them while managing version bumps in the `_sbl_migrations` table.
- **AsyncMigrationRunner** orchestrates ordered lists of up and down migrations, providing both batch (`run_all`) and single-step (`run_one_up`, `run_one_down`) execution.
- **AsyncMigrationManager** serves as the public API, automatically loading 15 up and 15 down migrations from `open_notebook/database/migrations/` and exposing version-checking helpers.
- **MigrationManager** provides a synchronous wrapper for legacy code using `asyncio.run()`.
- Migrations execute automatically on API startup in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), ensuring SurrealDB schema consistency before handling requests.

## Frequently Asked Questions

### What is the `_sbl_migrations` table in SurrealDB?

The `_sbl_migrations` table is a hidden system table used by Open Notebook to track which schema migrations have been applied. It stores version numbers as rows, allowing `AsyncMigrationManager` to determine the current state and calculate whether upgrades are needed.

### How do I create a new schema migration in Open Notebook?

Create a new SurrealQL file in `open_notebook/database/migrations/` with the next sequential number (e.g., `16.surrealql` for the next up-migration) and a corresponding `16_down.surrealql` for the rollback. The `AsyncMigrationManager` automatically discovers these files on instantiation and includes them in the migration chain.

### Can I roll back a specific SurrealDB migration without restarting the API?

Yes. The `AsyncMigrationRunner` provides `run_one_down()` for single-step rollbacks, and the synchronous `MigrationManager` exposes this functionality. You can instantiate either manager and call the down migration method to revert the most recent schema change recorded in `_sbl_migrations`.

### Why does the migration system use AsyncMigrationManager instead of blocking calls?

Open Notebook uses **asyncio** throughout its FastAPI-based API to handle concurrent database operations efficiently. The `AsyncMigrationManager` maintains this async-first architecture, preventing blocking I/O during startup while ensuring SurrealDB schema changes complete before the application accepts traffic.