Architecting Local-First Software with SQLite and WAL Mode

First-Person Developer POV: Mechanical Keyboard, Terminal & Coffee

In October of last year, I was on an Amtrak Northeast Regional train headed from Penn Station to Boston.

The train dove into a concrete tunnel outside Stamford, Connecticut. Right at that exact second, I was using a celebrated, multi-billion-dollar cloud "workspace" app to jot down a critical note about a distributed compaction bug.

I typed three sentences. The cursor stopped blinking. The interface locked up like an engine without oil. A condescending little gray spinner appeared in the top-right corner, cheerfully announcing: "Reconnecting to cloud workspace..."

When the train emerged into the Connecticut daylight four minutes later, two of my three sentences had simply evaporated into the ether. A silent optimistic-concurrency merge failure. No error toast. No recovery draft. Just gone.

I sat there staring at my M3 Max MacBook Pro—a machine equipped with sixteen CPU cores, 64 gigabytes of unified memory, and an NVMe solid-state drive capable of reading seven gigabytes of raw data per second—and I started laughing out loud like a lunatic.

If that isn’t a collective failure of software engineering sanity, I don't know what is.

The Fallacy of the Dumb Terminal

For the past twenty years, enterprise software architecture has been trapped in a religious dogma: The server is the single source of truth; the client is just a dumb terminal rendering HTML over the wire.

Every time a user presses a key, checks a box, or moves a card on a kanban board, modern web apps do this absurd circus trick:

[ User Keystroke ] 
       │
       ▼ (40ms - 250ms Network Latency via 5G/WiFi)
[ Cloud Load Balancer ]
       │
       ▼
[ Microservice Authentication & Schema Validation ]
       │
       ▼
[ Remote PostgreSQL Transaction Lock ]
       │
       ▼ (40ms - 250ms Return Packet)
[ React Re-render with Optimistic Reconciliation ]

Total latency? Anywhere from 120ms to 600ms. If you have spotty cellular coverage on a train or hotel Wi-Fi, total latency is infinity—the app simply stops working.

We took the most powerful edge devices ever invented and turned them into fragile, shivering display terminals waiting for permission from an AWS data center in Northern Virginia just to display twenty characters of text.

The Alternative: Hardware Physics First

In a local-first architecture, the order of operations is reversed with extreme prejudice:

1. Local NVMe is Ground Zero: When the user presses a key, the mutation is written directly to an embedded, in-process SQLite database stored on the local disk. Time elapsed: under 200 microseconds (0.2ms).
2. Instant UI Response: The user interface updates at a silky 120 frames per second before the next monitor refresh cycle has even begun.
3. The Network is an Asynchronous Sync Bus: A background worker thread leisurely packages mutations into Conflict-Free Replicated Data Types (CRDTs) and pushes them to a sync server whenever an internet connection happens to exist.

If the user goes into an airplane cabin for twelve hours? The app works identically. If an AWS availability zone catches fire? The app doesn't care. The data belongs to the user, on the user's hardware.

Why SQLite WAL Mode Changes the Entire Game

Whenever I preach the gospel of embedded SQLite to frontend engineers, their eyes widen in panic: "Wait, Elena! Doesn’t SQLite lock the entire database file on every write? Won’t the UI freeze when background sync runs?"

In default rollback journal mode (PRAGMA journal_mode = DELETE), yes. A write transaction grabs an exclusive file lock, blocking all readers until the transaction commits.

But when you activate Write-Ahead Logging (WAL mode), SQLite undergoes an architectural metamorphosis:

-- Elena's Production Local-First Pragmas
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 30000000000; -- 30GB Memory-Mapped I/O

How WAL Mode Actually Works:

• Readers Never Block Writers: A background thread can execute a massive Full-Text Search (FTS5) query scanning 100,000 documents, and the user can still type at 100 words per minute without dropping a single millisecond of input responsiveness.
• Writers Never Block Readers: Instead of overwriting live database pages, SQLite appends new writes sequentially to an auxiliary -wal file on disk. Readers continue reading untouched pages from the main database file in parallel.
• Sequential Append Speed: Because SSD controllers love sequential sector writes, appending to the WAL log executes in sub-millisecond bursts.

Elena's Unfiltered Take

We spent the last decade building bloated cloud SaaS architectures that require fifteen Kubernetes pods, three Redis caches, and a team of DevOps engineers just to manage a grocery list.

The next generation of enduring software will not be built by adding more cloud layers. It will be built by engineers who respect hardware physics, trust local storage, and write software that treats user autonomy as a non-negotiable principle.

Put SQLite on the client. Turn on WAL mode. Give users their speed back.