Offline-First Mobile App Architecture: PowerSync vs. WatermelonDB vs. ElectricSQL (Postgres Sync Blueprint)

# Offline First Mobile App Sync Architecture: PowerSync vs. WatermelonDB vs. ElectricSQL

Summary: An architectural blueprint for mobile tech founders and engineering leaders implementing an offline first mobile app sync architecture. We evaluate PowerSync, WatermelonDB, and ElectricSQL across PostgreSQL WAL replication, client SQLite mutation pipelines, and conflict resolution models (CRDT, LWW, Server-Authoritative). This guide covers end-to-end data flow, sync rule configurations, mobile background execution limits, and multi-tenant scoping strategies to ensure data integrity at scale.

---

The Strategic Shift to Offline-First Mobile Systems

Cloud-first mobile applications treat network connectivity as a guaranteed precondition. Client views issue imperative HTTP calls across API gateways, blocking user interfaces on spinners while remote PostgreSQL instances execute queries. When devices enter cellular dead zones, transit tunnels, aviation cabins, or congested facilities, the application fractures with request timeouts and blocking retry alerts.

For enterprise SaaS, field service, clinical healthcare, and high-frequency productivity tools, intermittent connectivity is an operational reality.

An offline first mobile app sync architecture inverts this dependency model. The local device database—embedded SQLite—serves as the primary, immediate source of truth for read and write operations. The user interface binds directly to reactive local database queries, executing mutations instantly in sub-16 milliseconds. Synchronization with the central PostgreSQL backend executes asynchronously in the background.

Traditional Cloud-First Paradigm:
[User Interaction] -> [Network Request] -> [Cloud API] -> [Database] -> [UI Update]
(Subject to Variable Cellular Latency and Timeouts; Blocks on Network Drop)

Local-First Reactive Paradigm:
[User Interaction] -> [Local SQLite Write] -> [UI Reactive Re-render (<16ms Frame Budget)]
                             |
                     (Async Sync Engine)
                             |
                    [PostgreSQL Backend]

Implementing this architecture requires solving core distributed systems problems on resource-constrained mobile hardware: bidirectional state transfer, minimal battery consumption, mobile OS process shutdowns, and concurrent write collisions across distributed nodes.

---

Core Data Flow Architecture: From Local SQLite to PostgreSQL WAL

A resilient local-first architecture couples an embedded client database to a central relational database through continuous Change Data Capture (CDC). Rather than polling REST endpoints, the architecture isolates local writes, logs durable mutations, and synchronizes state via streaming protocols.

Production Data Flow Architecture Diagram

flowchart TD
    subgraph MobileClient["Mobile Device (Flutter / React Native)"]
        UI["User Interface (Reactive Views)"]
        LocalDB[("Local SQLite (Encrypted)")]
        MutQueue["Local Mutation Queue (Durable Storage)"]
        SyncWorker["Client Sync Engine (WebSocket Listener)"]
    end

    subgraph EdgeBoundary["API Gateway & Ingestion"]
        APIGW["Reverse Proxy / API Gateway"]
        AuthService["Ingestion API (Validation & Auth)"]
    end

    subgraph CentralStorage["Central Infrastructure"]
        PostgresPrimary[("PostgreSQL Primary Database")]
        WAL["Postgres Write-Ahead Log (WAL)"]
        LogicalRep["Logical Replication Slot (pgoutput)"]
        SyncService["Sync Server (CDC Translation Engine)"]
    end

    UI -->|"1. Immediate Read/Write"| LocalDB
    LocalDB -->|"2. Enqueue Mutation"| MutQueue
    LocalDB -.->|"3. Reactive Query Stream"| UI
    MutQueue -->|"4. Upstream HTTP Batch"| APIGW
    APIGW --> AuthService
    AuthService -->|"5. Atomic Write Transaction"| PostgresPrimary
    PostgresPrimary -->|"6. Commit Changes"| WAL
    WAL -->|"7. Stream Decoded Tuple Changes"| LogicalRep
    LogicalRep -->|"8. Ingest CDC Stream"| SyncService
    SyncService -->|"9. Evaluate Bucket Rules & Scopes"| SyncService
    SyncService -.->|"10. Downstream WebSocket Stream"| SyncWorker
    SyncWorker -->|"11. Reconcile Delta to SQLite"| LocalDB

