python-sdk-duckdb-board-audit

Answer

Problem. h3-sdk-python's tick #36 idle audit against the DuckDB board (v2.1) had to be codified as a repeatable guard: 0 real pending (all pending rows are fixtures), inventory must come from the real namespace, generation must be idempotent, and the unmanaged scheduler project's 404 must stay a cooldown no-op. The recurring failure mode was reading the wrong namespace (default, which holds only 2 legacy keys) and counting fixture rows as real pending.

Fix. Two deliverables in /workspace/python-sdk-duckdb-board-audit/: - make_board.py — seeds a board reproducing the tick #36 state (fixtures, legacy keys, unmanaged scheduler, Hilo metrics, deps, meta). - board_audit.py — the audit CLI implementing the pattern, exit 0 = PASS.

The three core fixes:

# 1) Query the board ONLY through the board venv interpreter + duckdb.
#    System python has no duckdb; the venv is the single source of truth.
BOARD_PY = "~/.hermes/venvs/board/bin/python3"
BOARD_DB = "~/.hermes/board/board.duckdb"
REAL_NS, LEGACY_NS, LEGACY_EXPECTED = "real", "default", 2

def _board_query(board_py, board_db, sql, write=False):
    runner = (
        "import duckdb, sys, json\n"
        "con = duckdb.connect(sys.argv[1], read_only=%s)\n"
        "cur = con.execute(sys.stdin.read())\n"
        "cols = [d[0] for d in cur.description] if cur.description else []\n"
        "rows = cur.fetchall(); con.close()\n"
        "print(json.dumps([dict(zip(cols, r)) for r in rows], default=str))\n"
    ) % ("False" if write else "True")
    p = subprocess.run([board_py, "-c", runner, board_db], input=sql,
                       capture_output=True, text=True)
    if p.returncode != 0:
        raise RuntimeError(f"board query failed: {p.stderr.strip()}")
    return json.loads(p.stdout.strip() or "[]")

# 2) Pending count EXCLUDES fixtures (the bug this audit fixes).
pending = _board_query(board_py, board_db,
    "SELECT namespace, COUNT(*) AS n FROM tasks "
    "WHERE status = 'pending' AND NOT is_fixture GROUP BY namespace")
real_pending = pending.get(REAL_NS, 0)          # -> 0, fixtures invisible here

# 3) list_keys ALWAYS targets the REAL namespace, never the legacy default.
real_keys = _board_query(board_py, board_db,
    "SELECT key, status FROM tasks WHERE namespace = 'real' ORDER BY key")
assert not any(k.startswith("legacy:") for k in real_keys)  # ns leak guard

Idempotent generation and the scheduler no-op:

# 4) Idempotent generate: deterministic key + INSERT OR IGNORE.
key = f"audit:snapshot:{dt.date.today().isoformat()}"
_board_query(board_py, board_db,
    f"INSERT OR IGNORE INTO tasks (key, namespace, status, is_fixture) "
    f"VALUES ('{key}', 'real', 'skipped', TRUE)", write=True)
# re-running writes the same row; total count never grows

# 5) Unmanaged scheduler project: 404 + active cooldown => no-op, no retry storm.
s = _board_query(board_py, board_db,
    "SELECT managed, last_status, cooldown_until FROM scheduler_projects "
    "WHERE project = 'h3-sdk-python'")[0]
noop = (not s["managed"]) and (s["last_status"] == 404) and \
       (str(s["cooldown_until"]) > _now_iso())

Run: ~/.hermes/venvs/board/bin/python3 board_audit.py (defaults target the board venv); override with --board-python/--board for local testing, --generate for idempotence proof, --no-fixtures for fixture-free boards.