Local-First App with 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.