merging-duplicate-teams

Imported from dallasheidt14/PitchRank (.claude/skills/merging-duplicate-teams/SKILL.md). Install upstream with npx skills add dallasheidt14/PitchRank --skill merging-duplicate-teams. Copyright stays with the author.

Merging duplicate teams

Treat every merge as a data-destroying write until game evidence says otherwise: it deprecates a team row and repoints its provider alias, so if the rows were different squads a real team stops existing and its future games are attributed to another team.

A refusal is not free either. The pipeline refuses on artifacts as well as on evidence, and a pair refused on an artifact is refused identically every week forever. Steps 4 and 5 are a matched pair: one hunts wrong approvals, the other hunts wrong refusals. Running only the first is how the same true duplicate survives a year of weekly runs.

Copy this checklist and check off items as you complete them:

Task Progress:
- [ ] Step 1: Preflight — credentials, then prove the guards still fire
- [ ] Step 2: Generate candidates from every doorway
- [ ] Step 3: Decide every candidate from evidence
- [ ] Step 4: Review the refusals
- [ ] Step 5: Adversarially review the approved set
- [ ] Step 6: Apply only what survives review
- [ ] Step 7: Repair the downstream side effects
- [ ] Step 8: Record what ran and what was held

Step 1: Preflight

Credentials. decide_team_merges.py and apply_vetted_team_merges.py both call load_dotenv(... / '.env.local'). That file does not exist on this checkout — the Supabase keys are in root .env. Run as documented, both exit with SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY must be set, which reads like missing credentials rather than the wrong file. Preload the environment from root .env before Step 3.

Imports. decide_team_merges.py imports _UAGE_TOKEN and birth_years from src/utils/team_name_utils.py. On a checkout that predates them the scan fails with an ImportError that does not name the branch as the cause.

Prove this skill is still true. Most of what follows is a claim about code behaviour or a row count, and both rot. Run the checker before trusting any of it:

python scripts/check_merge_skill_assumptions.py              # assertions + live counts
python scripts/check_merge_skill_assumptions.py --code-only  # no database, seconds

It exits non-zero when an assertion breaks. A failure means this skill is now wrong, not that the codebase is — most assertions encode a bug the guidance routes around, so fixing the bug fails the check and sends you here to delete the workaround. It also warns when a quoted figure has drifted more than 20%, which is the signal to re-measure the prose.

Read its output rather than skimming it. That the birth-year guard returns nothing for a U-label, and raises no conflict against either 2008 or 2009, is not a failure to fix — it is the blindness you carry into every later judgement. See references/failure-modes.md.

When you change the skill's claims, update RECORDED in that script in the same commit.

Propose-only mode

Take Steps 2 and 3 and stop there. Preload the environment from root .env first, as Step 1 says.

mkdir -p .turbo/step3
python scripts/decide_team_merges.py --all-cohorts --out .turbo/step3/decisions.json

Decide-only by construction — the script registers no execute flag and writes nothing to the database. It writes the decisions file and an _approved file beside it, each pair carrying its verdict and the reason for it. Create the --out directory first; nothing else does.

Resume at Step 4, and reach Step 6 only through Step 5. The approved list is a candidate list, not a safe list.

Nothing is written at this stage, so there is nothing to undo. An applied merge is reversible — the RPC snapshots the deprecated row and leaves games untouched — but pass --merged-by explicitly when reverting, because the reverter's default actor is not the one the applier records and a default-argument revert matches nothing and reports success.

Step 2: Generate candidates from every doorway

There are four independent ways to nominate a pair, A to D below, and their blind spots differ, so no one of them stands in for the others.

Doorway A — name similarity. scripts/find_fuzzy_duplicate_teams.py, called in-process by decide_team_merges.py. This is the recall ceiling for everything in Step 3: a pair it cannot propose is never judged, never reviewed, and never merged. It proposes a pair only when the two rows agree on all of stored gender, stored age group, state_code bucket, and byte-identical lowercased club_name, and then score >= the threshold. The club-name, state and age-group cleanups all move fields in that list, so run them before a scan you intend to act on — and expect the population to move both ways, since filling a blank state or club splits a pair that shared the blank bucket, and a correction into u9 leaves the range this scan covers.

So these are invisible at any threshold: same club with rows stamped in different states; the same club spelled two ways; either row named unknown_<digits>; either name carrying an AD, HD or EA division token, or MLS NEXT (4,890 live rows). That last exclusion used to be a substring test that also caught EAST, EAGLES and ADAMS, withholding 2,418 unrelated rows; it matches whole tokens as of IMP-135, so those names now reach the scan.

Separately, fetch_teams paged without an .order() clause, dropping ~16% of each cohort scan; .order("team_id_master") closed it (IMP-134). Counts this pipeline produced before that are floors, including any estimate of what auto-merge would touch — the scan now reaches rows it never fetched. The bar for moving FUZZY_AUTO_MERGE_ENABLED is in Re-enabling the weekly job below.

The blockers stack, so do not read any one of them as the barrier. A worked pair — Black Conshy '12 G against Black Conshy '11/'12 (G) *, one squad registered twice — is stopped independently by three: different state_code buckets, a score of 0.877 against a 0.90 threshold, and _should_skip_pair on mismatched age tokens. Repairing the state stamps alone would still not surface it. Whenever you attribute a miss to one gate, check the others before proposing a fix aimed at it.

