Release bayanat v1.34 · sjacorg/bayanat

Actor Profiles: Complete overhaul of the Actor component and addition of Profiles to help analysts store information from multiple sources in one Actor, cutting down on duplication within the database and improving efficiency, while allowing for information from different sources to remain clearly separated.
- Move origin ID, description, labels, sources, source links, documentation date and publish date, as well as all missing persons fields, to a new component called Actor Profile.
- Profile can have one of three types:
  - Normal.
  - Missing Person: This type will contain fields from the normal profile in addition to missing persons fields.
  - Main: Same fields as in normal profile. However, each Actor can have one main profile and this profile can be used to consolidate and verify information stored in all profiles.
- Every Actor can contain unlimited number of normal and MP Profiles and one main profile.
- Profiles can be deleted and duplicated to allow for speedy creation.
- Changes to the Actor component to make the fields more universal, improve input uniformity and efficiency:
  - Change family status to dropdown list.
  - Change dialect to dropdown list. Dialects can be created and managed in the Component Data dashboard.
  - Split father and middle name to two separate fields.
  - Remove date of birth, place of birth and place of residence and move existing data to events.
  - Remove the need to re-enter full name of a person, will be generated from first, middle and last name.
  - Move "number of children" from missing persons to Actor.
  - Add shortcuts to auto fill first and last names for persons with unknown names.
Activity monitor: Improving this tool to allow admins to create audit trail of all users actions in the database.
- Complete revamp of the UI.
- Add a search tool, allowing admins to filter actions by date, type, subject and/or user.
- Track more user actions.
- Add settings for Activity Monitor in the System Settings dashboard to allow admins to customize their instances:
  - Setting for retention period (minimum is 90 days), after which activities will be automatically deleted.
  - Settings to enable/disable logging of types of user actions.
Sheets import:
- Changes in code to support sheets import after changes in Actor/Actor Profiles.
- More refactoring and improvements to code quality.
- Fix a bug in skin markings import.
Remove unneeded languages setting from System Settings dashboard.
Re-enable OAuth, disabling sign-up and allowing sign-ins from users already created by an admin only.
Fix a bug that caused related items to appear multiple times in review dialogs.
Fix a bug in user management dashboard that prevented opening of the navigation drawer.
Fix a bug in upload to S3 bucket which prevented uploads.
Fix bug in global default language setting.
Upgrade requirements
Tweak tests to accommodate the above changes.
Change to automated tests in GitHub Action to build the Bayanat container instead of using pre-built images.
New management command to check the local database alignment with the database models in the code.

Please ensure the Bayanat database, config.js and the .env files are backed up before beginning this upgrade.

After stopping all Bayanat services, carefully follow the following steps to upgrade to this version:

Upgrade deps:

source env/bin/activate
pip install -r requirements.txt

Database migrations:

Migrate family status and dialects first:

BEGIN;

-- Step 1: Update the description column
UPDATE actor       
SET description = COALESCE(description, '') ||
                '<br> <br> Family status: ' || COALESCE(family_status, '') where family_status != '';

UPDATE actor       
SET description = COALESCE(description, '') ||
                '<br> <br> Family status (AR): ' || COALESCE(family_status_ar, '') where family_status_ar != '';

UPDATE actor       
SET description = COALESCE(description, '') ||
                '<br> <br> Dialects: ' || COALESCE(dialects, '') where dialects != '';

UPDATE actor       
SET description = COALESCE(description, '') ||
                '<br> <br> Dialects (AR): ' || COALESCE(dialects_ar, '') where dialects_ar != '';

-- Step 2: Drop the family_status and dialects columns if the update was successful
ALTER TABLE actor
DROP COLUMN family_status,
DROP COLUMN family_status_ar,
DROP COLUMN dialects,
DROP COLUMN dialects_ar;

ALTER TABLE actor ADD COLUMN IF NOT EXISTS family_status VARCHAR(255); 

COMMIT;

BEGIN;

CREATE TABLE dialects (
    id SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL,
    title_tr VARCHAR,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'UTC'),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'UTC'),
    deleted BOOLEAN
);

CREATE TABLE actor_dialects (
    actor_id INTEGER,
    dialect_id INTEGER,
    PRIMARY KEY (actor_id, dialect_id),
    FOREIGN KEY (actor_id) REFERENCES actor(id),
    FOREIGN KEY (dialect_id) REFERENCES dialects(id)
);

COMMIT;

Migrate father name. This will depend on the previous use of the Father/Middle Name column. Please choose one of the two following option:

