Nudge users who are not entering data

[mahalakshme]

Requirement:

Nudge users who are have not entered data for past 5 days

AC:

• Do in integration service to retain sanity and not to corrupt ETL
• Store custom_query to retrieve users who have not entered data for past 5 days.
• Call the glific API(on Avni) synchrously from integration-service and store the error records in integration service. One user at a time message can be sent.
• Have a window such that the job should not send message to the same users for whom message was sent in the past 23 hours. So somewhere need to store the users data for whom message was sent in the past 23 hours.
• Store the manual_message in table
• Currently we can retain the 4 days timeline to retry for any failed messages.
• For any other missing details refer this spike. Also checkout the commits to implement or cherry-pick.

Old: Ignore:

AC:

• Have a ETL/integration service job to identify the users who meet all the following criteria:
  - Didn't enter/edit any registrations or encounters in the past 5 days
  - The number of days should be configurable easily
  - Should belong to 'Primary Users' user group
  - Should not consider users who have completed endline for all the work orders in their catchment
• Store the users identified above in some ETL/some table temporarily(Needed just for testability)

Technical suggestions:

• Option 1: integration service that executes custom query to figure out the users.
• Option 2: ETL bg job that uses ETL tables to figure out the users
• Option 3: ETL bg job that uses custom query to figure out the users

Approaches discarded because of complexity or suitability: Background job in Avni, Using Avni APIs, Dalgo

Independent of the approach followed, the same ETL and integration service should be usable in Avni as well without forking.

Out of scope:

Sending messages to such users using Glific.

Inputs:

• int., service access to ETL database?
• window to not send msg again - 1 day - run every 10 mins
• • report - to whom sent/failed message
• • job monitoring
• /executeQuery?
• add spike
• add 30 percent estimate

[himeshr]

Possible issue with AC

Condition set 1

• Call the glific API(on Avni) synchrously from integration-service and store the error records in integration service. One user at a time message can be sent.
• Have a window such that the job should not send message to the same users for whom message was sent in the past 23 hours. So somewhere need to store the users data for whom message was sent in the past 23 hours.

Condition set 2

• Store the manual_message in table
• Currently we can retain the 4 days timeline to retry for any failed messages.

If we do conditionSet 1, there is no need to do conditionSet 2, as we'll run the nudge main job to check and send message once every day in a synchronous manner.

[himeshr]

Putting this card on Hold, as we are facing following 2 issues:

• ATECF Glific Application Access
  The Glific application is currently inaccessible, and we’re encountering a "403 Forbidden" error when trying to log in. This impedes our development and testing. User credentials used are for +919632322066 login.

• Parameters for Nudge Users Glific Message Template
  We would also like to know which parameters need to be passed to the Nudge Users Glific Message Template. For example, in the case of the "User Registration" Glific Message Template (Template-id: 333223), we pass the UserId and Password as parameters.

[himeshr]

Deployment steps

• External System config for RWB org

INSERT INTO public.external_system_config (id, organisation_id, uuid, is_voided, version, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time, system_name, config) VALUES (DEFAULT, <org_id>, uuid_generate_v4(), false, 1, 1, 1, now(), now(), 'Glific', '{"phone": "91Number", "baseUrl": "https://api.<org>.glific.com", "password": "<pwd>", "avniSystemUser": "<user>@org"}');

• Integration Config to be done in infra

RWB_INT_SCHEDULE_CRON = 0 0/5 * * * ?
RWB_AVNI_API_USER = "username"
RWB_AVNI_API_PASSWORD = "password"
RWB_AVNI_NUDGE_CUSTOM_QUERY_NAME = "Inactive users"
RWB_AVNI_NUDGE_SINCE_NO_OF_DAYS = 01
RWB_AVNI_NUDGE_WITHIN_NO_OF_DAYS = 01
RWB_AVNI_MESSAGE_TEMPLATE_ID = 542201 

• Insert customQuery for targeted RWB org..

customQueryInsertSql.txt

[mahalakshme]

@himeshr this is unblocked. Credentials in Keeweb. Also tested rwb2023 org in prerelease with the configuration. Template to be used is 'User Nudge'. 1st parameter is users' name, 2nd and 3rd parameters are no of days for which we are checking if they entered any data - it is good to keep it configurable - they have changed it to 3 from 5 days now.

Multiple 'User Nudge' templates will show up. Selection of any should work. Each one created for different languages. All templates in different languages are mapped to same id. Hence based on the language configured for the user, message will get sent automatically in that language - feature of Glific - tested and it is working.

[mahalakshme]

@himeshr have added the parameter details in the above comment. Missed it.

[petmongrels]

• We won't be able to run this query in QA environments as the org name is hardcoded. One way we can solve this and the security issue in general is to make :organisation_id or :db_user as a mandatory parameter in custom queries. The platform can always set the organisation_id/dbuser in the prepared statement. All custom queries must have one of this parameter.
• cc @1t5j0y

[himeshr]

