Changelog

Consolidated history of BreedingInfill, newest first. Entries dated 23–25 August 2026 come from the retired RQDG/BreedingInFill2 repository and are folded in here so the record is continuous.

---

2026-09-06 — Repository consolidated (project 0.4.0, commit b299932)

• BreedinginFill2 retired. Work had been split across two repositories since 23 August. RQDG/BreedingInFill is now the single source of truth. Everything the second repository contained is preserved under archive/breedinginfill2/ with a README mapping each file to what replaced it.
• Six files had never been committed anywhere — including src/breeding_load.py (the 724-line two-pass loader) and both pilot reports. They were committed to BreedinginFill2 first (d4daaf4) so that repository's history is complete, then copied across. Nothing was lost.
• .gitignore was excluding deliverables. reports/ was excluded as a directory, which makes any negation inside it impossible — so the August validation reports could never be tracked and had been sitting untracked on disk. Changed to reports/* plus !reports/*.docx. Added !archive/**/*.sql so archived schema and SQL keep their provenance.
• Decided against database triggers for the lock. add_lock_triggers.sql was one way to enforce is_locked on rs.breeding. The decision was to enforce it in a single write gateway instead — one routine with all the checks, through which every update, insert and delete passes. The file is kept unrunnable and clearly headed at archive/add_lock_triggers_NOT_ADOPTED.sql. It was never applied to either server.

2026-09-05/06 — The rebuild (commits 3584aab, d3d1776)

Replaced the eight-phase staging pipeline (226 GB of intermediate tables) with load-then-link. breeding now holds 3,106,726 rows, one per identity on (publish_name, country_id, year_of_foal), with 2,895,470 horses carrying a complete five-generation pedigree.

New pipeline — rebuild/

File	Role
common.py, linking.py	Shared helpers and the rule B statements
preflight_rebuild.py	Environment, schema, source-data and program checks; exits 2 on blockers
load_pq.py	parsed_horses → breeding (step 1)
load_studbook.py	newAUData → breeding, linking as it loads (step 2)
link_parents.py	Rule B over the whole table (step 3)
reports.py	Identity conflicts, unlinked parents, anomalous links (step 4)
dedup_adjacent_years.sql	Duplicate identity resolution
reconcile_sources.py	Two-source agreement, check and lock
ship_to_qdb.py	Id-for-id copy to QDB, resumable and verifiable

Driven by run_breeding_rebuild.bat, dry run unless given apply. Full build timing on 5 September: PQ load 1.6 min, studbook load 28.9 min, link 0.3 min.

Decisions taken

• sex is derived from is_male; the source vocabulary is not stored, because the studbook cannot tell a stallion from a gelding. Measured on 66 matched AU/NZ males, 33 said stallion where the run records said gelding. Retired the tblhorse gelding lookup and the gelded_from_tblhorse / male_generic counters. (Robert, 5 September.)
• A row with progeny is never deleted automatically. Duplicate pairs whose loser has children are marked for review and both rows left untouched. (Robert, 5 September.)
• Linking within a source may ignore the year; matching across sources requires it, allowing +1 for northern-hemisphere parents only.

Duplicate resolution

5,052 adjacent-year pairs found; 2,301 also shared both parent names. Verdicts: 1,233 MERGE, 537 held back because the duplicate has progeny (1,888 children involved), 474 with no foal date to arbitrate, 28 where the date contradicts its own row, 18 chains of three or more consecutive years, 9 where both rows are dated, 2 descriptor names (WILDAIR MARE and similar, a known false-positive class).

Validation

Tested against race-day runners for 1, 2, 8 and 9 August 2026: 2,963 runners, 5,926 stated parents, no error identified in breeding. Reports committed as reports/Breeding_validation_August_2026.docx.

Fixed

• .gitignore blanket *.sql had been excluding the schema definitions and the pipeline SQL along with data dumps — so the schema for the table this project builds was never under version control. Negations added.
• run_breeding_rebuild.bat killed by its own log output. A log line containing ->, a pipe and parentheses reached cmd's parser. Tail output now goes through PowerShell, which writes to the console and appends to the summary itself; cmd never sees the text. Banner assignments quoted so -> is not read as a redirect.

