referral

-1 rows


Description

The referral that caused the CSIP record to be created. Referrals are step 1 of the CSIP process

Columns

Column Type Size Nulls Auto Default Children Parents Comments
referral_id uuid 2147483647 null
contributory_factor.referral_id contributory_factor_referral_id_fkey R
contributory_factor_audit.referral_id Implied Constraint R
decision_and_actions.decision_and_actions_id decision_and_actions_decision_and_actions_id_fkey R
investigation.investigation_id investigation_investigation_id_fkey R
safer_custody_screening_outcome.safer_custody_screening_outcome_id safer_custody_screening_outco_safer_custody_screening_outc_fkey R
csip_record.record_id referral_referral_id_fkey R

Shared primary key with csip_record. Uses the same value as csip_record.record_id
incident_date date 13 null

The date the incident that motivated the CSIP referral occurred. Will be displayed on the UI as date behaviour first occurred for a proactive referral Maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_DATE
incident_time time 15,6 √ null

The time the incident that motivated the CSIP referral occurred. Will be displayed on the UI as time behaviour first occurred for a proactive referral. Maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_TIME
incident_type_id int4 10 null
reference_data.reference_data_id referral_incident_type_id_fkey R

Foreign key to reference data using the ‘INCIDENT_TYPE’ domain for the type of incident (or behavior for a proactive referral) that motivated the referral. The reference_data.code value maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_TYPE
incident_location_id int4 10 null
reference_data.reference_data_id referral_incident_location_id_fkey R

Foreign key to reference data using the ‘INCIDENT_LOCATION’ domain for the location of the incident (or behavior for a proactive referral) that motivated the referral. The reference_data.code value maps to OFFENDER_CSIP_REPORTS.RFR_INCIDENT_LOCATION
referred_by varchar 240 null

Either the display name of the user making the referral or free text if making the referral on behalf of someone else. Therefore not guaranteed to match user information. Maps to OFFENDER_CSIP_REPORTS.RFR_REPORTED_BY
referer_area_of_work_id int4 10 null
reference_data.reference_data_id referral_referer_area_of_work_id_fkey R

Foreign key to reference data using the ‘AREA_OF_WORK’ domain for the area of work of the person making the referral. The reference_data.code value maps to OFFENDER_CSIP_REPORTS.RFR_CSIP_FUNCTION
referral_date date 13 null

The date the referral was submitted. Set to today by the UI. Maps to OFFENDER_CSIP_REPORTS.RFR_DATE_REPORTED
proactive_referral bool 1 √ null

Whether the referral was proactive or the result of an incident. Maps to OFFENDER_CSIP_REPORTS.RFR_PROACTIVE_RESPONSE where the default is ‘N’
staff_assaulted bool 1 √ null

Whether any member(s) of staff assaulted in the incident. Maps to OFFENDER_CSIP_REPORTS.RFR_STAFF_ASSAULTED where the default is ‘N’
assaulted_staff_name text 2147483647 √ null

Names of any assaulted member(s) of staff. Maps to OFFENDER_CSIP_REPORTS.RFR_STAFF_NAME which has a 1000 character limit
incident_involvement_id int4 10 √ null
reference_data.reference_data_id referral_incident_involvement_id_fkey R

Foreign key to reference data using the ‘INCIDENT_INVOLVEMENT’ domain for the type of involvement the person had in the incident (or behavior for a proactive referral). The reference_data.code value maps to OFFENDER_CSIP_REPORTS.CDR_INVOLVEMENT
description_of_concern text 2147483647 √ null

The reasons why there is cause for concern. Maps to OFFENDER_CSIP_REPORTS.CDR_CONCERN_DESCRIPTION which has a 4000 character limit
known_reasons text 2147483647 √ null

The reasons already known about the causes of the incident or behaviour. Maps to OFFENDER_CSIP_REPORTS.INV_KNOWN_REASONS which has a 4000 character limit
other_information text 2147483647 √ null

Any other information about the incident or behaviour. Maps to OFFENDER_CSIP_REPORTS.CDR_OTHER_INFORMATION which has a 4000 character limit
safer_custody_team_informed varchar 12 √ null

Records whether the safer custody team been informed. Maps to OFFENDER_CSIP_REPORTS.CDR_SENT_DENT where the default is ‘N’
referral_complete bool 1 √ null

Whether the referral complete. Will be true when DPS is used to make a referral. Maps to OFFENDER_CSIP_REPORTS.REFERRAL_COMPLETE_FLAG where the default is ‘N’
referral_completed_by varchar 64 √ null

The username of the user who completed the referral. Maps to OFFENDER_CSIP_REPORTS.REFERRAL_COMPLETED_BY
referral_completed_by_display_name varchar 255 √ null

The first and last name of the user who completed the referral. Does not update if their name changes
referral_completed_date date 13 √ null

The date the referral was completed. Will be set to today when DPS is used to make a referral. Maps to OFFENDER_CSIP_REPORTS.REFERRAL_COMPLETED_DATE
version int4 10 null

Supports optimistic locking preventing unnecessary select queries for new entities

Indexes

Constraint Name Type Sort Column(s)
referral_pkey Primary key Asc referral_id
idx_referral_incident_involvement_id Performance Asc incident_involvement_id
idx_referral_incident_location_id Performance Asc incident_location_id
idx_referral_incident_type_id Performance Asc incident_type_id
idx_referral_referer_area_of_work_id Performance Asc referer_area_of_work_id
idx_referral_referral_date Performance Asc referral_date

Check Constraints

Constraint Name Constraint
safer_custody_team_informed_enum_check (((safer_custody_team_informed)::text = ANY ((ARRAY['YES'::character varying, 'NO'::character varying, 'DO_NOT_KNOW'::character varying])::text[])))

Relationships

Close relationships within degrees of separation