board-storage-parquet-to-jsonl-migration
The migration has two parts: (1) the canonical export via python+duckdb with strict single-line separators and the UTC fold for the zombie timestamp, and (2) the git surgery (untrack parquet, gitignore, force-add the JSONL).
migrate_jsonl.py — the core fix:
from datetime import UTC, datetime
import json, re
import duckdb
LOCAL_TZ = datetime.now().astimezone().tzinfo # host tz (America/Bogota, UTC-05)
def fold_ts(ts: str) -> str:
"""Canonical UTC: '...Z' passes through; naive (LOCAL) timestamps are
interpreted as host-local wall clock and converted to UTC + 'Z' suffix.
This folds the zombie board write (event 45)."""
ts = ts.strip()
if ts.endswith("Z"):
return ts
dt = datetime.fromisoformat(ts) # naive -> host LOCAL wall clock
dt_utc = dt.replace(tzinfo=LOCAL_TZ).astimezone(UTC)
return dt_utc.strftime("%Y-%m-%dT%H:%M:%SZ")
def line(rec: dict) -> str:
"""Strict single-line separator: compact JSON, no embedded newlines."""
return json.dumps(rec, ensure_ascii=False, separators=(",", ":"))
def export_to_jsonl():
con = duckdb.connect()
con.execute(f"ATTACH '{DB}' AS b (TYPE sqlite)")
tasks = con.execute("SELECT id, title, status, created_ts FROM b.tasks ORDER BY id").fetchall()
events = con.execute("SELECT seq, task_id, kind, ts FROM b.events ORDER BY seq").fetchall()
with open(OUT, "w", encoding="utf-8", newline="\n") as f:
for t in tasks:
f.write(line({"type":"task", "id":t[0], "title":t[1], "status":t[2],
"created_ts": fold_ts(t[3])}) + "\n")
for e in events:
f.write(line({"type":"event", "seq":e[0], "task_id":e[1], "kind":e[2],
"ts": fold_ts(e[3])}) + "\n")
Key details: ensure_ascii=False (unicode — ✓ survives), separators=(",",":") (compact), newline="\n", exactly one trailing newline. fold_ts is the "fold the zombie write" — event 45's naive 2025-08-06T21:45:00 (local) becomes canonical 2025-08-07T02:45:00Z.
Parity probe (reads the legacy parquet back through duckdb, canonicalizes with the same fold_ts, and compares record-for-record):
def probe_parity() -> bool:
tasks = con.execute("SELECT id, title, status, created_ts FROM 'data/tasks.parquet' ORDER BY id").fetchall()
events = con.execute("SELECT seq, task_id, kind, ts FROM 'data/events.parquet' ORDER BY seq").fetchall()
expected = [{"type":"task","id":t[0],"title":t[1],"status":t[2],"created_ts":fold_ts(t[3])} for t in tasks] \
+ [{"type":"event","seq":e[0],"task_id":e[1],"kind":e[2],"ts":fold_ts(e[3])} for e in events]
got = [json.loads(r) for r in open(OUT, encoding="utf-8") if r.strip()]
return got == expected # -> "PARITY PROBE: MATCH (55 records)"
Git surgery (parquet untracked, ignore rules, force-add the gitignored dir):
git rm --cached data/tasks.parquet data/events.parquet
printf 'data/\nboard/\nboard.db\n' > .gitignore # parquet dir + generated JSONL dir + source db
git add -f board/board.jsonl # dir is gitignored -> -f required
git commit -m "board: migrate parquet-tracked storage to canonical JSONL
Full export from board.db (9 tasks/46 events) via python+duckdb with
strict single-line separators. Zombie board write folded: event 45 ts
was LOCAL (naive) vs UTC convention; normalized to 2025-08-07T02:45:00Z.
Parity probe: MATCH (55 records).
Co-authored-by: Bane <bane@<project>.local>"
Verified in `~/board-repo` (git history: baseline `6ebed35` → migration `3804fd5` → scripts `a3469f2`):
**Parity probe:** `PARITY PROBE: MATCH (55 records)` — 9 tasks + 46 events, record-for-record equal against the parquet after canonicalization.
**Automated edge checks (9/9 passed):**
```
[ok] 55 records -> 55 non-empty lines
[ok] no embedded newline breaks any record (all lines parse)
[ok] file ends with exactly one trailing newline
[ok] all event ts fields are canonical UTC (…Z)
[ok] zombie event 45 folded LOCAL->UTC (2025-08-07T02:45:00Z)
[ok] 9 task records + 46 event records
[ok] unescaped \n in payload escaped on the wire, intact after parse
[ok] migration is idempotent (byte-identical re-run)
[ok] duckdb round-trip read_json_auto -> 55 rows
```
**Edge cases tested:**
- **Zombie fold**: `{"type":"event","seq":45,...,"ts":"2025-08-07T02:45:00Z"}` — naive local `2025-08-06T21:45:00` (America/Bogota, UTC-05) normalized to UTC. All 55 timestamps end in `Z`.
- **Strict single-line separators**: T-002 title contains an embedded `\n` + unicode (`"Ship parity probe\nv2 — ✓"`); `json.dumps` escapes it on the wire (`\n`), so the raw file stays 55 physical lines and re-parses intact.
- **Negative control (probe teeth)**: with the fold disabled, the canonicality check fails (`FAIL: all event ts fields are canonical UTC`); with a record dropped during export, parity reports `MISMATCH (jsonl=54 expected=55)` and pinpoints the first diff at record #53.
- **Fresh-clone replay**: cloned the repo, regenerated artifacts from seed, re-ran migration → `MATCH (55 records)`, working tree clean, only `board/board.jsonl` + scripts tracked.
- **Git semantics**: `git ls-files` shows parquet gone (`data/*.parquet` → `Bin -> 0 bytes` in the migration commit); `data/`, `board/`, `board.db` all confirmed ignored via `git check-ignore`; `board/board.jsonl` remains tracked (git ignores the pattern for tracked files, so `-f` on first add is the only special step).{"model": "deepseek-v4-flash", "problem_class": "board-storage-parquet-to-jsonl-migration", "result": "passed", "tests": 10}The migration has two parts: (1) the canonical export via python+duckdb with strict single-line separators and the UTC fold for the zombie timestamp, and (2) the git surgery (untrack parquet, gitignore, force-add the JSONL).
migrate_jsonl.py — the core fix:
from datetime import UTC, datetime
import json, re
import duckdb
LOCAL_TZ = datetime.now().astimezone().tzinfo # host tz (America/Bogota, UTC-05)
def fold_ts(ts: str) -> str:
"""Canonical UTC: '...Z' passes through; naive (LOCAL) timestamps are
interpreted as host-local wall clock and converted to UTC + 'Z' suffix.
This folds the zombie board write (event 45)."""
ts = ts.strip()
if ts.endswith("Z"):
return ts
dt = datetime.fromisoformat(ts) # naive -> host LOCAL wall clock
dt_utc = dt.replace(tzinfo=LOCAL_TZ).astimezone(UTC)
return dt_utc.strftime("%Y-%m-%dT%H:%M:%SZ")
def line(rec: dict) -> str:
"""Strict single-line separator: compact JSON, no embedded newlines."""
return json.dumps(rec, ensure_ascii=False, separators=(",", ":"))
def export_to_jsonl():
con = duckdb.connect()
con.execute(f"ATTACH '{DB}' AS b (TYPE sqlite)")
tasks = con.execute("SELECT id, title, status, created_ts FROM b.tasks ORDER BY id").fetchall()
events = con.execute("SELECT seq, task_id, kind, ts FROM b.events ORDER BY seq").fetchall()
with open(OUT, "w", encoding="utf-8", newline="\n") as f:
for t in tasks:
f.write(line({"type":"task", "id":t[0], "title":t[1], "status":t[2],
"created_ts": fold_ts(t[3])}) + "\n")
for e in events:
f.write(line({"type":"event", "seq":e[0], "task_id":e[1], "kind":e[2],
"ts": fold_ts(e[3])}) + "\n")
Key details: ensure_ascii=False (unicode — ✓ survives), separators=(",",":") (compact), newline="\n", exactly one trailing newline. fold_ts is the "fold the zombie write" — event 45's naive 2025-08-06T21:45:00 (local) becomes canonical 2025-08-07T02:45:00Z.
Parity probe (reads the legacy parquet back through duckdb, canonicalizes with the same fold_ts, and compares record-for-record):
def probe_parity() -> bool:
tasks = con.execute("SELECT id, title, status, created_ts FROM 'data/tasks.parquet' ORDER BY id").fetchall()
events = con.execute("SELECT seq, task_id, kind, ts FROM 'data/events.parquet' ORDER BY seq").fetchall()
expected = [{"type":"task","id":t[0],"title":t[1],"status":t[2],"created_ts":fold_ts(t[3])} for t in tasks] \
+ [{"type":"event","seq":e[0],"task_id":e[1],"kind":e[2],"ts":fold_ts(e[3])} for e in events]
got = [json.loads(r) for r in open(OUT, encoding="utf-8") if r.strip()]
return got == expected # -> "PARITY PROBE: MATCH (55 records)"
Git surgery (parquet untracked, ignore rules, force-add the gitignored dir):
git rm --cached data/tasks.parquet data/events.parquet
printf 'data/\nboard/\nboard.db\n' > .gitignore # parquet dir + generated JSONL dir + source db
git add -f board/board.jsonl # dir is gitignored -> -f required
git commit -m "board: migrate parquet-tracked storage to canonical JSONL
Full export from board.db (9 tasks/46 events) via python+duckdb with
strict single-line separators. Zombie board write folded: event 45 ts
was LOCAL (naive) vs UTC convention; normalized to 2025-08-07T02:45:00Z.
Parity probe: MATCH (55 records).
Co-authored-by: Bane <bane@<project>.local>"
Verified in `~/board-repo` (git history: baseline `6ebed35` → migration `3804fd5` → scripts `a3469f2`):
**Parity probe:** `PARITY PROBE: MATCH (55 records)` — 9 tasks + 46 events, record-for-record equal against the parquet after canonicalization.
**Automated edge checks (9/9 passed):**
```
[ok] 55 records -> 55 non-empty lines
[ok] no embedded newline breaks any record (all lines parse)
[ok] file ends with exactly one trailing newline
[ok] all event ts fields are canonical UTC (…Z)
[ok] zombie event 45 folded LOCAL->UTC (2025-08-07T02:45:00Z)
[ok] 9 task records + 46 event records
[ok] unescaped \n in payload escaped on the wire, intact after parse
[ok] migration is idempotent (byte-identical re-run)
[ok] duckdb round-trip read_json_auto -> 55 rows
```
**Edge cases tested:**
- **Zombie fold**: `{"type":"event","seq":45,...,"ts":"2025-08-07T02:45:00Z"}` — naive local `2025-08-06T21:45:00` (America/Bogota, UTC-05) normalized to UTC. All 55 timestamps end in `Z`.
- **Strict single-line separators**: T-002 title contains an embedded `\n` + unicode (`"Ship parity probe\nv2 — ✓"`); `json.dumps` escapes it on the wire (`\n`), so the raw file stays 55 physical lines and re-parses intact.
- **Negative control (probe teeth)**: with the fold disabled, the canonicality check fails (`FAIL: all event ts fields are canonical UTC`); with a record dropped during export, parity reports `MISMATCH (jsonl=54 expected=55)` and pinpoints the first diff at record #53.
- **Fresh-clone replay**: cloned the repo, regenerated artifacts from seed, re-ran migration → `MATCH (55 records)`, working tree clean, only `board/board.jsonl` + scripts tracked.
- **Git semantics**: `git ls-files` shows parquet gone (`data/*.parquet` → `Bin -> 0 bytes` in the migration commit); `data/`, `board/`, `board.db` all confirmed ignored via `git check-ignore`; `board/board.jsonl` remains tracked (git ignores the pattern for tracked files, so `-f` on first add is the only special step).{"model": "deepseek-v4-flash", "problem_class": "board-storage-parquet-to-jsonl-migration", "result": "passed", "tests": 10}