[STFF-104] Specification Details

Data Types Specifications

The Data Types in the report that are outlined within this article are as follows:

Assessment
Client Contact
Client Field Interaction
Client File
Client Location
Client Note
Client ROI
Coordinated Entry Event
Manual Personal ID Management
Profile Created
Profile Deleted
Profile Edited
Program Annual Update
Program Current Living Situation
Program Enrollment
Program Exit
Program Follow-up
Program Status Update
Referral
Referral Scheduled
Service Provided/Updated
Unit/Bed Assignment

Each Data Type above has a section within the article and is linked appropriately.

Notes:

If any Data Type was deleted and then re-activated, the client is not reported as deleted.
If any Data Type was deleted, then re-activated, and then deleted again, the first deletion will not be reported as deleted but will be reported for the DateTime of the last deletion.

Assessment

Data Type(s):	Assessment
Deleted Option:	all; not deleted

When an assessment is added or an existing assessment is updated, a corresponding row is added to the "common_logs" table.

For "Assessment" staff activity, all rows are selected from the "common_logs" table and "client_assessment_demographics" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 8
and activity DateTime (common_logs.date) is within [report date range]
and client_assessment_demographics.ref_agency = CURRENT_AGENCY
and client_assessment_demographics record is not deleted
and client record is not deleted

Assessment Deleted

Data Type(s):	Assessment
Deleted Option:	all; deleted only

When client's assessment is deleted, "client_assessment_demographics" table is updated with "client_assessment_demographics.deleted = 1 AND client_assessment_demographics.ref_agency_deleted = current_agency AND client_assessment_demographics.ref_user_updated = staff_id".

For "Assessment Deleted" staff activity, all rows are selected from the "client_assessment_demographics" table where:

client_assessment_demographics.ref_user_updated = STAFF_ID
and client_assessment_demographics.ref_agency_deleted = CURRENT_AGENCY
and client_assessment_demographics.last_updated is within [report date range]
and client_assessment_demographics.deleted = 1

Client Contact Created

Data Type(s):	Client Contact
Deleted Option:	all; not deleted

When a client contact is added, a corresponding row is added to the "client_contacts" and "common_logs" tables.

For "Client Contact" staff activity, all rows are selected from the "client_contacts" table where:

client_contacts.ref_user= STAFF_ID
and activity DateTime (client_contacts.added_date) is within [report date range]
and Client Contact is not deleted
and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and client record is not deleted
and activity DateTime (client_contacts.added_date) before 01/01/2023
- OR Client Contact Type = Client Supplied (client_contacts.ref_type= 500) AND activity DateTime (client_contacts.added_date) before 02/10/2026

For "Client Contact" staff activity, all rows are selected from the "common_logs" and "client_contacts" tables where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 35
and the previous record (prev_record) in the common_log table is equal to null
and there is a correspondent record in the client_contacts table
and activity DateTime (common_logs.date) is within [report date range]
and Client Contact is not deleted
and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and client record is not deleted

Client Contact Edited

Data Type(s):	Client Contact
Deleted Option:	all; not deleted

When an existing client contact is updated, a current row from the "client_contacts" table is updated and a new row is added to the "common_logs" table.

For "Client Contact Created/Edited" staff activity, all rows are selected from the "client_contacts" table where:

client_contacts.ref_user= STAFF_ID
and client_contacts.added_date <> client_contacts.last_updated
and activity DateTime (client_contacts.added_date) is within [report date range]
and Client Contact is not deleted
and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and client record is not deleted
and activity DateTime (client_contacts.added_date) before 01/01/2023
- OR Client Contact Type = Client Supplied (client_contacts.ref_type= 500) AND activity DateTime (client_contacts.added_date) before 02/10/2026

For "Client Contact Created/Edited" staff activity, all rows are selected from the "common_logs" and "client_contacts" tables where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 35
and the previous record (prev_record) in the common_log table is not equal to null
and the current record (curr_record) does not include the text "deleted"
and there is a correspondent record in the client_contacts table
and activity DateTime (common_logs.date) is within [report date range]
and Client Contact is not deleted
and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and client record is not deleted

Client Contact Deleted

Data Type(s):	Client Contact
Deleted Option:	all; deleted only

When a client contact is deleted, the "client_contacts" table is updated with "client_contacts.deleted = 1 AND client_contacts.ref_user_updated = staff_id".