2026-08-27 — Schema v4: key_name dropped

key_name, sire_key_name and dam_key_name removed from breeding. Identity became the triple (publish_name, country_id, year_of_foal), enforced by uq_breeding_identity. mention_count and updated_at retained. The loaders refuse to run against a schema that still carries the dropped columns.

2026-08-25 — Studbook cross-check (BreedinginFill2 v0.6.0)

• Cross-checked a 50-horse random sample (foaled 2000+) from studbook.org.au against the build, read-only. Most "not found" horses simply had not been loaded yet or needed punctuation-insensitive matching.
• studbook_profiles.dam_country has systemic corruption — roughly 17,750 of 235,384 rows hold date strings instead of country codes.
• Isolated the northern-hemisphere year offset to the foal page, not the parent's record. A consistent one-year offset appears in the Studbook foal-page sire_year/dam_year fields for GB/USA/IRE parents. Robert asked whether the parent's own profile shows the same shift — it does not. Worked example on CHOPPA's page: dam BOSSINOVA (AUS) agrees everywhere; sire AMADEUS WOLF (GB) shows 2002 on the foal page but 2003 on his own profile and in breeding. Same pattern on FINETTI's page for SHOP AROUND (GB). Zero build impact, and the origin of the season-year rule.
• Found a bug that existed only in the saved script. LOAD_RULES filtered on the raw pre-sanitize publish_name for both R4 (length) and R5 (ONMOUSE), in the WHERE clause ahead of the sanitizer in the SELECT. For a horse with only one corrupted mention this would silently drop the identity. The ad-hoc SQL actually run throughout the build did not have it. Fixed by moving the length check to HAVING against the sanitized name.
• Build advanced to 1,560,531 rows, cursor NESTOR VIVE, ~52 % of target. Re-ran LINK: +256,355 sire / +276,877 dam links, zero R14 refusals.

2026-08-24 — Pilot hardening (BreedinginFill2 v0.3.0–v0.5.0)

• Schema v4 applied on both rs.breeding and qdb.breeding: added sire_key_name, dam_key_name, mention_count, updated_at. 26 columns, identical ordinal positions, no drift between servers.
• Chunking bug fixed. Chunking by id range splits one horse's mentions across chunks, making COUNT(*) a partial count and breaking mention_count; LIMIT-then-recover-cursor-via-MAX(key_name) silently skips data once breeding holds a row sorting past the chunk. Fixed by chunking on key_name range with the boundary computed first by an index-only query, then loading the closed range with no LIMIT. Also faster: 450 identities/sec against 250.
• Scraper artefact in publish_name. 6,378 of 71.8 M parsed_horses rows carry the name duplicated plus a stray HTML attribute fragment. One instance at 59 characters hard-failed a LOAD chunk. Handled by detecting the ONMOUSEOUT marker, stripping it and recovering the real name from the NAME NAME duplication.
• payload_json returns the literal string "null", not SQL NULL, when a horse has no named sire — CAST(... AS UNSIGNED) then errors. Fixed with NULLIF(...,'null'). Recorded as fixed in v0.4.0 but not actually saved to the file; found again the next day. Lesson recorded at the time: verify the live file, do not trust the changelog's description of a past fix.
• Rule R14 added (since superseded): LINK required the matched parent row's own year and country to agree with what the child's page stated, refusing the link rather than the row. Zero false refusals on a 43,784-identity chunk.
• Rule R13 added: an identity collision is a duplicate candidate only when both parent keys also match. Cut the review queue from 60 groups to 14 on a 60,000-row sample.
• Manually walked a real 10-horse pedigree one insert at a time (PINK TERRACE → ALL PINK2 → ALL RED → STEPNIAK → NORDENFELDT → MUSKET/ONYX2), confirming links resolve as each generation loads and stay correctly NULL for ancestors not yet present.
• Cross-checking a PedigreeQuery family chart found 5 names (ATALANTA, CLEMENTINA, GYMKHANA, PETAL, WAR DANCE) whose plain key_name resolves to a completely different horse — the correct identity needed its PQ suffix. This is the evidence that key_name text alone is not reliably matchable to a real-world identity, and it fed directly into the 27 August decision to drop it.
• Chunk size dropped 50,000 → 10,000: a 50,000-identity LOAD reported success and inserted zero rows, silently cancelled by the MCP bridge's 60-second client timeout. Also observed the timeout is not consistent — of three consecutive timeouts, two had committed server-side and one had not. Always verify count/cursor after a timeout.

