Random messages displayed in LHN and unable to discard

[m-natarajan]

If you haven’t already, check out our contributing guidelines for onboarding and email contributors@expensify.com to request to join our Slack channel!

---

Version Number:
Reproducible in staging?: Needs Reproduction
Reproducible in production?: Needs Reproduction
If this was caught during regression testing, add the test name, ID and link from TestRail:
Email or phone of affected tester (no customers):
Logs: https://stackoverflow.com/c/expensify/questions/4856
Expensify/Expensify Issue URL:
Issue reported by: @coleaeason
Slack conversation: https://expensify.slack.com/archives/C05LX9D6E07/p1719842019363059

Action Performed:

1. Go to staging.new.expensify.com
2. Observe the LHN

Expected Result:

Messages displayed in LHN as per priority mode

Actual Result:

Random messages displayed in LHN and unable to discard it

Workaround:

Unknown

Platforms:

Which of our officially supported platforms is this issue occurring on?

• Android: Native
• Android: mWeb Chrome
• iOS: Native
• iOS: mWeb Safari
• MacOS: Chrome / Safari
• MacOS: Desktop

Screenshots/Videos

Add any screenshot/video evidence

View all open jobs on GitHub

[MelvinBot]

This has been labelled "Needs Reproduction". Follow the steps here: https://stackoverflowteams.com/c/expensify/questions/16989

[melvin-bot]

Triggered auto assignment to @sonialiap (Bug), see https://stackoverflow.com/c/expensify/questions/14418 for more details. Please add this bug to a GH project, as outlined in the SO.

[melvin-bot]

Triggered auto assignment to @jasperhuangg (AutoAssignerNewDotQuality)

[m-natarajan]

Tagging this issue #44683 as per this comment https://expensify.slack.com/archives/C05LX9D6E07/p1719853583621449?thread_ts=1719842019.363059&cid=C05LX9D6E07

[deetergp]

I'll take this one @jasperhuangg, I'm pretty sure I know why the expense report related ones are notifying.

[deetergp]

When Kortney copilots into accounting@expensify.com and exports expense reports to our accounting integration, it generates two report actions with the same exact timestamp: BILLABLEDELEGATE and EXPORTINTEGRATION because these are both technically visible report actions, when we run this subquery for either report action, it returns the same created time which is always going to be more recent than anyone's lastReadTime for that report and so these report actions get delivered as read and the unread reports clutter everyone's LHN.

scott@db2.rno:~$ sudo readdb.sh "SELECT * FROM reportActions WHERE reportID = 7468358293046644 ORDER BY created;"
created                  reportID          accountID  action             message                                                                                                                                                                                                                                                                                   reportActionID
-----------------------  ----------------  ---------  -----------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------
2024-06-11 19:52:11.356  7468358293046644  297463     CREATED                                                                                                                                                                                                                                                                                                      8987734204827604593
2024-06-11 19:52:11.359  7468358293046644  297463     IOU                {"IOUReportID":7468358293046644,"IOUTransactionID":"1275664852902458768","amount":0,"comment":"","currency":"USD","lastModified":"2024-06-11 19:52:11.359","participantAccountIDs":[297463,0],"type":"create"}                                                                            1977781071499759992
2024-06-12 08:41:35.675  7468358293046644  297463     SUBMITTED          {"cc":"","harvesting":true,"harvestingGUID":"0CFE78B25EBE2A0A66694e5a32265","hasExternalComment":false,"lastModified":"2024-06-12 08:41:35.675","message":"","to":"puneet@expensify.com","workflow":"BASIC"}                                                                              9080892226874754453
2024-06-13 16:46:59.361  7468358293046644  778531     APPROVED           {"IOUReportID":7468358293046644,"amount":1000,"currency":"USD","html":"approved $10.00","lastModified":"2024-06-13 16:46:59.361"}                                                                                                                                                         3284189292439783785
2024-06-13 16:47:06.207  7468358293046644  10903701   REIMBURSED         {"accountNumber":"XXXXXX8990","addressName":"CIBC Expensify Inc. Test Account","automaticAction":true,"creditBankAccountNumber":"9345","expectedDate":"2024-06-14","lastModified":"2024-06-13 16:47:06.207","method":"Fast_ACH","routingNumber":"071006486"}                              6642415101504022780
2024-06-27 22:37:28.511  7468358293046644  9713816    EXPORTINTEGRATION  {"automaticAction":false,"delegate":"rhindel.lleva-contractor@mossadams.com","label":"All Transaction Data Report (Expensify/Zedra/MA/SVFG)","lastModified":"2024-06-27 22:37:28.511","markedManually":false,"nonReimbursableUrls":null,"reimbursableUrls":null,"type":"exportTemplate"}  5731281980129695591
2024-06-27 22:37:28.511  7468358293046644  10389575   BILLABLEDELEGATE   {"actionTaken":"EXPORTINTEGRATION","forAccountID":9713816}                                                                                                                                                                                                                                9223372026415719862
2024-07-01 17:56:08.295  7468358293046644  9645353    EXPORTINTEGRATION  {"automaticAction":false,"delegate":"kortney@expensify.com","label":"All Transaction Data Report (Expensify/Zedra/MA/SVFG)","lastModified":"2024-07-01 17:56:08.295","markedManually":false,"nonReimbursableUrls":null,"reimbursableUrls":null,"type":"exportTemplate"}                   4262162027559088473
2024-07-01 17:56:08.295  7468358293046644  5726973    BILLABLEDELEGATE   {"actionTaken":"EXPORTINTEGRATION","forAccountID":9645353}                                                                                                                                                                                                                                9223372026415860932
scott@db2.rno:~$ getemail.sh 5726973
kortney@expensify.com
scott@db2.rno:~$ getemail.sh 9645353
accounting@expensify.com

