Completed notifications are missing status feed

[Ahmed-Ghanam]

Description

A subset of notification orders that have reached a final status do not have a corresponding status feed.

Detection query

SELECT
  O.creatorname,
  O.processedstatus,
  COUNT(DISTINCT O._id) AS orders_missing_statusfeed
FROM notifications.orders O
WHERE O.created >= TIMESTAMPTZ '2025-06-01 00:00:00+00'
  AND EXISTS (
    SELECT 1
    FROM notifications.emailnotifications E
    WHERE E._orderid = O._id
      AND E.result IN (
        'Failed'::emailnotificationresulttype,
        'Delivered'::emailnotificationresulttype,
        'Failed_TTL'::emailnotificationresulttype,
        'Failed_Bounced'::emailnotificationresulttype,
        'Failed_RecipientReserved'::emailnotificationresulttype,
        'Failed_SupressedRecipient'::emailnotificationresulttype,
        'Failed_RecipientNotIdentified'::emailnotificationresulttype
      )
  )
  AND NOT EXISTS (
    SELECT 1
    FROM notifications.statusfeed SF
    WHERE SF.orderid = O._id
  )
  AND NOT EXISTS (
    SELECT 1
    FROM notifications.smsnotifications S
    WHERE S._orderid = O._id
  )
GROUP BY O.creatorname, O.processedstatus
ORDER BY O.creatorname, O.processedstatus;

Expected behavior

For any order that has at least one email notification with a final result (Delivered, any Failed*, TTL expiration, Bounced) a status feed entry must be created.

Observed Behavior

Orders meeting final result criteria are missing a corresponding status feed entirely.

Impact

• Potential reporting inaccuracies (delivery success vs failure).
• Downstream consumers relying on status feed are blind to final outcome.