Step-by-Step Data Flow Execution

1. Immediate Local Writes: User actions write to embedded SQLite within an isolated transaction. The UI updates instantly through reactive query subscriptions.

2. Local Mutation Queuing: Atomically with the local write, the client inserts an intent record into a persistent mutation_queue table capturing operation type, target entity, record ID, JSON payload, and a monotonic client sequence.

3. Upstream Ingestion: A background worker posts unacknowledged mutation batches over HTTPS to the API gateway.

4. Server Validation & Persistence: The backend validates tokens, enforces business invariants, checks role-based access controls, and commits changes to PostgreSQL.

5. PostgreSQL WAL Capture: PostgreSQL writes committed transactions to its Write-Ahead Log (WAL).

6. Logical Decoding: A PostgreSQL replication slot running pgoutput decodes WAL binary records into row changes.

7. CDC Stream Transformation: The synchronization engine ingests the CDC stream, applying tenant filtering rules and bucket authorizations.

8. Downstream WebSocket Streaming: The engine pushes compact binary or JSON delta frames over WebSockets to subscribed devices.

9. Reconciliation: The client SDK writes incoming deltas to SQLite, purges confirmed mutation_queue entries, and refreshes UI listeners.

---

Engine Deep-Dive: PowerSync vs. WatermelonDB vs. ElectricSQL

Choosing a synchronization engine determines backend coupling, operational overhead, and client footprint. Three frameworks dominate the modern PostgreSQL-to-mobile landscape: PowerSync, WatermelonDB, and ElectricSQL.

1. PowerSync: Decoupled CDC with Postgres WAL Streaming

PowerSync deploys an intermediate sync service (available as managed cloud or self-hosted container) connecting directly to PostgreSQL logical replication slots.

Downstream replication is automated: PowerSync decodes the WAL, partitions rows according to declarative sync_rules.yaml SQL statements, and streams deltas over WebSockets to client SQLite instances across Flutter, React Native, Swift, Kotlin Multiplatform, and Web.

PowerSync explicitly decouples upstream mutations from downstream replication. Client mutations route through your existing REST or GraphQL backend APIs, ensuring enterprise business logic, schema validations, authorization rules, and third-party webhooks remain strictly enforced on your servers.

2. WatermelonDB: Client-Optimized Reactive SQLite

WatermelonDB provides an optimized, thread-isolated reactive SQLite database for React Native via the JavaScript Interface (JSI). It is architected to scale to large local datasets by lazily instantiating model objects and executing queries on separate native threads, preventing JavaScript event loop stutter.

However, WatermelonDB is strictly a client database and sync protocol specification; it includes no backend service or CDC engine. Engineering teams must build and maintain custom server endpoints:

• pullChanges: Accepts a last_pulled_at timestamp and returns created, updated, and deleted records.
• pushChanges: Applies client mutation arrays to PostgreSQL.

This requires maintaining soft-delete flags, tombstone tables, and indexed updated_at columns across all synced PostgreSQL tables, generating substantial database index contention at scale.

3. ElectricSQL: Shape-Based Partial Replication

ElectricSQL provides a local-first layer that replicates query-defined data slices ("Shapes") from PostgreSQL to edge runtimes.

The Electric sync service consumes PostgreSQL logical decoding and exposes shape logs over HTTP. Because shapes are addressed via log offsets and ETags, updates are cacheable across standard CDNs and reverse proxies. On mobile, ElectricSQL pairs with embedded SQLite or PGlite, allowing dynamic mounting and unmounting of data subscriptions as users navigate views.

---

Comprehensive Architecture Comparison Matrix

---

Production Implementation: PowerSync Sync Rules and Client Schema

Below is a production implementation for an enterprise field operations platform where technicians manage projects and tasks offline.

