Local-First App with Zero sync or ElectricSQL & TanStackDB

[armelhbobdad]

Why “Local-First”?

Local-first (aka offline-first) apps store data on the client so they work seamlessly without a network. According to the RxDB docs,

“Local-First (aka offline first) is a software paradigm where the software stores data locally... and must work as well offline as it does online.”

In practice, this means:

• No waiting on slow networks.
• No lost work when you’re offline.
• Instant reads/writes via a local database.
• Automatic sync and real-time collaboration once connectivity returns.

Overall, local-first apps feel faster and more robust—operations hit the local store first (no spinners!), and updates propagate automatically when you reconnect.

---

ElectricSQL: PostgreSQL Sync Engine

ElectricSQL is an open-source local-first sync platform for Postgres. It sits between your Postgres database and clients:

1. Elixir-based sync layer in front of Postgres.
2. Local PGLite store on each client.
3. CRDTs under the hood, so concurrent edits merge without conflicts.

You can query/update your data locally (in a web or mobile app), and ElectricSQL handles background reads/writes, partial replication, and conflict resolution for you. In short, it “solves the hard problems of sync.”

---

TanStack DB: Reactive Client-Side Store

TanStack DB is a new in-browser database from the TanStack Query team, tailored for live, offline-capable UIs:

• Live queries for sub-millisecond updates.
• Optimistic mutations to apply writes instantly.
• Backend-agnostic: plug in REST, GraphQL, or a custom sync engine.

It acts as the write-path under your UI: you define typed collections, components subscribe to reactive queries, and TanStack DB handles local edits. Later, it can sync those edits to any backend. Pairing it with ElectricSQL gives you:

• Read-path sync: ElectricSQL streams fresh data from Postgres → local store.
• Write-path sync: TanStack DB batches and optimistically applies local edits.

Together, they form a fully offline-capable, active-active replication system.

---

Use Cases for Payload CMS Developers

1. Mobile-first & PWAs
   Let users browse/edit content on their phone without a connection; sync automatically when online.

2. Field-work & Low-connectivity
   Remote teams (branches, warehouses) edit catalog or inventory data offline; changes queue & merge later.

3. Real-time Collaboration
   Multiple authors editing the same entry see each other’s updates live (à la Google Docs).

4. Edge & Offline Admin UIs
   Integrate TanStack DB into the Payload admin panel so it works when the CMS server is unreachable.

Any scenario that demands reliability and responsiveness—spotty Wi-Fi, mobile users, multiple editors—benefits from local-first.

---

Proposed Features & Integrations

• ElectricSQL Plugin
  Add a first-class plugin or “Electric Mode” in Payload to publish selected collections to Electric’s sync layer.

• Starter Kits & Guides
  Official examples (e.g. Next.js + @tanstack/db + @electric-sql/react) so devs don’t build plumbing from scratch.

• CRDT-Friendly API Hooks
  Extend Payload’s REST/GraphQL endpoints to accept batched, CRDT-compatible updates.

• Offline-Capable Client Cache
  Recommend TanStack DB as a built-in cache for Payload queries—components query local collections and mutate offline. For example, a TanStack DB collection could mirror a Payload collection, so UI components query the local collection (no stale data) and use mutations that “just work” offline.

• CLI & Scaffolding
  Automate setup for local DBs (PGLite with Drizzle ORM) and sync; document lifecycle hooks for create/update.

These should be optional—incremental adopters can enable local-first without disrupting existing workflows.

---

Simplifying Development for All Skill Levels

ElectricSQL and TanStack DB abstract away websockets, background sync loops, and conflict resolution:

• ElectricSQL offers React hooks (e.g. useShape) that auto-sync data.
• TanStack DB provides query/mutation hooks for live, optimistic updates.

Junior devs can “write to this collection” and get eventual sync for free—no manual IndexedDB or fetch loops. Fewer bugs, faster feature development.

---

Discussion

This is a community-driven idea, not an official roadmap announcement. We welcome feedback:

• Alignment: How might offline/local-first support fit Payload’s vision?
• Challenges: What data-modeling, security, or complexity concerns arise? How can we address them?
• Interest: Which use cases excite you most? Who in the community needs offline or real-time features?

