Understanding the Migration System for Schema Updates in Open Notebook

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.

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

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:

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:

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 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 provides backward-compatible synchronous access
• Automatic execution: The system checks and applies pending migrations automatically when the API server starts in 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 is the native implementation using async/await patterns suitable for FastAPI. MigrationManager in 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.

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