Local-First Architecture Series III: Building the Local Database Layer

Posted on:December 2, 2025

Welcome back, Developer!

By now, you’ve completed:

Now we build the most important layer of the entire system:

The Local Database Layer

This is the backbone of the Local‑First architecture. It is what makes your app:

If this layer is wrong, the entire architecture becomes unstable.

Principles of a Local‑First Database Layer

A Local-First database must satisfy 7 strict engineering constraints:

1. It must be available before UI renders

No screen should depend on the network.

2. It must support atomic writes

Every write must be durable and consistent.

3. It must separate local vs server state

To support bidirectional sync.

4. It must track causal updates

Timestamps or version counters are required.

5. It must support deterministic queries

No “sometimes returns data” behavior.

6. It must support migrations

Schema drift is guaranteed.

7. It must support partial sync

Not all data arrives at once.

Creating Your App Database

Before defining tables or building the sync engine, we need a stable, predictable database setup. This is the foundation of every Local-First architecture.

To enable the app to run on Expo GO, let’s create a new file called database.ts:

import * as SQLite from "expo-sqlite";
 
let _db: SQLite.SQLiteDatabase | null = null;
 
// to support Expo GO app
export async function getDb() {
  if (!_db) {
    _db = await SQLite.openDatabaseAsync("app.db");
  }
  return _db;
}

Let’s create a new file called src/db/init.ts. Here is the minimal, recommended structure:

// src/db/init.ts
import { getDb } from "./database";
 
export const db = await SQLite.openDatabaseAsync("app.db");
 
export async function initDb() {
  const db = await getDb();
 
  await db.withTransactionAsync(async () => {
    await db.execAsync("PRAGMA foreign_keys = ON;");
  });
}

Why this setup matters

A few important principles guide this structure:

1. Open the database once, at app startup

A single shared connection prevents:

2. Keep initialization and schema creation in dedicated files

This allows you to add:

…without touching the rest of your app.

3. Keep DB logic out of UI components

Your screens should consume data, not manage storage.

Layers should look like: UI → domain → db helpers → init.ts

This ensures your UI stays predictable and business logic stays testable.

4. Use the async API

openDatabaseAsync() gives you a modern Promise-based API. It integrates cleanly with React Native and removes callback complexity.

withTransactionAsync() ensures atomic setup. Even the DB initialization runs inside a safe transaction.

5. Summary

At the end of this step, your app should:

This is all you need for now. We’ll add schema creation, the pending-actions queue, and indexing next.

Designing the Schema

We’ll support three critical tables:

  1. notes
  2. pending_actions
  3. metadata

1. notes table

This is the authoritative local model.

await db.execAsync(`
CREATE TABLE IF NOT EXISTS notes (
  id TEXT PRIMARY KEY,
  serverId TEXT,
  title TEXT,
  body TEXT,
  localUpdatedAt INTEGER,
  serverUpdatedAt INTEGER,
  isSynced INTEGER
);
`);

2. pending_actions Table

This acts as the deterministic local write queue.

await db.execAsync(`
CREATE TABLE IF NOT EXISTS pending_actions (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  entityType TEXT NOT NULL,
  entityId TEXT NOT NULL,
  payload TEXT NOT NULL,
  createdAt INTEGER NOT NULL,
  retryCount INTEGER DEFAULT 0,
  status TEXT DEFAULT 'pending'
);
`);

Why a queue?

This is production‑grade sync behavior.

3. metadata Table

Tracks app-wide state:

await db.execAsync(`
CREATE TABLE IF NOT EXISTS metadata (
  key TEXT PRIMARY KEY,
  value TEXT
);
`);

Used for:

After adding the logic to create the tables, your init.ts content should look like this:

// src/db/init.ts
import { getDb } from "./database";
import { migrateDb } from "./migrations";
 
