Create a Script that Pulls Down and Anonymizes Prod Data

andrew-rist-tw commented 1 month ago

We often have to deal with problems that come up in messy production data, but pulling down actual production data is inherently dangerous because we have a lot of personally identifiable information that would be put at risk, therefore we should write a script that pulls down production data from either NO or Hawaii and anonymizes all the fields that contain PII

TODO List

[x] Identify which fields in the database contain PII (confirm with @christina-luong)
[x] Choose a tool that anonymizes PII
[x] Write the script to pull data from a database (use the db credentials as command line arguments) and output as a .csv
[ ] Add anonymization to the script for the fields identified as PII

andrew-rist-tw commented 1 month ago

Researched data-anonymizer as a possible tool for anonymizing our data

andrew-rist-tw commented 1 month ago

First draft of PII data in db

PII Data

action_audits table
- user
addresses table
- street_address
- street_address2
- city
- state
- zip_code
- country
- intersection
- lat
- lng
- place_id
- additional_location_info
attachments table
- file_name (can contain complainant names)
- description
audits table
- user
case_notes table
- user
- notes
cases table
- narrative_details
- created_by
- assigned_to
- narrative_summary
- case_number
- pib_case_number
cases_inmates table
- notes
- first_name
- middle_initial
- last_name
- suffix
- inmate_id
- not_found_inmate_id
- facility
cases_officers table
- notes
- first_name
- middle_name
- last_name
- employee_id
- supervisor_first_name
- supervisor_middle_name
- supervisor_last_name
- supervisor_employee_id
- dob
- end_date
- hire_date
- phone_number
- email
civilians table
- first_name
- middle_initial
- last_name
- suffix
- birth_date
- phone_number
- email
- additional_info
complainant_letters table
- final_pdf_filename (will contain complainant name)
data_change_audits table
- model_description
- snapshot
- changes
facilities table
- abbreviation
- name
- address
file_audits table
- file_name (can contain complainant name)
inmates table
- inmate_id
- first_name
- last_name
- region
- facility
- location_sub_1
- location_sub_2
- location_sub_3
- location_sub_4
- housing
- current_location
- muster
- classification_date
- booking_start_date
- tentative_release_date
- booking_end_date
- actual_release_date
- date_of_birth
- age
- date_death_recorded
- sentence_length
- transfer_date
- address
legacy_data_change_audits table
- snapshot
- changes
- model_description
- user
letter_officers table
- historical_behavior_notes
- recommended_action_notes
letters table
- recipient
- recipient_address
- sender
- transcribed_by
- edited_letter_html
- final_pdf_filename
notifications table
- user
officers table
- first_name
- last_name
- dob
- hire_date
- end_date
- employee_id
- supervisor_employee_id
officers_allegations table
- details
referral_letter_officer_history_notes table
- details
- pib_case_number
referral_letters table
- recipient
- sender
- transcribed_by
- edited_letter_html
- final_pdf_filename
- recipient_address

Non-PII Data

action_audits table
- id
- action
- created_at
- updated_at
- audit_type (indicates the type of data touched not the data itself)
addresses table
- id
- created_at
- updated_at
- deleted_at
- addressable_type (only has civilian or cases as value)
- addressable_id
allegations table (none of it is PII)
attachments table
- id
- case_id
- created_at
- updated_at
audits table
- id
- reference_id
- audit_action
- created_at
- updated_at
- manager_type
case_classifications table (none of it is PII)
case_note_actions table (none of it is PII)
case_notes table
- id
- case_id
- action_taken_at
- created_at
- updated_at
- deleted_at
- case_note_action_id
case_statuses table (none of it is PII)
case_tags table (none of it is PII)
cases table
- id
- first_contact_date
- created_at
- updated_at
- incident_date
- incident_time
- complaint_type
- intake_source_id
- deleted_at
- year
- how_did_you_hear_about_us_source_id
- district_id
- incident_timezone
- status
- complaint_type_id
- is_case
- priority_level_id
- priority_reason_id
- facility_id
cases_inmates table
- case_id
- role_on_case
- is_anonymous
- created_at
- updated_at
- id
- deleted_at
- person_type_key
cases_officers table
- id
- case_id
- officer_id
- created_at
- updated_at
- role_on_case
- supervisor_officer_number
- employee_type
- district
- bureau
- rank
- sex
- race
- work_status
- deleted_at
- is_anonymous
- case_employee_type
- person_type
civilian_titles table (none of it is PII)
civilians table
- id
- case_id
- role_on_case
- gender_identity
- race_ethnicity
- deleted_at
- updated_at
- created_at
- title
- race_ethnicity_id
- is_anonymous
- gender_identity_id
- civilian_title_id
- person_type
- person_subtype
classifications table (none of it is PII)
complainant_letters table
- id
- case_id
- created_at
- updated_at
- complainant_civilian_id
- complainant_officer_id
complaint_types table (none of it is PII)
configs table (none of it is PII)
data_access_audits table (none of it is PII)
data_access_values table (none of it is PII)
data_change_audits table
- id
- audit_id
- model_name
- model_id
directives table (none of it is PII)
districts table (none of it is PII)
export_audits table (none of it is PII)
facilities table
- id
- created_at
- updated_at
feature_toggles table (none of it is PII)
file_audits table (none of it is PII)
gender_identities table (none of it is PII)
how_did_you_hear_about_us_sources table (none of it is PII)
inmates table
- deleted_at
- facility_id
- updated_at
- created_at
- on_count
- language
- religion
- citizenship
- country_of_birth
- weekender
- release_type
- indigent
- race
- primary_ethnicity
- security_classification
- custody_status
- custody_status_reason
- status
intake_sources table (none of it is PII)
legacy_data_access_audits table (none of it is PII)
legacy_data_change_audits table
- id
- case_id
- model_name
- model_id
- action
- created_at
- updated_at
letter_images table (none of it is PII)
letter_input_pages table (none of it is PII)
letter_officers table
- id
- case_officer_id
- num_historical_high_allegations
- num_historical_med_allegations
- num_historical_low_allegations
- created_at
- updated_at
- deleted_at
- officer_history_option_id
letter_settings table (none of it is PII)
letter_type_complaint_type table (none of it is PII)
letter_type_letter_input_pages table (none of it is PII)
letter_types table (none of it is PII)
letter_types_letter_images table (none of it is PII)
letters table
- id
- case_id
- created_at
- updated_at
- type_id
notifications table
- id
- case_note_id
- has_been_read
- created_at
- updated_at
- deleted_at
officer_history_options table (none of it is PII)
officers table
- id
- rank
- race
- sex
- bureau
- district
- work_status
- created_at
- updated_at
- employee_type
- district_id
officers_allegations table
- id
- case_officer_id
- allegation_id
- created_at
- updated_at
- deleted_at
- severity
- chapter
- directive
- custom_directive
person_types table (none of it is PII)
priority_levels table (none of it is PII)
priority_reasons table (none of it is PII)
public_data_visualizations table (none of it is PII)
race_ethnicities table (none of it is PII)
recommended_actions table (none of it is PII)
referral_letter_officer_history_notes table
- id
- referral_letter_officer_id
- created_at
- updated_at
- deleted_at
referral_letter_officer_recommended_actions table (none of it is PII)
referral_letters table
- id
- case_id
- created_at
- updated_at
- include_retaliation_concerns
rule_chapters table (none of it is PII)
sequelize_data table (none of it is PII)
sequelize_meta table (none of it is PII)
signers table (none of it is PII)
tags table (none of it is PII)

PublicDataWorks / complaint-manager

Create a Script that Pulls Down and Anonymizes Prod Data #630