board-git-integrity-commit-hash-backfill

Answer

Diagnosis. "Complete" rows with commit_hash = null split into three recoverable classes plus one fraud class, discriminated by two git primitives — git cat-file -e <sha>^{commit} (does the object exist?) and git merge-base --is-ancestor <sha> main (is it reachable from main?):

Key search insight: dangling-but-recent commits are invisible to git log --all (no ref), but visible to git log --reflog; unmerged chore branches are visible to --all. So search with --all --reflog, and prefer --no-merges so the merge commits we create never shadow the original fix.

The fix (backfill_commit_hashes.py — the core flow; the full script is at /tmp/boardfix/backfill_commit_hashes.py):

def find_candidates(task_id: str) -> list[str]:
    """--all --reflog --grep per task; prefer non-merge fix commits,
    fall back to all matches; oldest first (original fix wins)."""
    seen: dict[str, str] = {}
    for merge_mode in ("--no-merges", None):
        for pat in (task_id, task_id.lower(), task_id.upper()):
            args = ["log", "--all", "--reflog", "--grep=" + pat, "--format=%H"]
            if merge_mode: args.insert(2, merge_mode)
            r = git(*args, check=False)
            for line in r.stdout.splitlines():
                if line.strip(): seen.setdefault(line.strip(), pat)
        if seen: break
    return list(reversed(list(seen.keys())))   # oldest = real fix commit

def verify(hash_: str) -> tuple[bool, bool]:
    """(resolves, is_ancestor_of_main)."""
    resolves = git("cat-file", "-e", f"{hash_}^{{commit}}", check=False).returncode == 0
    ancestor = resolves and git("merge-base", "--is-ancestor", hash_, MAIN,
                                check=False).returncode == 0
    return resolves, ancestor

Per row: candidates = find_candidates(id) → resolved = [(h, *verify(h)) for h in candidates] → pick first (r and a); if none is an ancestor but objects resolve → board-only route; if nothing resolves → tick-theater (no hash written, exit code 1 escalates to a human).

Dual-write backfill — same contract as update_board_task_notes.py --task T1 --commit-hash <sha> (patch tasks.jsonl and board.db in one unit, so the stores never diverge):

def update_board_task_notes(task_id: str, commit_hash: str | None) -> None:
    rows = [json.loads(l) for l in TASKS_JSONL.read_text().splitlines() if l.strip()]
    for row in rows:
        if row["task_id"] == task_id: row["commit_hash"] = commit_hash
    TASKS_JSONL.write_text("\n".join(json.dumps(r, sort_keys=True) for r in rows) + "\n")
    conn = sqlite3.connect(BOARD_DB)
    conn.execute("UPDATE board_tasks SET commit_hash=?, updated_at=? WHERE task_id=?",
                 (commit_hash, now(), task_id))          # (INSERT branch for jsonl-only rows)
    conn.commit(); conn.close()

JSONL-only rows (made by create_board_tasks.py, absent from board.db) are handled by the direct JSONL edit above + sync_tasks_jsonl_to_db.py. The sync must normalize the schema mismatch — complexity is a JSONL string 'low' but an INT8 column; on a STRICT table a naive sync dies with sqlite3.IntegrityError: cannot store TEXT value in INT column. Unmappable values (e.g. 'zero') are skipped with a WARN, not crashed on:

COMPLEXITY_MAP = {"low": 0, "medium": 1, "high": 2}
if isinstance(complexity, str):
    if complexity not in COMPLEXITY_MAP:
        warnings.append(f"{row['task_id']}: unmappable complexity {complexity!r} - needs human")
        continue                                     # don't die; surface for review
    complexity = COMPLEXITY_MAP[complexity]

Board-only commits (protected main — never push to main directly): cut chore-board-<task> off main and git merge --no-ff the original fix SHA in (preserves the hash), then emit push + MR instructions; the hash is backfilled only after the MR merges (idempotent re-run — re-verify requires ancestor, so pre-MR backfill would fail its own check):

branch = f"chore-board-{task_id.lower()}"
git("switch", "-q", "-c", branch, MAIN)
git("merge", "--no-ff", "-m", f"chore(board): land {task_id} fix evidence", sha)
# then:  git push -u origin <branch>
#        glab mr create --source-branch <branch> --target-branch main ...

Re-verify (last pass, every complete row): hash non-null AND verify(h) → (True, True). Exit 0 only when all pass; exit 1 with a named FAIL list otherwise (only legitimate remaining case is tick theater needing a human).