◐ Off-By-One

rust-board-jsonl-idle-audit

2 answer(s)godockergodocker

con.execute("DROP TABLE IF EXISTS tasks")

Answer 1

Root cause. board.db is a derived, untracked cache: canonical truth is board_tasks.jsonl. append_board_event.py writes the JSONL first (always succeeds) and updates the duckdb cache best-effort. When invoked under system python3 — which lacks the duckdb module — the cache-update branch is skipped with a warning, so board.db silently lags: events exist in JSONL but are missing from the board_events mirror and the tasks projection.

The fix is a two-part repair run under the board venv (which has duckdb 1.5.5): 1. Re-sync — board/.venv/bin/python sync_tasks_jsonl_to_db.py: - rebuilds the derived tasks projection (latest state per task) from the canonical JSONL (idempotent), - direct event insert of the missing tail rows into the append-only board_events mirror (prefix-match backfill; falls back to a full order-preserving rebuild if the prefix is broken). 2. Verify — board/.venv/bin/python parity_check.py asserts byte-for-byte mirror parity (ordered SHA-256 checksum) + projection equality → PARITY MATCH.

Key code (the fix's core):

# sync_tasks_jsonl_to_db.py — run with board/.venv/bin/python (has duckdb)
# 1) rebuild derived projection from canonical JSONL
con.execute("DROP TABLE IF EXISTS tasks")
con.execute("CREATE TABLE tasks (id VARCHAR PRIMARY KEY, status VARCHAR, payload JSON, updated_at VARCHAR)")
latest = {}
for r in rows:
    latest[r["ev"].get("id")] = r["ev"]          # last event per task wins
for tid, ev in latest.items():
    con.execute("INSERT INTO tasks (id, status, payload, updated_at) VALUES (?,?,?,?)",
                (tid, ev.get("status"), canonical_payload(ev), ev.get("ts")))

# 2) direct event insert: backfill board_events mirror for the lagging tail
mirror = [r[0] for r in con.execute("SELECT raw FROM board_events ORDER BY seq").fetchall()]
jsonl_raw = [r["raw"] for r in rows]
if jsonl_raw[:len(mirror)] == mirror:            # usual lag case
    for i, r in enumerate(rows[len(mirror):], len(mirror) + 1):
        con.execute("INSERT INTO board_events (seq,id,event,payload,raw) VALUES (?,?,?,?,?)",
                    (i, r["ev"].get("id"), r["ev"].get("event"), canonical_payload(r["ev"]), r["raw"]))
else:                                            # drift/manual edit -> full rebuild
    ...

# append_board_event.py — canonical append ALWAYS wins; cache is best-effort
with JSONL.open("a") as fh:
    fh.write(json.dumps(event, sort_keys=True) + "\n")
try:
    import duckdb
except ImportError:                              # system python3 path
    print("WARN: system python3 lacks duckdb -> board.db NOT updated (cache lagging)")
    return 0

Files: ~/board/{append_board_event.py, sync_tasks_jsonl_to_db.py, parity_check.py, board_tasks.jsonl, board.db}.

Evidence & signatures

Verified live in this sandbox (system `python3` duckdb: **MISSING**; board venv duckdb: **1.5.5**):

| Scenario | Result |
|---|---|
| Cold start (seed JSONL → sync → parity) | `PARITY MATCH` rc=0 (jsonl=4, events=4, tasks=3) |
| **Bug**: 2 appends via system python3 with `--set id=T-0004/status=open`, `--set id=T-0002/status=done` | JSONL grows to 6; cache still 4 events, T-0002 still `open`, T-0004 absent; parity **MISMATCH** rc=1 (count + checksum + projection all differ) |
| **Fix**: venv re-sync (rebuild tasks + direct insert) | `inserted=2`, jsonl=6 = events=6 = tasks=4, checksums equal → `PARITY MATCH` rc=0 |
| Idempotency (sync again) | `inserted=0`, still MATCH |
| Venv append (duckdb present) | inline cache update immediate, `seq=11`, MATCH without sync |
| Batch lag (3 system-python appends) | one sync backfills all 3 → MATCH |
| Prefix drift (out-of-band line reorder) | full-rebuild path (`inserted=11`) → MATCH |
| Malformed JSONL line | sync skips with `WARN: skipping malformed jsonl line 12`, no crash; parity reports clean FAIL; after restore → MATCH |
| Deleted `board.db` (cold rebuild) | single sync recreates full cache → MATCH |

Edge cases covered: typed `--set` flags (`count=3`, `flag=true`, `score=0.5`, `tags=["a","b"]` survive into both JSONL and DB), duplicate-ish content via byte-mirror prefix logic, TZ-free `VARCHAR` timestamps (no pytz dependency in the cache), last-event-wins projection semantics, and canonical payload normalization (`sort_keys`, excluding `id/event/ts/raw`) shared across append/sync/parity to guarantee byte-stable checksums.

{"model": "deepseek-v4-flash", "problem_class": "rust-board-jsonl-idle-audit", "result": "passed", "tests": 9}

Answer 2

Key code (the fix's core):

# sync_tasks_jsonl_to_db.py — run with board/.venv/bin/python (has duckdb)
# 1) rebuild derived projection from canonical JSONL
con.execute("DROP TABLE IF EXISTS tasks")
con.execute("CREATE TABLE tasks (id VARCHAR PRIMARY KEY, status VARCHAR, payload JSON, updated_at VARCHAR)")
latest = {}
for r in rows:
    latest[r["ev"].get("id")] = r["ev"]          # last event per task wins
for tid, ev in latest.items():
    con.execute("INSERT INTO tasks (id, status, payload, updated_at) VALUES (?,?,?,?)",
                (tid, ev.get("status"), canonical_payload(ev), ev.get("ts")))

# 2) direct event insert: backfill board_events mirror for the lagging tail
mirror = [r[0] for r in con.execute("SELECT raw FROM board_events ORDER BY seq").fetchall()]
jsonl_raw = [r["raw"] for r in rows]
if jsonl_raw[:len(mirror)] == mirror:            # usual lag case
    for i, r in enumerate(rows[len(mirror):], len(mirror) + 1):
        con.execute("INSERT INTO board_events (seq,id,event,payload,raw) VALUES (?,?,?,?,?)",
                    (i, r["ev"].get("id"), r["ev"].get("event"), canonical_payload(r["ev"]), r["raw"]))
else:                                            # drift/manual edit -> full rebuild
    ...

# append_board_event.py — canonical append ALWAYS wins; cache is best-effort
with JSONL.open("a") as fh:
    fh.write(json.dumps(event, sort_keys=True) + "\n")
try:
    import duckdb
except ImportError:                              # system python3 path
    print("WARN: system python3 lacks duckdb -> board.db NOT updated (cache lagging)")
    return 0

Files: ~/board/{append_board_event.py, sync_tasks_jsonl_to_db.py, parity_check.py, board_tasks.jsonl, board.db}.

Evidence & signatures

Verified live in this sandbox (system `python3` duckdb: **MISSING**; board venv duckdb: **1.5.5**):

| Scenario | Result |
|---|---|
| Cold start (seed JSONL → sync → parity) | `PARITY MATCH` rc=0 (jsonl=4, events=4, tasks=3) |
| **Bug**: 2 appends via system python3 with `--set id=T-0004/status=open`, `--set id=T-0002/status=done` | JSONL grows to 6; cache still 4 events, T-0002 still `open`, T-0004 absent; parity **MISMATCH** rc=1 (count + checksum + projection all differ) |
| **Fix**: venv re-sync (rebuild tasks + direct insert) | `inserted=2`, jsonl=6 = events=6 = tasks=4, checksums equal → `PARITY MATCH` rc=0 |
| Idempotency (sync again) | `inserted=0`, still MATCH |
| Venv append (duckdb present) | inline cache update immediate, `seq=11`, MATCH without sync |
| Batch lag (3 system-python appends) | one sync backfills all 3 → MATCH |
| Prefix drift (out-of-band line reorder) | full-rebuild path (`inserted=11`) → MATCH |
| Malformed JSONL line | sync skips with `WARN: skipping malformed jsonl line 12`, no crash; parity reports clean FAIL; after restore → MATCH |
| Deleted `board.db` (cold rebuild) | single sync recreates full cache → MATCH |

Edge cases covered: typed `--set` flags (`count=3`, `flag=true`, `score=0.5`, `tags=["a","b"]` survive into both JSONL and DB), duplicate-ish content via byte-mirror prefix logic, TZ-free `VARCHAR` timestamps (no pytz dependency in the cache), last-event-wins projection semantics, and canonical payload normalization (`sort_keys`, excluding `id/event/ts/raw`) shared across append/sync/parity to guarantee byte-stable checksums.

{"model": "deepseek-v4-flash", "problem_class": "rust-board-jsonl-idle-audit", "result": "passed", "tests": 9}

Generated from the verified corpus · MIT licensedBack to the catalog