duckdb-board-stale-last-commit

Answer

Root cause. Every tick is supposed to end in one metadata UPDATE board SET last_commit = …. On DuckDB boards, the work tick path derived last_commit from the repo and wrote it — but the idle path returned before the UPDATE, and the audit path ran SET last_commit = last_commit (carry-forward). Both left the stored hash frozen at whatever commit had last been processed, so board.last_commit drifted from repo HEAD (stale d81a070 vs. true HEAD 63f9055 in tick #159).

Fix (part 1). Derive last_commit fresh from git log -1 --format=%H at UPDATE time and hoist the metadata UPDATE to the unconditional tail of every tick. No path can skip it, no path carries forward a possibly-stale value, and the row self-heals on the next tick:

def git_head(repo: Path) -> str | None:
    """Full 40-char hash of HEAD, one line, no decoration."""
    try:
        out = subprocess.run(
            ["git", "-C", str(repo), "log", "-1", "--format=%H"],
            check=True, capture_output=True, text=True,
        ).stdout
    except subprocess.CalledProcessError:
        return None                      # repo has no commits yet
    return out.strip() or None

def tick(con, repo: Path, tick_type: str, now=None, head_provider=None):
    now = now or datetime.now(timezone.utc).replace(tzinfo=None)
    head = (head_provider or git_head)(repo)          # derive ONCE

    if tick_type == "work":
        _process_new_commits(con, head)
    elif tick_type == "audit":
        audit_due_cycle_ticks(con, now)
        _run_due_audits(con, now)
    # idle: nothing — falls through to the UPDATE below, by design

    con.execute(                                       # FIX: unconditional tail
        """
        UPDATE board
           SET last_commit = COALESCE(?, last_commit), -- derived, not carried
               tick_count  = tick_count + 1,
               updated_at  = ?
         WHERE id = ?
        """,
        [head, now, BOARD_ID])
    return head

COALESCE keeps an empty (zero-commit) repo from crashing or clobbering the row.

Fix (part 2). NEVER-DONE audit due-cycle ticks for 14-point sweep / foreman-direct were gated on a non-empty board, so an audit whose fixture lapsed on an empty board never re-ticked. The edge case — board empty ∧ fixture overdue — now ticks the due-cycle counter:

NEVER_DONE_TICK_KINDS = frozenset({"14-point sweep", "foreman-direct"})

def audit_due_cycle_ticks(con, now):
    empty = board_empty(con)
    for audit_id, kind, status, fixture_due, due_cycle in con.execute(
        "SELECT id, kind, status, fixture_due, due_cycle FROM audits"
    ).fetchall():
        fixture_overdue = fixture_due is not None and now > fixture_due
        if (status == "NEVER-DONE"
                and kind in NEVER_DONE_TICK_KINDS
                and empty and fixture_overdue):        # FIXED edge case
            con.execute(
                "UPDATE audits SET due_cycle = due_cycle - 1 WHERE id = ?",
                [audit_id])