Building Local-First SaaS Architecture: Real-Time Syncing Between PostgreSQL and Browsers via Yjs

The Paradigm Shift: Moving Beyond Traditional Request-Response SaaS

For over two decades, the architectural blueprint of Software-as-a-Service (SaaS) applications has remained largely unchanged: a thin client (the browser) communicating with a centralized database via a stateless API layer. While this model offers robust security and centralized control, it introduces significant friction. Every user action requires a network round-trip, leading to structural latency, intermittent spinners, and complete application failure the moment a user loses internet connectivity.

Modern collaborative platforms like Figma, Linear, and Notion have proven that users demand snappy, instantaneous interactions. To achieve this level of performance, engineering teams are increasingly turning to a Local-First architecture. In a local-first application, the primary data store resides directly within the client's device (e.g., indexedDB inside the browser). The application reads and writes to this local database at memory speeds, completely independent of network status. Data synchronization with the central server happens asynchronously and transparently in the background.

Implementing this architecture requires solving one of computer science’s hardest problems: distributed data conflict resolution. This comprehensive guide details how to build a production-grade, local-first synchronization engine using PostgreSQL hosted on a Virtual Private Server (VPS) and Yjs, a high-performance Conflict-free Replicated Data Type (CRDT) framework.

Understanding the Foundation: CRDTs and Yjs

In a traditional database setup, state resolution relies on sequential transactions and pessimistic locking. In a distributed, offline-capable local-first environment, locking is impossible because clients must modify data simultaneously while disconnected. If two users edit the same piece of data offline, the system must merge their changes deterministically once connection is re-established without requiring human intervention.

This is where Conflict-free Replicated Data Types (CRDTs) come into play. CRDTs are mathematical data structures that guarantee that as long as a set of updates are received by all replicas (regardless of the order of arrival or network delays), all replicas will eventually converge to the exact same state.

Key Principle: CRDTs ensure strong eventual consistency by design, eliminating the need for complex, error-prone merge-conflict code in your application layer.

Yjs is an open-source, ultra-high-performance CRDT implementation specifically optimized for JavaScript environments. It represents data as a directed acyclic graph of operation structures and compresses updates into highly efficient binary blobs. While widely recognized for powering collaborative rich-text editors, Yjs is fully capable of handling structured application state, making it an ideal choice for syncing general SaaS application data.

The Architecture: Browser, VPS, and PostgreSQL

To implement a robust sync pipeline, we must map the collaborative, operational nature of Yjs CRDTs to the structured, relational world of PostgreSQL. The architecture consists of three core layers:

• The Client-Side Layer (Browser): The frontend application interacts with a local Yjs document (`Y.Doc`). This document is persisted locally using IndexedDB so that state survives browser refreshes and offline periods.
• The Synchronization Layer (VPS WebSocket Server): A Node.js or Go-based background service running on your VPS acts as the central communication hub. It accepts WebSocket connections from clients, authenticates them, broadcasts binary CRDT updates to other active clients, and streams those updates to the database pipeline.
• The Persistence Layer (PostgreSQL): The relational database serving as the ultimate source of truth. PostgreSQL handles user accounts, billing, global state snapshots, and historical audit logs.

The Challenge of Relational Mapping

A frequent architectural pitfall is trying to convert every single character change or minor CRDT operation into instant SQL `UPDATE` or `INSERT` statements. Doing so will immediately bottleneck your VPS CPU and overwhelm PostgreSQL with transaction locks. Instead, a hybrid strategy should be applied: store the compressed Yjs binary updates directly as a `BYTEA` or `BLOB` column for rapid syncing, and periodically decode these updates into structured relational tables for analytical queries and standard backend processing.

Step-by-Step Implementation Guide

1. Setting Up the Client-Side Storage and Sync

On the client side, initialization involves creating a unified Yjs document and binding it to both a local persistence provider (IndexedDB) and a network provider (WebSockets). This ensures that data is saved immediately to the hard drive before any network request is even attempted.

Developers typically utilize the `y-indexeddb` and `y-websocket` packages to achieve this. When the application boots up, the indexedDB provider reads the cached binary state from the disk and populates the local memory. The user can immediately begin reading and writing data. Concurrently, the WebSocket provider establishes a duplex connection to the VPS, automatically sending any locally accumulated offline updates and receiving delta updates from other users.

2. Building the VPS WebSocket Sync Server

The synchronization server running on your VPS acts as a traffic controller. Using the standard `y-websocket/bin/utils` package as a base, you can build an authenticated WebSocket server. When a client connects with a specific document identifier (such as a project or workspace ID), the server must:

1. Authenticate the user's session token and verify access permissions for that specific document ID.
2. Fetch the existing document state from PostgreSQL.
3. Send the missing state updates back to the client via an efficient *Sync Step 1* handshake.
4. Listen for subsequent binary updates from the client, broadcast them to all other connected peers in the same room, and queue them for database insertion.

3. Designing the PostgreSQL Database Schema

To store Yjs update blobs efficiently, your PostgreSQL schema should feature a dedicated table optimized for document storage. Rather than overwriting a single row continually, appending updates is often the most performant approach to prevent row-locking contentions. Consider the following structural approach:

CREATE TABLE yjs_document_updates (
    id BIGSERIAL PRIMARY KEY,
    document_id VARCHAR(255) NOT NULL,
    update_data BYTEA NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_yjs_doc_id ON yjs_document_updates(document_id);

To prevent this table from growing infinitely and slowing down connection times, you should implement a compaction routine. Every few minutes, or after a specific number of updates, a background worker should load all binary updates for a `document_id`, merge them into a single consolidated binary blob using `Y.mergeUpdates()`, save the consolidated blob, and delete the historical rows.

Optimizing Performance and Scaling on a VPS

Running a real-time, local-first sync engine on a cost-effective VPS requires careful resource optimization. Unlike traditional HTTP requests that terminate quickly, WebSockets maintain persistent, open TCP connections, consuming memory and file descriptors continuously.

• Kernel Tuning: Modify your VPS configuration (`/etc/security/limits.conf` and `sysctl.conf`) to increase the maximum number of open file descriptors (`nofile`). This allows your server to handle tens of thousands of concurrent WebSocket connections smoothly.
• Memory Management: Because JavaScript objects inside Node.js can carry overhead, ensure your compaction background workers are written efficiently, freeing up memory rapidly after merging binary blobs.
• Network Load Balancing: Place a reverse proxy like Nginx or Caddy in front of your WebSocket server. This layer handles SSL/TLS termination, shielding your core sync application from cryptographic overhead and allowing you to easily scale horizontally by routing traffic based on URL paths.

Conclusion: The Future of High-Performance SaaS

Embracing a Database Local-First architecture by combining PostgreSQL and Yjs completely redefines the user experience of modern SaaS applications. By moving the primary database closer to the user—directly inside the browser—you eliminate the perception of network latency and deliver an application that works seamlessly regardless of connectivity status. While the initial setup requires shifting your mindset from synchronous HTTP transactions to asynchronous, state-merging CRDT pipelines, the resulting benefits of extreme responsiveness, offline capability, and reduced server overhead create a powerful competitive advantage for any modern business platform.