export async function initDb() {
  const db = await getDb();
 
  await db.withTransactionAsync(async () => {
    await db.execAsync("PRAGMA foreign_keys = ON;");
 
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS notes (
        id TEXT PRIMARY KEY,
        serverId TEXT,
        title TEXT,
        body TEXT,
        localUpdatedAt INTEGER,
        serverUpdatedAt INTEGER,
        isSynced INTEGER
      );
    `);
 
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS pending_actions (
        id TEXT PRIMARY KEY,
        type TEXT NOT NULL,
        entityType TEXT NOT NULL,
        entityId TEXT NOT NULL,
        payload TEXT NOT NULL,
        createdAt INTEGER NOT NULL,
        retryCount INTEGER DEFAULT 0,
        status TEXT DEFAULT 'pending'
      );
    `);
 
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS metadata (
        key TEXT PRIMARY KEY,
        value TEXT
      );
    `);
 
    // run schema upgrades
    await migrateDb();
  });
}

Migrations

As soon as your app is in production, you cannot change your SQLite tables without a migration.

Every user already has a database on their device, so you must update that database safely when your schema evolves.

Think of migrations as - Small upgrades that bring the user’s local database up to date when they install a new version of the app.

Why migrations matter

If you don’t use migrations

Migrations are how you keep everyone consistent.

How the migration system works

You store the current schema version in the metadata table. When the app starts, you check which version the user is on. If they are behind, you apply the necessary schema changes.

You update the schema version. That’s it, one simple flow.

Let’s create a new file to add the migrations, called migrations.ts:

// src/db/migrations.ts
import { getDb } from "./database";
 
export async function getCurrentSchemaVersion() {
  const db = await getDb();
  const row = (await db.getFirstAsync(
    "SELECT value FROM metadata WHERE key='schemaVersion'"
  )) as { value?: string } | null;
 
  return row?.value ?? "1";
}
 
export async function setSchemaVersion(version: string) {
  const db = await getDb();
  await db.runAsync(
    "INSERT OR REPLACE INTO metadata (key, value) VALUES (?, ?)",
    ["schemaVersion", version]
  );
}
 
export async function migrateDb() {
  const db = await getDb();
  const currentVersion = await getCurrentSchemaVersion();
 
  if (currentVersion === "1") {
    await db.execAsync(`ALTER TABLE notes ADD COLUMN tags TEXT;`);
    await setSchemaVersion("2");
  }
 
  if (currentVersion === "2") {
    await db.execAsync(`
      CREATE INDEX IF NOT EXISTS idx_notes_updatedAt
      ON notes (localUpdatedAt DESC);
    `);
    await setSchemaVersion("3");
  }
 
  if (currentVersion === "3") {
    await db.execAsync(`
      ALTER TABLE notes ADD COLUMN conflictFlag INTEGER DEFAULT 0;
    `);
    await setSchemaVersion("4");
  }
}

Those are just examples that simulates real-world db changes.

Always call migrateDb at the end of initDb, after creating tables. Migrations depend on existing schema, and running them early corrupts the database.

// src/db/init.ts
import { getDb } from "./database";
import { migrateDb } from "./migrations";
 
export async function initDb() {
  const db = await getDb();
 
  await db.withTransactionAsync(async () => {
    await db.execAsync("PRAGMA foreign_keys = ON;");
 
    await db.execAsync(`CREATE TABLE IF NOT EXISTS notes (...);`);
 
    await db.execAsync(`CREATE TABLE IF NOT EXISTS pending_actions (...);`);
 
    await db.execAsync(`CREATE TABLE IF NOT EXISTS metadata (...);`);
 
    await migrateDb(); // <-- call it last
  });
}

The device is now up to date. Safe. Predictable. Easy to maintain.

The mindset is simple: If you plan to ship more than one version of your app, migrations are mandatory — not optional.

Query Helpers

Your Local-First architecture must expose a small, predictable set of query functions. These helpers act as the single interface between your domain logic and SQLite.

Why?

Below are the core query helpers needed for a simple Notes domain.

Create a file notesRepository.ts, then add the helpers to it:

mkdir -p src/domain/notes
touch src/domain/notes/notesRepository.ts

Strongly-Typed Note Model

/**
 * Strongly-typed Note model used across UI, domain, and sync layers.
 */
export interface Note {
  id: string;
  serverId?: string;
  title: string;
  body: string;
  localUpdatedAt: number;
  serverUpdatedAt?: number;
  isSynced: number;
}

A Local-First system needs to distinguish between:

The fields localUpdatedAt, serverUpdatedAt, and isSynced are essential for conflict resolution (covered in Part 5).

Fetch All Notes (UI Reads Only from DB)

import { getDb } from "@/src/db/database";
 
/**
 * Fetch all notes from the local database, sorted by most recently updated.
 */
export async function getAllNotes(): Promise<Note[]> {
  const db = await getDb();
 
  const rows = await db.getAllAsync<Note>(
    "SELECT * FROM notes ORDER BY localUpdatedAt DESC"
  );
  return rows;
}

A Local-First UI never reads directly from backend responses. The UI must always:

Sorting by localUpdatedAt ensures the UI always shows the most recently edited note first—exactly how users expect apps like Notes, Notion, or Google Keep to behave.

Create Note (Local Mutation Only)

/**
 * Create a new note locally.
 * This does NOT sync to the backend—sync engine handles that later.
 */
export async function createNote(title: string, body: string): Promise<Note> {
  const db = await getDb();
 
  const id = `note_${Date.now()}_${Math.random().toString(36).slice(2, 9)}`;
  const now = Date.now();
 
  const note: Note = {
    id,
    title,
    body,
    localUpdatedAt: now,
    isSynced: 0,
  };
 
  await db.runAsync(
    `INSERT INTO notes (
        id, title, body, localUpdatedAt, isSynced
      ) VALUES (?, ?, ?, ?, ?)`,
    [note.id, note.title, note.body, note.localUpdatedAt, note.isSynced]
  );
 
  return note;
}

This function is the core of Local-First design:

The sync engine (Part 4) uploads this note later.

Update Note (Local Mutation + Sync Trigger)

/**
 * Update a note locally & mark it as unsynced.
 */
export async function updateNote(
  id: string,
  data: { title?: string; body?: string }
): Promise<void> {
  const db = await getDb();
  const now = Date.now();
 
  await db.runAsync(
    `UPDATE notes
     SET title = COALESCE(?, title),
         body = COALESCE(?, body),
         localUpdatedAt = ?,
         isSynced = 0
     WHERE id = ?`,
    [data.title ?? null, data.body ?? null, now, id]
  );
}

This preserves the core rule of Local-First systems - All writes are local first, and all writes must be queued for sync.

This update handler:

Setting COALESCE(?, title) allows partial updates without overwriting existing values.

This is the same pattern used in systems like Notion and Apple Notes.

Delete Note (Local Deletion Only)

/**
 * Delete a note locally.
 * Sync engine will handle upstream deletion if needed.
 */
export async function deleteNote(id: string): Promise<void> {
  const db = await getDb();
  await db.runAsync("DELETE FROM notes WHERE id = ?", [id]);
}

Even deletion follows the Local-First rule that it deletes locally first. The sync engine sends delete request later.

In Part 4, the write pipeline will generate a pending DELETE_NOTE action for the sync queue. Nothing in this repository talks directly to the network.

Building a Minimal UI to Validate the Local Database

Before we invest in sync logic, conflict resolution, background tasks, or optimistic UI, we need to confirm one thing:

Can the app reliably read and write data locally?

To verify this, we build the simplest possible UI—something intentionally small, testable, and focused. This isn’t a production UI. It’s a diagnostic tool to prove that SQLite, the repository layer, and Expo Go are all working as expected.

Why a Simple Notes List?

It also keeps the architecture honest. If anything in the storage layer is unstable, the UI will reveal it immediately.

Minimal Screen UI

The component below does exactly two things:

If this works consistently—even after reloads, kills, and offline restarts—you know your foundation is solid.

// app/(tabs)/index.tsx
import {
  createNote,
  getAllNotes,
  type Note,
} from "@/src/domain/notes/notesRepository";
import { useEffect, useState } from "react";
import { FlatList, StyleSheet, Text, View } from "react-native";
import { SafeAreaView } from "react-native-safe-area-context";
 
export default function HomeScreen() {
  const [notes, setNotes] = useState<Note[]>([]);
 
  async function load() {
    // Add a note every time the page loads
    await createNote("Auto Note", `Created at ${new Date().toISOString()}`);
 
    const rows = await getAllNotes();
    setNotes(rows);
  }
 
  useEffect(() => {
    load();
  }, []);
 
  return (
    <SafeAreaView style={styles.safe}>
      <View style={styles.container}>
        <Text style={styles.title}>Notes (auto-added on load)</Text>
 
        <FlatList
          data={notes}
          keyExtractor={(item) => item.id}
          contentContainerStyle={{ gap: 12 }}
          renderItem={({ item }) => (
            <View style={styles.card}>
              <Text style={styles.noteTitle}>{item.title}</Text>
              <Text style={styles.noteBody}>{item.body}</Text>
              <Text style={styles.timestamp}>
                updated: {new Date(item.localUpdatedAt).toLocaleTimeString()}
              </Text>
            </View>
          )}
        />
      </View>
    </SafeAreaView>
  );
}
 
const styles = StyleSheet.create({
  safe: {
    flex: 1,
    backgroundColor: "#fff",
  },
  container: {
    flex: 1,
    padding: 20,
  },
  title: {
    fontSize: 24,
    fontWeight: "bold",
    marginBottom: 20,
  },
  card: {
    padding: 12,
    backgroundColor: "#f3f3f3",
    borderRadius: 8,
    borderWidth: 1,
    borderColor: "#ddd",
  },
  noteTitle: { fontSize: 18, fontWeight: "600" },
  noteBody: { fontSize: 14, marginTop: 4 },
  timestamp: { marginTop: 6, fontSize: 12, color: "#777" },
});

What This UI Confirms

If the list grows every time the screen loads, you have validated:

1. SQLite is working correctly in Expo Go

If the DB were broken or unavailable, the component would crash immediately.

2. Local writes are durable

Restart the app and the notes must persist. If they disappear, migrations or initialization are failing.

3. Local reads are stable

No hydration calls. No API. No network dependency.

4. Your repository layer is healthy

Each function runs through SQLite without errors.

Summary

A Local-First architecture stands on one requirement:

The device must be a reliable source of truth.

We must ensure the local store works perfectly under the simplest possible conditions.

This simple UI proves whether the foundation is solid.

If it passes, we move to Part 4 with confidence.

Conclusion

You’ve just built the backbone of the Local‑First architecture, a durable, sync‑ready database layer.

With this foundation in place, the rest of the architecture becomes significantly easier.

Stay focused, Developer — you’re building something resilient. Congrats!

Next up:

👉 Part 4 — The Local Write Pipeline