+-----------------------------------------------------------------------------------------+
|                  Offline-First Mobile Synchronization Matrix (2026)                     |
+-----------------------------------------------------------------------------------------+

WATERMELONDB (The High-Performance React Native Standard)
- Core Model: Reactive SQLite ORM with Observable queries (RxJS / native C++ bridge).
- Sync Protocol: Custom HTTP pull/push endpoint (Requires manual backend implementation).
- Best for: Large local relational datasets (> 50,000 records) in React Native / Expo.

POWERSYNC (The Enterprise PostgreSQL-to-SQLite Sync Engine)
- Core Model: Managed / Self-Hosted sync service streaming partial Postgres tables to SQLite.
- Sync Protocol: Dynamic Client Replication Rules (Streams only authorized tenant/user rows).
- Best for: Production enterprise apps requiring plug-and-play PostgreSQL synchronization.

ELECTRICSQL (The Open-Source CRDT Shapes Pioneer)
- Core Model: Open-source Elixir/Rust sync service streaming Postgres Write-Ahead Logs (WAL).
- Sync Protocol: Shapes & CRDT mathematics for conflict-free multi-writer merging.
- Best for: Open-source purists, collaborative multi-user apps, real-time local-first platforms.

+-----------------------------------------------------------------------------------------+
|                  Local-First Read/Write Data Flow                                       |
+-----------------------------------------------------------------------------------------+

[1. USER TAPS 'CREATE INVOICE']
                 |
                 v (Instant 0.2ms write to local SQLite database!)
[ON-DEVICE SQLITE DATABASE: Record saved with 'sync_status = pending']
                 |
                 +---> [2. REACTIVE UI: Instantly renders new invoice on screen! (0ms Lag!)]
                 |
                 v (Background Asynchronous Synchronization Loop)
+-----------------------------------------------------------------+
| 3. BACKGROUND SYNC ENGINE (PowerSync / ElectricSQL):            |
| - Checks device network connectivity.                           |
| - If Online: Streams pending transaction to Cloud PostgreSQL.   |
| - Cloud Postgres commits & broadcasts new WAL sequence number.  |
| - Local SQLite marks record as 'sync_status = synced'.          |
| - If Offline: Queues mutations persistently on disk!            |
+-----------------------------------------------------------------+

+-----------------------------------------------------------------------------------------+
|                  Conflict Resolution Strategies                                         |
+-----------------------------------------------------------------------------------------+

1. LAST-WRITE-WINS (LWW with Hybrid Logical Clocks):
   - Compares client timestamps: The edit with the latest timestamp overwrites the earlier one.
   - Simple to implement, but risk of silent field overwrites if users edit different fields.

2. COLUMN-LEVEL FIELD MERGING (PowerSync Standard):
   - If User A edits 'invoice.status' and User B edits 'invoice.shipping_address',
     both changes are merged cleanly into PostgreSQL without conflict!

3. CRDT MATHEMATICS (ElectricSQL Standard):
   - Uses State-Based or Operation-Based Conflict-Free Replicated Data Types.
   - Mathematically guarantees that all distributed nodes converge to the identical state
     regardless of network delivery order!

// schema/appSchema.ts
import { column, Schema, Table } from "@powersync/react-native";

export const CUSTOMERS_TABLE = new Table({
  name: "customers",
  columns: [
    column.text("name"),
    column.text("email"),
    column.text("phone"),
    column.integer("is_vip"),
    column.text("created_at"),
  ],
});

export const INVOICES_TABLE = new Table({
  name: "invoices",
  columns: [
    column.text("customer_id"),
    column.real("amount"),
    column.text("status"), // 'paid', 'pending', 'draft'
    column.text("due_date"),
  ],
  indexes: {
    customer_idx: ["customer_id"],
  },
});

export const appSchema = new Schema({
  customers: CUSTOMERS_TABLE,
  invoices: INVOICES_TABLE,
});

# sync_rules.yaml - PowerSync PostgreSQL Server Replication
bucket_definitions:
  # 1. Sync global reference data to all authorized mobile users
  global_catalog:
    data:
      - SELECT * FROM product_catalog WHERE is_active = true

  # 2. Sync tenant-specific customer data ONLY to sales reps assigned to that organization
  user_customers:
    parameters: SELECT request.user_id() as current_user_id
    data:
      - SELECT c.* FROM customers c
        INNER JOIN organization_members m ON m.org_id = c.org_id
        WHERE m.user_id = bucket.current_user_id
      - SELECT i.* FROM invoices i
        INNER JOIN customers c ON c.id = i.customer_id
        INNER JOIN organization_members m ON m.org_id = c.org_id
        WHERE m.user_id = bucket.current_user_id

// components/CustomerInvoiceList.tsx
import React from "react";
import { View, Text, FlatList, TouchableOpacity } from "react-native";
import { useQuery } from "@powersync/react-native";
import { db } from "../db/powersyncClient";

export function CustomerInvoiceList({ customerId }: { customerId: string }) {
  // 1. Reactive Observable Hook: Automatically re-renders UI in 0ms when SQLite updates!
  const { data: invoices, isLoading } = useQuery(
    "SELECT * FROM invoices WHERE customer_id = ? ORDER BY due_date DESC",
    [customerId]
  );

  // 2. Instant Local Write (Zero Network Lag!)
  const handleMarkAsPaid = async (invoiceId: string) => {
    // Writes directly to local SQLite in 0.3ms! Syncs to Postgres in background!
    await db.execute(
      "UPDATE invoices SET status = 'paid' WHERE id = ?",
      [invoiceId]
    );
  };

  return (
    <View style={{ flex: 1, padding: 16 }}>
      <FlatList
        data={invoices}
        keyExtractor={(item) => item.id}
        renderItem={({ item }) => (
          <View style={{ padding: 12, borderBottomWidth: 1, borderColor: "#333" }}>
            <Text style={{ color: "#fff", fontSize: 16 }}>Invoice: ${item.amount}</Text>
            <Text style={{ color: item.status === "paid" ? "#4ade80" : "#f87171" }}>
              Status: {item.status.toUpperCase()}
            </Text>
            <TouchableOpacity onPress={() => handleMarkAsPaid(item.id)}>
              <Text style={{ color: "#38bdf8", marginTop: 4 }}>Mark as Paid</Text>
            </TouchableOpacity>
          </View>
        )}
      />
    </View>
  );
}

       +-------------------------------------------------------------+
       |             Screen Load & Data Mutation Latency (ms)        |
       +-------------------------------------------------------------+
 Traditional Online-First REST API    | ==================================== [380.0 ms]
 Offline-First SQLite Local Read/Write| = [0.4 ms] (950x Faster!)
                                      +-------------------------------------+
                                      0ms    100ms   200ms   300ms   400ms