◐ Off-By-One · answer catalog

go-board-migration-dashless-ids

2 answer(s)godockergodocker

OLDTASKIDRE = re.compile(r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$")

📦 Source in repository (JSON)

Answer 1

The root causes were two regex assumptions in migrate-board-to-duckdb.py and one file-format assumption:

1. TASK_ID_RE required a dash and an end-of-cell anchor — [A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$ — so W05 (no dash group) and GITREINS-JUDGE ✅ (emoji violates $) failed re.match and were silently dropped. Fix: split into two alternations (hyphenated IDs, or dashless IDs containing a digit so plain words like READY aren't misread), and strip trailing icon/emoji/whitespace before a fullmatch:

# Old (buggy):  r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$"
OLD_TASK_ID_RE = re.compile(r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$")

# Trailing decoration: emoji blocks, dingbats, misc symbols, variation
# selectors, ©/®/™, and whitespace.
TRAILING_DECOR_RE = re.compile(
    r"[\U0001F000-\U0001FAFF\U00002600-\U000027BF\U0000FE00-\U0000FE0F"
    r"\U00002B00-\U00002BFF\u00A9\u00AE\u2122\s]+$")

# Fixed: hyphenated IDs (GITREINS-JUDGE, TASK-42, PROJ-123-SUB) OR
# dashless IDs containing a digit (W05, W06, W07).
TASK_ID_RE = re.compile(
    r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+"   # one or more dash segments
    r"|[A-Z][A-Z0-9]*[0-9][A-Z0-9]*")  # dashless-with-digit

def extract_task_id(cell):
    if not cell:
        return None
    cleaned = TRAILING_DECOR_RE.sub("", cell.strip())  # drop " ✅", " ✔️", …
    m = TASK_ID_RE.fullmatch(cleaned)                  # whole cell must be one ID
    return m.group(0) if m else None

2. Board file is no longer guaranteed JSONL — after an append_board_event.py merge the file is one pretty-printed JSON object, so line-by-line json.loads throws. Fix: try whole-file json.loads first, fall back to JSONL, then normalize a dict document (look for rows/events/tasks/board/items/data) or a bare list:

def load_board(path):
    text = Path(path).read_text(encoding="utf-8").strip()
    try:
        doc = json.loads(text)                      # single doc (merged shape)
    except json.JSONDecodeError:
        rows = []                                   # JSONL fallback
        for line in text.splitlines():
            if line.strip():
                obj = json.loads(line)
                rows.extend(obj if isinstance(obj, list) else [obj])
        return [r for r in rows if isinstance(r, dict)]
    if isinstance(doc, list):
        return [r for r in doc if isinstance(r, dict)]
    if isinstance(doc, dict):
        for key in ("rows", "events", "tasks", "board", "items", "data"):
            if isinstance(doc.get(key), list):
                return [r for r in doc[key] if isinstance(r, dict)]
        return [doc]
    raise ValueError(...)

3. Cross-check + backfill — after migration, recompute the expected (row_idx, task_id) pairs from the source board and compare against the DuckDB table; anything absent is inserted with ON CONFLICT DO NOTHING (so re-runs are idempotent and pre-existing rows from the old buggy run are kept), and the report surfaces missing/spurious:

before = con.execute("SELECT COUNT(*) FROM board_tasks WHERE source = ?", (src,)).fetchone()[0]
for idx, tid in expected:
    con.execute("INSERT INTO board_tasks (row_idx, task_id, source) VALUES (?,?,?)
                 ON CONFLICT (row_idx, task_id) DO NOTHING", (idx, tid, src))
backfilled = con.execute("SELECT COUNT(*) FROM board_tasks WHERE source = ?", (src,)).fetchone()[0] - before
stored = {(r[0], r[1]) for r in con.execute("SELECT row_idx, task_id FROM board_tasks WHERE source = ?", (src,))}
missing = [(i, t) for (i, t) in expected if (i, t) not in stored]

Run: python3 migrate-board-to-duckdb.py board.jsonl --db board.duckdb (add --check-only to audit without writing). Full file: ~/migrate-board-to-duckdb.py.

Evidence & signatures

Verification harness `~/verify_t71.py` + CLI runs, **26/26 assertions passed, 0 failed** (`python3 verify_t71.py`):

| # | Check | Result |
|---|---|---|
| 1 | Old regex matches only 3 of 11 task-ID cells (reproduces silent skip: `W05/W06/W07`, `GITREINS-JUDGE ✅`, `TASK-42 ✔️` all missed) | PASS |
| 2 | New regex finds all 11; extracts `W05/W06/W07`, `GITREINS-JUDGE`, `TASK-42`, `PROJ-123-SUB` | PASS |
| 3 | Plain words `READY`, `DONE`, `In Progress` are **not** misread as dashless IDs | PASS |
| 4 | Per-row extraction: 9 rows with IDs, 11 total expected, row with `GITREINS-JUDGE ✅` → `GITREINS-JUDGE`, no-ID row → `[]` | PASS |
| 5 | Board loading: JSONL (10 rows), single pretty-printed doc (10 rows), bare pretty-printed list (10 rows); identical per-row IDs across formats | PASS |
| 6 | Fresh migration (both formats): `all_ok`, 11 inserted, 0 missing/spurious, exit 0 | PASS |
| 7 | **Backfill**: DB pre-seeded with only the 3 rows the old regex would have written → rerun cross-checks OK, backfills exactly 8 missing rows, table ends with all 11 pairs, no duplicates | PASS |
| 8 | `--check-only` reports all 11 missing without writing | PASS |

Edge cases covered: dashless-with-digit IDs, multi-segment hyphenated IDs (`PROJ-123-SUB`), emoji ✅, check-mark + variation selector `✔️`, multi-ID comma cell (`W05,W06,W07` → dedupe per row), rows with no task ID, dict-vs-bare-list single-doc shapes, stale partially-migrated DB, idempotent re-runs.
{"model": "deepseek-v4-flash", "problem_class": "go-board-migration-dashless-ids", "result": "passed", "tests": 26}

Answer 2

The root causes were two regex assumptions in migrate-board-to-duckdb.py and one file-format assumption:

1. TASK_ID_RE required a dash and an end-of-cell anchor — [A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$ — so W05 (no dash group) and GITREINS-JUDGE ✅ (emoji violates $) failed re.match and were silently dropped. Fix: split into two alternations (hyphenated IDs, or dashless IDs containing a digit so plain words like READY aren't misread), and strip trailing icon/emoji/whitespace before a fullmatch:

# Old (buggy):  r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$"
OLD_TASK_ID_RE = re.compile(r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+$")

# Trailing decoration: emoji blocks, dingbats, misc symbols, variation
# selectors, ©/®/™, and whitespace.
TRAILING_DECOR_RE = re.compile(
    r"[\U0001F000-\U0001FAFF\U00002600-\U000027BF\U0000FE00-\U0000FE0F"
    r"\U00002B00-\U00002BFF\u00A9\u00AE\u2122\s]+$")

# Fixed: hyphenated IDs (GITREINS-JUDGE, TASK-42, PROJ-123-SUB) OR
# dashless IDs containing a digit (W05, W06, W07).
TASK_ID_RE = re.compile(
    r"[A-Z][A-Z0-9]+(?:-[A-Z0-9]+)+"   # one or more dash segments
    r"|[A-Z][A-Z0-9]*[0-9][A-Z0-9]*")  # dashless-with-digit

def extract_task_id(cell):
    if not cell:
        return None
    cleaned = TRAILING_DECOR_RE.sub("", cell.strip())  # drop " ✅", " ✔️", …
    m = TASK_ID_RE.fullmatch(cleaned)                  # whole cell must be one ID
    return m.group(0) if m else None

2. Board file is no longer guaranteed JSONL — after an append_board_event.py merge the file is one pretty-printed JSON object, so line-by-line json.loads throws. Fix: try whole-file json.loads first, fall back to JSONL, then normalize a dict document (look for rows/events/tasks/board/items/data) or a bare list:

def load_board(path):
    text = Path(path).read_text(encoding="utf-8").strip()
    try:
        doc = json.loads(text)                      # single doc (merged shape)
    except json.JSONDecodeError:
        rows = []                                   # JSONL fallback
        for line in text.splitlines():
            if line.strip():
                obj = json.loads(line)
                rows.extend(obj if isinstance(obj, list) else [obj])
        return [r for r in rows if isinstance(r, dict)]
    if isinstance(doc, list):
        return [r for r in doc if isinstance(r, dict)]
    if isinstance(doc, dict):
        for key in ("rows", "events", "tasks", "board", "items", "data"):
            if isinstance(doc.get(key), list):
                return [r for r in doc[key] if isinstance(r, dict)]
        return [doc]
    raise ValueError(...)

3. Cross-check + backfill — after migration, recompute the expected (row_idx, task_id) pairs from the source board and compare against the DuckDB table; anything absent is inserted with ON CONFLICT DO NOTHING (so re-runs are idempotent and pre-existing rows from the old buggy run are kept), and the report surfaces missing/spurious:

before = con.execute("SELECT COUNT(*) FROM board_tasks WHERE source = ?", (src,)).fetchone()[0]
for idx, tid in expected:
    con.execute("INSERT INTO board_tasks (row_idx, task_id, source) VALUES (?,?,?)
                 ON CONFLICT (row_idx, task_id) DO NOTHING", (idx, tid, src))
backfilled = con.execute("SELECT COUNT(*) FROM board_tasks WHERE source = ?", (src,)).fetchone()[0] - before
stored = {(r[0], r[1]) for r in con.execute("SELECT row_idx, task_id FROM board_tasks WHERE source = ?", (src,))}
missing = [(i, t) for (i, t) in expected if (i, t) not in stored]

Run: python3 migrate-board-to-duckdb.py board.jsonl --db board.duckdb (add --check-only to audit without writing). Full file: ~/migrate-board-to-duckdb.py.

Evidence & signatures

Verification harness `~/verify_t71.py` + CLI runs, **26/26 assertions passed, 0 failed** (`python3 verify_t71.py`):

| # | Check | Result |
|---|---|---|
| 1 | Old regex matches only 3 of 11 task-ID cells (reproduces silent skip: `W05/W06/W07`, `GITREINS-JUDGE ✅`, `TASK-42 ✔️` all missed) | PASS |
| 2 | New regex finds all 11; extracts `W05/W06/W07`, `GITREINS-JUDGE`, `TASK-42`, `PROJ-123-SUB` | PASS |
| 3 | Plain words `READY`, `DONE`, `In Progress` are **not** misread as dashless IDs | PASS |
| 4 | Per-row extraction: 9 rows with IDs, 11 total expected, row with `GITREINS-JUDGE ✅` → `GITREINS-JUDGE`, no-ID row → `[]` | PASS |
| 5 | Board loading: JSONL (10 rows), single pretty-printed doc (10 rows), bare pretty-printed list (10 rows); identical per-row IDs across formats | PASS |
| 6 | Fresh migration (both formats): `all_ok`, 11 inserted, 0 missing/spurious, exit 0 | PASS |
| 7 | **Backfill**: DB pre-seeded with only the 3 rows the old regex would have written → rerun cross-checks OK, backfills exactly 8 missing rows, table ends with all 11 pairs, no duplicates | PASS |
| 8 | `--check-only` reports all 11 missing without writing | PASS |

Edge cases covered: dashless-with-digit IDs, multi-segment hyphenated IDs (`PROJ-123-SUB`), emoji ✅, check-mark + variation selector `✔️`, multi-ID comma cell (`W05,W06,W07` → dedupe per row), rows with no task ID, dict-vs-bare-list single-doc shapes, stale partially-migrated DB, idempotent re-runs.
{"model": "deepseek-v4-flash", "problem_class": "go-board-migration-dashless-ids", "result": "passed", "tests": 26}
Generated from the verified corpus · MIT licensedBack to the catalog