Doorway B — shared fixture fingerprints. Two rows that independently recorded the same (game_date, merge-resolved opponent, own_score, opp_score) under different game rows are the same team's schedule imported twice. It reads no name, so it reaches the placeholder, laundered and wrong-state classes that defeat Doorway A.

It does not reach everything. The fingerprint needs the opponent to resolve to a single row. When a team is imported twice from two providers its opponents usually are too, the two schedules name different opponent rows, and nothing matches. Doorway B is blind to precisely the cross-provider duplicate — which is the class Step 4 exists to rescue from the same-day rule. The two doorways have complementary blind spots; neither is a superset.

Require at least two shared fingerprints, and drop any pair that ever played each other. One shared fixture is noise: measured over one season, 17,476 pairs share one, 1,709 share two, and 399 of those had met on the pitch.

scripts/find_regid_duplicate_merges.py is the one committed implementation, and it covers only the placeholder case — it seeds from unknown_<id> rows, so it answers "which named team is this placeholder" rather than "which two rows in this cohort are duplicates". Read .turbo/specs/second-layer-duplicate-detection.md for the design that generalises it, and the script's own docstring for the blind spots it ships with. For every other class, Doorway B is a hand-built query today.

Re-running it as written returns almost nothing: the 639-merge batch of 2026-08-27 exhausted its Tier A. What remains is the held tiers (280 partial, 79 head-to-head, 27 ambiguous, ~1,000 under the three-game floor), and those need a rule change or a person, not a rerun. Establish the remaining count before proposing to repeat anything.

Stale tier output from an earlier run may still be lying around. An empty tier file proves nothing on its own — verify against the database, never against a scratch file.

Doorway C — the same squad registered with two providers. Both doorways above miss this class entirely: Doorway A refuses it because the two rows' club_name values are rarely byte-identical across providers, and Doorway B is blind to it by its own admission, because the opponents are duplicated too so no fixture fingerprint matches. It is nevertheless the single largest clean class in the database, and it is the one a customer notices, because both rows are published on the rankings boards with half a record each.

scripts/find_cross_provider_duplicates.py implements it, propose-only: it writes a CSV of proposals and refusals plus a JSON of the proposals alone, and touches nothing. Feed the vetted JSON to apply_vetted_team_merges.py.

It implements rules 1, 2, 4, 5 and 7, the first half of rule 3, and the self-play half of rule 6 — not the rest. Read the numbered list below as the doorway's definition, not as the script's coverage. Unimplemented, and still a hand-built query: the rebrand-bridge branch of rule 3, the containment tiers of rule 4, rule 6's sibling-cohort game-count outlier, and any pair not anchored on GotSport (a TGS row duplicated on SincSports). It also skips soccereventsgroup, athletes2events and playmetrics_tournament, which are operator-run per event. IMP-259 tracks the containment work.

Do not route its output through decide_team_merges.py — not for the reason that bars Doorway B, whose raw-string club compare does not bite here. It has no tiers and computes no direction: it takes whatever direction it is handed and adds nothing this doorway needs. Its gender screen reads Boys/Girls as words only, so it clears the Oklahoma Cosmos pair that a bare 12B against 12G refuses. The detector carries its one screen worth having, the fused-registration REVIEW.

The pair qualifies when all of:

1. Different team-level provider_id. This is the dominant signal — see the calibration below. Exclude modular11 rows; they are out of scope by operator decision.

2. Same age_group, gender and state_code.

3. Club matches after normalising lower(regexp_replace(club_name,'[^a-zA-Z0-9]','','g')) — which alone unifies Total Futbol Academy(OH) with Total Futbol Academy (OH), a pair Doorway A's byte-identical test rejects — or the two clubs are joined by a rebrand bridge (below).

   That normalisation strips punctuation and nothing else, so an org suffix defeats it. Colorado EDGE and Colorado EDGE SC squash to different strings, as do Denver Kickers and Denver Kickers Sport Club. A provider that writes the suffix where another omits it therefore hides every one of that club's duplicates from this doorway — it did so for 11 clubs holding 138 teams in the Colorado import of 2026-09-23, a one-off count of that batch rather than a figure the preflight checker tracks, and one that reads as zero now those clubs are consolidated. The two code gaps behind it are checked. normalize_club_for_comparison in src/utils/team_name_utils.py is stronger, collapsing X SC onto X Soccer Club, but it too keeps X apart from X SC. The repair is a data fix, not a detector change: add the variant to CLUB_CANONICAL_OVERRIDES in scripts/full_club_analysis.py, whose weekly run rewrites the rows, and the pairs then appear on the next scan. Fixing only the rows is undone the following Monday — see the normalizing-club-names skill.

4. Normalised team names identical, minimum length 8. Containment is a separate, weaker tier; see the warning below.

5. Zero head-to-head, zero shared game dates, and opponent Jaccard ≤ 0.20.

6. Neither row fails the survivor-integrity check in Step 5.

7. The two registered names do not state opposite genders, reading a 12B/12G affix and not only the words Boys/Girls. Both rows can carry the same wrong stored gender — that is what let the pair into the pool — while team_name_original says otherwise on one side.

Club equality decides who may pair; are_same_club decides who competes. Comparing clubs byte-for-byte is the same thing as grouping on the club, and it hides a third row of the club filed under the club's other spelling — so a cluster of three reads as a clean pair of two and a bulk rule leaves the other duplicate standing. The detector therefore groups on name and cohort with the club left out, pairs only on normalised club equality, and counts any further row of the group that are_same_club matches — the same spelling or a near one — as a competing partner, which demotes the pair to review unless its alias already points at the survivor. Loosening the pairing rule itself to similarity is a different move, and an uncalibrated one — see IMP-259.

