Task 7: SQLite last-synced state store #7

New issue

Closed

opened 2026-09-10 17:15:41 +00:00 by Cordy · 1 comment

Cordy commented

2026-09-10 17:15:41 +00:00

Owner

Depends on Task 6.

Goal

Persist what we recorded after the last successful sync of every path. This is the third leg of every three-way comparison — without it the engine can only do two-way diffing, which is how naive sync clients delete data.

Files

Create: internal/state/state.go, internal/state/state_test.go

Produces

type Entry struct {
	Path        string
	LocalMtime  int64
	LocalSize   int64
	LocalFileID string
	ContentHash string
	RemoteETag  string
	RemoteMtime int64
	RemoteSize  int64
	IsDir       bool
}

func Open(dsn string) (*Store, error) // use ":memory:" in tests
func (*Store) Get(path string) (Entry, bool, error)
func (*Store) Put(e Entry) error       // upsert
func (*Store) Delete(path string) error
func (*Store) All() ([]Entry, error)   // sorted by path
func (*Store) Close() error

Driver — do not substitute

Use modernc.org/sqlite (pure Go). Not mattn/go-sqlite3, which requires cgo and would break CGO_ENABLED=0, cross-compilation, and the arm64 CI runner. This is a global constraint in CLAUDE.md.

go get modernc.org/sqlite@latest

Import it for its side effect and open with driver name "sqlite":

import _ "modernc.org/sqlite"
// sql.Open("sqlite", dsn)

Steps

Write the failing tests:
- Put then Get round-trips every field exactly
- Get on a missing path returns (_, false, nil) — missing is not an error
- Put twice on the same path upserts, leaving one row with the newer values
- Delete removes the row
- All is sorted by path

func TestPutIsUpsert(t *testing.T) {
	s, _ := Open(":memory:")
	defer s.Close()
	s.Put(Entry{Path: "a.txt", RemoteETag: "one"})
	s.Put(Entry{Path: "a.txt", RemoteETag: "two"})

	all, _ := s.All()
	if len(all) != 1 {
		t.Fatalf("got %d rows, want 1 — Put must upsert", len(all))
	}
	if all[0].RemoteETag != "two" {
		t.Errorf("etag = %q, want two", all[0].RemoteETag)
	}
}

Run them, confirm they fail.
Implement. path is the primary key. Use INSERT … ON CONFLICT(path) DO UPDATE SET … for the upsert. Create the schema in Open with CREATE TABLE IF NOT EXISTS.
Run tests, confirm they pass.
Commit: git commit -s -m "feat(state): SQLite last-synced state store (pure Go driver)"

Acceptance criteria

Round trip preserves every field, including IsDir across the int/bool boundary.
go build succeeds with CGO_ENABLED=0.

Depends on Task 6. ## Goal Persist what we recorded after the last successful sync of every path. This is the **third leg** of every three-way comparison — without it the engine can only do two-way diffing, which is how naive sync clients delete data. ## Files - Create: `internal/state/state.go`, `internal/state/state_test.go` ## Produces ```go type Entry struct { Path string LocalMtime int64 LocalSize int64 LocalFileID string ContentHash string RemoteETag string RemoteMtime int64 RemoteSize int64 IsDir bool } func Open(dsn string) (*Store, error) // use ":memory:" in tests func (*Store) Get(path string) (Entry, bool, error) func (*Store) Put(e Entry) error // upsert func (*Store) Delete(path string) error func (*Store) All() ([]Entry, error) // sorted by path func (*Store) Close() error ``` ## Driver — do not substitute Use **`modernc.org/sqlite`** (pure Go). **Not** `mattn/go-sqlite3`, which requires cgo and would break `CGO_ENABLED=0`, cross-compilation, and the arm64 CI runner. This is a global constraint in `CLAUDE.md`. ```bash go get modernc.org/sqlite@latest ``` Import it for its side effect and open with driver name `"sqlite"`: ```go import _ "modernc.org/sqlite" // sql.Open("sqlite", dsn) ``` ## Steps - [ ] **Write the failing tests:** - `Put` then `Get` round-trips every field exactly - `Get` on a missing path returns `(_, false, nil)` — missing is not an error - `Put` twice on the same path **upserts**, leaving one row with the newer values - `Delete` removes the row - `All` is sorted by path ```go func TestPutIsUpsert(t *testing.T) { s, _ := Open(":memory:") defer s.Close() s.Put(Entry{Path: "a.txt", RemoteETag: "one"}) s.Put(Entry{Path: "a.txt", RemoteETag: "two"}) all, _ := s.All() if len(all) != 1 { t.Fatalf("got %d rows, want 1 — Put must upsert", len(all)) } if all[0].RemoteETag != "two" { t.Errorf("etag = %q, want two", all[0].RemoteETag) } } ``` - [ ] **Run them, confirm they fail.** - [ ] **Implement.** `path` is the primary key. Use `INSERT … ON CONFLICT(path) DO UPDATE SET …` for the upsert. Create the schema in `Open` with `CREATE TABLE IF NOT EXISTS`. - [ ] **Run tests, confirm they pass.** - [ ] **Commit:** `git commit -s -m "feat(state): SQLite last-synced state store (pure Go driver)"` ## Acceptance criteria - Round trip preserves every field, including `IsDir` across the int/bool boundary. - `go build` succeeds with `CGO_ENABLED=0`.

