# Automatic Database Migrations on API Startup in Open Notebook: How SurrealDB Schema Changes Run Automatically

> Open Notebook uses an AsyncMigrationManager for automatic SurrealDB schema migrations on FastAPI startup. Ensure your database is up-to-date before accepting traffic.

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

---

**Open Notebook automatically executes SurrealDB schema migrations on FastAPI startup using an `AsyncMigrationManager` that detects the current version from the `_sbl_migrations` table and runs pending SurrealQL scripts before the API accepts traffic.**

The FastAPI lifespan architecture in Open Notebook eliminates manual migration steps by embedding database synchronization directly into the server boot sequence. When the application starts, it initializes a migration manager that compares the database state against versioned scripts, applies missing changes atomically, and handles legacy data transformations—all without developer intervention.

## How the Migration System Works on Startup

The automatic migration pipeline leverages FastAPI's lifespan context manager to guarantee schema consistency before opening HTTP endpoints.

### Lifespan Hook Registration

In [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), the lifespan event handler is registered during FastAPI instantiation at lines 98-103. This hook ensures migrations execute strictly during the startup phase, preventing race conditions while the API serves traffic.

The `AsyncMigrationManager` is instantiated at line 18:

```python
migration_manager = AsyncMigrationManager()

```

This constructor automatically discovers all SurrealQL scripts in `open_notebook/database/migrations/` and prepares them for ordered execution.

### Version Detection and Execution

The migration flow in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) follows a strict protocol:

1. **Read current version**: `await migration_manager.get_current_version()` queries the hidden `_sbl_migrations` table (implemented at lines 204-211).
2. **Check pending work**: `await migration_manager.needs_migration()` compares the stored version against available scripts (lines 81-84).
3. **Run scripts**: `await migration_manager.run_migration_up()` iterates through missing versions, invoking `AsyncMigration.run()` for each file (lines 86-95).
4. **Update metadata**: After successful execution, `bump_version()` inserts a new row into `_sbl_migrations` (lines 26-34).

Scripts execute in strict numeric order (e.g., `1.surrealql`, `2.surrealql`, `15.surrealql`), ensuring deterministic schema evolution from initial creation through the latest structural changes.

### Error Handling and Safety Guarantees

The startup sequence implements fail-fast semantics. Any exception during migration propagation aborts at lines 133-138 in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py), preventing the FastAPI application from fully initializing. This ensures the API never runs against an outdated or partially migrated schema.

After structural migrations complete, the system executes `migrate_podcast_profiles()` at lines 39-45 to handle data migrations separately—rewriting legacy provider strings to the new model registry without blocking schema changes.

## The Migration Script Format

Open Notebook stores migrations as raw SurrealQL files in `open_notebook/database/migrations/`:

- **Forward migrations**: Named sequentially as `NN.surrealql` (where `NN` is the next integer)
- **Rollback scripts**: Optional `NN_down.surrealql` files that reverse specific changes

The `AsyncMigrationManager.__init__` method automatically discovers these files at runtime, requiring no manual registration or configuration updates when adding new schema versions.

## Adding a New Migration

Extending the database schema requires only file creation—no Python code changes:

1. Create `open_notebook/database/migrations/16.surrealql` (replace `16` with the next available integer):

```sql
-- open_notebook/database/migrations/16.surrealql
DEFINE TABLE new_resources SCHEMAFULL;
DEFINE FIELD created_at ON new_resources TYPE datetime DEFAULT time::now();
DEFINE INDEX idx_created ON new_resources COLUMNS created_at;

```

2. Optionally create `16_down.surrealql` for rollback capability:

```sql
-- open_notebook/database/migrations/16_down.surrealql
REMOVE INDEX idx_created ON new_resources;
REMOVE TABLE new_resources;

```

3. Commit the files. The next API restart automatically detects and applies the migration via `AsyncMigrationManager`.

## Running Migrations Independently of the API

