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
Purchasable Item
- id
- user_id (owner)
- price
- createdAt
- etc.
Transaction
- id
- 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)
- transaction_fee
- createdAt
- 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?
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.
- id
- status (depending on PayPal response to Payout API webhook. COMPLETED, FAILED, REFUNDED etc.)
- createdAt
Logic:
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.
Problems:
- 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.
I'm answering on the assumption this is MySQL.
Firstly - this on how to store money in MySQL.
Secondly - the common pattern for doing this is to have the transaction table only reflect complete transactions. That way, the current balance is always sum(transaction_value), with the transaction date showing you the balance at a given point in time. You typically store the interim state for each transaction in a dedicated table (e.g. "payout"), and only insert into the transaction table once that payout transaction is complete.
You should remove all the status and transaction_fee references from the transaction table, and store them in the dedicated tables. A transaction_fee can be represented as a transaction in its own right.
If you want to store the relationship between purchase and payout, you might have something like:
Payout
Payout_id
Payout_amount
Payout_status
Payout_date
...
Purchase
Purchase_id
Customer_id
Item_id
Purchase_date
....
Payout_purchase
Purchase_id
Payout_id
Your logic then becomes:
- cron job searches all purchases that haven't been paid out (where purchase_id not in (select purchase_id from payout_purchase)
- for each vendor:
- create new record in payout_purchase
- find sum of new payout_purchase records
- attempt payout
- if (payout succeeded)
- insert record into transaction table with vendor ID, payout ID and payout amount
- else
- handle error case. This could be deleting the record (and logging the failure somewhere else), or by adding a "status" column with the value "failed". The latter option makes it easier to provide your vendors with a statement - "we attempted to pay you, but the payment failed". Either way, you want to have a way of monitoring failures, and monitor them.
- end if
- next vendor
I've left out the various state and error management logic steps.
Things you want to worry about:
- What happens if a purchase occurs while the payout logic is running? You need to make sure you work on defined data sets in each step. For instance, you need to insert data into the "payout_purchase" table, and then work only on those records - new purchases should not be included until the next run.
- What happens if a payout fails? You must ensure they are included in the next payment run.
- How do you provide a statement to your buyers and sellers? What level of detail do you want?
- Transaction management from MySQL may help, but you need to spend time learning the semantics and edge cases.