a. If the column was used to store middle name information, create a new column for Father's Name:

 BEGIN;

 -- Add father name as new columns
 ALTER TABLE actor ADD COLUMN IF NOT EXISTS father_name VARCHAR(255);
 ALTER TABLE actor ADD COLUMN IF NOT EXISTS father_name_ar VARCHAR(255);

 COMMIT;

b. If the column was used to store father's name information, rename existing column and create a new one for Middle Name:

 BEGIN;
 -- ALTERNATIVE SCENARIO FOR DBS THAT USED THE COLUMN AS FATHER NAME
 ALTER TABLE actor RENAME COLUMN middle_name to father_name;
 ALTER TABLE actor RENAME COLUMN middle_name_ar to father_name_ar;
 ALTER TABLE actor ADD COLUMN IF NOT EXISTS middle_name VARCHAR(255);
 ALTER TABLE actor ADD COLUMN IF NOT EXISTS middle_name_ar VARCHAR(255);
 COMMIT;

Update the following columns:

 -- Rename columns
 -- Note: These statements will fail if the columns do not exist.
 ALTER TABLE actor RENAME COLUMN actor_type TO type;
 ALTER TABLE actor RENAME COLUMN bio_children TO no_children;
 ALTER TABLE actor RENAME COLUMN national_id_card TO id_number;

Move data from Actor to Actor Profile. This will create a new Profile, link it to Actor, move the data from Actor to Profile, and delete columns in Actor:

BEGIN;

CREATE TABLE actor_profile (
    id SERIAL PRIMARY KEY,
    description TEXT,
    source_link VARCHAR(255),
    source_link_type BOOLEAN DEFAULT FALSE,
    publish_date TIMESTAMP,
    documentation_date TIMESTAMP,
    actor_id INTEGER REFERENCES actor(id),
    originid VARCHAR(255),

    mode INTEGER DEFAULT 1 NOT NULL,

    -- Fields from BaseMixin
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted BOOLEAN

);


INSERT INTO actor_profile (id, description, source_link, source_link_type, publish_date, documentation_date, actor_id, originid)
SELECT id, description, source_link, source_link_type, publish_date, documentation_date, id, originid FROM actor;


-- reset actor profile counter
SELECT setval('actor_profile_id_seq', (SELECT MAX(id) FROM actor_profile));

-- Rename actor_id to actor_profile_id in actor_sources table
ALTER TABLE actor_sources RENAME COLUMN actor_id TO actor_profile_id;

-- Rename actor_id to actor_profile_id in actor_labels table
ALTER TABLE actor_labels RENAME COLUMN actor_id TO actor_profile_id;

-- Rename actor_id to actor_profile_id in actor_verlabels table
ALTER TABLE actor_verlabels RENAME COLUMN actor_id TO actor_profile_id;

-- migrate foreign key constraints

-- actor_labels table
ALTER TABLE actor_labels DROP CONSTRAINT IF EXISTS actor_labels_actor_id_fkey;
ALTER TABLE actor_labels ADD CONSTRAINT actor_labels_actor_profile_id_fkey FOREIGN KEY (actor_profile_id) REFERENCES actor_profile(id);

-- actor_sources table
ALTER TABLE actor_sources DROP CONSTRAINT IF EXISTS actor_sources_actor_id_fkey;
ALTER TABLE actor_sources ADD CONSTRAINT actor_sources_actor_profile_id_fkey FOREIGN KEY (actor_profile_id) REFERENCES actor_profile(id);

-- actor_verlabels table
ALTER TABLE actor_verlabels DROP CONSTRAINT IF EXISTS actor_verlabels_actor_id_fkey;
ALTER TABLE actor_verlabels ADD CONSTRAINT actor_verlabels_actor_profile_id_fkey FOREIGN KEY (actor_profile_id) REFERENCES actor_profile(id);



-- handle updated generated column (Search)

-- drop old column
ALTER TABLE actor DROP COLUMN IF EXISTS search;

-- Add the new 'search' column as a computed column based on new structure
ALTER TABLE actor ADD COLUMN search TEXT GENERATED ALWAYS AS (
    (id)::text || ' ' ||
    COALESCE(name, '') || ' ' ||
    COALESCE(name_ar, '') || ' ' ||
    COALESCE(comments, '')
) STORED;

-- Create a GIN index on the 'search' column using gin_trgm_ops
CREATE INDEX ix_actor_search ON actor USING gin (search gin_trgm_ops);


