Offline‑First SQL Sync That Won’t Melt Your Backend: A CTO Playbook

Your app’s offline mode is probably a spinner and a hope. Then a field team goes into a tunnel, five deliveries stack up, and your backend gets a write storm when they come back online. Show HN just lit up with Syncular—offline-first SQL sync with TypeScript and Rust cores—reminding everyone that local-first is not a toy anymore. If you serve mobile, edge, or regulated workflows, you need to treat sync as a product, not a retry policy.

The good news: you can deliver offline-first with plain SQL and a solid sync layer without rewriting your stack or adopting a bespoke database. The bad news: you have to make a handful of non-negotiable design decisions up front—oplog format, conflict semantics, schema migration strategy, selective sync, observability, and device security. Get those right and you’ll get predictable convergence, bounded blast radius, and a backend that doesn’t buckle when 10,000 phones reconnect at 5:01 p.m.

When Offline‑First Actually Pays Back

Offline-first is not a feature; it’s an architectural posture. It pays back when:

• Devices operate in and out of coverage: couriers, inspectors, field service, retail POS, healthcare rounds, and construction sites. Expect disconnections measured in tens of minutes to hours—not seconds.
• Latency matters more than strict serializability: capturing evidence, scanning items, logging notes, or taking payments that can be settled later.
• Privacy or regulatory pressure pushes computation to the edge: on-device PII redaction, clinical notes, or client-side inference.

Brazil and broader LATAM are textbook offline-first markets: dense metros with dead zones, long-haul travel, and a lot of prepaid data plans. If you’re building for these realities (or for US field operations that look the same), you need a sync model that is boringly reliable.

The Minimal Viable Sync (MVS): SQL + Oplogs + Deterministic Merges

You don’t need magic. You need a constrained, testable protocol that runs on your clients and merges into your source of truth (often Postgres):

1) Primary keys are immutable and globally unique

• Use UUIDv7 (timestamp-ordered) or ULIDs for rows created on device. Never repurpose primary keys.
• Keep foreign keys stable; avoid composite keys for sync-critical tables.

2) Changes are expressed as an append-only oplog

• Each client appends operations (insert/update/delete) to a local oplog table with: op_id (UUID), table, row_id, site_id (device), counter (monotonic per device), timestamp, op_type, payload (column deltas), and optional domain metadata.
• Deletes are tombstones with an explicit deleted_at field. Hard-deletes are for archival tasks only.
• Sync batches stream oplog entries to a server endpoint that validates, merges, and returns server-accepted ops plus any missed remote ops for pull.

3) Version vectors for causal ordering

• Maintain a per-device counter and a compact version vector on the server (and optionally per-row) to compute what each side is missing.
• This is how you avoid quadratic diffing and why naive “compare latest timestamp” solutions fall apart under clock skew.

4) Deterministic conflict semantics

• Default to last-writer-wins (LWW) at the column level for non-critical data. It’s simple and predictable.
• For money, inventory, and idempotent counters, use CRDTs (e.g., PN-Counters) or domain-specific merges. Don’t “LWW” currency.
• Use multi-value registers for true conflicts you want humans to resolve; store all contenders and raise a task.

If that sounds like what Syncular, ElectricSQL, cr-sqlite, Realm, Couchbase Lite, and Replicache variants do—it is. The patterns converge because the problem is old. The difference is whether you privilege SQL, CRDT richness, or developer ergonomics.

Conflict Strategy: Be Boring on 80%, Precise on 20%

Most schemas have three categories of tables. Treat them differently.

Category A: Event logs and telemetry (LWW or append-only)

• Examples: audit trails, read receipts, usage logs. You rarely update past rows; you append or LWW trivial fields.
• Storage tip: compress payloads and batch to reduce network chatter.

Category B: Documents and profiles (field-level LWW + human resolution)

• Examples: customer profiles, forms, notes.
• Approach: field-level LWW for most columns; multi-value registers for fields where divergence matters (e.g., legal name). Generate a deterministic conflict key so humans resolve once.

Category C: Counters and inventory (CRDTs or domain merges)

• Examples: stock levels, meter readings, financial adjustments.
• Approach: PN-Counters for increments/decrements; Observed-Remove Sets (OR-Set) for membership; for money, persist immutable ledger entries and compute balances—don’t overwrite balances.

Rule of thumb: aim for under 0.5% of sync batches producing human-visible conflicts in normal use. If you see more, you’re asking humans to debug your consistency model.

Schema Migrations Under Sync Without Taking a Week Off

Schema changes are where offline-first projects go to die. Put guardrails in now:

