Openfoodnetwork: Stripe Payment Processed through wrong Enterprise (Aus)

[emilyjeanrogers]

Description

A customer of Warialda Belted Galloway Beef has had a payment charged to them from Warialda's Stripe account for a product from McIvor Beef's shop.

The Order is R221166673 and is related to a transaction on McIvor Beef's shopfront for Dried Chorizo 130g
The above Order number appears in the customer's Stripe transaction
The Order does not appear in Warialda Belted Galloway Beef's OFN account
The Stripe Payment appears in Warialda Belted Galloway Beef's Stripe account

Detailed Summary:

• A customer of Warialda Belted Galloway Beef has had a payment charged to them from Warialda's Stripe account for a product from McIvor Beef's shop.
• We don't know if the customer actually ordered this product from McIvor Beef - emailed and waiting for reply
• The payment is apparently on Warialda's Stripe account - waiting for a screen grab to confirm
• McIvor and Warialda DO have a relationship: both permit each other to add to O/C and Inventory.
• McIvor DO have a current open order cycle with that product in it
• The transaction is in McIvor's Orders as having been paid for (though we assume it went through Warialda's Stripe, not McIvor's)

Expected Behavior

Actual Behaviour

Steps to Reproduce

Animated Gif/Screenshot

Will provide further details once it becomes available

Severity

S2?

[luisramos0]

I investigated this a little. I confirm there is an order in McIvor Beef's shopfront with a payment done with the Stripe payment method of Warialda Belted Galloway Beef. It all looks as if the Warialda's payment method was at the time of the order available and active for the McIvor enterprise. The database though shows that the stripe payment method configured for McIvor's shop is their own and not Warialda's...

I tried to replicate the problem but I didnt manage to get anything out of it.

How did this happen? Maybe we should ask the managers of the enterprises if they changed something in the payments configuration recently?

[emilyjeanrogers]

@luisramos0 I am waiting for more information from the shops and customer. Could this have something to do with a shopper having both shops open at the same time? They are both meat producers in the same region, so it is possible the customer was shopping in parallel.

Also, can you confirm and tag sev as appropriate? I wasn't sure, thinking S2 and possibly V3 regression along with other Stripe issues we are seeing?

[luisramos0]

We agreed to tag this S1 in the delivery train meeting because this could be very serious where payments are taken from the wrong stripe accounts... hopefully it will just be a mix up of some sort specific to the fact these two enterprises are related in some way...
It needs to be investigated.

[sauloperez]

I tried tracing what happened to this payment method by checking the logs but there's too much happening in a single day and as a result, our data retention is pretty short. The first line of the log is from 2020-07-20 20:45 and the issue happened before that. This also happened to me while investigating production issue in FR.

[luisramos0]

I'd a second look as well.
I just confirmed that the payment method on the payment is from the wrong enterprise already.

This means it's not the dangerous preferences issue I thought it could be! This is not a problem with the preferred_enterprise of the payment method, it's a problem with the selection of the payment method itself. Based on this I am downgrading this to S2 for now.

[sauloperez]

@emilyjeanrogers I'll wait to hear from the customer to take any actions but they are likely to have messed up with the enterprise's payment methods. Once we have a clear understanding of what has happened we can clean things up.

I proceeded to check if there are any stripe payments recently taken like this one where that stripe payment method is no longer connected to the order's distributor but in Katuma staging I'm seeing too many of those to just be an edge case. I'm wondering if the query is correct:

SELECT
    spree_orders.number,
    spree_orders.distributor_id AS order_distributor_id,
    spree_payment_methods.id AS payment_method_id,
    distributors_payment_methods.distributor_id AS method_distributor_id,
    spree_payment_methods.type
FROM spree_payments
  INNER JOIN spree_orders ON spree_orders.id = spree_payments.order_id
  INNER JOIN spree_payment_methods ON spree_payment_methods.id = spree_payments.payment_method_id
  INNER JOIN distributors_payment_methods ON distributors_payment_methods.payment_method_id = spree_payment_methods.id
WHERE distributors_payment_methods.distributor_id <> spree_orders.distributor_id
  AND spree_payment_methods.type ilike '%stripe%'

   number   | order_distributor_id | payment_method_id | method_distributor_id |             type              
