From Offline-First to Local-First: Schema Migrations on Distributed SQLite Databases

When building a mobile application, the easiest database migration you will ever run is the one on your backend.

On a server, you control the deployment timeline. You put the API in maintenance mode or run an expand-contract migration, run php artisan migrate, verify integrity against your PostgreSQL or MySQL cluster, and bring traffic back. If something goes wrong, you inspect server logs in real time and roll back within minutes.

On mobile devices, none of those luxuries exist.

Once you ship an app with an embedded database like SQLite, you are no longer managing a centralized database. You are managing tens of thousands of autonomous, disconnected database nodes distributed across user pockets around the world.

A customer might install your app, draft five offline records, put the device in airplane mode, forget about it for six months, and then update from version 1.2 directly to version 2.4 while sitting on a spotty cellular connection.

If your database migration crashes on app boot, the app enters a crash loop. If your migration script naively drops a table to recreate it with new columns, you have just permanently destroyed the user’s un-synced local work.

Moving from simple offline caching to true local-first architecture requires treating SQLite migrations with the same rigor you would treat a distributed database consensus protocol.

Offline-first vs. Local-first: The architectural shift

Most mobile apps are built as offline-tolerant caches:

• The backend database is the sole source of truth.
• The phone stores a read-only or read-mostly cache in SQLite or key-value storage.
• If a migration fails or data gets out of sync, the nuclear option is harmless: clear cache, re-fetch from /api/v1/sync, and redraw the screen.

In local-first architecture, that safety net disappears:

• The local SQLite database is the primary source of truth for all local mutations.
• The user can create, update, and delete complex records indefinitely without an internet connection.
• A background synchronization worker pushes mutations to a sync broker and pulls down remote CRDTs or change vectors.

Because the local database holds writes that have not yet reached your servers, a schema migration on the device must never be destructive. You cannot drop tables, you cannot wipe columns that hold un-synced changes, and you cannot assume the database was on the immediately preceding version.

The constraints of embedded SQLite

Before designing a migration runner, you have to respect SQLite’s specific runtime constraints on mobile platforms:

1. Primitive ALTER TABLE capabilities: Older SQLite engines shipped with legacy Android and iOS builds do not support dropping columns, renaming columns, or modifying foreign key constraints without creating a temporary table, copying data, dropping the old table, and renaming.
2. Synchronous execution during startup: Database migrations usually run when the app initializes its database connection during launch. If a migration takes 8 seconds to run because it touches 200,000 rows without proper index planning, the operating system watchdog (like iOS’s “watchdog timer”) will terminate your app process for taking too long to launch.
3. No server rollback: If an exception is thrown halfway through an un-transactional migration on a user’s device, that database is left in a corrupted intermediate state. You cannot deploy a hotfix that rolls back the phone’s storage.

The sequential migration loop

The foundation of robust local SQLite migration is tracking schema version via SQLite’s built-in user_version pragma:

PRAGMA user_version;

This integer stored in the database file header is completely independent of your tables and updates atomically inside transactions.

Never write “jump” migrations (e.g. migrating directly from v1 to v4). Always write atomic, step-by-step transitions: $1 \to 2$, $2 \to 3$, $3 \to 4$.

[ Installed Device: Schema v2 ]
               │
               ▼
       [ Step 1: v2 → v3 ]  (Additive column change)
               │
               ▼
       [ Step 2: v3 → v4 ]  (Outbox table creation)
               │
               ▼
    [ Target: Schema v4 reached ]

A user upgrading from v1 to v4 executes all three migration steps sequentially within a single startup sequence. If a user is already on v3, they only execute the final step.

Implementing the migration runner in Dart / Flutter

In Flutter using a typed SQLite layer (like Drift or raw sqflite), the migration strategy should be structured explicitly:

import 'package:sqflite/sqflite.dart';

class DatabaseMigrationManager {
  static const int currentSchemaVersion = 3;

  static Future<void> onUpgrade(
    Database db,
    int oldVersion,
    int newVersion,
  ) async {
    for (var version = oldVersion; version < newVersion; version++) {
      await db.transaction((txn) async {
        switch (version) {
          case 1:
            await _migrateV1ToV2(txn);
            break;
          case 2:
            await _migrateV2ToV3(txn);
            break;
          default:
            throw StateError('Unhandled migration step: v$version to v${version + 1}');
        }
      });
    }
  }

  static Future<void> _migrateV1ToV2(Transaction txn) async {
    // Step 1: Additive column for sync status
    await txn.execute(
      'ALTER TABLE notes ADD COLUMN sync_status TEXT NOT NULL DEFAULT "synced";',
    );
    await txn.execute(
      'CREATE INDEX idx_notes_sync_status ON notes(sync_status);',
    );
  }

