alexpott's balance doesn't add up to 0

[chadwhitacre]

His balance should subtract out to 0 at the bottom but it's 25.90. This has to be a bug in MassPay (#943).

[chadwhitacre]

I hypothesize that this will be the case for anyone whom we've Mass Paid. I believe there's a SQL query buried somewhere in the issue tracker to find out who is affected by a bug like this. We should dig that up, run that, and compare it to the list of people we've Mass Paid.

[zbynekwinkler]

select paypal_email from participants where paypal_email is not null;

Ref #1118, #1116. That's all I could find.

[chadwhitacre]

I think it was more like ...

select username, balance 
  from participants
 where balance != (select sum(amount)
                     from exchanges
                    where participant = username);

But that's timing out for me (on a local backup).

[zbynekwinkler]

Try this, its fast (150ms):

select username, balance 
from participants 
where paypal_email is not null 
and balance != (select sum(amount) from exchanges where participant =username);

It runs only for 5 rows and not for 50k. Judging from the number of rows the full query should run for about 30 minutes. Maybe adding index on exchanges participant column could speed it up (now it does seq scan). I don't have my local setup at hand to test.

[chadwhitacre]

Thanks @zwn. That's good enough for this ticket.

[chadwhitacre]

Alright, what's going on here?

[chadwhitacre]

I'm looking at this locally. I'm looking at SlappyBag instead of alexpott because Alex has tons more history, making his case harder to reason about.

[chadwhitacre]

SlappyBag's current balance is 0.63.

[chadwhitacre]

I believe the problem is in how fees are posted back to Gittip in step 3 of the Mass Pay flow.

[chadwhitacre]

SlappyBag's balance is off by 2.98, and that is the sum of the fees for the two Mass Pays he's been involved with (2.35 + 0.63).

[chadwhitacre]

I believe this is a bug in /%username/history/record-an-exchange.spt. There we update the user's balance based on the amount but ignore the fee. I think this is probably correct when the exchange is a credit card charge, but not when it is a bank account deposit.

[chadwhitacre]

We've overpaid people. SlappyBag, for example. The buggy POST to record-an-exchange two weeks ago meant that SlappyBag had too high of a balance going into last week, so we paid him too much.

[chadwhitacre]

So we've got to:

• Fix the bug.
• Repair the database.
• Recover overpayments.

[chadwhitacre]

Okay, there's a fix in #1650 waiting to be merged.

[chadwhitacre]

Now to repair the database ...

[chadwhitacre]

We need to adjust balanced downward for those affected. Now is actually the ideal time to do that because we've run another payday so there should be enough for each user to take out the fee. Then we can proceed with Mass Pay as usual.

[chadwhitacre]

So who all is affected? Anyone for whom we used record-an-exchange with a negative amount and a positive (non-zero) fee.

[chadwhitacre]

Namely, select * from exchanges where amount < 0 and fee > 0 and note is not null;.

[chadwhitacre]

  SELECT username, balance, sum(fee) as fees
    FROM exchanges join participants on participant=username
   WHERE amount < 0 and fee > 0 and note is not null
GROUP BY username
ORDER BY username;

Seven users are affected.

[chadwhitacre]

I'm not seeing the numbers line up for the seven people affected. I expected to see balances on the history page be off by the amount of the fees from that last query. But in some cases they are, in others, not.

[chadwhitacre]

Got it!

[chadwhitacre]

We've done three Mass Pays: October 11, 24, and 31. It appears that only during the second two did we screw up and not take the payout fee out of the users' balances.

• Two of the seven affected users only participated in the first Mass Pay, the good one.
• Three of the seven only participated in the second and/or third Mass Pay, the bad ones.
• Two of the seven participated in all three.

I'd like to see if I can dig up a reason why the bug wasn't in the first Mass Pay (we didn't use record-an-exchange, I guess?), and then I think we're in a position to adjust balances for the five users who participated in one or both of the bad Mass Pays. Adjusting these balances will take care of both db repair and overpayment recovery from #1633 (comment).

[chadwhitacre]

Best I can find is #943 (comment) and #943 (comment). I would've used the admin UI on the history page to record those exchanges. I guess I manually updated their balances in the db at that point? That sounds vaguely familiar. Since we don't log balance changes (see #1549) I guess that's the closest we're going to get to the bottom of this. :-/

[chadwhitacre]

I've manually adjusted the balances for these users: @alexpott @evbogue @Higgo @nicferrier @SlappyBag.

[chadwhitacre]

I believe this is now resolved and I can go run Mass Pay for this week!

[chadwhitacre]

Overpayments due to this bug manifest as a negative balance on the history page for affected users.

[chadwhitacre]

MassPay done for this week. Spot-checked a few accounts, looks good!