For "Client Contact Deleted" staff activity, all rows are selected from the "client_contacts" table where:

client_contacts.ref_user_updated = STAFF_ID
and client_contacts.deleted = 1
and activity DateTime (client_contacts.last_updated) is within [report date range]
and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and client record is not deleted

Client Contact Edited

Data Type(s):	Client Contact
Deleted Option:	all; not deleted

When an existing client contact is updated, a current row from the "client_contacts" table is updated.

For "Client Contact Created/Edited" staff activity, all rows are selected from the "release_of_information" table where:

client_contacts.ref_user= STAFF_ID
and client_contacts.added_date <> client_contacts.last_updated
and activity DateTime (client_contacts.added_date) is within [report date range]
and Client Contact is not deleted
and if the client contacts record is private, then client_contacts.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and client record is not deleted

Client Field Interaction

Data Type(s):	Client Field Interaction
Deleted Option:	all; not deleted

When a Field Interaction is added, a corresponding row is added to the "geolocation_mapping," “geolocations,“ and “client_field_interactions" tables.

For "Client Field Interaction" staff activity, all rows are selected from the "client_field_interactions" table where:

client_field_interactions.ref_user = STAFF_ID
and client_field_interactions.ref_agency = CURRENT_AGENCY
and activity DateTime (client_field_interactions.added_date) is within [report date range]
and Field Interaction is not deleted
and client record is not deleted
and activity DateTime (client_field_interactions.added_date) before 07/17/2023

For "Client Field Interaction" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 40
and common_logs.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and the current record (curr_record) contains a non-empty "ref_geolocation" value
and the current record (curr_record) includes the text "Field Interaction"
and the previous record (prev_record) is empty (the Field Interaction record was created, not edited)
and client record is not deleted

Client Field Interaction (Deleted)

Data Type(s):	Client Field Interaction
Deleted Option	all; deleted only

When a Field Interaction is deleted, a corresponding row is added to the "common_logs" table. Also, the deleted field from geolocations is updated using the deleted = 1 value.

For "Client Field Interaction Deleted" staff activity, all rows are selected from the "client_field_interactions" table where:

client_field_interactions.ref_user_updated = STAFF_ID
and client_field_interactions.ref_agency_deleted = CURRENT_AGENCY
and activity DateTime (client_field_interactions.last_updated) is within [report date range]
and Field Interaction is deleted
and client record is not deleted
and creation DateTime (client_field_interactions.added_date) before 07/17/2023

For "Client Field Interaction Deleted" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 40
and common_logs.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and the previous record (prev_record) includes the text "Field Interaction"
and the current record (curr_record) includes the text "(deleted)"
and client record is not deleted

Client File

Data Type(s):	Client File
Deleted Option:	all; not deleted

When a new client file is added, a corresponding row is added to the "common_logs", "files", and to the "client_files" tables.

For "Client File" staff activity, all rows are selected from the “common_logs” table, "files" table, and the "client_files" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 41
and there is a correspondent record in the client_files table
and there is a correspondent record in the files table
and activity DateTime (common_logs.date) is within [report date range]
and client_files.ref_agency = CURRENT_AGENCY
and record from the "client_files" table is not deleted
and client record is not deleted

Client File Deleted

Data Type(s):	Client File
Deleted Option:	all; deleted only

When Client File is deleted, the "client_files" table is updated with "client_files.deleted = 1 AND client_files.ref_agency_deleted = current_agency AND client_files.ref_user_updated = staff_id".

For "Client File Deleted" staff activity, all rows are selected from the "client_files" table where:

client_files.ref_user_updated = STAFF_ID
and client_files.ref_agency_deleted= CURRENT_AGENCY
and client_files.deleted = 1
and activity DateTime (client_files.last_updated) is within [report date range]
and client record is not deleted
and file record is exists

Note: If Client File Deleted is selected, only clients with deleted files during the report date range will be reported.

Client Location

Data Type(s):	Client Location
Deleted Option:	all; not deleted

When a client location is added, a corresponding row is added to the "client_addresses" table.

For "Client Location" staff activity, all rows are selected from the "client_addresses" table where:

client_addresses.ref_user = STAFF_ID
and client_addresses.ref_agency = CURRENT_AGENCY
and activity DateTime (client_addresses.added_date) is within [report date range]
and client location is not deleted
and client record is not deleted
and activity DateTime (client_addresses.added_date) before 07/17/2023

