Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| investigation_id | uuid | 2147483647 | null |
|
|
Shared primary key with csip_record. Uses the same value as csip_record.record_id |
|||||||||||
| staff_involved | text | 2147483647 | √ | null |
|
|
The names of the staff involved in the investigation. Maps to OFFENDER_CSIP_REPORTS.INV_STAFF_INVOLVED which has a 4000 character limit |
||||||||||
| evidence_secured | text | 2147483647 | √ | null |
|
|
Any evidence that was secured as part of the investigation. Maps to OFFENDER_CSIP_REPORTS.INV_EVIDENCE_SECURED which has a 4000 character limit |
||||||||||
| occurrence_reason | text | 2147483647 | √ | null |
|
|
The reasons why the incident or behaviour occurred. Maps to OFFENDER_CSIP_REPORTS.INV_OCCURRENCE_REASON which has a 4000 character limit |
||||||||||
| persons_usual_behaviour | text | 2147483647 | √ | null |
|
|
The normal behaviour of the person in prison. Maps to OFFENDER_CSIP_REPORTS.INV_USUAL_BEHAVIOUR which has a 4000 character limit |
||||||||||
| persons_trigger | text | 2147483647 | √ | null |
|
|
What triggers the person in prison has that could have motivated the incident. Maps to OFFENDER_CSIP_REPORTS.INV_PERSONS_TRIGGER which has a 4000 character limit |
||||||||||
| protective_factors | text | 2147483647 | √ | null |
|
|
Any protective factors. Maps to OFFENDER_CSIP_REPORTS.INV_PROTECTIVE_FACTORS which has a 4000 character limit |
||||||||||
| version | int4 | 10 | null |
|
|
Supports optimistic locking preventing unnecessary select queries for new entities |
|||||||||||
| recorded_by | varchar | 64 | √ | null |
|
|
The username of the user who recorded the investigation. New field populated from 13th of February 2025 and only via DPS |
||||||||||
| recorded_by_display_name | varchar | 255 | √ | null |
|
|
The first and last name of the user who recorded the investigation. New field populated from 13th of February 2025 and only via DPS. Does not update if their name changes |
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| investigation_pkey | Primary key | Asc | investigation_id |