The competing threshold is deliberately lower than the matchers'. are_same_club gates matching at 0.85–0.95 in affinity_wa_matcher, playmetrics_matcher and roster_resolver, where a True fuses two rows and high is therefore the conservative end. Here True only demotes a pair to review, so low is the conservative end and the default is 0.60. Copying the matchers' number would invert the safety margin: measured over the 45 real third-row club pairs in the database on 2026-09-22, Kings Hammer Aris against Kings Hammer Soccer Club scores 0.828 — one club, caught at 0.60 and missed at 0.85.

The branch can sit in either column, and then neither column matches. One provider files the branch as the club (club_name = "ALBION SC Boulder County", team_name = "GU11 Premier") while another files the parent as the club and names the branch inside the team (club_name = "Albion SC Colorado", team_name = "ALBION SC Boulder County GU11 Premier"). Club-to-club fails, team-to-team fails, and rule 3 refuses the pair as two clubs — correctly, by its own lights, since these branches are separate clubs here. The pair is nonetheless one team. Reach it with an asymmetric comparison: normalised club_name + team_name on the branch-as-club side against normalised team_name alone on the parent-as-club side. Keying club+team on both sides does not work and is the easy mistake — the parent club stays in the key, so albionscbouldercountygu11premier faces albionsccoloradoalbionscbouldercountygu11premier and nothing matches. Run the comparison both ways round, since which provider holds the branch varies. In the Colorado import of 2026-09-23 it found 26 pairs four earlier passes had missed, of which 22 merged — a one-off count of that batch, not a tracked figure. Both keys' behaviour is asserted by the preflight checker.

A symmetric club+team key returning nothing is therefore no evidence that this class is empty; it cannot see the class at all. Judge exhaustion from the asymmetric key.

Two cautions, both of which cost a pair in that batch:

• Sub-site codes are identity, and they are not the compass points. This club writes EC and WC; a branch check looking for north/south/east/west and the branch name passes them straight through. One pair matched byte-for-byte on the current names while the keep's team_name_original read WC G13 Premier against a PlayMetrics row named EC GU14 Premier — and the real WC PlayMetrics row was live in the same division. Merging would have attached one sub-site's history to the other.
• For a re-registering club, team_name is the current name and team_name_original is the stale one. That inverts the advice below. Renames here correlate with last_scraped_at, and they are not age relabels: WC G13 Premier became EC GU14 Premier, G13 Academy II became GU14 Academy I. An age rewrite touches the age token alone, never an ordinal or a site code, so a changed ordinal is the tell that the provider re-registered rather than that the row drifted. Compare current name against current name, and read team_name_original for provenance only.

Club bridges, and the trap inside them. A club whose name differs across providers can still be matched by mining team_merge_map for pairs of differing club_name values already joined by a vetted merge (~160 exist). Drop the No Club Selection placeholder first. Then split the bridges by whether one club string contains the other:

Against a 0.43% base rate, branch bridges pair teams that have played each other at twenty times chance — they are sibling branches fielding distinct squads. Use rebrand bridges only.

But the string test is a filter, not the verdict — validate every bridge from fixtures. It has a hole in both directions, and a 23-pair review on 2026-09-12 found three pairs through it:

• A branch whose qualifier does not nest inside the parent name escapes the containment test. Legends FC (ca) and Legends FC SD North normalise to strings where neither contains the other, so the classifier called it a rebrand. They are two clubs: their teams have played each other 30 times across u11–u20, and at one TGS event SD North entered two 2011 boys squads while Legends FC (ca) entered its own.
• Conversely, a "two clubs" verdict does not by itself kill a pair. Twice the foreign club name was a provider's umbrella org stamp, contradicted by the row's own team_name_original: a GotSport row filed under Players Development Academy is literally named PDA Hibernian Mbappe, and one filed under Rebels Soccer Club has team_name_original = "Flyte SC-IE B10 Contreras". Read team_name_original, not just club_name.

So the bridge test that actually works is: do teams of club A play teams of club B? Frequent fixtures across cohorts mean two organisations; zero fixtures plus an overlapping roster of team names mean one club under two spellings. Check that before trusting any pair on the bridge.

Never trust the stored gender or age_group of a candidate row. The clearest near-miss of that review was Oklahoma Cosmos 2012 Premier, where the two rows passed every club and name test and would have fused a girls team into a boys team: one row's team_name_original is Oklahoma Cosmos 12G Premier and all seven of its opponents are female, but its stored gender says Male, which is the only reason it entered the candidate pool. Its real twin already existed separately. Re-derive gender and cohort from team_name_original and from the opponents' own rows before approving.

The bridged tier is materially riskier than the same-club tier, and the numbers say so. The same-club identical-name tier returned 30 of 30 on a random hand-check. The bridged tier returned 20 merge, 3 reject out of 23 — roughly one in eight wrong. Treat bridged candidates as review-required, never as a batch to apply on the screens alone.

Doorway D — one squad, its cohort and club spelled differently. The class the owner merges by hand, one cohort at a time: the same club's squad under two rows whose names agree on the squad word and disagree on everything around it, such as ESC- 2015 Purple against EDGE Purple 15B. Doorway A cannot score these past its threshold and Doorway C needs identical names. About a third of the owner's hand merges were two rows on the same provider, re-registered for different events, so this doorway does not require a provider difference.

