No notifications generated for unreachable recipients

[Ahmed-Ghanam]

Description

When the Notifications API processes an order that should be used to send both email and SMS notifications for a single recipient, and that recipient has no registered contact information at the time of processing, the API currently does not create either an email or SMS notification. However, the expected behavior is to create one email notification with the status Failed_RecipientNotIdentified and one SMS notification with the same status. These notifications are essential for generating a complete and accurate status feed. Without them, the end user receives an incomplete status feed.

Queries to further investigation

• Get identifiers for both main orders and reminders:

SELECT
	ORDERCHAIN ->> 'OrderId' AS ORDERALTERNATEID
FROM
	NOTIFICATIONS.ORDERSCHAIN
WHERE
	ORDERCHAIN @? '$.Recipient.*.ChannelSchema ? (@ == 4)'
UNION
SELECT
	REM ->> 'OrderId'
FROM
	NOTIFICATIONS.ORDERSCHAIN
	CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(
		CASE
			WHEN JSONB_TYPEOF(ORDERCHAIN -> 'Reminders') = 'array' THEN ORDERCHAIN -> 'Reminders'
			ELSE '[]'::JSONB
		END
	) REM
WHERE
	REM @? '$.Recipient.*.ChannelSchema ? (@ == 4)'

• Get details for orders and reminders:

SELECT
	COUNT(O._ID),
	O.CREATORNAME,
	O.PROCESSEDSTATUS,
	O.TYPE
FROM
	NOTIFICATIONS.ORDERS O
WHERE
	O.ALTERNATEID IN (
		SELECT
			ORDERALTERNATEID::UUID
		FROM
			(
				SELECT
					ORDERCHAIN ->> 'OrderId' AS ORDERALTERNATEID
				FROM
					NOTIFICATIONS.ORDERSCHAIN
				WHERE
					ORDERCHAIN @? '$.Recipient.*.ChannelSchema ? (@ == 4)'
				UNION
				SELECT
					REM ->> 'OrderId'
				FROM
					NOTIFICATIONS.ORDERSCHAIN
					CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(
						CASE
							WHEN JSONB_TYPEOF(ORDERCHAIN -> 'Reminders') = 'array' THEN ORDERCHAIN -> 'Reminders'
							ELSE '[]'::JSONB
						END
					) REM
				WHERE
					REM @? '$.Recipient.*.ChannelSchema ? (@ == 4)'
			)
	)
GROUP BY
	O.CREATORNAME,
	O.PROCESSEDSTATUS,
	O.TYPE;