Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| review_id | uuid | 2147483647 | null |
|
|
Public primary key |
||||||||
| plan_id | uuid | 2147483647 | null |
|
|
Parent plan foreign key which is the shared primary key with csip_record and therefore uses the same value as csip_record.record_id |
||||||||
| review_sequence | int4 | 10 | null |
|
|
The review sequence which starts at 1 and increments for each new review. Used to order the reviews. Maps to OFFENDER_CSIP_REVIEWS.REVIEW_SEQ |
||||||||
| review_date | date | 13 | null |
|
|
The date of the review. Maps to OFFENDER_CSIP_REVIEWS.CREATE_DATE |
||||||||
| recorded_by | varchar | 64 | null |
|
|
The username of the person who recorded the review. Maps to OFFENDER_CSIP_REVIEWS.CREATE_USER |
||||||||
| recorded_by_display_name | varchar | 255 | null |
|
|
The first and last name of the user who recorded the review. Does not update if their name changes |
||||||||
| next_review_date | date | 13 | √ | null |
|
|
The date of the next review. Maps to OFFENDER_CSIP_REVIEWS.NEXT_REVIEW_DATE |
|||||||
| actions | _varchar | 2147483647 | null |
|
|
An enumeration of actions that should be taken following the review one of which is to close the CSIP. CSIP to NOMIS mappings: ‘CLOSE_CSIP’ -> OFFENDER_CSIP_REVIEWS.CLOSE_CSIP, ‘REMAIN_ON_CSIP’ -> OFFENDER_CSIP_REVIEWS.REMAIN_ON_CSIP, ‘RESPONSIBLE_PEOPLE_INFORMED’ -> OFFENDER_CSIP_REVIEWS.PEOPLE_INFORMED, ‘CSIP_UPDATED’ -> OFFENDER_CSIP_REVIEWS.CSIP_UPDATED, ‘CASE_NOTE’ -> OFFENDER_CSIP_REVIEWS.CASE_NOTE. Only CLOSE_CSIP is populated via DPS. The rest are populated via NOMIS only and retained for sync and reporting |
||||||||
| csip_closed_date | date | 13 | √ | null |
|
|
The date the CSIP plan was closed following a review outcome decision to close it. Maps to OFFENDER_CSIP_REVIEWS.CLOSE_DATE |
|||||||
| summary | text | 2147483647 | √ | null |
|
|
Additional information about the review. Maps to OFFENDER_CSIP_REVIEWS.SUMMARY which has a 4000 character limit |
|||||||
| version | int4 | 10 | null |
|
|
Supports optimistic locking preventing unnecessary select queries for new entities |
||||||||
| legacy_id | int8 | 19 | √ | null |
|
|
The NOMIS OFFENDER_CSIP_REVIEWS.REVIEW_ID primary key value. Stored to guarantee uniqueness via sync. Not returned by API |
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| review_pkey | Primary key | Asc | review_id |
| idx_review_record_id | Performance | Asc | plan_id |
| idx_review_sequence | Must be unique | Asc/Asc | plan_id + review_sequence |
| review_legacy_id_key | Must be unique | Asc | legacy_id |
Check Constraints
| Constraint Name | Constraint |
|---|---|
| review_actions_enum_check | ((actions <@ ARRAY['RESPONSIBLE_PEOPLE_INFORMED'::character varying, 'CSIP_UPDATED'::character varying, 'REMAIN_ON_CSIP'::character varying, 'CASE_NOTE'::character varying, 'CLOSE_CSIP'::character varying])) |