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

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, 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:

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 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, 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):

-- 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:

-- 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:

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	FastAPI entry point; registers the lifespan hook that triggers migrations at startup (lines 98-103, 133-138).	api/main.py
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
open_notebook/database/migrations/*.surrealql	Ordered SurrealQL scripts stored as versioned files (e.g., 1.surrealql through 15.surrealql).	Migrations folder
open_notebook/podcasts/migration.py	Legacy data migration module for podcast profile strings, executed after schema migrations.	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.

What happens if a migration fails during API startup?

The application implements fail-fast error handling at lines 133-138 in 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). 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.

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