run Gratipay 238

[gratipay-bot]

← Payday 237

---

Docs

http://inside.gratipay.com/howto/run-payday

Checklist

• Review accounts
  • New givers - One violations still opened - https://github.com/gratipay/violations/issues/32
  • 1.0 Payout applications
• Trigger backup
• Run payday script
• Commit payday logs
• Run masspay
  • DB query to update broken paypal fees
  • Generate paypal inputs
  • Verify there is no fee mismatch
  • Submit masspay on paypal
  • Post back masspay
• Commit masspay logs
• Review decline rate
• Shuffle escrow

Rotation

Month	Pilot	Copilot
December	@whit537 ✈️	@clone1018
January	@rohitpaulk	@whit537
February	@clone1018	@rohitpaulk
March	@whit537	@clone1018
April	@rohitpaulk	@whit537
May	@clone1018	@rohitpaulk
June	@whit537	@clone1018
July	@rohitpaulk	@whit537
August	@clone1018	@rohitpaulk
September	@whit537	@clone1018
October	@rohitpaulk	@whit537
November	@clone1018	@rohitpaulk

[chadwhitacre]

One 1.0 payout approved.

[chadwhitacre]

No other accounts to review.

[chadwhitacre]

Backup started ...

[chadwhitacre]

Backup finished and verified.

[chadwhitacre]

Script ran for 51 seconds (0:00:51.667995).

[chadwhitacre]

MassPay submitted.

[chadwhitacre]

Eff. Okay: I forgot to post MassPay back last week, which means that we never decreased people's balances last week, which means that we overpaid people this week.

[chadwhitacre]

First: I'm sorry. 😞

[chadwhitacre]

Second ... what do we do? Hmm ... 🤔

[chadwhitacre]

If we can pull back from PayPal we should consider that first.

[chadwhitacre]

You can only cancel payments that have an unclaimed status.

https://developer.paypal.com/docs/classic/mass-pay/integration-guide/MassPayFAQ/#id09BDB200WHT

All ours are claimed except one for 88¢. 😕

Next option! ...

[chadwhitacre]

Decision taken in slack w/ @clone1018 @mattbk is to post back both masspays, which will send some balances negative, and then email affected users.

[chadwhitacre]

Alright, so I don't think we need to mail anybody unless their balance is going negative. Can we predict who that will be?

[chadwhitacre]

I think it will be those who had a payout in both weeks (last and this).

If we paid them out last, then it doesn't matter that we forgot to record it because we didn't pay them out this week.

Likewise, if we didn't pay them out last week, then there was nothing to post back, so this week's payout would have been the same anyway.

[chadwhitacre]

Logs in https://github.com/gratipay/logs/commit/ce0011f744052dc97435b9e8364abafe8ae14bbc.

[chadwhitacre]

Run in the logdir:

(Pdb) len(this_week)
105
(Pdb) len(last_week)
72
(Pdb) unaffected = this_week ^ last_week
(Pdb) len(unaffected)
33
(Pdb) affected = this_week & last_week
(Pdb) len(affected)
72
(Pdb)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from __future__ import absolute_import, division, print_function, unicode_literals

import csv

def extract_emails(date):
    fp = open('{}.report.paypal.csv'.format(date))
    for i in range(20):
        next(fp)
    as_list = [rec[1] for rec in csv.reader(fp)]
    as_set = set(as_list)
    assert len(as_list) == len(as_set)
    return as_set

last_week = extract_emails('2016-12-15')
this_week = extract_emails('2016-12-22')

import pdb; pdb.set_trace()

[chadwhitacre]

It makes sense that everyone from last week would be affected, since they're precisely the ones whose balances we didn't update last week.

[chadwhitacre]

(Pdb) affected == last_week
True
(Pdb)

[chadwhitacre]

Alright, so what's our expectation? All 72 of those from last week will now have a negative balance, ya? In the amount of their payout last week?

[chadwhitacre]

Spot-check: @mattbk.

[chadwhitacre]

$2 payout last week, $4.34 this week.

[chadwhitacre]

When we post back today's, that should take you down to $0 balance, and then when we post last week's that would take you down to -$2. 👍

[chadwhitacre]

Due to human error, we did not properly record your payout last week, so we overpaid you in that amount this week. You now have a negative balance as a result.

[chadwhitacre]

Okay! How are we emailing them?

/me checks Freshdesk for bcc ...

[mattbk]

Fees?

[chadwhitacre]

/me gets distracted by #137 ...

[chadwhitacre]

Fees?

Hmm ...

[chadwhitacre]

Fees are included, you should've gotten 1.96 of the 2.00, and 4.25 of the 4.34. Ya?

[chadwhitacre]

So let's understand a little more about the set of users we're dealing with here—how the previously-negative relate to the currently negative, and why the currently negative are slightly fewer than we expected—and then get this mail out!

[chadwhitacre]

Two of four previously negative balance participants have email addresses. Neither is affected here.

[chadwhitacre]

I've manually created a sql file to load the emails of affected participants into a table.

CREATE TABLE affected (email text not null);                                                                  
INSERT INTO affected VALUES                                                                                   
('foo'),
[...]

[chadwhitacre]

I've created this table in the production database.

[chadwhitacre]

I'm working on a query to collate the two (affected and negative).

[chadwhitacre]

