​

ADR-0040: FieldForce offline + realtime sync — adopt PowerSync over a hand-built sync engine

​

Status

​

Tags

​

Decision

• The client keeps a local SQLite database that PowerSync keeps in sync with the existing Postgres via logical replication. The app reads and writes locally and works fully offline.
• Reads are realtime: PowerSync streams changes into local SQLite; reactive queries re-run on change. This is the realtime mechanism for all synced data — there is no hand-built WebSocket for synced entities.
• Writes flow through PowerSync’s upload queue → a uploadData() function → the Go API → Postgres. The Go backend remains the single source of truth and write authority, and performs server-authoritative conflict resolution (last-write-wins default).
• Upload contract: one endpoint (POST /api/mobile/sync/upload), batched CRUD ops, per-op results (applied | rejected{code}). Every op carries a UUIDv7 op id; the server dedupes on it, so at-least-once replay is safe. LWW compares at row level using server-arrival time — device clocks are never trusted (CLAUDE.md §14).
• Rejected-mutation policy (dead-letter): uploadData() distinguishes transient from permanent failures. Transient (network, 5xx, 429) → rethrow so PowerSync retries with backoff. Permanent (4xx: validation, authorization, missing parent) → the op is acknowledged anyway — the queue must never wedge behind an unappliable mutation. The Go backend records the rejection in a rejected_mutations table synced down into the user’s bucket, so the UI surfaces “this change couldn’t be applied” reactively; dismissing a rejection is itself a normal synced mutation.
• Sync Rules define per-user / per-team buckets, scoped by the identity in the JWT the Go backend mints (see ADR-0041).
• The split between “what the engine owns” and “what we own” is deliberate and divergent:

​

Why

• It is non-invasive to the existing Postgres — connects via logical replication (the same change-data-capture mechanism as Debezium) with minimal read-only permissions, riding alongside the database already in use rather than replacing the backend. The Go backend remains write authority.
• It is production-proven for exactly this scenario (offline field work), removing the bus-factor risk of the young indie Flutter sync packages.
• It eliminates client-side migrations by syncing schemaless and projecting the client schema as SQLite views — schema evolution becomes “update the schema definition + Sync Rules,” not migration scripts, and offline-created rows in the upload queue survive schema changes by construction. This removes the “migration that mustn’t lose a field worker’s queued work” failure mode.
• Its offline-then-reconnect model means background suspension is just another disconnect — it pairs cleanly with FCM/APNs wake-ups (ADR-0044), so lifecycle-aware connection management for synced data is not hand-built.

• Hand-built Drift + outbox sync engine. The documented Flutter community pattern (Drift tables + operation queue + connectivity detection + custom sync loop). Full control, no vendor — but we would own every offline-sync bug class precisely where the project wants the least debugging. Rejected: re-solving a solved problem against stated priorities.
• Indie Flutter sync packages (offline_sync_engine, SynapseLink, flutter_sync_engine). Backend-agnostic and a fit for the Go API, but young, mostly solo-maintained, with real longevity/bus-factor risk for a load-bearing foundation. Rejected for the MVP foundation.
• Backend-coupled BaaS (Supabase + syncable). Mature offline sync, but hard-couples to a foreign backend and displaces the Go/Postgres source of truth. Rejected — it inverts the architecture.
• Letting the PowerSync client resolve conflicts / be authoritative. Simpler on paper, but makes the device a second source of truth and bypasses Go business rules. Rejected — conflicts resolve server-side at the Go API.
• Routing background-location GPS telemetry through PowerSync buckets. Rejected — high-volume append-only pings would bloat buckets past PowerSync’s per-bucket sweet spot (thousands–tens of thousands of rows, not millions). Location uses its own queue → dedicated Go endpoint (ADR-0043).

​

Consequences

• Sync is at-least-once with server-authoritative resolution: a contested offline edit can be overwritten by the server’s decision on reconnect; users may see a value change after sync. Acceptable for field work; surfaced in UI where material.
• Sync Rules become a first-class design and test artifact — buckets must be scoped tightly per user/team and tested per user scenario; incorrect rules either leak data across tenants or fail to deliver a user’s own data.
• uploadData() ↔ Go API is the one contract we must get right, including idempotency (UUIDv7 client keys + per-op ids make replayed writes safe) and conflict policy. It is a thin driven adapter.
• A permanently rejected offline write is surfaced, not silently dropped: the user watched it “save” locally, so its later rejection must appear in UI (via the synced rejected_mutations row). The dead-letter path is integration-tested (CLAUDE.md §17) — the failure mode it prevents (a wedged upload queue) blocks all subsequent writes.
• The self-hosted PowerSync Service is another homelab component (Docker on K3s). Bucket-state storage backend (Postgres vs Mongo) must be confirmed against current self-host docs at deploy time; prefer Postgres to avoid reintroducing Mongo.
• A failover/outage beyond local retention, or a very large initial bucket, can make first-sync slow or storage-heavy — mitigated by tight Sync Rules.
• All entity primary keys are UUIDv7, in both Postgres and client SQLite, so offline-created rows have stable IDs before the server sees them.

​

Rules for agents

• The Go backend is ALWAYS the write authority. The mobile client MUST NOT write directly to Postgres or resolve conflicts on-device.
• Writes go: local SQLite → PowerSync upload queue → uploadData() → Go API → Postgres. Conflict resolution is server-authoritative (LWW default).
• Do NOT hand-build a WebSocket or polling loop for synced data — PowerSync reactive queries are the realtime mechanism.
• ALL meaningful local state lives in PowerSync-synced tables. Do NOT introduce a separate Drift/sqflite database for app state unless explicitly justified and documented.
• Do NOT hand-write client-side schema migrations — evolve the client schema definition + Sync Rules. Data transformation logic belongs in the Go backend.
• All entity primary keys are client-generatable UUIDv7.
• Scope Sync Rules tightly per user/team; treat bucket correctness as a tested invariant.
• Do NOT route high-volume background-location telemetry through PowerSync buckets — it uses its own queue → dedicated Go endpoint (ADR-0043).
• NEVER let a permanent (4xx) rejection wedge the upload queue: acknowledge the op, dead-letter it to rejected_mutations, surface it in UI. Only transient failures (network, 5xx, 429) rethrow for retry.
• Every uploaded op carries a UUIDv7 op id; LWW is row-level on server-arrival time — never trust device clocks for conflict resolution.