Breeding Rules

Last reviewed: 2026-09-07 Authoritative code: rebuild/common.py, rebuild/load_pq.py, rebuild/load_studbook.py, rebuild/linking.py, rebuild/reconcile_sources.py, rebuild/dedup_adjacent_years.sql

This is the page to read before changing anything. Every rule below is here because it catches something real, and most carry the measurement that set the threshold. Where a rule looks arbitrary, it is not — the number came from counting.

---

0. Identity — the rule everything else rests on

A horse is (publish_name, country_id, year_of_foal). Nothing else.

Enforced by the unique index uq_breeding_identity. publish_name is stored uppercased with apostrophes stripped.

key_name — PedigreeQuery's own disambiguating key, e.g. GOLD DIGGER2, TIEPOLO3 — is not stored. It survives only inside load_pq.py as the join key that resolves parents during the load, and the page it points at is recorded in link_to_file for provenance. The key_name, sire_key_name and dam_key_name columns were dropped on 27 August 2026, and the loaders refuse to run against a schema that still has them.

Why the triple rather than key_name: key_name is a PedigreeQuery artefact. The Australian Studbook does not have one, and neither does race data. An identity that only one source can express cannot be the identity two sources agree on. The triple is expressible by every source, which is what makes cross-source matching possible at all.

The known cost: two genuinely different horses can share a name, a country and a year. That is a real, small population and it is reported, not merged — see §6. Conversely, GOLD DIGGER (ARG 1933) and GOLD DIGGER2 (USA 1962) separate cleanly on the triple; so do the two HOIST THE FLAGs (USA 1968, 97,872 mentions; NZ 1970, exactly one).

---

1. The merge rule — fill blanks, never overwrite

Insert if absent. Fill blanks if present. Never overwrite. Never touch is_locked = 1.

Every write path implements this identically, as ON DUPLICATE KEY UPDATE col = IF(is_locked, existing, COALESCE(existing, new)). It applies to load_pq.py, load_studbook.py, the linker and the dedup merge.

Three consequences worth stating explicitly:

• The loads are idempotent. Run one once or a hundred times, same result.
• Load order is a decision, not an accident. PedigreeQuery loads first, so on a row both sources describe, PQ's values are the ones that survive and the studbook's are discarded. This is deliberate (decision F5) and it is why agreement between the two sources cannot be computed from the table as it stands — reconcile_sources.py re-reads the dump instead of comparing the row against itself.
• mention_count is the one exception. It is a count of evidence, not a fact about the horse, so it accumulates rather than being preserved. The largest value in the table is 960,446.

---

2. Load rules R1–R7 — the row does not enter

Applied identically by both loaders. A row failing any of these is not written and is counted in the run's own tally.

Rule	Test	Why
R1	Country of origin required	A row with no country can never be matched later — not by another source, not by race data, not by a human. Robert's decision, 16 Aug, reaffirmed 22 Aug. Costs roughly 12 % of PQ identities.
R2	Year of foal required	Same reason, and NULLs cannot be deduplicated by a unique index anyway.
R3	Sex required (is_male not NULL)	A NULL blocks progeny lookups in both directions.
R4	publish_name ≤ 45 characters	Column width. The longest real measured value is 41.
R5	publish_name free of ONMOUSE	The PedigreeQuery parser markup leak — a scraping defect, not a name.
R6	A horse is not its own parent
R7	Sire ≠ dam

Measured on the 5 September studbook load of 1,168,043 records: R1 rejected 1,603, R2 zero, R3 17,041, R4 5,334, R5 zero, R6 65, R7 four. 998,626 records queued.

The suffix repair, applied before R1

The studbook dump leaves the country and year inside the name string — "The Hoe (AUS) 1972 ntb" — and nulls the country and year columns for exactly those rows. R1 would have dropped all 67,284 of them. split_suffix() recovers name, country and year from the string first; it fixed 67,010 records on the last run, a 98.0 % recovery.

Sex is derived, not stored as given