Please share your thoughts, questions, or prototypes. Let’s explore how Payload can power fast, collaborative, local-first apps!

---

Useful Links

• Local First / Offline First | RxDB
• Linearlite: Local-First with React | ElectricSQL
• ElectricSQL Official Site
• TanStack DB on GitHub
• ElectricSQL + TanStack Integrations
• PGLite with Drizzle ORM

[armelhbobdad]

Hi @GermanJablo . Please take a look #12506 . Here is the full doc TanStack DB - Documentation

[armelhbobdad]

Proposal: Local-First In-App Notification System for Payload CMS

Summary
Combine PGLite (embedded Postgres), ElectricSQL (CRDT sync), and TanStack DB (reactive local store) to deliver a fully offline-capable, in-app notification experience—100% self-hosted, zero third-party fees, and instant UI feedback.

---

1. Why Local-First In-App Notifications?

• Offline reliability: Notifications queue and display locally when offline; sync automatically when reconnected.
• Instant UI feedback: Local writes update badges/toasts/inboxes without any network latency or spinners.
• Conflict-free merging: ElectricSQL’s CRDTs ensure concurrent status changes (read/unread, dismiss) never conflict.

---

2. Component Overview

Layer	Technology	Role
Local Store	PGLite	In-browser/embedded Postgres for offline data
Reactive UI	TanStack DB	Live queries & optimistic writes on local data
Sync Engine	ElectricSQL	Bi-directional CRDT sync between PGLite ↔ Postgres

---

3. Architecture

3.1 Client-Side

1. PGLite persists a notifications table locally.

2. TanStack DB wraps PGLite queries:

   const notifications = useLiveQuery(
     `SELECT * FROM notifications ORDER BY created_at DESC`
   );

3. On new events (e.g. via a Payload hook), write to PGLite:

   INSERT INTO notifications (id, type, payload, status, created_at)
   VALUES (...);

3.2 Bi-Directional Sync

• ElectricSQL Read-Path: streams server Postgres notifications → client PGLite.
• ElectricSQL Write-Path: streams local PGLite inserts and updates (new notifications, status changes) → central Postgres.

---

4. UI Integration

• <NotificationInbox /> React component uses TanStack DB’s useLiveQuery to render notifications.
• Toasts/Badges update immediately on local inserts or status changes.
• Actions (mark read/dismiss) write to PGLite and sync back to server via ElectricSQL.

---

5. Benefits & Cost

• Zero per-event fees: All components are open-source and self-hosted.
• Offline-first UX: Users never lose in-app notifications due to connectivity issues.
• Conflict resolution: Built-in CRDT merge of concurrent local edits.
• Simple infra: Leverage existing Postgres; no additional job-queue or external services (e.g. Novu) needed.

Get Payload CMS in-app notifications that work everywhere—online or off—instantly and reliably.

[GermanJablo]

Thank you for diving deep into this. I’m very familiar with the local-first ecosystem and literature, and I’ve been working for a couple of years on my own CRDT.

I’d love for Payload to offer a solution to this problem—I imagine it as a plugin. The two most important questions to tackle first, IMO, are:

1. Which local DB to use: indexeddb, sqlite or pglite. Also, how to reconcile if different DB adapters are used on the server (e.g., mongdb).
2. How to define partial-replication rules. I picture the plugin having something like PowerSync’s sync rules.

[armelhbobdad]

Hi @GermanJablo,

TL;DR
ElectricSQL is ideal if you’re Postgres-only; Zero is the stronger long-term choice for Postgres + Mongo (and you don’t need TanStack DB on top).

---

📑 Table of Contents

1. Local DB Choices
2. Defining Sync Rules
3. Deep Dives
   • ElectricSQL Path
   • Zero Path
4. Why Zero Wins

---

1. Local DB Choices

Pick the right client store for your workload and backend.

• PGlite (Postgres-WASM)

  • Real Postgres dialect in the browser (PGlite docs)
  • ✅ Zero schema/type drift with your server
  • 🚫 Heavier WASM footprint

