How Automatic Database Migrations Work on API Startup in Open Notebook

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

When the FastAPI application in the lfnovo/open-notebook repository starts, it orchestrates automatic database migrations to ensure the SurrealDB schema always matches the codebase expectations. This process is handled by the AsyncMigrationManager class defined in open_notebook/database/async_migrate.py and invoked through the application's lifespan context manager in api/main.py.

The Lifespan Hook: Triggering Migrations on Startup

In api/main.py, the @asynccontextmanager decorator defines a lifespan function (lines 98-105) that FastAPI calls during startup and shutdown events. Inside the startup block, the code instantiates AsyncMigrationManager to check for and execute any pending schema changes before the application begins accepting requests.

This design ensures the database schema is fully migrated before any API routes become available, preventing runtime errors caused by missing tables or columns.

Version Detection and Migration Discovery

The AsyncMigrationManager follows a two-step discovery process to determine if migrations are required.

Reading the Current Schema Version

The get_current_version() method queries the _sbl_migrations table using get_latest_version() (lines 71-80 in open_notebook/database/async_migrate.py) to retrieve the last applied migration sequence. If the table does not exist or contains no records, the version defaults to 0, indicating a fresh database state.

This version check represents the single source of truth for the database's current schema state.

Scanning Available Migration Files

The needs_migration() method compares the current version against the total number of available migration files. The manager pre-loads all SurrealQL migration files (such as 1.surrealql, 2.surrealql, etc.) from the open_notebook/database/migrations/ directory during initialization (lines 96-108).

If the current version is lower than the number of available migration files, the system identifies pending migrations that must be applied before the API can safely start.

Executing Pending Migrations

When needs_migration() returns True, the manager initiates the execution workflow to bring the database schema up to date.

The Migration Runner Workflow

The run_migration_up() method delegates to AsyncMigrationRunner.run_all() (lines 66-73), which iterates from the current version upward through each pending migration. For every migration, AsyncMigration.run() performs three critical operations:

1. Opens a connection to SurrealDB using the db_connection() helper from open_notebook/database/repository.py.
2. Executes the SQL by sending the cleaned-up SurrealQL statement via await connection.query(self.sql).
3. Updates the version record by calling bump_version() (or lower_version() for rollbacks) to maintain consistency in the _sbl_migrations table.

This sequence ensures each migration executes atomically and in correct order, maintaining schema integrity throughout the process.

Fail-Fast Error Handling

If any exception occurs during migration execution, the system logs the error and raises a RuntimeError, causing the API startup to abort immediately. This "fail-fast" design prevents the server from running with an outdated or partially migrated schema, which could lead to data corruption or unpredictable application behavior.

Post-Migration Confirmation

After successful migration completion, the lifespan handler logs the new schema version (lines 124-132 in api/main.py) and proceeds to load FastAPI routers. This confirmation step provides visibility into the applied changes before the application enters its ready state.

Practical Examples

Triggering Migrations Manually

For scripts, tests, or administrative tasks, you can manually invoke the migration manager outside of the standard startup flow:

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

Checking Current Schema Version

To inspect the current database 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

To extend the schema with new changes:

1. Create a new file in open_notebook/database/migrations/ following the numeric sequence (e.g., 15.surrealql if the last file is 14.surrealql).
2. Add the corresponding DDL/DML statements to the file.
3. Restart the API; the AsyncMigrationManager automatically detects and applies the new migration on startup.

Core Files and Components

The automatic migration system relies on these key files:

• open_notebook/database/async_migrate.py – Contains the core async migration classes (AsyncMigration, AsyncMigrationRunner, AsyncMigrationManager) and version bookkeeping logic.
• api/main.py – Defines the FastAPI entry point and the lifespan context manager that triggers migrations on startup.
• open_notebook/database/migrations/*.surrealql – Individual SurrealQL migration scripts (e.g., 1.surrealql, 2.surrealql) containing schema changes.
• open_notebook/database/repository.py – Provides the db_connection and repo_query utilities used by the migration runner to communicate with SurrealDB.

Summary

• Automatic execution: The AsyncMigrationManager runs inside the FastAPI lifespan context in api/main.py, ensuring migrations complete before the server accepts traffic.
• Version tracking: Schema versions are stored in the _sbl_migrations table, with get_current_version() defaulting to 0 when uninitialized.
• Sequential processing: The AsyncMigrationRunner applies migrations in order from the current version through the latest available file in open_notebook/database/migrations/.
• Fail-fast safety: Any migration error raises RuntimeError and aborts startup, preventing operation against an outdated schema.
• Idempotent design: The system checks needs_migration() before execution, allowing safe restarts without re-running applied migrations.

Frequently Asked Questions

What happens if a migration fails during API startup?

The migration system implements a fail-fast strategy. If any AsyncMigration.run() call raises an exception, the error is logged and re-raised as a RuntimeError, causing the API startup process to abort immediately. This prevents the server from running with an inconsistent or partially migrated schema.

How does AsyncMigrationManager track schema versions?

The manager queries the _sbl_migrations table via get_latest_version() to determine the current schema version. If the table does not exist, it defaults to version 0. After each successful migration, bump_version() updates this table to reflect the new state, creating a durable record of applied changes.

Can migrations be run manually outside of the automatic startup process?

Yes, you can instantiate AsyncMigrationManager directly in scripts or test suites. Call await manager.needs_migration() to check for pending changes, then await manager.run_migration_up() to apply them. This is useful for deployment scripts or database maintenance tasks that require explicit migration control.

Where should new SurrealQL migration files be placed?

New migration files belong in open_notebook/database/migrations/ and should follow the numeric naming convention (e.g., 15.surrealql). The AsyncMigrationManager automatically loads these files during initialization and includes them in the pending migration check when the API restarts.

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