sex holds only male or female, derived from is_male. The source vocabulary (stallion / gelding / colt / mare / filly / rig) is deliberately not stored.

Measured 2026-08-27 against tblhorse on 66 matched AU/NZ males: 33 were labelled stallion by the dump where tblhorse and the contemporaneous run records said gelding — BOMBORA BOY (NZ 1973) among them, six starts in 1982 all recorded G. Only one conflicted the other way. is_male, by contrast, had zero male/female conflicts across the whole dump.

So only the trustworthy half is kept. A stored stallion also goes stale the moment a colt is gelded at three, whereas which males are actually sires is a question the table answers directly: SELECT DISTINCT sire_id FROM breeding. (Decision: Robert, 5 September. This retired an earlier tblhorse gelding lookup.)

---

3. Parent identity is read from the parent's own row

A parent's year and country come from the parent's own record, never from the child's page.

Measured across 989,090 parent pairs, the parent's own row agrees with the child's page 99.997 % of the time — and where it differs, the parent's own row is the better source, because it is the consensus across every page that horse appears on rather than one page's transcription.

The stated values the child's page gave are still written, to sire_name / sire_year / sire_country_id and the dam equivalents. They are kept as stated, so a disagreement stays visible rather than being silently corrected. This is what makes a link verifiable later: a bare name string is not enough, because names repeat.

---

4. Link rules R8–R11 — the link is refused, the row still enters

A link that fails these stays NULL and the horse still loads. A bad parent year costs one edge, not a horse.

Rule	Test
R8	A sire must be male, a dam must be female
R9	The parent was foaled strictly before the child
R10	Gap ≥ 2 years (gestation)
R11	Sire gap ≤ 40 years, dam gap ≤ 30 years

The R11 ceilings come from measurement over 400,000 rows: beyond 40 for sires and 30 for dams, the population is dominated by the ~YYY- year-truncation artefact rather than by real horses (max observed "gap" 2,005 years). Setting the dam limit at 20 instead would have wrongly blocked 5,011 real broodmare rows per 400,000.

Do not remove the CASTs

year_of_foal is smallint unsigned. A bare c.year_of_foal - p.year_of_foal raises "BIGINT UNSIGNED value is out of range" the moment the parent is younger — which is ordinary data — and ABS() does not help, because the subtraction fails first. Every comparison is CAST(... AS SIGNED). This bug has been found the hard way twice.

---

5. Rule B — how a parent is matched within the table

Three passes per role, each strictly narrower, each touching only rows whose parent id is still NULL. The order is the rule. Implemented once, in rebuild/linking.py, and used by both link_parents.py and load_studbook.py so the two cannot drift apart.

Pass	Match on	Notes
1	(name, country) where that pair identifies exactly one horse	Year ignored — if only one horse carries that name in that country, the year cannot make it a different horse
2a	Exact (name, country, year)
2b	(name, country, year + 1), northern-hemisphere parents only	The season-year rule, §5.1

Every pass is unique by construction. Pass 1 reads _uniq_parent, a scratch table of the (name, country) pairs occurring exactly once; 2a and 2b join on the unique key itself, so at most one row can match. R8–R11 apply to all three.

The scratch table exists because MySQL raises ER_UPDATE_TABLE_USED (1093) for a subquery reading the table being updated. The uniqueness test has to be materialised somewhere, and a two-column table of ~2.5 M rows is the cheapest somewhere there is. On the last build it held 2,543,692 unambiguous pairs.

Nothing here ever overwrites a parent id that is already set, and nothing touches is_locked = 1. That is what makes the linker safe to re-run at any time, on any range, as often as you like — and it is why linking is a permanent maintenance operation, not a build phase. Roughly 22 % of sires have no scraped page of their own, new scrapes keep arriving, and correcting a corrupt sire year should reconnect the thousands of children it broke. All three are re-links, not re-loads.

5.1 The season-year rule

A horse's own record carries its true foaling year. An Australian-sourced reference to that horse as a parent quotes the season year, one lower.

This applies to northern-hemisphere horses foaled in the first half of the year, and never to southern-hemisphere horses. Verified across 17,206 northern records holding a real date of birth, with no exceptions in any month. Measured across 1.45 M studbook edges, not one southern-hemisphere parent carried the offset.

Southern countries are therefore excluded from pass 2b by name: AUS, NZ, ARG, SAF, CHI, BRZ, URU, PER, ZIM.

Worked example — ZIGELLO (IRE), foaled 30 January 2016, a date that admits no other reading:

Source	States her year as
Her own studbook record (hid 1173408)	2016
Her own PedigreeQuery page	2016
breeding	2016
Miss Ziggy (AUS 2020), naming her as dam	2015
Terzima (AUS 2021), naming her as dam	2015
Zig Zag Torque (AUS 2022), naming her as dam	2015

All three foals link to the correct mare, and the stated 2015 is preserved on each foal's row so the discrepancy stays visible.

The relaxation is one-directional and never symmetric. year - 1 is not tried, and year + 1 is never applied to a southern parent — the AU/NZ name pool is exactly where a loosened match could do harm.

5.2 Do not relax the year further

Recommendation from the August validation, and it is load-bearing. Where a parent cannot be identified on name + country + year (+1 northern), leave it unlinked and report it. Twelve such cases arose across four race days and eleven were resolved by hand within minutes using the damsire. A person reviewing five exceptions is a better outcome than an automatic rule linking to the wrong mare — because a wrong link is invisible afterwards.

---

6. Cross-source agreement — rule C5

Matching within one source may ignore the year (rule B pass 1). Matching across sources requires it. reconcile_sources.py resolves the studbook's stated parents by exact (name, country, year), then year + 1 for northern parents only, and accepts a match only where exactly one candidate survives.

What "agree" means is worth being precise about, because the obvious reading is a tautology. Two sources land on the same row because the identity triple matched — that is the unique key — so identity agreement is true by construction for every dual-source row and proves nothing.

The test is the parents, on resolved identity: do the studbook's stated sire and dam resolve to the same breeding rows that PedigreeQuery's stated sire and dam resolved to?

That is tolerant of the two sources' different year conventions, and it is the stronger claim anyway — both sources point at the same horse, not merely at the same string.

Result: is_checked = 1 where both sources were present; is_locked = 1 where both were present and both parents resolved to the same horse.

---

7. Duplicate identities and the progeny rule

The same horse can appear twice, one year apart, because the two sources disagree about which year a southern foal belongs to. rebuild/dedup_adjacent_years.sql resolves these. Two rules govern it.

Rule 1 — nothing merges without positive evidence

The arbiter is foal_date. Where exactly one of the two rows carries a real foaling date, the season rule says which year that date implies, and the row holding that year survives. Neither row dated, or both dated, is not evidence — both are skipped.

Rule 2 — a row with progeny is never deleted (Robert, 5 September)

If anything in breeding names the duplicate as its sire or dam, the pair is marked for manual review and both rows are left exactly as they are. Repointing children automatically would rewrite real pedigree links on the strength of a year comparison, and a wrong one is invisible afterwards — the child simply has a different parent and nothing says it ever had another.

Outcome of the September run, from rs.breeding_dedup_20260905:

Verdict	Pairs	Progeny involved
MERGE	1,233	0
REVIEW — duplicate has progeny, do not delete	537	1,888
SKIP — neither row has a foal date	474	0
REVIEW — foal date contradicts its own row	28	0
REVIEW — 3+ rows in consecutive years	18	0
SKIP — both rows dated, treat as distinct	9	0
SKIP — descriptor name, not a real name	2	0

Two exclusions are worth knowing:

• Descriptor names. WILDAIR MARE, SPECULATOR COLT, HAUTBOY MARE are not names — they are how an unnamed 18th/19th-century horse is written down. A mare by Wildair out of a Babraham mare could legitimately be produced in consecutive years, so same-name-same-parents-adjacent-years is a false positive for this class.
• Chains. Where three or more rows run in consecutive years (WILDAIR MARE GB runs 1775–1779) the keep/drop logic would corrupt it — a row could be a survivor in one pair and a casualty in another.