• SQLite (sql.js)

  • WASM-compiled SQLite engine in-browser (sql.js GitHub)
  • ✅ Mature SQL support
  • ⚠️ Postgres-to-SQLite type mapping can get complex

• IndexedDB

  • Browser built-in key/value store
  • ❌ Too low-level alone—no schema, no joins
  • ✔️ Typically used under the hood by PGlite/sql.js

• TanStack DB

  • In-memory reactive store (TanStack DB)
  • ✅ Live queries & optimistic updates
  • ⚠️ Not a full SQL engine—requires PGlite or SQLite underneath

Recommendation:

• Postgres backend: PGlite + optional TanStack DB for UI reactivity
• Lightweight cache: SQLite (sql.js)
• Avoid raw IndexedDB for complex sync

---

2. Defining Sync Rules

How you tell your client app what to sync—filters, relations, and reactivity.

• ElectricSQL “Shapes”

  const shape = {
    table: 'projects',
    where: 'status = "active"',
    columns: ['id', 'name', 'status'],
  };

  • Type-safe, code-driven API (Shapes docs)
  • Deduplicates overlapping shapes; manages subscriptions
  • Supports nested includes, filters, pagination

• Zero Query-Driven Sync

  // Find employees not in any org.
  z.query.employee.where('orgID', 'IS', null);

  • Your queries = your sync rules
  • Implicit partial-replication: only queried data is cached
  • ZQL schema files give you strong typing (Reading Data with ZQL docs)

---

3. Deep Dives

ElectricSQL Path

• Architecture: Elixir sync layer → Postgres → WebSocket → PGlite in client
• Partial Replication via “shapes” (table + WHERE + include-tree)
• Conflict Resolution: CRDT-inspired “transactional causal+”
• Live Queries & optimistic mutations via TanStack DB or raw SQL
• Postgres-only: If you’re on Mongo, you’d need a Postgres mirror
• Link: ElectricSQL Homepage

Zero Path

• Architecture:

• Partial Replication: query results + related objects only
• Conflict Resolution: optimistic; two-way live sync
• Multi-DB Roadmap: Postgres today; Mongo/MySQL adapters coming (PR #4251)

cbnsndwch is working on Mongo support.

• Single Client Lib: cache + reactive API in one package—no TanStack DB needed
• Demos:
  • Samples
  • OneJs React Framework
  • Zero + OneJs Demo on YouTube

---

4. Why Zero Wins

One engine, any DB, query-driven sync, single client library.

Feature	ElectricSQL	Zero
Multi-DB Support	❌ Postgres-only	✅ Postgres & Mongo (soon), MySQL, etc.
Sync-rules	Shapes API	Your normal queries (ZQL)
Client Stack	PGlite + TanStack DB	Single zero library
Partial Replication	Explicit shapes configuration	Implicit via queries
Open-source	Apache-2 license	Apache-2 license

[carlosbensant]

What a surprise to read this post.

Today, I spent the day playing with PayloadCMS + Zero Sync.

Since both PayloadCMS and Zero are betting on the Drizzle, I didn't need much to make it work.

1. PayloadCMS generates the Drizzle schema built-in. You can even customize the file output generateSchemaOutputFile.
2. You can easily use drizzle-zero to generate the Zero Schema from the (Payload generated) Drizzle Schema.
3. You just have to import the schema, add permissions, and the rest is history.

[carlosbensant]

Trello Clone using PayloadCMS and Zero Sync
https://github.com/carlosbensant/trello-clone-payloadcms-zero-sync

[armelhbobdad]

Wow, this is super helpful – thanks a ton for sharing! I didn’t realize it could be that smooth to go from Payload’s built-in Drizzle schema to a working Zero schema with drizzle-zero. Just importing it, adding a few permissions, and you’re good to go? That’s awesome. Really appreciate you dropping this tip!

[carlosbensant]

Yes, it is that easy. Remember, Zero Sync is still in alpha. Many people use it in production, but they do so at their own risk. Also, the Drizzle-zero package is still a work in progress and may contain bugs.