------------+----------------------+-------------------+-----------------------+-------------------------------
 R417186123 |                    9 |                13 |                     4 | Spree::Gateway::StripeConnect
 R171173471 |                   11 |                16 |                    15 | Spree::Gateway::StripeConnect
 R171173471 |                   11 |                16 |                    15 | Spree::Gateway::StripeConnect
 R171173471 |                   11 |                16 |                    15 | Spree::Gateway::StripeConnect
 R454817208 |                   11 |                16 |                    15 | Spree::Gateway::StripeConnect
 R201654300 |                    7 |                35 |                     5 | Spree::Gateway::StripeConnect
 R861168436 |                    7 |                35 |                     5 | Spree::Gateway::StripeConnect
 R103803027 |                    7 |                35 |                     5 | Spree::Gateway::StripeConnect
 R217011011 |                    4 |                13 |                     9 | Spree::Gateway::StripeConnect
 R232274520 |                   23 |                43 |                    29 | Spree::Gateway::StripeSCA
(10 rows)

[sauloperez]

Checking in the last 6 days we have 4 payment methods that caused 168 orders to be inconsistent:

SELECT spree_payment_methods.id
FROM spree_payments                                         
  INNER JOIN spree_orders ON spree_orders.id = spree_payments.order_id
  INNER JOIN spree_payment_methods ON spree_payment_methods.id = spree_payments.payment_method_id
  INNER JOIN distributors_payment_methods ON distributors_payment_methods.payment_method_id = spree_payment_methods.id
WHERE distributors_payment_methods.distributor_id <> spree_orders.distributor_id
  AND spree_payment_methods.type ilike '%stripe%'
  AND spree_orders.completed_at > current_date - interval '6' day
GROUP BY spree_payment_methods.id;

```

id

377
400
432
466
(4 rows)

Some of these orders are:

```sql
   number   | order_distributor_id | payment_method_id | method_distributor_id |             type              
------------+----------------------+-------------------+-----------------------+-------------------------------
 R857402263 |                 2630 |               466 |                  2813 | Spree::Gateway::StripeConnect
 R534428134 |                 2630 |               466 |                  2813 | Spree::Gateway::StripeConnect
 R231378318 |                 2630 |               466 |                  2813 | Spree::Gateway::StripeConnect

We'll need to dig further.

[luisramos0]

I changed the query a little to make the result more readable:

SELECT spree_payment_methods.id payment_method_id, spree_payment_methods.name payment_method_name ,  spree_orders.distributor_id recent_order_distributor, 
distributors_payment_methods.distributor_id current_payment_method_distributor,
count(spree_orders.id) orders_count_with_currently_inactive_payment_method
 FROM spree_payments                                         
  INNER JOIN spree_orders ON spree_orders.id = spree_payments.order_id
  INNER JOIN spree_payment_methods ON spree_payment_methods.id = spree_payments.payment_method_id
  INNER JOIN distributors_payment_methods ON distributors_payment_methods.payment_method_id = spree_payment_methods.id
WHERE distributors_payment_methods.distributor_id <> spree_orders.distributor_id
  AND spree_payment_methods.type ilike '%stripe%'
  AND spree_orders.completed_at > current_date - interval '15' day
GROUP BY  spree_payment_methods.id, spree_payment_methods.name,  spree_orders.distributor_id , 
distributors_payment_methods.distributor_id;

We need to check this data:

The question in this table: is it correct/ok that enterprise recent_order_distributor received orders_count_with_currently_inactive_payment_method order payments with payment_method_name which is now only linked to current_payment_method_distributor?

