duckdb-board-header-drift-post-migration

Answer

Root cause. Migration initialized the header (board.ticks_total = 1) instead of reading it from the data, so the header drifted from events.parquet (real max tick 78). The fix: make events the single source of truth and make the header write recomputed from events, never carried over. One script (board_tick.py, run with the board-venv interpreter) does all four steps in one pass:

1. INSERT the next event row with id = COALESCE(MAX(id), 0) + 1 (→ tick #79 for h3-sdk-go). id and tick are monotonic for tick events; idle events attach to the current tick (they don't advance it) so ticks_total and ticks_idle stay orthogonal.
2. UPDATE the header from events — self-healing regardless of how stale the header is: ticks_total = MAX(tick), ticks_idle = COUNT(*) FILTER (type='idle'), last_commit = newest event's commit.
3. COPY tasks/events → tasks.parquet/events.parquet (the tracked set) with OVERWRITE_OR_IGNORE.
4. Refresh gitignored mirrors tasks.jsonl/events.jsonl (FORMAT JSON, ARRAY false = NDJSON), then git add only the parquet.

#!/usr/bin/env python3
# board_tick.py — drift fix + tick pipeline (run: board-venv/bin/python board_tick.py ...)
import argparse, datetime as dt, json, subprocess, sys
from pathlib import Path
import duckdb

def git_sha():
    try: return subprocess.check_output(["git","rev-parse","HEAD"], text=True,
                                        stderr=subprocess.DEVNULL).strip()
    except Exception: return "unknown"

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--db", default="board.db")
    ap.add_argument("--task", required=True)
    ap.add_argument("--type", default="tick", choices=["tick","idle"])
    ap.add_argument("--commit", default=None)      # default: git HEAD
    ap.add_argument("--ts", default=None)          # default: now UTC
    ap.add_argument("--out", default=".")
    ap.add_argument("--no-git-add", action="store_true")
    a = ap.parse_args()
    out = Path(a.out); out.mkdir(parents=True, exist_ok=True)
    ts = a.ts or dt.datetime.now(dt.timezone.utc).isoformat()
    commit = a.commit or git_sha()
    con = duckdb.connect(str(Path(a.db)))

    # 1) INSERT next event row: id = COALESCE(MAX(id),0)+1
    con.execute("BEGIN TRANSACTION")
    try:
        next_id = con.execute("SELECT COALESCE(MAX(id),0)+1 FROM events").fetchone()[0]
        cur_tick = con.execute("SELECT COALESCE(MAX(tick),0) FROM events").fetchone()[0]
        tick = next_id if a.type == "tick" else cur_tick   # idle attaches, no advance
        con.execute("INSERT INTO events (id,tick,type,task,commit,ts) VALUES (?,?,?,?,?,?)",
                    [next_id, tick, a.type, a.task, commit, ts])

        # 2) UPDATE board header, recomputed from events (heals drift: 1 -> real max tick)
        ticks_total, ticks_idle, _ = con.execute(
            "SELECT MAX(tick), COUNT(*) FILTER (WHERE type='idle'), MAX(ts) FROM events").fetchone()
        last_commit = con.execute("SELECT commit FROM events ORDER BY id DESC LIMIT 1").fetchone()[0]
        if con.execute("SELECT COUNT(*) FROM board").fetchone()[0] == 0:
            con.execute("INSERT INTO board (id,name,ticks_total,ticks_idle,last_commit,updated_at) "
                        "VALUES (1,'board',?,?,?,?)", [ticks_total, ticks_idle, last_commit, ts])
        else:
            con.execute("UPDATE board SET ticks_total=?, ticks_idle=?, last_commit=?, updated_at=?",
                        [ticks_total, ticks_idle, last_commit, ts])
        con.execute("COMMIT")
    except Exception:
        con.execute("ROLLBACK"); raise

    # 3) COPY tasks/events TO parquet (tracked set)
    con.execute(f"COPY tasks TO '{out/'tasks.parquet'}' (FORMAT PARQUET, OVERWRITE_OR_IGNORE)")
    con.execute(f"COPY events TO '{out/'events.parquet'}' (FORMAT PARQUET, OVERWRITE_OR_IGNORE)")
    # 4) refresh gitignored JSONL mirrors
    con.execute(f"COPY tasks TO '{out/'tasks.jsonl'}' (FORMAT JSON, ARRAY false)")
    con.execute(f"COPY events TO '{out/'events.jsonl'}' (FORMAT JSON, ARRAY false)")
    con.close()
    if not a.no_git_add:  # tracked set stays parquet-only
        subprocess.run(["git","add",str(out/"tasks.parquet"),str(out/"events.parquet")],
                       check=False, capture_output=True)
    print(json.dumps({"event_id": next_id, "tick": tick, "task": a.task, "type": a.type,
                      "header": {"ticks_total": ticks_total, "ticks_idle": ticks_idle,
                                 "last_commit": last_commit}}, indent=2))
    return 0

if __name__ == "__main__":
    sys.exit(main())

.gitignore (untracked sidecar, already assumed present): board.db and *.jsonl; the tracked set is tasks.parquet + events.parquet only.