-- Dropping 'source_link' and 'source_link_type' columns from actor table
ALTER TABLE actor DROP COLUMN IF EXISTS source_link;
ALTER TABLE actor DROP COLUMN IF EXISTS source_link_type;

-- Dropping 'publish_date' column
ALTER TABLE actor DROP COLUMN IF EXISTS publish_date;

-- Dropping 'documentation_date' column
ALTER TABLE actor DROP COLUMN IF EXISTS documentation_date;

-- Dropping 'description' column
ALTER TABLE actor DROP COLUMN IF EXISTS description;

-- Dropping 'originid' column
ALTER TABLE actor DROP COLUMN IF EXISTS originid;

-- Step 1: Add new columns to actor_profile for MISSING_PERSONS data
ALTER TABLE actor_profile ADD COLUMN last_address TEXT;
ALTER TABLE actor_profile ADD COLUMN social_networks JSONB;
ALTER TABLE actor_profile ADD COLUMN marriage_history VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN pregnant_at_disappearance VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN months_pregnant INTEGER;
ALTER TABLE actor_profile ADD COLUMN missing_relatives BOOLEAN;
ALTER TABLE actor_profile ADD COLUMN saw_name VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN saw_address TEXT;
ALTER TABLE actor_profile ADD COLUMN saw_email VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN saw_phone VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN detained_before VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN seen_in_detention JSONB;
ALTER TABLE actor_profile ADD COLUMN injured JSONB;
ALTER TABLE actor_profile ADD COLUMN known_dead JSONB;
ALTER TABLE actor_profile ADD COLUMN death_details TEXT;
ALTER TABLE actor_profile ADD COLUMN personal_items TEXT;
ALTER TABLE actor_profile ADD COLUMN height INTEGER;
ALTER TABLE actor_profile ADD COLUMN weight INTEGER;
ALTER TABLE actor_profile ADD COLUMN physique VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN hair_loss VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN hair_type VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN hair_length VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN hair_color VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN facial_hair VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN posture TEXT;
ALTER TABLE actor_profile ADD COLUMN skin_markings JSONB;
ALTER TABLE actor_profile ADD COLUMN handedness VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN glasses VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN eye_color VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN dist_char_con VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN dist_char_acq VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN physical_habits VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN other TEXT;
ALTER TABLE actor_profile ADD COLUMN phys_name_contact TEXT;
ALTER TABLE actor_profile ADD COLUMN injuries TEXT;
ALTER TABLE actor_profile ADD COLUMN implants TEXT;
ALTER TABLE actor_profile ADD COLUMN malforms TEXT;
ALTER TABLE actor_profile ADD COLUMN pain TEXT;
ALTER TABLE actor_profile ADD COLUMN other_conditions TEXT;
ALTER TABLE actor_profile ADD COLUMN accidents TEXT;
ALTER TABLE actor_profile ADD COLUMN pres_drugs TEXT;
ALTER TABLE actor_profile ADD COLUMN smoker VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN dental_record BOOLEAN;
ALTER TABLE actor_profile ADD COLUMN dentist_info TEXT;
ALTER TABLE actor_profile ADD COLUMN teeth_features TEXT;
ALTER TABLE actor_profile ADD COLUMN dental_problems TEXT;
ALTER TABLE actor_profile ADD COLUMN dental_treatments TEXT;
ALTER TABLE actor_profile ADD COLUMN dental_habits TEXT;
ALTER TABLE actor_profile ADD COLUMN case_status VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN reporters JSONB;
ALTER TABLE actor_profile ADD COLUMN identified_by VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN family_notified BOOLEAN;
ALTER TABLE actor_profile ADD COLUMN hypothesis_based TEXT;
ALTER TABLE actor_profile ADD COLUMN hypothesis_status VARCHAR(255);
ALTER TABLE actor_profile ADD COLUMN reburial_location VARCHAR(255);

UPDATE actor_profile SET mode = 1;

COMMIT;

