I'm developing a marketplace-style application that allows users to upload purchasable digital items -> the public purchases these items -> and for my application to pay the users (owners of items) their owed funds via PayPal Payouts API on a daily basis.
I'm struggling with how best to calculate/store the owing balance, and how to map the individual purchase transaction records to the concept of a "payout" (when we send owed funds to the user).
Schema so far:
- user_id (owner)
- type ("purchase" or "payout")
- status (depending on PayPal response. COMPLETED, FAILED, REFUNDED etc.)
- value (integer (lowest demomination of currency). Positive integer for purchase, negative for a payout).
- purchasable_id (For "purchase" transactions, reference the ID of the purchasable item that was purchased)
- payout_id (?) The ID of the payout (below) this purchase is included in. Not sure about this. This won't be known at the time of the transaction, so it would need to be updated to store it and I'm not sure how to know which transaction will belong in which payout?
Not sure about this. Feels like a duplicate of a payout transaction entry, but I want a way to store which purchase transactions were paid out in which payouts.
- status (depending on PayPal response to Payout API webhook. COMPLETED, FAILED, REFUNDED etc.)
This is where I need the most help.
CRON job. Every 24hrs:
Calculate each users balance by summing the payout_balance_change fields of the Transactions table. i.e balance isn't stored, it's always calculated. Is that a good idea?
Insert a row into "Transactions" of type "payout" with a negative "payout_balance_change". i.e. subtracting the amount we will send in the payout, zeroing their balance in the Transactions table.
Insert a row into "Payouts" table that stores the details of the payout attempt.
- How will I know which purchase transactions belong to each payout cycle (so I can then store the payout_id in those transaction records). I could use the date of the transaction, and each payout could be for the 24hr period prior to the CRON job? I'm flexible on this and not sure what the most robust logic would be.
Any advice on how best to structure this, or links to similar projects would be greatly appreciated.