How SurrealDB Handles Automatic Schema Migrations on API Startup in Open Notebook

Open Notebook automatically executes SurrealDB schema migrations during FastAPI startup using an AsyncMigrationManager that reads the current version from a _sbl_migrations table, compares it against numbered SurrealQL scripts in the migrations directory, and runs pending upgrades sequentially before the application accepts requests.

The lfnovo/open-notebook repository implements a fully asynchronous migration system that ensures the SurrealDB database schema stays synchronized with application code. By hooking into the FastAPI lifespan coroutine defined in api/main.py, the system guarantees schema consistency every time the API service launches, preventing runtime errors caused by outdated database structures.

Core Migration Architecture

The migration system centers on three primary components working together during the startup sequence:

• AsyncMigrationManager – Orchestrates the detection and execution logic
• Numbered SurrealQL files – Store schema changes in open_notebook/database/migrations/
• _sbl_migrations table – Tracks the current schema version in SurrealDB

According to the source code in open_notebook/database/async_migrate.py, the manager loads migration scripts automatically and determines which ones need execution based on version comparison logic.

Step-by-Step Migration Process

When the API starts, the lifespan coroutine triggers a three-phase migration workflow:

Step 1: Detect Current Schema Version

The get_latest_version() function queries the hidden _sbl_migrations table via repo_query to determine the database's current state. As implemented in open_notebook/database/async_migrate.py:199‑205, the function returns the stored version number or defaults to 0 if the table does not exist yet.

from open_notebook.database.async_migrate import get_latest_version

current = await get_latest_version()
print(f"Current DB version: {current}")  # Returns 0 on fresh database

Step 2: Determine Migration Requirements

The needs_migration() method compares the detected version against the number of available migration scripts loaded into self.up_migrations. If the stored version is less than the total available migrations, the system identifies which scripts require execution.

# Internally implemented at async_migrate.py:175‑179

if current_version < len(self.up_migrations):
    return True  # Migration required

Step 3: Execute Pending Migrations

The run_migration_up() method calls self.runner.run_all(), which iterates through pending migrations and executes each AsyncMigration.run() coroutine. Each migration:

1. Opens a temporary SurrealDB connection using db_connection() from open_notebook/database/repository.py:47‑62
2. Executes the raw SurrealQL via await connection.query(self.sql)
3. Calls bump_version() to atomically update the version tracking table upon success

This sequential execution ensures schema changes apply in order, with the version bumping only occurring after successful query completion.

Migration File Structure and Discovery

Migration files reside in open_notebook/database/migrations/ and follow a strict naming convention:

• Up migrations: 1.surrealql, 2.surrealql, 3.surrealql (sequential integers)
• Down migrations (optional): 1_down.surrealql, 2_down.surrealql

The AsyncMigration.from_file() class method (lines 22‑35 in async_migrate.py) reads these files, strips comments, and stores the cleaned SQL for execution. Adding a new migration requires no code changes—simply create the next numbered file:

-- open_notebook/database/migrations/15.surrealql
CREATE table podcast_episode;
DEFINE FIELD title ON podcast_episode TYPE string;

FastAPI Lifespan Integration

The migration trigger lives in the lifespan coroutine within api/main.py. During startup (lines 178‑190), the code:

1. Instantiates AsyncMigrationManager
2. Calls await manager.get_latest_version() to query the current state
3. Checks await manager.needs_migration() to determine if work is required
4. Executes await manager.run_migration_up() if migrations are pending
5. Logs the final version or "already up-to-date" status

If any migration fails, the system raises a RuntimeError that aborts API startup, ensuring the service never runs against an incompatible schema.

Error Handling and Safety Guarantees

The migration system implements strict safety measures:

• Atomic version tracking: The bump_version() function updates _sbl_migrations only after successful query execution
• Startup blocking: Failures in run_migration_up() propagate to the lifespan coroutine, preventing the FastAPI application from initializing
• Connection isolation: Each migration uses a fresh connection via db_connection(), preventing connection state pollution between migrations

Practical Usage Examples

Running Migrations Manually

You can trigger migrations outside of the standard startup flow using the 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")

Adding a New Schema Change

1. Create open_notebook/database/migrations/16.surrealql:

-- Add indexes for performance
DEFINE INDEX idx_note_created ON note FIELDS created_at;

2. Optionally create 16_down.surrealql for rollback support
3. Restart the API—the AsyncMigrationManager automatically detects and executes the new script

Summary

• Automatic detection: The AsyncMigrationManager queries the _sbl_migrations table on startup to determine current schema version
• Sequential execution: Migrations run in order from 1.surrealql through the latest numbered file, with each query executing in its own database connection
• Zero-downtime safety: The API aborts startup if migrations fail, preventing code/schema mismatches
• File-based management: Simply add numbered SurrealQL files to open_notebook/database/migrations/ without modifying Python code
• Async-first design: All migration logic uses async/await patterns compatible with FastAPI's asynchronous architecture

Frequently Asked Questions

What happens if a migration fails during startup?

The system raises a RuntimeError in the lifespan coroutine at api/main.py, which prevents the FastAPI application from completing startup. This ensures the service never runs with a partially applied or outdated schema. You must fix the failing SurrealQL syntax or database issue before the API can start.

How do I add a new schema migration to Open Notebook?

Create a new SurrealQL file in open_notebook/database/migrations/ using the next sequential number (e.g., 17.surrealql). Write your schema changes using standard SurrealQL syntax. Optionally create a matching 17_down.surrealql file for rollback support. The AsyncMigrationManager automatically detects new files on the next API startup.

Where does SurrealDB store the current migration version?

Open Notebook tracks versions in a hidden table named _sbl_migrations within your SurrealDB database. The get_latest_version() function queries this table via repo_query, and bump_version() updates it after each successful migration. If the table is missing, the system assumes version 0 and creates the table during the first migration run.

Can I run migrations manually outside of the API startup?

Yes. Import AsyncMigrationManager from open_notebook.database.async_migrate and call await manager.run_migration_up() within an async context. This uses the same code path as the automatic startup sequence, querying the current version and executing any pending migrations from the open_notebook/database/migrations/ directory.

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