board-storage-jsonl-migration

Answer

The fix is a three-part migration: an exporter that turns the duckdb-readable board into strict single-line JSONL, git hygiene that drops the parquet/db mirrors, and two JSONL-native mutation scripts. Norm id: JSONL-NORM-001 (muster tick 112, commit 8d80345).

1) migrate_board_to_jsonl.py — export via duckdb fetchall, sanitize, write strict lines:

import json, math, duckdb
from datetime import date, datetime

def sanitize(v):
    if v is None: return None
    if isinstance(v, float) and (math.isnan(v) or math.isinf(v)):
        return None                      # NaN/Inf -> null (strict JSON)
    if isinstance(v, (datetime, date)):
        return v.isoformat()             # datetime -> str
    if isinstance(v, dict):  return {k: sanitize(x) for k, x in v.items()}
    if isinstance(v, (list, tuple)): return [sanitize(x) for x in v]
    return v

def dump_line(obj):
    # strict single-line JSONL: compact separators, unicode kept
    return json.dumps(obj, separators=(",", ":"), ensure_ascii=False) + "\n"

con = duckdb.connect()
lines = [dump_line({"_board_header": dict(con.execute(
    "SELECT key, value FROM sqlite_scan('board.db','board_meta')").fetchall())})]
for table in ("tasks", "events", "fixtures"):
    rows = con.execute(f"SELECT * FROM sqlite_scan('board.db','{table}')").fetchall()
    cols = [d[0] for d in con.description]
    for row in rows:
        rec = {"_table": table}
        rec.update({c: sanitize(v) for c, v in zip(cols, row)})
        lines.append(dump_line(rec))
open("board.jsonl", "w", encoding="utf-8").writelines(lines)

Layout: line 1 = {"_board_header":{...}}; every data line carries "_table":"tasks|events|fixtures". Output shape:

{"_board_header":{"board":"foreman","schema":"jsonl-norm-001","muster_tick":"112"}}
{"_table":"tasks","id":2,"title":"NaN task","progress":null,"updated_at":"2026-08-07T08:00:00Z",...}

2) Verification + git hygiene (round-trip via read_json_auto, then untrack the dead stores):

python migrate_board_to_jsonl.py          # export + read_json_auto round-trip + parity probe
git rm --cached board.db '*.parquet'
printf '\n# JSONL-NORM-001: pure-JSONL storage — parquet mirrors are dead\n*.parquet\nboard.db\n' >> .gitignore

Round-trip: SELECT count(*) FROM read_json_auto('board.jsonl') must equal source record count. Parity probe: rebuild each table from its parquet mirror through the same sanitize+dump_line path and compare exact lines to board.jsonl → MATCH or exit 1.

3) Mutation scripts — same strict writer, append-only / line-rewrite semantics:

# append_board_task_completed.py — O(1) append, never touches existing lines
rec = {"_table":"events","kind":"task.completed","task_id":3,
       "at": datetime.now(timezone.utc).isoformat(), "note": note}
open("board.jsonl","a",encoding="utf-8").write(dump_line(rec))

# update_board_task_notes.py — rewrite only the {"_table":"tasks","id":N} line,
# same separators, atomic os.replace, exit 1 if task id missing