id (uuid, PK)

work_email (unique)

full_name

manager_id (FK -> profile.id)

location_id (FK)

timezone_id (FK)

time_commitment_id (FK)

status (enum)

created_at, updated_at

one source of truth for people; leaders/managers are profiles

id (uuid, PK)

title, description

leader_profile_id (FK)

opportunity_type_id (FK)

location_id (FK), timezone_id (FK)

time_commitment_id (FK)

start_date, end_date, deadline_to_apply

status (enum)

created_at, updated_at

posting owned by a project lead profile

id (uuid, PK)

profile_id (FK)

opportunity_id (FK)

lead_status (enum)

manager_status (enum)

overall_status (enum)

applied_at

created_at, updated_at

unique(profile_id, opportunity_id)

prevents duplicate apply; drives approvals and emails

id (PK)

name (unique)

normalized skills

profile_id (FK)

skill_id (FK)

proficiency (enum)

unique(profile_id, skill_id)

enables shared vs missing + proficiency scoring

opportunity_id (FK)

skill_id (FK)

min_proficiency (enum)

unique(opportunity_id, skill_id)

defines required skills for a posting

id (PK)

name (unique)

normalized passions

id (PK)

label (unique)

hours_per_week (int)

numeric hours support ≥ check

id (PK)

name (unique)

region

region can back a soft location rule

id (PK)

iana_name (unique)

utc_offset

can support ± offset rules later

id (PK)

name (unique)

paired with passions for 30% of score

id (PK)

name (unique)

colleague, project_lead, admin

profile_id (FK)

role_id (FK)

unique(profile_id, role_id)

a person can have multiple roles

id (PK)

actor_profile_id (FK)

type

entity

entity_id

payload (json)

created_at

audit of applied/status_changed/email_sent

profile_id (FK)

passion_id (FK)

unique(profile_id, passion_id)

aligns to opportunity_type in scoring