Respond to IRS letter

[chadwhitacre]

First time for everything, friends. :)

The income and payment information we have on file from sources such as employers or financial institutions doesn't match the information you reported on your tax return. If our information is correct, you will owe $9,220 (including interest), which you need to pay by October 5, 2017.

If you agree with the changes we made, [pay us].

If you don't agree with the changes, complete the Response form on Page 9, and send it to us along with a signed statement and any documentation that supports your claim so we receive it by October 5, 2017.

[chadwhitacre]

When I manually truncate the transaction_search.csv to the one record and the gratipay query also to the one record and step through the code carefully, I observe the desired behavior that it spits out one match.

[chadwhitacre]

But when I run over the full csv and query ...

FOUND IT IN BRAINTREE fghrjgg 7872 2015-11-26 12.67
number unmatched: 744
[gratipay] $

No FOUND IT IN GRATIPAY.

[chadwhitacre]

How about with two entries?

[chadwhitacre]

Hrm ... two fails, and now one also fails if I run it straight through vs. set_traceing.

[chadwhitacre]

I'm not forcing it in the query this time. Is that what's different?

[chadwhitacre]

Yes.

[chadwhitacre]

Ooh! Ooh!

(Pdb) gratipay
{None: Record(exchange_id=79004, ref=None, date=datetime.date(2015, 11, 26), amount=Decimal('12.67'), participant_id=7872L)}
(Pdb)

I'm clobbering all null refs down to a single key/value in the gratipay dict! 😅

[chadwhitacre]

👊

FOUND IT IN BRAINTREE fghrjgg 7872 2015-11-26 12.67
FOUND IT IN GRATIPAY (7872, datetime.date(2015, 11, 26), Decimal('12.67'))
[gratipay] $

[chadwhitacre]

Eeeeee, now getting number unmatched: 2164.

Let's go with a gist for versioning.

[chadwhitacre]

Oops! Still had the query constrained. 😬

[chadwhitacre]

Okay! Phew. Satisfied. Deep cleansing breath. They all match! 😇

[chadwhitacre]

Do the payment method tokens also match up?

[chadwhitacre]

