Beyond Centralized Infrastructure: The Case for Local-First Software Architecture

One-trick ponies are easy to replace

Alfredo Rivera identifies a structural weakness in modern software where applications are architecturally inseparable from their parent companies. When the server bill goes unpaid or the company is acquired, the user’s data effectively ceases to exist.

Why This Matters

The technical reality of modern software is an ownership gap where ‘your data’ actually means ‘data stored on your behalf’ on provider-controlled infrastructure. By moving data to the device using SQLite and CRDTs, developers can close this architectural gap, ensuring that software continues to function and data remains accessible even if the central service provider vanishes.

Key Insights

• The Origin Private File System (OPFS) landed in all major browsers in 2023, providing the synchronous I/O required for persistent, local SQLite databases.
• The cr-sqlite tool implements CRDT semantics to allow two local databases to sync and merge correctly without requiring a central authority for conflict resolution.
• Libp2p enables peer discovery and transport over local networks or WebRTC, allowing cloud nodes to function as well-connected peers rather than absolute sources of truth.
• Identity can be managed via Ed25519 keypairs derived from 12-word mnemonics, removing the need for server-side session tokens and password resets.
• Schema-encoded behavior allows developers to declare sync logic, such as REALTIME_TEXT or LWW_INTEGER, directly within standard SQL CREATE TABLE statements.

Working Examples

Encoding sync behavior directly in the SQLite schema using custom column types for a sync runtime.

CREATE TABLE notes (id TEXT PRIMARY KEY, body REALTIME_TEXT, is_pinned LWW_INTEGER, cursor EPHEMERAL_INTEGER);

Practical Applications

• Use case: Real-time collaborative editors using REALTIME_TEXT columns to stream changes between peers via libp2p. Pitfall: Centralized database dependency leads to application death if the hosting company shuts down.
• Use case: Offline-first data management using cr-sqlite for last-write-wins (LWW) integer resolution. Pitfall: Using standard ACID transactions over a network causes high latency and failure in low-connectivity environments.

References:

• https://dev.to/alfierivera/one-trick-ponies-are-easy-to-replace-c5o