1. Server-Side Sync Rules (`sync_rules.yaml`)

PowerSync partitions PostgreSQL tables into parameterized sync buckets defined in declarative SQL.

bucket_definitions:
  # Global reference catalog shared across technicians
  global_catalog:
    data:
      - select * from assets where status = 'ACTIVE'
      - select * from inspection_templates

  # Technician workspace scoped by user and organization
  technician_workspace:
    parameters: 
      - select id as user_id, organization_id from users where id = request.user_id()
    data:
      # Sync active organization projects updated within 90 days
      - select * from projects 
        where organization_id = bucket.organization_id
        and status in ('ACTIVE', 'SCHEDULED')
        and updated_at >= NOW() - INTERVAL '90 days'

      # Cascade sync tasks assigned to technician
      - select t.* from tasks t
        inner join projects p on t.project_id = p.id
        where p.organization_id = bucket.organization_id
        and t.assigned_technician_id = bucket.user_id
        and t.status != 'ARCHIVED'

2. Client-Side Schema Definition & Mutation Queue (React Native / TypeScript)

On mobile, we define the relational SQLite schema and an upstream connector that uploads mutations with idempotency tokens.

// Client schema definition
import { column, Schema, Table } from '@powersync/react-native';

export const projectsTable = new Table({
  name: 'projects',
  columns: [
    column.text('name'),
    column.text('organization_id'),
    column.text('status'),
    column.text('updated_at'),
  ],
  indexes: { org_idx: ['organization_id'] },
});

export const tasksTable = new Table({
  name: 'tasks',
  columns: [
    column.text('project_id'),
    column.text('title'),
    column.text('status'),
    column.text('assigned_technician_id'),
    column.text('updated_at'),
  ],
  indexes: {
    proj_idx: ['project_id'],
    tech_idx: ['assigned_technician_id'],
  },
});

export const AppSchema = new Schema({ projects: projectsTable, tasks: tasksTable });

// Upstream backend connector implementation
import { AbstractPowerSyncDatabase, PowerSyncBackendConnector, CrudEntry } from '@powersync/react-native';
import axios from 'axios';

export class ProductionBackendConnector implements PowerSyncBackendConnector {
  constructor(private baseURL: string, private getAuthToken: () => Promise<string>) {}

  async fetchCredentials() {
    const token = await this.getAuthToken();
    const res = await axios.get(`${this.baseURL}/api/v1/sync/credentials`, {
      headers: { Authorization: `Bearer ${token}` }
    });
    return { endpoint: res.data.powersync_url, token: res.data.token };
  }

  async uploadData(database: AbstractPowerSyncDatabase): Promise<void> {
    const transaction = await database.getNextCrudTransaction();
    if (!transaction) return;

    try {
      const token = await this.getAuthToken();
      await axios.post(
        `${this.baseURL}/api/v1/sync/mutations`,
        {
          batch_id: transaction.crud[0]?.id,
          mutations: transaction.crud.map((e: CrudEntry) => ({
            op: e.op,
            table: e.table,
            id: e.id,
            data: e.opData,
          })),
        },
        {
          headers: { 
            Authorization: `Bearer ${token}`,
            'X-Idempotency-Key': `batch-${transaction.crud[0]?.id}` 
          }
        }
      );
      await transaction.complete();
    } catch (error: any) {
      if (error.response?.status === 409) {
        // Business rule conflict: discard rejected mutation to unblock queue
        await transaction.complete();
      } else {
        throw error; // Network error: retry on subsequent cycle
      }
    }
  }
}

---

Conflict Resolution Decision Framework: CRDT vs. LWW vs. Server-Authoritative

Offline systems inevitably encounter write collisions. When two mobile users modify the same record concurrently, the sync layer must resolve differences deterministically.

                      Write Conflict Detected
                                |
             ----------------------------------------
            |                                        |
    Is the data critical                     Does the entity
    for business invariants?              represent collaborative text,
   (e.g., Inventory, Financials)           sets, or append-only tallies?
            |                                        |
           YES                                      YES
            |                                        |
            v                                        v