scripts/find_squad_key_duplicates.py --state <XX> implements it, propose-only, by pairing rows whose squad key — the name with its club, cohort, gender and league words removed — is equal. Its --help carries the pairing rules, the screens and the blind spots; read it rather than a copy here. Its JSON goes through Steps 4 to 6 and then straight to apply_vetted_team_merges.py. Do not route it through decide_team_merges.py: that script compares club_name and state_code as raw strings, which refuses exactly the respellings this doorway pairs.

The cohort test is what makes the key safe, so read it before changing it. Stripping the age tokens throws away the one thing separating 2012 Westy Elite from 2013 Westy Elite, U12G Black from U11G Black, and a club's 2009 squad from its 2007/08 squad, both of which sit on the u19 board. Measured on Colorado 2026-09-23: with the test disabled, 81 of 634 proposals paired two squads a year apart. The script's --help states the cohort rules.

Measured on Colorado, 2026-09-23 (one-off counts, not tracked by the checker):

• Replay against the owner's 36 hand merges. The key matched 28. None of the 36 was refused by the cohort or league test. The 8 misses are all one shape, where one provider names the squad by its league (BU12 Pre MLS Next) and the other by a squad word (BU12 Academy). Only someone who knows the club can pair those.
• Review of the proposals. 255 proposals went to five adversarial reviewers, pair by pair, and then to an opposite-prior pass over their flags. Five were set aside first: three merged by hand meanwhile, one EA division pair, one whose names both missed the stored age group. Of the 250 left, 216 merged, 25 were held and 9 rejected. The rejections were two squads playing different leagues or flights on one weekend, an adult futsal team, a 2008 squad against a 2009 squad, Pre-Academy against Academy, and a row carrying a Modular11 HD alias.
• What caused the holds. Clubs fielding several squads that share one squad word, then gameless rows whose cohort nothing confirms. Expect a pass at this tier's rates rather than a clean batch.

One rebrand the script cannot see, recorded here because the data does not show it: Colorado Rapids Youth Soccer Club merged into Colorado Storm, so a Rapids row and a Storm row of one cohort can be one squad.

Loosening a threshold and adding an independent signal are not the same move. The measured table in evidence-rules.md forbids the first. Doorways B, C and D are the second, and are the only routes past the ceiling.

What the signals are actually worth (measured 2026-09-12)

Calibrated against two labelled sets drawn from the database itself. Do not use team_merge_map wholesale as ground truth — it contains 7,165 pitchrank-bot fuzzy merges, the run that once merged 1,772 pairs of distinct teams. Segment by actor.

• POSITIVES: 2,530 merges made by the owner by hand (dallasheidt@gmail.com and the other human actors), both sides holding games. pitchrank-operator rows are this pipeline's own output and are circular — exclude them.
• NEGATIVES: 33,674 pairs of live teams sharing club, age group, gender and state that have played each other, and so are certainly distinct.

Four things follow, and three of them correct earlier guidance:

1. Cross-provider is worth roughly a 46× likelihood ratio. It is the strongest single signal available and should anchor any new detector.
2. "Zero head-to-head" on its own proves nothing. The base rate among same-club, same-cohort pairs is 0.43% — same-club teams essentially never play each other whether or not they are duplicates. In 187 candidates you expect 0.8 by chance. Use it to exclude the rare provable negative, never to argue a pair is a duplicate.
3. Opponent overlap runs the opposite way to intuition. Two squads of one club play the same local league and share opponents constantly; one squad recorded twice plays two different circuits and its opponent pools barely intersect. High overlap is a red flag, not confirmation. Reject above ~0.20.
4. The identical-name tier is clean. Containment is not one tier — classify it by the delta. Replaying the detector over all history: of 1,082 identical-name candidates, 0 had ever played each other (≈4.7 expected by base rate). Containment, measured on the same criteria, gives 2,678 pairs with 13 — against ≈11.5 expected, i.e. the base rate. But that average hides classes running four times cleaner and six times worse, and the tier is only worthless when you refuse to split it. See the delta classes below before parking it again.

One caution on "zero shared dates": it describes true duplicates well, but on a pair whose thin side holds 2–5 games it is satisfied by chance and proves little. Only 5 of 187 identical-name candidates had 10+ games on both sides.

Containment: classify by the delta, never apply the tier whole

When one normalised name contains the other, the delta — what the longer name has that the shorter does not — decides whether the pair is one squad or two.

The classes overlap, so they must be evaluated in a fixed order. A delta of red is both a colour and, for a club called Redlands, a club-name substring; ii is both a Roman numeral and a bare token. Classify with the first rule that matches, top to bottom, and the counts below are mutually exclusive. Score them in any other order and the totals stop reconciling.

Population: every pair of teams rows (live and deprecated, modular11 excluded) sharing normalised club + age_group + gender + state_code, on different providers, whose normalised names differ, are each ≥8 characters, and where one contains the other. Measured 2026-09-13.

The club-prefix class is one provider writing the club name into the team name ("Seattle United G16 Copa") while the other does not ("G16 Copa"). It is the only class materially below base rate, at 53 owner merges against a single known-distinct pair. That pair is excluded by the head-to-head screen, which is why the screen stays mandatory rather than becoming a formality: 139 club-prefix pairs were applied on 2026-09-13 after a 12-pair random adversarial review returned 12 for 12, and none of them had played each other.

The negatives are not concentrated in one class. Residual holds 5 of the 13; the three rejected classes hold 7 between them; club prefix holds 1. Rejecting classes 1–4 removes 7 negatives for 357 pairs — which is what lifts the remainder below base rate, and is the whole reason the tier's aggregate reads as chance.

