How AsyncMigrationManager Handles Database Schema Migrations in Open Notebook

The AsyncMigrationManager class automatically orchestrates SurrealDB schema updates during FastAPI startup by detecting version drift in the _sbl_migrations table and executing pending SQL migrations sequentially to ensure database consistency.

The lfnovo/open-notebook repository implements a self-healing database migration system that requires no manual intervention when deploying new versions. When the FastAPI application initializes, the AsyncMigrationManager inspects the current schema state, compares it against the migration files shipped with the codebase, and bridges any gaps by running the necessary SQL commands against SurrealDB. This architecture guarantees that the API never starts against an incompatible schema version.

The Migration Lifecycle on API Startup

The migration process hooks into the FastAPI lifespan context manager defined in api/main.py. On every startup, the system executes a five-step verification sequence:

1. Instantiation – The lifespan handler creates an AsyncMigrationManager instance (migration_manager = AsyncMigrationManager()).
2. Version Detection – migration_manager.get_current_version() queries the _sbl_migrations table to retrieve the last applied migration ID. If the table does not exist, it returns 0.
3. Drift Analysis – migration_manager.needs_migration() compares the current version against the count of up migrations available in open_notebook/database/migrations/.
4. Execution – When pending migrations exist, await migration_manager.run_migration_up() triggers the AsyncMigrationRunner to iterate through each unapplied migration, execute its SQL via the SurrealDB client, and record the new version.
5. Validation – Successful completion logs the new version; failure raises a RuntimeError to abort startup and prevent the API from serving requests against an outdated schema.

This logic resides in api/main.py lines 118-125, ensuring the database is always synchronized before the first HTTP request reaches the application.

Core Components of the Async Migration System

The migration subsystem is organized into three primary abstractions that handle SQL file parsing, version bookkeeping, and transaction execution.

AsyncMigrationManager

The AsyncMigrationManager class, defined in open_notebook/database/async_migrate.py (lines 91-119), serves as the central coordinator. It maintains two ordered lists of AsyncMigration objects: one for up migrations (forward changes) and one for down migrations (rollbacks). These lists are populated automatically from the SQL files located in the migrations directory.

The manager exposes the public API for migration checks (needs_migration()), full upgrades (run_migration_up()), and single-step operations (run_one_up(), run_one_down()).

AsyncMigration and AsyncMigrationRunner

Each AsyncMigration object represents a single schema change. It reads its corresponding SQL file, strips comments, and stores a clean SQL string for execution. The AsyncMigrationRunner class handles the actual database interaction.

The run_all() method (lines 66-73 in open_notebook/database/async_migrate.py) walks through the migration list starting from the current version, invoking await migration.run(bump=True) for each step. The run method executes the SQL (await connection.query(self.sql)) and then calls bump_version() to persist the change.

Version Tracking Infrastructure

The _sbl_migrations table acts as the ledger for schema history. Helper functions manage this state:

• get_current_version() – Returns the highest version number in the table, or 0 if uninitialized.
• bump_version() – Inserts a new row recording the applied migration ID (lines 220-228).
• lower_version() – Removes the latest entry during rollback operations.

These utilities ensure that every schema change is atomic and auditable, preventing partial migration states.

Executing Migrations Programmatically

While the lifespan hook handles automatic upgrades, the AsyncMigrationManager provides a programmatic API for manual execution in scripts or maintenance tasks.

Typical automatic usage (already present in the codebase):

# 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 (e.g., from a maintenance script):

import asyncio
from open_notebook.database.async_migrate import AsyncMigrationManager

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

    await manager.run_one_up()

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

Rolling back the last migration:

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

Migration File Organization

Migration SQL files are stored in open_notebook/database/migrations/ and follow a naming convention that allows the AsyncMigrationManager to sort them into ordered lists. The manager automatically discovers these files during initialization, distinguishing between up (forward) and down (rollback) scripts to populate its internal registry.

Summary

• Automatic startup verification – The AsyncMigrationManager integrates with FastAPI's lifespan context to verify and repair schema state before accepting traffic.
• Atomic version tracking – The _sbl_migrations table persists completed migration IDs, enabling idempotent upgrades and safe rollbacks.
• Orchestrated execution – AsyncMigrationRunner processes SQL files sequentially, ensuring dependencies are applied in order.
• Bidirectional support – The system maintains both up and down migration lists, supporting forward upgrades and backward rollbacks.
• Fail-safe defaults – Migration failures trigger RuntimeError to prevent the API from starting with an incompatible SurrealDB schema.

Frequently Asked Questions

What happens if the _sbl_migrations table does not exist?

The get_current_version() method returns 0 when the table is missing, causing needs_migration() to evaluate as True. The first migration execution will create the table and populate it with the initial version entry via bump_version().

How does the system handle migration failures?

If any migration step fails during run_migration_up(), the AsyncMigrationRunner propagates the exception, causing the lifespan context to raise a RuntimeError. This aborts the FastAPI startup process entirely, preventing the application from running against a partially migrated or outdated schema.

Can migrations be run outside of the FastAPI startup sequence?

Yes. You can instantiate AsyncMigrationManager in standalone scripts and call run_migration_up() for full upgrades, run_one_up() for single steps, or run_one_down() for rollbacks. All methods are async and require an active SurrealDB connection.

Where are the migration SQL files stored?

Migration files reside in open_notebook/database/migrations/. The AsyncMigrationManager automatically scans this directory during initialization to build the ordered lists of AsyncMigration objects for both upgrade and downgrade paths.

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