2026-08-23 — The two-pass design (BreedinginFill2 v0.1.0–v0.2.0)

• ANALYSIS_current_pipeline.md — measured review of the eight-phase pipeline. Key findings: 226 GB of staging produces a 0.4 GB table; parsed_horses is 96 % repeated mentions (23.6 per horse); the parent's own row agrees with the child's payload_json on 989,090 pairs to 99.997 %; one named parent in 24,524 has no row of its own.
• DESIGN_breeding_load.md — the two-pass design: LOAD then LINK, twelve rules down from twenty-four, no staging tables, no tiers, no confidence score. Still the best statement of why the pipeline has the shape it does.
• Pilot run: 1,408 identities loaded across 9 cycles, 607/607 sires and 472/472 dams linked, recursion verified to 9 generations, 188 pre-existing rows provably untouched.
• PETITIONER cross-check — 201,854 source pages give her foaling year as 1952 and 49 give it as 0. The load wrote 1952, taken from her own row: the design's central claim demonstrated on live data.
• Decided: foal_date stays and NULL is an accepted permanent state, not a gap to report against. (Robert, 23 August.)
• Decided: reuse rs.parsed_horses; do not re-scrape or re-parse.
• Decided: horses with no country of origin remain excluded (rule R1), carrying forward the 16 August decision. Costs roughly 12 % of identities.

2026-08-20/21 — Schema v3 and tooling consolidation (project 0.3.0–0.3.2, commit e3ef37d)

breeding schema v3 (breaking)

• Redefined breeding from a single canonical CREATE TABLE applied identically to both servers, rather than patching a table that had accumulated history.
• Added sire_year, sire_country_id, dam_year, dam_country_id. Storing only sire_name left a later re-link nothing but a name string, and names repeat: GOLD DIGGER (ARG 1933) is not GOLD DIGGER2 (USA 1962); HOIST THE FLAG is 1968 USA (97,872 mentions) and 1970 NZ (1). Measured across 400,000 rows the parent's year and country are present in payload_json for 99.11 % of sires and 99.63 % of dams — and were being discarded at load.
• Dropped new_id and guid_id. The importer wrote 0 to every new_id and never populated guid_id; each carried an index over a single constant.
• Column order made identical on both servers for the first time (publish_name previously sat at position 19 locally and 3 on ODIN). Verified by signature 13d52f55bd0fbb14e98e0912125c0f81.
• 19 index parts reduced to 10 real indexes; sire_name/dam_name narrowed 60 → 45 to match publish_name (longest measured real value: 41).
• Every column commented from QDB_V8_1_Field_Map.md.

Tooling

• check_breeding_candidates.py, load_breeding_candidates.py and ship_breeding_to_qdb.py merged into breeding_build.py — four commands (prepare, load, lock, ship) replacing 13 stages across three scripts.
• Added breeding_bundle.py, the incremental path: one horse plus two generations validated against what breeding already holds, returning true/false/unknown per fact and refusing to write if any check failed.
• prepare takes horses by name — "Brutal (NZ) 2015" — resolving publish_name + country + year to a key_name. Ambiguous specs stop the run. Extended to breeding_bundle.py --horse on 20 August, with an identical regex and country match so a horse name means the same thing whichever tool runs it.
• Archived three scripts referencing the dropped new_id.

Validation — 24 rules, 20 blocking

