SurrealDB Database Migration Structure in Open Notebook: Version-Controlled Schema Management

Open Notebook implements a self-contained, version-driven migration system using ordered SurrealQL scripts and a _sbl_migrations tracking table to manage SurrealDB schema changes asynchronously with automatic startup verification.

Open Notebook uses SurrealDB as its primary data store, requiring a robust strategy for evolving database schemas without data loss. The lfnovo/open-notebook repository implements a lightweight yet comprehensive database migration structure that treats schema changes as version-controlled code, ensuring idempotent and ordered deployments across environments.

Core Components of the Migration System

Migration Files in open_notebook/database/migrations/

The migration layer stores plain SurrealQL scripts in the open_notebook/database/migrations/ directory. Each file follows a numbered naming convention (e.g., 1.surrealql, 2.surrealql) representing incremental schema changes. Optional rollback scripts use the *_down.surrealql suffix (e.g., 19_down.surrealql), containing DROP TABLE or REMOVE FIELD statements to reverse specific changes.

The _sbl_migrations Version Table

SurrealDB maintains a dedicated _sbl_migrations table that records applied versions and timestamps. The system treats a missing table as version 0, enabling clean installations on fresh databases. The bump_version() function in async_migrate.py inserts rows after successful migrations, while lower_version() removes entries during rollbacks.

The Async Migration Engine

AsyncMigration Class

The AsyncMigration class in open_notebook/database/async_migrate.py (lines 13-34) encapsulates individual migration scripts. It strips comments from SurrealQL files and executes them against the database connection. Upon successful execution, it triggers version tracking updates.

AsyncMigrationRunner

The AsyncMigrationRunner class (lines 52-88 in async_migrate.py) orchestrates the execution flow. It maintains ordered lists of "up" and "down" migrations, providing methods to run all pending migrations, single up migrations, or single down migrations. This class handles the actual database transaction logic.

AsyncMigrationManager

The high-level AsyncMigrationManager class (lines 90-128 in async_migrate.py) builds the migration queue and determines whether schema changes are needed. It implements needs_migration() to compare the current database version against available migration files, and provides a ping() method used during API health checks.

Synchronous Wrapper for Legacy Code Paths

For synchronous contexts such as legacy tests or synchronous scripts, the MigrationManager class in open_notebook/database/migrate.py wraps AsyncMigrationManager. This facade allows non-async code to invoke the migration system without modifying existing synchronous call stacks.

Startup Integration and API Lifecycle

The migration system integrates into the application lifespan via api/main.py. During API startup, the lifespan hook invokes MigrationManager().run_migration_up(), ensuring the database schema reaches the latest version before serving any requests. This prevents runtime errors caused by schema mismatches.

How SurrealDB Schema Changes Are Applied

The migration process follows a strict sequence:

1. Version Detection: get_latest_version() queries the _sbl_migrations table. If the table does not exist, the version defaults to 0.
2. Need Assessment: needs_migration() compares the detected version against the count of available migration files in the directory.
3. Sequential Application: run_migration_up() iterates from the current version to the latest available script, executing each via AsyncMigration.run(bump=True).
4. Version Tracking: After each successful script execution, bump_version() inserts a new row into _sbl_migrations with the current timestamp.
5. Rollback Capability: Calling run_one_down() executes the corresponding down-migration script and invokes lower_version() to remove the latest version entry.

Practical Implementation Examples

Creating a New Migration

To add a schema change, create a new numbered SurrealQL file in the migrations directory:

# Create: open_notebook/database/migrations/19.surrealql

# Content:

# -- Add a new table for podcast transcripts

# CREATE TABLE podcast_transcript;

# DEFINE FIELD transcript TEXT;

# DEFINE FIELD created_at DATETIME DEFAULT time::now();

# Optional rollback: open_notebook/database/migrations/19_down.surrealql

# Content:

# DROP TABLE podcast_transcript;

The AsyncMigrationManager automatically detects new files when building the migration list, requiring no additional registration code.

Running Migrations Manually

For maintenance scripts or CLI tools, use the synchronous wrapper:

from open_notebook.database.migrate import MigrationManager

def migrate():
    mgr = MigrationManager()
    if mgr.needs_migration:
        print("Applying pending SurrealDB migrations…")
        mgr.run_migration_up()
        print("Migrations complete.")
    else:
        print("Database already up-to-date.")

if __name__ == "__main__":
    migrate()

Checking the Current Database Version

To verify the current schema version programmatically:

from open_notebook.database.async_migrate import get_latest_version

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

Rolling Back Changes

For emergency rollbacks, execute the down-migration:

from open_notebook.database.async_migrate import AsyncMigrationManager

async def rollback_one():
    manager = AsyncMigrationManager()
    await manager.runner.run_one_down()
    print("Rolled back the most recent migration.")

Summary

• Open Notebook stores SurrealDB schema changes as numbered *.surrealql files in open_notebook/database/migrations/.
• The _sbl_migrations table tracks applied versions, treating missing tables as version 0 for fresh installs.
• AsyncMigrationManager in async_migrate.py orchestrates the async migration pipeline, while MigrationManager in migrate.py provides a synchronous interface.
• The API startup sequence in api/main.py automatically applies pending migrations before accepting requests.
• Each migration runs sequentially and is tracked individually, supporting both forward migrations and optional rollbacks.

Frequently Asked Questions

How does the migration system handle fresh database installations?

When connecting to a new SurrealDB instance, get_latest_version() detects the absence of the _sbl_migrations table and defaults to version 0. The system then applies all available migration scripts sequentially, building the schema from scratch.

Can I skip specific migration versions when updating the schema?

No, the AsyncMigrationRunner enforces sequential execution. It iterates from the current database version to the latest available script number, ensuring that schema changes apply in the order they were created to prevent dependency conflicts.

What happens if a migration script fails during execution?

The migration system applies scripts individually. If a script fails, the exception propagates before bump_version() is called, leaving the database at the last successful version. This prevents partial schema states and allows for retry logic upon the next startup.

Is there a way to run migrations outside of the API startup process?

Yes, the MigrationManager class in open_notebook/database/migrate.py provides a synchronous interface suitable for CLI tools or maintenance scripts. Instantiate it and call run_migration_up() to apply pending changes manually without starting the full API server.

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