How SurrealDB Schema Migration Works in Open Notebook: AsyncMigrationManager Explained

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 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, 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. 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, 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):

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

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:

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, 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.

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