  static Future<void> _migrateV2ToV3(Transaction txn) async {
    // Step 2: Dedicated mutations outbox table for reliable local-first sync
    await txn.execute('''
      CREATE TABLE mutation_outbox (
        id TEXT PRIMARY KEY NOT NULL,
        entity_type TEXT NOT NULL,
        entity_id TEXT NOT NULL,
        payload TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        attempts INTEGER NOT NULL DEFAULT 0
      );
    ''');
    await txn.execute(
      'CREATE INDEX idx_outbox_created_at ON mutation_outbox(created_at);',
    );
  }
}

Notice two critical principles in this structure:

• Transactions per step: Each migration step ($v1 \to v2$) executes inside its own transaction. If step 2 fails, step 1 has already succeeded and the user_version remains at 2, leaving the database valid for debugging or graceful error handling.
• Strictly additive: New columns always specify a sensible DEFAULT so existing rows don’t violate NOT NULL constraints upon creation.

Preserving the outbox: never drop un-synced data

The most critical table in a local-first application is the mutation outbox. This is where edits, creations, and deletions sit while the device is offline, waiting to be sent to your synchronization server.

If a migration requires altering a table structure where unsynced mutations are pending, you must follow the Three-Phase Table Migration pattern:

-- Phase 1: Create new table with target schema
CREATE TABLE activities_new (
  id TEXT PRIMARY KEY NOT NULL,
  title TEXT NOT NULL,
  category_id TEXT NOT NULL,
  duration_seconds INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL
);

-- Phase 2: Copy existing data safely with defaults
INSERT INTO activities_new (id, title, category_id, duration_seconds, updated_at)
SELECT id, title, COALESCE(category_id, "uncategorized"), 0, updated_at
FROM activities;

-- Phase 3: Swap references atomically
DROP TABLE activities;
ALTER TABLE activities_new RENAME TO activities;

-- Recreate any indexes
CREATE INDEX idx_activities_category ON activities(category_id);

Before running a destructive swap like this, check your outbox: if the table modification changes column names or shapes that pending outbox mutations rely upon, you must also migrate the serialised mutation payloads in mutation_outbox so the background syncer doesn’t crash when it wakes up online.

Testing migrations against real historical databases

Unit testing that the latest schema can create fresh tables is easy. The test that actually prevents production disasters is verifying that real historical databases upgrade cleanly.

In my projects, I commit historical SQLite database snapshots directly to the test suite:

test/
└── fixtures/
    └── databases/
        ├── schema_v1_with_sample_data.db
        ├── schema_v2_with_pending_outbox.db
        └── schema_v2_empty.db

Then, write an integration test that loads an actual schema_v1 file, executes the migration runner up to currentSchemaVersion, and asserts both data integrity and row counts:

test('migrates schema from v1 to v3 without corrupting pending records', () async {
  // 1. Copy fixture file to a temporary test path
  final testDbPath = await copyFixtureToTemp('schema_v1_with_sample_data.db');

  // 2. Open and run upgrades
  final db = await openDatabase(
    testDbPath,
    version: DatabaseMigrationManager.currentSchemaVersion,
    onUpgrade: DatabaseMigrationManager.onUpgrade,
  );

  // 3. Verify user_version pragma updated
  final versionResult = await db.rawQuery('PRAGMA user_version;');
  expect(versionResult.first.values.first, equals(3));

  // 4. Verify original rows are still present and accessible
  final rows = await db.query('notes');
  expect(rows.length, equals(12));
  expect(rows.first['sync_status'], equals('synced'));

  // 5. Verify newly created tables are queryable
  final outboxRows = await db.query('mutation_outbox');
  expect(outboxRows, isEmpty);

  await db.close();
});

If a developer introduces a syntax error in an ALTER TABLE statement or misses a default value on a NOT NULL column, this test fails in CI before the release reaches the App Store or Google Play.

Architectural rules for local-first database evolution

1. Never rename or drop columns in the field: Instead of renaming name to title, add title as a nullable column or populate it from name. Keep reading both until the old app versions drop below your support threshold.
2. All migrations are forward-only: Mobile devices cannot roll back. If version 3 has a bug, you don’t roll back to version 2; you release version 4 with a corrective forward migration.
3. Keep indexes out of migration transactions where possible: Large table re-indexing can hit SQLite lock timeouts on slower hardware. Index creation should be fast and targeted.
4. Never rely on the network during a migration: A database migration must run entirely locally using bundled SQLite statements. Never make an HTTP call inside an upgrade callback to fetch remote schema definitions.

When you treat SQLite on the client not as an ephemeral cache, but as a long-lived, first-class storage engine, your mobile app gains true resilience. It starts instantly, never drops user input on flaky subway connections, and upgrades seamlessly across releases.