For "Client Location" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 40
and common_logs.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and the current record (curr_record) contains a non-empty "ref_geolocation" value
and the current record (curr_record) includes the text "Address"
and the previous record (prev_record) is empty (the location record was created, not edited)
and client record is not deleted

Client Location Deleted

Data Type(s):	Client Location
Deleted Option:	all; deleted only

When Client Location is deleted, the "client_addresses" table is updated with "client_addresses.deleted = 1 AND client_addresses.ref_agency_deleted = current_agency AND client_addresses.ref_user_updated = staff_id".

For "Client Location Deleted" staff activity, all rows are selected from the "client_addresses" table where:

client_addresses.ref_user = STAFF_ID
and client_addresses.ref_agency_deleted = CURRENT_AGENCY
and activity DateTime (client_addresses.last_updated) is within [report date range]
and client location is deleted
and client record is not deleted
and creation DateTime (client_addresses.added_date) before 07/17/2023

For "Client Location Deleted" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 40
and common_logs.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and the previous record (prev_record) includes the text "Address"
and the current record (curr_record) includes the text "(deleted)"
and client record is not deleted

Client Note

Data Type(s):	Client Note
Deleted Option:	all; not deleted

When a new client note is added, a corresponding row is added to the "client_notes" table.

When an existing client note is updated, a corresponding row is added to the "client_notes_log" table.

For "Client Note" staff activity, all rows are selected from the "client_notes" and the "client_notes_log" table where:

the client note is not deleted
and client_notes.ref_agency = CURRENT_AGENCY
and (client_notes.ref_user or client_notes_log.ref_user ) = STAFF_ID
and activity DateTime (client_notes.date or client_notes_log.date) is within [report date range]
and client note is not deleted
and client record is not deleted

Client Note Deleted

Data Type(s):	Client Note
Deleted Option:	all; deleted only

When Client Note is deleted, the "client_notes" table is updated with the "client_notes.deleted = 1 AND client_notes.ref_agency_deleted = current_agency AND client_notes.ref_user_updated = staff_id".

For "Client Note Deleted" staff activity, all rows are selected from the "client_notes" table where:

client_notes.ref_user_updated = STAFF_ID
and client_notes.ref_agency_deleted= CURRENT_AGENCY
and client_notes.deleted = 1
and activity DateTime (client_notes.last_updated) is within [report date range]
and client record is not deleted

Client ROI

Data Type(s):	Client ROI
Deleted Option:	all; not deleted

When an ROI is added, a corresponding row is added to the to the "common_logs" and "release_of_information" tables.

For "Client ROI" staff activity, all rows are selected from the "common_logs" and "release_of_information" tables, where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 24
and there is a correspondent record in the release_of_information table
and release_of_information.ref_agency = CURRENT_AGENCY
and activity DateTime (release_of_information.date) is within [report date range]
and client ROI is not deleted
and client record is not deleted

Client ROI Deleted

Data Type(s):	Client ROI
Deleted Option:	all; deleted only

When an ROI is deleted, the "release_of_information" table is updated with "release_of_information.deleted = 1 AND release_of_information.ref_agency_deleted = current_agency AND release_of_information.ref_user_updated = staff_id".

For "Client ROI Deleted" staff activity, all rows are selected from the "release_of_information" table where:

release_of_information.ref_user_updated = STAFF_ID
and release_of_information.ref_agency_deleted= CURRENT_AGENCY
and release_of_information.deleted = 1
and activity DateTime (release_of_information.last_updated) is within [report date range]
and client record is not deleted

Coordinated Entry Event

Data Type(s):	Coordinated Entry Event
Deleted Option:	all; not deleted

When an existing Coordinated Entry Event is created or updated, a corresponding row is added to the "common_logs" table.

For "Coordinated Entry Event” staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 9
and a Coordinated Entry Event is manually entered OR inferred
and activity DateTime (clients_log.date) is within [report date range]
and programs.ref_agency = CURRENT_AGENCY where the “programs” is a program linked to the client enrollment that is linked to the Coordinated Entry Event
and the client record/event is not deleted. The referral should not be deleted for cases where the Coordinated Entry Event is inferred.

Coordinated Entry Event (Deleted)

Data Type(s):	Coordinated Entry Event
Deleted Option	all; deleted only

