go-foreman-board-db-cache-header-lag

Answer

Root cause. board.jsonl (tracked) is the canonical board; board.db (gitignored) is a derived SQLite cache. A late tick-266 append rewrote the cache header with its own stale values (ticks_total 266 / last_commit 1ce30e9c) plus a UTC synced_at, clobbering the correct 267 / 5eeb931e already committed to the canonical file. The audit tick must (1) treat the JSONL header as truth, (2) repair the cache via append_board_event.py --set, (3) never classify a cache/JSONL divergence as a board regression, and (4) verify rows semantically by tick_number, never by sqlite id (rowid), because a rebuild renumbers ids.

The audit decision logic (audit_board.py):

regressions = jsonl_regressions(jheader, jrows)   # canonical JSONL only
if regressions:
    return "REGRESSION", out                      # exit 2 (true board regression)

cache_matches = (dheader.get("ticks_total") == str(jheader["ticks_total"])
                 and dheader.get("last_commit") == jheader["last_commit"])
if not cache_matches:
    # TRUST board.jsonl over the db cache; repair the derived cache.
    subprocess.run([sys.executable, APPEND_SCRIPT, "--set"])
    dheader = cache_header(BOARD_DB)
# ... semantic verification by tick_number:
missing = sorted(set(jmap) - set(dmap))   # keyed on r["tick"], NOT r["id"]

The --set recovery path (append_board_event.py) — rebuilds header + events table from the canonical file, so the cache always converges to JSONL:

def rebuild_cache_from_jsonl(jsonl_path, db_path):
    header, rows = read_jsonl(jsonl_path)      # JSONL header is the source of truth
    conn = connect_db(db_path)
    conn.execute("DELETE FROM events")         # rowids get renumbered here --
    conn.execute("DELETE FROM meta")           # verification is by tick_number
    set_cache_header(conn, header)             # ticks_total/last_commit/synced_at (UTC 'Z')
    for row in rows:
        mirror_event(conn, row)                # keyed on row["tick"] UNIQUE
    conn.commit()

Semantic verification (not by id) — after a rebuild the same tick keeps its semantic identity while id shifts:

def cache_events(path):
    return [{"id": r[0], "tick": r[1], ...} for r in conn.execute(
        "SELECT id, tick, commit_sha, ts, payload FROM events ORDER BY tick")]
# audit matches JSONL rows to cache rows by r["tick"]; id is informational only

Never flag the divergence as regression: exit codes are 0 = OK/CACHE_RESYNCED, 1 = CACHE, 2 = REGRESSION. REGRESSION is reserved exclusively for canonical-file problems (header count ≠ row count, duplicate/non-monotonic ticks). Cache lag → CACHE_RESYNCED, exit 0.