[deetergp]

scott@db2.rno:~$ sudo readdb.sh "
>     SELECT MAX(created)
>     FROM reportActions
>     WHERE JSON_VALID(message)
>     AND reportID = 7468358293046644
>     AND reportActionID != 9223372026415860932
>     AND JSON_TYPE(message, '$.deleted') IS NULL
>     AND JSON_TYPE(message, '$.whisperedTo') IS NULL
>     AND COALESCE(message->>'$.moderationDecisions[#-1].decision', '') != 'pendingRemove'
> ;"
MAX(created)
-----------------------
2024-07-01 17:56:08.295

scott@db2.rno:~$ sudo readdb.sh "
>     SELECT MAX(created)
>     FROM reportActions
>     WHERE JSON_VALID(message)
>     AND reportID = 7468358293046644
>     AND reportActionID != 4262162027559088473
>     AND JSON_TYPE(message, '$.deleted') IS NULL
>     AND JSON_TYPE(message, '$.whisperedTo') IS NULL
>     AND COALESCE(message->>'$.moderationDecisions[#-1].decision', '') != 'pendingRemove'
> ;"
MAX(created)
-----------------------
2024-07-01 17:56:08.295

[deetergp]

One solution could be to include a clause in that query to only include report actions whose created time is older than the action being sent. But I think the best solution is to just exclude any of these ESM-supported report actions from the list.

scott@db2.rno:~$ sudo readdb.sh "
>     SELECT MAX(created)
>     FROM reportActions
>     WHERE JSON_VALID(message)
>     AND reportID = 7468358293046644
>     AND action NOT IN ('BILLABLEDELEGATE', 'EXPORTINTEGRATION')
>     AND JSON_TYPE(message, '$.deleted') IS NULL
>     AND JSON_TYPE(message, '$.whisperedTo') IS NULL
>     AND COALESCE(message->>'$.moderationDecisions[#-1].decision', '') != 'pendingRemove'
> ;"
MAX(created)
-----------------------
2024-06-13 16:47:06.207

[deetergp]

Here it is with all of them excluded:

