This guide explains how the Mulm platform manages species names, synonyms, and the canonical naming system for fish, plants, invertebrates, and corals.

The species names system solves critical problems in aquarium taxonomy: the same species often has multiple names.

Challenges addressed:

• 🐟 Multiple common names (e.g., "Guppy", "Fancy Guppy", "Millions Fish")
• 🔬 Multiple scientific names (synonyms, reclassifications, misspellings)
• 📚 Need for standardized reference (canonical names)
• 🔍 Search must find species regardless of name variant used
• 📊 Statistics should group all variants together
• 🔄 Common and scientific names aren't always one-to-one paired

Solution: Split schema with three tables: one for canonical species data, and separate tables for common names and scientific name variants.

1. Database Structure
2. Split Schema Design
3. How It Works
4. Canonical Names
5. Name Variants
6. Linking Submissions
7. Search Functionality
8. Examples
9. Admin Workflows
10. Migration from Legacy Schema

erDiagram
    species_name_group ||--o{ species_common_name : "has many"
    species_name_group ||--o{ species_scientific_name : "has many"
    species_common_name ||--o{ submissions : "used in"
    species_scientific_name ||--o{ submissions : "used in"

    species_name_group {
        INTEGER group_id PK
        TEXT program_class
        TEXT species_type
        TEXT canonical_genus
        TEXT canonical_species_name
        INTEGER base_points
        INTEGER is_cares_species
    }

    species_common_name {
        INTEGER common_name_id PK
        INTEGER group_id FK
        TEXT common_name
    }

    species_scientific_name {
        INTEGER scientific_name_id PK
        INTEGER group_id FK
        TEXT scientific_name
    }

    submissions {
        INTEGER id PK
        INTEGER common_name_id FK
        INTEGER scientific_name_id FK
        TEXT species_common_name
        TEXT species_latin_name
    }

Stores canonical (authoritative) species data:

CREATE TABLE species_name_group (
  group_id INTEGER PRIMARY KEY AUTOINCREMENT,
  program_class TEXT NOT NULL,              -- BAP program class
  species_type TEXT NOT NULL,               -- 'Fish', 'Plant', 'Invert', 'Coral'
  canonical_genus TEXT NOT NULL,            -- Authoritative genus
  canonical_species_name TEXT NOT NULL,     -- Authoritative species
  base_points INTEGER DEFAULT NULL,         -- Points for breeding
  is_cares_species INTEGER DEFAULT 0,       -- CARES conservation flag
  iucn_redlist_category TEXT DEFAULT NULL,  -- IUCN status (VU, EN, CR, etc.)
  -- Additional metadata fields...
  UNIQUE (canonical_genus, canonical_species_name)
);

Each group represents ONE biological species.

Example rows:

Stores common name variants (many-to-one with species_name_group):

CREATE TABLE species_common_name (
  common_name_id INTEGER PRIMARY KEY AUTOINCREMENT,
  group_id INTEGER NOT NULL,
  common_name TEXT NOT NULL,
  FOREIGN KEY (group_id) REFERENCES species_name_group(group_id) ON DELETE CASCADE,
  UNIQUE (group_id, common_name)
);

Each row represents ONE common name variant.

Example rows:

All three "Guppy" variants point to same group_id=1 (same species).

Stores scientific name variants (many-to-one with species_name_group):

CREATE TABLE species_scientific_name (
  scientific_name_id INTEGER PRIMARY KEY AUTOINCREMENT,
  group_id INTEGER NOT NULL,
  scientific_name TEXT NOT NULL,
  FOREIGN KEY (group_id) REFERENCES species_name_group(group_id) ON DELETE CASCADE,
  UNIQUE (group_id, scientific_name)
);

Each row represents ONE scientific name variant.

Example rows:

Both "Poecilia reticulata" and "Lebistes reticulatus" point to group_id=1 (same species, different names).

The old schema forced one-to-one pairing of common names with scientific names:

-- OLD SCHEMA (deprecated)
CREATE TABLE species_name (
  name_id INTEGER PRIMARY KEY,
  group_id INTEGER,
  common_name TEXT,
  scientific_name TEXT,
  UNIQUE (common_name, scientific_name)  -- Forced pairing!
);

Problems with pairing:

• ❌ One species might have 10 common names but 1 scientific name → Creates 10 duplicate rows
• ❌ One species might have 1 common name but 5 scientific synonyms → Inefficient
• ❌ Can't add common name without also adding scientific name
• ❌ Searching returns duplicate rows

New split schema solves this:

-- NEW SCHEMA (current)
-- Common names stored separately
CREATE TABLE species_common_name (
  common_name_id INTEGER PRIMARY KEY,
  group_id INTEGER,
  common_name TEXT
);

-- Scientific names stored separately
CREATE TABLE species_scientific_name (
  scientific_name_id INTEGER PRIMARY KEY,
  group_id INTEGER,
  scientific_name TEXT
);

Benefits:

• ✅ Common and scientific names are independent
• ✅ No duplicate data
• ✅ Can have any ratio (5 common : 1 scientific, or 1 common : 5 scientific)
• ✅ Simpler to add/edit individual names
• ✅ More accurate representation of real taxonomy

Case 1: Many common names, one scientific

Xiphophorus maculatus (Southern Platyfish):

• 1 Scientific name: Xiphophorus maculatus
• 8+ Common names: Platy, Southern Platy, Moonfish, Mickey Mouse Platy, etc.

Old schema: 8 rows (7 duplicates of scientific name) New schema: 1 + 8 + 1 = 10 rows total (no duplicates)

Case 2: One common name, many scientific synonyms

Bronze Cory:

• 1 Common name: Bronze Corydoras
• 3+ Scientific names: Corydoras aeneus, Corydoras venezuelanus, Corydoras schultzei

Old schema: 3 rows (3 duplicates of common name) New schema: 1 + 1 + 3 = 5 rows total (no duplicates)

Case 3: Many-to-many (complex)

African Cichlid species with regional names:

• 3 Common names: Lake Malawi Cichlid, Malawi Mbuna, Peacock Cichlid
• 2 Scientific names: Current name + obsolete synonym

Old schema: 3 × 2 = 6 rows (cross product, lots of duplication) New schema: 1 + 3 + 2 = 6 rows total (but no duplication within tables)

A species group represents a single biological species with its canonical (standardized) scientific name and all metadata.

Think of it as:

• The "true" scientific name according to current taxonomy
• The standardized reference all other names point to
• Central hub for all species data (points, CARES status, IUCN data)
• Immutable identifier (group_id never changes)

Example:

Species Group #1066:
  Canonical: Puntius conchonius
  Type: Fish
  Class: Barbs and Rasboras
  Base Points: 10
  CARES Species: No
  IUCN Status: Least Concern

Common names are informal names used in the aquarium hobby.

Types of common names:

• Standard common names: "Guppy", "Bronze Corydoras"
• Trade names: "Fancy Guppy", "Neon Tetra"
• Regional names: "Millions Fish" (Caribbean), "Rainbow Fish" (South America)
• Color variant names: "Mickey Mouse Platy", "Tuxedo Guppy"

Example:

Common Name #1:
  common_name_id: 1
  group_id: 1066  →  Puntius conchonius
  common_name: "Rosy Barb"

Common Name #2:
  common_name_id: 2
  group_id: 1066  →  Puntius conchonius
  common_name: "Red Barb"

Both point to the same species group, but are stored independently.

Scientific names are formal taxonomic names.

Types of scientific names:

• Current accepted name: The taxonomically valid name
• Historical synonyms: Old names before reclassification
• Invalid synonyms: Names that were never accepted
• Misspellings: Common misspellings for search purposes

Example:

Scientific Name #1:
  scientific_name_id: 1
  group_id: 1066  →  Puntius conchonius
  scientific_name: "Puntius conchonius"  (current accepted)

Scientific Name #2:
  scientific_name_id: 2
  group_id: 1066  →  Puntius conchonius
  scientific_name: "Barbus conchonius"  (historical synonym)

Both point to the same species group.

The canonical name is the standardized, authoritative scientific name for a species according to current taxonomy.

Components:

• Canonical Genus: e.g., "Puntius"
• Canonical Species: e.g., "conchonius"
• Full Canonical Name: "Puntius conchonius"

Problem without canonical names:

Member A submits: "Guppy (Poecilia reticulata)"
Member B submits: "Fancy Guppy (Poecilia reticulata)"
Member C submits: "Guppy (Lebistes reticulatus)"  ← Old scientific name

Question: How many unique species were bred?
Answer without canonical: 3 (wrong!)
Answer with canonical: 1 (correct!)

With canonical names:

1. Admin approves Member A's submission
2. Admin enters canonical name: Poecilia reticulata
3. System creates species_name_group (or uses existing)
4. System creates entries in species_common_name and species_scientific_name
5. Future submissions link to same group regardless of name variant

Admin guidelines:

1. Use current accepted taxonomy - Check authoritative sources
2. Use binomial nomenclature - Genus + species (no subspecies)
3. Be consistent - Once set, don't change unless taxonomy officially changes

Helpful resources:

• Fish: FishBase, Catalog of Fishes
• Plants: IPNI, World Flora Online
• Invertebrates/Corals: WoRMS

During submission approval, the system automatically:

1. Creates/finds the species group by canonical name
2. Adds the common name to species_common_name (if new)
3. Adds the scientific name to species_scientific_name (if new)
4. Links submission to both via common_name_id and scientific_name_id

Example approval flow:

// Member submits: "Rosy Barb (Puntius conchonius)"
// Admin enters canonical: Puntius conchonius

// Step 1: Create/find species group
INSERT INTO species_name_group (program_class, species_type, canonical_genus, canonical_species_name)
VALUES ('Barbs and Rasboras', 'Fish', 'Puntius', 'conchonius')
ON CONFLICT (canonical_genus, canonical_species_name)
DO UPDATE SET group_id = group_id
RETURNING group_id;
// Returns: group_id = 1066

// Step 2: Add common name
INSERT INTO species_common_name (group_id, common_name)
VALUES (1066, 'Rosy Barb')
ON CONFLICT (group_id, common_name)
DO UPDATE SET common_name = common_name
RETURNING common_name_id;
// Returns: common_name_id = 5001

// Step 3: Add scientific name
INSERT INTO species_scientific_name (group_id, scientific_name)
VALUES (1066, 'Puntius conchonius')
ON CONFLICT (group_id, scientific_name)
DO UPDATE SET scientific_name = scientific_name
RETURNING scientific_name_id;
// Returns: scientific_name_id = 5001

// Step 4: Link submission
UPDATE submissions
SET common_name_id = 5001,
    scientific_name_id = 5001
WHERE id = 123;

The split schema allows any ratio of common to scientific names:

Scenario 1: Many common, one scientific

Species: Poecilia reticulata
  Common names (5): Guppy, Fancy Guppy, Millions Fish, Rainbow Fish, Endler's Guppy
  Scientific names (1): Poecilia reticulata

Database:
  species_name_group: 1 row
  species_common_name: 5 rows
  species_scientific_name: 1 row

Scenario 2: One common, many scientific

Species: Corydoras aeneus complex
  Common names (1): Bronze Corydoras
  Scientific names (3): Corydoras aeneus, Corydoras venezuelanus, Corydoras schultzei

Database:
  species_name_group: 1 row
  species_common_name: 1 row
  species_scientific_name: 3 rows

Scenario 3: No names yet

Species: Newly added with only canonical name
  Common names (0): (none added yet)
  Scientific names (1): [canonical name auto-added]

Database:
  species_name_group: 1 row
  species_common_name: 0 rows
  species_scientific_name: 1 row

Submissions link to both the common name and scientific name used:

-- Submissions table (relevant fields)
CREATE TABLE submissions (
  id INTEGER PRIMARY KEY,
  -- Text fields: preserve what member entered
  species_common_name TEXT NOT NULL,
  species_latin_name TEXT NOT NULL,

  -- FK fields: link to catalog (set during approval)
  common_name_id INTEGER REFERENCES species_common_name(common_name_id) ON DELETE SET NULL,
  scientific_name_id INTEGER REFERENCES species_scientific_name(scientific_name_id) ON DELETE SET NULL
);

Text fields (species_common_name, species_latin_name):

• ✅ Preserve exactly what member wrote
• ✅ Historical accuracy (name might change in catalog)
• ✅ Display on submission page

FK fields (common_name_id, scientific_name_id):

• ✅ Link to canonical species (via group_id)
• ✅ Enable searching/filtering by species
• ✅ Calculate statistics across all name variants
• ✅ Apply point values from species_name_group

Example submission:

-- Submission record
id: 123
member_id: 5
species_common_name: "Fancy Guppy"        -- What member typed
species_latin_name: "Poecilia reticulata" -- What member typed
common_name_id: 2                         -- Links to "Fancy Guppy" in catalog
scientific_name_id: 1                     -- Links to "Poecilia reticulata" in catalog

-- Can find canonical name via either FK:
SELECT sng.canonical_genus, sng.canonical_species_name
FROM submissions s
JOIN species_common_name cn ON s.common_name_id = cn.common_name_id
JOIN species_name_group sng ON cn.group_id = sng.group_id
WHERE s.id = 123;

-- Result: Poecilia reticulata (group_id = 1)

Searches both common and scientific names independently:

-- User types: "rosy"
SELECT DISTINCT
  cn.common_name_id as name_id,
  'common' as name_type,
  cn.common_name,
  sng.canonical_genus || ' ' || sng.canonical_species_name as scientific_name,
  sng.group_id,
  sng.program_class,
  sng.species_type
FROM species_common_name cn
JOIN species_name_group sng ON cn.group_id = sng.group_id
WHERE LOWER(cn.common_name) LIKE '%rosy%'

UNION

SELECT DISTINCT
  sn.scientific_name_id as name_id,
  'scientific' as name_type,
  '' as common_name,
  sn.scientific_name,
  sng.group_id,
  sng.program_class,
  sng.species_type
FROM species_scientific_name sn
JOIN species_name_group sng ON sn.group_id = sng.group_id
WHERE LOWER(sn.scientific_name) LIKE '%rosy%'

ORDER BY common_name, scientific_name
LIMIT 10;

Returns:

[
  {
    "name_id": 5001,
    "name_type": "common",
    "common_name": "Rosy Barb",
    "scientific_name": "Puntius conchonius",
    "group_id": 1066,
    "program_class": "Barbs and Rasboras",
    "species_type": "Fish"
  }
]

Groups results by species_name_group:

SELECT
  sng.group_id,
  sng.canonical_genus,
  sng.canonical_species_name,
  sng.program_class,
  sng.species_type,
  sng.base_points,
  sng.is_cares_species,
  GROUP_CONCAT(DISTINCT cn.common_name) as common_names,
  GROUP_CONCAT(DISTINCT sn.scientific_name) as scientific_names,
  COUNT(DISTINCT sub.id) as total_breeds,
  COUNT(DISTINCT sub.member_id) as total_breeders
FROM species_name_group sng
LEFT JOIN species_common_name cn ON sng.group_id = cn.group_id
LEFT JOIN species_scientific_name sn ON sng.group_id = sn.group_id
LEFT JOIN submissions sub ON (
  sub.common_name_id = cn.common_name_id OR
  sub.scientific_name_id = sn.scientific_name_id
) AND sub.approved_on IS NOT NULL
WHERE sng.species_type = 'Fish'
GROUP BY sng.group_id
ORDER BY sng.canonical_genus, sng.canonical_species_name;

Returns one row per species with comma-separated name lists.

Species: Guppy (Poecilia reticulata)

Database structure:

species_name_group:
  group_id: 1
  canonical_genus: Poecilia
  canonical_species_name: reticulata
  program_class: Livebearers
  species_type: Fish
  base_points: 10

species_common_name:
  1 | 1 | Guppy
  2 | 1 | Fancy Guppy
  3 | 1 | Millions Fish

species_scientific_name:
  1 | 1 | Poecilia reticulata
  2 | 1 | Lebistes reticulatus  (historical synonym)

Submissions:

• Member A: "Guppy" / "Poecilia reticulata" → common_name_id=1, scientific_name_id=1
• Member B: "Fancy Guppy" / "Poecilia reticulata" → common_name_id=2, scientific_name_id=1
• Member C: "Guppy" / "Lebistes reticulatus" → common_name_id=1, scientific_name_id=2

All three submissions count toward Poecilia reticulata statistics (group_id=1).

Species: Bronze Cory (complex taxonomic history)

Database structure:

species_name_group:
  group_id: 2
  canonical_genus: Corydoras
  canonical_species_name: aeneus
  program_class: Catfish
  species_type: Fish
  base_points: 15

species_common_name:
  4 | 2 | Bronze Cory
  5 | 2 | Bronze Corydoras
  6 | 2 | Metallic Cory

species_scientific_name:
  3 | 2 | Corydoras aeneus
  4 | 2 | Corydoras venezuelanus  (some populations reclassified)
  5 | 2 | Corydoras schultzei     (some populations reclassified)

Why this structure?

• Recent research split C. aeneus into multiple species
• Until members specify exact population, all variants grouped together
• Common names don't change (same fish, different scientific names)

Species: Newly imported species without common name

Database structure:

species_name_group:
  group_id: 500
  canonical_genus: Apistogramma
  canonical_species_name: nijsseni

species_common_name:
  (empty - no common names added yet)

species_scientific_name:
  250 | 500 | Apistogramma nijsseni

Valid state: Species can exist with only scientific names.

Species: Xiphophorus maculatus (highly variable)

Database structure:

species_name_group:
  group_id: 10
  canonical_genus: Xiphophorus
  canonical_species_name: maculatus

species_common_name:
  20 | 10 | Platy
  21 | 10 | Southern Platy
  22 | 10 | Moonfish
  23 | 10 | Mickey Mouse Platy
  24 | 10 | Sunset Platy

species_scientific_name:
  15 | 10 | Xiphophorus maculatus
  16 | 10 | Platypoecilus maculatus  (historical)

Flexibility: 5 common names × 2 scientific names, but stored as 1 + 5 + 2 = 8 rows (not 10).

Scenario: First time anyone in club bred Puntius conchonius

Steps:

1. Admin reviews submission:

   Species Common: "Rosy Barb"
   Species Latin: "Puntius conchonius"
   

2. Admin enters canonical name on approval form:

   Canonical Genus: Puntius
   Canonical Species: conchonius
   

3. System executes:

   -- Create species group
   INSERT INTO species_name_group (
     program_class, species_type, canonical_genus, canonical_species_name
   ) VALUES ('Barbs and Rasboras', 'Fish', 'Puntius', 'conchonius')
   RETURNING group_id;
   -- Returns: 1066
   
   -- Add common name
   INSERT INTO species_common_name (group_id, common_name)
   VALUES (1066, 'Rosy Barb')
   RETURNING common_name_id;
   -- Returns: 5001
   
   -- Add scientific name
   INSERT INTO species_scientific_name (group_id, scientific_name)
   VALUES (1066, 'Puntius conchonius')
   RETURNING scientific_name_id;
   -- Returns: 5001
   
   -- Link submission
   UPDATE submissions
   SET common_name_id = 5001, scientific_name_id = 5001
   WHERE id = 123;

4. Species now in catalog, searchable by either name

Scenario: Member submits "Red Barb (Puntius conchonius)"

Steps:

1. Admin recognizes same species

2. Admin enters same canonical name:

   Canonical Genus: Puntius
   Canonical Species: conchonius
   

3. System executes:

   -- Try to insert species_name_group (will find existing)
   INSERT INTO species_name_group (...)
   VALUES ('Barbs and Rasboras', 'Fish', 'Puntius', 'conchonius')
   ON CONFLICT (canonical_genus, canonical_species_name)
   DO UPDATE SET group_id = group_id
   RETURNING group_id;
   -- Returns: 1066 (existing group)
   
   -- Add new common name variant
   INSERT INTO species_common_name (group_id, common_name)
   VALUES (1066, 'Red Barb')
   RETURNING common_name_id;
   -- Returns: 5002 (new common name)
   
   -- Scientific name already exists, reuse
   INSERT INTO species_scientific_name (group_id, scientific_name)
   VALUES (1066, 'Puntius conchonius')
   ON CONFLICT (group_id, scientific_name)
   DO UPDATE SET scientific_name = scientific_name
   RETURNING scientific_name_id;
   -- Returns: 5001 (existing scientific name)
   
   -- Link submission
   UPDATE submissions
   SET common_name_id = 5002,  -- New common name
       scientific_name_id = 5001   -- Existing scientific name
   WHERE id = 124;

Result: New common name variant added to existing species group.

Scenario: Member submits "Rosy Barb (Barbus conchonius)" - using old genus

Steps:

1. Admin recognizes this is Puntius conchonius (modern taxonomy)

2. Admin enters modern canonical name:

   Canonical Genus: Puntius
   Canonical Species: conchonius
   

3. System executes:

   -- Finds existing species group
   -- group_id = 1066
   
   -- Common name already exists
   -- common_name_id = 5001
   
   -- Add historical scientific name synonym
   INSERT INTO species_scientific_name (group_id, scientific_name)
   VALUES (1066, 'Barbus conchonius')
   RETURNING scientific_name_id;
   -- Returns: 5002 (new scientific name variant)
   
   -- Link submission
   UPDATE submissions
   SET common_name_id = 5001,  -- Existing common name
       scientific_name_id = 5002   -- New scientific name
   WHERE id = 125;

Result: Historical scientific name now searchable, links to modern species.

SELECT common_name
FROM species_common_name
WHERE group_id = 1066
ORDER BY common_name;

SELECT scientific_name
FROM species_scientific_name
WHERE group_id = 1066
ORDER BY scientific_name;

SELECT
  sng.canonical_genus,
  sng.canonical_species_name
FROM submissions s
JOIN species_common_name cn ON s.common_name_id = cn.common_name_id
JOIN species_name_group sng ON cn.group_id = sng.group_id
WHERE s.id = 123;

SELECT
  sng.canonical_genus,
  sng.canonical_species_name
FROM submissions s
JOIN species_scientific_name sn ON s.scientific_name_id = sn.scientific_name_id
JOIN species_name_group sng ON sn.group_id = sng.group_id
WHERE s.id = 123;

SELECT DISTINCT s.*
FROM submissions s
LEFT JOIN species_common_name cn ON s.common_name_id = cn.common_name_id
LEFT JOIN species_scientific_name sn ON s.scientific_name_id = sn.scientific_name_id
WHERE (cn.group_id = 1066 OR sn.group_id = 1066)
  AND s.approved_on IS NOT NULL
ORDER BY s.approved_on DESC;

-- Search for "barb"
SELECT DISTINCT
  sng.group_id,
  sng.canonical_genus,
  sng.canonical_species_name,
  sng.program_class,
  sng.species_type
FROM species_name_group sng
LEFT JOIN species_common_name cn ON sng.group_id = cn.group_id
LEFT JOIN species_scientific_name sn ON sng.group_id = sn.group_id
WHERE LOWER(cn.common_name) LIKE '%barb%'
   OR LOWER(sn.scientific_name) LIKE '%barb%'
   OR LOWER(sng.canonical_genus || ' ' || sng.canonical_species_name) LIKE '%barb%'
ORDER BY sng.canonical_genus, sng.canonical_species_name;

SELECT
  sng.canonical_genus || ' ' || sng.canonical_species_name as species,
  COUNT(DISTINCT s.id) as total_submissions,
  COUNT(DISTINCT s.member_id) as unique_breeders
FROM species_name_group sng
LEFT JOIN species_common_name cn ON sng.group_id = cn.group_id
LEFT JOIN species_scientific_name sn ON sng.group_id = sn.group_id
LEFT JOIN submissions s ON (
  s.common_name_id = cn.common_name_id OR
  s.scientific_name_id = sn.scientific_name_id
) AND s.approved_on IS NOT NULL
WHERE sng.group_id = 1066
GROUP BY sng.group_id;

Old Schema (pre-migration 025):

species_name_group:      -- Canonical species data
species_name:            -- Forced pairs of (common_name, scientific_name)

New Schema (post-migration 030):

species_name_group:         -- Canonical species data (enhanced)
species_common_name:        -- Common names only (independent)
species_scientific_name:    -- Scientific names only (independent)

Migration process:

1. Migration 025: Created species_common_name table
2. Migration 026: Created species_scientific_name table
3. Migration 027: Populated split tables from old species_name data
4. Migration 028: Added common_name_id and scientific_name_id to submissions
5. Migration 029: Linked submissions to split tables
6. Migration 030: BREAKING - Dropped legacy species_name table

For code that used the old schema, backward compatibility wrappers exist in src/db/species.ts:

// Legacy wrapper - returns cross-product view
export async function getSynonymsForGroup(groupId: number): Promise<SpeciesSynonym[]>

// Legacy wrapper - adds to both tables
export async function addSynonym(groupId: number, commonName: string, scientificName: string): Promise<number>

// Legacy wrapper - updates common name only
export async function updateSynonym(nameId: number, updates: {...}): Promise<number>

// Legacy wrapper - deletes common name only
export async function deleteSynonym(nameId: number, force?: boolean): Promise<number>

⚠️ Use new functions for new code:

// New split-schema functions (recommended)
export async function getCommonNamesForGroup(groupId: number)
export async function getScientificNamesForGroup(groupId: number)
export async function addCommonName(groupId: number, commonName: string)
export async function addScientificName(groupId: number, scientificName: string)
export async function updateCommonName(commonNameId: number, newName: string)
export async function updateScientificName(scientificNameId: number, newName: string)
export async function deleteCommonName(commonNameId: number)
export async function deleteScientificName(scientificNameId: number)

✅ DO:

• Use authoritative taxonomy sources for canonical names
• Accept common misspellings as variants (improves search)
• Add all known common names for better discoverability
• Add historical scientific synonyms for completeness
• Be consistent with canonical naming

❌ DON'T:

• Create duplicate species groups
• Use common names as canonical (use scientific)
• Delete species groups (breaks foreign keys)
• Pair common and scientific names manually (they're independent)

✅ DO:

• Use getCommonNamesForGroup() and getScientificNamesForGroup() for new code
• Join through both species_common_name AND species_scientific_name for complete coverage
• Use group_id for grouping statistics
• Search both common and scientific name tables

❌ DON'T:

• Assume one-to-one pairing of common and scientific names
• Query only one name table (will miss variants)
• Use legacy getSynonymsForGroup() for new code (use split functions)
• Delete name variants without checking submission links

• Database Schema - Complete schema with ER diagrams
• Species MCP Server - API for managing species programmatically
• Admin Species Management - Admin interface guide
• IUCN Red List Integration - Conservation status sync

**Last Updated: November 2025 (Migration 030 - Split Schema)