How AsyncMigrationManager Handles Database Schema Migrations on API Startup in Open Notebook

On every FastAPI launch, the AsyncMigrationManager automatically detects and applies pending SurrealDB schema migrations before the API accepts traffic, ensuring the database version always matches the application code.

The lfnovo/open-notebook project relies on an async migration subsystem to keep its SurrealDB schema synchronized with the application codebase. When the FastAPI server starts, the AsyncMigrationManager inspects the current schema version, identifies any missing migrations, and executes them automatically. This self-checking behavior prevents the API from running against an out-of-date database without requiring manual intervention.

The Automatic Migration Flow on API Startup

The entire process is orchestrated inside the lifespan context manager defined in api/main.py (lines 118-125). On every startup, the application delegates schema readiness to the AsyncMigrationManager through a five-step sequence.

Step 1 – Instantiate the Migration Manager

Inside the startup hook, the application creates a fresh manager instance:

# api/main.py – inside the lifespan context manager

migration_manager = AsyncMigrationManager()

Step 2 – Read the Current Schema Version

The manager calls get_current_version() to query the _sbl_migrations table. If the table does not yet exist, it safely returns 0, allowing the system to treat the database as untouched.

Step 3 – Check for Pending Migrations

Next, migration_manager.needs_migration() compares the recorded version against the number of up migrations shipped with the codebase under open_notebook/database/migrations/.

Step 4 – Run Pending Migrations

If the codebase has a higher version, the startup hook awaits migration_manager.run_migration_up(). According to the source in open_notebook/database/async_migrate.py (lines 66-73), AsyncMigrationRunner.run_all() loops from the current version to the end of the up_migrations list. For each step, it invokes await migration.run(bump=True), which performs two operations:

1. Executes the SQL through the SurrealDB client.
2. Records the new version in _sbl_migrations via bump_version().

Step 5 – Enforce Failure Handling

On success, the new version is logged and startup continues. On failure, the process raises a RuntimeError to abort API startup, preventing the application from serving requests against an outdated or partially migrated schema.

Core Architecture of the Async Migration Subsystem

The migration engine is implemented primarily in open_notebook/database/async_migrate.py. It separates version metadata from SQL execution and couples them through an ordered runner pipeline.

AsyncMigrationManager

Defined in open_notebook/database/async_migrate.py (lines 91-119), the AsyncMigrationManager maintains two ordered lists of AsyncMigration objects: one for up migrations and one for down migrations. Both lists are built dynamically from the SQL files stored under open_notebook/database/migrations/.

AsyncMigration and AsyncMigrationRunner

Each AsyncMigration reads its corresponding SQL file, strips comments, and stores a clean SQL string. The AsyncMigrationRunner orchestrates execution. Its run_all() method walks through the migration list starting at the current version and invokes await migration.run(bump=True) for each unapplied step. The run method itself performs the critical work by executing await connection.query(self.sql) and then calling bump_version().

Version Tracking via _sbl_migrations

All version bookkeeping is centralized through helper functions. The bump_version() implementation in open_notebook/database/async_migrate.py (lines 220-228) inserts a new record into _sbl_migrations after each successful migration. Supporting helpers such as get_latest_version(), get_all_versions(), and lower_version() manage reads and rollbacks.

Practical Code Examples

The following snippets demonstrate how to interact with the migration system inside scripts or external tooling.

Standard API Startup Hook

This is the exact flow already present in the codebase. It lives inside the FastAPI lifespan context and runs automatically on every boot:

# api/main.py – inside the lifespan context manager

migration_manager = AsyncMigrationManager()
if await migration_manager.needs_migration():
    await migration_manager.run_migration_up()

Running a Single Migration Manually

For maintenance scripts or selective updates, you can advance the schema by one version:

import asyncio
from open_notebook.database.async_migrate import AsyncMigrationManager

async def migrate_one():
    manager = AsyncMigrationManager()
    # Apply the next pending migration only

    await manager.run_one_up()

if __name__ == "__main__":
    asyncio.run(migrate_one())

Rolling Back the Last Migration

To undo the most recently applied change, use the down-migration path:

import asyncio
from open_notebook.database.async_migrate import AsyncMigrationManager

async def rollback():
    manager = AsyncMigrationManager()
    await manager.run_one_down()   # removes the latest version entry

if __name__ == "__main__":
    asyncio.run(rollback())

Summary

• The FastAPI lifespan handler in api/main.py automatically triggers AsyncMigrationManager on every startup.
• get_current_version() queries _sbl_migrations to determine the database's existing schema version.
• needs_migration() and run_migration_up() detect and execute any pending SQL migrations shipped under open_notebook/database/migrations/.
• AsyncMigrationRunner.run_all() applies each migration sequentially, while bump_version() records progress in _sbl_migrations.
• A migration failure raises a RuntimeError that blocks API startup, enforcing schema consistency.

Frequently Asked Questions

What happens if the _sbl_migrations table does not exist?

If the _sbl_migrations version table is missing, AsyncMigrationManager.get_current_version() returns 0. The system treats the database as pristine and will apply every migration in the up_migrations list from the beginning.

Where does Open Notebook store migration SQL files?

Migration scripts live in the open_notebook/database/migrations/ directory. The AsyncMigrationManager scans this folder at runtime to build ordered lists of AsyncMigration objects for both upward and downward schema changes.

How does the system behave if a migration fails during startup?

If any migration throws an exception, the AsyncMigrationManager aborts the process with a RuntimeError. This prevents the FastAPI application from starting against an inconsistent or partially migrated SurrealDB schema.

Is there a synchronous wrapper for legacy callers?

Yes. The project includes open_notebook/database/migrate.py, which provides a synchronous wrapper around the async migration engine. This allows non-async contexts to invoke the same migration logic without directly using asyncio or AsyncMigrationManager.

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