Age thresholds set by measurement over 400,000 rows: dam gap >20 warns / >30 blocks; sire gap >25 warns / >40 blocks. The >30 dam band is 321 rows of which 317 exceed 40 — the ~YYY- truncation artefact, max gap 2,005 years. Setting the dam error at 20 would have blocked 5,011 real broodmare rows per 400,000. Sire errors: 3,134 rows from only 508 distinct sires — a few hundred corrupt years blocking thousands of horses each.

Fixed

• Unsigned underflow (crash). year_of_foal is smallint unsigned, so s.year_of_foal - d.year_of_foal raised "BIGINT UNSIGNED value is out of range" whenever the dam was older than the sire — ordinary data. ABS() does not help. 10 subtractions across 8 rules now CAST(... AS SIGNED).
• parent_cycle_3 never finished on a large table. The rule joined ON c.key_name IN (b.sire_key_name, b.dam_key_name). An IN list holding column expressions cannot resolve to an index lookup, so MySQL scanned the whole table per driving row — about 2.7 × 10¹¹ row reads. Rewritten as two UNIONed equality joins: index scan → eq_ref → eq_ref, linear. Invisible at the 88- and 3,722-row scales it was written against; found when a 503,100-row run sat on it for over half an hour with no output. The other 23 rules were EXPLAIN-checked at the same scale and are all linear.
• All-runs scan. Staging a named page walked all 8 import runs (~15.2 M index entries each, ~122 M total) to find a page whose run was already known.
• evidence_json was silently dropped on ship. It was absent from SHIP_COLUMNS on the assumption that a breeding_evidence table existed; it never did. --stage check passed because it only tested that the column list was a subset of the source. The check now fails if any source column is not shipped.
• relink matched on name alone, defeating the point of the new columns. Now requires name + year + country + sex in two passes, and reports matches rejected on mismatch rather than skipping them silently.
• insert became a single set-based INSERT ... SELECT with JSON_OBJECT() built server-side.

Added — progress reporting

A silent console is indistinguishable from a hung one, which is exactly how the parent_cycle_3 bug went unnoticed for half an hour. A progress() context manager prints an elapsed-time heartbeat every 10 s from a daemon thread while a statement is in flight; faster steps stay quiet. Applied to every tier step, all 24 validation rules, the insert, both relink passes, every verify check and each build chunk.

Changed

• lock became state-based, not tier-based: a row locks when it passed validation and both parents are named and resolved, regardless of tier. Tier records what was known at staging and never changes; completeness changes as data arrives. HALO was staged tier 2 with both parents NULL, and later loads brought HAIL TO REASON in — under a tier-1 rule HALO could become fully linked and still never lock.
• lock refuses to run ahead of lock_breeding_from_studbook_profiles.py, which sets is_locked and is_checked and only considers unlocked rows. On the proving build, 53 of 73 unlocked rows would otherwise have been denied is_checked permanently.

Caught before it shipped

Work on breeding_bundle.py --horse started against a stale cached copy of the file (pre-schema-v3: still had new_id/guid_id). Diffing the patch against the actual file on disk before writing anything caught it. Always re-stage and diff before patching a file that may have moved on.

Proving runs

Built WINX, BRUTAL (NZ 2015) and KISS MOON (USA 2011) end to end: 88 identities, 45 sire links, 44 dam links, verify passing. Shipped an earlier 31-row version to ODIN and verified an exact id-for-id copy (CRC32 match on both sides). Demonstrated cross-load linking: HALO staged tier 2 with both parents NULL, then connected to HAIL TO REASON by a later relink.

2026-08-15 — Investigation and repo housekeeping (project 0.2.1, commits f8be938, 5479bff)

No importer, parser or dashboard code shipped. Two identified fixes were designed and deliberately not applied pending Robert's go-ahead.