When an existing Coordinated Entry Event is deleted, "client_events" table is updated with "client_events.deleted = 1 AND client_events.ref_agency_deleted = current_agency AND client_events.ref_user_updated = staff_id".

For "Coordinated Entry Event Deleted” staff activity, all rows are selected from the "common_logs" table where:

client_events.ref_user_updated = STAFF_ID
and client record is not deleted
and agency project is not deleted
Coordinated Entry Event is manually entered
- and client_events.last_updated is within [report date range]
- and client_events.ref_agency_deleted= CURRENT_AGENCY
Coordinated Entry Event is inferred
- and referrals.last_updated is within [report date range]
- and referrals.ref_agency_deleted= CURRENT_AGENCY

Manual Personal ID Management - Link

Data Type(s):	Manual Personal ID Management
Deleted Option:	all; not deleted

When a client is linked to another client using the same personal ID in the Link/Unlink clients section, a corresponding row is added to the "client_linking_history" table.

For "Manual Personal ID Management" staff activity, all rows are selected from the "client_linking_history" table where:

client_linking_history.ref_user = STAFF_ID
and client_linking_history.operation_type = 2
and activity DateTime (client_linking_history.added_date) is within [report date range]
and client_linking_history.ref_agency = CURRENT_AGENCY
and the client_linking_history is related to the client record using client_linking_history.ref_client
and the client record is not deleted

Manual Personal ID Management - Unlink

Data Type(s):	Manual Personal ID Management
Deleted Option:	All;Deleted Only

When a client is unlinked from another client with the same personal ID in the Link/Unlink clients section, a corresponding row is added to the "client_linking_history" table.

For "Manual Personal ID Management" staff activity, all rows are selected from the "client_linking_history" table where:

client_linking_history.ref_user = STAFF_ID
and client_linking_history.operation_type = 1
and activity DateTime (client_linking_history.added_date) is within [report date range]
and client_linking_history.ref_agency = CURRENT_AGENCY
and the client_linking_history is related to the client record using client_linking_history.ref_client
and the client record is not deleted

Profile Created

Data Type(s):	Profile Created
Deleted Option:	all; not deleted

When a new client record is added, a corresponding row is added to the "common_logs" table.

For "Profile Created" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 12
and it is the profile creation record
and activity DateTime (common_logs.date) is within [report date range]
and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and the client record is not deleted

Profile Deleted

Data Type(s):	Profile Created, Profile Edited
Deleted Option:	all; deleted only

When the client record is deleted, the "clients" table is updated with "clients.deleted = 1.

For "Profile Deleted" staff activity, all rows are selected from the "clients" table where:

clients.deleted = 1
and clients.last_updated is within [report date range]
and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction

Note: If a client record is merged, the record is marked as deleted as reflected in the "clients" table.

Profile Edited

Data Type(s):	Profile Edited
Deleted Option:	all; not deleted

When an existing client record is updated, a corresponding row is added to the "common_logs" table.

For "Profile Edited" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 12
and it is the profile update record
and activity DateTime (clients_log.date) is within [report date range]
and if the client is private, then clients.ref_agency = CURRENT_AGENCY, otherwise there is no ref_agency restriction
and the client record is not deleted

Program Annual Update

Data Type(s):	Program Annual Update
Deleted Option:	all; not deleted

When a status update is added or an existing status update is updated, a corresponding row is added to the "common_logs" table.

For "Program Annual Update" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 6
and activity DateTime (common_logs.date) is within [report date range]
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and the client's project stay is not deleted
and agency project is not deleted

Program Annual Update Deleted

Data Type(s):	Program Annual Update
Deleted Option:	all; deleted only

When client's project annual update screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Annual Update Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

client_program_demographics.ref_user_updated = STAFF_ID
and client_program_demographics.screen_type = 6
and client_program_demographics.deleted = 1
and client_program_demographics.last_updated is within [report date range]
and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY
and client's project stay is not deleted
and client record is not deleted
and agency project is not deleted

Program Current Living Situation

Data Type(s):	Program Current Living Situation
Deleted Option:	all; not deleted

When a current living situation is added or an existing current living situation is updated, a corresponding row is added to the "common_logs" table.

For "Program Current Living Situation" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 3
and common_logs.status_screen_type= 2
and activity DateTime (common_logs.date) is within [report date range]
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and the client's project stay is not deleted
and agency project is not deleted

