erDiagram
    alert one to zero or more comment : "threaded comments (not currently used)"
    alert one to one or more audit_event : "detailed change history"
    alert many to one alert_code : "categorised by"
    alert_code one to one or more alert_type : "grouped by"
    alert one to zero or one migrated_alert : "records NOMIS alert information (no longer required)"

    alert {
        bigserial alert_id PK "not null"
        uuid alert_uuid "public unique id"
        bigint alert_code_id "not null"
        varchar(10) prison_number "not null"
        text description
        varchar(40) authorised_by
        date active_from "not null"
        date active_to
        timestamp created_at
        timestamp last_modified_at
        timestamp deleted_at
    }

    comment {
        bigserial comment_id PK "not null"
        uuid comment_uuid "not null"
        bigserial alert_id FK "not null"
        text comment "not null"
        timestamp created_at "not null"
        varchar created_by "not null"
        varchar created_by_display_name "not null"
    }

    audit_event {
        bigserial audit_event_id PK "not null"
        bigserial alert_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_display_name "not null"
        varchar(12) source "not null"
        varchar(6) active_case_load_id
        boolean description_updated
        boolean authorised_by_updated
        boolean active_from_updated
        boolean active_to_updated
        boolean comment_appended
    }

    alert_code {
        bigserial alert_code_id PK "not null"
        bigserial alert_type_id FK "not null"
        varchar(12) code "not null"
        varchar(40) description "not null"
        integer list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp modified_at
        varchar(32) modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    alert_type {
        bigserial alert_type_id PK "not null"
        varchar(12) code "not null"
        varchar(40) description "not null"
        integer list_sequence
        timestamp created_at "not null"
        varchar(32) created_by "not null"
        timestamp modified_at
        varchar(32) modified_by
        timestamp deactivated_at
        varchar(32) deactivated_by
    }

    bulk_alert {
        bigserial bulk_alert_id PK "not null"
        uuid bulk_alert_uuid "not null"
        jsonb request "not null"
        timestamp requested_at "not null"
        varchar(32) requested_by "not null"
        varchar(255) requested_by_display_name "not null"
        timestamp completed_at "not null"
        boolean successful "not null"
        jsonb messages "not null"
        jsonb existing_active_alerts "not null"
        jsonb alerts_created "not null"
        jsonb alerts_updated "not null"
        jsonb alerts_expired "not null"
    }
