Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | |||
|---|---|---|---|---|---|---|---|---|---|---|---|
| id | uuid | 2147483647 | null |
|
|
Public primary key |
|||||
| person_identifier | varchar | 12 | null |
|
|
The prison number of the person the case note is for |
|||||
| location_id | varchar | 12 | null |
|
|
The location of the person at the time the case note was created. A prison code, TRN or OUT |
|||||
| varchar | 64 | null |
|
|
The username of the user who authored (created) the note |
||||||
| varchar | 80 | null |
|
|
The first and last name of the user at the time the case note was created. Does not update if their name changes |
||||||
| sub_type_id | int4 | 10 | null |
|
|
Foreign key to case note sub-type categorising the note |
|||||
| occurred_at | timestamp | 29,6 | null |
|
|
The date and time the event the case note is recording took place. Will be on or before created at |
|||||
| note_text | text | 2147483647 | √ | null |
|
|
The text of the case note |
||||
| created_at | timestamp | 29,6 | null |
|
|
The date and time the case note was created. Will be on or after occurred at |
|||||
| created_by | varchar | 64 | null |
|
|
The username of the user who created the case note |
|||||
| legacy_id | bigserial | 19 | √ | nextval('case_note_legacy_id_seq'::regclass) |
|
|
A numerical id for consuming services incompatible with UUID case note ids. Will be positive for migrated case notes and those synced from NOMIS, negative if created in the DPS database first |
||||
| varchar | 64 | null |
|
|
The NOMIS staff id of the user who created the note. This is a NOMIS internal primary key used to support consuming services reliant on it |
||||||
| system_generated | bool | 1 | false |
|
|
Whether the case note was generated by a consuming service directly or as a side effect of another process |
|||||
| system | varchar | 6 | 'DPS'::character varying |
|
|
The system used to create the case note. Either DPS or NOMIS |
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| case_note_pkey | Primary key | Asc | id |
| idx_author_id_sub_type_created_at_location | Performance | Asc/Asc/Asc/Asc | author_user_id + sub_type_id + created_at + location_id |
| idx_author_id_sub_type_occurred_at_location | Performance | Asc/Asc/Asc/Asc | author_user_id + sub_type_id + occurred_at + location_id |
| idx_case_note_author_username | Performance | Asc | author_username |
| idx_case_note_created_at | Performance | Asc | created_at |
| idx_case_note_location_id | Performance | Asc | location_id |
| idx_case_note_occurred_at_location_id | Performance | Asc/Asc | occurred_at + location_id |
| idx_case_note_sub_type_id | Performance | Asc | sub_type_id |
| idx_person_identifier_sub_type_id_created_at_location_id | Performance | Asc/Asc/Asc/Asc | person_identifier + sub_type_id + created_at + location_id |
| idx_person_identifier_sub_type_id_occurred_at_location_id | Performance | Asc/Asc/Asc/Asc | person_identifier + sub_type_id + occurred_at + location_id |
| unq_legacy_id | Must be unique | Asc | legacy_id |
Check Constraints
| Constraint Name | Constraint |
|---|---|
| check_system | (((system)::text = ANY ((ARRAY['DPS'::character varying, 'NOMIS'::character varying])::text[]))) |