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

> Master automatic database migrations and custom migrations in Open Notebook. Effortlessly update your SurrealQL schema and transform legacy data with Python scripts on API startup.

- Repository: [Luis Novo/open-notebook](https://github.com/lfnovo/open-notebook)
- Tags: how-to-guide
- Published: 2026-07-05

---

**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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/podcasts/migration.py). The `migrate_podcast_profiles()` function executes after schema migrations complete in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/tests/test_startup_migration_retry.py) to verify the migration runs exactly once and that errors propagate correctly.

```sql
-- 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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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.

```python

# 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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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`](https://github.com/lfnovo/open-notebook/blob/main/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.