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

> Explore OmniRoute's src/lib/db/core.ts for robust database migration patterns. Discover versioned, idempotent SQL migrations, inline schema initialization, and automatic corruption recovery for SQLite.

- Repository: [Diego Rodrigues de Sa e Souza/OmniRoute](https://github.com/diegosouzapw/OmniRoute)
- Tags: architecture
- Published: 2026-07-03

---

**OmniRoute's [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

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

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

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) and [`src/lib/db/schemaColumns.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) and invoke it directly after obtaining a database instance via `getDbInstance()` from [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.