FD Centre — Entity Relationship Diagram

erDiagram
  NATIONAL_SOCIETY ||--o{ REQUEST : "submits"
  NATIONAL_SOCIETY ||--o{ CONTACT : "has"
  NATIONAL_SOCIETY ||--o{ ENGAGEMENT : "engages in"
  REQUEST ||--o{ REQUEST_SERVICE_CATEGORY : "selects"
  SERVICE_CATEGORY ||--o{ REQUEST_SERVICE_CATEGORY : "classifies"
  CONTACT ||--o{ REQUEST : "raises"
  REQUEST ||--o| TRIAGE : "is triaged in"
  FD_CENTRE_STAFF ||--o{ TRIAGE : "officer"
  TRIAGE ||--o{ TRIAGE_SCORE : "scored by"
  TRIAGE ||--o{ APPROVAL : "signed off"
  REQUEST ||--o| RISK_ACCEPTANCE : "risk reviewed in"
  RISK_ACCEPTANCE ||--o{ RISK_SCREENING_ITEM : "assesses"
  RISK_ACCEPTANCE ||--o{ APPROVAL : "signed off"
  REQUEST ||--o| ENGAGEMENT : "becomes"
  FD_CENTRE_STAFF ||--o{ ENGAGEMENT : "leads"
  FUNDER ||--o{ ENGAGEMENT : "sponsors"
  ENGAGEMENT ||--o| ENGAGEMENT_LETTER : "contracted by"
  ENGAGEMENT_LETTER ||--o{ APPROVAL : "signed off"
  ENGAGEMENT ||--o| NEEDS_ASSESSMENT : "assessed in"
  NEEDS_ASSESSMENT ||--o{ MATURITY_DIMENSION : "diagnoses"
  NEEDS_ASSESSMENT ||--o{ PRIORITY_NEED : "ranks"
  NEEDS_ASSESSMENT ||--o{ READINESS_FACTOR : "evaluates"
  ENGAGEMENT ||--o| PLAN_OF_ACTION : "planned in"
  PLAN_OF_ACTION ||--o{ RESULTS_FRAMEWORK : "defines"
  PLAN_OF_ACTION ||--o{ POA_RISK : "tracks"
  PLAN_OF_ACTION ||--o{ BENEFIT : "expects"
  PLAN_OF_ACTION ||--o{ APPROVAL : "signed off"
  ENGAGEMENT ||--o| BUDGET : "costed in"
  BUDGET ||--o{ BUDGET_LINE : "itemised by"
  BUDGET ||--o{ APPROVAL : "signed off"
  FUNDER ||--o{ BUDGET_LINE : "funds"
  PLAN_OF_ACTION ||--o| WORKPLAN : "scheduled in"
  ENGAGEMENT ||--o{ WORKPLAN : "delivered via"
  WORKPLAN ||--o{ WORKPLAN_ACTIVITY : "contains"
  WORKPLAN ||--o{ TIME_ENTRY : "logs"
  WORKPLAN ||--o{ RESOURCE_DEPLOYMENT : "staffs"
  WORKPLAN ||--o{ ISSUE_LOG : "raises"
  CONTRACTING_MODALITY ||--o{ RESOURCE_DEPLOYMENT : "engaged under"
  CONTRACTING_MODALITY ||--o{ FD_CENTRE_STAFF : "contracts"
  ENGAGEMENT ||--o{ TECHNICAL_VISIT_REPORT : "visited in"
  TECHNICAL_VISIT_REPORT ||--o{ VISIT_ACTIVITY : "conducts"
  TECHNICAL_VISIT_REPORT ||--o{ VISIT_FINDING : "records"
  TECHNICAL_VISIT_REPORT ||--o{ VISIT_RECOMMENDATION : "recommends"
  ENGAGEMENT ||--o{ PERFORMANCE_REPORT : "reported in"
  PERFORMANCE_REPORT ||--o{ KPI_RESULT : "measures"
  PERFORMANCE_REPORT ||--o{ FINANCIAL_SUMMARY_LINE : "summarises"
  PERFORMANCE_REPORT ||--o{ PERF_RISK_ITEM : "registers"
  KPI_DEFINITION ||--o{ KPI_RESULT : "measured as"
  KPI_DEFINITION ||--o{ RESULTS_FRAMEWORK : "specifies"
  ENGAGEMENT ||--o| SATISFACTION_SURVEY : "rated in"
  SATISFACTION_SURVEY ||--o{ SURVEY_RATING : "captures"
  ENGAGEMENT ||--o| CLOSE_OUT : "closed by"
  ENGAGEMENT ||--o{ ATTACHMENT : "documented by"
  FD_CENTRE_STAFF ||--o{ APPROVAL : "approves"
  NATIONAL_SOCIETY {
    string national_society_id PK
    string ns_name
    string country
    string region
    string income_category_wb
    string nsd_priority_status
    string finance_team_size
  }
  CONTACT {
    string contact_id PK
    string national_society_id FK
    string full_name
    string title_position
    string email
    string phone
  }
  SERVICE_CATEGORY {
    string category_id PK
    string category_name
    string tier
    string service_stage
  }
  FD_CENTRE_STAFF {
    string staff_id PK
    string name
    string role
    string grade
    string staff_category
    string hardship_class
    string region
    string contracting_modality_id FK
  }
  CONTRACTING_MODALITY {
    string modality_id PK
    string modality_name
    string description
  }
  FUNDER {
    string funder_id PK
    string funder_name
    string funder_type
    string gl_code
  }
  KPI_DEFINITION {
    string kpi_def_id PK
    string kpi_name
    string kpi_type
    string formula
    string frequency
  }
  ATTACHMENT {
    string attachment_id PK
    string engagement_id FK
    string parent_type
    string file_name
    string uploaded_by
    date uploaded_date
  }
  REQUEST {
    string request_id PK
    string national_society_id FK
    string contact_id FK
    string requesting_entity_type
    string request_title
    date date_submitted
    string desired_response_timing
    string status
  }
  REQUEST_SERVICE_CATEGORY {
    string request_id FK
    string category_id FK
  }
  ENGAGEMENT {
    string engagement_id PK
    string request_id FK
    string national_society_id FK
    string lead_staff_id FK
    string funder_id FK
    string status
    string current_stage
    string complexity_band
    string funding_route
    date start_date
    date close_date
  }
  ENGAGEMENT_LETTER {
    string letter_id PK
    string engagement_id FK
    string scope_of_work
    string milestones
    string finance_terms
    date signed_date
    string status
  }
  TRIAGE {
    string triage_id PK
    string request_id FK
    string triage_officer_id FK
    date date_received
    decimal total_weighted_score
    string outcome_band
    string recommended_modality
    string decision
  }
  TRIAGE_SCORE {
    string score_id PK
    string triage_id FK
    string dimension
    int score
    decimal weight_pct
    decimal weighted_score
  }
  RISK_ACCEPTANCE {
    string risk_acceptance_id PK
    string request_id FK
    string proposed_service
    string sponsor
    string engagement_owner
    string recommended_decision
    date next_review_date
  }
  RISK_SCREENING_ITEM {
    string item_id PK
    string risk_acceptance_id FK
    string risk_domain
    int likelihood
    int impact
    string residual_band
    string owner
    boolean escalation_required
  }
  NEEDS_ASSESSMENT {
    string assessment_id PK
    string engagement_id FK
    string assessment_dates
    string assessment_team
    string problem_statement
    string root_causes
    string intervention_pathway
  }
  MATURITY_DIMENSION {
    string dimension_id PK
    string assessment_id FK
    string pillar_name
    int current_maturity
    int target_maturity
    string findings_gaps
  }
  PRIORITY_NEED {
    string need_id PK
    string assessment_id FK
    int rank
    string need_gap
    string timeframe
    string rationale
  }
  READINESS_FACTOR {
    string factor_id PK
    string assessment_id FK
    string factor
    string status
    string notes
  }
  PLAN_OF_ACTION {
    string poa_id PK
    string engagement_id FK
    string period_covered
    string intervention_lead
    string funding_route
  }
  RESULTS_FRAMEWORK {
    string rf_id PK
    string poa_id FK
    string kpi_def_id FK
    string outcome_impact
    string output_service
    string baseline
    string target
    string status
  }
  POA_RISK {
    string poa_risk_id PK
    string poa_id FK
    string risk
    string likelihood
    string impact
    string residual_risk
    string status
  }
  BENEFIT {
    string benefit_id PK
    string poa_id FK
    string benefit_name
    string benefit_level
  }
  BUDGET {
    string budget_id PK
    string engagement_id FK
    string funding_route
    string sponsor
    decimal management_fee_pct
    decimal grand_total
    decimal total_invoiced
  }
  BUDGET_LINE {
    string line_id PK
    string budget_id FK
    string funder_id FK
    string gl_code
    string line_type
    string output_service
    string rate_card_code
    string complexity_band
    decimal subtotal
    decimal subsidy_pct
    decimal invoiced_amount
  }
  WORKPLAN {
    string workplan_id PK
    string poa_id FK
    string engagement_id FK
    string reporting_period
    string version
    string engagement_lead
  }
  WORKPLAN_ACTIVITY {
    string activity_id PK
    string workplan_id FK
    string workstream_output
    string task_activity
    date start_date
    date end_date
    string status
    decimal planned_hours
    decimal actual_hours
  }
  TIME_ENTRY {
    string time_id PK
    string workplan_id FK
    string cost_centre_code
    string timesheet_category
    string billable_subsidised
    string approval_status
  }
  RESOURCE_DEPLOYMENT {
    string resource_id PK
    string workplan_id FK
    string contracting_modality_id FK
    string role
    string name
    string organisation
    string level_of_effort
  }
  ISSUE_LOG {
    string issue_id PK
    string workplan_id FK
    string issue
    string impact
    string action
    string owner
    string status
  }
  TECHNICAL_VISIT_REPORT {
    string visit_id PK
    string engagement_id FK
    string location_visited
    string mission_dates
    string visit_type
    string team_members
  }
  VISIT_ACTIVITY {
    string va_id PK
    string visit_id FK
    string activity
    string completion_status
    string notes
  }
  VISIT_FINDING {
    string finding_id PK
    string visit_id FK
    string theme
    string observations
  }
  VISIT_RECOMMENDATION {
    string rec_id PK
    string visit_id FK
    string recommendation
    string priority
    string owner
    date due_date
    string status
  }
  PERFORMANCE_REPORT {
    string report_id PK
    string engagement_id FK
    string reporting_period
    string region
    string author
    string narrative_summary
  }
  KPI_RESULT {
    string kpi_id PK
    string report_id FK
    string kpi_def_id FK
    string baseline
    string target
    string actual
    string variance
  }
  FINANCIAL_SUMMARY_LINE {
    string fin_id PK
    string report_id FK
    string category
    decimal budget
    decimal actual
    decimal forecast
    decimal variance
  }
  PERF_RISK_ITEM {
    string prisk_id PK
    string report_id FK
    string risk_issue
    string category
    string current_rating
    string trend
    string owner
  }
  SATISFACTION_SURVEY {
    string survey_id PK
    string engagement_id FK
    string respondent_role
    date date_completed
    boolean anonymous
    int nps_score
    string most_valuable
    string improvement
  }
  SURVEY_RATING {
    string rating_id PK
    string survey_id FK
    string area
    int score
  }
  CLOSE_OUT {
    string closeout_id PK
    string engagement_id FK
    string deliverables_status
    string transition_notes
    string case_study_ref
    boolean green_indicator
    date review_date
  }
  APPROVAL {
    string approval_id PK
    string staff_id FK
    string document_type
    string document_id
    string approver_role
    string governance_body
    string method
    string threshold_band
    string decision
    date sign_date
  }