The script backs up every affected row before writing, and carries a guard that must return zero before the delete step runs.

Identity collisions that are not duplicates

Measured on 103,189 identities: 105 collision groups, of which only 27 shared both parents (probable PedigreeQuery duplicates) and 78 did not — genuinely distinct horses. The rule: a collision is a duplicate candidate only when the two rows also share both parent names. Collision alone means two different horses. The old pipeline blocked all of them, which was wrong three times in four. These are reported in identity_conflicts.csv and merged by a person, never automatically.

---

8. Locking

is_locked = 1 means automated processes must not modify the row. It is set only where two independent sources agree on the parents (§6).

Locking is a one-way door. A locked row can never be corrected by a reload, and the parent-link pass deliberately skips locked rows — so locking before linking freezes NULL links permanently. Link first.

is_checked = 1 means reviewed and verified against a trusted source. A tier rule is not that, and automated loads leave it 0.

Enforcement of the lock is by a single write gateway — one routine carrying all the checks, through which every update, insert and delete passes. Database triggers were considered and not adopted; the file is kept unrunnable at archive/add_lock_triggers_NOT_ADOPTED.sql and was never applied to either server.

Both flags are currently 0 across the whole table: the reconcile step has not been run against the rebuilt data.

---

9. Deliberately absent

Removed from the previous design, each for a stated reason:

Not present	Because
tier 1–4	Completeness is sire_id IS NOT NULL AND dam_id IS NOT NULL, asked live. Tier recorded what was known at staging and never changed, so a row could become fully linked and still never qualify.
Confidence score	mention_count is the honest version — a count of evidence rather than a number the pipeline invented.
Per-mention auto_write / review / blocked	88 M confidence judgements to produce 3 M rows, 96 % of them on repeats of the same horse.
damsire_id	It is dam.sire_id. Storing it means it can drift from what it is derived from.
Foreign keys	The load inserts children before their parents exist. Added after a build, where the validating ALTER doubles as an integrity proof.
Synthetic Unknown parent rows	An unknown parent stays NULL. A foundation mare with no known parents is a row with both links NULL, and the recursive walk terminates there naturally.

10. Acceptance checks

Run after a build. Each must return zero, except the last.

1. A sire that is not male, or a dam that is not female.
2. A child born before, or within two years of, a linked parent — two separate statements, one per role.
3. A sire_id or dam_id pointing at nothing.
4. A two-generation cycle: A is B's parent and B is A's.
5. Two rows sharing one identity — expected to be small and non-zero. Review the list; do not "fix" it by merging.

Never write ON p.id IN (c.sire_id, c.dam_id). An IN list holding column expressions rather than constants cannot be resolved to an index lookup, so MySQL scans the whole table for every driving row. That exact mistake cost this project a half-hour hang on a 503,100-row table (≈2.7 × 10¹¹ row reads); on 3 M rows it never finishes. Use two UNIONed equality joins — same predicate, eq_ref plan, linear.

---

11. Known data defects the rules do not fix

These need a re-parse of source HTML, not SQL:

• ~YYY- year truncation. PedigreeQuery's "approximate year, unknown final digit" notation is over-trimmed by one character, so ~184- parses as 184. This is why the table's minimum year_of_foal is 182. year_text already holds the damaged value, so it is not repairable from the database.
• Turkish charset defect — Windows-1254 read as Latin-1.
• The ONMOUSE markup leak — caught by R5 at load, but the underlying parse is still wrong.

Roughly 22.2 % of sires (24,986 of 112,295) have no scraped page of their own, including NORTHERN DANCER, NATIVE DANCER, SECRETARIAT, NASRULLAH, HYPERION and PHALARIS. They still enter breeding correctly from the pages they appear on; what is lost is depth behind them. Dams are only 1.7 % missing. reports.py ranks this backlog by how many children are waiting behind each missing horse.