# AsyncMigrationManager Database Migration Architecture in Open Notebook

> Explore the Open Notebook AsyncMigrationManager architecture for seamless SurrealDB schema updates. Learn how ordered SurrealQL scripts ensure reliable database migrations.

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

---

**Open Notebook implements an asynchronous, file-driven migration system using three core classes—`AsyncMigration`, `AsyncMigrationRunner`, and `AsyncMigrationManager`—to manage SurrealDB schema changes through ordered SurrealQL scripts.**

Open Notebook relies on SurrealDB as its primary datastore, requiring a robust mechanism to evolve the schema across deployments. The `AsyncMigrationManager` class provides the high-level façade for this system, orchestrating version tracking and script execution through an async-first architecture defined in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py).

## Core Architecture Components

The migration system is built around three distinct classes that separate concerns between individual migration steps, execution orchestration, and application-level management.

### AsyncMigration: The Atomic Migration Unit

The `AsyncMigration` class represents a single migration step. It loads a SurrealQL script from a file, removes comments to store a cleaned SQL string, and provides a `run()` coroutine that executes the script inside a database connection. According to the source code in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py), each instance can optionally update the version table when executed with `bump=True`.

### AsyncMigrationRunner: The Execution Engine

`AsyncMigrationRunner` orchestrates collections of `AsyncMigration` objects. It manages the execution of all pending "up" migrations, single "up" migrations, or single "down" migrations for rollback. The runner always consults the current database version via `get_latest_version()` before proceeding, ensuring idempotent operations.

### AsyncMigrationManager: The Application Interface

`AsyncMigrationManager` serves as the high-level façade used by the application startup sequence. Its constructor builds ordered lists of **up** and **down** migrations by scanning files in `open_notebook/database/migrations/`, instantiates a runner, and exposes convenience methods including `needs_migration()`, `run_migration_up()`, and `get_current_version()`.

## Version Tracking and State Management

The system maintains state through a lightweight `_sbl_migrations` table that stores one row per applied migration. Three helper functions manage this state:

- **`bump_version()`** inserts a new row to record a successful migration
- **`lower_version()`** deletes the latest row during rollback operations
- **`get_latest_version()`** reads the highest version number, defaulting to `0` when the table is absent

These functions interact with SurrealDB through the async repository layer defined in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py), which provides connection management via `repo_query` calls.

## Migration File Organization

Migration scripts follow a strict file-based convention. Each migration lives as a plain `.surrealql` script (e.g., `1.surrealql`, `2.surrealql`) under `open_notebook/database/migrations/`. A matching `*_down.surrealql` file provides the reverse operation for rollback scenarios. The manager loads these files using `AsyncMigration.from_file` to create executable migration objects.

## Execution Flow

The migration process follows a deterministic sequence during application startup:

1. **Initialization** – The API imports `AsyncMigrationManager`, which constructs ordered `AsyncMigration` objects by reading every `*.surrealql` file in the migrations directory.

2. **Version Detection** – `get_latest_version()` queries the `_sbl_migrations` table via `repo_query`. If the table does not exist, it returns `0`, treating the database as fresh.

3. **Decision** – `needs_migration()` compares the stored version with the length of the `up_migrations` list to determine if work is required.

4. **Execution** – If migrations are needed, `run_migration_up()` calls `runner.run_all()`. The runner iterates from the current version to the end of the list, invoking each `AsyncMigration.run(bump=True)`. After each successful migration, `bump_version()` records the new version.

5. **Rollback** – `run_one_down()` mirrors the process, executing the corresponding `down_migrations` entry with `bump=False` and then calling `lower_version()` to remove the version record.

All operations are fully asynchronous, ensuring that the API can handle concurrent requests without blocking while migrations are applied.

## Implementation Examples

### Running All Pending Migrations

This is the typical pattern used during API startup:

```python

# api/main.py (simplified)

from open_notebook.database.async_migrate import AsyncMigrationManager

async def init_db():
    manager = AsyncMigrationManager()
    await manager.run_migration_up()

# Somewhere in the startup coroutine

await init_db()

```

### Querying Current Schema Version

Useful for diagnostic endpoints or health checks:

```python
from open_notebook.database.async_migrate import AsyncMigrationManager

async def print_version():
    manager = AsyncMigrationManager()
    version = await manager.get_current_version()
    print(f"SurrealDB schema version: {version}")

# Called in a diagnostic endpoint

await print_version()

```

### Applying a Single Migration Manually

Helpful for debugging specific migration steps:

```python
from open_notebook.database.async_migrate import AsyncMigrationManager

async def apply_next():
    manager = AsyncMigrationManager()
    await manager.runner.run_one_up()   # runs the next pending up migration

```

### Rolling Back the Most Recent Migration

For recovery scenarios during development:

```python
from open_notebook.database.async_migrate import AsyncMigrationManager

async def rollback():
    manager = AsyncMigrationManager()
    await manager.runner.run_one_down()   # rolls back one step

```

## Summary

- **AsyncMigrationManager** provides the high-level interface for managing SurrealDB schema evolution in Open Notebook.
- The system uses three classes (`AsyncMigration`, `AsyncMigrationRunner`, `AsyncMigrationManager`) to separate migration definition, execution, and coordination concerns.
- Version state is tracked in the `_sbl_migrations` table using `bump_version()` and `lower_version()` helpers.
- Migration scripts are stored as `.surrealql` files in `open_notebook/database/migrations/` with corresponding `*_down.surrealql` rollback files.
- All database interactions occur through the async repository layer in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py).

## Frequently Asked Questions

### How does AsyncMigrationManager determine if the database needs migrating?

The `needs_migration()` method compares the current database version—retrieved via `get_latest_version()`—against the total number of available up migrations. If the stored version is less than the length of the migration list, pending migrations exist.

### What is the role of the `_sbl_migrations` table?

The `_sbl_migrations` table persists the migration history within SurrealDB. Each row represents an applied migration, enabling the system to track which scripts have executed and support rollback operations through `lower_version()`.

### How do I execute a single rollback migration?

Invoke `run_one_down()` on the runner instance obtained from `AsyncMigrationManager`. This executes the corresponding `*_down.surrealql` script and decrements the version counter in the `_sbl_migrations` table.

### Where does the database connection originate for migrations?

All database interactions route through the async repository layer in [`open_notebook/database/repository.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/repository.py). The migration system calls `repo_query` to execute SurrealQL, which manages connections via an async context manager that handles authentication, namespace selection, and connection closure.