def sync(dbpath, eventspath, boardpath, dryrun=False):
Root cause. board.db is a cache; events.jsonl + board.jsonl (git-tracked) are the source of truth. When the DB lagged by non-contiguous events (ids 47 and 51 missing), board-idle-append.py derived next_id = max(id in db) + 1 from the stale DB — which collided with an id already present in events.jsonl. Since the JSONL is plain text with no primary key, the duplicate id line was written silently.
Fix: sync FROM JSONL first, then append. New board_sync.py does the three steps from the spec — (1) INSERT missing rows by id, (2) UPDATE header from board.jsonl, (3) VERIFY count equality — inside one transaction, then board_idle_append.py appends with a hard stale-DB guard.
# board_sync.py — run BEFORE any append (pipeline: sync && append)
def sync(db_path, events_path, board_path, dry_run=False):
header = load_header(board_path) # board.jsonl = header truth
events, by_id = load_events(events_path) # events.jsonl = event truth
jsonl_sha = events_sha256(events)
con = connect(db_path) # events(id BIGINT PRIMARY KEY, ...)
db_before = {r[0] for r in con.execute("SELECT id FROM events").fetchall()}
missing = sorted(set(by_id) - db_before)
if dry_run:
print(f"dry-run: {len(missing)} missing rows: {missing}"); return
con.execute("BEGIN TRANSACTION")
try:
# (1) INSERT missing rows BY ID
for eid in missing:
row = by_id[eid]
con.execute("INSERT INTO events (id, ts, payload) VALUES (?, ?, ?::JSON)",
[row["id"], row.get("ts"), json_canonical(row.get("payload"))])
# (2) UPDATE header from board.jsonl
set_meta(con, "event_count", str(len(events)))
set_meta(con, "latest_id", str(max(by_id)))
set_meta(con, "events_sha256", jsonl_sha)
# (3) VERIFY count equality
verify(con, events, by_id, header, jsonl_sha) # raises SyncError on any mismatch
con.execute("COMMIT")
except BaseException:
con.execute("ROLLBACK"); raise
The verify step enforces four equalities — id sets, per-row content, len(events.jsonl) == COUNT(events) == header.event_count, and a sha256 of DB vs JSONL — and aborts with exit 1 on drift.
The append script's guard prevents the original failure mode even if sync is skipped:
# board_idle_append.py
ids = jsonl_ids(events_path)
next_id = (max(ids) + 1) if ids else 1
db_max = con.execute("SELECT COALESCE(MAX(id), 0) FROM events").fetchone()[0]
if db_max != next_id - 1:
raise SystemExit("FATAL: board.db is stale — run board_sync.py first. "
"Otherwise id %d would be reused/duplicated." % next_id)
`python3 tests/test_lag_sync.py` — **12/12 passed** (exit 0): | Test | Verifies | |---|---| | `sync_repairs_noncontiguous_lag` | Reported case: DB missing ids 47, 51 → sync INSERTs both by id; counts equal (jsonl=60 db=60 header=60); meta/sha updated | | `naive_append_without_sync_would_reuse_existing_id` | Bug reproduced: naive `max(db)+1` on lagging DB wrote a **second id=51 line** into events.jsonl | | `sync_then_append_fixes_the_duplicate_bug` | Same stale state + fix → sync restores, append emits id 61, JSONL has 61 unique ids | | `append_guard_refuses_stale_db` | Append on stale DB exits 1 and writes nothing | | `sync_is_idempotent` | Fully-synced DB → 0 inserts, JSONL untouched, still verifies | | `sync_detects_header_count_mismatch` / `sync_detects_content_drift` / `sync_detects_extra_db_rows` | Header count, sha256 payload drift, and orphan DB rows all abort with exit 1 | | `sync_rejects_duplicate_ids_in_jsonl` | Dup id line in source-of-truth JSONL → hard error, never silently fixed | | `sync_repairs_multiple_gaps_including_edges` | Holes at head/middle/tail (1,2,3,47,51,78,79,80) all restored | | `dry_run_reports_without_writing` | `--dry-run` previews `[47, 51]`, modifies nothing | End-to-end smoke test of `pipeline.sh`: stale DB (missing 7, 11) → sync repaired → append produced id 21; final state: 21 JSONL lines, 21 unique ids, DB count 21, header/meta agree.
{"model": "deepseek-v4-flash", "problem_class": "duckdb-board-jsonl-lag-sync", "result": "passed", "tests": 12}