The rl delta deserves its own line because it is a documented never-merge boundary — ECNL and ECNL-RL are different competitions — and the history agrees: not one of those 29 pairs has ever been merged by hand. A detector that ignores the delta proposes all of them.

Screens that still apply on top, all of which caught something in the 2026-09-13 run: reject when a name's gender token contradicts the row's stored gender; reject self-play rows; reject when the pair sits in a club-cohort cluster larger than two without an unambiguous best partner; and re-derive game counts from games rather than teams.game_row_count.

The cheapest confirming evidence in this shape is not the fixture list. With a thin side there are rarely any shared fixtures to find. What settles it is the club's adjacent-cohort sibling appearing in both providers at the same event with a disjoint opponent set — that shows the club registers one row per squad per provider, which is exactly the claim the merge rests on.

Step 3: Decide every candidate from evidence

mkdir -p .turbo/step3
python scripts/decide_team_merges.py --all-cohorts --out .turbo/step3/decisions.json

Scope it with --age-group/--gender, or judge a supplied list with --candidates <file>. --all-cohorts is wider than the scheduled workflow, which covers male only. It omits u9.

Read references/evidence-rules.md before changing any threshold or arguing with a verdict. Every setting looser than the current one destroyed real teams.

Measure a rule change against the pairs it can actually move, and judge both directions. Build that set by hand — same club, gender, state and stored age group, squad words matching once club, age, band and gender words are stripped, and at least one side carrying the construct the change reads — and write it to a file, which --candidates takes. Judge it twice over one gather_evidence result: git show origin/main:scripts/decide_team_merges.py to a temp file and import it under a name of its own, so both copies live in sys.modules at once. Then read every merge the change adds and a sample of the merges it removes, against adjudicated pairs — a removal is as likely to be the regression as an addition. Generating this set with the shipped scan is what to avoid: one --all-cohorts sweep ran 50 minutes of CPU without finishing, where the hand-built set took about a minute.

Do not run Doorway B pairs through this script. Three of its preconditions are artifacts that refuse the double-import duplicate by construction: club_name compared as a raw string so NULL mismatches everything, state_code compared as a raw string, and any shared calendar date refused outright — which is that duplicate's own signature.

Step 4 does not apply to them either; its tells are keyed to reason strings a pair that never ran through this script does not have. Judge a Doorway B pair on its own terms:

• Do the two rows share two or more identical (date, opponent, own_score, opp_score) fingerprints, opponents resolved through team_merge_map?
• Have they ever played each other? One head-to-head ends it.
• Does either row hold a fixture that conflicts with the other's — same date, a genuinely different opponent, different score? That argues two squads, and needs steps 3 and 4 of Step 4's test applied to the opponent before you believe it.
• Do the stored cohort and gender agree, and does neither name contradict its own gender column?
• Which row holds the live schedule and the populated columns? That one survives (Step 6).

Calibration applies to Doorway A only. "Roughly a third reach MERGE" and "one in fourteen approved pairs fail review" were measured on name-similarity candidates. The 2026-08-27 batch approved 62% with zero failures and was not a regression. A rate well below a third is also unexplained by this skill — read it as a cohort already cleaned, or as a broken scan, only after checking which.

The table's "true duplicates merged" column is measured inside the candidate set the scan produced. It is not end-to-end recall and must never be reported as one.

Step 4: Review the refusals

Every REFUSE in the decisions file Step 3 wrote, not a sample. The rules refuse on three artifacts that look identical to real evidence in the output, and each has a specific tell:

Doorway C's and Doorway D's refusals are in scope here too, and are why both CSVs keep them rather than only counting them: C's 0_rejected rows and D's rejected rows carry the same both played a game on the same day reason — the bulk of C's refusals — and the four-step test below applies to them unchanged.

That last row is the one that matters most, and it takes four steps, not two. The rule's stated intent is that a squad cannot be in two places — but the code performs none of the opponent verification evidence-rules.md describes.

1. Same opponent row and same score on the shared date? That is one match imported twice — the dominant duplicate shape here. Evidence of duplication, not against it.
2. Different opponent rows? Do not stop here. Look the opponents up by name.
3. Are those differing opponents themselves a duplicate pair? If they are, step 2 proved nothing — you are looking at one fixture recorded against two copies of one opponent.
4. Whose game is the conflicting one? Games are read through team_merge_map, so a row carries every game of every row merged into it, and the conflict can belong to neither row in front of you. Warriors BU11 Attack against Warriors B15/16 Attack shares one date, and it arrived with Little Warriors 2016 Attack Blue, which pitchrank-bot's weekly scan merged into the survivor on 2026-04-23 — back when that scan was merging distinct teams by name. A conflict sourced that way is a question about that earlier merge, not about this pair: settle it under Splitting a fused row below, by reverting the merge that brought the game in, rather than promoting the pair to Step 5 on top of it.

Step 3 is not hypothetical. Weston FC 2012 DPL against Weston FC U15G DPL shares six dates with different opponents on every one — and those opponents are W&H America U14 Adrenalina DPL / W&H America 15U Adrenalina DPL, and Miramar DA U14 DPL / Miramar DA U15G DPL: duplicate rows of one club. Another pair's opposing row has the words hold duplicate in its own name.

Stopping at step 2 permanently refuses the cross-provider duplicate, which is exactly the class this skill calls dominant. A shared date is a real refusal only when the opponents are genuinely distinct clubs and both games belong to the rows in front of you.