• Additive-first: add new columns/tables; don’t drop or rename in place. Use backfills and dual-write transforms during a transition window.
• Version-gated sync: each client declares a schema version; the server refuses sync beyond N-1. Ship a forced upgrade path before you ship a destructive migration.
• Transform at the edges: if “status” enum becomes “state + substate,” your sync ingress applies a deterministic transform for old clients and writes both shapes during the cutover.
• Shadow migrations: deploy migrations in two phases—create new structures + transformers, then flip reads, finally clean up after a full device upgrade cycle.

Practical target: support one breaking migration per quarter with a 14–28 day upgrade window. Anything faster and you’ll strand devices.

Selective Sync and Privacy by Default

Pushing the whole database to every device is how you end up on the news. Selectivity is your friend—for performance, cost, and compliance.

• Row-level filters: sync only a user’s teams, assigned routes, or nearby inventory. Encode filter predicates at login and rotate them with auth tokens.
• Column-level redaction: do not sync SSNs, card PANs, or secrets to devices. For sensitive-but-required fields, consider field-level encryption.
• Attachment policy: binaries kill you. Cap initial sync size (e.g., 100–200 MB), chunk large files, and lazy-load previews with explicit user actions.
• Device lifecycle: per-device credentials, attestation where available, remote wipe, and an off-ramp when a device misses check-ins.

If you need end-to-end encryption (E2EE) for certain columns, accept that server-side search and server-initiated transforms on those fields are out. You can still do selective sync on encrypted blobs with filter keys kept server-side, but you won’t index what you can’t see.

Capacity Planning: Don’t Let Reconnect Spikes Torch Your DB

Run the math before you ship:

• Ops per device per day: start with 300–1,000 writes and 2–5× reads for typical field apps.
• Oplog entry size: 150–300 bytes for metadata + deltas if you avoid verbose JSON; 0.5–1.0 KB if you serialize whole rows as JSON. Choose wisely.
• Back-of-the-envelope: 10,000 devices × 1,000 ops/day × 250 bytes ≈ 2.5 GB/day of ingress to your sync layer. If 30% reconnect in a 10‑minute window, you need to absorb ~75 MB of ops per minute, plus read-amplified pulls.
• Batching: cap batches by size (e.g., 256–512 KB) and time (e.g., 250 ms) to smooth reconnect storms.

Is your source-of-truth Postgres? Put a message broker and a dedicated merge service in front of it. Avoid having thousands of devices slam your DB connection pool directly. Consider write-ahead queues per tenant, then apply merges in controlled workers with rate limits.

Observability and SLOs for Sync (So You Can Sleep)

If you can’t see it, it didn’t converge. Treat sync like a payments pipeline with clear SLOs:

Metrics to instrument from day one

• sync_batch_ingest_latency_ms (P50/P95/P99)
• sync_batch_apply_latency_ms (server-side merge)
• device_backlog_ops (gauge) and backlog_age_seconds
• merge_conflict_rate (per table/column)
• time_to_convergence_seconds after reconnection (P50/P95)
• bytes_synced_per_device_per_day and initial_full_sync_bytes

Suggested SLOs

• P95 time to convergence under 120 seconds after a device regains connectivity.
• Merge conflict rate under 0.5% of batches in steady state.
• Initial device sync completes under 5 minutes on median 4G.
• No data loss: deduplicate by op_id; every op is idempotent to apply.

Testing like you mean it

• Jepsen-ish chaos: time skew ±15 minutes, duplicate batches, out-of-order delivery, simulated packet loss.
• Clock discipline: monotonic counters per device; never trust wall clocks for ordering.
• Model-based tests: generate randomized interleavings of operations and assert CRDT invariants or LWW outcomes, then compare to the server’s final state.

Security: Devices Are a Perimeter You Don’t Control

Sync expands your attack surface. Close the obvious holes:

• Per-device credentials and least privilege: each device gets a scoped token that encodes its sync filters; rotate on logout/device loss.
• Transport: enforce TLS 1.3 on modern devices; keep 1.2 only if you absolutely must support legacy Android. Track deprecations (e.g., obsolete TLS 1.2 key exchanges are on the way out per current IETF RFCs) and plan upgrades.
• At-rest on device: OS keystore-backed encryption for local SQLite; wipe on jailbreak/root detection or repeated attestation failure.
• Audit: every merge is attributable—who, what, when, from which device. Keep 30–90 days of oplog for forensics; archive longer if regulated.

What to Use: Build vs Buy (and What Each Actually Does)