(That's exchange_routes.address.)

[chadwhitacre]

Not entirely. Okay! We're not trying to solve all of https://github.com/gratipay/gratipay.com/issues/4442 here. Do we have enough to get what we need yet?

What do we need?

[chadwhitacre]

We need to produce a csv of customer,amount where the amounts sum to $36,428.04 and Gratipay's amount is $18,758.

[chadwhitacre]

I guess that means tracing the inputs through paydays and correlating them with outputs. We need to list the outputs that sum to the input.

[chadwhitacre]

I think we should go ahead and fill in the refs. You okay with that @kaguillera? I will test it out and spot-check it on a backup.

[chadwhitacre]

go ahead and fill in the refs

... because that will be the best way to identify the input set.

[chadwhitacre]

As far as tracing the output set, I guess we will have to use transfers and payments and look at timestamps?

[chadwhitacre]

Okay!

[chadwhitacre]

https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql

update exchanges e
   set ref = tmp.ref
  from (values ('399g55w', 78775)
            -- ...
             , ('nkv6gqw', 78568)
              ) as tmp(ref, exchange_id)
 where tmp.exchange_id = e.id
      ;

[chadwhitacre]

👍

gratipay-bak=# \i backfill-braintree-2015.sql 
UPDATE 744
gratipay-bak=#

[chadwhitacre]

Rerunning match-2015.py is now a no-op. 💃

[chadwhitacre]

Okay! Refs backfilled locally! Now to trace payments ...

[chadwhitacre]

Working up a query to select the inputs to trace.

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

from gratipay import wireup

db = wireup.db(wireup.env())

inputs = db.all('''

    SELECT count(*), sum(amount + fee)
      FROM exchanges e
      JOIN exchange_routes er
        ON e.route = er.id
     WHERE network = 'braintree-cc'
       AND "timestamp"::text >= '2015-01-01'
       AND "timestamp"::text < '2016-01-01'
       AND amount > 0

''')

print(inputs)

[Record(count=7996L, sum=Decimal('136429.97'))]

[chadwhitacre]

Need to match ... 2,165 and $36,428.

[chadwhitacre]

Constraining to status='succeeded':

(7704, Decimal('133386.20'))

🤔

[chadwhitacre]

Well, I guess we have the refs of all 2,165 transactions that Braintree is counting. What's the pattern difference between those and the extra 5,539 that my query is counting?

P.S. If this rabbit hole is too deep we could punt and just query on ref in the known set from Braintree. Would feel more confident understanding this, though ...

[chadwhitacre]

I think I'm going to add a from_braintree column to my local exchanges table and go from there.

[chadwhitacre]

gratipay-bak=# \i refs.sql 
ALTER TABLE
UPDATE 2165
gratipay-bak=# 

alter table exchanges add column from_braintree boolean not null default false;
update exchanges
   set from_braintree = true
 where ref in ( 'jyb8j8g'
             -- ...
              , '7jkhp32'
               )
      ;

[chadwhitacre]

Golly. My query is only finding 1,687 items from_braintree!

[chadwhitacre]

Null routes?

[chadwhitacre]

No. Phew. 😓

gratipay-bak=# select count(*) from exchanges where from_braintree and route is null;
┌───────┐
│ count │
├───────┤
│     0 │
└───────┘
(1 row)

gratipay-bak=#

[chadwhitacre]

Double- and triple-phew. 😅

gratipay-bak=# select count(*) from exchanges where from_braintree;
┌───────┐
│ count │
├───────┤
│  2165 │
└───────┘
(1 row)

gratipay-bak=# select count(*) from exchanges where ref is not null and route is null;
┌───────┐
│ count │
├───────┤
│     0 │
└───────┘
(1 row)

gratipay-bak=#

[chadwhitacre]

Oh! !m us :D

gratipay-bak=# select count(*) from exchanges where route is null;
┌───────┐
│ count │
├───────┤
│     0 │
└───────┘
(1 row)

gratipay-bak=#

[chadwhitacre]

💃

gratipay-bak=# \d exchanges
                                       Table "public.exchanges"
┌────────────────┬──────────────────────────┬────────────────────────────────────────────────────────┐
│     Column     │           Type           │                       Modifiers                        │
├────────────────┼──────────────────────────┼────────────────────────────────────────────────────────┤
  ...
│ route          │ bigint                   │ not null                                               │
  ...

[chadwhitacre]

Convenience view ...

gratipay-bak=# create view ewr as (select e.*, er.network, er.address from exchanges e join exchange_routes er on e.route = er.id);

[chadwhitacre]

Yeah, thought so.

gratipay-bak=# select count(*) from ewr where from_braintree and network != 'braintree-cc';
┌───────┐
│ count │
├───────┤
│   478 │
└───────┘
(1 row)

[chadwhitacre]

🤔

gratipay-bak=# select count(*) from ewr where from_braintree and network = 'unknown';
┌───────┐
│ count │
├───────┤
│   416 │
└───────┘
(1 row)

[chadwhitacre]

Oh sweet mercy. 🙈

gratipay-bak=# select network, count(network) from ewr where from_braintree group by network order by count desc;
┌──────────────┬───────┐
│   network    │ count │
├──────────────┼───────┤
│ braintree-cc │  1687 │
│ unknown      │   416 │
│ paypal       │    31 │
│ balanced-cc  │    31 │
└──────────────┴───────┘
(4 rows)

[chadwhitacre]

Something in the matcher script?

[chadwhitacre]

Or did we link the wrong routes at some point under gratipay/gratipay.com#3912?

[chadwhitacre]

Looks like 11 routes are implicated:

gratipay-bak=# select route, count(route) from ewr where from_braintree and not network in ('braintree-cc', 'unknown') group by route order by count desc;
┌───────┬───────┐
│ route │ count │
├───────┼───────┤
│  2554 │    27 │
│ 12629 │    15 │
│ 12216 │     8 │
│ 11403 │     3 │
│  5144 │     3 │
│ 10402 │     1 │
│ 12440 │     1 │
│  2715 │     1 │
│ 11426 │     1 │
│ 11584 │     1 │
│ 12565 │     1 │
└───────┴───────┘
(11 rows)

gratipay-bak=#

[chadwhitacre]

Alright, how do we avoid caring about this?

[chadwhitacre]

I guess we can just start with the from_braintree in the query—I don't actually have to run anything against production here, I can do it all from this here backup here.

[chadwhitacre]

gratipay-bak=# select sum(amount+fee), count(*) from inputs;
┌──────────┬───────┐
│   sum    │ count │
├──────────┼───────┤
│ 36428.04 │  2165 │
└──────────┴───────┘
(1 row)

DROP VIEW ewr;
CREATE VIEW inputs AS (SELECT * FROM exchanges WHERE from_braintree ORDER BY "timestamp" ASC)

[chadwhitacre]

There are only a handful of transfers after we started charging on Braintree (https://github.com/gratipay/inside.gratipay.com/issues/1164#issuecomment-330670984). I am going to try ignoring them and only look at payments.

gratipay-bak=# select "timestamp"::date, amount from transfers where "timestamp" >= '05-28-2015'::date order by "timestamp" desc;
┌────────────┬────────┐
│ timestamp  │ amount │
├────────────┼────────┤
│ 2016-10-13 │   0.56 │
│ 2016-08-24 │   3.00 │
│ 2015-11-23 │ 147.48 │
│ 2015-09-13 │   0.43 │
│ 2015-07-30 │   0.34 │
│ 2015-07-30 │   0.87 │
└────────────┴────────┘
(6 rows)

gratipay-bak=#

[chadwhitacre]

Alright, let's trace one exchange through payments. I guess we want to trace it iteratively until it goes out again in another exchange or we hit 2016-01-01. Do we have any regiving to speak of tho?

[chadwhitacre]

payments start with Gratipay 2.0 on 2015-05-15.

[chadwhitacre]

payments are linked to a payday, but exchanges are not.