0

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
  • name
  • createdAt
  • etc.

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.

Any advice on how best to structure this, or links to similar projects would be greatly appreciated.

Thank you!

Improve this question
3
  • It is absolutely unclear what are entities in your scheme, what attributes they have, how they're referred to each other and what are consistency rules, what processes are performed over them... based on the usual ideas about the described processes, your scheme seems to be not logical.
    – Akina
    Jun 6, 2022 at 7:20
  • Hi @Akina, I've updated the post with a clearer view of my proposed schema and the main issue. I'm looking for more of a higher level explanation of how a typical transaction -> payout flow might work, because my proposed version doesn't know how to map purchase Transactions to the Payouts in which they are paid. Thanks.
    – Jim Stevens
    Jun 6, 2022 at 11:56
  • I don't fully understand how the process of sale and purchase takes place. More precisely, how money is transferred is clear to me, but how are the items transferred? It turns out that there should be not only the processes of applying for the purchase of an item and paying for the purchase, but also the process of confirming the transfer of the item? The scheme should exclude fraud, both receiving money without transferring the item, and receiving the item with the subsequent statement that the item has not been received and demanding the return of the money transferred.
    – Akina
    Jun 6, 2022 at 12:32

1 Answer 1

Reset to default
0

These are questions about the data design and the application process. Here are some ideas about the payout and purchase transaction data. This note is mostly related with the MongoDB database.

Purchases and payouts are two entities. The relationship between them is that one payout is associated with multiple purchases, One-to-Many. See Model Relationships Between Documents.


Processing each user's purchases and their payouts:

There are different ways you can model this. The first is, that the purchase can store a reference to the payout_id. The payout details are stored separately. The second option is that you store the most used details like payout_id and payout_date within the purchase and keep all the details in the payout. The reference of the payout id is used to get more details about the payout as needed by the application.

Its important that you know what kind of queries (important ones), the CRUD operations, you are going to perform with this data and understand the expectations (complexity of the query, performance, amount of data, available resources, etc.).

Do you want to store the purchases and payouts within the same collection or different collections? These are possibilities and again the same comment above applies.


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?

Calculation at process time or keeping track of payout balance as a separate field - both are possibilities and this depends upon your application requirement. The Computed Pattern (a design pattern) discusses this issue.

The exact process of how you store, read, calculate, update data and balances - are details that depend upon how you design your data, and other requirements. This requires a more thorough study, generally called as Data Modeling. See Data Modeling Introduction.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.