scott@db2.rno:~$ sudo readdb.sh "
>     SELECT MAX(created)
>     FROM reportActions
>     WHERE JSON_VALID(message)
>     AND reportID = 7468358293046644
>     AND action NOT IN ('APPROVED', 'BILLABLEDELEGATE', 'CHANGEFIELD', 'CHANGEPOLICY', 'CHANGETYPE', 'DELEGATESUBMIT', 'EXPORTCSV', 'EXPORTINTEGRATION', 'FORWARDED', 'MANAGERATTACHRECEIPT', 'MANAGERDETACHRECEIPT', 'POLICYCHANGELOG_ADD_APPROVER_RULE', 'POLICYCHANGELOG_ADD_BUDGET', 'POLICYCHANGELOG_ADD_CATEGORY', 'POLICYCHANGELOG_ADD_CUSTOM_UNIT_RATE', 'POLICYCHANGELOG_ADD_EMPLOYEE', 'POLICYCHANGELOG_ADD_INTEGRATION', 'POLICYCHANGELOG_ADD_REPORT_FIELD', 'POLICYCHANGELOG_ADD_TAG', 'POLICYCHANGELOG_DELETE_ALL_TAGS', 'POLICYCHANGELOG_DELETE_APPROVER_RULE', 'POLICYCHANGELOG_DELETE_BUDGET', 'POLICYCHANGELOG_DELETE_CATEGORY', 'POLICYCHANGELOG_DELETE_CUSTOM_UNIT', 'POLICYCHANGELOG_DELETE_CUSTOM_UNIT_RATE', 'POLICYCHANGELOG_DELETE_CUSTOM_UNIT_SUB_RATE', 'POLICYCHANGELOG_DELETE_EMPLOYEE', 'POLICYCHANGELOG_DELETE_INTEGRATION', 'POLICYCHANGELOG_DELETE_REPORT_FIELD', 'POLICYCHANGELOG_DELETE_TAG', 'POLICYCHANGELOG_IMPORT_CUSTOM_UNIT_RATES', 'POLICYCHANGELOG_IMPORT_TAGS', 'POLICYCHANGELOG_INDIVIDUAL_BUDGET_NOTIFICATION', 'POLICYCHANGELOG_INVITETOROOM', 'POLICYCHANGELOG_LEAVEROOM', 'POLICYCHANGELOG_LEAVE_POLICY', 'POLICYCHANGELOG_REMOVEFROMROOM', 'POLICYCHANGELOG_SET_AUTO_JOIN', 'POLICYCHANGELOG_SET_CATEGORY_NAME', 'POLICYCHANGELOG_SHARED_BUDGET_NOTIFICATION', 'POLICYCHANGELOG_UPDATE_ACH_ACCOUNT', 'POLICYCHANGELOG_UPDATE_APPROVER_RULE', 'POLICYCHANGELOG_UPDATE_AUDIT_RATE', 'POLICYCHANGELOG_UPDATE_AUTOHARVESTING', 'POLICYCHANGELOG_UPDATE_AUTOREPORTING_FREQUENCY', 'POLICYCHANGELOG_UPDATE_AUTOREIMBURSEMENT', 'POLICYCHANGELOG_UPDATE_BUDGET', 'POLICYCHANGELOG_UPDATE_CATEGORY', 'POLICYCHANGELOG_UPDATE_CURRENCY', 'POLICYCHANGELOG_UPDATE_CUSTOM_UNIT', 'POLICYCHANGELOG_UPDATE_CUSTOM_UNIT_RATE', 'POLICYCHANGELOG_UPDATE_CUSTOM_UNIT_SUB_RATE', 'POLICYCHANGELOG_UPDATE_DEFAULT_BILLABLE', 'POLICYCHANGELOG_UPDATE_DEFAULT_REIMBURSABLE', 'POLICYCHANGELOG_UPDATE_DEFAULT_TITLE', 'POLICYCHANGELOG_UPDATE_DEFAULT_TITLE_ENFORCED', 'POLICYCHANGELOG_UPDATE_DISABLED_FIELDS', 'POLICYCHANGELOG_UPDATE_EMPLOYEE', 'POLICYCHANGELOG_UPDATE_FIELD', 'POLICYCHANGELOG_UPDATE_MANUAL_APPROVAL_THRESHOLD', 'POLICYCHANGELOG_UPDATE_MAX_EXPENSE_AMOUNT', 'POLICYCHANGELOG_UPDATE_MAX_EXPENSE_AMOUNT_NO_RECEIPT', 'POLICYCHANGELOG_UPDATE_NAME', 'POLICYCHANGELOG_UPDATE_OWNERSHIP', 'POLICYCHANGELOG_UPDATE_REIMBURSEMENT_CHOICE', 'POLICYCHANGELOG_UPDATE_REPORT_FIELD', 'POLICYCHANGELOG_UPDATE_TAG', 'POLICYCHANGELOG_UPDATE_TAG_ENABLED', 'POLICYCHANGELOG_UPDATE_TAG_LIST', 'POLICYCHANGELOG_UPDATE_TAG_LIST_NAME', 'POLICYCHANGELOG_UPDATE_TAG_NAME', 'POLICYCHANGELOG_UPDATE_TIME_ENABLED', 'POLICYCHANGELOG_UPDATE_TIME_RATE', 'REIMBURSEMENTDELAYED', 'REIMBURSEMENTACHCANCELED', 'MARKEDREIMBURSED', 'ACTIONMARKEDREIMBURSEDFROMINTEGRATION', 'SELECTEDFORRANDOMAUDIT', 'SUBMITTED', 'SHAREREPORT', 'UNHOLD', 'UNSHAREREPORT', 'STRIPEPAID', 'TAKECONTROL', 'UNAPPROVED')
>     AND JSON_TYPE(message, '$.deleted') IS NULL
>     AND JSON_TYPE(message, '$.whisperedTo') IS NULL
>     AND COALESCE(message->>'$.moderationDecisions[#-1].decision', '') != 'pendingRemove'
> "
MAX(created)
-----------------------
2024-06-13 16:47:06.207