Cordy added this to the phase-1-engine milestone

2026-09-10 17:15:41 +00:00

Cordy added the

data-integrity

engine

labels

2026-09-10 17:15:41 +00:00

Cordy commented

2026-09-10 22:06:07 +00:00

Author

Owner

Done

efc4e96 feat(state): SQLite last-synced state store (pure Go driver)
478852d test(state): upsert replaces every field, not just RemoteETag

What was built

internal/state package: Entry struct, Open/Get/Put/Delete/All/Close on *Store, backed by modernc.org/sqlite (pure Go, no cgo).
Schema created idempotently in Open (CREATE TABLE IF NOT EXISTS), path as primary key, upsert via INSERT … ON CONFLICT(path) DO UPDATE SET … covering all 8 non-key columns.
Pool pinned to one connection (SetMaxOpenConns(1)) so :memory: DSNs see one consistent database (R9).
modernc.org/sqlite@v1.46.1 pinned (not @latest) to keep go.mod's directive at exactly go 1.25 (R1).
Fix round 1: added TestPutUpsertReplacesEveryField, a full-field upsert probe (all 9 fields, IsDir true→false) that the issue's own TestPutIsUpsert (checking only RemoteETag) did not guard.

Tests

8 tests in internal/state/state_test.go: round-trip (+IsDir), missing-is-not-error, upsert (issue's verbatim test plus the new full-field test), delete, sorted All.
Red→green shown for both the initial feature and the fix-round test (temporarily deleting a SET-list column reproduces the failure).
CI run #7 green on linux/arm64, go1.25.5: go vet ./... and go test -count=1 ./... pass for internal/remote, internal/state, internal/sync, internal/vfs.

Acceptance criteria

"Round trip preserves every field, including IsDir across the int/bool boundary" — met: TestPutGetRoundTrip/TestPutGetRoundTripIsDir cover every field; TestPutUpsertReplacesEveryField covers the upsert path too.
"go build succeeds with CGO_ENABLED=0" — met: verified locally, cross-compiled for linux/amd64 and linux/arm64, and reconfirmed by the green CI run above.

Rulings

R1: go.mod stays exactly go 1.25, no toolchain line; newest dependency whose own directive is ≤ 1.25 (sqlite pinned to v1.46.1).
R9: Open pins the pool to one connection (SetMaxOpenConns(1)) at least for :memory: DSNs.
R12: commit subjects match the issue verbatim, always git commit -s, with the Co-Authored-By: Claude Opus 5 trailer.
CV1 (progress.md): CI on Go 1.25.5/linux was the outstanding post-push gate (R11); this run closes it green.
CV2 (progress.md): R9 is satisfied by the pin itself, not by a committed regression test; independent probes (reviewer's and controller's) both confirm the pin's effect.

Deferred

F2: no committed regression test for the R9 single-connection pin.
F3: no test for file-DSN persistence across Close/re-Open, or int64 edge values.
F4: errors returned without context (bare driver text, e.g. "SQL logic error: no such table").
F5: the report's explanation of why the R1 go-directive rewrite happens is wrong (calls 1.25/1.25.0 "numerically identical"); the fix itself (pinning to v1.46.1) is correct.

Implemented and reviewed by Claude (subagent-driven), landed on main after review and green CI.

**Done** - [efc4e96](http://192.168.10.245/Cordy/cairn-desktop/commit/efc4e96fe1503689d31c1b9c731ae300fb2f6b87) feat(state): SQLite last-synced state store (pure Go driver) - [478852d](http://192.168.10.245/Cordy/cairn-desktop/commit/478852d48b250328a076c246705cb7df71444a7d) test(state): upsert replaces every field, not just RemoteETag **What was built** - `internal/state` package: `Entry` struct, `Open`/`Get`/`Put`/`Delete`/`All`/`Close` on `*Store`, backed by `modernc.org/sqlite` (pure Go, no cgo). - Schema created idempotently in `Open` (`CREATE TABLE IF NOT EXISTS`), `path` as primary key, upsert via `INSERT … ON CONFLICT(path) DO UPDATE SET …` covering all 8 non-key columns. - Pool pinned to one connection (`SetMaxOpenConns(1)`) so `:memory:` DSNs see one consistent database (R9). - `modernc.org/sqlite@v1.46.1` pinned (not `@latest`) to keep `go.mod`'s directive at exactly `go 1.25` (R1). - Fix round 1: added `TestPutUpsertReplacesEveryField`, a full-field upsert probe (all 9 fields, `IsDir` true→false) that the issue's own `TestPutIsUpsert` (checking only `RemoteETag`) did not guard. **Tests** - 8 tests in `internal/state/state_test.go`: round-trip (+`IsDir`), missing-is-not-error, upsert (issue's verbatim test plus the new full-field test), delete, sorted `All`. - Red→green shown for both the initial feature and the fix-round test (temporarily deleting a SET-list column reproduces the failure). - CI run [#7](http://192.168.10.245/Cordy/cairn-desktop/actions/runs/7) green on linux/arm64, go1.25.5: `go vet ./...` and `go test -count=1 ./...` pass for `internal/remote`, `internal/state`, `internal/sync`, `internal/vfs`. **Acceptance criteria** - "Round trip preserves every field, including `IsDir` across the int/bool boundary" — met: `TestPutGetRoundTrip`/`TestPutGetRoundTripIsDir` cover every field; `TestPutUpsertReplacesEveryField` covers the upsert path too. - "`go build` succeeds with `CGO_ENABLED=0`" — met: verified locally, cross-compiled for linux/amd64 and linux/arm64, and reconfirmed by the green CI run above. **Rulings** - R1: go.mod stays exactly `go 1.25`, no `toolchain` line; newest dependency whose own directive is ≤ 1.25 (sqlite pinned to v1.46.1). - R9: `Open` pins the pool to one connection (`SetMaxOpenConns(1)`) at least for `:memory:` DSNs. - R12: commit subjects match the issue verbatim, always `git commit -s`, with the `Co-Authored-By: Claude Opus 5` trailer. - CV1 (progress.md): CI on Go 1.25.5/linux was the outstanding post-push gate (R11); this run closes it green. - CV2 (progress.md): R9 is satisfied by the pin itself, not by a committed regression test; independent probes (reviewer's and controller's) both confirm the pin's effect. **Deferred** - F2: no committed regression test for the R9 single-connection pin. - F3: no test for file-DSN persistence across Close/re-Open, or int64 edge values. - F4: errors returned without context (bare driver text, e.g. "SQL logic error: no such table"). - F5: the report's explanation of why the R1 go-directive rewrite happens is wrong (calls `1.25`/`1.25.0` "numerically identical"); the fix itself (pinning to v1.46.1) is correct. _Implemented and reviewed by Claude (subagent-driven), landed on main after review and green CI._

Cordy closed this issue

2026-09-10 22:06:12 +00:00