当前位置：文江博客话题详情

在市场应用中支付的结构和逻辑

发布于 2025-02-05 04:51:49 字数 1436 浏览 4 评论 0原文

我正在开发一个市场风格的应用程序，该应用程序允许用户上传可购买的数字物品 - ＆gt;公众购买这些物品 - ＆GT;为了使我的申请每天通过PayPal支出API向用户（物品的所有者）付款。

我正在努力如何最好地计算/存储欠款余额，以及如何将单独的购买交易记录映射到“支出”的概念（当我们向用户发送欠款时）。

到目前为止的模式：

用户

strong
<
<
strong

可购买的项目

id
user_id（所有者）
价格
创建的
等

/
strong
状态（取决于PayPal响应。完成，失败，退款等）
值（整数（货币的最低解放）。购买的正整数，负数为负数）。
purpyasable_id（用于“购买”交易，请参考购买的可购买项目的ID）
trassaction_fee
createat
payout_id （？）付款的ID（下图）此购买已包含在内。不确定此事。。在交易时尚不知道这是不知道的，因此需要对其进行更新以存储它，我不确定如何知道哪个交易将属于哪个支出？

付款

不确定这一点。感觉就像是支付交易条目的重复，但我想要一种存储购买交易的方法。

ID
状态（根据PayPal响应付款API Webhook。完成，失败，退款等）
创建了

逻辑：

这是我需要最多帮助的地方。

cron工作。每24小时：

通过总结事务表的付费_balance_change字段来计算每个用户的平衡。 IE余额没有存储，它始终是计算的。 这是一个好主意吗？
插入类型“支出”的“交易”中，带有负面的“ payout_balance_change”。 IE减去我们将在支出中发送的金额，在交易表中归零其余额。
将一行插入“支出”表中，该表存储了付款尝试的详细信息。

问题：

我将如何知道哪个购买交易属于每个支付周期（这样我就可以将付款_ID存储在这些交易记录中）。我可以使用交易的日期，每个支出可能是在Cron作业之前的24小时内？我对此灵活，不确定最强大的逻辑是什么。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初相遇 2025-02-12 04:51:49

我正在回答这是MySQL的假设。

首先 - this 如何将钱存储在mysql中。

其次 - 这样做的常见模式是让交易表仅反映完整的交易。这样，当前余额始终为sum（transaction_value），交易日期显示给定时间点处的余额。通常，您将每次交易的临时状态存储在专用表（例如“支出”）中，而仅一旦完成支出事务完成后才插入交易表中。

您应该从交易表中删除所有状态和TransAction_FEE参考，并将其存储在专用表中。 TransAction_FEE本身可以表示为交易。

如果您想存储购买和支出之间的关系，则可能有类似的东西：

付款

付款_id
Payout_Amount
Payout_status
付款_date
...

购买

puphoy_id
customer_id
item_id
public_date
....

Payout_purchase

puphoy_id
付款_id

您的逻辑变成：

尚未支付的购买（poyper_id ins of in payout_purchase ofere_id）
cron求职搜索所有
- 在Payout_purchase中创建新记录
- 查找新的Payout_purchase记录的总和
- 尝试支付
- 如果（支出成功）
  - 将记录插入带有供应商ID，支出ID和支付金额的交易表中
- 其他
  - 处理错误情况。这可以删除记录（并在其他地方记录故障），也可以通过添加带有“失败”的“状态”列。后一种选项使您更容易为您的供应商提供声明 - “我们试图向您付款，但付款失败了”。无论哪种方式，您都希望有一种监视失败的方法，并进行监视。
- 如果
下一个供应商，

我忽略了各种状态和错误管理逻辑步骤。

您想担心的事情：

如果付款逻辑运行时发生购买会发生什么？您需要确保您在每个步骤中使用定义的数据集工作。例如，您需要将数据插入“ Payout_purchase”表中，然后仅在这些记录上工作 - 在下一次运行之前，不应包括新的购买。
如果支出失败会发生什么？您必须确保将它们包括在下一个付款运行中。
您如何向买卖双方提供声明？您想要什么级别的细节？
来自MySQL的交易管理可能会有所帮助，但是您需要花时间学习语义和边缘案例。

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.

回复收藏 0 原文

~没有更多了~