How Open Notebook Handles SurrealDB Async Migration and Schema Setup at API Startup

The FastAPI lifespan coroutine triggers AsyncMigrationManager to detect the current schema version, compare it against available migration scripts, and execute pending SurrealQL migrations before the API accepts requests, aborting startup if any migration fails.

The lfnovo/open-notebook repository implements a robust asynchronous migration system for SurrealDB that runs automatically during application initialization. When the FastAPI service starts, the lifespan context manager in api/main.py orchestrates schema validation and migration execution, ensuring the database schema matches the application's expectations before handling traffic.

The Lifespan Startup Hook

Every API startup begins in api/main.py where the lifespan coroutine serves as the central orchestrator. This hook executes after environment variables load and security checks complete, but before the application accepts incoming requests.

The lifespan sequence:

1. Creates an AsyncMigrationManager instance
2. Queries the current schema version from SurrealDB
3. Determines if migrations are required
4. Executes pending migrations sequentially
5. Logs the final version or aborts startup on failure

If any step fails, the API raises a RuntimeError and prevents the service from starting with an outdated schema, eliminating data inconsistency risks.

AsyncMigrationManager Core Logic

The migration engine resides in open_notebook/database/async_migrate.py. This module provides the AsyncMigrationManager class that coordinates version detection, comparison, and execution.

Schema Version Detection

The manager identifies the current database state through get_latest_version() (lines 199‑205). This function queries the hidden _sbl_migrations table using repo_query to retrieve the stored version number.

If the _sbl_migrations table does not exist—such as during initial deployment—the system defaults to version 0. This zero-based default ensures that fresh installations automatically run all available migrations from the beginning.

Migration Execution Flow

When needs_migration() determines that the stored version differs from the number of available up_migrations scripts, the manager triggers run_migration_up() (lines 66‑73). This method delegates to self.runner.run_all(), which iterates through pending migrations and executes each AsyncMigration.run coroutine.

Individual migrations follow this strict sequence:

• Open a temporary SurrealDB connection via db_connection() from open_notebook/database/repository.py:47‑62
• Execute the raw SurrealQL using await connection.query(self.sql)
• Increment the version counter via bump_version() upon successful completion

This transactional approach ensures that each schema change commits independently before the next migration begins.

Migration File Structure and Format

Migration scripts reside in open_notebook/database/migrations/ as plain SurrealQL files following the naming convention {version}.surrealql (for example, 1.surrealql, 2.surrealql). The system also supports optional rollback scripts named {version}_down.surrealql.

The AsyncMigration.from_file static method (lines 22‑35) handles file ingestion by:

• Reading the SurrealQL file contents
• Stripping comments from the SQL
• Storing the cleaned query for execution

This design allows developers to add new schema changes by simply creating numbered files without modifying application code.

Error Handling and Safety Guarantees

The migration system implements fail-fast semantics to protect data integrity. If any migration query fails during execution, the error propagates to the lifespan coroutine in api/main.py, which immediately raises a RuntimeError and halts API startup.

This guarantee ensures that:

• The service never runs against a partially migrated schema
• Administrators receive immediate feedback about database configuration issues
• Failed migrations block traffic until the schema discrepancy resolves

Code Examples

Running Migrations Manually

For administrative scripts or maintenance tasks, you can invoke the migration manager directly:

from open_notebook.database.async_migrate import AsyncMigrationManager

async def migrate():
    manager = AsyncMigrationManager()
    if await manager.needs_migration():
        await manager.run_migration_up()
        print("Migrations applied successfully")
    else:
        print("Database already at latest version")

Creating a New Migration

To add a schema change, create a numbered SurrealQL file:

-- 15.surrealql: add podcast episodes table
CREATE table podcast_episode;

Optionally create a rollback script:

-- 15_down.surrealql: remove podcast episodes table
REMOVE table podcast_episode;

The manager automatically detects and sequences any new numbered files on the next startup.

Checking Current Version

Inspect the database version programmatically:

from open_notebook.database.async_migrate import get_latest_version

current = await get_latest_version()
print(f"Current DB version: {current}")

Summary

• Lifespan orchestration: api/main.py triggers migrations during FastAPI startup before accepting requests
• Version tracking: get_latest_version() queries the _sbl_migrations table, defaulting to 0 for new databases
• Sequential execution: AsyncMigrationManager runs migrations from open_notebook/database/migrations/ in numeric order
• Connection handling: Each migration opens a temporary connection via db_connection() from the repository module
• Safety mechanism: Startup aborts with RuntimeError if any migration fails, preventing service initialization with inconsistent schemas

Frequently Asked Questions

What happens if the SurrealDB migrations table is missing?

If the _sbl_migrations table does not exist, get_latest_version() returns 0 as the default version. This causes AsyncMigrationManager to execute all available migration scripts sequentially, effectively performing a fresh installation of the entire schema history.

Can I run migrations outside of the API startup process?

Yes, you can instantiate AsyncMigrationManager and call needs_migration() followed by run_migration_up() in any async context, such as administrative scripts or maintenance tasks. The migration engine operates independently of the FastAPI lifespan once imported.

How does the system handle failed migrations?

If a migration query fails, the AsyncMigration.run method raises an exception that propagates to the lifespan coroutine in api/main.py. This triggers a RuntimeError that aborts API startup, ensuring the service never initializes with a partially applied schema. You must fix the migration file or database state before the API will start.

What is the difference between the async and sync migration files?

The repository includes open_notebook/database/async_migrate.py for modern asynchronous operations and open_notebook/database/migrate.py as a synchronous wrapper for backward compatibility. The FastAPI application exclusively uses the async version during startup, while the sync version remains available for legacy tooling or blocking contexts.

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