ER Diagrams
Domain 1: Agency & Configuration
erDiagram Agency { int id PK string name string subdomain string subscription_tier } AgencyPreferences { int id PK int agency_id FK bool has_ed_program bool has_gc_program bool has_sd_program } AgencyColors { int id PK int agency_id FK string primary_color string secondary_color string logo_s3_key } AgencyDQSettings { int id PK int agency_id FK int ed_min_age int ed_max_age int gc_min_age int gc_max_age } MatchingFilterPreferences { int id PK int agency_id FK bool show_age_filter bool show_ethnicity_filter bool show_education_filter } AgencyTeam { int id PK int agency_id FK string name } AgencyTeamAdmin { int id PK int team_id FK int admin_id FK }
Agency ||--o| AgencyPreferences : "has" Agency ||--o| AgencyColors : "has" Agency ||--o| AgencyDQSettings : "has" Agency ||--o| MatchingFilterPreferences : "has" Agency ||--o{ AgencyTeam : "has" AgencyTeam ||--o{ AgencyTeamAdmin : "has"Domain 2: Users & Admins
erDiagram Admin { int id PK string email string user_type int team_id FK bool is_active } User { int id PK string email string user_type string first_name string last_name bool is_active } NonParticipatingUser { int id PK string email int case_id FK } ThirdPartyUser { int id PK string email int case_id FK } DashboardSettings { int id PK int admin_id FK } DashboardWidget { int id PK int dashboard_settings_id FK string widget_type int position json config }
Admin ||--o| DashboardSettings : "has" DashboardSettings ||--o{ DashboardWidget : "has"Domain 3: Cases & Parties
erDiagram Case { int id PK string case_type string status string stage bool is_archived } CaseParty { int id PK int case_id FK int user_id FK string party_type } GCCase { int id PK int case_id FK } CaseDocument { int id PK int case_id FK string s3_key string doc_type string filename } CaseNote { int id PK int case_id FK int admin_id FK text body bool is_pinned } CaseLog { int id PK int case_id FK string action datetime created_at } CaseClosingDetails { int id PK int case_id FK string outcome date closed_date } CaseMilestone { int id PK int case_id FK int milestone_id FK datetime achieved_at } JourneyPhoto { int id PK int case_id FK string s3_key string caption }
Case ||--o{ CaseParty : "has parties" CaseParty }o--|| User : "is" Case ||--o| GCCase : "may have (GC only)" Case ||--o{ CaseDocument : "has" Case ||--o{ CaseNote : "has" Case ||--o{ CaseLog : "has" Case ||--o| CaseClosingDetails : "may have" Case ||--o{ CaseMilestone : "has" Case ||--o{ JourneyPhoto : "has"Domain 4: Medical Cycles
erDiagram Case { int id PK } Transfer { int id PK int case_id FK date transfer_date int embryos_transferred string outcome } Retrieval { int id PK int case_id FK date retrieval_date int eggs_retrieved } RetrievalBatch { int id PK int retrieval_id FK } SDRetrieval { int id PK int case_id FK date retrieval_date } Lab { int id PK int case_id FK string test_name string result date test_date } Pregnancy { int id PK int case_id FK date confirmed_date string status } Delivery { int id PK int case_id FK date delivery_date string outcome int babies_born }
Case ||--o{ Transfer : "has" Case ||--o{ Retrieval : "has" Retrieval ||--o{ RetrievalBatch : "has" Case ||--o{ SDRetrieval : "has" Case ||--o{ Lab : "has" Case ||--o{ Pregnancy : "has" Case ||--o{ Delivery : "has"Domain 5: Profiles
erDiagram ProfileBase { int id PK int user_id FK string profile_type bool is_approved bool is_visible } EDProfile { int id PK int user_id FK int height_in int weight_lbs string eye_color string hair_color string education_level } EDEducation { int id PK int profile_id FK string school_name string degree } EDEthnicity { int id PK int profile_id FK string ethnicity_code } EDFamilyMember { int id PK int profile_id FK string relationship string conditions } EDTestResult { int id PK int profile_id FK string test_name string result } EDProfileImage { int id PK int profile_id FK string s3_key bool is_primary } ProfileKey { int id PK int agency_id FK string profile_type string field_name bool is_visible bool is_required } UserCustomProfile { int id PK int user_id FK int section_id FK } UserCustomProfileAnswer { int id PK int custom_profile_id FK int question_id FK text answer }
ProfileBase ||--o| EDProfile : "is (for EDs)" EDProfile ||--o{ EDEducation : "has" EDProfile ||--o{ EDEthnicity : "has" EDProfile ||--o{ EDFamilyMember : "has" EDProfile ||--o{ EDTestResult : "has" EDProfile ||--o{ EDProfileImage : "has" ProfileBase ||--o{ UserCustomProfile : "extends" UserCustomProfile ||--o{ UserCustomProfileAnswer : "has"Domain 6: Matching
erDiagram Case { int id PK } MatchingQueue { int id PK int case_id FK int candidate_user_id FK string candidate_type int position string status } Favorite { int id PK int case_id FK int candidate_user_id FK string candidate_type } MatchBreakDetails { int id PK int case_id FK int candidate_user_id FK string reason_code text notes } MatchSheetTemplate { int id PK int agency_id FK string name string case_type } MatchSheetQuestion { int id PK int template_id FK string question_text string question_type int position } CaseMatchSheetAnswer { int id PK int case_id FK int question_id FK text answer }
Case ||--o{ MatchingQueue : "has" Case ||--o{ Favorite : "has" Case ||--o{ MatchBreakDetails : "has" MatchSheetTemplate ||--o{ MatchSheetQuestion : "has" Case ||--o{ CaseMatchSheetAnswer : "has"Domain 7: Workflow & Tasks
erDiagram TemplateBase { int id PK int agency_id FK string name string case_type } TemplateStage { int id PK int template_id FK string name int position } TemplateSubStage { int id PK int stage_id FK string name int position } TemplateTask { int id PK int sub_stage_id FK string name string description bool requires_document } CaseTask { int id PK int case_id FK int template_task_id FK bool is_complete int assigned_to_id FK date due_date } DateBasedToDoTemplate { int id PK int agency_id FK string trigger_event int offset_days } CaseDateBasedToDoSnippet { int id PK int case_id FK int template_id FK date trigger_date bool is_complete }
TemplateBase ||--o{ TemplateStage : "has" TemplateStage ||--o{ TemplateSubStage : "has" TemplateSubStage ||--o{ TemplateTask : "has" TemplateTask ||--o{ CaseTask : "instantiates" CaseTask }|--|| Case : "for" DateBasedToDoTemplate ||--o{ CaseDateBasedToDoSnippet : "generates" CaseDateBasedToDoSnippet }|--|| Case : "for"Domain 8: Contracts & Signatures
erDiagram HelloSignTemplate { int id PK int agency_id FK string hs_template_id string contract_type string name } HelloSignParent { int id PK int case_id FK int template_id FK string hs_signature_request_id string status } SignedContract { int id PK int hello_sign_parent_id FK string s3_key datetime signed_at } CaseContract { int id PK int case_id FK string contract_type string status }
HelloSignTemplate ||--o{ HelloSignParent : "generates" HelloSignParent ||--o| SignedContract : "results in" CaseContract }|--|| Case : "for"Domain 9: Email & Sync
erDiagram EmailSync { int id PK int admin_id FK string sync_type string status datetime last_synced_at } EmailMessage { int id PK int admin_id FK int case_id FK int contact_id FK string from_address string subject text body datetime received_at } OAuthToken { int id PK int admin_id FK string token_type text access_token text refresh_token datetime expires_at } EmailTemplate { int id PK int agency_id FK string name string subject text body }
EmailSync }|--|| Admin : "for" EmailMessage }|--o| Case : "linked to" OAuthToken }|--|| Admin : "for"