[CRITICAL] Backwards Compatibility - Moving expenses between reports

[mountiny]

Problem

As part of the workspace chats for paid group policies, we need to make sure actions possible to take in OldDot are correctly reflected in NewDot.

One of them is moving expenses between reports, this influences how the expense reports are shown in newdot.

The high level details are explained in the Design doc section here in terms of what we need to do.

Solution

In OldDot, users can move an expense/money request from one report to another report. With the App IOU report actions approach, this however means we need to handle some additional stuff when such a move occurs.

When an expense is moved from ReportA to ReportB and both reports are on the same policy-expense-chat-enabled policies, we need to in UpdateTransaction:

• Update the reportID in the transactions table (this is already happening)
• Change the reportID of the IOU report action linked to the transaction to the new reportID
  • The transaction thread does not have parent report ID NVP, only the parent report action which was moved to the new report so no need to change anything here
• Recompute the report cache to get updated total
• In case the report we are moving to has been created after the expense/ IOU report action has been created, we need to update the created property of the report and the created timestamp of the CREATED report action of the expense report to be before the transaction created timestamp
  • We can follow what we already do in PayMoneyRequest here

---

• If we move the expense to a report on policy without workspace chat, we will delete the policy expense chat aspects of the expense report. We will delete the report preview, the parent reportID rNVP, and parent report action rNVP.

• If we unreport an expense, let's move the IOU report action to the deleted report so it's not shown on any expense report.

Please bring any questions to the wave6 room, the design doc is not written in a lot of detail but the things we need to do are captured there in plain English

[melvin-bot]

Whoops! This issue is 2 days overdue. Let's get this updated quick!

[melvin-bot]

Eep! 4 days overdue now. Issues have feelings too...

[melvin-bot]

Now this issue is 8 days overdue. Are you sure this should be a Daily? Feel free to change it!

[melvin-bot]

10 days overdue. I'm getting more depressed than Marvin.

[melvin-bot]

12 days overdue now... This issue's end is nigh!

[melvin-bot]

This issue has not been updated in over 14 days. eroding to Weekly issue.

[yuwenmemon]

Looking into this now.

[yuwenmemon]

When an expense is moved from ReportA to ReportB and both reports are on the same policy-expense-chat-enabled policies, we need to in UpdateTransaction

I think we mean ReportTransactions here? Or, am I missing something?

[yuwenmemon]

Thinking of how to break this up into as small pieces as possible. Going to tackle this first:

If we move the expense to a report on policy without workspace chat, we will delete the policy expense chat aspects of the expense report. We will delete the report preview, the parent reportID rNVP, and parent report action rNVP.

[yuwenmemon]

Asked a question about the above here: https://expensify.slack.com/archives/C02MW39LT9N/p1704494359050069

[yuwenmemon]

Asked another question about this:

In case the report we are moving to has been created after the expense/ IOU report action has been created, we need to update the created property of the report and the created timestamp of the CREATED report action of the expense report to be before the transaction created timestamp

[yuwenmemon]

Okay trying to summarize what this looks like presently so I can understand what we need to fix. I currently create a Money Request in NewDot. This creates the following report 115475633052248:

sqlite> select * from reports where reportID=115475633052248;
   created = 2024-01-05 23:28:50
 submitted = 2024-01-05 23:28:50
  approved = 
  reportID = 115475633052248
 accountID = 32
 managerID = 2
reportName = Expense Report #115475633052248
     state = 0
     total = -500
  currency = USD
cachedData = {"submitted":"","submittedTo":"","approved":"","unapproved":"","reimbursed":"","shared":"","type":"expense","retracted":"","expensify_policyID":"D5885A35873BF696","policyLastModified":1704406113036338,"isSmartReport":false,"cacheComputedBy":32,"inbox":{"violations":{"oldestExpenseDate":null,"newestExpenseDate":null,"maxAge":null,"potentialRTERViolations":[],"cacheableViolationTypes":["violation"],"ignoredWarnings":[],"hasTransactionsWithDifferentCurrency":false,"violationsReportAmount":-500,"totalViolations":1},"policyType":"corporate","firstSubmittedDate":"2024-01-05 23:28:50","wasComputedInReportCache":true},"reimburserEmail":"[email protected]","queuedForAutoExport":{"queued":false,"connections":[],"exportFailed":false,"failedConnections":[]},"hasBeenExportedToIntegration":false,"wasReimbursedViaACH":false,"statementCardID":null,"statementBank":null,"statementPeriodEndDay":null,"isWaitingOnBankAccount":false,"isABAReimbursed":false,"expectedReimbursementDate":null,"canMarkReimbursementReceived":true,"hasReimbursableTotal":true,"hasTransactions":true,"hasBillableTransactions":false,"reportOwners":[],"managerOnVacation":"","isSmartReportOnFile":false,"invoiceID":0,"billID":0}
    status = 0

