I Built My Own Sync Gateway Instead of Using Couchbase. Here's How.

Push queues, pull checkpoints, safe conflict merges, and keeping secrets off the wire. All from a real offline-first healthcare app.

---

When we started on the offline sync for our healthcare app, I seriously considered Couchbase Sync Gateway and Firestore. Both are good. Both handle offline sync well for most apps.

Then we wrote down our constraints:

• Doctors in remote clinics with no signal for hours
• Patient records where an allergy can never be silently overwritten
• PIN hashes and recovery secrets that must never leave the device they were created on
• The whole backend has to run on a laptop in a clinic, with no cloud

That last one settled it. We built our own.

The shape of it

Two stores, one protocol. The phone has SQLite with a full local copy and a queue of unsent changes. The server has PostgreSQL as the source of truth, with a server_seq number on every change.

The phone pushes its changes up and pulls the server's changes down. Everything interesting happens in how you order, dedupe and merge those two flows.

1. The phone never does a plain UPDATE

Every edit creates a new revision with its own hash and a parent_rev pointing at the version it was based on. In one transaction, we write the revision and add a row to the queue:

CREATE TABLE sync_queue (
  operation_id TEXT PRIMARY KEY,   -- UUID, never changes
  collection   TEXT NOT NULL,
  document_id  TEXT NOT NULL,
  rev          TEXT NOT NULL,
  status       TEXT DEFAULT 'pending',  -- pending, sending, sent, failed
  attempts     INTEGER DEFAULT 0
);

That operation_id matters more than it looks. If the server saves a change but the response gets lost on a bad connection, the phone retries. The server sees the same operation_id, says "already have it," and returns ok. No double writes.

2. Secrets are stripped before they leave

PIN hashes, recovery hashes and lockout counters live on the device only. The push engine removes them before building the payload, and the server strips them again on the way out of pull. Belt and suspenders.

const DEVICE_ONLY_FIELDS = [
  'pin_hash', 'recovery_hash', 'failed_pin_attempts', 'pin_locked_until'
];

3. One push at a time

Every push runs in a Postgres transaction, and the first thing it does is take an advisory lock:

conn.execute("SELECT pg_advisory_xact_lock(%s)", (_PUSH_LOCK_KEY,))

Why? Pull uses server_seq as a checkpoint. If push A gets seq 10, push B gets seq 11, and B commits first, a pull in between moves its checkpoint past 10. Seq 10 is then missed forever. The lock keeps sequence order equal to commit order. For field-deployment write volumes, the cost is nothing.

For each incoming change, the server asks:

1. Seen this operation_id? Return duplicate.
2. Does this document belong to this account? If not, reject.
3. Does parent_rev match the server's current rev? Clean write, assign a new server_seq.
4. Does rev already equal the server's rev? Idempotent, return ok.
5. Otherwise it's a conflict. The server version stays as the winner.

4. Why last-write-wins wasn't allowed

Two health workers, offline, each add a different allergy to the same patient. Last-write-wins would quietly delete one of them. In a clinic, that's not a bug, it's a risk.

So we built a safety merge. On a conflict, the server checks whether the losing version has allergies, conditions or medications the winner lacks. If so, it creates a new merged revision and that becomes the truth:

for list_key in ("allergies", "conditions", "medications"):
    existing = {i["id"]: i for i in server_body.get(list_key, [])}
    for item in incoming_body.get(list_key, []):
        if item["id"] not in existing:
            existing[item["id"]] = item
            changed = True
    merged[list_key] = list(existing.values())

The merged revision gets source = 'merge' and its own server_seq, so every device pulls it. The losing revision isn't deleted. It's kept as conflict_loser, so there's a full audit trail.

5. Pull with a checkpoint, crash-safe

Each account stores a last_pulled_seq. A pull cycle looks like this:

1. GET /sync/pull?since={checkpoint}&limit=200
2. For each change: already have the rev, skip it. Have unsent local edits for that doc, run the conflict merge. Otherwise apply it to SQLite.
3. Move the checkpoint to the highest seq seen, in the same transaction as the document writes.
4. If has_more, repeat.

Same transaction is the whole trick. If the app dies halfway through a page, SQLite rolls back the documents and the checkpoint together. The next pull re-fetches that page cleanly. No partial state.

6. New phone, same flow

Log in, the gateway returns the profile (with device-only fields already stripped), the checkpoint is set to 0, and a full pull rebuilds the local database. The user sets a fresh PIN, and that hash stays local from day one.

The cheat sheet

Problem	What we did
Offline storage	SQLite with immutable revisions
Duplicate writes	Unique operation_id per change
Missed sequences	Postgres advisory transaction lock
Delta sync	server_seq checkpoint, paged pull
Conflict audit	Losers kept as conflict_loser
Safety-critical data	Domain-aware merge on allergies, meds, conditions
Secrets	Stripped on client and server
New device	Authenticate, checkpoint 0, full pull

The best part: the backend can run on a laptop in a clinic, and every phone syncs to it over a local Wi-Fi hotspot. No cloud needed.

Should you build your own?

Probably not, if Couchbase or Firestore fits. But if you have strict conflict rules, secrets that can't leave the device, or no cloud, it's very doable. You need four things: an append-only queue, monotonic sequences, idempotent operations, and conflict rules that match your domain.

If you want a deep dive on peer-to-peer sync between phones over local Wi-Fi, or how we migrated old queue formats, drop a comment and I'll write it up.