Screen shots:

payments_final_status is failed and the payment_status is Denied, while we still have
2022-10-10 01:32:00,781 INFO 2022-10-10T01:32:00+00:00 [INFO ] {SmashPig-ConsumePendingQueue::SPCID-0241138935} Storing message with gateway paypal_ec and order ID 134445935.1 in database, which is not the expected result based on the code since just checked payments_initial table, this row did returned failed for payments_final_status, which will return isMessageFailed true, and then isTransactionFailed true, and then skip to store...., also confirmed those two donations were not picked by audit, so pretty weird

Looks like these were recorded in response to an IPN message, not a donation direct from the front-end. Let's see what the IPN looked like and if we need to examine some other fields when recording those.

Ha, find out the reason, that we have the payments_init table later than the time we were checking for that donation status, and currently we if we did not find the record at payments_init table, return not failed, should we return failed instead to skip the record store to civi? (If trxn success, will pick up by audit parse eventually, and this function isTransactionFailed is only been used by the pendingConsume processMessage)

In T320989#8327091, @Ejegg wrote:

Looks like these were recorded in response to an IPN message, not a donation direct from the front-end. Let's see what the IPN looked like and if we need to examine some other fields when recording those.

I think it's a bigger issue that we're not actually doing anything with the valid_statuses key in the paypal config. That key SHOULD be used to discard any IPN where the payment_status is not (in our case) Completed or Reversed. But... there's no code to do that. The IPNs have the payment_status Denied, so we should definitely fix the code to stop sending those to Civi.

Change 844059 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/SmashPig@master] Drop PayPal IPNs outside of valid_statuses list

https://gerrit.wikimedia.org/r/844059

@EMartin We're making a pretty small code change to make this drop 'Denied' donations in the future. Year to date I see 110 donations in that status that might have been incorrectly recorded to CiviCRM. Is it worth doing a batch update and changing the status of those donations in CiviCRM to 'Failed'?

@Ejegg. Is it a great deal of work? If so, I say don't bother if it's fixed going forward. If it is easy, then I'm sure DR would appreciate it, but knowing it is fixed will likely do the trick here.

Change 844059 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Drop PayPal IPNs outside of valid_statuses list

https://gerrit.wikimedia.org/r/844059

@Ejegg could we get a list of the transactions? The DR team can update them manually (as long as setting Contribution Status=canceled is equivalent to 'Failed') - might spare us any confusion should affected donors reach out.

@MBeat On Monday I'll take a stab at doing it automatically. Only about half of the IDs from IPNs with 'Denied' status were actually in Civi when I was doing some experimental lookups earlier, so it may be closer to 50 spurious donations.

Donor in ticket 1189252 / CID 18246037 attempted a new donation via PayPal on 10/21. It's showing in civi as received but the transaction itself in PayPal (transaction 137545892.1) shows as denied.

Flagging incase this was after the fix? Should we advise this donor to retry their donation via a different payment method?

Aha, the latest one for CID 18246037 was pulled in by the audit processor, not the IPN listener like the last ones. Looks like there's one more hole to plug before we can call this ticket done.

Change 849205 had a related patch set uploaded (by Ejegg; author: Ejegg):

[wikimedia/fundraising/tools@master] Ignore non-successful lines in PayPal audit

https://gerrit.wikimedia.org/r/849205

Change 849205 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Ignore non-successful lines in PayPal audit

https://gerrit.wikimedia.org/r/849205

OK, that's the fix for the PayPal audit processor deployed. Going forward we shouldn't see any more of these. I still owe you a list of the bad txn IDs.

I was able to set all the ones from the past year to 'Cancelled' via the Civi API. Here's the list of invoice IDs for posterity:

57968521.1
58998779.1
80729530.1
81541360.1
93016260.1
93992648.1
94693905.1
95981853.1
107843290.1
108533596.1
110482633.1
111069640.1
112600728.1
114393662.1
114624827.1
115468376.2
118933969.1
120699553.1
120828004.1
121319639.1
121394505.1
121394933.1
121413610.1
121421357.1
121424807.1
121425148.1
121437974.1
121441228.1
121442396.1
121451291.1
121458358.1
121498309.1
121564829.1
121570610.1
121588765.1
121593525.1
121607094.1
121611692.1
121612968.1
121616400.1
121621430.1
121625982.1
121627771.1
121643525.1
121649675.1
121657197.1
121660485.1
121662589.1
121669936.1
121670237.1
121675058.1
121677333.1
121689284.1
121691535.1
122418006.1
122489974.1
122775970.1
122781252.1
122782567.1
122989765.1
126163776.1
126299048.1
126400857.1
126413554.1
126426287.1
126431675.1
126452041.1
126468196.1
126481616.1
126498199.1
126540701.1
126566196.1
126567973.1
126606714.1
126615596.1
126664077.1
126739495.1
126743954.1
126752211.1
126790771.1
126817091.1
126924173.1
126943906.1
127132309.1
127174724.1
127197724.1
127260907.1
127334744.1
127364430.1
127391833.1
127539429.1
127841330.1
127884176.1
128010153.1
128485675.1
129840440.1
130124899.1
131599586.1
132769421.1
132848004.1
133025427.1
133097378.1
133114302.1
133497649.1
134445935.1
134996790.1
135087716.1
135853203.1
135918881.1
136154022.1

Thanks, @Ejegg !

Hi @Ejegg! QQ for ya.

For this donor cid 18246037: I see that two of their PayPal donation attempts that were declined at PayPal now show as cancelled in Civi for the October 7th and October 10th dates.

However, the October 22nd attempt (PP transaction id 137545892.1) still show as completed in Civi.

Do we need to just mark this one to "cancelled" in Civi status manually as it seems like it wasn't pulled in?

Apologies if I'm misunderstanding! This one is a tricky one :).