sqlite> select * from reportNameValuePairs where reportID=115475633052248;
reportID         name                     value                                     
---------------  -----------------------  ------------------------------------------
115475633052248  expensify_policyID       D5885A35873BF696                          
115475633052248  lastReadTime_32          2024-01-05 23:28:50.407                   
115475633052248  notificationPreferences  {"2":"hidden","32":"hidden","46":"hidden"}
115475633052248  parentReportActionID     7370303887022919                          
115475633052248  parentReportID           7352862044010264                          
115475633052248  type                     expense       

The transaction 2589566964639939880 is on this report:

sqlite> select * from transactions where reportID = 115475633052248;
created     transactionID        cardID  reportID         receiptID  amount  merchant  mcc  state  comment              tag  externalID  memo  inserted             currency  modifiedCreated  modifiedAmount  modifiedMerchant  modifiedMCC  category  transactionHash                               modifiedCurrency
----------  -------------------  ------  ---------------  ---------  ------  --------  ---  -----  -------------------  ---  ----------  ----  -------------------  --------  ---------------  --------------  ----------------  -----------  --------  --------------------------------------------  ----------------
2024-01-05  2589566964639939880  31      115475633052248             -500    FIVERRRR       -3     {"comment":"Fiver"}                         2024-01-05 23:28:50  USD                                                                                 hash6798A0234119C3CFA6D3FB7F6C73E59E9C5264B3                  
sqlite> 

And it has an IOU report action that corresponds to it:

