Cloud architecture

Fexl Lite’s cloud architecture is unusually simple for a sync system: one Go binary, two databases, one adapter pattern, one shared row-scanner. There is no microservice fleet and no eventing pipeline — just push-then-pull every 30 seconds, with cloud as primary and local SQLite as a hot backup. This page sketches what’s actually running, where the seams are, and why the choices look the way they do.

Updated 5 May 2026·For v2.2.0·7 min read

sync · architecture diagram · desktop sqlite, sync worker, cloud postgres

One binary, two database modes

The Go server in apps/retail/server-go/ builds to a single binary. At runtime, the DB_TYPE environment variable picks the database adapter:

• DB_TYPE=sqlite — the desktop binary. Reads / writes to a local SQLite file (fexl.db in the per-platform data dir). Embedded as a Tauri sidecar, listens on localhost:8089, lives the whole lifetime of the desktop app.
• DB_TYPE=postgres — the cloud binary. Reads / writes to PostgreSQL via pgx. Deployed via Docker (Dockerfile.server), listens on a load-balancer port, scales horizontally if needed.

Both build from the same Go code. The adapter pattern in db/adapter.go defines a DB interface; db/sqlite.go and db/postgres.go are the two implementations. Handler code in handlers/ calls the interface and never knows which it’s talking to.

This matters because every fix in the Go server lands on both desktop and cloud at the same time — no shipping a feature on cloud and waiting for a desktop release.

The shared scanRows

Both adapters use the same scanRows() function in db/sqlite.go to convert a sql.Rows cursor into []map[string]any for the JSON encoder. PostgreSQL via pgx returns numeric columns as []byte, and scanRows normalises those into either int64 or string depending on context. The frontend always treats numeric API fields as “might be a string” and runs Number() over them before arithmetic.

This is the single most important gotcha to know if you’re touching the API. A reduce that adds invoice totals will produce string concatenation instead of addition the moment cloud sync is enabled — the same code path was correct against SQLite. See the numeric-coercion notes in CLAUDE.md for the longer story.

How a desktop reaches data

When cloud sync is enabled:

React frontend  →  api.client (cloud URL primary)
                 ↓
                 Cloud Postgres (DB_TYPE=postgres)
                 ↓ on timeout / 5xx
                 fall over to localhost:8089
                 ↓
                 Local SQLite (DB_TYPE=sqlite)

When cloud sync is disabled, the API client only knows about localhost:8089 and never reaches out. This is the default for new installs; you opt in to cloud sync explicitly.

The sync worker

Per desktop, a single goroutine runs the sync loop:

1

Tick every 30 seconds

The worker wakes up. Healthcheck GET /api/sync/health against the cloud, with a 5-second timeout. Failure → bail out, wait for the next tick.

2

Push the dirty rows

Find every locally-modified row (_isDirty = 1). Bundle them into a payload — products, customers, invoices, journal entries, the lot — and POST to /api/sync/push. The cloud writes them, runs FK resolution, and returns a snapshot of confirmed UUIDs.

3

Pull what changed elsewhere

GET /api/sync/pull?since=<lastSyncedAt>. The cloud returns every row that updated since this device’s last successful pull. Local SQLite merges them in, resolving conflicts last-write-wins on updated_at.

4

Stamp success

Every row that round-tripped successfully gets _lastSyncedAt = now, _isDirty = 0. The next tick is in 30 seconds.

A separate WebSocket subscription runs alongside the cycle, listening for events the cloud broadcasts (invoice:created from another device, product:updated, etc.). Events arrive in under a second; the 30-second cycle is the safety net for events the WebSocket missed.

FK resolution

When a desktop creates a new invoice while offline, the invoice’s customer row also might be a brand-new offline-created customer. The local SQLite uses string UUIDs as primary keys, but the cloud Postgres assigns its own UUIDs at insert time. The FK resolver in apps/retail/server-go/sync/ fixes this:

1. Push payload includes the customer row first, then the invoice row.
2. The cloud writes the customer, gets back a Postgres-assigned UUID.
3. The resolver substitutes the new UUID into the invoice row’s customer_id field before writing the invoice.
4. The push response stamps the original local UUIDs with their cloud equivalents (_cloudId field), so future writes route correctly.

Without this, FK constraint violations would block every offline-bundled write. With it, offline batches reconcile cleanly on reconnect.

ⓘ

Local UUIDs stay stable

The desktop never overwrites its local primary keys. A row created locally as UUID abc-123 keeps that key forever. The cloud’s UUID is stamped as _cloudId for cross-reference, but the local key is what local FKs point at.

Multi-tenancy

Every table has a tenant_id column. Every query filters on it. The middleware in middleware/tenant.go extracts the tenant from the auth header and rejects any request with a missing or mismatched tenant ID. There is no row-level cross-tenant access path.

On the desktop, the tenant ID is set at pairing time and stamped into every API request. On the cloud, it’s derived from the device’s session token. A device can belong to exactly one tenant at a time.

Where state lives

Concern	Stored on
Products, customers, invoices, journal entries	Both — full mirror, last-write-wins
Per-device printer path, terminal config	Desktop only — cloud sync ignores these fields
User PINs	Both, hashed
Settings document	Both — last-write-wins; conflicts here are rare since one person edits at a time
Attachments (PDFs, images)	Cloud Postgres + S3-compatible storage; desktop fetches on demand and caches
Ledger account balances (cached)	Both — recomputed from journal entries on write

Related

SYNC

Sync overview

The section landing — high-level architecture and pairing.

CONFLICT

Conflict resolution

Last-write-wins, the duplicate-UUID retry path, and the corner cases.

DEVICES

Pair, approve, revoke

The device list, the approval flow, and what revoke does.

MANUAL

Manual sync

The Force sync now button — when to use it, what it actually does.