Pipeline and Operations

Last reviewed: 2026-09-07

Every command is a dry run unless given --apply (or apply for the batch driver). Nothing writes by default.

Full rebuild

run_breeding_rebuild.bat            dry run, writes nothing
run_breeding_rebuild.bat apply      actually writes

Runs five steps in order and stops at the first failure. Each step writes its own log under logs\rebuild_<timestamp>\, and a rolling 00_summary.log.

Step	Script	What it does	Last measured
0	rebuild\preflight_rebuild.py	Environment, schema, source data and program inventory. Exits 2 on blockers.	seconds
1	rebuild\load_pq.py	rs.parsed_horses → breeding	1.6 min, 2,496,498 rows
2	rebuild\load_studbook.py	newAUData\*.jsonl.gz → breeding, linking as it loads	28.9 min, +611,461 rows
3	rebuild\link_parents.py	Rule B over the whole table	0.3 min
4	rebuild\reports.py	Conflicts, unlinked parents, anomalous links	~1 min

To watch a step live from another window:

powershell -Command "Get-Content -Wait logs\rebuild_<stamp>\01_load_pq.log"

Step 1 — PedigreeQuery

Builds a scratch table _pq_identity, one row per key_name, in a single pass over parsed_horses in chunks of 200,000 ids. It then self-joins that table twice to read each parent's own identity, writes breeding, and drops the scratch table (--keep-map keeps it, --rebuild-map starts over).

payload_json and profile_json are never referenced, so the 107 GB of overflow pages in parsed_horses are never touched. gen_file packs the generation in front of the source path so MIN() returns the lowest generation — the horse's own page where it has one.

An interrupted run is safe: the last committed chunk stands and re-running resumes.

Step 2 — Studbook

Reads horses_*.jsonl.gz recursively from %BREEDING_DATA%\Studbook\newAUData (--source overrides for one run). One file, one flush — batches never straddle files, because the point is a clean id range to hand the linker.

After each file the new id range goes through rule B, then one full-table pass at the end picks up children whose parent only arrived in a later file. A horse loaded in file 130 can be the sire of one loaded in file 2; the per-file passes only look forward. On the last run that final pass backfilled 2,493,712 sire and 2,476,985 dam links.

--no-link turns linking off; --link-every N reduces how often the uniqueness table is rebuilt.

Step 3 — Link parents

The full-table run of rule B. Builds _uniq_parent once, sweeps every id in chunks of 500,000, drops it. Safe to re-run whenever — every pass only fills NULLs and skips is_locked = 1, so a horse that arrives next month and turns out to be somebody's dam is picked up by running this again. Nothing has to be reloaded.

Because step 2 already links as it goes, step 3 normally reports zero new links on a fresh full build. That is the expected outcome, not a failure.

Step 4 — Reports

Writes CSVs to reports\. Read-only.

File	What it is
anomalous_links_sire.csv / _dam.csv	Read this one first. A link whose parent's own year is more than one off the stated year, or any gap at all on a southern-hemisphere parent. These are name collisions rather than calendar differences — R8–R11 cannot catch them because the age gaps stay plausible. 789 rows on the last build.
unlinked_sires.csv / unlinked_dams.csv	Parents named but never found, most children first. This is the ranked scrape backlog.
identity_conflicts.csv	One name and country carrying several years. Under the identity triple these are separate horses; the list exists so a genuine duplicate can be spotted.
summary.txt	Counts and linked percentages.

Duplicate resolution — dedup_adjacent_years.sql

Run after a build, by hand, in MySQL Workbench.

SET SQL_SAFE_UPDATES = 0 is per connection. In Workbench each tab is its own connection, so run it in the same tab as the statements or they fail with error 1175.

Steps 1–8 write nothing to breeding: they build a decision table (rs.breeding_dedup_20260905), classify every pair, count progeny, and back up every affected row. Step 8 carries a guard that must return zero — if anything with progeny is queued for deletion, do not run step 9. Step 9 merges; step 10 verifies, and every check must return 0.

Both governing rules are on the Breeding Rules page: nothing merges without positive evidence, and a row with progeny is never deleted.

After running this, qdb.breeding is stale. Re-ship it.

Cross-source reconcile — reconcile_sources.py

Re-reads the studbook dump into a scratch table, resolves each record's stated parents by rule C5, and compares them against what the breeding row already holds.