OPTIONAL: Execute the following migration if Missing Persons extension was perviously enabled:

 BEGIN;

 -- Update all profiles to set their mode to 3 in the Missing Persons section
 UPDATE actor_profile SET mode = 3;

 -- Migrate Data: Update actor_profile rows with Missing Persons data from actor table (execute if migrating from Existing MP installation)
 UPDATE actor_profile
 SET
     last_address = actor.last_address,
     social_networks = actor.social_networks,
     marriage_history = actor.marriage_history,
     pregnant_at_disappearance = actor.pregnant_at_disappearance,
     months_pregnant = actor.months_pregnant,
     missing_relatives = actor.missing_relatives,
     saw_name = actor.saw_name,
     saw_address = actor.saw_address,
     saw_email = actor.saw_email,
     saw_phone = actor.saw_phone,
     detained_before = actor.detained_before,
     seen_in_detention = actor.seen_in_detention,
     injured = actor.injured,
     known_dead = actor.known_dead,
     death_details = actor.death_details,
     personal_items = actor.personal_items,
     height = actor.height,
     weight = actor.weight,
     physique = actor.physique,
     hair_loss = actor.hair_loss,
     hair_type = actor.hair_type,
     hair_length = actor.hair_length,
     hair_color = actor.hair_color,
     facial_hair = actor.facial_hair,
     posture = actor.posture,
     skin_markings = actor.skin_markings,
     handedness = actor.handedness,
     glasses = actor.glasses,
     eye_color = actor.eye_color,
     dist_char_con = actor.dist_char_con,
     dist_char_acq = actor.dist_char_acq,
     physical_habits = actor.physical_habits,
     other = actor.other,
     phys_name_contact = actor.phys_name_contact,
     injuries = actor.injuries,
     implants = actor.implants,
     malforms = actor.malforms,
     pain = actor.pain,
     other_conditions = actor.other_conditions,
     accidents = actor.accidents,
     pres_drugs = actor.pres_drugs,
     smoker = actor.smoker,
     dental_record = actor.dental_record,
     dentist_info = actor.dentist_info,
     teeth_features = actor.teeth_features,
     dental_problems = actor.dental_problems,
     dental_treatments = actor.dental_treatments,
     dental_habits = actor.dental_habits,
     case_status = actor.case_status,
     reporters = actor.reporters,
     identified_by = actor.identified_by,
     family_notified = actor.family_notified,
     hypothesis_based = actor.hypothesis_based,
     hypothesis_status = actor.hypothesis_status,
     reburial_location = actor.reburial_location
 FROM actor
 WHERE actor_profile.actor_id = actor.id;


 -- Dropping Missing Persons fields from actor table
 ALTER TABLE actor DROP COLUMN IF EXISTS last_address;
 ALTER TABLE actor DROP COLUMN IF EXISTS social_networks;
 ALTER TABLE actor DROP COLUMN IF EXISTS marriage_history;
 ALTER TABLE actor DROP COLUMN IF EXISTS pregnant_at_disappearance;
 ALTER TABLE actor DROP COLUMN IF EXISTS months_pregnant;
 ALTER TABLE actor DROP COLUMN IF EXISTS missing_relatives;
 ALTER TABLE actor DROP COLUMN IF EXISTS saw_name;
 ALTER TABLE actor DROP COLUMN IF EXISTS saw_address;
 ALTER TABLE actor DROP COLUMN IF EXISTS saw_email;
 ALTER TABLE actor DROP COLUMN IF EXISTS saw_phone;
 ALTER TABLE actor DROP COLUMN IF EXISTS detained_before;
 ALTER TABLE actor DROP COLUMN IF EXISTS seen_in_detention;
 ALTER TABLE actor DROP COLUMN IF EXISTS injured;
 ALTER TABLE actor DROP COLUMN IF EXISTS known_dead;
 ALTER TABLE actor DROP COLUMN IF EXISTS death_details;
 ALTER TABLE actor DROP COLUMN IF EXISTS personal_items;
 ALTER TABLE actor DROP COLUMN IF EXISTS height;
 ALTER TABLE actor DROP COLUMN IF EXISTS weight;
 ALTER TABLE actor DROP COLUMN IF EXISTS physique;
 ALTER TABLE actor DROP COLUMN IF EXISTS hair_loss;
 ALTER TABLE actor DROP COLUMN IF EXISTS hair_type;
 ALTER TABLE actor DROP COLUMN IF EXISTS hair_length;
 ALTER TABLE actor DROP COLUMN IF EXISTS hair_color;
 ALTER TABLE actor DROP COLUMN IF EXISTS facial_hair;
 ALTER TABLE actor DROP COLUMN IF EXISTS posture;
 ALTER TABLE actor DROP COLUMN IF EXISTS skin_markings;
 ALTER TABLE actor DROP COLUMN IF EXISTS handedness;
 ALTER TABLE actor DROP COLUMN IF EXISTS glasses;
 ALTER TABLE actor DROP COLUMN IF EXISTS eye_color;
 ALTER TABLE actor DROP COLUMN IF EXISTS dist_char_con;
 ALTER TABLE actor DROP COLUMN IF EXISTS dist_char_acq;
 ALTER TABLE actor DROP COLUMN IF EXISTS physical_habits;
 ALTER TABLE actor DROP COLUMN IF EXISTS other;
 ALTER TABLE actor DROP COLUMN IF EXISTS phys_name_contact;
 ALTER TABLE actor DROP COLUMN IF EXISTS injuries;
 ALTER TABLE actor DROP COLUMN IF EXISTS implants;
 ALTER TABLE actor DROP COLUMN IF EXISTS malforms;
 ALTER TABLE actor DROP COLUMN IF EXISTS pain;
 ALTER TABLE actor DROP COLUMN IF EXISTS other_conditions;
 ALTER TABLE actor DROP COLUMN IF EXISTS accidents;
 ALTER TABLE actor DROP COLUMN IF EXISTS pres_drugs;
 ALTER TABLE actor DROP COLUMN IF EXISTS smoker;
 ALTER TABLE actor DROP COLUMN IF EXISTS dental_record;
 ALTER TABLE actor DROP COLUMN IF EXISTS dentist_info;
 ALTER TABLE actor DROP COLUMN IF EXISTS teeth_features;
 ALTER TABLE actor DROP COLUMN IF EXISTS dental_problems;
 ALTER TABLE actor DROP COLUMN IF EXISTS dental_treatments;
 ALTER TABLE actor DROP COLUMN IF EXISTS dental_habits;
 ALTER TABLE actor DROP COLUMN IF EXISTS case_status;
 ALTER TABLE actor DROP COLUMN IF EXISTS reporters;
 ALTER TABLE actor DROP COLUMN IF EXISTS identified_by;
 ALTER TABLE actor DROP COLUMN IF EXISTS family_notified;
 ALTER TABLE actor DROP COLUMN IF EXISTS hypothesis_based;
 ALTER TABLE actor DROP COLUMN IF EXISTS hypothesis_status;
 ALTER TABLE actor DROP COLUMN IF EXISTS reburial_location;

 COMMIT;

