Database Schema - jra3/mulm GitHub Wiki

Database Schema

This document provides a comprehensive overview of the Mulm database schema, including entity relationships, field descriptions, and design patterns.

Overview

The Mulm database uses SQLite with a migration-based schema management system. All migrations are located in db/migrations/ and run automatically on application startup.

Key Design Patterns:

Dual connection pattern (separate read-only and write connections)
Transaction wrapper for atomic operations
Auto-incrementing integer primary keys
Foreign key constraints with explicit ON DELETE behavior
Composite indexes for query optimization

Entity Relationship Diagram

erDiagram
    members ||--o{ submissions : "submits"
    members ||--o{ submissions : "approves"
    members ||--o{ submissions : "witnesses"
    members ||--o{ submissions : "denies"
    members ||--o{ awards : "earns"
    members ||--o{ tank_presets : "saves"
    members ||--o| password_account : "has"
    members ||--o| google_account : "has"
    members ||--o{ sessions : "has"
    members ||--o{ auth_codes : "has"
    members ||--o{ activity_feed : "generates"
    members ||--o{ submission_notes : "writes"

    submissions ||--o| species_common_name : "references"
    submissions ||--o| species_scientific_name : "references"
    submissions ||--o{ submission_notes : "has"
    submissions ||--o{ submission_images : "has"
    submissions ||--o{ submission_supplements : "has"

    species_common_name }o--|| species_name_group : "belongs to"
    species_scientific_name }o--|| species_name_group : "belongs to"
    species_name_group ||--o{ species_external_references : "has"
    species_name_group ||--o{ species_images : "has"

    members {
        INTEGER id PK
        TEXT contact_email UK
        TEXT display_name
        INTEGER is_admin
        TEXT fish_level
        TEXT plant_level
        TEXT coral_level
    }

    submissions {
        INTEGER id PK
        TEXT program
        DATETIME created_on
        DATETIME updated_on
        INTEGER member_id FK
        INTEGER common_name_id FK
        INTEGER scientific_name_id FK
        TEXT species_type
        TEXT species_class
        TEXT species_common_name
        TEXT species_latin_name
        TEXT water_type
        TEXT count
        DATETIME reproduction_date
        TEXT foods
        TEXT spawn_locations
        TEXT propagation_method
        TEXT tank_size
        TEXT filter_type
        TEXT water_change_volume
        TEXT water_change_frequency
        TEXT temperature
        TEXT ph
        TEXT gh
        TEXT specific_gravity
        TEXT substrate_type
        TEXT substrate_depth
        TEXT substrate_color
        TEXT light_type
        TEXT light_strength
        TEXT light_hours
        TEXT co2
        TEXT co2_description
        DATETIME submitted_on
        DATETIME approved_on
        INTEGER approved_by FK
        INTEGER points
        INTEGER article_points
        BOOLEAN first_time_species
        BOOLEAN flowered
        BOOLEAN sexual_reproduction
        INTEGER witnessed_by FK
        DATETIME witnessed_on
        TEXT witness_verification_status
        DATETIME denied_on
        INTEGER denied_by FK
        TEXT denied_reason
    }

    submission_images {
        INTEGER id PK
        INTEGER submission_id FK
        TEXT r2_key
        TEXT public_url
        INTEGER file_size
        DATETIME uploaded_at
        TEXT content_type
        INTEGER display_order
    }

    submission_supplements {
        INTEGER id PK
        INTEGER submission_id FK
        TEXT supplement_type
        TEXT supplement_regimen
        INTEGER display_order
    }

    awards {
        INTEGER member_id FK "PK"
        TEXT award_name "PK"
        TEXT award_type
        DATETIME date_awarded
    }

    tank_presets {
        INTEGER member_id FK "PK"
        TEXT preset_name "PK"
        DATETIME created_on
        DATETIME updated_on
        TEXT tank_size
        TEXT filter_type
        TEXT water_change_volume
        TEXT water_change_frequency
        TEXT temperature
        TEXT ph
        TEXT gh
        TEXT specific_gravity
        TEXT substrate_type
        TEXT substrate_depth
        TEXT substrate_color
    }

    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
        TEXT iucn_redlist_category
        TEXT iucn_population_trend
        INTEGER iucn_redlist_id
        TEXT iucn_redlist_url
    }

    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
    }

    species_external_references {
        INTEGER id PK
        INTEGER group_id FK
        TEXT reference_url
        INTEGER display_order
    }

    species_images {
        INTEGER id PK
        INTEGER group_id FK
        TEXT image_url
        INTEGER display_order
    }

    password_account {
        INTEGER member_id FK "PK"
        INTEGER N
        INTEGER r
        INTEGER p
        TEXT salt
        TEXT hash
    }

    google_account {
        TEXT google_sub PK
        TEXT google_email
        INTEGER member_id FK "UK"
    }

    sessions {
        TEXT session_id PK
        INTEGER member_id FK
        DATETIME expires_on
    }

    auth_codes {
        TEXT code PK
        INTEGER member_id FK
        TEXT purpose
        DATETIME expires_on
    }

    activity_feed {
        INTEGER id PK
        TEXT activity_type
        INTEGER member_id FK
        TEXT related_id
        TEXT activity_data
        DATETIME created_at
    }

    submission_notes {
        INTEGER id PK
        INTEGER submission_id FK
        INTEGER admin_id FK
        TEXT note_text
        DATETIME created_at
    }

Core Tables

members

Stores user accounts and program level tracking.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing member ID
contact_email	TEXT	NOT NULL, UNIQUE	Email address for contact and login
display_name	TEXT	NOT NULL	Public display name
is_admin	INTEGER	DEFAULT 0	Admin flag (0=member, 1=admin)
fish_level	TEXT	NULL	Current fish program level
plant_level	TEXT	NULL	Current plant program level
coral_level	TEXT	NULL	Current coral program level

Indexes:

idx_members_contact_email - Fast email lookups for login
idx_member_search - Typeahead search optimization

Foreign Key Behavior:

Referenced by many tables with ON DELETE CASCADE (deleting member removes all their data)
approved_by and denied_by use ON DELETE RESTRICT (cannot delete admin who approved/denied submissions)

submissions

Tracks breeding/propagation submissions for all programs (fish, plants, corals).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing submission ID
program	TEXT	NOT NULL	Program type: 'fish', 'plant', or 'coral'
created_on	DATETIME	DEFAULT CURRENT_TIMESTAMP	When draft was created
updated_on	DATETIME	DEFAULT CURRENT_TIMESTAMP	Last modification timestamp
member_id	INTEGER	FK → members(id) ON DELETE SET NULL	Submitting member
common_name_id	INTEGER	FK → species_common_name(common_name_id) ON DELETE SET NULL	Linked common name from catalog
scientific_name_id	INTEGER	FK → species_scientific_name(scientific_name_id) ON DELETE SET NULL	Linked scientific name from catalog
species_type	TEXT	NOT NULL	Species type: 'Fish', 'Plant', 'Invert', or 'Coral'
species_class	TEXT	NOT NULL	Species classification/program class
species_common_name	TEXT	NOT NULL	Common name entered by member
species_latin_name	TEXT	NOT NULL	Scientific name entered by member
water_type	TEXT	NULL	Freshwater, brackish, saltwater
count	TEXT	NULL	Number of offspring/propagations
reproduction_date	DATETIME	NULL	Date of breeding/propagation
foods	TEXT	NULL	Foods used during conditioning
spawn_locations	TEXT	NULL	Where spawning occurred
propagation_method	TEXT	NULL	Method used (plants/corals)
tank_size	TEXT	NULL	Tank dimensions or volume
filter_type	TEXT	NULL	Filtration system
water_change_volume	TEXT	NULL	Volume changed per water change
water_change_frequency	TEXT	NULL	How often water is changed
temperature	TEXT	NULL	Water temperature
ph	TEXT	NULL	pH level
gh	TEXT	NULL	General hardness
specific_gravity	TEXT	NULL	Saltwater density
substrate_type	TEXT	NULL	Substrate material
substrate_depth	TEXT	NULL	Substrate depth
substrate_color	TEXT	NULL	Substrate color
light_type	TEXT	NULL	Lighting system
light_strength	TEXT	NULL	Light intensity
light_hours	TEXT	NULL	Photoperiod duration
co2	TEXT	NULL	CO2 supplementation (yes/no)
co2_description	TEXT	NULL	CO2 system details
submitted_on	DATETIME	NULL	When submitted for approval
approved_on	DATETIME	NULL	When approved by admin
approved_by	INTEGER	FK → members(id) ON DELETE RESTRICT	Admin who approved
points	INTEGER	NULL	Points awarded
article_points	INTEGER	NULL	Bonus points for article
first_time_species	BOOLEAN	NULL	First breeding of species in club
flowered	BOOLEAN	NULL	Plant flowered (plants only)
sexual_reproduction	BOOLEAN	NULL	Sexual vs asexual (plants only)
witnessed_by	INTEGER	FK → members(id) ON DELETE SET NULL	Member who witnessed
witnessed_on	DATETIME	NULL	When witness request was sent
witness_verification_status	TEXT	CHECK ('pending', 'confirmed', 'declined')	Witness confirmation status
denied_on	DATETIME	NULL	When submission was denied
denied_by	INTEGER	FK → members(id) ON DELETE RESTRICT	Admin who denied
denied_reason	TEXT	NULL	Reason for denial

Indexes:

idx_member_id - Fast member submission lookups
idx_date_approved - Approved submissions chronological ordering
idx_submissions_witness_status - Witness queue filtering
idx_submissions_witnessed_by - Witness confirmation lookups
idx_submissions_denied_on - Denied submissions queries
idx_submissions_denied_by - Admin denial tracking
Composite witness index (migration 012) - Optimized witness queries

Submission Lifecycle:

stateDiagram-v2
    [*] --> Draft: Create
    Draft --> Submitted: Submit
    Submitted --> WitnessRequested: Request Witness
    WitnessRequested --> WitnessConfirmed: Witness Confirms
    WitnessRequested --> WitnessDeclined: Witness Declines
    WitnessConfirmed --> InReview: Ready for Review
    Submitted --> InReview: No Witness Required
    InReview --> Approved: Admin Approves
    InReview --> ChangesRequested: Admin Requests Changes
    InReview --> Denied: Admin Denies
    ChangesRequested --> InReview: Resubmit (preserves witness)
    WitnessDeclined --> Draft: Returned for more documentation
    Approved --> [*]
    Denied --> [*]

    note right of WitnessRequested
        witness_verification_status = 'pending'
        witnessed_by IS NOT NULL
    end note

    note right of WitnessConfirmed
        witness_verification_status = 'confirmed'
        Submission moves to approval queue
    end note

    note right of WitnessDeclined
        witness_verification_status = 'declined'
        User must add documentation and resubmit
    end note

    note right of ChangesRequested
        changes_requested_on IS NOT NULL
        User can edit while preserving witness data
        Witness confirmation is maintained
    end note

    note right of Approved
        approved_on IS NOT NULL
        points IS NOT NULL
    end note

    note right of Denied
        denied_on IS NOT NULL
        denied_reason IS NOT NULL
    end note

awards

Tracks specialty awards earned by members.

Column	Type	Constraints	Description
member_id	INTEGER	FK → members(id) ON DELETE CASCADE, PK	Member earning award
award_name	TEXT	NOT NULL, PK	Award name (e.g., "Catfish Specialist")
award_type	TEXT	CHECK ('species', 'meta_species', 'manual')	Award category
date_awarded	DATETIME	DEFAULT CURRENT_TIMESTAMP	When award was granted

Composite Primary Key: (member_id, award_name) - Members can only earn each award once.

Award Types:

species - Category-specific awards (e.g., "Catfish Specialist")
meta_species - Meta awards spanning categories (e.g., "Senior Specialist Award")
manual - Manually granted awards by admins

tank_presets

Saves tank parameter templates for quick submission entry.

Column	Type	Constraints	Description
member_id	INTEGER	FK → members(id) ON DELETE CASCADE, PK	Preset owner
preset_name	TEXT	NOT NULL, PK	User-defined preset name
created_on	DATETIME	DEFAULT CURRENT_TIMESTAMP	Creation timestamp
updated_on	DATETIME	DEFAULT CURRENT_TIMESTAMP	Last update timestamp
tank_size	TEXT	NULL	Tank dimensions/volume
filter_type	TEXT	NULL	Filtration system
water_change_volume	TEXT	NULL	Volume per change
water_change_frequency	TEXT	NULL	Change frequency
temperature	TEXT	NULL	Water temperature
ph	TEXT	NULL	pH level
gh	TEXT	NULL	General hardness
specific_gravity	TEXT	NULL	Saltwater density
substrate_type	TEXT	NULL	Substrate material
substrate_depth	TEXT	NULL	Substrate depth
substrate_color	TEXT	NULL	Substrate color

Composite Primary Key: (member_id, preset_name) - Preset names must be unique per member.

Usage: Members can save tank parameters and quickly load them when creating new submissions.

Species Catalog

The species catalog uses a split schema design with three tables to independently manage common names and scientific names.

species_name_group

The core species table containing canonical (authoritative) taxonomic names and metadata.

Column	Type	Constraints	Description
group_id	INTEGER	PRIMARY KEY	Auto-incrementing group ID
program_class	TEXT	NOT NULL	BAP program class (e.g., "Cichlids - New World", "Livebearers")
species_type	TEXT	NOT NULL	High-level category: 'Fish', 'Plant', 'Invert', or 'Coral'
canonical_genus	TEXT	NOT NULL	Authoritative genus name
canonical_species_name	TEXT	NOT NULL	Authoritative species epithet
base_points	INTEGER	NULL	Base points awarded for breeding this species
is_cares_species	INTEGER	DEFAULT 0	CARES conservation priority flag (0 or 1)
iucn_redlist_category	TEXT	NULL	IUCN conservation status (e.g., 'VU', 'EN', 'CR')
iucn_population_trend	TEXT	NULL	Population trend from IUCN
iucn_taxon_id	INTEGER	NULL	IUCN Red List taxon ID
iucn_last_synced_at	DATETIME	NULL	Last IUCN data sync timestamp

Unique Constraint: (canonical_genus, canonical_species_name)

Design: Each row represents one biological species with its standardized taxonomic name.

species_common_name

Common name variants for species (many-to-one with species_name_group).

Column	Type	Constraints	Description
common_name_id	INTEGER	PRIMARY KEY	Auto-incrementing common name ID
group_id	INTEGER	FK → species_name_group(group_id) ON DELETE CASCADE	Parent species group
common_name	TEXT	NOT NULL	Common name variant (e.g., "Guppy", "Fancy Guppy")

Unique Constraint: (group_id, common_name) - Each species can have each common name only once

Indexes:

idx_species_common_name_lookup - Fast search by common name
idx_species_common_name_group - List all common names for a species

Design: One species can have many common names. Each common name is independent.

species_scientific_name

Scientific name variants for species (many-to-one with species_name_group).

Column	Type	Constraints	Description
scientific_name_id	INTEGER	PRIMARY KEY	Auto-incrementing scientific name ID
group_id	INTEGER	FK → species_name_group(group_id) ON DELETE CASCADE	Parent species group
scientific_name	TEXT	NOT NULL	Scientific name variant (synonyms, reclassifications)

Unique Constraint: (group_id, scientific_name) - Each species can have each scientific name only once

Indexes:

idx_species_scientific_name_lookup - Fast search by scientific name
idx_species_scientific_name_group - List all scientific names for a species

Design: One species can have many scientific names (historical synonyms, misspellings, reclassifications).

Split Schema Benefits:

Common names and scientific names are managed independently
A species can have multiple common names with one scientific name, or vice versa
No forced pairing of common + scientific (was a limitation of old schema)
More flexible for real-world taxonomy (many scientific synonyms, one common name is common)

species_external_references

External reference URLs for species (many-to-one with species_name_group).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing reference ID
group_id	INTEGER	FK → species_name_group(group_id) ON DELETE CASCADE	Parent species group
reference_url	TEXT	NOT NULL	External reference URL (FishBase, IUCN, etc.)
display_order	INTEGER	DEFAULT 0	Order for display

Unique Constraint: (group_id, reference_url) - Each URL appears once per species

Indexes:

idx_species_references_group - List all references for a species

Design: Stores reference links to external databases and resources for each species.

species_images

Image URLs for species (many-to-one with species_name_group).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing image ID
group_id	INTEGER	FK → species_name_group(group_id) ON DELETE CASCADE	Parent species group
image_url	TEXT	NOT NULL	Image URL
display_order	INTEGER	DEFAULT 0	Order for display

Unique Constraint: (group_id, image_url) - Each image URL appears once per species

Indexes:

idx_species_images_group - List all images for a species

Design: Stores reference images for each species.

Submission-Related Tables

submission_images

Image metadata for submission photos (many-to-one with submissions).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing image ID
submission_id	INTEGER	FK → submissions(id) ON DELETE CASCADE	Parent submission
r2_key	TEXT	NOT NULL	Cloudflare R2 object key
public_url	TEXT	NOT NULL	Public CDN URL for the image
file_size	INTEGER	NOT NULL	File size in bytes
uploaded_at	DATETIME	NOT NULL	Upload timestamp
content_type	TEXT	NOT NULL	MIME type (image/jpeg, image/png, etc.)
display_order	INTEGER	DEFAULT 0	Order for display

Unique Constraint: (submission_id, r2_key) - Each image appears once per submission

Indexes:

idx_submission_images_submission - List all images for a submission

Design: Stores metadata for submission photos uploaded to Cloudflare R2. The actual image files are stored in R2; this table tracks URLs and metadata.

submission_supplements

Plant fertilizer/supplement data for submissions (many-to-one with submissions).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing supplement ID
submission_id	INTEGER	FK → submissions(id) ON DELETE CASCADE	Parent submission
supplement_type	TEXT	NOT NULL	Type of supplement/fertilizer
supplement_regimen	TEXT	NOT NULL	Dosing schedule/regimen
display_order	INTEGER	DEFAULT 0	Order for display

Unique Constraint: (submission_id, supplement_type, supplement_regimen) - Each supplement/regimen combo appears once per submission

Indexes:

idx_submission_supplements_submission - List all supplements for a submission
idx_submission_supplements_type - Search by supplement type

Design: Stores plant supplement data that was previously stored as parallel JSON arrays. The normalized structure allows proper querying and indexing.

Authentication & Security

password_account

Stores scrypt password hashes for email/password authentication.

Column	Type	Constraints	Description
member_id	INTEGER	FK → members(id) ON DELETE CASCADE, PK	Member account
N	INTEGER	NOT NULL	Scrypt CPU/memory cost parameter
r	INTEGER	NOT NULL	Scrypt block size parameter
p	INTEGER	NOT NULL	Scrypt parallelization parameter
salt	TEXT	NOT NULL	Random salt for hash
hash	TEXT	NOT NULL	Scrypt password hash

One-to-One: Each member can have at most one password account.

google_account

Links Google OAuth accounts to members.

Column	Type	Constraints	Description
google_sub	TEXT	PRIMARY KEY	Google subject identifier (unique per user)
google_email	TEXT	NULL	Google email address
member_id	INTEGER	FK → members(id) ON DELETE CASCADE, UNIQUE	Linked member account

Indexes:

idx_google_member_id - Fast member OAuth lookups

One-to-One: Each member can have at most one Google account linked.

sessions

Active user sessions (cookie-based).

Column	Type	Constraints	Description
session_id	TEXT	PRIMARY KEY	Random session identifier
member_id	INTEGER	FK → members(id) ON DELETE CASCADE	Session owner
expires_on	DATETIME	NOT NULL	Expiration timestamp

Session Management:

Sessions are checked on every request via middleware
Expired sessions are periodically cleaned up
Session ID is stored in HTTP-only cookie

auth_codes

Temporary codes for password reset and email verification.

Column	Type	Constraints	Description
code	TEXT	PRIMARY KEY	Random code (URL-safe)
member_id	INTEGER	FK → members(id) ON DELETE CASCADE	Code recipient
purpose	TEXT	NOT NULL	Code purpose: 'password_reset', 'email_verify', etc.
expires_on	DATETIME	NOT NULL	Expiration timestamp

Purposes:

password_reset - Forgot password flow
set_password - Initial password setup (invited users)

Activity & History

activity_feed

Public activity stream shown on homepage.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing activity ID
activity_type	TEXT	CHECK ('submission_approved', 'award_granted')	Event type
member_id	INTEGER	FK → members(id) ON DELETE CASCADE	Member who triggered event
related_id	TEXT	NOT NULL	submission_id or award_name
activity_data	TEXT	NULL	JSON data specific to activity type
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	Event timestamp

Indexes:

idx_activity_created_at - Chronological feed ordering (DESC)
idx_activity_type - Filter by activity type
idx_activity_member - Member activity history

Activity Types:

submission_approved - When submission gets approved (shows species, points)
award_granted - When member earns specialty award

submission_notes

Private admin notes on submissions.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY	Auto-incrementing note ID
submission_id	INTEGER	FK → submissions(id) ON DELETE CASCADE	Related submission
admin_id	INTEGER	FK → members(id) ON DELETE SET NULL	Admin who wrote note
note_text	TEXT	NOT NULL	Note content
created_at	DATETIME	DEFAULT CURRENT_TIMESTAMP	Note timestamp

Indexes:

idx_submission_notes_submission - Load notes for submission (chronological)
idx_submission_notes_admin - Track admin activity

Usage: Admins can leave notes on submissions to track communication, decisions, or context for future reviews.

Database Patterns

Foreign Key Deletion Policies

The schema uses explicit foreign key deletion policies:

Policy	Usage	Example
`ON DELETE CASCADE`	Child data should be deleted with parent	Sessions deleted when member is deleted
`ON DELETE SET NULL`	Preserve child data, null out reference	Submissions preserved when member is deleted
`ON DELETE RESTRICT`	Prevent deletion if child data exists	Cannot delete admin who approved submissions

Timestamp Tracking

Tables use consistent timestamp patterns:

created_on/created_at - When record was created (DEFAULT CURRENT_TIMESTAMP)
updated_on - When record was last modified (must be manually updated)
*_on suffixes for specific events (submitted_on, approved_on, denied_on)

JSON Storage

SQLite's limited type system leads to JSON storage for complex data:

activity_data - Activity-specific metadata
images - Array of image objects: [{key: string, url: string, size: number, uploadedAt: string}]

Text-Based Enums

SQLite doesn't have native ENUM types, so CHECK constraints enforce valid values:

witness_verification_status TEXT CHECK (witness_verification_status IN ('pending', 'confirmed', 'declined'))
award_type TEXT CHECK (award_type IN ('species', 'meta_species', 'manual'))
activity_type TEXT CHECK (activity_type IN ('submission_approved', 'award_granted'))

Migration History

Migration	Description
001-init.sql	Initial schema: members, submissions, awards, auth tables
002-add-tank-presets.sql	Tank parameter templates
003-species-name-tracking.sql	Species catalog with name groups
004-add-index-for-members-contact-email.sql	Email lookup optimization
005-add-index-for-member-search.sql	Typeahead search optimization
006-add-indexes-for-species-search.sql	Species search optimization
007-add-award-type.sql	Award type classification
008-add-activity-feed.sql	Public activity stream
009-add-witness-fields.sql	Witness verification workflow
010-backward-compatibility-witness-status.sql	Witness status migration
011-fix-approved-witness-status.sql	Witness status data fix
012-add-witness-composite-index.sql	Witness query optimization
013-add-denial-fields.sql	Submission denial tracking
014-add-submission-images.sql	Image attachment support
015-add-submission-notes.sql	Admin notes on submissions
016-add-oauth-state.sql	OAuth state tracking for security
017-add-account-lockout.sql	Failed login tracking and account lockout
018-import-fish-species-data.sql	Initial fish species import
019-add-species-type.sql	Add species_type field to species_name_group
020-extend-species-metadata.sql	Add metadata fields (points, CARES, references)
021-import-aquatic-plants.sql	Import aquatic plant species
022-mark-cares-species.sql	Mark CARES conservation species
023-set-species-points.sql	Set base point values for species
024-add-video-url.sql	Add video URL support to submissions
025-create-species-common-name-table.sql	Create species_common_name split table
026-create-species-scientific-name-table.sql	Create species_scientific_name split table
027-populate-split-name-tables.sql	Migrate data from species_name to split tables
028-add-submission-name-fks.sql	Add common_name_id and scientific_name_id to submissions
029-backfill-submission-name-references.sql	Link submissions to split tables
030-complete-split-schema-migration.sql	BREAKING: Drop legacy species_name table, complete split schema
031-add-webauthn-support.sql	Add passkey/WebAuthn authentication
032-split-cichlid-classes.sql	Split Cichlids class into Old World/New World
033-add-cares-species-bonus.sql	Add CARES species bonus points
034-add-article-link.sql	Add article link to submissions
035-add-changes-requested-fields.sql	Add "request changes" workflow fields
036-add-iucn-integration.sql	Add IUCN Red List integration fields and tables
037-add-iucn-url.sql	Add IUCN species page URL field
038-add-iucn-canonical-recommendations.sql	Add IUCN canonical name recommendations table

Critical Migration: Migration 030 dropped the legacy species_name table and completed the transition to the split schema with separate species_common_name and species_scientific_name tables.

Query Patterns

Find Pending Submissions

SELECT * FROM submissions
WHERE submitted_on IS NOT NULL
  AND approved_on IS NULL
  AND denied_on IS NULL
ORDER BY submitted_on ASC;

Find Submissions Awaiting Witness Confirmation

SELECT * FROM submissions
WHERE witnessed_by IS NOT NULL
  AND witness_verification_status = 'pending'
ORDER BY witnessed_on ASC;

Calculate Member Points (Fish Program)

SELECT SUM(points) as total_points
FROM submissions
WHERE member_id = ?
  AND program = 'fish'
  AND approved_on IS NOT NULL;

Recent Activity Feed

SELECT * FROM activity_feed
ORDER BY created_at DESC
LIMIT 20;

Member's Earned Awards

SELECT award_name, award_type, date_awarded
FROM awards
WHERE member_id = ?
ORDER BY date_awarded DESC;

Database Maintenance

Backup Procedures

Development:

sqlite3 src/database/database.db '.backup backup.db'

Production:

ssh BAP "sqlite3 /mnt/basny-data/app/database/database.db '.backup /tmp/backup_$(date +%Y%m%d_%H%M%S).db'"
scp BAP:/tmp/backup_*.db ./backups/

Integrity Checks

sqlite3 database.db 'PRAGMA integrity_check;'
sqlite3 database.db 'PRAGMA foreign_key_check;'

Vacuum and Optimization

sqlite3 database.db 'VACUUM;'
sqlite3 database.db 'ANALYZE;'

Database Schema - jra3/mulm GitHub Wiki

Database Schema

Overview

Entity Relationship Diagram

Core Tables

members

submissions

awards

tank_presets

Species Catalog

species_name_group

species_common_name

species_scientific_name

species_external_references

species_images

Submission-Related Tables

submission_images

submission_supplements

Authentication & Security

password_account

google_account

sessions

auth_codes

Activity & History

activity_feed

submission_notes

Database Patterns

Foreign Key Deletion Policies

Timestamp Tracking

JSON Storage

Text-Based Enums

Migration History

Query Patterns

Find Pending Submissions

Find Submissions Awaiting Witness Confirmation

Calculate Member Points (Fish Program)

Recent Activity Feed

Member's Earned Awards

Database Maintenance

Backup Procedures

Integrity Checks

Vacuum and Optimization

Related Documentation