A Four-Byte Blueprint for the Database Schema

When you ship a local-first application, you aren't just shipping code; you're shipping a tiny, persistent data center that lives on a stranger's device. If you screw up the database schema on a server, you can roll back the deployment or run a manual fix in the production shell, but if you break a user’s local SQLite file, you've effectively bricked their data and lost their trust.

Managing schema evolution in distributed, local-first environments is usually treated with the same heavy-handed approach as server-side migrations: a dedicated migrations table, a list of filenames with timestamps, and an expensive library that bloats your bundle. But SQLite gives us a cleaner, more native way to handle this. It’s tucked away in the database header—a single, four-byte integer that can serve as the entire backbone for your migration logic.

The Secret in the Header

Every SQLite database file contains a 100-byte header. Most of it is boring stuff like page sizes and file format write versions. However, at offset 60, there are four bytes dedicated to the user_version.

This isn't a value SQLite uses internally. It’s a scratchpad specifically for developers. You can read it, you can write to it, and it survives across connections and restarts. Most importantly, it’s atomic. If you update the user_version as part of a transaction, it stays consistent with your data.

You access it via a PRAGMA:

-- Read the current version
PRAGMA user_version;

-- Set the version to 5
PRAGMA user_version = 5;

It’s an integer. That’s it. And yet, that single integer is all you need to build a migration system that is faster, simpler, and less error-prone than any third-party library I've ever used.

Why Skip the Migration Table?

Standard migration tools usually create a table like schema_migrations to track which scripts have run. This works fine for Postgres, but in a local-first SQLite context, it introduces unnecessary friction.

1. Bootstrapping Paradox: You have to write code to check if the migrations table exists before you can check what version the database is at. If you’re building a minimalist app, this is just extra noise.
2. Overhead: In environments like React Native or Electron, every SQL query counts toward your startup time. Using a PRAGMA is significantly faster than querying a table.
3. State Synchronization: Because the user_version is part of the database header, it’s much harder to accidentally corrupt or desynchronize than a row in a table that a curious user might edit with a DB browser tool.

The Strategy: The Migration Sandwich

The goal is to create a function that runs every time your application starts. It should look at the current user_version, compare it to the "target" version defined in your code, and apply only the missing changes in a linear sequence.

Here is a robust pattern I use in TypeScript. We define our migrations as an array of functions where the index of the function (+1) represents the version it will produce.

type Migration = (db: any) => void | Promise<void>;

const MIGRATIONS: Migration[] = [
  // Version 1: Initial Setup
  (db) => {
    db.exec(`
      CREATE TABLE users (
        id TEXT PRIMARY KEY,
        email TEXT UNIQUE,
        created_at INTEGER
      );
    `);
  },
  // Version 2: Add a 'bio' column
  (db) => {
    db.exec(`ALTER TABLE users ADD COLUMN bio TEXT;`);
  },
  // Version 3: Create a 'posts' table
  (db) => {
    db.exec(`
      CREATE TABLE posts (
        id TEXT PRIMARY KEY,
        title TEXT,
        author_id TEXT REFERENCES users(id)
      );
    `);
  }
];

The logic to run these is where the "Four-Byte Blueprint" comes into play. You fetch the current version, loop through the migrations starting from that index, and update the version as you go.

Building the Migration Engine

Let's look at a concrete implementation. I’m going to use a generic db object here, but this logic applies whether you’re using better-sqlite3, sql.js, or the Expo SQLite library.

async function migrate(db) {
  // 1. Get the current version from the header
  // Note: PRAGMA user_version returns a result set like [{user_version: 0}]
  const result = db.prepare('PRAGMA user_version').get();
  let currentVersion = result.user_version;

  const targetVersion = MIGRATIONS.length;

  if (currentVersion >= targetVersion) {
    return; // We are up to date
  }

  console.log(`Migrating database from ${currentVersion} to ${targetVersion}`);

  // Run migrations in a transaction for safety
  db.transaction(() => {
    while (currentVersion < targetVersion) {
      const migrationIndex = currentVersion;
      const migration = MIGRATIONS[migrationIndex];

      console.log(`Applying migration to version ${migrationIndex + 1}...`);
      
      try {
        migration(db);
        currentVersion++;
        
        // Update the header version inside the same transaction
        db.exec(`PRAGMA user_version = ${currentVersion}`);
      } catch (err) {
        console.error(`Migration failed at version ${migrationIndex + 1}`);
        throw err; // Rollback the transaction
      }
    }
  })();

  console.log("Database is now at version", currentVersion);
}

The Transaction Catch

There’s a massive "gotcha" here that bites people every time: SQLite PRAGMAs behave differently inside transactions.

While you can set the user_version inside a transaction in many SQLite wrappers, some environments (especially those using certain drivers or FTS5 extensions) might not persist the header change if the transaction isn't committed correctly. More importantly, certain schema changes—like DROP COLUMN (in older SQLite versions) or VACUUM—cannot be performed inside a transaction.

If you find yourself needing to do complex schema re-tooling, you might have to wrap each individual migration in its own transaction rather than wrapping the entire loop.

Dealing with the "Point of No Return"

In a traditional web app, you have "up" and "down" migrations. If a deployment fails, you run migrate:down.

Forget about down migrations for local-first.