With numbers this becomes:
A - is it correct/ok that enterprise 2174 (Harcourt Organic Farming Co-op) received 5 order payments with "Credit card payment" which is now only linked to enterprise 2992 (Carr's Organic Fruit Tree Nursery)?
B - is it correct/ok that enterprise 2545 (Willunga Farmers Market) received 47 order payments with "Payment online" which is now only linked to enterprise 2586 (Soul Food & Co)?
C - the next one is this issues case, only one order in this case
D - is it correct/ok that enterprise 2565 (Southern Harvest Association Community Stall) received 92 order payments with "Stripe card payment" which is now only linked to enterprise 2684 (Southern Harvest Ad-Hoc Produce Boxes) and enterprise 2685 (Southern Harvest Farmers Market)?
E - is it correct/ok that enterprise 2630 (Wangaratta Farmers' Market Hub) received 180 order payments with "Wangaratta Farmers' Market Hub" method which is now only linked to enterprise 2813 (Pasta Adele)?

For cases A, B, D, E I can see in the DB that the enterprises in question all have common managers, so a manager can change and swap payment methods between enterprises (whether they did it on purpose or by mistake it's another question).

It's really just the one order of this issue where we don't have a user/manager with access to both enterprises. Any super admin could have done it?

Anyway, this analysis shows this problem is very specific to this order (data analyzed was from the last 10 days only).

[kirstenalarsen]

Can I have some affected order numbers for investigating with the hubs please @luisramos0 .b and E look most suspicious to me

[kirstenalarsen]

@sauloperez ?

[kirstenalarsen]

OK, based on order numbers and a bit of investigation it looks very much like all of these are deliberate and reasonable cases where multiple shops are using the same payment method. In all cases (except the one originally reported) the payment method in question IS still attached to all the shops through which the orders were placed. I'm not sure if the query was picking up something slightly different

We will follow up with the Hubs where it looks slightly weird that the payment method is attached to a particular producer's shop and just make sure that's what they mean.

I'm now with you @luisramos0 and @sauloperez - leaning towards user error

[luisramos0]

the payment method in question IS still attached to all the shops through which the orders were placed.

Kirsten the query is an attempt at finding exactly the opposite. Order payment merhod not attached to order distributor...

Sent from my iPhone

On 21 Jul 2020, at 23:51, Kirsten Larsen [e-mail] wrote:

the payment method in question IS still attached to all the shops through which the orders were placed.

[kirstenalarsen]

the distributor is the shop through which the order was placed. I understand that that is what the query was meant to find. What I am telling you is that when I checked the order numbers you gave me the payment methods WERE attached to the shops. So i think the query was not telling us what you thought it was

[sauloperez]

I recall having a discussion around

so a manager can change and swap payment methods between enterprises

back in the Barcelona's gathering with Myriam. Or maybe it was just related to that.

[luisramos0]

Yes Pau, there are quite a few things we would like to improve in this area, we discussed a few of them in the past with Myriam. I think they are in discourse.

Yes, users that manage multiple enterprises can get payment methods confused really easily. One problem is that the name of the payment method shows in checkout so you dont want to put the name of the enterprise in the name of the payment method, that's also why we see here things like "Payment online" but the manager will see this "Payment online" which is a stripe method for one enterprise mixed with a "Stripe card payment" of another enterprise if they are manager of both enterprises. It's really easy to mix things up.

Anyway, the query is indeed wrong, sorry Kirsten!
The correct query is this and only returns the case reported in the issue, one single order in the last 15 days. If I remove the date filter and make it all time, I see a few orders from July 2019 for hub 2331 and from 2019 Mar for hub 768 but these could also just be stripe payment methods that have now been detached from the enterprise where they we used originally.

SELECT spree_orders.distributor_id, spree_payments.payment_method_id, spree_orders.number, spree_orders.completed_at
FROM spree_payments
  INNER JOIN spree_orders ON spree_orders.id = spree_payments.order_id
  INNER JOIN spree_payment_methods ON spree_payment_methods.id = spree_payments.payment_method_id
WHERE spree_payments.payment_method_id not in (select payment_method_id from distributors_payment_methods where distributors_payment_methods.distributor_id = spree_orders.distributor_id)
  AND spree_payment_methods.type like '%Stripe%'
order by spree_orders.distributor_id, spree_payments.payment_method_id;

[kirstenalarsen]

ok awesome. single relevant case, there is not something evil happening to lots of people. we think user error, close the issue?

[luisramos0]

yes, that's an option yes. Let's close this and see if it ever happens again.

[sauloperez]

For the record (and mostly a reminder for myself) it's easy to forget that those order records are the "photo" in the DB of a particular situation at that time they were placed. Chances are that the distributor's payment methods are no longer the ones the customer found back in time. Things might be inconsistent now but weren't back then.