Program Current Living Situation Deleted

Data Type(s):	Program Current Living Situation
Deleted Option:	all; deleted only

When a client's project current living situation screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "current living situation Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

client_program_demographics.ref_user_updated = STAFF_ID
and client_program_demographics.screen_type = 3
and client_program_demographics.status_screen_type= 2
and client_program_demographics.deleted = 1
and client_program_demographics.last_updated is within [report date range]
and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY
and client's project stay is not deleted
and client record is not deleted
and agency project is not deleted

Program Enrollment

Data Type(s):	Program Enrollment
Deleted Option:	all; not deleted

When a project enrollment is created or updated, a corresponding row is added to the "common_logs" table.

For "Program Enrollment" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 2
and activity DateTime (common_logs.date) is within [report date range]
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and client project stay is not deleted
and agency project is not deleted

Program Enrollment Deleted

Data Type(s):	Program Enrollment
Deleted Option:	all; deleted only

When client project stay is deleted, "client_programs" table is updated with "client_programs.deleted = 1 AND client_programs.ref_agency_deleted = current_agency AND client_programs.ref_user_updated = staff_id".

For "Program Enrollment Deleted" staff activity, all rows are selected from the "client_programs" table where:

client_programs.ref_user_updated = STAFF_ID
and client_programs.deleted = 1
and client_programs.last_updated is within [report date range]
and clients.ref_agency_deleted = CURRENT_AGENCY
and client record is not deleted
and agency project is not deleted

Program Exit

Data Type(s):	Program Exit
Deleted Option:	all; not deleted

When a project exit is added or an existing exit is updated, a corresponding row is added to the "common_logs" table.

For "Program Exit" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 4
and activity DateTime (common_logs.date) is within [report date range]
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and client project stay is not deleted
and agency project is not deleted

Program Exit Deleted

Data Type(s):	Program Exit
Deleted Option:	all; deleted only

When an exit screen is deleted or deleted exit screen is updated, a corresponding row is added to the "common_logs" table.

For "Program Exit Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

client_program_demographics.ref_user_updated = STAFF_ID
and client_program_demographics.screen_type = 4
and client_program_demographics.deleted = 1
and client_program_demographics.last_updated is within [report date range]
and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY
and client project stay is not deleted
and client record is not deleted
and agency project is not deleted

OR all rows are selected from the "client_programs_log" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 4
and activity DateTime (common_logs.date) is within [report date range]
and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) != "null"
and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) != ""
and JSON_EXTRACT( common_logs.prev_record, "$.program_date" ) IS NOT NULL
and ( JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) = "null"
- or JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) = ""
- or JSON_EXTRACT( common_logs.curr_record, "$.program_date" ) IS NULL
  )
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and client project stay is not deleted
and agency project is not deleted

Program Follow-up

Data Type(s):	Program Follow-Up
Deleted Option:	all; not deleted

When a follow-up screen is added or an existing follow-up screen is updated, a corresponding row is added to the "common_logs" table.

For "Program Follow-up" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 5
and activity DateTime (common_logs.date) is within [report date range]
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and client project stay is not deleted
and agency project is not deleted

Program Follow-up Deleted

Data Type(s):	Program Exit
Deleted Option:	all; deleted only

When client follow-up screen is deleted, the"client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Follow-up Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

client_program_demographics.ref_user_updated = STAFF_ID
and client_program_demographics.screen_type = 5
and client_program_demographics.deleted = 1
and client_program_demographics.last_updated is within [report date range]
and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY
and client project stay is not deleted
and client record is not deleted
and agency project is not deleted

Program Status Update

Data Type(s):	Program Status Update
Deleted Option:	all; not deleted

When a status update is added or an existing status update is updated, a corresponding row is added to the "common_logs" table.

For "Program Status Update" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 10
and common_logs.screen_type = 3
and activity DateTime (common_logs.date) is within [report date range]
and client_programs.ref_agency = CURRENT_AGENCY
and client record is not deleted
and client project stay is not deleted
and agency project is not deleted

Program Status Update Deleted

Data Type(s):	Program Status Update
Deleted Option:	all; deleted only

When a status update screen is deleted, the "client_program_demographics" table is updated with "client_program_demographics.deleted = 1 AND client_program_demographics.ref_agency_deleted = current_agency AND client_program_demographics.ref_user_updated = staff_id".