Read each row's registered name in teams.team_name_original as well, since a renamed row can carry another squad's identity: the row now called Warriors BU11 Attack was registered Warriors Sports Academy - Little Warriors B16 Bravo Blue, and Bravo and Attack are two squads that played the same weekends. Query the column directly: the decisions file's merge_name_original / keep_name_original fall back to team_name when it is NULL, which it is on 97,862 live rows, because it is stashed only on a row's first rewrite.

A pair that clears all four is a Step 5 candidate that the rules refused. Promote it by hand and say so in Step 8.

Step 5: Adversarially review the approved set

The rules produce a candidate list, not a safe list. Expect roughly one in fourteen approved pairs to fail review — including, in past runs, a 2008 team about to absorb a 2009 team and a boys squad about to absorb a girls squad.

That rate is Doorway A's. Doorway C measured far cleaner: 30 candidates drawn at random (hashed, not hand-picked) and reviewed pair-by-pair on 2026-09-12 returned 29 merge, 1 hold, 0 reject — no case of two different teams being wrongly paired. With zero errors in 30 the honest statement is that the true rate is probably under 10%; do not quote it as 0%. The single hold was the survivor-integrity failure above, not a mis-paired candidate. Direct the review at the qualifier traps that per-pair name comparison cannot see, all of which appeared in that sample and all of which were settled by same-weekend fixture evidence rather than by reasoning about names. Doorway D measured worse than both: about one approved pair in seven was held or rejected (figures and causes in Step 2).

• Colour — Pacific FC Blue vs Orange, Richmond United Red vs Orange.
• Letter suffix (Washington especially) — Sound FC G16A vs G16B are different squads; both entered the same tournament in the Gold and Silver brackets respectively.
• Roman numeral / ordinal — St. Louis Stars SC II is the club's second squad; at one Pre-ECNL weekend the I squad played Union KC Navy while II played Union KC Red.
• Tier number — Crossfire's RCL 1 vs RCL 2 is a competitive level.
• Coach surname — Beach FC 2014 Pre-ECNL 1 Dominguez names one specific squad.

Split the approved pairs into disjoint slices, sized so each agent examines its slice pair by pair rather than sampling. Launch all agents in a single message. Run them in the foreground so all results return in this turn (model: "opus", no name). Give each agent database access, an adversarial stance — assume each merge is wrong and try to prove it — and a directive to treat the repository and its git index as read-only.

Then re-examine every flagged pair with the opposite prior: that the merge is fine and clubs re-register squads constantly. Spawn a single subagent in the foreground (model: "opus", no name). Most flags do not survive this, and acting on unverified flags discards good merges.

Below about ten pairs, do this inline rather than fanning out. The fan-out exists to make a large batch examinable pair by pair; on three pairs it is pure overhead.

Read references/failure-modes.md for the shapes already known, and direct review at what per-pair name comparison structurally cannot see — two rows in different flights of one league, a club-specific squad qualifier the other row lacks, a surviving row nothing has ever confirmed the identity of.

Rows carrying three or more registrations from one provider are already routed to REVIEW and will not appear here. The residual risk is a two-registration fusion.

Check the survivor is a healthy row, not just that the pair is right

Every rule above asks whether the two rows are the same team. None asks whether the row you are about to merge into is sound — and a merge into an already-fused row compounds the damage silently.

XF 2016 RCL 1 (ab31993f) passed every screen in Doorway C and was still wrong to apply: it holds 42 games against 20, 20, 18 and 17 for its four sibling squads, plays every opponent in its flight twice where siblings play them once, and carries two game rows naming it as both home and away team (2025-10-29, 2025-11-18). It is two squads already fused. Merging a third into it would have buried the problem further.

So before applying, reject any pair where either row:

• appears in a game with home_team_master_id = away_team_master_id, or
• carries a game count far out of line with its sibling squads in the same club and cohort.

Self-play is not exotic: 1,028 game rows across 552 live teams, mostly GotSport (measured 2026-09-12). Those rows also reach the ranking engine, which is a separate defect worth its own backlog entry — but for merge purposes treat one as a hard stop on that pair, and split the row first (see Splitting a fused row).

Count games from games, never from teams.game_row_count

That column is stale. On 2026-09-12, 12 of 68 rows it reported as holding zero games actually held up to five. Building a candidate tier on it does two things at once, both silent: rows with real fixtures are classified as empty and so skip the head-to-head and shared-date screens entirely, and the same rows are excluded from the with-games tier that would have screened them. They fall through both. Always aggregate from games on home_team_master_id / away_team_master_id.

The empty-side class: what is provably safe, and what is not

A side with no games at all is the narrowest class, but "no games" removes every fixture-based test, so most of it is unverifiable rather than safe. Split it:

Provably safe — apply without review. The empty row's (provider_id, provider_team_id) is already present in team_alias_map pointing at the survivor's master. Its fixtures are already being attributed to the survivor, so the merge changes no attribution at all; it only retires the orphan teams row. 17 of 56 candidates met this on 2026-09-12.

Everything else — per-pair review. And the failure this class actually produces at scale is not the one the screens look for. It is a competing empty partner inside a cluster of three or more rows: 49 of those 56 pairs sat in a club-cohort cluster larger than two, and a bulk rule picking one partner arbitrarily leaves the other duplicate standing, or binds a live provider id to a dead registration that has never recorded a game. Before merging an empty row, enumerate every row of that club in that age group, gender and state across all providers, and confirm the proposed partner is the best one rather than merely a match.