[deetergp]

Not sure why this didn't close since the PR was deployed to production five hours ago and the email script was definitely run. Weird.

I have reached out to @kortneywynn and asked that she ping me the next time she copilots into accounting@expensify.com and exports expense reports. Will follow up here once that happens and hopefully put this issue to bed for good.

[melvin-bot]

@deetergp, @sonialiap Uh oh! This issue is overdue by 2 days. Don't forget to update your issues!

[deetergp]

Daily Update

• We discovered what was causing the EXPORTINTEGRATION report actions to mark the chats as unread and fixed it here, but may have introduced a new bug that is causing this test to be flaky when we run the tests in Github Actions.

Next steps

Fix it. Obivously! 😅

[muttmuure]

Looks like latest investigations are in this thread: https://expensify.slack.com/archives/C05LX9D6E07/p1722550695230219?thread_ts=1722533224.423589&cid=C05LX9D6E07

[deetergp]

Yes, @muttmuure 👍 The tl;dr is that accounting@expensify.com exported ~500 reports yesterday and some of them did become unread for a few users. I looked into the ones Puneet & JMills had gotten and the good news is that each of their lastReadTimes were updated on the back and as appropriate. The bad news while we found proof in the logs that we were queueing their last read times to go out as onyx updates in Pusher, for whatever reason, the updates did not make it to their NewDot clients, so the reports became unread when they received the EXPORTINTEGRATION system message.

[muttmuure]

Latest update here: https://expensify.slack.com/archives/C05LX9D6E07/p1722883688176339?thread_ts=1722533224.423589&cid=C05LX9D6E07

[deetergp]

It looks like the latest PR finally took care of this issue. I think we can finally close this one out 🎉

[muttmuure]

Wooo!

[deetergp]

We unfortunately had to revert the PR that fixed this issue because adding IOU actions to Enhanced System Messages broke one of our bottom up flows. By setting an updated lastReadTime for IOUs, we ended up preventing IOU from appearing in any NotifyOfflineUsersAboutActivity emails which one of the key ways we hope to bring more users into the fold.

@puneetlath and I are investigating alternative ways to fix this issue in a way that does not break things or cause other issues.

[melvin-bot]

Triggered auto assignment to @joekaufmanexpensify (Bug), see https://stackoverflow.com/c/expensify/questions/14418 for more details. Please add this bug to a GH project, as outlined in the SO.

[sonialiap]

@joekaufmanexpensify I'm OOO Aug 19-30, adding leave buddy.
Status: engineering is reworking the solution. At the moment no action necessary for BZ

[joekaufmanexpensify]

Sounds good! Also @deetergp do we expect the fix for this to be entirely in auth? If so, I wonder if this even needs a BZ, as there wouldn't really be any payment we'd be processing.

[joekaufmanexpensify]

Still pending input from Scott

[joekaufmanexpensify]

@deetergp is this auth PR the last thing needed in this issue?

[joekaufmanexpensify]

Auth PR was deployed. Just pending input from @deetergp on if this one is now all set.

[muttmuure]

I might still be seeing this with workspace chats that were created by setting a preferred domain group:

https://expensify.slack.com/archives/C05LX9D6E07/p1724243554783079?thread_ts=1723722469.695359&cid=C05LX9D6E07

[joekaufmanexpensify]

Interesting. I see discussion of another issue in that thread. You think it might be related to this instead?

[joekaufmanexpensify]

Also, as an fyi, I am now also OOO until Monday. I don't think this needs a tertiary BZ member as there is no frontend PR(s) here. So should be nothing for BZ to do while I am out. Please bring to slack if that proves to not be correct.

[deetergp]

I'll be diving back into this today.

[joekaufmanexpensify]

Sounds good. So there are more PR(s) left for this one?

[deetergp]

There are not currently any PRs pending, but Matt just got hit with a bunch of unreads in his LHN, so there may be some soon.

[deetergp]

I think we're going to create a new issue for the latest batch that have popped up. Let's go ahead and close this one.

[joekaufmanexpensify]

Sounds good. TY!