Automated Species Synonym Detection and Merging #257

Description

Create automated system to detect species synonyms across external databases and suggest merging duplicate species entries.

Background

Species names change over time due to:

• Taxonomic revisions: Species reclassified to different genus
• Synonymization: Multiple names for same organism
• Nomenclatural changes: Spelling corrections, priority rules
• Common errors: Capitalization, spacing, diacritics

Current Problem: We have 41 potential duplicate species (see `SPECIES_DUPLICATES_REVIEW.md`)

Proposed Solution

Use external databases to detect when different names refer to same species:

1. Cross-Reference External IDs

Strategy: If two species have same GBIF/WoRMS/FishBase ID, they're duplicates

Example:

Species A: "Danio kerri" → GBIF ID: 2361388
Species B: "Danio Kerri" → GBIF ID: 2361388
→ Same species, merge them

2. Synonym Lookup

Strategy: Query external databases for synonyms of each species

Databases to Check:

• GBIF: GET /species/{id}/synonyms
• WoRMS: GET /AphiaSynonymsByAphiaID/{id}
• FishBase: Synonyms table in DuckDB

Example:

Species A: "Poecilia reticulata"
Synonyms from GBIF: ["Lebistes reticulatus", "Poecilia reticulatus"]

Species B: "Lebistes reticulatus" (in our database)
→ Species B is synonym of Species A, merge them

3. Fuzzy Matching

Strategy: Detect near-matches that likely represent same species

Detection:

• Levenshtein distance < 2
• Same genus + similar species epithet
• Capitalization differences only

Examples:

"Danio kerri" vs "Danio Kerri"        → Merge
"Neocaridina davidi" vs "N. davidi"   → Merge
"Poecilia reticulata" vs "P. reticulata" → Merge

Implementation

Phase 1: Detection Script

scripts/detect-species-synonyms.ts

Output:
- List of potential duplicates with confidence scores
- External database evidence (shared IDs, synonym lists)
- Suggested merge actions
- CSV report for admin review

Phase 2: Admin Review Interface

• Display detected synonyms in admin UI
• Show evidence from external databases
• Allow manual approval/rejection
• Preserve submission history for both species

Phase 3: Merge Script

scripts/merge-species.ts --keep-id=123 --merge-id=456

Actions:
1. Move all submissions from merge-id to keep-id
2. Preserve breeder history and points
3. Add synonym to species_synonyms table (new)
4. Mark merged species as inactive
5. Create audit log entry

New Database Table

CREATE TABLE species_synonyms (
  id INTEGER PRIMARY KEY,
  group_id INTEGER NOT NULL,        -- The accepted species
  synonym_name TEXT NOT NULL,        -- The alternate name
  source TEXT,                       -- 'gbif', 'worms', 'manual'
  FOREIGN KEY (group_id) REFERENCES species_groups(id)
);

Tasks

• Create detection script using external database APIs
• Implement fuzzy matching algorithm
• Generate confidence scores for matches
• Create CSV report for admin review
• Design admin UI for review/approval
• Create `species_synonyms` table migration
• Implement merge script with data preservation
• Test merge preserves all submissions and points
• Create audit logging for merges
• Update documentation

Acceptance Criteria

• Detection script identifies all duplicates in `SPECIES_DUPLICATES_REVIEW.md`
• External database evidence provided for each match
• Admin can review and approve merges via UI
• Merge script preserves all breeder data and points
• Synonyms stored in database for future reference
• Audit trail created for all merges
• Documentation updated

Safety Considerations

• NO automatic merging - Always require admin approval
• Data preservation - Never delete submissions or breeder records
• Reversibility - Keep synonym mappings in case of mistakes
• Audit trail - Log all merge operations with timestamps

Priority

Low - Important for data quality but requires careful manual review.

Related Files

• `SPECIES_DUPLICATES_REVIEW.md` - List of 41 potential duplicates
• `SPECIES_DUPLICATES.csv` - Raw duplicate data
• `scripts/find-duplicate-species.ts` - Current duplicate detection script

Related to #240 (External Data Sources Integration) and #253 (Data Quality Improvements)