SELECT * FROM (
    SELECT id
         , username
         , balance
         , (SELECT address
              FROM current_exchange_routes
             WHERE participant=participants.id
               AND network='paypal'
            ) AS paypal
      FROM participants
     WHERE balance < 0
) _ full join affected ON email=paypal
ORDER BY affected is null
       , paypal is null
       , balance ASC;

[chadwhitacre]

Ah! The nine declines didn't send the associated balances in Gratipay negative.

[chadwhitacre]

That takes us from 72 down to 63. 👍

[chadwhitacre]

Okay, here are scripts that are giving me the set of participants whose balance we sent negative today, with their paypal address.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from __future__ import absolute_import, division, print_function, unicode_literals

import csv

def extract_emails(date):
    fp = open('{}.report.paypal.csv'.format(date))
    for i in range(20):
        next(fp)
    as_list = [rec[1] for rec in csv.reader(fp) if rec[5] != 'Denied']
    as_set = set(as_list)
    assert len(as_list) == len(as_set)
    return as_set

last_week = extract_emails('2016-12-15')
this_week = extract_emails('2016-12-22')
affected = this_week & last_week

print("""\
DROP TABLE IF EXISTS affected;
CREATE TABLE affected (email text not null);
INSERT INTO affected VALUES
""")

n = len(affected)
for i, address in enumerate(sorted(affected), start=1):
    print("('{}'){}".format(address, ',' if i < n else ';'))

    SELECT *
      FROM (    SELECT id
                     , username
                     , balance
                     , (SELECT address
                          FROM current_exchange_routes
                         WHERE participant=participants.id
                           AND network='paypal'
                        ) AS paypal
                  FROM participants
                 WHERE balance < 0
            ) _ join affected ON email=paypal
  ORDER BY affected is null
         , paypal is null
         , balance ASC
         , paypal
          ;

[chadwhitacre]

Dumped addresses to a file with:

CREATE TEMP TABLE combined AS (
    SELECT *
      FROM (    SELECT id
                     , username
                     , balance
                     , (SELECT address
                          FROM current_exchange_routes
                         WHERE participant=participants.id
                           AND network='paypal'
                        ) AS paypal
                  FROM participants
                 WHERE balance < 0
            ) _ join affected ON email=paypal
  ORDER BY affected is null
         , paypal is null
         , balance ASC
         , paypal
);
\copy (SELECT paypal FROM combined) TO affected.txt WITH CSV;

[chadwhitacre]

Question: if someone has a negative balance and a credit card on file, will we charge them next week to make up the difference? If so, how might we avoid that?

[chadwhitacre]

a negative balance and a credit card on file

Ten folks are in this boat.

[chadwhitacre]

Seven of the ten have ngiving_to > 0.

[chadwhitacre]

If they take enough next week, they won't be charged, right?

[chadwhitacre]

I find a couple tests related to negative balances during payday:

• test_hold_amount_includes_negative_balance
• test_payin_cant_make_balances_more_negative

The second doesn't cover our situation, and I'm pretty sure the first doesn't either. Seems that the right next step would be to write some tests demonstrating current behavior for the cases we've got before us, and then decide what to do once we know what'll happen.

[chadwhitacre]

Variant for seeing who has a credit card and who gives:

    SELECT balance, username, braintree, ngiving_to, giving, paypal
      FROM (    SELECT id
                     , username
                     , balance
                     , ngiving_to
                     , giving
                     , (SELECT address
                          FROM current_exchange_routes
                         WHERE participant=participants.id
                           AND network='paypal'
                        ) AS paypal
                     , (SELECT error
                          FROM current_exchange_routes
                         WHERE participant=participants.id
                           AND network='braintree-cc'
                        ) AS braintree
                  FROM participants
                 WHERE balance < 0
            ) _ join affected ON email=paypal
  ORDER BY affected is null
         , paypal is null
         , braintree is null
         , braintree
         , ngiving_to desc
         , balance asc
         , paypal
          ;

[chadwhitacre]

Alright, done for now. I am going to try to work on this and get it done (tests written, emails sent) tomorrow morning.

[chadwhitacre]

We really don't want to get support requests from people who, like @mattbk, noticed that their payout was larger than normal. We want to get ahead of that ... though I expect we'll have at least one or two by the time we get an email out. :-/

[chadwhitacre]

Alright! So we do charge people with a negative balance if they have a working card. I've made some tests that demonstrate this, and started a PR to change this behavior: gratipay/gratipay.com#4255. My plan is to send an email today stating that we will recoup the loss from their future taking only (not by charging their card) and that therefore no action is necessary on their part. We'll then need to land gratipay/gratipay.com#4255 before next Thursday in order to make that not a lie. :-)

[chadwhitacre]

I'm getting ready to send an email.

[chadwhitacre]

Sent: "We made a mistake. :-("

Due to human error on our part, we did not properly record your payout from Gratipay last week, with the result that we overpaid you in that amount this week. Long story short, you now have a negative balance as a result, equal to last week's payout.

No action is necessary on your part. We will simply recoup the negative balance from the money you receive in future weeks.

Basically, you got next week's payout a week early. Merry Christmas? :-)

Seriously, though, we're sorry for the mistake, and we're working to prevent it from happening again in the future. Let us you know if you have any questions. Thank you for using Gratipay, and have a wonderful holiday season!

— Gratipay Support Team

[chadwhitacre]

Alright, closing. I've put gratipay/gratipay.com#4255 and gratipay/gratipay.com#4256 on my radar for next week.

[mattbk]

Confirmed receipt.

[mattbk]

!m @whit537