How Automatic Database Migrations Work with AsyncMigrationManager on API Startup in Open Notebook

The Open Notebook API automatically executes pending SurrealDB schema migrations during FastAPI startup using an AsyncMigrationManager that detects the current version, compares it against available migration files, and runs them asynchronously before the server accepts requests.

When the Open Notebook API boots, it ensures the SurrealDB schema is current without manual intervention. This is handled by the AsyncMigrationManager class defined in open_notebook/database/async_migrate.py, which integrates with FastAPI's lifespan context to provide asynchronous, ordered, and idempotent schema evolution.

The Lifespan Hook: Triggering Migrations on Startup

In api/main.py, the application defines an @asynccontextmanager called lifespan. FastAPI invokes this during the startup and shutdown phases. Inside the startup block, the code instantiates AsyncMigrationManager and triggers the migration check before loading API routers.

# Conceptual flow from api/main.py

@asynccontextmanager
async def lifespan(app: FastAPI):
    # Startup phase

    migration_manager = AsyncMigrationManager()
    if await migration_manager.needs_migration():
        await migration_manager.run_migration_up()
    # ... continue to load routers

Detecting Schema Version and Pending Changes

The AsyncMigrationManager performs a three-step validation process to determine if work is required.

Current Version Detection

get_current_version() queries the _sbl_migrations table via get_latest_version(). If the table does not exist (as in a fresh database), the version defaults to 0.

Migration Inventory

The constructor pre-loads every migration file from open_notebook/database/migrations/. These files follow the naming convention 1.surrealql, 2.surrealql, etc., and represent sequential schema changes.

Needs Migration Check

needs_migration() compares the current database version against the count of available "up" migrations bundled in the manager. If the database version is lower than the number of migration files, the system identifies pending work.

Executing Migrations Asynchronously

When migrations are required, run_migration_up() delegates to AsyncMigrationRunner.run_all(). The runner iterates from the current version upward, executing each AsyncMigration.run() which performs the following:

1. Opens a SurrealDB connection using db_connection()
2. Executes the SQL statement via await connection.query(self.sql)
3. Updates the version table using bump_version() (or lower_version() for rollbacks)

Fail-Fast Error Handling

Any exception during migration execution is logged and re-raised as a RuntimeError, causing the API startup to abort immediately. This "fail-fast" design guarantees that the server never runs with an outdated or partially migrated schema, preventing data model inconsistencies in production.

Practical Code Examples

Manually Triggering Migrations

Use this pattern in scripts, tests, or administrative tools to run migrations outside of the standard API startup:

from open_notebook.database.async_migrate import AsyncMigrationManager

async def migrate():
    manager = AsyncMigrationManager()
    if await manager.needs_migration():
        await manager.run_migration_up()
    print("Database is now at version:", await manager.get_current_version())

Inspecting the Current Version

To check the schema version without running migrations:

from open_notebook.database.async_migrate import get_latest_version

async def show_version():
    version = await get_latest_version()
    print(f"Current DB schema version: {version}")

Adding a New Migration

1. Create a file in open_notebook/database/migrations/ with the next sequential number (e.g., 15.surrealql).
2. Write your SurrealQL DDL/DML statements in the file.
3. The AsyncMigrationManager automatically includes the new migration on the next startup because the constructor loads all files from the migrations directory.

Summary

• Lifespan Integration: The AsyncMigrationManager runs inside the lifespan context manager in api/main.py, ensuring migrations complete before the API serves requests.
• Version Tracking: Uses the _sbl_migrations table with automatic fallback to version 0 for new databases.
• Asynchronous Execution: AsyncMigrationRunner.run_all() processes migrations sequentially via AsyncMigration.run(), handling database connections and versioning atomically.
• Safety First: Fail-fast behavior aborts startup on any migration error, preventing the API from running against an outdated schema.

Frequently Asked Questions

What happens if the _sbl_migrations table doesn't exist?

If the table is missing, get_current_version() defaults to version 0, and the migration manager treats all available migrations as pending. This allows the system to bootstrap itself on a fresh SurrealDB instance automatically.

How does the migration manager determine which migrations to run?

AsyncMigrationManager compares the current database version (stored in _sbl_migrations) against the number of .surrealql files in open_notebook/database/migrations/. If the file count exceeds the stored version, needs_migration() returns True, and run_migration_up() executes the missing versions sequentially.

Can I run migrations outside of the API startup?

Yes. You can instantiate AsyncMigrationManager manually in scripts or tests and call await manager.run_migration_up() after checking needs_migration(). This is useful for CI/CD pipelines or administrative maintenance without starting the full FastAPI server.

Where are migration files stored and how are they discovered?

Migration files are stored in open_notebook/database/migrations/ with sequential numeric names like 1.surrealql, 2.surrealql, etc. The AsyncMigrationManager constructor automatically discovers these files when instantiated, so adding a new migration requires no registry updates—simply create the file with the next number in the sequence.

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