• is_checked = 1 — both sources were present for this identity
• is_locked = 1 — and both parents resolved to the same horse

Locking is a one-way door, which is why it is only applied where two independent sources agree. Not yet run against the rebuilt table — both flags are currently 0 across all 3.1 M rows.

Shipping to QDB — ship_to_qdb.py

python rebuild\ship_to_qdb.py --stage check
python rebuild\ship_to_qdb.py --stage ship
python rebuild\ship_to_qdb.py --stage ship --apply
python rebuild\ship_to_qdb.py --stage verify

Copies rs.breeding to qdb.breeding preserving primary keys. sire_id and dam_id are self-referencing links into breeding.id; if the destination reassigned ids by AUTO_INCREMENT, every one of those links would have to be remapped afterwards. Since there are no foreign keys on qdb.breeding, insert order does not matter — a child may be inserted before its parent.

What it enforces:

• the destination must be empty (override is --allow-nonempty, deliberately awkward)
• source and destination column sets must match exactly
• every source column must appear in COLUMNS — nothing is dropped silently. This check exists because evidence_json was once omitted on the assumption that a breeding_evidence table existed. It never did, so the entire audit trail was being silently dropped from every shipped row while --stage check still went green — it only tested that the column list was a subset of the source.
• verify re-reads both sides and compares counts, id ranges and checksums

--max-id scopes ship and verify identically, so a small trial verifies cleanly. --limit caps how many rows one run will insert.

Re-shipping

Shipping is provisional until something downstream stores a breeding id. While qdb.runner.breeding_id is 0 on every row, nothing depends on these ids and the whole table can be thrown away and shipped again:

python rebuild\ship_to_qdb.py --stage ship --reship --apply

--reship truncates first and refuses the moment any runner carries a non-zero breeding_id — at that point the ids are load-bearing, re-shipping would break those references, and rs.breeding is effectively frozen. The guard is checked, not remembered.

Do not use --allow-nonempty to re-ship. It switches to INSERT IGNORE, which silently keeps whatever is already there and hides every row that differs. It exists only to resume an interrupted ship.

Current state: qdb.breeding holds 3,106,726 rows, ids 1–4,218,032, matching rs.breeding row for row.

Incremental work

The bulk build and a single new horse are the same two operations. Scope the load to the horses you want, then run the linker. breeding_bundle.py is the horse-plus-two-generations path — it validates one horse plus both parents against what breeding already holds (seven records checked, three written) and refuses to write if any check failed. run_horse_bundle.bat always prints the dry run first and asks [y/N] before applying.

Horses are named the way you would say them:

run_horse_bundle.bat "Brutal (NZ) 2015" "Kiss Moon (USA) 2011"

Country matches racing_code, iso_alpha or iso3. An ambiguous spec stops the run and lists the options; an unresolvable one prints every horse carrying that name.

Data location

Bulk data is read from %BREEDING_DATA%, default Z:\Breedinginfill. copy_data_to_z.bat populates it. To point a run at a different copy:

set BREEDING_DATA=C:\Users\Robert\Projects\BreedingInfill

There is deliberately no silent fallback to the project directory. If the share is not mounted the run stops rather than quietly reading a stale local copy nobody has updated since the move — which is the failure mode that makes a data migration look fine for a week.

Safety rules

1. Nothing writes without --apply.
2. Link before you lock. The link pass skips is_locked = 1, so locking first freezes NULL links permanently.
3. Interrupts are safe. Every loader commits by chunk; Ctrl+C leaves the last committed chunk intact and re-running resumes.
4. After a dedup, re-ship QDB.
5. Verify after a timeout, never assume. Of three consecutive client timeouts observed on the MCP bridge, two had committed server-side and one had not. Always check the count or cursor.
6. Re-read a file before patching it. Work has twice been started against a stale cached copy; both times diffing against the file on disk before writing caught it.

Verification

python -m unittest discover tests
python -m py_compile breeding_infill\cli.py breeding_infill\importer.py breeding_infill\mysql_staging.py breeding_infill\dashboard.py

Legacy paths

The breeding_infill/ package (importer, parser, dashboard on http://127.0.0.1:8765/) and breeding_build.py / breeding_bundle.py belong to the pre-rebuild eight-phase pipeline. They still run, but rebuild/ is the current path. SQLite is legacy and is not part of any supported workflow.