For "Program Status Update Deleted" staff activity, all rows are selected from the "client_program_demographics" table where:

client_program_demographics.ref_user_updated = STAFF_ID
and client_program_demographics.screen_type = 3
and client_program_demographics.deleted = 1
and client_program_demographics.last_updated is within [report date range]
and client_program_demographics.ref_agency_deleted = CURRENT_AGENCY
and client project stay is not deleted
and client record is not deleted
and agency project is not deleted

Client Referral

Data Type(s):	Client Referral
Deleted Option:	all; not deleted

When a client referral is added, a corresponding row is added to the to the "common_logs" and "referrals" tables.

For "Client Referral" staff activity, all rows are selected from the "common_logs" and "referrals" tables where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 36
and the previous record (prev_record) in the common_log table is equal to null
and there is a correspondent record in the referrals table
and referrals.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and Referral != Upcoming/Scheduled
and referrals record is not deleted
and client record is not deleted

Client Referral Scheduled

Data Type(s):	Client Referral Scheduled
Deleted Option:	all; not deleted

When a client upcoming referral is added, a corresponding row is added to the "common_logs" and "referrals" tables.

For "Client Referral Scheduled" staff activity, all rows are selected from the "common_logs" and "referrals" tables where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 36
and the current record (curr_record) in the common_log table includes is_upcoming = 1
and the current record (curr_record) in the common_log table includes canceled != 1
and the previous record (prev_record) in the common_log table is not equal to null
- or there is no previous record in the common_log table that includes is_upcoming = 1
and referrals.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and referrals record is not deleted
and client record is not deleted

Client Referral Canceled

Data Type(s):	Client Referral Canceled
Deleted Option:	all; deleted only

When Client Scheduled Referral is canceled, the "referrals" table is updated with "referrals.canceled = 1 AND referrals.ref_agency = current_agency AND referrals.ref_user_updated = staff_id" and the correspondent row is added to the "common_logs" table.

For "Client Referral Canceled" staff activity, all rows are selected from the "common_logs" and "referrals" tables where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 36
and the current record (curr_record) in the common_log table includes canceled = 1
and there is no previous record in the common_log table that includes canceled = 1
and referrals.ref_agency = CURRENT_AGENCY
and activity DateTime (common_logs.date) is within [report date range]
and referrals record is not deleted
and client record is not deleted

Client Referral Deleted

Data Type(s):	Client Referral
Deleted Option:	all; deleted only

When Client Referral is deleted, the "referrals" table is updated with "referrals.deleted = 1 AND referrals.ref_agency_deleted = current_agency AND referrals.ref_user_updated = staff_id".

For "Client Referrals Deleted" staff activity, all rows are selected from the "referrals" table where:

referrals.ref_user_updated = STAFF_ID
and referrals.ref_agency_deleted= CURRENT_AGENCY
and referrals.deleted = 1
and Referral != Upcoming/Scheduled
and activity DateTime (referrals.last_updated) is within [report date range]
and client record is not deleted

Service Provided/Service Updated

Data Type(s):	Service Provided, Service Updated
Deleted Option:	all; not deleted

When a new service is added or updated, a corresponding row is added to the "common_logs" table.

For "Service Provided" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 11
and activity DateTime (common_logs.date) is within [report date range]
and client_services record is not deleted
and client_services.ref_agency = CURRENT_AGENCY
and service item record is not deleted
and service record is not deleted
and client record is not deleted

In addition, if the conditions below are true the service is pulled as “Service Provided;” in other cases, the service is pulled as “Service Updated”

and ( IFNULL( JSON_LENGTH( common_logs.prev_record ), 0 ) = 0
- or IF( common_logs.prev_record IN ( ““, [““], “null“ ), 0, 1 ) = 0 )
and JSON_EXTRACT( common_logs.curr_record, "$.id" ) IS NOT NULL

Service Deleted

Data Type(s):	Service Provided, Service Updated
Deleted Option:	all; deleted only

When a service is deleted, the "client_services" table is updated with "client_services.deleted = 1 and client_services.ref_agency_deleted = current_agency" and client_services.ref_user_updated= staff_id".

For "Service Deleted" staff activity, all rows are selected from the "client_services" table where:

client_services.ref_user_updated = STAFF_ID
and client_services.deleted = 1
and client_services.ref_agency_deleted= CURRENT_AGENCY
and client_services.last_updated is within [report date range]
and service item record is not deleted
and service record is not deleted
and client record is not deleted

