Local‑First Architecture Series IV: The Local Write Pipeline

Welcome, Developer!

If Part 3 established your durable, schema-backed local database, Part 4 takes you into one of the most fundamental pillars of a Local-First architecture:

Every write must succeed locally — instantly, deterministically, and without relying on the network.

This is what makes Local-First apps feel fast, responsive, and reliable, even when the network is slow or unavailable.

To achieve this, modern apps (Notion, WhatsApp, Apple Notes, Linear) all implement variations of the same mechanism: A Local Write Pipeline.

The Local Write Pipeline’s a structured flow where user actions mutate the local database first, log the mutation in a durable queue, update the UI immediately, and defer server sync to a background process.

Let’s build that pipeline step by step. Shall we?

What the Local Write Pipeline Is

A durable record of every mutation:

Local update to the entity (in our scenario, notes)
Logged pending action for sync
No network dependence

Detailed Write Lifecycle

In a Local‑First architecture, writes do not go to the server first.

Writes go to the local device, and the server learns about them later.

A durable write pipeline ensures:

offline safety
instant UI updates
crash resilience
deterministic sync
conflict-ready state

The full lifecycle looks like this:

User Action
   ↓
Domain Function
   ↓
Local Database Write (SQLite)
   ↓
Pending Action Logged (Write-Ahead Log for Sync)
   ↓
UI Update (instant)
   ↓
Sync Engine Uploads (later)

Each stage exists for a reason, let’s break them down.

1. User Action -> Domain Function

The UI never writes directly to storage. It calls a domain function that encapsulates:

validation
ID generation
timestamps
normalization
business rules

Example:

await createNote("Hello", "This is offline-safe.");

The domain layer is the stable API for your application logic.

2. Domain Function -> Local Database Write

await db.runAsync(
  "INSERT INTO notes (id, title, body, localUpdatedAt, isSynced) VALUES (?, ?, ?, ?, 0)",
  [id, title, body, Date.now()]
);

This ensures:

the note exists even offline
the app never waits on a server
state survives crashes and OS kills
timestamps support conflict resolution
isSynced = 0 marks it as requiring upload

SQLite is the source of truth, not React state or remote APIs.

3. Local Database Write -> Pending Action Logged

Every mutation must also be logged:

await db.runAsync(
  `INSERT INTO pending_actions (id, type, entityType, entityId, payload, createdAt)
   VALUES (?, ?, ?, ?, ?, ?)`,
  [
    crypto.randomUUID(),
    "CREATE",
    "note",
    id,
    JSON.stringify({ title, body }),
    Date.now(),
  ]
);

This queue guarantees:

retryability
ordering
durability
deterministic sync
conflict-resolution metadata

If the app crashes before syncing? Actions remain safely logged.

4. Pending Action -> UI Update (Instant)

The UI re-renders from SQLite:

const notes = await getAllNotes();

No network waiting.
No loading states.
No spinners.
No inconsistent API-driven views.

Your UI becomes:

instant
predictable
offline‑safe
frustration‑free

This is how apps like Notion, Linear, WhatsApp, Apple Notes, and Figma feel “fast”.

5. UI → Sync Engine (Later)

Sync happens separately via:

app‑open
app‑resume
background tasks
connectivity restored

The UI is never blocked by sync!

The sync engine picks up pending actions:

SELECT * FROM pending_actions WHERE status = 'pending'

Uploads them, merges server responses, and marks them complete.

This separation keeps your app fast and resilient.

Creating Notes (Local-First Mutation)

Let’s update the createNote function to include the logic to log pending actions:

// src/domain/notes/notesRepository.ts
import { getDb } from "@/src/db/database";
import * as Crypto from "expo-crypto";
 
