fleet-idle-audit-duckdb-board

Answer

Root cause (schema drift, not config, not event plumbing): the 35-idle-streak audit was never migrated to the BOARD-V2 board layout. BOARD-V2 changed the parquet schema three ways:

The audit still ran V1 SQL. epoch(now()) - ts on a TIMESTAMP is a bind-time type error, and the harness's try/except swallowed it as "nothing pending." Result: 0 pending rows → no PUT, no reversion, for 2 ticks, while fx-never-done sat idle ~253,500 s ≫ the 43,200 s cooldown. The cooldown pinned in both fleet.toml files since tick #63 and the git add -f events.parquet insert path were both fine — untouched.

The fix — schema-aware audit that (1) introspects the parquet, (2) maps V1 legacy columns to V2 semantics, (3) fails loudly on drift instead of swallowing, (4) compares idle time in the TIMESTAMP domain against the cooldown read from fleet.toml, and (5) emits PUT + reversion back to the board:

# audit_idle.py (post-fix)
import duckdb
from tomllib import load

COOLDOWN = load(open("fleet.toml","rb"))["fleet"]["cooldown_seconds"]  # 43200

STATUS_EXPRS = {"status": "lower(status)",  # V2
                "state":  "lower(state)"}   # V1 legacy (uppercase enum)
TS_EXPRS = {"ts":       "ts",                                                       # V2: TIMESTAMP
            "ts_epoch": "(TIMESTAMP '1970-01-01 00:00:00' + to_seconds(ts_epoch))"} # V1: epoch secs

def schema_probe(board="events.parquet"):
    con = duckdb.connect()
    cols = {r[0] for r in con.execute(
        f"DESCRIBE SELECT * FROM read_parquet('{board}')").fetchall()}
    status_expr = next((e for c, e in STATUS_EXPRS.items() if c in cols), None)
    ts_expr     = next((e for c, e in TS_EXPRS.items()     if c in cols), None)
    if not status_expr or not ts_expr:
        raise RuntimeError(                    # LOUD: no silent no-op on drift
            f"AUDIT-ABORT: schema drift on {board} — found {sorted(cols)}")
    return con, status_expr, ts_expr

def run_audit(board="events.parquet"):
    con, status_expr, ts_expr = schema_probe(board)
    return con.execute(f"""
        SELECT fixture_id, {status_expr}, {ts_expr}::TIMESTAMP AS last_ts,
               date_diff('second', {ts_expr}::TIMESTAMP, current_timestamp) AS idle_seconds
        FROM read_parquet('{board}')
        WHERE {status_expr} = 'never_done'
          AND {ts_expr} IS NOT NULL
          AND date_diff('second', {ts_expr}::TIMESTAMP, current_timestamp) > {COOLDOWN}
        ORDER BY idle_seconds DESC""").fetchall()

pending = run_audit()
for fid, status, last_ts, idle in pending:      # requeue path — unchanged ops
    con = duckdb.connect()
    con.execute("INSERT INTO board VALUES (?, 'PUT', current_timestamp)", [fid])       # PUT
    con.execute("INSERT INTO board VALUES (?, 'reversion', current_timestamp)", [fid]) # reversion
    con.execute("COPY board TO 'events.parquet' (FORMAT PARQUET)")                     # export
# shell: git add -f events.parquet   (unchanged — parquet is the only tracked artifact)

Operational companion fix: run the event insert/export/git add -f events.parquet before the audit in the tick pipeline (or have the audit read the live DuckDB file, not the committed parquet) so the audit never judges a stale snapshot.