# Understanding the Migration System for Schema Updates in Open Notebook

> Learn how Open Notebooks migration system automatically applies schema updates to SurrealDB using an async-first framework and numeric SurrealQL files. Ensure seamless database evolution.

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

---

**The migration system in Open Notebook uses an async-first framework with numeric SurrealQL files and a version tracking table to automatically apply schema changes to SurrealDB on application startup.**

Open Notebook persists its data in **SurrealDB**, requiring a reliable way to evolve the database schema as the application grows. The project implements a lightweight, custom migration framework located in `open_notebook/database/` that handles incremental schema updates without manual intervention.

## Core Architecture of the Migration System

The migration stack is organized into four distinct layers, each with a specific responsibility in the schema lifecycle.

### AsyncMigration

The `AsyncMigration` class represents a single schema change file. It parses individual `.surrealql` files and executes the contained SurrealQL statements against the database. After successful execution, it updates the version counter by calling `bump_version()`, or reverts it via `lower_version()` during rollbacks. This class is defined in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py).

### AsyncMigrationRunner

This component holds ordered lists of *up* and *down* migrations. The `AsyncMigrationRunner` executes migrations one-by-one, processing files sequentially from the current version to the target version. It provides `run_all()` for applying pending migrations and `run_one_down()` for rolling back the most recent change. The runner also maintains the `_sbl_migrations` table integrity throughout the process.

### AsyncMigrationManager

Serving as the primary entry point, the `AsyncMigrationManager` initializes the full migration catalog on startup. It exposes high-level helper methods including `needs_migration()` to check if updates are required, `run_migration_up()` to execute pending changes, and `get_current_version()` to inspect the database state. This manager is instantiated in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) during the FastAPI application startup event.

### MigrationManager (Sync Wrapper)

For legacy code paths and command-line utilities that expect blocking calls, the `MigrationManager` class in [`open_notebook/database/migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/migrate.py) provides a synchronous façade. This wrapper internally delegates all operations to `AsyncMigrationManager` using `asyncio.run()`, allowing the rest of the codebase to remain fully asynchronous while supporting synchronous consumers.

## How Schema Migrations Work in Practice

### Migration File Structure

Each schema change lives in a numbered file within `open_notebook/database/migrations/`. The naming convention follows a simple integer sequence: `1.surrealql`, `2.surrealql`, etc. For every *up* migration that applies a change, there exists a corresponding *down* file (e.g., `1_down.surrealql`) that reverses the modification. These files contain raw SurrealQL statements that alter tables, indexes, or relationships.

### Version Tracking with `_sbl_migrations`

The system tracks applied migrations using a dedicated SurrealDB table named `_sbl_migrations`. This table stores a single record per migration containing the version number and timestamp of when it was applied. When `AsyncMigrationManager` initializes, it queries this table via `get_latest_version()` to determine the current state. If the table does not exist, the system assumes version 0 and initializes the schema from scratch.

### Execution Flow on Application Startup

When the FastAPI application starts in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the following sequence occurs:

1. An `AsyncMigrationManager` instance is created
2. `needs_migration()` compares the current version against the total number of migration files
3. If `current_version < len(up_migrations)`, the system calls `run_migration_up()`
4. This delegates to `AsyncMigrationRunner.run_all()`, which iterates through pending files
5. Each migration executes via `AsyncMigration.run()`, followed by `bump_version()` to record completion
6. The database schema is now synchronized with the codebase

## Code Examples

### Checking and Running Migrations Synchronously

Use the sync wrapper when integrating with legacy scripts or CLI tools:

```python
from open_notebook.database.migrate import MigrationManager

manager = MigrationManager()

if manager.needs_migration:
    print(f"Current version: {manager.get_current_version()}")
    manager.run_migration_up()
    print("All pending migrations applied.")
else:
    print("Database schema is already up‑to‑date.")

```

### Async Usage in FastAPI Startup Events

For modern async applications, use the native async manager during startup:

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

app = FastAPI()

@app.on_event("startup")
async def run_migrations():
    mgr = AsyncMigrationManager()
    if await mgr.needs_migration():
        await mgr.run_migration_up()
        version = await mgr.get_current_version()
        print(f"Database migrated to version {version}")

```

### Rolling Back the Last Migration

In rare cases requiring schema reversion, execute the down migration:

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

async def rollback_last():
    mgr = AsyncMigrationManager()
    await mgr.runner.run_one_down()
    # Version automatically decremented via lower_version()

```

## Summary

- **Async-first design**: All database interactions in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) use `await` to prevent blocking the FastAPI event loop
- **File-based versioning**: Migrations use numeric `.surrealql` files in `open_notebook/database/migrations/` with paired `*_down.surrealql` rollback scripts
- **State tracking**: The `_sbl_migrations` table ensures idempotent execution and prevents duplicate schema changes
- **Dual API**: `AsyncMigrationManager` handles modern async code while `MigrationManager` in [`migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/migrate.py) provides backward-compatible synchronous access
- **Automatic execution**: The system checks and applies pending migrations automatically when the API server starts in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py)

## Frequently Asked Questions

### What file format are Open Notebook migrations written in?

Migrations are written in **SurrealQL** and stored in files with the `.surrealql` extension. Each file contains raw database commands that create tables, define fields, or establish relationships specific to SurrealDB's query language.

### How does the system prevent the same migration from running twice?

The `AsyncMigrationManager` queries the `_sbl_migrations` table before executing any changes. When a migration runs successfully, `bump_version()` inserts a record into this table. Subsequent checks compare the current version against available files, ensuring each migration only executes once.

### What is the difference between `AsyncMigrationManager` and `MigrationManager`?

`AsyncMigrationManager` in [`async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/async_migrate.py) is the native implementation using async/await patterns suitable for FastAPI. `MigrationManager` in [`migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/migrate.py) is a synchronous wrapper that executes async operations via `asyncio.run()`, designed for legacy code or command-line scripts that cannot use async syntax.

### How do I add a new schema change to the project?

Create a new file in `open_notebook/database/migrations/` using the next available integer (e.g., `3.surrealql` if `2.surrealql` exists). Write your SurrealQL statements to apply the change. Optionally create a corresponding `3_down.surrealql` file with rollback commands. The system will automatically detect and apply the new migration on the next application startup.