Every July, I look forward to the "Ask HN: What Are You Working On?" thread. It is the ultimate sanity check for developers. It cuts through the venture capital hype cycles, the corporate marketing fluff, and the buzzword-heavy keynotes to show us what engineers are actually building late at night when the Slack notifications are finally silent.
Scanning through the July 2026 thread, a massive, undeniable shift in architectural design patterns jumped out at me. The era of the "everything-is-an-API-call" cloud monolith is facing a massive developer rebellion. Instead, developers are building local-first software. From collaborative markdown editors to offline-ready project management boards, the community is obsessed with running heavy client-side databases and syncing them peer-to-peer or client-to-server using Conflict-free Replicated Data Types (CRDTs) over SQLite.
But why is this happening right now in 2026, and how do you actually build one of these architectures without tearing your hair out over data sync conflicts? Let’s dive deep into the world of SQLite-CRDTs, understand the core mechanics, and write some code to see how it works under the hood.
The Developer's Frustration with "Cloud-First"
For the last decade, we have been trained to build apps in a specific way: the user clicks a button, a loading spinner appears, a REST or GraphQL request flies across the internet to an AWS region, a database transaction occurs, and the UI finally updates.
This model has three massive flaws that we’ve just accepted as "the cost of doing business":
- Latency: Even on 5G, round-trip times are noticeable. It kills the "instant" feel of an application.
- Offline fragility: If your user steps into an elevator or a subway, your app becomes a expensive brick.
- Infrastructure cost: Running massive database clusters in the cloud just to handle basic CRUD state for millions of active users is incredibly expensive.
Local-first architectures flip this model. Your client-side application reads and writes directly to an embedded database running right inside the browser (via WebAssembly) or the native app wrapper. The UI updates instantly (0ms latency). Syncing that data to the cloud or other peers happens asynchronously in the background. If the network drops, the user doesn't even notice.
Enter SQLite and CRDTs: The Perfect Marriage
To make local-first work, you need two pieces of technology: a robust local database and a deterministic way to merge conflicting writes from different devices without a central server acting as the single source of truth.
For the database, SQLite has emerged as the undisputed king. Thanks to massive improvements in WebAssembly (Wasm) and Origin Private File System (OPFS) support, we can now run full-featured, highly performant SQLite databases directly in a user’s browser tab with persistent storage.
For the syncing mechanism, we use CRDTs (Conflict-free Replicated Data Types). Traditional databases rely on locking or "last-write-wins" based on wall-clock time (which is notoriously unreliable due to clock drift). CRDTs, on the other hand, are mathematically designed data structures that can be updated independently and concurrently. When those updates are merged, they are guaranteed to converge to the exact same state, regardless of the order in which the updates were received.
The Two Flavors of CRDTs
In web development, we generally work with two types of CRDTs:
- State-based CRDTs (CvRDT): Peers send their entire state to each other. Merging is done via a join semi-lattice operation. This is simple but can become network-heavy as your dataset grows.
- Operation-based CRDTs (CmRDT): Peers only transmit the concurrent operations (mutations) to each other. This is highly efficient but requires an underlying transport layer that guarantees delivery of all operations.
Under the Hood: Building a Simple LWW-Element-Register in TypeScript
To understand how these mathematical structures resolve conflicts without a central coordinator, let's write a simple Last-Write-Wins-Element-Register (LWW-Register) in TypeScript. This is one of the foundational building blocks of local-first state syncing.
interface RegisterValue<T> {
value: T;
timestamp: number;
peerId: string;
}
class LWWRegister<T> {
private state: RegisterValue<T>;
constructor(initialValue: T, peerId: string) {
this.state = {
value: initialValue,
timestamp: Date.now(),
peerId: peerId
};
}
// Read the current local value
get value(): T {
return this.state.value;
}
// Update the value locally
set(newValue: T, peerId: string) {
this.state = {
value: newValue,
timestamp: Date.now(),
peerId: peerId
};
}
// Merge state with another peer's register
merge(incoming: RegisterValue<T>): void {
// 1. Compare timestamps
if (incoming.timestamp > this.state.timestamp) {
this.state = incoming;
}
// 2. Tie-breaker: If timestamps are identical, lexicographically compare Peer IDs
else if (incoming.timestamp === this.state.timestamp) {
if (incoming.peerId > this.state.peerId) {
this.state = incoming;
}
}
// 3. If incoming is older, discard it silently (convergent state maintained)
}
// Export state for replication
getState(): RegisterValue<T> {
return { ...this.state };
}
}
In this code, even if Peer A and Peer B update the register at the exact same millisecond, the tie-breaker rule (comparing the string values of their unique peerIds) guarantees that both peers will arrive at the exact same conclusion when they eventually sync. No server-side lock required!
Integrating SQLite with CRDTs in the Browser
Writing registers for every single variable in your codebase manually is exhausting. That's why the open-source community has been busy building tools that embed CRDT logic directly into the SQLite engine itself. Projects like cr-sqlite (a run-time extension for SQLite) allow you to write standard SQL tables and automatically converts them into CRDTs.
Let's look at how you would set up a collaborative "Todo" application table using this architecture. Notice how we use virtual tables and custom functions provided by the extension to track changes dynamically:
-- 1. Create a standard SQLite table
CREATE TABLE todo (
id TEXT PRIMARY KEY,
task TEXT,
completed INTEGER
);
-- 2. Tell the CRDT extension to track this table
SELECT crsql_as_crr('todo');
-- 3. Insert data locally (the extension automatically generates vector clocks under the hood)
INSERT INTO todo (id, task, completed) VALUES ('todo-1', 'Write blog post on local-first', 0);
-- 4. To replicate, we simply query the changesets generated by the engine
SELECT * FROM crsql_changes;
The crsql_changes virtual table acts as an operation log. When a user changes a row, a new changeset row is created containing the primary key, the column that changed, the new value, a database-level version number, and a client identifier.
To sync this database with another client or a backup server, you simply query the changes since your last sync timestamp from crsql_changes, send them over WebSockets, WebRTC, or even a simple HTTP POST request, and apply them on the destination database using:
-- On the receiving client: apply the incoming changeset
INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?);
The local SQLite engine handles the mathematical merging logic out-of-the-box, ensuring both databases end up completely identical.
The Challenges of Local-First
While local-first architecture is incredibly satisfying to build and lightning-fast for users, it is not a silver bullet. If you are planning to build something using this pattern, keep these hard engineering problems in mind:
1. Schema Migrations are Hard
In a traditional cloud application, you run a database migration script during your CI/CD deployment, and it updates a single database. In a local-first world, your database schema is distributed across thousands of user devices running different versions of your application. You have to write highly defensive schema migration paths that can migrate databases forward and backward gracefully.
2. Security and Authorization
If the user has a full copy of the database locally, how do you prevent them from reading data they shouldn't have access to? The answer is selective replication. Your sync gateway in the cloud must act as an authorization barrier, filtering out changesets that a specific user does not have permission to view before sending them down the wire.
What Are You Building?
The rise of high-performance WebAssembly SQLite and mature CRDT libraries is fundamentally changing our expectations of software performance. It feels like the early days of React or Docker—there is a palpable sense of architectural excitement in the developer community right now.
Have you experimented with local-first databases or CRDTs in your personal projects or production environments yet? What tools are you using to handle state sync? Let's discuss in the comments below!
Until next time, keep coding. — Alex