export async function createNote(title: string, body: string): Promise<Note> {
  const db = await getDb();
 
  // 1. Generate a durable local ID
  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,
  };
 
  // 2. Store the note in the local DB
  await db.runAsync(
    `INSERT INTO notes (
        id, title, body, localUpdatedAt, isSynced
      ) VALUES (?, ?, ?, ?, ?)`,
    [note.id, note.title, note.body, note.localUpdatedAt, note.isSynced]
  );
 
  // 3. Log the pending action
  await db.runAsync(
    `INSERT INTO pending_actions (id, type, entityType, entityId, payload, createdAt)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [
      Crypto.randomUUID(),
      "CREATE",
      "note",
      id,
      JSON.stringify({ title, body }),
      now,
    ]
  );
 
  // 4. UI will refresh by re-reading SQLite
  return note;
}

What this guarantees

The note exists even if the app loses power.
The UI updates immediately (zero waiting, zero spinners).
Sync can replay the mutation later.
No server availability required.
Ordering is preserved.
Conflict resolution can be applied in next post - Part 5.

Updating Notes (Local Edit + Sync Intent)

Updating an entity in a Local-First system is not just replacing text, actually it is a sequence of deterministic steps designed to:

guarantee durability
preserve ordering
enable conflict resolution
avoid UI flicker
remain fully offline-safe

// src/domain/notes/notesRepository.ts
import { getDb } from "@/src/db/database";
import * as Crypto from "expo-crypto";
 
export async function updateNote(
  id: string,
  data: { title?: string; body?: string }
): Promise<void> {
  const db = await getDb();
  const now = Date.now();
 
  // Update the note locally
  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]
  );
 
  // Log an UPDATE action for sync
  await db.runAsync(
    `INSERT INTO pending_actions (id, type, entityType, entityId, payload, createdAt)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [Crypto.randomUUID(), "UPDATE", "note", id, JSON.stringify(data), now]
  );
}

When the user edits a note

we update the local SQLite row
update the localUpdatedAt timestamp
set isSynced = 0
log an UPDATE action for the sync engine

This ensures the UI updates immediately and the server is updated later. The Key detail is that updates do not require a round-trip to the server.

Deleting Notes (Soft Delete or Hard Delete Locally)

In a Local-First system, deleting something follows the exact same rule as creating and updating:

Always write locally first
Queue the change for later sync

Here is the delete function:

// src/domain/notes/notesRepository.ts
import { getDb } from "@/src/db/database";
import * as Crypto from "expo-crypto";
 
