How can I get the original charge and refund ids of an automatic payout

Question

Stripe connect accounts are configurable to coalesce payouts in a regular payout schedule, e.g. for monthly payouts in our case. For these monthly payouts we need to explain the account owners which of the transactions on our platform (bookings and refunds in our case) produced the overall amount they receive. We store the stripe charge id (resp. refund id) in the booking (resp refund) objects in our database. Thus the question boils down to:

Given a stripe account id, how can you get the list of stripe charge and refund ids that contributed to the last payout?

Answer 1

I've had an extensive exchange with Stripe's support team and there are several puzzle pieces necessary to get there:

Payouts are scoped by accounts

If you query stripe for a list of payouts, you will only receive the payout objects that you, the platform owner, get from stripe. To get the payout objects of a specific account you can use the normal authentication for the platform, but send the stripe account id as a header. So the code snippet to get the last payout looks like this (I'll use ruby snippets as examples for the rest of the answer):

Stripe::Payout.list({limit: 1}, {stripe_account: 'acct_0000001234567890aBcDeFgH'})
=> #<Stripe::ListObject:0x0123456789ab> JSON: {
  "object": "list",
  "data": [
    {"id":"po_1000001234567890aBcDeFgH",
     "object":"payout",
     "amount":53102,
     "arrival_date":1504000000,
     "balance_transaction":"txn_2000001234567890aBcDeFgH",
     "created":1504000000,
     "currency":"eur",
     "description":"STRIPE TRANSFER",
     "destination":"ba_3000001234567890aBcDeFgH",
     "failure_balance_transaction":null,
     "failure_code":null,
     "failure_message":null,
     "livemode":true,"metadata":{},
     "method":"standard",
     "source_type":"card",
     "statement_descriptor":"[…]",
     "status":"paid",
     "type":"bank_account"
    }
  ],
  "has_more": true,
  "url": "/v1/payouts"
}

Having the payout id, we can query the list of balance transactions, scoped to a payout:

Stripe::BalanceTransaction.all({
    payout: 'po_1000001234567890aBcDeFgH',
    limit: 2,
}, {
    stripe_account: 'acct_0000001234567890aBcDeFgH'
})

Objects viewed as an account are stripped of most information, compared to those viewed as a platform owner

Even though you now have the payout id, the object is still scoped to the account and you cannot retrieve it as platform owner. But viewed as an account, the payout only shows pseudo charge and refund objects like these (notice the second transaction has a py_7000001234567890aBcDeFgH object as a source instead of a regular ch_ charge object):

Stripe::BalanceTransaction.all({
    payout: 'po_1000001234567890aBcDeFgH',
    limit: 2,
}, {
    stripe_account: 'acct_0000001234567890aBcDeFgH'
})
=> {
    :object => "list",
    :data => [
        {
            :id => "txn_4000001234567890aBcDeFgH",
            :object => "balance_transaction",
            :amount => -53102,
            :available_on => 1504000000,
            :created => 1504000000,
            :currency => "eur",
            :description => "STRIPE TRANSFER",
            :fee => 0,
            :fee_details => [],
            :net => -53102,
            :source => "po_5000001234567890aBcDeFgH",
            :status => "available",
            :type => "payout"
        },
        {
            :id => "txn_6000001234567890aBcDeFgH",
            :object => "balance_transaction",
            :amount => 513,
            :available_on => 1504000000,
            :created => 1504000000,
            :currency => "eur",
            :description => nil,
            :fee => 0,
            :fee_details => [],
            :net => 513,
            :source => "py_7000001234567890aBcDeFgH",
            :status => "available",
            :type => "payment"
        }
    ],
    :has_more => true,
    :url => "/v1/balance/history"
}

You can let stripe automatically expand objects in the response

As an additional parameter, you can give stripe paths of objects which you want stripe to expand in their response. Thus we can walk from the pseudo objects back to the original charge objects via the transfers:

Stripe::BalanceTransaction.all({
    payout: 'po_1000001234567890aBcDeFgH',
    limit: 2,
    expand:['data.source.source_transfer',]
}, {
    stripe_account: 'acct_0000001234567890aBcDeFgH'
}).data.second.source.source_transfer.source_transaction
=> "ch_8000001234567890aBcDeFgH"

And if you want to process the whole list you need disambiguate between the source.object attribute:

Stripe::BalanceTransaction.all({
    payout: 'po_1000001234567890aBcDeFgH',
    limit: 2,
    expand:['data.source.source_transfer',]
}, {
    stripe_account: 'acct_0000001234567890aBcDeFgH'
}).data.map do |bt| 
  if bt.source.object == 'charge'
    ['charge', bt.source.source_transfer.source_transaction]
  else
    [bt.source.object]
  end
end
=> [["payout"], ["charge", "ch_8000001234567890aBcDeFgH"]]

Refunds have no connecting object path back to the original ids

Unfortunately, there is currently no way to get the original re_ objects from the pseudo pyr_ that are returned by the BalanceTransaction list call for refund transactions. The best alternative I've found is to go via the data.source.charge.source_transfer.source_transaction path to get the charge id of the charge on which the refund was issued and use that in combination with the created attribute of the pyr_ to match our database refund object. I'm not sure, though, how stable that method really is. The code to extract that data:

Stripe::BalanceTransaction.all({
    payout: 'po_1000001234567890aBcDeFgH',
    limit: 100, # max page size, the code to iterate over all pages is TBD
    expand: [
        'data.source.source_transfer', # For charges
        'data.source.charge.source_transfer', # For refunds
    ]
}, {
    stripe_account: 'acct_0000001234567890aBcDeFgH'
}).data.map do |bt|
  res = case bt.source.object
    when 'charge'
      {
          charge_id: bt.source.source_transfer.source_transaction
      }
    when 'refund'
      {
          charge_id: bt.source.charge.source_transfer.source_transaction
      }
    else
      {}
  end
  res.merge(type: bt.source.object, amount: bt.amount, created: bt.created)
end

Answer 2

It is now possible to get the refund ids via a "transfer reversal" object:

Stripe::BalanceTransaction.list({
  payout: 'po_1000001234567890aBcDeFgH',
  expand: [
    'data.source.source_transfer', # For charges
    'data.source.transfer_reversal', # For refunds
  ]
}, {
  stripe_account: 'acct_0000001234567890aBcDeFgH'
}).auto_paging_each do |balance_transaction|
  case balance_transaction.type
    when 'payment'
      charge_id = balance_transaction.source.source_transfer.source_transaction
    when 'payment_refund'
      refund_id = balance_transaction.source.charge.source_transfer.source_transaction
    end
  end
end