sqlite> select * from reportActions where reportActionID=8969807020746277047;
created                  reportID         accountID  action  message                                                       reportActionID     
-----------------------  ---------------  ---------  ------  ------------------------------------------------------------  -------------------
2024-01-05 23:28:50.406  115475633052248  32         IOU     {"IOUReportID":115475633052248,"IOUTransactionID":"258956696  8969807020746277047
                                                             4639939880","amount":500,"comment":"Fiver","currency":"USD",                     
                                                             "lastModified":"2024-01-05 23:28:50.406","participantAccount                     
                                                             IDs":[32,0],"type":"create"}    

That Money Request has a transaction thread 4028651028712853:

sqlite> select * from reports where reportID=4028651028712853;
   created = 2024-01-05 23:28:50
 submitted = 
  approved = 
  reportID = 4028651028712853
 accountID = 0
 managerID = 
reportName = Chat Report
     state = 0
     total = 0
  currency = USD
cachedData = 
    status = 0

sqlite> select * from reportNameValuePairs where reportID=4028651028712853;
reportID          name                     value                                     
----------------  -----------------------  ------------------------------------------
4028651028712853  expensify_policyID       D5885A35873BF696                          
4028651028712853  lastReadTime_32          2024-01-05 23:36:08.261                   
4028651028712853  notificationPreferences  {"2":"hidden","32":"hidden","46":"hidden"}
4028651028712853  parentReportActionID     8969807020746277047                       
4028651028712853  type                     chat     

I now change the transaction to go on another report in the same policy.

New reportID: 8616140694773666

If I click on the IOU report action, it still takes me to 115475633052248:

Makes sense because the IOU report action is unchanged:

sqlite> select * from reportActions where reportActionID=8969807020746277047;
created                  reportID         accountID  action  message                                                       reportActionID     
-----------------------  ---------------  ---------  ------  ------------------------------------------------------------  -------------------
2024-01-05 23:28:50.406  115475633052248  32         IOU     {"IOUReportID":115475633052248,"IOUTransactionID":"258956696  8969807020746277047
                                                             4639939880","amount":500,"comment":"Fiver","currency":"USD",                     
                                                             "lastModified":"2024-01-05 23:28:50.406","participantAccount                     
                                                             IDs":[32,0],"type":"create"}  

So this makes sense to me:

Change the reportID of the IOU report action linked to the transaction to the new reportID

We need to update the IOUReportID in the JSON above to be the new reportID.

Looking at the old report:

   created = 2024-01-05 23:28:50
 submitted = 2024-01-05 23:28:50
  approved = 
  reportID = 115475633052248
 accountID = 32
 managerID = 2
reportName = Expense Report #115475633052248
     state = 0
     total = 0
  currency = USD
cachedData = 
    status = 0

... All good with the report object itself in the db ✅

sqlite> select * from reportNameValuePairs where reportID=115475633052248;
reportID         name                     value                                     
---------------  -----------------------  ------------------------------------------
115475633052248  expensify_policyID       D5885A35873BF696                          
115475633052248  lastReadTime_32          2024-01-06 00:14:19.052                   
115475633052248  notificationPreferences  {"2":"hidden","32":"hidden","46":"hidden"}
115475633052248  parentReportActionID     7370303887022919                          
115475633052248  parentReportID           7352862044010264                          
115475633052248  type                     expense  

... rNVPs for the old report are unchanged

The transaction itself gets the reportID changed to the new one:

sqlite> select * from transactions where transactionID=2589566964639939880;
created     transactionID        cardID  reportID          receiptID  amount  merchant  mcc  state  comment              tag  externalID  memo  inserted             currency  modifiedCreated  modifiedAmount  modifiedMerchant  modifiedMCC  category       transactionHash                               modifiedCurrency
----------  -------------------  ------  ----------------  ---------  ------  --------  ---  -----  -------------------  ---  ----------  ----  -------------------  --------  ---------------  --------------  ----------------  -----------  -------------  --------------------------------------------  ----------------
2024-01-05  2589566964639939880  31      8616140694773666             -500    FIVERRRR       3      {"comment":"Fiver"}                         2024-01-05 23:28:50  USD                                                                       Uncategorized  hash6798A0234119C3CFA6D3FB7F6C73E59E9C5264B3  USD    

And we do have another IOU report action for it, this happens here:

sqlite> select * from reportActions where action="IOU" AND JSON_EXTRACT(message, '$.IOUTransactionID') = "2589566964639939880";
created                  reportID          accountID  action  message                                                       reportActionID     
-----------------------  ----------------  ---------  ------  ------------------------------------------------------------  -------------------
2024-01-06 00:13:40.412  8616140694773666  32         IOU     {"IOUReportID":8616140694773666,"IOUTransactionID":"25895669  3054177841806116451
                                                              64639939880","amount":-500,"comment":"{\"comment\":\"Fiver\"                     
                                                              }","currency":"USD","lastModified":"2024-01-06 00:13:40.412"                     
                                                              ,"participantAccountIDs":[32,2],"type":"create"}                                 

2024-01-05 23:28:50.406  115475633052248   32         IOU     {"IOUReportID":115475633052248,"IOUTransactionID":"258956696  8969807020746277047
                                                              4639939880","amount":500,"comment":"Fiver","currency":"USD",                     
                                                              "lastModified":"2024-01-05 23:28:50.406","participantAccount                     
                                                              IDs":[32,0],"type":"create"}   

And then looking at the new report there is no cachedData, as with the old report:

sqlite> select * from reports where reportID="8616140694773666";
   created = 2024-01-06 00:12:08
 submitted = 
  approved = 
  reportID = 8616140694773666
 accountID = 32
 managerID = 
reportName = New Report To Transfer Expenses To
     state = 0
     total = -500
  currency = USD
cachedData = 
    status = 0

After the cachedData gets recomputed, they both show up in the workspace chat, although the old one now shows as "TBD":

... This makes me think if we don't have any more expense left on the report we need to delete the report preview action as well.

[yuwenmemon]

Okay so will tackle this one first:

Change the reportID of the IOU report action linked to the transaction to the new reportID

[yuwenmemon]

Have a WIP PR up, just to make sure I'm on the right track for tackling the tight scope of "Change the reportID of the IOU report action linked to the transaction to the new reportID"

[yuwenmemon]

Looking at:

Recompute the report cache to get updated total

I believe we already do this here:

https://github.com/Expensify/Auth/blob/63f66f1a25f96329e719ad13a1a4739c531d2be3/auth/lib/Report.cpp#L2405-L2418

So I think the above PR which tackles:

Change the reportID of the IOU report action linked to the transaction to the new reportID

... is ready to pretty up. And then as a follow up we can do the following:

If we move the expense to a report on policy without workspace chat, we will delete the policy expense chat aspects of the expense report. We will delete the report preview, the parent reportID rNVP, and parent report action rNVP.

and

If we unreport an expense, let's move the IOU report action to the deleted report so it's not shown on any expense report.

...in separate follow-up PRs - what do you think of this plan @mountiny? If 👍 I'll add tests to the above WIP PR and get it ready for review.

[mountiny]

I love this plan, thanks for digging into this one!

[melvin-bot]

Triggered auto assignment to @greg-schroeder (NewFeature), see https://stackoverflowteams.com/c/expensify/questions/14418#:~:text=BugZero%20process%20steps%20for%20feature%20requests for more details.

[yuwenmemon]

PR is approved for this! @flodnv had a question but I think we answered it! ....Should I just merge?

[yuwenmemon]

Closing this in favor of https://github.com/Expensify/Expensify/issues/364642

Moving expenses between reports has been deployed 🎉