export async function deleteNote(id: string): Promise<void> {
  const db = await getDb();
  const now = Date.now();
 
  // 1. Delete immediately from the local database
  await db.runAsync("DELETE FROM notes WHERE id=?", [id]);
 
  // 2. Record the delete operation in the pending action queue
  await db.runAsync(
    `INSERT INTO pending_actions (id, type, entityType, entityId, payload, createdAt)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [Crypto.randomUUID(), "DELETE", "note", id, JSON.stringify({}), now]
  );
}

What this function does

Deletes the note locally right away
- The UI updates instantly.
- No loading, no waiting, no network required.
Adds a DELETE entry to the pending_actions table
- This tells the sync engine: “When you get a connection, delete this note on the server.”
- It also means the delete is preserved if the user goes offline, kills the app, restarts the device.
No server call happens here
- The function only mutates the local database.
- The sync engine handles all server communication later.

Why this matters

Local-First apps always update the device first.
Deletes must feel instant and never depend on network conditions.

By separating local changes from network sync, your architecture becomes offline-safe.

This is the same pattern used by apps like Notion, Apple Notes, WhatsApp, and Slack — and it’s the foundation for the sync engine.

Why This Pipeline Works

From a senior-engineering perspective, this pattern gives you:

Determinism: Every mutation is logged, ordered, and replayable.
Crash safety: SQLite transactions protect data even during sudden app termination.
Fault tolerance: Network failures never block the user.
Observability: The pendingActions table becomes a built-in audit log.
Extensibility: 5.1 Conflict resolution 5.2 Versioning 5.3 CRDT transforms 5.4 Multi-entity writes

All without touching your UI.

This separation of local state mutation and sync is what makes Local-First architectures scalable.

UI Walkthrough

To validate the full write pipeline, we build a simple UI that can:

create notes
edit note titles
delete notes
display the live SQLite list

Here’s the full example:

// app/(tabs)/index.tsx
import {
  createNote,
  deleteNote,
  getAllNotes,
  updateNote,
  type Note,
} from "@/src/domain/notes/notesRepository";
import { useEffect, useState } from "react";
import {
  Alert,
  Button,
  FlatList,
  StyleSheet,
  Text,
  TextInput,
  View,
} from "react-native";
import { SafeAreaView } from "react-native-safe-area-context";
 
export default function HomeScreen() {
  const [notes, setNotes] = useState<Note[]>([]);
  const [newTitle, setNewTitle] = useState("");
  const [newBody, setNewBody] = useState("");
 
  async function loadNotes() {
    const rows = await getAllNotes();
    setNotes(rows);
  }
 
  async function handleCreate() {
    if (!newTitle.trim()) return;
 
    await createNote(newTitle, newBody);
    setNewTitle("");
    setNewBody("");
 
    await loadNotes();
  }
 
  async function handleEdit(note: Note) {
    Alert.prompt(
      "Edit Note Title",
      "",
      async (text) => {
        if (!text) return;
        await updateNote(note.id, { title: text });
        await loadNotes();
      },
      "plain-text",
      note.title
    );
  }
 
  async function handleDelete(id: string) {
    await deleteNote(id);
    await loadNotes();
  }
 
  useEffect(() => {
    loadNotes();
  }, []);
 
  return (
    <SafeAreaView style={styles.safe}>
      <View style={styles.container}>
        <Text style={styles.title}>Welcome, Developer</Text>
        <Text style={styles.content}>Notes App</Text>
 
        {/* Create Note */}
        <View style={styles.inputCard}>
          <TextInput
            placeholder="Note title"
            value={newTitle}
            onChangeText={setNewTitle}
            style={styles.input}
          />
          <TextInput
            placeholder="Note body"
            value={newBody}
            onChangeText={setNewBody}
            style={[styles.input, { height: 60 }]}
            multiline
          />
          <Button title="Add Note" onPress={handleCreate} />
        </View>
 
        {/* Notes List */}
        <FlatList
          data={notes}
          keyExtractor={(item) => item.id}
          contentContainerStyle={{ gap: 12, paddingBottom: 50 }}
          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 style={styles.actions}>
                <Button title="Edit" onPress={() => handleEdit(item)} />
                <Button
                  title="Delete"
                  color="red"
                  onPress={() => handleDelete(item.id)}
                />
              </View>
            </View>
          )}
        />
      </View>
    </SafeAreaView>
  );
}
 
const styles = StyleSheet.create({
  safe: {
    flex: 1,
    backgroundColor: "#fff",
  },
  container: {
    flex: 1,
    padding: 20,
  },
  title: {
    fontSize: 26,
    fontWeight: "bold",
    marginBottom: 20,
  },
  content: {
    fontSize: 22,
    fontWeight: "semibold",
    color: "#333",
    marginBottom: 20,
  },
  inputCard: {
    marginBottom: 20,
    padding: 16,
    backgroundColor: "#f7f7f7",
    borderRadius: 8,
    borderWidth: 1,
    borderColor: "#ddd",
    gap: 10,
  },
  input: {
    borderWidth: 1,
    borderColor: "#ccc",
    padding: 10,
    borderRadius: 6,
    backgroundColor: "#fff",
  },
  card: {
    padding: 12,
    backgroundColor: "#fafafa",
    borderRadius: 8,
    borderWidth: 1,
    borderColor: "#ddd",
  },
  noteTitle: { fontSize: 18, fontWeight: "600" },
  noteBody: { fontSize: 14, marginTop: 4 },
  timestamp: { marginTop: 6, fontSize: 12, color: "#777" },
  actions: {
    flexDirection: "row",
    justifyContent: "space-between",
    marginTop: 10,
  },
});

Then run the app:

npm run ios #or npm run android

You can validate if your pipeline works as expected.

What You Accomplished in Part 4

Nice work, Developer! You just built:

a durable local write pipeline
a deterministic mutation log
instant UI responsiveness
a stable foundation for synchronization
offline-safe create/update/delete flows

Your app is now predictable, fault-tolerant, sync-ready, conflict-resolution-ready.

This is the level of discipline required for modern mobile platforms.

Next: Part 5 — Bidirectional Sync & Conflict Resolution

Once writes work locally, the next task is to teach your system how to:

upload pending actions
download server state
reconcile both sides safely
resolve conflicts deterministically

That’s where the real challenge begins, Developer. But no worries, I’ll guide you through that!

Stay focused, Developer 💙