Move data in date and place of birth and place of residence to events:

DO $$
DECLARE
    actor_id INTEGER;
    new_event_id INTEGER;
    actor_rec RECORD;
BEGIN
    FOR actor_rec IN SELECT id, birth_date, birth_place_id FROM actor WHERE birth_date IS NOT NULL OR birth_place_id IS NOT NULL
    LOOP
        -- Insert the event and get its ID
        INSERT INTO event (created_at, updated_at, location_id, eventtype_id, from_date)
        VALUES (NOW(), NOW(), actor_rec.birth_place_id, 10, actor_rec.birth_date)
        RETURNING id INTO new_event_id;

        -- Link the actor to the event
        INSERT INTO actor_events (actor_id, event_id) VALUES (actor_rec.id, new_event_id);
    END LOOP;
END $$;

-- Dropping birth_date and birth_place_id columns from actor table only if the previous operations were successful
ALTER TABLE actor DROP COLUMN IF EXISTS birth_date;
ALTER TABLE actor DROP COLUMN IF EXISTS birth_place_id;

COMMIT;


DO $$
DECLARE
    actor_rec RECORD;
    new_event_id INTEGER;
BEGIN
    -- Loop through actors with a non-null residence_place
    FOR actor_rec IN SELECT id, residence_place_id FROM actor WHERE residence_place_id IS NOT NULL
    LOOP
        -- Insert the event and get its ID
        INSERT INTO event (created_at, updated_at, location_id, eventtype_id)
        VALUES (NOW(), NOW(), actor_rec.residence_place_id, 23) -- Event type ID is 23
        RETURNING id INTO new_event_id;

        -- Link the actor to the event
        INSERT INTO actor_events (actor_id, event_id) VALUES (actor_rec.id, new_event_id);
    END LOOP;
END $$;

-- drop the residence_place_id column from actor table if the previous operations were successful
ALTER TABLE actor DROP COLUMN IF EXISTS residence_place_id;

COMMIT;

UPDATE actor SET type = 'Person' WHERE type IS NULL;

Activity table changes:

BEGIN;

ALTER TABLE activity
RENAME COLUMN tag TO model;

ALTER TABLE activity
ADD COLUMN details TEXT;

ALTER TABLE activity
ADD COLUMN status TEXT;

COMMIT;
```

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

bayanat v1.34