• Discovered the project had moved from C:\Users\Robert\Codex\Projects\BreedingInfill to C:\Users\Robert\Projects\BreedingInfill. The .git folder was empty and uninitialised; git init run, remote added, .gitignore written, initial commit made (73 files).
• Full live-state audit: 2,969,350 source_files; 88,092,047 parsed_horses; 87,327,841 auto_write / 758,170 review / 6,036 blocked decisions; 1,014,732 studbook_profiles; 8 completed import runs with contiguous coverage. rs.breeding and qdb.breeding both confirmed at 0 rows.
• Review-queue root cause. The dominant driver of 758,170 review rows was unresolved_country_id, present on ~89 % of a 100k sample. Isolated to 8 country codes (DR, TRI, ROM, BUL, STV, STK, KSA, LEB) whose racing_code in rs.country duplicated iso3 instead of the real racing abbreviation.
• Blocked-row triage, all 6,036 rows. A random sample of 400 of the 47,445 parse_error files, reopened against the source site: 399 were PedigreeQuery's expected "missing horse" page, not parser failures.
• Duplicate-identity shadow-blocking confirmed. A low-reference near-duplicate identity (same name/country/sex, year off by ~1) can block real descendant data attached to a heavily-referenced identity of the same name. The BLACKLOCK MARE cluster is the single largest true blocked_lineage_branch cascade in the dataset (7 descendants blocked).
• Found the ~YYY- truncation bug at precision. BLACKLOCK MARE2's year_of_foal is literally 182. Run 1 alone has 17 rows with year_of_foal < 1000, all consistent with an approximate-year-marker regex over-trimming by one character. A second instance (SIR WATKIN, ~184- → 184) confirmed it recurs.
• Run 8 deep dive — 706 blocked rows, 637 age-related. Candidate matching produced 45 high-confidence, 91 medium, 117 ambiguous and 384 no-candidate matches. All 375 independently-verifiable candidates' own pages were checked against the source HTML. In every case the flagged "bad" year is exactly what PedigreeQuery's own page displays — a source-data problem, not an import or linking bug. No defensible auto-fix exists; the recommendation was to leave them blocked.

2026-07-14 — MySQL staging replaces SQLite (project 0.2.0)

Architecture

• Replaced SQLite as the active staging backend with local MySQL database rs. SQLite scripts kept for historical reference only.
• Added MySQL staging tables for runs, source files, parsed horses, decisions and existing-breeding fixtures, plus a local rs.breeding copy, parent-link cleanup and Studbook locking stages.
• Added branch-level blocking: descendants of an invalid sire/dam branch cannot auto-write.
• Continued ignoring generation 5, whose parent links are not reliably represented by the source page.

Studbook self-healing

• Indexed lookup against rs.studbook_profiles. An exact unique horse name/year plus exact available parent names can fill missing horse country/year, extended to missing sire/dam country/year.
• Existing non-null parsed values are never overwritten — the ancestor of the current merge rule.
• Parent year disagreements stored as warnings and evidence, not applied.

Performance

• Identified a leading-wildcard Studbook query (LIKE '%name%') as the major slowdown; replaced with indexed exact-name lookup plus an indexed prefix fallback restricted to records with Australian context. Added bounded LRU Studbook caches (100,000 entries).
• A 600-file benchmark improved from ~69 files/s to ~207–210 files/s.
• Four importer workers saturated the NVMe drive; settled on three, with equal non-overlapping 988,350-file ranges.

Dashboard and operations

• Compact worker progress with absolute source positions, deltas, rate, elapsed and ETA; explicit --worker-id; live aggregation of active workers; 10-second Runs page refresh; direct links to each worker's decisions, errors, reviews, blocked and auto-write rows.
• reset-mysql-local-rs --yes truncates staging and breeding while always keeping country and studbook_profiles.

Review logic

• pre_1970, pre_1940 and large parent-age gaps kept as warnings, not blocks.
• Impossible parent ages, wrong parent sex, same sire/dam identity and blocked lineage branches kept as blocking.

2026-07-05 and earlier — SQLite prototype

The original tool imported PedigreeQuery HTML into a local staging SQLite database at data/breeding_infill.sqlite3, with a review dashboard on 127.0.0.1:8765 serving the source HTML with PedigreeQuery's CSS rewritten to a local copy. It was explicitly a staging and review tool, not a QDB writer. The project then lived at C:\Users\Robert\Codex\Projects\BreedingInfill.

SQLite is no longer part of any supported path.