For testing, CLI utilities, or deployment scripts, invoke the migration engine directly without starting the full FastAPI server:

```python
import asyncio
from open_notebook.database.async_migrate import AsyncMigrationManager

async def run_migrations():
    manager = AsyncMigrationManager()
    cur = await manager.get_current_version()
    print(f"Current DB version: {cur}")

    if await manager.needs_migration():
        print("Pending migrations found – applying...")
        await manager.run_migration_up()
        print(f"Updated to version {await manager.get_current_version()}")
    else:
        print("Database already up‑to‑date.")

if __name__ == "__main__":
    asyncio.run(run_migrations())

```

This script executes the identical logic as the production lifespan hook, making it suitable for CI/CD pipelines or database maintenance operations.

## Key Source Files and References

| File | Role | Direct Link |
|------|------|-------------|
| [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) | FastAPI entry point; registers the lifespan hook that triggers migrations at startup (lines 98-103, 133-138). | [api/main.py](https://github.com/lfnovo/open-notebook/blob/main/api/main.py) |
| [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) | Core async migration engine containing `AsyncMigrationManager`, `AsyncMigrationRunner`, and `AsyncMigration` classes (lines 26-34, 81-95, 204-211). | [async_migrate.py](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py) |
| `open_notebook/database/migrations/*.surrealql` | Ordered SurrealQL scripts stored as versioned files (e.g., `1.surrealql` through `15.surrealql`). | [Migrations folder](https://github.com/lfnovo/open-notebook/tree/main/open_notebook/database/migrations) |
| [`open_notebook/podcasts/migration.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/podcasts/migration.py) | Legacy data migration module for podcast profile strings, executed after schema migrations. | [podcasts/migration.py](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/podcasts/migration.py) |

## Summary

- **Zero-touch deployments**: The FastAPI lifespan hook automatically triggers `AsyncMigrationManager` on every startup, eliminating manual `migrate` commands.
- **Immutable version tracking**: The `_sbl_migrations` table persists state via `get_current_version()` and `bump_version()`, creating an audit trail of applied changes.
- **Ordered execution**: Files in `open_notebook/database/migrations/` run sequentially through `run_migration_up()`, ensuring predictable schema evolution.
- **Fail-safe architecture**: Startup aborts if any migration raises an exception (lines 133-138), preventing API operation against inconsistent databases.
- **Dual-phase processing**: Structural schema migrations execute first, followed by optional data migrations like `migrate_podcast_profiles()` for legacy record handling.

## Frequently Asked Questions

### Does Open Notebook require running manual migration commands before starting the API?

No. The system automatically detects and applies pending migrations during the FastAPI lifespan startup sequence. You only need to place new `.surrealql` files in `open_notebook/database/migrations/` and restart the server; the `AsyncMigrationManager` handles the rest according to the logic in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py).

### What happens if a migration fails during API startup?

The application implements fail-fast error handling at lines 133-138 in [`api/main.py`](https://github.com/lfnovo/open-notebook/blob/main/api/main.py). Any exception raised during `run_migration_up()` propagates to the lifespan context, preventing the FastAPI application from fully initializing. This ensures the server never runs with a partially migrated schema or incomplete database state.

### How does the system track which migrations have already been applied?

The `AsyncMigrationManager` queries the hidden `_sbl_migrations` table using `get_current_version()` (defined at lines 204-211 in [`open_notebook/database/async_migrate.py`](https://github.com/lfnovo/open-notebook/blob/main/open_notebook/database/async_migrate.py)). After each successful script execution, `bump_version()` inserts a new record documenting the completed migration, creating an immutable audit trail that persists across API restarts.

### Can migrations be run outside of the normal API startup process?

Yes. Instantiate `AsyncMigrationManager` directly and await `run_migration_up()` in any async Python context. The standalone code example demonstrates how to check versions and apply migrations outside the FastAPI lifespan, making it suitable for CI/CD pipelines, testing environments, or database maintenance scripts that cannot launch the full HTTP server.