Unit/Bed Assignment

Data Type(s):	Unit/Bed Assignment
Deleted Option:	all; not deleted

When an existing Unit/Bed Assignment is created or updated, a corresponding row is added to the "common_logs" table.

For "Unit/Bed Assignment (Enrollment)" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 34
and Unit/Bed is Occupied through a direct assignment through an enrollment
and activity DateTime (clients_log.date) is within [report date range]
and unit_occupancy.ref_agency = CURRENT_AGENCY
and the client record/assignment is not deleted

For "Unit/Bed Assignment (Referral)" staff activity, all rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 34
and Unit/Bed is Occupied through a referral to the unit/bed
and related Referral != Upcoming/Scheduled
and activity DateTime (clients_log.date) is within [report date range]
and unit_occupancy.ref_agency = CURRENT_AGENCY
and the client record/assignment is not deleted

For "Unit/Bed Assignment (Pending Assignment)" staff activity will populate data based on referral-connected household members. All rows are selected from the "common_logs" table where:

common_logs.ref_user = STAFF_ID
and common_logs.source_table = 34
and Unit/Bed is Referred
and activity DateTime (clients_log.date) is within [report date range]
and unit_occupancy.ref_agency = CURRENT_AGENCY
and the client record/assignment is not deleted

Unit/Bed Assignment (Deleted)

Data Type(s):	Unit/Bed Assignment
Deleted Option:	All;Deleted Only

When Unit/Bed Assignment is deleted, "unit_occupancy" table is updated with "unit_occupancy.deleted = 1 AND unit_occupancy.ref_agency_deleted = current_agency AND unit_occupancy.ref_user_updated = staff_id".

For "Unit/Bed Assignment (Enrollment) Deleted" staff activity, all rows are selected from the "common_logs" table where:

unit_occupancy.ref_user_updated = STAFF_ID
and unit_occupancy.deleted = 1
and Unit/Bed is Occupied through a direct assignment through enrollment
and unit_occupancy.last_updated is within [report date range]
and unit_occupancy.ref_agency_deleted= CURRENT_AGENCY
and client record is not deleted
and agency project is not deleted

For "Unit/Bed Assignment (Referral) Deleted" staff activity, all rows are selected from the "common_logs" table where:

unit_occupancy.ref_user_updated = STAFF_ID
and unit_occupancy.deleted = 1
and Unit/Bed is Occupied through a referral to the unit/bed
and related Referral != Upcoming/Scheduled
and unit_occupancy.last_updated is within [report date range]
and unit_occupancy.ref_agency_deleted= CURRENT_AGENCY
and client record is not deleted

For "Unit/Bed Assignment (Pending Assignment) Deleted" staff activity will populate data based on referral-connected household members. All rows are selected from the "common_logs" table where:

unit_occupancy.ref_user_updated = STAFF_ID
and unit_occupancy.deleted = 1
and Unit/Bed is Referred
and unit_occupancy.last_updated is within [report date range]
and unit_occupancy.ref_agency_deleted= CURRENT_AGENCY
and client record is not deleted
and agency project is not deleted

Updated: 06/17/2026

[STFF-104] Specification Details

This article outlines the Data Types Specifications for the [STFF-104] Staff Client Data Activity Report.

Data Types Specifications

Assessment

Assessment Deleted

Client Contact Created

Client Contact Edited

Client Contact Deleted

Client Contact Edited

Client Field Interaction

Client Field Interaction (Deleted)

Client File

Client File Deleted

Client Location

Client Location Deleted

Client Note

Client Note Deleted

Client ROI

Client ROI Deleted

Coordinated Entry Event

Coordinated Entry Event (Deleted)

Manual Personal ID Management - Link

Manual Personal ID Management - Unlink

Profile Created

Profile Deleted

Profile Edited

Program Annual Update

Program Annual Update Deleted

Program Current Living Situation

Program Current Living Situation Deleted

Program Enrollment

Program Enrollment Deleted

Program Exit

Program Exit Deleted

Program Follow-up

Program Follow-up Deleted

Program Status Update

Program Status Update Deleted

Client Referral

Client Referral Scheduled

Client Referral Canceled

Client Referral Deleted

Service Provided/Service Updated

Service Deleted

Unit/Bed Assignment