• ElectricSQL (Postgres-centric, TypeScript): SQL-first sync, great for web and mobile with familiar tooling. Good if you’re all-in on Postgres schemas and want strong typing.
• cr-sqlite (CRDT layer on SQLite): true multi-master on SQLite via CRDTs; excellent for peer-to-peer or heavy offline edits. You’ll do more integration work.
• Replicache (client-focused, web): optimistic UI, server reconciliation. Great DX; you implement merges on the server.
• Realm/Couchbase Lite (mobile-first, batteries included): robust mobile SDKs with conflict strategies; steeper buy-in and data model differences from plain SQL.
• Syncular (new, TS/Rust cores): SQL sync with offline-first stance. Worth piloting if you want a TypeScript-first developer experience with a Rust performance core.
• LiteFS/Litestream (SQLite replication): fantastic for server failover, not for multi-master edits from many devices. Don’t use these as a mobile sync layer.

Decision heuristic:

• If your source of truth is Postgres and you want SQL schemas end-to-end, start with ElectricSQL-style approaches.
• If you expect heavy concurrent offline edits and peer-to-peer scenarios, lean into CRDT-backed SQLite (cr-sqlite).
• If you prioritize web UX and optimistic updates with custom server logic, Replicache patterns fit well.
• If you want a mobile-first managed path and can accept a new data model, Realm/Couchbase Lite reduce lift.

A 30‑60‑90 Rollout That Won’t Melt Your Ops

Day 0: Draw your blast radius

• Pick one table that represents your heaviest offline edit flow but is not money (e.g., work orders, notes).
• Define SLOs and instrument both client and server.

Days 1‑30: Ship MVS (minimal viable sync)

• Implement local oplog, version vectors, batch push/pull, and LWW merges for the pilot table.
• Cap initial sync size and add per-device credentials.
• Run chaos: time skew, reordering, duplicates. Target P95 convergence under 120 seconds.

Days 31‑60: Add conflict precision and migrations

• Add CRDTs or domain merges to the 20% of fields that matter (counters, sets).
• Introduce schema version gates and run a safe additive migration with dual writes.
• Start selective sync filters (teams, routes).

Days 61‑90: Harden and scale

• Move merges off your primary DB connection pool into a dedicated service with queues and rate limits.
• Introduce field-level encryption for sensitive columns if needed.
• Roll to 10–20% of production users; watch reconnect windows and backlog gauges. Tune batch sizes and backpressure.

At each stage, keep the failure mode the same: convergence delayed, never corruption or data loss. If you can’t make that true, stop and add more determinism before scaling.

Costs You’ll Actually See (and How to Keep Them Predictable)

• Network egress: selective sync saves real money. Capping initial sync to 200 MB and using deltas can cut egress by 50–70% compared to naive whole-row JSON pulls.
• CPU on merges: CRDT merges are cheap (vector math and set ops) compared to conflict-resolution via whole-document diffs. Avoid whole-row JSON blobs; send column deltas.
• Storage: oplog retention at 30–90 days is often sub‑10% of total DB size if you compact batches and compress. Archive older history to cold storage.
• Team cost: expect 2–4 experienced engineers full-time for 2–3 quarters to take sync from pilot to boring. Nearshore pods in Brazil with 6–8 hours of time overlap can cut cash burn by 20–30% while keeping velocity.

Pitfalls That Kill Offline Projects

• Clock worship: relying on timestamps instead of counters/vectors. Under skew, you will lose writes.
• Silent drops: failing to persist the oplog durably before ACKing to the user. Crash -> lost change.
• Schema roulette: renames and destructive migrations without a version gate. You’ll strand devices.
• Attachments everywhere: images and PDFs in the same sync channel as critical data. Split the lanes.
• All-or-nothing UX: forcing full convergence before the user can continue. Let the app be useful under partial sync with clear indicators.

The Bottom Line

Offline-first is having a moment because the ecosystem finally makes it tractable in mainstream stacks. SQL-centric sync (as trending with projects like Syncular) means your team can own the model, test it like any other distributed system, and keep your operational envelope tight. Treat sync as a product: minimal viable protocol, conflict precision where it matters, migrations that don’t strand devices, selective sync for privacy and cost, and SLOs you can explain to your CEO.

Key Takeaways

• Use SQL plus an append-only oplog and version vectors; avoid timestamp-only ordering.
• Apply LWW to 80% of fields; use CRDTs or domain merges for money, inventory, and counters.
• Design migrations as additive with version gates; transform at ingress/egress during cutovers.
• Selective sync is non-negotiable—for privacy, cost, and performance. Cap initial sync size.
• Instrument real SLOs: P95 convergence under 120s; conflict rate under 0.5%.
• Protect the edge: per-device credentials, TLS 1.3 where possible, keystore-backed at-rest encryption, remote wipe.
• Plan capacity for reconnect spikes; decouple merges from your primary DB with queues and rate limits.
• Expect 2–4 engineers for 2–3 quarters to make sync boring; nearshore pods can cut costs by 20–30% with 6–8 hours overlap.