[Server-Authoritative Validation]            [Conflict-Free Replicated]
- Optimistic Concurrency Control              [Data Types (CRDTs)]
- PostgreSQL Version Vectors                 - State-based / Op-based CRDTs
- Explicit Reject (HTTP 409)                 - High storage metadata cost
            |
            NO
            |
            v
[Last-Write-Wins (LWW) via HLC]
- Hybrid Logical Clocks
- Deterministic, low overhead
- Silent update overwrite risk

1. Last-Write-Wins (LWW) via Hybrid Logical Clocks (HLC)

• Mechanism: Updates carry timestamps. The system accepts the newest timestamp and discards prior modifications.
• Vulnerabilities: Physical device clocks drift. Using system timestamps allows a misconfigured phone clock to overwrite valid server updates. Production LWW requires Hybrid Logical Clocks (HLC) combining physical time with monotonic counters.
• Best For: Non-critical attributes, user preferences, reading status, and single-user drafts.

2. Conflict-Free Replicated Data Types (CRDTs)

• Mechanism: Mathematical data structures (such as Observed-Remove Sets, LWW-Element-Sets, and JSON-CRDTs) that guarantee eventual consistency and convergence across distributed nodes without requiring central locking.
• Trade-offs: CRDT metadata—including causal history, actor identifiers, vector clocks, and tombstones for deleted records—substantially inflates local storage footprint and network payload volume compared to raw relational rows (as documented in distributed systems research by Kleppmann et al.). Furthermore, maintaining strict relational foreign-key integrity across distributed CRDT tables requires complex causal graph checks to prevent orphaned child records.
• Best For: Collaborative document editing, shared whiteboards, live counters, and multi-user messaging where non-blocking local writes and eventual convergence are paramount.

3. Server-Authoritative Optimistic Concurrency Control (OCC)

• Mechanism: The client applies updates optimistically to local SQLite. Upstream requests include an expected_version. The server checks the version atomically in PostgreSQL:

  UPDATE tasks 
  SET status = $new_status, version = version + 1, updated_at = NOW()
  WHERE id = $task_id AND version = $expected_version;

If zero rows match, the server returns an HTTP 409 Conflict. The server then triggers business merge rules or prompts the client to roll back optimistic local state.

• Trade-offs: Demands client-side rollback handling upon server rejection.
• Best For: Invariant-heavy workflows, transactional accounting, inventory reservations, scheduling dispatches, and immutable audit logs where state mutations must pass centralized server validations before becoming canonical.

---

Total Cost of Ownership (TCO) and Engineering Tradeoffs

Constructing custom synchronization engines in-house is an expensive engineering endeavor. Teams routinely underestimate the complexity of connection lifecycle recovery, distributed schema migrations, differential delta tracking, and tombstone pruning.

For engineering leaders scaling mission-critical platforms, partnering with an experienced mobile app development services team provides the architectural rigor needed to configure resilient logical replication, mutation queues, and failover topologies.

---

Production Risk Checklist: Mobile Background Tasks & Partial Replication

Offline architectures must operate within mobile operating system constraints. Neglecting OS resource policies causes terminated background processes, locked storage, and excessive cellular consumption.

1. Mobile Background Task Execution & OS Constraints

Operating System Lifecycle Constraints:
--------------------------------------------------------------------------------
iOS Background Execution:
• BGAppRefreshTask allows ~30 seconds of execution before watchdog termination.
• Attempting long-running WebSocket connections in background triggers 0x8badf00d crashes.
• NSFileProtectionComplete locks SQLite encryption keys when the device is locked.

Android Background Execution:
• WorkManager must be scheduled with NetworkType.CONNECTED and BatteryNotLow constraints.
• OEM proprietary power managers aggressively kill background workers unless exempted.
• Foreground Service with persistent notification required for continuous field sync.
--------------------------------------------------------------------------------