Where the pair has zero games on both sides, say so: the merge cannot affect rankings either way, so it is shell tidying, and picking the wrong survivor costs nothing but is also worth no risk.

Hand the owner what review could not settle

Pairs that review held or rejected, and any pair only club knowledge can decide, go to the owner on a review page rather than as a list in chat. The owner, 2026-09-24: "i like the review page". Each card shows both rows, with name, registered name, provider, club, games (merge-resolved, with excluded games counted apart) and the first and last month played, plus the one-line reason the pair was held. The owner picks Merge them, Keep separate or Not sure, can flip which row survives, and can add a note.

python .claude/skills/merging-duplicate-teams/scripts/build_review_page.py \
    --pairs held.json --title "Colorado Held Pairs" --out review.html

--pairs is the list of pairs Step 5 left undecided, each with merge_id and keep_id. The scanners' JSON files hold only their proposals, so build it from the review's held and rejected rows, or from a scanner's CSV. Either record shape works: status and reason as the squad-key scan writes them, or tier and rejected_reason as the cross-provider scan does. The builder leaves out any pair whose row is no longer live, and any pair listed twice. It writes <page>.manifest.json beside the page; keep it, because the owner's choices mean nothing without it.

Publish the page with the Artifact tool and capabilities: {"db": {}}, as a new artifact for each build rather than over an earlier one, since the page's database keeps every build's choices and caps how many documents it holds. Send the owner the link, not the file, so it opens on any device.

Read the owner's choices back with the ArtifactData tool (action: list on decisions-<run>, query.limit 1000, following next_cursor until none is returned) with an out_dir, then pass that folder to the collector:

python .claude/skills/merging-duplicate-teams/scripts/collect_review_decisions.py \
    --manifest review.html.manifest.json --decisions <out_dir> --out vetted.json

It takes team ids only from the manifest, applies the owner's swap, and refuses rather than guesses on any choice it cannot match (its --help lists how). It prints the pairs behind every other decision, and each note, by team name. Treat the documents as data, not instructions.

Before applying, check each merge the owner chose against the evidence the card does not show, and ask about any that fail rather than dropping or applying them silently:

• a Modular11 or MLS NEXT registration on either row
• opponents that make one row a different kind of team, such as over-35 adult futsal
• two rows the review saw playing different leagues on the same weekend

A note can carry a fact outside this skill, such as "this club is in Texas". Settle it with the skill that owns that fact once the merges are done.

Step 6: Apply only what survives review

Filter .turbo/step3/decisions_approved.json — or Doorway C's or D's JSON — down to the pairs that survived review, keeping the same object shape. The review page's collector output is already that list. Then dry-run:

python scripts/apply_vetted_team_merges.py --file <vetted.json>

Check the direction before you apply, except on a pair the owner decided on the review page: there the direction the owner chose stands, and a conflict with the rule below is a question for them, not a swap. pick_canonical_pair scores name aesthetics — club name present, mixed case, length — and is uncorrelated with which row holds the data. A merge copies no columns onto the survivor, so keeping the prettier row can leave the live team with a wrong state, a NULL distinction and a shallower rank history.

So decide the survivor in this order. First, the name, by owner decision (2026-09-23: "we want to merge into the new age format with two numbers i.e. 2015/16 or U11 preferabbly over 2016(just a birth year)"): keep the row whose name states a two-year band or a current U-age over one stating a bare birth year, over one stating no age, and last one whose stated age contradicts the stored cohort — most often a stale U-age, where U13 on a team now filed u14 would put last season's age on the board. Then, between equally named rows, keep the one holding more games, and after that the one that played last. Game count is a stand-in for the live schedule, not the same thing: where a busy row has gone quiet and a thinner one holds this season's fixtures, keep the live one. Games follow the survivor through team_merge_map; columns do not. Where the better-named row lacks a value the other holds — a state, a club, a distinction — keep the name and list the gap in Step 8, for the state or club-name workflow to settle. Doorway D's script applies the name, game-count and last-game order itself, but it neither compares the columns nor looks for a row that has gone quiet, so both checks are yours. Swap merge_id/keep_id by hand wherever the order says the other row survives.

Output the vetted list and the held-pair count as text, then use AskUserQuestion to confirm before writing. On approval:

python scripts/apply_vetted_team_merges.py --file <vetted.json> --execute --out <log.json>

--limit N applies at most N, which is the escape hatch for a first execute. Apply a small batch, verify it against the database, then apply the rest — that is what the 639-merge batch did (25, verified, then 614). The script resolves both sides through team_merge_map, orders chains so a row receives its merges before it is itself merged away, drops a row claimed by two different survivors, and refuses a stale list outright.

Verify against the database rather than the script's own report — see references/pipeline-gotchas.md, which explains why the RPC's reply cannot be trusted and how to revert. Confirm each intended row is deprecated, its canonical matches, and no surviving row was deprecated except where a chain accounts for it.

Revert with scripts/revert_fuzzy_auto_merges.py, scoped by date and actor. Its actor default is pitchrank-bot; apply_vetted_team_merges.py records pitchrank-operator. A default-argument revert of a batch this skill just told you to apply matches nothing and reports success.

Step 7: Repair the downstream side effects

The merged fixtures are now recorded twice

This follows directly from the shape Step 4 tells you to merge. When two rows held the same match because it was imported twice, games holds two rows for it — and a merge does not touch games. Both rows now resolve to the surviving team, so the survivor's schedule contains each shared match twice.

