Automatic Database Migrations and Custom Migrations in Open Notebook: A Complete Guide

Open Notebook uses an asynchronous migration engine that automatically applies SurrealQL schema scripts on every API startup, followed by optional Python-based custom data migrations to transform legacy records without manual intervention.

The lfnovo/open-notebook repository implements a self-healing database migration system that ensures the SurrealDB schema stays synchronized with the application code. By combining automatic schema versioning with extensible custom data migrations, the project eliminates manual database updates while safely handling data transformations after structural changes.

How the Automatic Migration Engine Works

The migration system is orchestrated through FastAPI’s lifespan handler and a dedicated AsyncMigrationManager class. This engine runs every time the API starts, probing the database, detecting version drift, and applying pending changes before the application serves its first request.

API Startup and Connection Probing

In api/main.py, the lifespan function creates an AsyncMigrationManager instance and waits for SurrealDB to become reachable. The _wait_for_database() method implements an exponential back-off strategy, retrying the database ping up to 12 times with delays increasing from 1 to 5 seconds. If the database never responds, the API aborts immediately with a RuntimeError, preventing the application from starting with an invalid connection.

Version Detection and Migration Logic

Once connected, the engine determines if migration is required. The get_current_version() method in open_notebook/database/async_migrate.py reads the migration_version record stored directly in SurrealDB. The needs_migration() function compares this stored version against the latest migration script index in the filesystem. If the latest script number is greater than the database version, the system flags a migration as required.

Applying Schema Migrations

When needs_migration() returns true, the run_migration_up() method executes each pending SurrealQL script in sequential order. Each migration resides in open_notebook/database/migrations/ as a numbered file (e.g., 12.surrealql). The engine wraps each script in a transaction, ensuring atomic schema changes. After successful execution, the API logs the new version; if any script fails, the API raises a RuntimeError and halts, preventing partial schema updates.

Custom Data Migrations in Open Notebook

Beyond schema changes, Open Notebook supports custom data migrations—Python functions that run after the schema is fully up-to-date. These migrations handle data transformations that cannot be expressed in SurrealQL alone.

The Podcast Profile Migration Example

The only built-in custom migration today resides in open_notebook/podcasts/migration.py. The migrate_podcast_profiles() function executes after schema migrations complete in api/main.py. It reads legacy provider and model strings from existing podcast records, maps them to new model-registry identifiers, and writes the transformed profiles back to the database. Because it runs after the schema upgrade, it safely relies on the new table structures already being in place.

Error Handling Differences

Custom migrations follow a different failure policy than schema migrations. While schema migration errors are fatal and stop the API, custom migration errors are caught, logged, and allowed to pass. This design ensures that data transformation issues do not prevent the application from starting, allowing administrators to troubleshoot data issues without downtime.

Adding New Automatic Schema Migrations

Creating a new schema migration requires no changes to the Python codebase. The AsyncMigrationManager discovers migration files dynamically at import time.

1. Create a SurrealQL file in open_notebook/database/migrations/ with the next integer index (e.g., 19.surrealql). Optionally include a rollback script named 19_down.surrealql.

2. Update the migration manager requires no action. The manager automatically loads all .surrealql files in the directory.

3. Write a test similar to tests/test_startup_migration_retry.py to verify the migration runs exactly once and that errors propagate correctly.

-- open_notebook/database/migrations/19.surrealql
BEGIN TRANSACTION;

-- Add a new field to the 'notebook' record
DEFINE TABLE notebook SCHEMAFULL;
ALTER TABLE notebook ADD FIELD thumbnail STRING;

COMMIT TRANSACTION;

Implementing Custom Data Migrations

To add a custom data migration for a new feature, follow the pattern established by the podcast migration.

1. Create a new module under open_notebook/ (e.g., myfeature/migration.py).

2. Expose an async function named migrate_<feature>_profiles() that accepts or instantiates a SurrealDB client via the AsyncMigrationManager.

3. Import and call the function from api/main.py inside the lifespan block, placing it after the _run_database_migrations() call.

4. Handle errors with a try/except block that logs the exception but allows the API to continue, mirroring the podcast migration pattern.

# myfeature/migration.py

from open_notebook.database.async_migrate import AsyncMigrationManager

async def migrate_myfeature_profiles() -> None:
    manager = AsyncMigrationManager()
    # Ensure DB is reachable (skip if already called in startup)

    await manager.ping()
    # Perform data transformation...

    # await manager.db.query("UPDATE myfeature SET new_field = old_field;")

Testing Migration Behavior

The repository includes comprehensive tests for migration reliability. The file tests/test_startup_migration_retry.py validates that the API retries the database probe with exponential back-off, executes migrations exactly once per startup, and aborts on unrecoverable schema errors. When adding custom migrations, create similar unit tests that mock the migration function and assert it is called after _run_database_migrations() completes.

Summary

• Automatic migrations run on every API startup via AsyncMigrationManager in api/main.py, ensuring the SurrealDB schema matches the codebase.
• Schema version tracking uses a migration_version record in SurrealDB, compared against numbered SurrealQL files in open_notebook/database/migrations/.
• Connection resilience is handled by _wait_for_database(), which implements exponential back-off before allowing migrations to proceed.
• Custom data migrations execute after schema updates, with migrate_podcast_profiles() in open_notebook/podcasts/migration.py serving as the reference implementation.
• Failure handling differs by type: schema migration failures halt the API (RuntimeError), while custom migration errors are logged but non-fatal.
• Adding migrations requires only creating new SurrealQL files for schema changes, or Python modules for data transformations, with no registry updates needed.

Frequently Asked Questions

What happens if the database is unreachable when Open Notebook starts?

The API calls _wait_for_database() with exponential back-off, retrying up to 12 times with delays ranging from 1 to 5 seconds. If the database remains unreachable, the API raises a RuntimeError and aborts startup, preventing the application from running with an invalid database state.

How does Open Notebook decide which migrations to run?

The AsyncMigrationManager compares the migration_version stored in SurrealDB against the highest-numbered .surrealql file in open_notebook/database/migrations/. If the file index exceeds the stored version, the system executes all pending scripts in numerical order using run_migration_up().

Can I roll back a schema migration in Open Notebook?

Yes, by creating a down-migration script suffixed with _down (e.g., 19_down.surrealql) in the migrations directory. The migration engine recognizes these files, though the current implementation in async_migrate.py focuses on forward migrations; you would extend the run_migration_down() logic to invoke these scripts.

Why do custom migrations run after schema migrations instead of during?

Custom migrations run after schema updates to ensure they operate on the final table structure. This sequencing prevents data transformation errors caused by missing columns or tables, and allows the API to start even if custom data transformations fail, since schema integrity is already guaranteed.

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