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

> AsyncMigrationManager on Open Notebook API startup automatically applies pending SurrealDB schema migrations. Ensure your database matches application code before accepting traffic.

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

---

**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`](https://github.com/lfnovo/open-notebook/blob/main/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:

```python

# 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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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:

```python

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

```python
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:

```python
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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`.