• Avoid Background WebSockets: Do not keep WebSockets open in the background. On iOS, background sockets trigger watchdog kills (0x8badf00d). Disconnect sockets when the app enters paused state; run short delta syncs using iOS BGAppRefreshTask or Android WorkManager.
• Database Lock Contention under File Protection: On iOS, NSFileProtectionComplete encrypts storage while locked. If a background sync queries SQLite while locked, SQLite fails with SQLITE_IOERR. Set SQLite database and WAL files to NSFileProtectionCompleteUntilFirstUserAuthentication.
• Idempotent Mutation Queue Processing: Mobile OSs kill background tasks mid-execution. Every client mutation batch must include a unique UUID stored in a backend deduplication cache to ensure retransmitted batches never execute duplicate side effects.
• Android Battery Optimization and OEM Lifecycles: Standard Android distributions enforce Doze mode and App Standby buckets, while proprietary OEM battery managers (as documented in mobile developer benchmarks such as DontKillMyApp) aggressively terminate background processes and drop deferred jobs. Reliable synchronization requires scheduling jobs via Android WorkManager with explicit battery and network constraints, or initiating a user-visible ForegroundService with a persistent notification for critical real-time operations.

2. Partial Replication & Scoping Discipline

Replicating complete enterprise databases to mobile devices exhausts flash storage, depletes cellular data, and exposes sensitive tenant records.

• Dynamic Parameterized Scoping: Eliminate unconstrained table syncs. All sync queries must enforce strict WHERE clauses matching user identity, tenant boundaries, and roles.
• Temporal Sliding Windows: Enforce chronological limits on transaction tables. Replicate active records updated within the last 30 to 90 days. Older historical records remain accessible through on-demand HTTP queries.
• Media and Blob Decoupling: Never write binary blobs (photos, PDFs) directly into SQLite. Instead, replicate lightweight metadata (file ID, hash, presigned S3/R2 URL) through the sync engine, downloading media assets via a separate disk-cached transfer manager.

---

Frequently Asked Questions (FAQ)

How does an offline-first architecture handle foreign key constraints when related records sync out of order?

Because CDC streams decompose relational updates into serialized change events, a child record can reach the client before its parent. Default SQLite foreign key constraints would reject the insert. Production sync engines bypass this by enabling PRAGMA defer_foreign_keys = ON; or PRAGMA foreign_keys = OFF; during sync ingestion. Relational integrity is enforced upstream by PostgreSQL during mutation processing.

What happens to local mutations when a user's authentication session expires while offline?

Local reads and writes continue uninterrupted against local SQLite. The client sync worker halts upstream mutation uploads upon receiving a 401 Unauthorized. Mutations remain safely queued in mutation_queue. When network connectivity returns, the app surfaces a non-destructive authentication prompt or executes a silent OAuth token refresh, resuming uploads in order.

Can an offline-first mobile app support full-text search across large local datasets?

Yes. SQLite contains a native Full-Text Search extension (FTS5) compiled into mobile runtimes. Setting up FTS5 virtual tables populated via SQLite triggers allows applications to execute tokenized, prefix, and match queries over extensive local datasets with immediate, localized response times, entirely eliminating remote network roundtrip latency.

How do WebSocket CDC streams impact mobile battery life compared to HTTP polling?

Holding open idle WebSockets prevents cellular baseband processors from entering low-power sleep states. Production systems use adaptive connection management: foreground states run WebSockets; prolonged inactivity throttles ping intervals (30–60s); background transitions disconnect the socket immediately, relying on silent push notifications (content-available: 1) to wake background workers for brief sync windows.

---

Sources

• PowerSync Architecture and PostgreSQL Logical Replication
• WatermelonDB Sync Protocol Specification
• ElectricSQL Shapes and HTTP Streaming Protocol
• Martin Kleppmann et al.: Conflict-Free Replicated Data Types & Local-First Software
• Apple Developer Documentation: Handling Background Tasks in iOS
• Android Developers Guide: Persistent Background Processing with WorkManager
• DontKillMyApp: Android OEM Background Process Benchmarks
• PostgreSQL Documentation: Logical Decoding and Replication Slots