erDiagram
    csip_record ||--|| referral : "was created by"
    csip_record ||--o{ audit_event : "changes tracked by"
    referral ||--o{ contributory_factor : "has contributory factors"
    referral ||--o| investigation : "optionally has an"
    referral ||--o| safer_custody_screening_outcome : "optionally has a"
    investigation ||--o{ interview : "can involve interviews with"
    referral ||--o| decisions_and_actions : "optionally has"
    csip_record ||--o| plan : "if decision is progress to CSIP"
    plan ||--o{ identified_need : "has one or more"
    plan ||--o{ review : "regularly reviewed"
    review ||--o{ attendee : "attended by"
    referral }o--|| incident_type : "incident categorised by"
    referral }o--|| incident_location : "incident occurred in"
    referral }o--|| area_of_work : "referer categorised by"
    referral }o--|| incident_involvement : "referred person was involved in the indecent as"
    safer_custody_screening_outcome }o--|| outcome : "outcome categorised by"
    interview }o--|| interviewee_role : "role of interviewee"
    decisions_and_actions }o--|| outcome : "outcome categorised by"
    decisions_and_actions }o--|| role : "role of person signing off the referral decision"
    contributory_factor }o--|| contributory_factor_type : "categorised by"

    csip_record {
        bigserial record_id PK "not null"
        uuid record_uuid "not null unique. Mapped to OFFENDER_CSIP_REPORTS"
        varchar(10) prison_number "not null. The prison number of the person the CSIP record is for"
        varchar(6) prison_code_when_recorded "The prison code where the person was resident at the time the CSIP record was created. Maps to OFFENDER_CSIP_REPORTS.AGY_LOC_ID"
        varchar(10) log_number "User entered identifier for the CSIP record. Defaults to the prison code. Maps to OFFENDER_CSIP_REPORTS.CSIP_SEQ"
        timestamp created_at "not null. The date and time the CSIP record was created. Maps to OFFENDER_CSIP_REPORTS.CREATE_DATETIME"
        varchar(32) created_by "not null. The username of the user who created the CSIP record. Maps to OFFENDER_CSIP_REPORTS.CREATE_USER_ID"
        varchar(255) created_by_display_name "not null. The displayable name of the user who created the CSIP record. Not mapped"
        timestamp last_modified_at "The date and time the CSIP record was last modified. Maps to OFFENDER_CSIP_REPORTS.MODIFY_DATETIME"
        varchar(32) last_modified_by "The username of the user who last modified the CSIP record. Maps to OFFENDER_CSIP_REPORTS.MODIFY_USER_ID"
        varchar(255) last_modified_by_display_name "The displayable name of the user who last modified the CSIP record. Not mapped"
    }

    referral {
        bigserial record_id PK "not null. Shared primary key with csip_record"
        date incident_date "not null. The date the incident that motivated the CSIP referral occurred. Maps to RFR_INCIDENT_DATE"
        time incident_time "The time the incident that motivated the CSIP referral occurred. Maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_TIME"
        int incident_type_id FK "not null. FK to incident_type. The type of incident that motivated the CSIP referral. Maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_TYPE"
        int incident_location_id FK "not null. FK to incident_location. The location of the incident that motivated the CSIP referral. Maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_LOCATION"
        varchar(240) referred_by "not null. The person reporting the incident or creating the CSIP referral. Maps to OFFENDER_CSIP_REPORTS.RFR_REPORTED_BY"
        int referer_area_of_work_id FK "not null. FK to area_of_work. The area of work of the person reporting the incident or creating the CSIP referral. Maps to OFFENDER_CSIP_REPORTS.RFR_CSIP_FUNCTION"
        date referral_date "not null. The date the CSIP referral was raised. Maps to OFFENDER_CSIP_REPORTS.RFR_DATE_REPORTED"
        text referral_summary "Summary of the CSIP referral. Maps to OFFENDER_CSIP_REPORTS.RFR_COMMENT? 4000 character limit"
        boolean proactive_referral "Was this referral proactive or preventative. Maps to OFFENDER_CSIP_REPORTS.RFR_PROACTIVE_RESPONSE where the default is 'N'"
        boolean staff_assaulted "Were any members of staff assaulted in the incident. Maps to OFFENDER_CSIP_REPORTS.RFR_STAFF_ASSAULTED where the default is 'N'"
        text assaulted_staff_name "Name or names of assaulted members of staff if any. Maps to OFFENDER_CSIP_REPORTS.RFR_STAFF_NAME. 1000 character limit"
        date release_date "The release date of the person in prison. Can be planned released date or the date they were released. Maps to OFFENDER_CSIP_REPORTS.CDR_RELEASE_DATE. Likely set by another screen in NOMIS"
        int incident_involvement_id FK "not null. FK to incident_involvement. The type of involvement the person had in the incident. Maps to OFFENDER_CSIP_REPORTS.CDR_INVOLVEMENT"
        text description_of_concern "not null. The reasons why there is cause for concern. Maps to OFFENDER_CSIP_REPORTS.CDR_CONCERN_DESCRIPTION"
        text known_reasons "not null. The reasons already known about the causes of the incident or motivation for CSIP referral. Maps to OFFENDER_CSIP_REPORTS.INV_KNOWN_REASONS"
        text other_information "Any other information about the incident or reasons for CSIP referral. Maps to OFFENDER_CSIP_REPORTS.CDR_OTHER_INFORMATION"
        boolean safer_custody_team_informed "Records whether the safer custody team been informed. Maps to OFFENDER_CSIP_REPORTS.CDR_SENT_DENT where the default is 'N'"
        boolean referral_complete "Is the referral complete. Maps to OFFENDER_CSIP_REPORTS.REFERRAL_COMPLETE_FLAG where the default is 'N'"
        varchar(32) referral_completed_by "The username of the user who completed the CSIP referral. Maps to OFFENDER_CSIP_REPORTS.REFERRAL_COMPLETED_BY"
        varchar(255) referral_completed_by_display_name "The displayable name of the user who completed the CSIP referral. Not mapped"
        date referral_completed_date "The date the referral was completed. Maps to OFFENDER_CSIP_REPORTS.REFERRAL_COMPLETED_DATE"
    }

    contributory_factor {
        bigserial contributory_factor_id PK "not null"
        uuid contributory_factor_uuid "not null unique. Mapped to OFFENDER_CSIP_FACTORS"
        bigserial record_id FK "not null"
        bigserial contributory_factor_type_id FK "not null. FK to contributory_factor_type. The type of contributory factor to the incident or motivation for CSIP referral. Mapped to OFFENDER_CSIP_FACTORS.CSIP_FACTOR"
        text comment "Additional information about the contributory factor to the incident or motivation for CSIP referral. Maps to OFFENDER_CSIP_FACTORS.COMMENTS"
        timestamp created_at "not null. The date and time the contributory factor was created. Maps to OFFENDER_CSIP_FACTORS.CREATE_DATETIME"
        varchar(32) created_by "not null. The username of the user who created the contributory factor. Maps to OFFENDER_CSIP_FACTORS.CREATE_USER_ID"
        varchar(255) created_by_display_name "not null. The displayable name of the user who created the contributory factor. Not Mapped"
        timestamp last_modified_at "The date and time the contributory factor was last modified. Maps to OFFENDER_CSIP_FACTORS.MODIFY_DATETIME"
        varchar(32) last_modified_by "The username of the user who last modified the contributory factor. Maps to OFFENDER_CSIP_FACTORS.MODIFY_USER_ID"
        varchar(255) last_modified_by_display_name "The displayable name of the user who last modified the contributory factor. Not mapped"
    }

    safer_custody_screening_outcome {
        bigserial record_id PK "not null. Shared primary key with csip_record"
        int outcome_id "not null. FK to outcome. The type of outcome of the safer custody screening. Maps to OFFENDER_CSIP_REPORTS.CDR_OUTCOME"
        varchar(100) recorded_by "not null. The username of the user who recorded the safer custody screening outcome. Maps to OFFENDER_CSIP_REPORTS.CDR_OUTCOME_RECORDED_BY"
        varchar(255) recorded_by_display_name "not null. The displayable name of the user who recorded the safer custody screening outcome. Not mapped"
        date date "not null. The date of the safer custody screening outcome. Maps to OFFENDER_CSIP_REPORTS.CDR_OUTCOME_DATE"
        text reason_for_decision "not null. The reasons for the safer custody screening outcome decision. Maps to OFFENDER_CSIP_REPORTS.CDR_DECISION_REASON. 4000 character limit"
    }

    investigation {
        bigserial record_id PK "not null. Shared primary key with csip_record"
        text staff_involved "The names of the staff involved in the investigation. Maps to OFFENDER_CSIP_REPORTS.INV_STAFF_INVOLVED. 4000 character limit"
        text evidence_secured "Any evidence that was secured as part of the investigation. Maps to OFFENDER_CSIP_REPORTS.INV_EVIDENCE_SECURED. 4000 character limit"
        text occurrence_reason "The reasons why the incident occurred. Maps to OFFENDER_CSIP_REPORTS.INV_OCCURRENCE_REASON. 4000 character limit"
        text persons_usual_behaviour "The normal behaviour of the person in prison. Maps to OFFENDER_CSIP_REPORTS.INV_USUAL_BEHAVIOUR. 4000 character limit"
        text persons_trigger "What triggers the person in prison has that could have motivated the incident. Maps to OFFENDER_CSIP_REPORTS.INV_PERSONS_TRIGGER. 4000 character limit"
        text protective_factors "Any protective factors. Maps to OFFENDER_CSIP_REPORTS.INV_PROTECTIVE_FACTORS. 4000 character limit"
    }

    interview {
        bigserial interview_id PK "not null"
        uuid interview_uuid "not null unique. Mapped to OFFENDER_CSIP_INTVW"
        bigserial record_id FK "not null"
        varchar(100) interviewee "not null. Name of the person being interviewed. Maps to OFFENDER_CSIP_INTVW.CSIP_INTERVIEWEE"
        date interview_date "not null. The date the interview took place. Maps to OFFENDER_CSIP_INTVW.INTVW_DATE"
        int interviewee_role_id FK "not null. FK to interviewee_role. What role the interviewee played in the incident or referral. Maps to OFFENDER_CSIP_INTVW.INTVW_ROLE"
        text interview_text "Information provided in interview. Maps to OFFENDER_CSIP_INTVW.COMMENTS. 4000 character limit"
        timestamp created_at "not null. The date and time the interview was created. Maps to OFFENDER_CSIP_INTVW.CREATE_DATETIME"
        varchar(32) created_by "not null. The username of the user who created the interview. Maps to OFFENDER_CSIP_INTVW.CREATE_USER_ID"
        varchar(255) created_by_display_name "not null. The displayable name of the user who created the interview. Not mapped"
        timestamp last_modified_at "The date and time the interview was last modified. Maps to OFFENDER_CSIP_INTVW.MODIFY_DATETIME"
        varchar(32) last_modified_by "The username of the user who last modified the interview. Maps to OFFENDER_CSIP_INTVW.MODIFY_USER_ID"
        varchar(255) last_modified_by_display_name "The displayable name of the user who last modified the interview. Not mapped"
    }

    decisions_and_actions {
        bigserial record_id PK "not null. Shared primary key with csip_record"
        text decision_conclusion "The conclusion of the referral and reasons for the outcome decision. Maps to OFFENDER_CSIP_REPORTS.INV_CONCLUSION. 4000 character limit"
        int decision_outcome_id "not null. FK to outcome. The outcome decision for the referral. Maps to OFFENDER_CSIP_REPORTS.INV_OUTCOME"
        int decision_outcome_signed_off_by_role_id "FK to role. The role of the person making the outcome decision. Maps to OFFENDER_CSIP_REPORTS.INV_SIGNED_OFF_BY"
        varchar(100) decision_outcome_recorded_by "The username of the user who recorded the outcome decision. Maps to OFFENDER_CSIP_REPORTS.INV_OUTCOME_RECORDED_BY"
        varchar(255) decision_outcome_recorded_by_display_name "The displayable name of the user who recorded the outcome decision. Not mapped"
        date decision_outcome_date "The date the outcome decision was made. Maps to OFFENDER_CSIP_REPORTS.INV_OUTCOME_DATE"
        text next_steps "The next steps that should be taken following the outcome decision. Maps to OFFENDER_CSIP_REPORTS.INV_NEXT_STEPS. 4000 character limit"
        boolean action_open_csip_alert "If a recommended action is to open a CSIP alert. Maps to OFFENDER_CSIP_REPORTS.OPEN_CSIP_ALERT where the default is 'N'"
        boolean action_non_associations_updated "If a recommended action is to update the non associations for the person. Maps to OFFENDER_CSIP_REPORTS.INV_NON_ASSOC_UPDATED where the default is 'N'"
        boolean action_observation_book "If a recommended action is to add the person to the observation book. Maps to OFFENDER_CSIP_REPORTS.INV_OBSERVATION_BOOK where the default is 'N'"
        boolean action_unit_or_cell_move "If a recommended action is to move the person. Maps to OFFENDER_CSIP_REPORTS.INV_MOVE where the default is 'N'"
        boolean action_csra_or_rsra_review "If a recommended action is to perform a CSRA/RSRA review. Maps to OFFENDER_CSIP_REPORTS.INV_REVIEW where the default is 'N'"
        boolean action_service_referral "If a recommended action is to refer the person to another service. Maps to OFFENDER_CSIP_REPORTS.INV_SERVICE_REFERRAL where the default is 'N'"
        boolean action_sim_referral "If a recommended action is to refer the person to SIM. Maps to OFFENDER_CSIP_REPORTS.INV_SIM_REFERRAL where the default is 'N'"
        text action_other "Any other actions that are recommended to be considered. Maps to OFFENDER_CSIP_REPORTS.INV_OTHER. 4000 character limit"
    }

    plan {
        bigserial record_id PK "not null. Shared primary key with csip_record"
        varchar(100) case_manager "not null. The case manager assigned to the CSIP plan. Maps to OFFENDER_CSIP_REPORTS.CASE_MANAGER"
        varchar(240) reason_for_plan "not null. The reasons motivating the creation of a CSIP plan. Maps to OFFENDER_CSIP_REPORTS.REASON"
        date first_case_review_date "not null. The first date the CSIP plan should be reviewed. Maps to OFFENDER_CSIP_REPORTS.CASE_REV_DATE"
    }

    identified_need {
        bigserial identified_need_id PK "not null"
        uuid identified_need_uuid "not null unique. Mapped to OFFENDER_CSIP_PLANS"
        bigserial record_id FK "not null"
        text identified_need "not null. Details of the need identified in the CSIP plan. Maps to OFFENDER_CSIP_PLANS.IDENTIFIED_NEED. 1000 character limit"
        varchar(100) need_identified_by "not null. The name of the person who identified the need. Maps to OFFENDER_CSIP_PLANS.BY_WHOM. Who identified the need (free text)"
        date created_date "not null. The date the need was identified. Maps to OFFENDER_CSIP_PLANS.CREATE_DATE. Date the need was identified"
        date target_date "not null. The target date the need should be progressed or resolved. Maps to OFFENDER_CSIP_PLANS.TARGET_DATE. Target date of the identified need"
        date closed_date "The date the identified need was resolved or closed. Maps to OFFENDER_CSIP_PLANS.CLOSED_DATE"
        text intervention "not null. The planned intervention for the identified need. Maps to OFFENDER_CSIP_PLANS.INTERVENTION. 4000 character limit"
        text progression "How the plan to address the identified need. is progressing. Maps to OFFENDER_CSIP_PLANS.PROGRESSION. 4000 character limit"
        timestamp created_at "not null. The date and time the identified need was created. Maps to OFFENDER_CSIP_PLANS.CREATE_DATETIME"
        varchar(32) created_by "not null. The username of the user who created the identified need. Maps to OFFENDER_CSIP_PLANS.CREATE_USER_ID"
        varchar(255) created_by_display_name "not null. The displayable name of the user who created the identified need. Not mapped"
        timestamp last_modified_at "The date and time the identified need was last modified. Maps to OFFENDER_CSIP_PLANS.MODIFY_DATETIME"
        varchar(32) last_modified_by "The username of the user who last modified the identified need. Maps to OFFENDER_CSIP_PLANS.MODIFY_USER_ID"
        varchar(255) last_modified_by_display_name "The displayable name of the user who last modified the identified need. Not mapped"
    }

    review {
        bigserial review_id PK "not null"
        uuid review_uuid "not null unique. Mapped to OFFENDER_CSIP_REVIEWS"
        bigserial record_id FK "not null"
        int review_sequence "not null. The review number. Maps to OFFENDER_CSIP_REVIEWS.REVIEW_SEQ"
        date review_date "The date of the review. Maps to OFFENDER_CSIP_REVIEWS.CREATE_DATE"
        varchar(32) recorded_by "not null. The username of the person who recorded the review. Maps to OFFENDER_CSIP_REVIEWS.CREATE_USER"
        varchar(255) recorded_by_display_name "not null. The displayable name of the person who recorded the review. Not mapped"
        date next_review_date "The date of the next review. Maps to OFFENDER_CSIP_REVIEWS.NEXT_REVIEW_DATE"
        boolean action_responsible_people_informed "If an action following the review was to inform people responsible for the person in prison. Maps to OFFENDER_CSIP_REVIEWS.PEOPLE_INFORMED where the default is 'N'"
        boolean action_csip_updated "If an action following the review was to update the CSIP plan. Maps to OFFENDER_CSIP_REVIEWS.CSIP_UPDATED where the default is 'N'"
        boolean action_remain_on_csip "If the outcome decision following the review was the person should remain on the CSIP plan. Maps to OFFENDER_CSIP_REVIEWS.REMAIN_ON_CSIP where the default is 'N'"
        boolean action_case_note "If an action following the review was to add a CSIP case note. Maps to OFFENDER_CSIP_REVIEWS.CASE_NOTE where the default is 'N'"
        boolean action_close_csip "If the outcome decision following the review was closing the CSIP plan. Maps to OFFENDER_CSIP_REVIEWS.CLOSE_CSIP where the default is 'N'"
        date csip_closed_date "The date the CSIP plan was closed following a review outcome decision to close it. Maps to OFFENDER_CSIP_REVIEWS.CLOSE_DATE"
        text summary "Additional information about the review. Maps to OFFENDER_CSIP_REVIEWS.SUMMARY. 4000 character limit"
        timestamp created_at "not null. The date and time the review was created. Maps to OFFENDER_CSIP_REVIEWS.CREATE_DATETIME"
        varchar(32) created_by "not null. The username of the user who created the review. Maps to OFFENDER_CSIP_REVIEWS.CREATE_USER_ID"
        varchar(255) created_by_display_name "not null. The displayable name of the user who created the review. Not mapped"
        timestamp last_modified_at "The date and time the review was last modified. Maps to OFFENDER_CSIP_REVIEWS.MODIFY_DATETIME"
        varchar(32) last_modified_by "The username of the user who last modified the review. Maps to OFFENDER_CSIP_REVIEWS.MODIFY_USER_ID"
        varchar(255) last_modified_by_display_name "The displayable name of the user who last modified the review. Not mapped"
    }

    attendee {
        bigserial attendee_id PK "not null"
        uuid attendee_uuid "not null unique. Mapped to OFFENDER_CSIP_ATTENDEES"
        bigserial review_id FK "not null"
        varchar(100) name "not null. Name of review attendee/contributor. Maps to OFFENDER_CSIP_ATTENDEES.ATTENDEE_NAME"
        varchar(50) role "not null. Role of review attendee/contributor. Maps to OFFENDER_CSIP_ATTENDEES.ATTENDEE_ROLE"
        boolean attended "If the person attended the review. Indicates that they were a contributor if false. Maps to OFFENDER_CSIP_ATTENDEES.ATTENDED where the default is 'N'"
        text contribution "Description of attendee contribution. Maps to OFFENDER_CSIP_ATTENDEES.CONTRIBUTION. 4000 character limit"
        timestamp created_at "not null. The date and time the attendee was created. Maps to OFFENDER_CSIP_ATTENDEES.CREATE_DATETIME"
        varchar(32) created_by "not null. The username of the user who created the attendee. Maps to OFFENDER_CSIP_ATTENDEES.CREATE_USER_ID"
        varchar(255) created_by_display_name "not null. The displayable name of the user who created the attendee. Not mapped"
        timestamp last_modified_at "The date and time the attendee was last modified. Maps to OFFENDER_CSIP_ATTENDEES.MODIFY_DATETIME"
        varchar(32) last_modified_by "The username of the user who last modified the attendee. Maps to OFFENDER_CSIP_ATTENDEES.MODIFY_USER_ID"
        varchar(255) last_modified_by_display_name "The displayable name of the user who last modified the attendee. Not mapped"
    }

    audit_event {
        long id PK "not null"
        long csip_record_id FK "not null"
        varchar(40) action "not null"
        text description "not null"
        timestamp actioned_at "not null"
        varchar(32) actioned_by "not null"
        varchar(255) actioned_by_captured_name "not null"
        boolean recordAffected
        boolean referralAffected
        boolean contributoryFactorAffected
        boolean saferCustodyScreeningOutcomeAffected
        boolean investigationAffected
        boolean interviewAffected
        boolean decisionsAndActionsAffected
        boolean planAffected
        boolean identifiedNeedAffected
        boolean reviewAffected
        boolean attendeeAffected
    }

    incident_type {
        bigserial incident_type_id PK "not null"
        varchar(12) code "not null unique. The NOMIS reference data code for the CSIP_TYP domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    incident_location {
        bigserial incident_location_id PK "not null"
        varchar(40) code "not null unique. The NOMIS reference data code for the CSIP_LOC domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    area_of_work {
        bigserial area_of_work_id PK "not null"
        varchar(40) code "not null unique. The NOMIS reference data code for the CSIP_FUNC domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    incident_involvement {
        bigserial incident_involvement_id PK "not null"
        varchar(12) code "not null unique. The NOMIS reference data code for the CSIP_INV domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    contributory_factor_type {
        bigserial contributory_factor_type_id PK "not null"
        varchar(12) code "not null unique. The NOMIS reference data code for the CSIP_FAC domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    outcome {
        bigserial outcome_id PK "not null"
        varchar(12) code "not null unique. The NOMIS reference data code for the CSIP_OUT domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    interviewee_role {
        bigserial interviewee_role_id PK "not null"
        varchar(12) code "not null unique. The NOMIS reference data code for the CSIP_INTVROL domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    role {
        bigserial role_id PK "not null"
        varchar(12) code "not null unique. The NOMIS reference data code for the CSIP_ROLE domain"
        varchar(40) description
        int list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp last_modified_at
        varchar(32) last_modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }
