# How AsyncMigrationManager Handles Database Schema Migrations in Open Notebook

> Learn how AsyncMigrationManager in Open Notebook automatically handles SurrealDB schema migrations on FastAPI startup, ensuring database consistency by executing pending SQL updates.

- Repository: [Luis Novo/open-notebook](https://github.com/lfnovo/open-notebook)
- Tags: how-to-guide
- Published: 2026-07-02

---

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

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

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

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

```

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