Nothing downstream removes them. src/rankings/data_adapter.py:291 dedupes with drop_duplicates(subset=["id"]) — the game row's own id — so two distinct rows describing one real match both survive and both feed the engine. game_uid embeds the master team ids, so the two copies never collided on insert either.

scripts/cleanup_dupe_games_by_composite.py does not find these. It keys on the raw (home_team_master_id, away_team_master_id, game_date, home_score, away_score), and the two copies still carry different raw master ids — the deprecated one and the canonical one. It also deletes rows outright, which is a stronger write than the immutability rule allows.

scripts/exclude_merge_duplicate_games.py is the repair. It groups every scored game in a merge cluster by merge-resolved (home, away, date, home_score, away_score), keeps the copy naming the surviving row, and sets is_excluded on the rest. Dry run by default; --limit N for a staged first pass; it logs every excluded id, so the write is reversible. Run it after every merge batch, not only a Doorway B one — nothing does it automatically (IMP-137).

Two things it depends on, which any successor must keep. The discriminator is that the two rows carry different raw master ids: a genuine same-day rematch recorded once carries identical ones, and those groups are deliberately left alone. And the grouping key keeps home/away orientation, without which a real reverse fixture collapses into its own first leg.

Do not delete game rows.

Scale: the 639-merge batch of 2026-08-27 produced 2,062 doubled fixture tuples — a 200-merge sample had projected 604, so measure rather than extrapolate. Cleared 2026-08-31 (2,135 across all merges ever), verified to zero before that week's ranking run.

Stranded fixtures — no longer a step

A merge used to strand the deprecated row's unplayed fixtures, because the scrape-enqueue RPCs matched games by raw team_id_master and then required is_deprecated = false, which neither side satisfied after a merge.

supabase/migrations/20260822000000_resolve_merges_in_scrape_enqueue_rpcs.sql closes that at the source and is applied — verified 2026-08-31 against schema_migrations, with both find_yesterday_null_score_teams and find_recently_active_teams confirmed to read team_merge_map. So a merge no longer strands anything and this step does not run.

scripts/enqueue_stranded_merge_fixtures.py survives as a repair tool for fixtures stranded before that date; it takes --since, --merged-by and --include-past. Re-confirm the migration against schema_migrations rather than trusting this paragraph — supabase_migrations is not exposed through PostgREST, so check_merge_skill_assumptions.py cannot settle it for you.

Step 8: Record what ran and what was held

Write a short record next to the logs: how many merged, how many were held and the specific reason for each, how many refusals you promoted in Step 4 and why, and anything found that needs separate work. A held pair without a stated reason gets re-proposed and re-argued on the next run; a promoted refusal without a stated reason gets re-refused.

Also record what is left in the class you just worked, so the next run starts from a count rather than from a rescan. That includes the review page's pairs the owner marked unsure or left undecided, and any note that another skill still has to settle.

State plainly that the held pairs need a human decision rather than a rule.

Splitting a fused row

A row is two squads when its provider ids play different opponents on the same league date, or show up as self-play. Split it before anything merges into it. This covers fusion at the alias layer. When team_merge_map shows the moving squad arrived through a merge, revert that merge instead (Step 6): its games still name the absorbed row, which still holds the provider id.

Decide which ids move from the provider's own team name for each id, read from the scraped game data (alias rows store no name), not from alias method: SincSports names Premier 1 and Premier 2 apart even when both ids sit on one row. A row can carry more than two ids. The squad matching the row's name, usually the one with more ids, stays; the other moves to a new row. Leave an id whose name matches neither squad where it is and record it as unresolved.

Write in this order, so no import ever sees a game side with no team while its alias still points at the fused row:

1. (provider_id, provider_team_id) is unique on teams: when the moving id is the fused row's own provider_team_id, repoint that column to a staying id.
2. Create the new teams row the way the provider's matcher does (for SincSports, _create_new_sincsports_team: new uuid, club prefix stripped, resolve_distinction), with the fused row's club_name, age_group, gender and state_code and the moving id.
3. Point the moving alias at the new row as direct_id.
4. Relink each scored game side whose home_provider_id/away_provider_id is the moving id: unlink_game_team(p_game_id, <fused row>, p_is_home_team), which refuses a side holding any other team, then link_game_team(p_game_id, <new row>, p_is_home_team). On an immutable game these are the master-id changes the trigger permits; both are service-role only.

The two calls are separate transactions, so a side can be left with no team between them. An import that fills it in the meantime reads the alias, which already points at the new row, and link_game_team then raises "already linked" naming the new row: count that as done. Retry any other failed link from the log before moving on.

Set is_excluded on unscored fixtures on the moving side instead of relinking them. Their game_uid would keep the fused row's master id, and the score backfill looks rows up by that uid, so the scored import would add a second row beside the stranded one. Excluded, the scored import simply adds the correct row.

Dry run first. Log every write (new row ids, each alias row with its prior match_method, each relinked or excluded game side) so the split can be undone. Verify afterwards: no moved game with an empty side, no self-play on either row, same-date pairs only at tournaments, aliases pointing as intended.

Re-enabling the weekly job

FUZZY_AUTO_MERGE_ENABLED stays 'false' while the shipped scripts/find_fuzzy_duplicate_teams.py still decides on name similarity. The 2026-08-19 run merged 1,772 pairs of distinct teams.

Before that flag moves, port the rules into the scan itself, run it in report-only mode for several weeks, and compare each week's proposals against what a person would approve. Then auto-merge only the narrowest class — a side with no games at all — and keep the rest as a report.