Once you’ve shipped a new schema to 10,000 devices, you cannot reliably roll them back. If a user’s app updates to v2.0 and migrates the database, but then they don't open the app for a week and the app store pulls the update due to a bug, the user might still have that v2.0 database. If they somehow downgrade to v1.9, the old code won't know how to read the new schema.

The user_version approach enforces a forward-only mindset. If you make a mistake in version 4, you don't undo version 4. You ship version 5 that fixes the mess version 4 made. It’s cleaner, it’s more honest about the reality of distributed state, and it keeps your migration engine dead simple.

Edge Case: The PRAGMA foreign_keys Problem

One thing the user_version won't solve for you automatically is foreign key constraints during migrations. By default, SQLite has foreign key constraints disabled for backward compatibility. If you’re altering tables that are referenced by other tables, you need to be careful.

When doing a migration that involves dropping and recreating tables (the standard SQLite way to "alter" complex things), you should:

1. Disable foreign keys.
2. Start your migration.
3. Re-enable foreign keys after.

db.exec('PRAGMA foreign_keys = OFF');
// ... run migrations ...
db.exec('PRAGMA foreign_keys = ON');

But wait—don't do this inside the migration loop if you're using a single transaction. PRAGMA foreign_keys cannot be toggled while a transaction is active. This is why I prefer a "Wrapper" approach:

async function runEvolution(db) {
  // Check if we even need to migrate first (fast read)
  const { user_version: current } = db.prepare('PRAGMA user_version').get();
  if (current >= MIGRATIONS.length) return;

  // Turn off FKs for the duration of the migration session
  db.exec('PRAGMA foreign_keys = OFF');
  
  try {
    await migrate(db); // Our engine from earlier
  } finally {
    db.exec('PRAGMA foreign_keys = ON');
  }
}

Scaling to Real-World Complexity

As your app grows, putting every SQL string in a single MIGRATIONS array in one file will become a nightmare. I’ve seen files with 50+ migrations that are 3,000 lines long. It's unreadable.

Instead, I recommend a folder structure where each migration is its own file, but they are collected and exported in a single index.

/db
  /migrations
    001_initial_setup.ts
    002_add_prefs.ts
    003_indexing_fix.ts
    index.ts

In index.ts:

import { m001 } from './001_initial_setup';
import { m002 } from './002_add_prefs';
import { m003 } from './003_indexing_fix';

export const MIGRATIONS = [m001, m002, m003];

This keeps your git history clean. When you change 002_add_prefs.ts, it's clear what changed, and you aren't constantly fighting merge conflicts in one giant database.ts file.

The Performance Benefit of No-Op Boots

In a local-first app, the "boot" sequence is critical. Users expect the UI to be interactive in milliseconds. If you use a heavy migration library that scans a migrations table or, heaven forbid, scans the file system for .sql files on every startup, you are wasting the user's battery and time.

The PRAGMA user_version check is effectively free. On a modern smartphone, reading the SQLite header takes less than a millisecond. If the integer matches your code's version, you skip the entire migration logic. This allows you to run the migration check on every single app launch without any perceptible delay.

Handling Data Integrity During Migrations

Since we are talking about local-first, we have to talk about the "what if the battery dies?" scenario.

If your migration involves moving a lot of data—say, you're migrating 50,000 rows from a JSON blob into separate columns—that operation might take a few seconds. If the device powers off mid-migration, you need to ensure the database isn't in a half-migrated state.

This is why the user_version update must be the last thing you do in the transaction.

db.transaction(() => {
  // 1. Heavy data lifting
  db.exec("INSERT INTO new_table SELECT ... FROM old_table");
  
  // 2. Drop the old stuff
  db.exec("DROP TABLE old_table");
  
  // 3. ONLY NOW do we update the version
  db.exec("PRAGMA user_version = 4");
})();

If the power cuts at step 1 or 2, the transaction rolls back. The user_version remains at 3. Next time the app starts, the migration engine sees version 3 and starts the whole process over again. It’s idempotent by design.

When Not to Use This

I love this pattern, but I'm not a zealot. There are times when the "Four-Byte Blueprint" isn't enough:

* Multi-Branch Schema Changes: If you have multiple developers working on different features that require schema changes simultaneously, a single linear integer user_version will cause collisions. Developer A makes version 5 (Add Photos), Developer B makes version 5 (Add Comments). When they merge, someone has to manually re-number their migration. In large teams, a timestamp-based naming convention (e.g., 202310271030_add_photos.sql) is safer.
* Syncing Engines: If you are using a sync engine like Replicache, VLCN, or PowerSync, they often have their own specific ways of handling schema changes to ensure that CRDTs (Conflict-free Replicated Data Types) don't break. Always check their docs first.

The Philosophy of "Good Enough"

In the software world, we are often told to use "industry standard" tools. For database migrations, that usually means a thick layer of abstraction. But abstractions have a cost—in bundle size, in cognitive load, and in performance.

SQLite is a masterpiece of engineering because it packs so much power into such a small space. By using the user_version PRAGMA, you’re choosing to work *with* the database instead of *on top* of it. You get a system that is:

1. Fast: Sub-millisecond checks on startup.
2. Robust: Atomic updates tied to your schema changes.
3. Zero-Dependency: No NPM packages to audit or update.
4. Transparent: You know exactly how it works because you wrote the 20 lines of code that power it.

For 90% of local-first applications, that four-byte integer is the only migration framework you’ll ever need. It’s not about being clever; it’s about being minimal. Write the migrations, trust the header, and get back to building features your users actually care about.