• We won't be able to run this query in QA environments as the org name is hardcoded. One way we can solve this and the security issue in general is to make :organisation_id or :db_user as a mandatory parameter in custom queries. The platform can always set the organisation_id/dbuser in the prepared statement. All custom queries must have one of this parameter.
• cc @1t5j0y

• Will include organisation id, schema_name and db_user as additional queryParams.
• Additionally, will retain db_role context while executing custom query, to prevent cross implementation data access.
• Updated documentation for Custom Query API

[himeshr]

Query to execute to fetch list of users that are to be nudged

QueryParams

• org_db_user => 'rwbni**'
• org_id => 191
• cutOffDate => '2025-02-07'

WITH primary_users as (
    select distinct u.id user_id, u.name first_name, catchment_id
    from users u
             join user_group ug on u.id = ug.user_id and ug.is_voided = false
             join groups g on g.id = ug.group_id and g.is_voided = false
    where g.name = 'Primary Users'
      and u.is_voided = false
),
     work_orders as (
         select i.id wo_id, i.address_id, organisation_id
         from individual i
         where i.is_voided = false
           and i.subject_type_id = (select id
                                    from subject_type
                                    where name = 'Work Order'
                                      and organisation_id = (select id from organisation where db_user = :org_db_user)
                                      and not subject_type.is_voided)
     ),
     closed_work_orders as (select wo.wo_id, wo.address_id
                            from work_orders wo
                                     join encounter e on wo.wo_id = e.individual_id
                            where e.encounter_type_id =
                                  (select id
                                   from encounter_type
                                   where name = 'Work order endline'
                                     and organisation_id = (select id from organisation where db_user = :org_db_user)
                                     and not encounter_type.is_voided)
                              and (e.is_voided is null or e.is_voided = false)
                              and wo.organisation_id = :org_id
                            group by 1, 2
                            having count(e.id) = 1),
     catchments_without_work_orders_or_atleast_one_open_work_order as (
         select c.id
         from catchment c
                  join virtual_catchment_address_mapping_table cam on cam.catchment_id = c.id
                  left join work_orders wo on wo.address_id = cam.addresslevel_id
                  left join closed_work_orders cwo on cwo.address_id = cam.addresslevel_id
         where c.is_voided = false
         group by 1
         having count(wo.wo_id) = null
             OR count(wo.wo_id) > count(cwo.wo_id)
     ),
     active_user_ids as (select (case
                                     when ind.created_date_time > TO_TIMESTAMP(:cutOffDate, 'YYYY-MM-DDTHH24:MI:ss.MS')
                                         then ind.created_by_id end) as cuid,
                                ind.last_modified_by_id              as muid
                         from individual ind
                         where ind.last_modified_date_time > TO_TIMESTAMP(:cutOffDate, 'YYYY-MM-DDTHH24:MI:ss.MS')
                         UNION
                         select (case
                                     when enc.created_date_time > TO_TIMESTAMP(:cutOffDate, 'YYYY-MM-DDTHH24:MI:ss.MS')
                                         then enc.created_by_id end) as cuid,
                                enc.last_modified_by_id              as muid
                         from encounter enc
                         where enc.last_modified_date_time > TO_TIMESTAMP(:cutOffDate, 'YYYY-MM-DDTHH24:MI:ss.MS')
                         order by 1 asc)
select distinct user_id, first_name
from primary_users pu
         join catchments_without_work_orders_or_atleast_one_open_work_order cat on pu.catchment_id = cat.id
where user_id not in (select cuid
                      from active_user_ids
                      union
                      select muid
                      from active_user_ids);

[mahalakshme]

@himeshr We need not insert message_rule and all? We need to mention message template via some configuration?

[mahalakshme]

Himesh reply: No insertion of messageRule, we just have to specify the messageTemplateID in integration rwb-app properties..

rwb.avni.message.template.id=${RWB_AVNI_MESSAGE_TEMPLATE_ID:542201}

[mahalakshme]

@himeshr I didnt yet receive any nudge message, though my user exists in 'RWB 2024 Staging' organisation and I have not done any work in the past one month.

[1t5j0y]

FYI @himeshr and @mahalakshme I just received what looks like a 'nudge' message.

[mahalakshme]

@1t5j0y Yayy!! Saw in the glific webapp. I also received around the same time.

[mahalakshme]

Cases tested with one day window:

• Send msg to all users in the 'Primary User group' when no work done in the past many days
• Farmer registered in the past one day - then dont send message
• Work order registered and endline completed 5 days back - then dont send message
• Send message again after a day when no work done for past few days
• Dont send message to someone not done any work in the past few days and who doesn't belong to 'Primary Users' group

[himeshr]

@mahalakshme Though Joy's profile has English language, message is sent in Hindi
It was supposed to internally use user profile specific template and send the msg right?

FYI @himeshr and @mahalakshme I just received what looks like a 'nudge' message.

[mahalakshme]

@himeshr Yes have sent a mail to Glific team asking about this. Found the issue in Maha contact when I was testing as well.