Database Migration Patterns in OmniRoute's `src/lib/db/core.ts`

OmniRoute's src/lib/db/core.ts implements a versioned, idempotent migration system that combines inline schema initialization, tracked SQL migrations, and automatic corruption recovery to ensure SQLite database integrity across app updates.

OmniRoute is an open-source routing application that relies on a robust SQLite storage layer for local data persistence. The src/lib/db/core.ts file serves as the central orchestrator for database initialization, implementing sophisticated database migration patterns that handle everything from first-run setup to legacy data conversion and corruption recovery.

Versioned Migration Architecture

The migration system centers on a hybrid approach that guarantees schema existence before applying versioned updates.

Inline Base Schema Initialization

The SCHEMA_SQL constant (lines 189-454) contains a large multiline string that defines all core tables. This inline schema executes on every application start to ensure tables exist, even on fresh databases, making the initialization idempotent. The code seeds the _omniroute_migrations table with version 001 immediately after creating the base schema, establishing the foundation for subsequent version tracking.

Migration Tracking Table

The system creates a _omniroute_migrations table (lines 1101-1108) to record applied migrations. This table stores the version number, migration name, and timestamp for each executed script. By querying this table before running new migrations, the system ensures exactly-once execution semantics.

Migration Runner Integration

After the inline schema executes, runMigrations(db, { isNewDb }) is invoked (line 1110) to process files in the db/migrations/ directory. According to the OmniRoute source code, this runner:

• Reads the migration history from _omniroute_migrations
• Skips already-applied versions
• Executes pending .sql files inside a single transaction

The isNewDb parameter optimizes behavior for fresh installations versus existing databases.

Resilience and Recovery Patterns

Beyond version tracking, the file implements defensive patterns to protect against data corruption during migration failures.

Critical-State Snapshot and Restore

Before opening potentially corrupted databases, the code captures a snapshot of critical tables using captureCriticalDbState (lines 531-560). This function whitelists tables defined in CRITICAL_DB_TABLES and preserves their data in memory. If the database probe fails, restoreCriticalDbState (lines 618-641) reinserts this data into a fresh database file, ensuring zero user data loss during recovery operations.

Automatic Backup on Failure

When database probes detect corruption, the error handling block (lines 1048-1064) automatically renames the corrupted file to *.probe-failed-<timestamp> and creates a managed backup. The listProbeFailureBackups function (lines 997-1015) maintains a registry of these backups, enabling automatic restoration of the most recent valid state on subsequent starts.

Legacy and Data Migration Strategies

The system handles transitions from older storage formats and schema versions without manual intervention.

Legacy Schema Migration

For users upgrading from pre-v3 releases, the code detects the deprecated schema_migrations table (lines 889-916). The migration logic branches based on data presence: if the old table contains data, it is dropped after migration; if empty, the entire database file is renamed to prevent conflicts. This conditional cleanup ensures seamless upgrades while preserving data integrity.

JSON to SQLite Migration

When the application detects a legacy db.json file, the migrateFromJson function (lines 891-945) executes a one-off migration to import file-based storage into SQLite. This pattern facilitated OmniRoute's transition from JSON-based persistence to relational storage without requiring users to manually export or re-import their data.

Practical Implementation Examples

Adding a New Migration

Create a file in the migrations directory following the versioned naming convention:

-- db/migrations/002_add_new_table.sql
CREATE TABLE IF NOT EXISTS new_feature (
  id TEXT PRIMARY KEY,
  enabled INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

The migration runner automatically detects this file on the next startup and records version 002 in _omniroute_migrations after successful execution.

Manual Migration Execution

For testing or script environments, you can trigger migrations programmatically:

import { getDbInstance } from '@/src/lib/db/core';
import { runMigrations } from '@/src/lib/db/migrationRunner';

const db = getDbInstance();                 // ensures DB is opened
runMigrations(db, { isNewDb: false });      // re-run pending migrations

Triggering Critical-State Snapshots

Before performing risky database operations, capture the critical state:

import { captureCriticalDbState } from '@/src/lib/db/core';
import path from 'path';

const snapshot = captureCriticalDbState(path.join(DATA_DIR, 'storage.sqlite'));
// … perform risky modifications …
// If corruption occurs, restoreCriticalDbState(db, snapshot) recovers the data

Summary

• src/lib/db/core.ts serves as the central hub for database initialization, implementing inline schema creation and migration orchestration.
• Version tracking via _omniroute_migrations ensures migrations run exactly once, with runMigrations handling the execution logic.
• Resilience patterns including captureCriticalDbState and automatic probe-failure backups protect user data during corruption events.
• Legacy support handles upgrades from pre-v3 schema versions and JSON-based storage through automated detection and migration.
• Supporting files like src/lib/db/migrationRunner.ts and src/lib/db/schemaColumns.ts provide the execution engine and utility functions for the migration system.

Frequently Asked Questions

How does OmniRoute track which migrations have already been applied?

OmniRoute maintains an _omniroute_migrations table (created at lines 1101-1108 in src/lib/db/core.ts) that records the version number, migration name, and timestamp of each applied script. The runMigrations function in src/lib/db/migrationRunner.ts queries this table before executing any .sql files from the db/migrations/ directory, skipping versions that already exist in the tracking table.

What happens if the SQLite database becomes corrupted during an update?

When corruption is detected during the database probe, the system executes a recovery protocol defined in src/lib/db/core.ts (lines 1048-1064). It captures a snapshot of critical tables using captureCriticalDbState, renames the corrupted file with a *.probe-failed-<timestamp> suffix, and attempts to restore the most recent valid backup. If no backup exists, the critical state snapshot is restored to a fresh database file.

How does the system handle upgrades from older versions that used a different schema tracking table?

The code detects the legacy schema_migrations table (lines 889-916) and applies conditional logic: if the table contains data, it is migrated and dropped; if empty, the entire database is renamed to prevent conflicts. This ensures users upgrading from pre-v3 releases transition cleanly to the new _omniroute_migrations tracking system without manual intervention.

Can I manually trigger migrations outside of the normal application startup?

Yes, you can import runMigrations from src/lib/db/migrationRunner.ts and invoke it directly after obtaining a database instance via getDbInstance() from src/lib/db/core.ts. Pass { isNewDb: false } to ensure the runner checks the _omniroute_migrations table and applies only pending migrations, maintaining the same transactional guarantees as the automatic startup sequence.

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