#newtreyes
wise shore
Hi @obtuse lion
obtuse lion
So I'm not actually sure collecting us bank account details over the phone is allowed. I would check with our support team on that: https://support.stripe.com/contact. Problem is we're just not really familiar with the laws on that
In Sigma, is the automatic_transfer_id balance transaction field the payout id the balance transaction is attached to? If not, what does it represent?
Pasting your other question in here
wise shore
Sounds good
obtuse lion
Not sure offhand
Looking
Yeah you're right
The balance_transactions Sigma table includes the automatic_transfer_id, which describes the automatic payout that the balance transaction was included in. Recommended for custom reports.```Retrieve the balance transaction
I think payout id should be the source: https://stripe.com/docs/api/balance_transactions/object#balance_transaction_object-source
wise shore
No, it's not. The source is the object that generated the balance transaction (Ex. A charge, a refund, etc)
obtuse lion
Well there's payout balance transactions
Which type of balance transaction are you looking at?
obtuse lion
So what's the end goal here? You want to see if a charge is included in a given payout?
I think I just need a little more context
restive owlBOT
wise shore
If it was included in any payout
because the ones that are available and have not been included add up to the future payments amount
and it helps us reconciling external systems to stripe
does that make sense?
fierce sleet
👋 hopping in here - give me a minute to catch up
As far as I know there isn't an attribute on the Balance Transaction API that represents what payout a transaction was part of. You'd have to do that reconciling on your end (or rely on sigma), since the only way to relate a payout back to the balance transactions it contains is by listing all BTs for that payout (https://stripe.com/docs/api/balance_transactions/list#balance_transaction_list-payout)
Or you'd have to check the available_on property of the BT and match that back to the arrival date of payouts
wise shore
@fierce sleet Hey, there
QQ: is available_on the date when a BT was attached to a payout?
Or is it the date when the BT is expected to be attached to a payout?
Also, if the status of a BT is changed to available, does that mean that it was already attached to a payout?
fierce sleet
available_on is the date that we expect those funds to be paid out if you're using automatic payouts (with automatic payouts we prep the payout beforehand so that the funds get paid out to your bank account as soon as they're available instead of you having to first wait for them to be available and THEN starting the payout, which would result in a delay)
wise shore
Can you explain this to me a bit more with an example of how that works?
restive owlBOT
whole vine
hello! Wil ltake me a while to catch up there are 12 other people with questions in real time
whole vine
available_on is usually 12am UTC of the day funds will be released. So if you are in the US and Charge on Monday at 2pm, available_on will be Wednesday 12am UTC.
But available_on is only a small part of the story around funds availability, Payouts, balances, etc. This is really complex and not something we are best equipped to chat about or teach you here at least.
But what is your real question behind those questions
wise shore
So, our solution among lots of other stuff tries to keep an external system (Salesforce org) in sync with Stripe. We do that by looking at balance transactions and make sure every one of them has a corresponding record on the Salesforce side.
Now, part of what we do to make sure things are in sync is looking at the future payments balance and make sure they match with the total we generate on the salesforce side. For that, what we do is adding up the net amount of all balance transactions that don't have a payout assigned to them.
Now, sometimes that matches the future payments balance and then we are good. But when there is a mismatch, we would like to know which balance transactions add up to the future payments balance.
If we had that information, then we could look for them on the Salesforce side and figure out what's out of sync.
whole vine
Just to repeat this back to you to be sure:
You are already all set on any existing/created Payout, you know how to look up what's into it, find all the BalanceTransactions you have in your database that match it and mark them as paid out and all that.
And all you're trying to do now is to figure out the "pending BalanceTransactions" and when they are likely to be paid out in the future?
wise shore
You are already all set on any existing/created Payout, you know how to look up what's into it, find all the BalanceTransactions you have in your database that match it and mark them as paid out and all that.
Yes
And all you're trying to do now is to figure out the "pending BalanceTransactions" and when they are likely to be paid out in the future?
If we could query the pending balance transactions from Stripe, that would be awesome. With that, we could stop looking at the future payments balance and just focus on that.
whole vine
that doesn't exist though
wise shore
And I think this Sigma query may get us that:
from connected_account_balance_transactions -- this table is the canonical record of changes to your Stripe balance
where
connected_account_balance_transactions.automatic_transfer_id is null
and connected_account_balance_transactions.account = 'acct_1LtzytRkccqSV6bC'
and connected_account_balance_transactions.status = 'pending'
order by created desc```the way it works is: you ingest all BTs and track their status. And then every time you get a Payout you look at what's inside, so you know what's paid out which means they are available and you clear those from your DB and the ones left are pending
I can't really help with Sigma questions and the balance is especially complex overall. This is really a lot better targetted at our support team or your Stripe point of contact if you have one
wise shore
whole vine
I wouldn't use Sigma if it were me because it's hours behind. I'd do what I described (which you said earlier you already ingested all Payouts and what's inside)
wise shore
Mmmmm
Interesting
the way it works is: you ingest all BTs and track their status. And then every time you get a Payout you look at what's inside, so you know what's paid out which means they are available and you clear those from your DB and the ones left are pending
That makes a lot of sense
Now, the problem with that is: what happens if something goes wrong and the external system is a couple of balance transactions off? How can we tell if that is the case and which balance transactions are those that we are missing?
whole vine
Fun fact: Internally I wrote a "knowledge base article" saying "if users try that, tell them to give up" basically lol
wise shore
That's what we were using the future payments balance for
whole vine
if it were me: I'd ingest new BTs daily
So every day, I list all BTs from the day until I get to the one I saw last (since it's always in most recent created order)
(you can do it hourly or every 10 minutes, depends a lot on your overall load)
As long as you do that you know which BTs are on the account and you know their status when you ingest them. If they are pending, they will be marked available once they are "paid out" which you separately reconcile through Payouts. So every time you get a Payout, you list all BTs associated with it (https://stripe.com/docs/api/balance_transactions/list + payout: po_123) and you marked those as paid out in your dabatase
If you do that for every Payout (and assuming you don't do manual Payouts) then you're going to cover all your bases
(If you do use manual Payouts, let me cry for a little bit, but then we can chat about that :p )
wise shore
No Manual payouts, thank God
😄
Ok, let me say that great minds think alike
because we are doing that already
our issue is that we were trying to match the total net amount of the balance transactions without a payout and trying to match that with the future payments balance.
We thought if those didn't match, we were having problems
whole vine
Yeah that is what my article tries to answer with "don't even try, tell them to give up" because it's sadly the truth
there are so many gotchas to this from funds in reserve for disputes and other reasons, bank holidays, currency conversions, it's just really really hard and we don't ahve a "future Payouts" API
wise shore
But a couple of weeks ago we had a customer that was seeing a positive balance on their salesforce org while stripe was showing a negative future payments balance.
and we were not able to tell why was that the case.
So, we wanted to know where the negative balance on the stripe side was coming from.
whole vine
Yeah and I'm not sure there's a way for us to know without finding what was different between your math and hours. Like did you miss a specific BalanceTransaction? Did you miss a reserve? You'd have to figure out where the discrepancy came from
wise shore
Yeah... so, let me ask for your expert advice
whole vine
to be clear: I'm trying to help because the two other people on my team did, if I had gotten your question upfront I'd have said this is a question for support, they put me in a weird position (and shouldn't have 😂)
but happy to try and bounce ideas for a bit!
wise shore
I was already bounced to support several times when asking about this
and let me tell you this: for simple questions, that's OK
but for these kind of questions, it's a dead end
whole vine
yeah I know, but I'm abit stricter than the rest of my team on what's in scope for Discord here. Right now I had to help 20 other developers in parallel while trying to grasp your question. And this is way outside the scope of what my team ever touches. Like if you ask simple questions it's fine, but then most of us wouldn't be able to talk about what I did with you, I've just been here for a long time 🙂
whole vine
someone on my team is going to take over soon but I'm around for a bit, so if you have any specific additional questions I'm game to try
whole vine
I'll be honest if I don't know the answer though. This is sadly really hard
wise shore
yeah, I know that by now
🙂
Ok
So, here's the question in my head right now: we have all balance transactions registered, their corresponding payouts registered too and it all seems to be working as expected. Nevertheless, we see that the future payments balance is negative on the Stripe side when we were expecting it to be positive on our side. How do we figure out what is going on?
whole vine
Is it one specific account? If so, the first thing I would do is run a one-off script to ingest all BalanceTransactions using https://stripe.com/docs/api/balance_transactions/list and then check if any is missing from my database. Have you tried that?
(I guess you could do a Dashboard export too)
wise shore
Have you tried that?
Yes. We call it "The Synchronization Engine" internally. I built it.
🙂
We do a 2 step process
First we sync all balance transactions that are attached to payouts in the last X days
Then, we ingest all balance transactions using the endpoint you just mentioned.
whole vine
okay so you're confident you have the right BTs and your total amount sums to say $1234 and the Dashboard shows -$278 or whatever?
What do you get when you call the Retrieve Balance API https://stripe.com/docs/api/balance/balance_retrieve? Does that match what the Dashboard shows?
wise shore
Sometimes it finishes without a problem and we still see things not matching
restive owlBOT
wise shore
Yes, that matches the balance in the dashboard
whole vine
okay so does that JSON response have anything weird like some reserve or something you're not accounting for?
wise shore
That I don't know... I haven't checked that
whole vine
maybe try just to see. We might have some weird feature for them like a loan, or a reserve, or whatever
whole vine
DM me the account id I'll have a check. I won't tell you something secret but I can at least tell you "oh yeah talk to support about X" if I see something (but honestly balance calculations baffles me to this day)
wise shore
LOL
Me too
Ok, right now I don't have an example
But once I have one, I will DM you
That may take a while since I am working on some other project
whole vine
Not sure I'll extend my offer past this short thread :p
whole vine
I can't really do that, I alreayd said a few times this is way out of scope for Discord help (and we get dozens of DMs from people 😅)
I get what you mean about support. I do understand, but beyond live chat there are experts you can get escalated to and get answers from
I think what you can do is share this Discord conversation to prove you spoke to us and ask to talk to a specialist on support that can dive into the balance reconciliation math with you. But you'll need the exact account id (acct_123) and all the information you already have to get through to the right people so that'd be the best next steps
wise shore
Ok
whole vine
the people who work on support at Stripe know who we are (we also do support when it's related to code for example) and so hopefully you can get through to the right person
wise shore
Is there a way to export this conversation for later reference?
whole vine
https://discord.com/channels/841573134531821608/1136339833405579335 this is the direct link to the whole conversation
there isn't a "download" button. I wish, I keep having to copy-paste and manually reformat when I use some of those conversations to help support my team's push for new features :p
wise shore
Thank you, Sir
whole vine
Going to close for now and hopefully you get an answer from support once you collect the detailed information. DM me if you don't, I can at least try to find an expert to look at it internally afterwards @wise shore
I might be slow to respond (I don't check Discord if I'm not actively helping developers at that time) but I'll see your DM