board-migration-duckdb-h3-style

Answer

The BOARD-V2 cutover moved an H3-style matrix board (status markers live inside the Pri column, e.g. ✦ done, ◌ pending, ✗ blocked) into DuckDB-backed Parquet. The fix is a scripted migration with five mandatory moves:

1. Parse, then backfill status+commit_hash from the Completed table (never trust the naive parse). An H3 board has no status column, so every row comes out pending. The completed table is authoritative.

import duckdb, os, re
con = duckdb.connect("board.duckdb")

# Naive parse: marker is a sub-token of the Pri cell; status defaults to 'pending'
rows = re.findall(r"^\|\s*(\w+)\s*\|\s*([^|]+?)\s*\|\s*([^|]+?)\s*\|", board_md, re.M)
for tid, title, pri in rows:
    if not re.match(r"t\d+", tid):            # skip header/separator rows
        continue
    m = re.search(r"[✦◌✗]", pri)
    con.execute("INSERT INTO board (task_id, task_title, marker, status, commit_hash, namespace) VALUES (?,?,?,'pending',NULL,?)",
                [tid, title, m.group(1) if m else "", ns])

# Backfill authoritative status + commit_hash (join key MUST include namespace)
con.execute("""
    UPDATE board b SET status = c.status, commit_hash = c.commit_hash
    FROM completed c
    WHERE c.task_id = b.task_id AND c.namespace = b.namespace
""")

2. Fix the namespace basename bug. The buggy code took the parent-2 directory basename (the home dir, e.g. kara) instead of the board's own directory (h3). Fix: use basename(dirname(path)), not two levels up — and repair already-loaded rows:

# board lives at <root>/h3/tasks.md
correct_ns = os.path.basename(os.path.dirname(os.path.abspath("h3/tasks.md")))  # 'h3'
con.execute("UPDATE board SET namespace = ? WHERE namespace = ?", [correct_ns, buggy_ns])

3. Do not "fix" the cooldown. 900 is enforcer-written policy output for ≥1 pending row, not a reversion. Validate it against the policy function instead of a previous value:

n_pending = con.execute("SELECT count(*) FROM board WHERE status='pending'").fetchone()[0]
assert observed_cooldown == (900 if n_pending >= 1 else 0)   # policy-correct

4. Commit board files with git add -f + git rm tasks.md. Boards/parquet are gitignored, so force-add; remove the old file in the same commit as the new file so rename detection fires:

git add -f h3/board.md
git rm  h3/tasks.md          # same commit as the add -> shows as h3/{tasks.md => board.md}
git commit -m "migrate h3-style matrix board to DuckDB parquet"

5. Two-commit pattern — then re-export. A single commit can't reference its own hash. Commit 1 = structural migration; commit 2 = write commit 1's hash into done rows and re-export the parquet so the artifact embeds it:

MIG_HASH=$(git rev-parse HEAD)
python3 - <<'PY'
import duckdb
con = duckdb.connect("board.duckdb")
con.execute("UPDATE board SET commit_hash = ? WHERE status = 'done'", [MIG_HASH])
con.execute("""COPY (SELECT task_id, task_title, marker, status, commit_hash, namespace
               FROM board ORDER BY namespace, task_id) TO 'h3/board.parquet' (FORMAT PARQUET)""")
PY
git add -f h3/board.parquet
git commit -m "backfill status+commit_hash from Completed and re-export parquet"