go-board-jsonl-export-format

Answer

The root cause: COPY ... TO 'x.jsonl' (FORMAT JSON) was being used to (re)generate the board mirror, so every line got re-serialized by DuckDB — collapsed to compact JSON, raw UTF-8 instead of \uXXXX, and timestamps normalized (2024-01-01T12:34:56.789Z → 2024-01-01 12:34:56.789). The fix: the JSONL mirror is now an append-only commit log written exclusively by stdlib json.dumps(..., ensure_ascii=True); DuckDB is only the live query store; and tick events are reconciled by an idempotent backfill keyed on event_id.

1. One canonical serializer defines the committed format (never touched by DuckDB):

def serialize_event(event: dict) -> str:
    """The ONLY serializer for the mirror: stdlib json, ensure_ascii=True,
    committed key order preserved -> byte-stable, diff-friendly lines."""
    return json.dumps(event, ensure_ascii=True)

2. Append-only mirror I/O (flock + fsync, old lines are never regenerated, so a new event is a one-line diff):

def append_mirror(mirror_path, event_id, line, event_id_key="event_id") -> bool:
    with open(mirror_path, "a", encoding="utf-8", newline="\n") as fh:
        fcntl.flock(fh, fcntl.LOCK_EX)
        try:
            if event_id in mirror_event_ids(mirror_path, event_id_key):
                return False                 # sibling already wrote it
            fh.write(line + "\n"); fh.flush(); os.fsync(fh.fileno())
            return True
        finally:
            fcntl.flock(fh, fcntl.LOCK_UN)

3. Store API — commit (mirror + live store) and tick (mirror only, then backfill, never duplicate):

class BoardStore:
    def commit(self, event):                       # normal path
        eid = event[self.event_id_key]
        line = serialize_event(event)
        append_mirror(self.mirror_path, eid, line)
        insert_event(self.con, eid, event, line)   # ON CONFLICT DO NOTHING

    def tick(self, event):                         # tick path
        eid = event[self.event_id_key]
        if eid not in mirror_event_ids(self.mirror_path):   # sibling may have written it
            append_mirror(self.mirror_path, eid, serialize_event(event))
        return self.sync_from_mirror()             # DuckDB filled from mirror only

    def sync_from_mirror(self) -> int:             # idempotent backfill
        existing = self.event_ids()
        n = 0
        for ev in read_mirror_events(self.mirror_path):   # skips torn/blank lines
            eid = ev.get(self.event_id_key)
            if eid is None or eid in existing:
                continue
            insert_event(self.con, eid, ev, serialize_event(ev))
            existing.add(eid); n += 1
        return n

insert_event stores the exact committed line bytes in the payload JSON column, so mirror and live store can never drift. The old COPY TO (FORMAT JSON) path remains only as demo_legacy_duckdb_copy for regression demonstration.