arctic-species-portal/data_architecture_may2025.md

Supabase Database Schema (Arctic Species)

This document outlines the data architecture of the Arctic Species Supabase database.

Tables

The database contains the following tables:

• catch_records
• cites_listings
• cites_trade_records
• common_names
• conservation_measures
• distribution_ranges
• iucn_assessments
• profiles
• species
• species_threats
• subpopulations
• timeline_events

Table Structures

Below is a preliminary structure for each table, inferred by querying a single record.

---

species

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
scientific_name	TEXT	Scientific name of the species
common_name	TEXT	Common name of the species
kingdom	TEXT	Taxonomic kingdom
phylum	TEXT	Taxonomic phylum
class	TEXT	Taxonomic class
order_name	TEXT	Taxonomic order
family	TEXT	Taxonomic family
genus	TEXT	Taxonomic genus
species_name	TEXT	Specific epithet
authority	TEXT	Authority who named the species
sis_id	INTEGER	Species Information Service ID (IUCN)
created_at	TIMESTAMP WITH TIME ZONE	Timestamp of record creation
inaturalist_id	INTEGER	iNaturalist taxon ID (nullable)
default_image_url	TEXT	URL for a default image (nullable)
description	TEXT	General description (nullable)
habitat_description	TEXT	Description of habitat (nullable)
population_trend	TEXT	Population trend (e.g., decreasing, stable) (nullable)
population_size	TEXT	Estimated population size (nullable)
generation_length	TEXT	Generation length in years (nullable)
movement_patterns	TEXT	Description of movement patterns (nullable)
use_and_trade	TEXT	Information on use and trade (nullable)
threats_overview	TEXT	Overview of threats (nullable)
conservation_overview	TEXT	Overview of conservation efforts (nullable)

---

catch_records

Column Name	Data Type (Inferred)	Notes
id	INTEGER	Primary Key
species_id	UUID	Foreign Key referencing species.id
country	TEXT	Country where the catch was recorded
year	INTEGER	Year of the catch record
area	TEXT	Specific area of catch (nullable)
catch_total	INTEGER	Total catch amount
quota	INTEGER	Catch quota, if applicable (nullable)
source	TEXT	Source of the catch data (e.g., NAMMCO)
created_at	TIMESTAMP WITH TIME ZONE	Timestamp of record creation

---

cites_listings

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
species_id	UUID	Foreign Key referencing species.id
appendix	TEXT	CITES Appendix (e.g., I, II, III)
listing_date	DATE	Date the species was listed on the appendix
notes	TEXT	Notes regarding the CITES listing (nullable)
is_current	BOOLEAN	Indicates if the listing is current

---

cites_trade_records

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
species_id	UUID	Foreign Key referencing species.id
record_id	TEXT	Unique ID for the trade record
year	INTEGER	Year the trade occurred
appendix	TEXT	CITES Appendix at the time of trade
taxon	TEXT	Scientific name of the taxon in trade
class	TEXT	Taxonomic class
order_name	TEXT	Taxonomic order
family	TEXT	Taxonomic family
genus	TEXT	Taxonomic genus
term	TEXT	Description of the traded item (e.g., skins)
quantity	FLOAT	Quantity of the item traded
unit	TEXT	Unit of measurement for quantity (nullable)
importer	TEXT	Importing country code (ISO 2-letter)
exporter	TEXT	Exporting country code (ISO 2-letter)
origin	TEXT	Country of origin code (nullable)
purpose	TEXT	Purpose of trade code (e.g., P for Personal)
source	TEXT	Source of specimen code (e.g., W for Wild)
reporter_type	TEXT	E for Exporter, I for Importer
import_permit	TEXT	Import permit number (nullable)
export_permit	TEXT	Export permit number (nullable)
origin_permit	TEXT	Origin permit number (nullable)

---

common_names

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
species_id	UUID	Foreign Key referencing species.id
name	TEXT	Common name of the species
language	TEXT	Language code for the common name (e.g., eng)
is_main	BOOLEAN	Indicates if this is the primary common name

---

conservation_measures

Structure for this table could not be determined by querying a single record (the table might be empty or access is restricted in this manner). Further investigation or direct schema inspection is required.

---

profiles

Structure for this table could not be determined by querying a single record (the table might be empty or access is restricted in this manner). Further investigation or direct schema inspection is required.

---

distribution_ranges

Structure for this table could not be determined by querying a single record (the table might be empty or access is restricted in this manner). Further investigation or direct schema inspection is required.

---

iucn_assessments

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
species_id	UUID	Foreign Key referencing species.id
year_published	INTEGER	Year the IUCN assessment was published
is_latest	BOOLEAN	Indicates if this is the latest assessment for the species
possibly_extinct	BOOLEAN	Indicates if the species is possibly extinct
possibly_extinct_in_wild	BOOLEAN	Indicates if the species is possibly extinct in the wild
status	TEXT	IUCN Red List status code (e.g., LC, VU, EN)
url	TEXT	URL to the assessment on the IUCN Red List website
assessment_id	INTEGER	Unique ID for the assessment from IUCN
scope_code	TEXT	Code indicating the geographic scope of the assessment
scope_description	TEXT	Description of the geographic scope (e.g., Europe, Global)

---

species_threats

Structure for this table could not be determined by querying a single record (the table might be empty or access is restricted in this manner). Further investigation or direct schema inspection is required.

---

subpopulations

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
species_id	UUID	Foreign Key referencing species.id
scientific_name	TEXT	Full scientific name including subpopulation
subpopulation_name	TEXT	Name of the subpopulation
sis_id	INTEGER	Species Information Service ID (IUCN)
authority	TEXT	Authority who named the subpopulation (nullable)

---

timeline_events

Column Name	Data Type (Inferred)	Notes
id	UUID	Primary Key
species_id	UUID	Foreign Key referencing species.id
event_date	DATE	Date of the event
year	INTEGER	Year of the event (can be derived from event_date)
event_type	TEXT	Type of event (e.g., iucn_assessment, cites_listing)
title	TEXT	Title of the event
description	TEXT	Description of the event (nullable)
status	TEXT	Status associated with the event (e.g., LC, Appendix II) (nullable)
source_type	TEXT	Type of the source table (e.g., iucn_assessments) (nullable)
source_id	UUID	Foreign Key to the source record (e.g., iucn_assessments.id) (nullable)

---

Authentication (auth schema)

Supabase provides a built-in authentication system that operates within its own auth schema. This schema is separate from the public schema detailed above but is integral to managing user identities, sessions, and access control.

Key tables typically found in the auth schema include:

• users: Stores user identity information (e.g., email, phone, password hash, user metadata). Each user is assigned a unique UUID.
• sessions: Manages active user sessions.
• instances: Information about authentication instances.
• refresh_tokens: Stores refresh tokens for maintaining sessions.
• audit_log_entries: Logs significant authentication events.

While these tables are managed by Supabase, the id from auth.users is commonly used as a foreign key in public schema tables (like profiles) to link user-specific data to their authentication record. For example, a profiles table would typically have a user_id column that references auth.users.id.