Understanding WhatsApp Integration Tables
Description of reflective fields
Message Rule
- entityType - see EntityType enum
- entityTypeId (Avni ID of the entity type like SubjectType, Program, etc)
(From reporting/querying standpoint, these fields provide information about meta entity)
Message Receiver
- receiverType (User, Subject, Group)
- receiverId (Avni ID for User and Subject. never populated for Group)
- externalId (for User and Subject this field gets populated when the message is first sent to the external system. for Group this field is always present)
Message Request (Queue)
- entityId (Subject ID, Program Enrolment ID, Program Encounter ID, Encounter ID)
To understand the status of automatic messages
These messages are triggered when an entity like Individual, Enrolment, or Encounter, is saved.
select o.name, mr.receiver_type, mr.receiver_id, mrq.scheduled_date_time, mrq.delivered_date_time, mrq.delivery_status
from message_request_queue mrq
join message_receiver mr on mrq.message_receiver_id = mr.id
join organisation o on mr.organisation_id = o.id
where mrq.is_voided = false
and mr.is_voided = false and o.is_voided = false
order by o.name, scheduled_date_time desc;
To understand the status of manually triggered messages
select o.name,
mr.receiver_type,
case
when mr.receiver_id is null then 'External id is used'
else mr.receiver_id::text
end
as receiver,
mr.external_id, mrq.scheduled_date_time, mrq.delivered_date_time, mrq.delivery_status, mbm.parameters
from message_request_queue mrq
join manual_broadcast_message mbm on mbm.id = mrq.manual_broadcast_message_id
join message_receiver mr on mrq.message_receiver_id = mr.id
join organisation o on mrq.organisation_id = o.id
where mrq.is_voided = false
and mbm.is_voided = false and o.is_voided = false;
Updated 12 months ago