如何改进 Subscription-based billing system 的数据库设计？

2026-10-24支付阿杰

结论

不要让 TransactionController 根据“用户是否返回网站”来激活订阅，也不要用 is_active = null 表示支付处理中。

更稳妥的做法是增加一层明确的购买单据：

Order -> OrderItem -> Subscription
  |
Payment / Transaction

transactions 只记录资金流并关联 orders；subscriptions 记录订阅生命周期；order_items 则说明这笔钱购买的是订阅还是额外服务。

这样不必在 transactions 中设置多个 nullable foreign key，支付完成后也能准确找到需要激活的订阅。

推荐的数据关系

users
  ├── subscriptions
  └── orders
        ├── order_items
        │     ├── subscription_order_items
        │     └── extra_service_order_items
        └── transactions

各表的职责如下：

orders：一次结算请求，记录应付总额。
order_items：订单中购买的项目。
transactions：支付平台产生的支付、退款等资金记录。
subscriptions：订阅本身及其有效期。
subscription_order_items：将订阅类订单项关联到具体订阅。
extra_service_order_items：记录额外服务，避免混入订阅关系。

示例 schema：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    amount_due BIGINT NOT NULL,
    currency CHAR(3) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    paid_at TIMESTAMP NULL,

    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    item_type VARCHAR(30) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_amount BIGINT NOT NULL,

    FOREIGN KEY (order_id) REFERENCES orders(id)
);

CREATE TABLE subscriptions (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    plan_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    valid_from TIMESTAMP NULL,
    valid_until TIMESTAMP NULL,
    activated_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,

    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (plan_id) REFERENCES plans(id)
);

CREATE TABLE subscription_order_items (
    order_item_id BIGINT PRIMARY KEY,
    subscription_id BIGINT NOT NULL,
    operation VARCHAR(20) NOT NULL,

    FOREIGN KEY (order_item_id) REFERENCES order_items(id),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id)
);

CREATE TABLE transactions (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    provider VARCHAR(30) NOT NULL,
    provider_transaction_id VARCHAR(255) NOT NULL,
    type VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount BIGINT NOT NULL,
    currency CHAR(3) NOT NULL,
    processed_at TIMESTAMP NULL,

    FOREIGN KEY (order_id) REFERENCES orders(id),
    UNIQUE (provider, provider_transaction_id)
);

金额应使用最小货币单位的整数保存，例如人民币分，不要使用 FLOAT：

¥99.00 -> 9900

如果业务能保证一张订单只购买一个对象，也可以直接增加 subscription_purchases、extra_service_purchases 等业务表，并让 transactions 统一关联 order_id。重点在于，交易应关联结算单据，而不是直接关联不同类型的业务对象。

首次购买时如何处理

创建支付请求前，依次完成以下操作：

创建状态为 pending 的 subscription。
创建状态为 pending 的 order。
创建 subscription 类型的 order item。
通过 subscription_order_items 关联 subscription。
使用 order 创建支付平台的支付请求。

这些记录最好在同一个数据库事务中创建：

DB::transaction(function () use ($user, $plan) {
    $subscription = Subscription::create([
        'user_id' => $user->id,
        'plan_id' => $plan->id,
        'status' => 'pending',
    ]);

    $order = Order::create([
        'user_id' => $user->id,
        'status' => 'pending',
        'amount_due' => $plan->price,
        'currency' => 'CNY',
    ]);

    $item = OrderItem::create([
        'order_id' => $order->id,
        'item_type' => 'subscription',
        'quantity' => 1,
        'unit_amount' => $plan->price,
    ]);

    SubscriptionOrderItem::create([
        'order_item_id' => $item->id,
        'subscription_id' => $subscription->id,
        'operation' => 'initial',
    ]);
});

此时订阅还没有生效，但订单与订阅之间已经建立了清晰、可追踪的关系。

不要用 null 表示 status 正在处理中。建议至少定义以下状态：

pending
active
past_due
cancelled
expired

是否允许 pending 状态的用户使用部分功能，应由权限规则决定，不能依赖 nullable boolean。

如何判断“首次购买”

不能根据当前是否存在 active subscription 来判断“首次购买”，因为用户可能以前买过订阅，后来取消了，或者订阅已经过期。

如果“首次购买”指用户历史上第一次成功获得订阅，应检查是否存在曾经激活过的订阅：

SELECT EXISTS (
    SELECT 1
    FROM subscriptions
    WHERE user_id = :user_id
      AND activated_at IS NOT NULL
);

没有记录，说明这是首次成功购买；存在记录，则属于再次购买或续订。

不过，许多场景并不需要在运行时推断。创建订单项时，可以直接记录操作类型：

initial    首次开通
renewal    续订
upgrade    升级
downgrade  降级

例如：

subscription_order_items.operation = 'initial'

这个值必须由订阅服务根据历史记录确定，不能直接采用客户端提交的参数。

如果业务对“首次购买”还有“从未试用”“从未退款”等限制，应分别定义规则，不能只看 subscription 是否存在。

如何确认已经付清

不要只检查某一条 transaction 是否为 paid。一张订单可能遇到以下情况：

多次支付尝试；
部分支付；
支付失败后重试；
重复回调；
退款或部分退款；
不同币种或金额异常。

订单付清至少需要满足：

currency 与订单一致
已成功 capture 的金额合计 - 已退款金额合计 >= amount_due

例如：

SELECT
    COALESCE(SUM(
        CASE
            WHEN type = 'payment' AND status = 'succeeded' THEN amount
            WHEN type = 'refund' AND status = 'succeeded' THEN -amount
            ELSE 0
        END
    ), 0) AS net_paid
FROM transactions
WHERE order_id = :order_id
  AND currency = :currency;

只有 net_paid >= orders.amount_due 时，订单才能进入 paid 状态。是否允许超额支付，需要另外制定退款或余额处理规则。

即使支付平台只提供“一次性完整支付”，也要验证回调中的以下信息：

商户订单号；
支付平台交易号；
金额；
币种；
支付状态；
回调签名；
商户或应用标识。

支付完成后如何激活订阅

浏览器返回地址只能用于展示支付结果，不能作为到账依据。用户可以伪造请求，也可能付款成功后直接关闭页面，不再返回网站。

应以支付平台经过签名验证的 webhook 为准：

public function handlePaymentWebhook(array $payload): void
{
    $event = $this->gateway->verifyWebhook($payload);

    DB::transaction(function () use ($event) {
        $order = Order::where('id', $event->orderId)
            ->lockForUpdate()
            ->firstOrFail();

        Transaction::firstOrCreate(
            [
                'provider' => $event->provider,
                'provider_transaction_id' => $event->transactionId,
            ],
            [
                'order_id' => $order->id,
                'type' => 'payment',
                'status' => $event->status,
                'amount' => $event->amount,
                'currency' => $event->currency,
                'processed_at' => now(),
            ]
        );

        if (! $this->paymentService->isFullyPaid($order)) {
            return;
        }

        if ($order->status === 'paid') {
            return;
        }

        $order->update([
            'status' => 'paid',
            'paid_at' => now(),
        ]);

        $items = $order->items()
            ->where('item_type', 'subscription')
            ->get();

        foreach ($items as $item) {
            $subscription = $item->subscriptionPurchase->subscription;

            $subscription->update([
                'status' => 'active',
                'valid_from' => now(),
                'valid_until' => now()->addMonth(),
                'activated_at' => $subscription->activated_at ?? now(),
            ]);
        }
    });
}

示例中的 addMonth() 只用于说明流程。实际有效期必须根据 plan 的计费周期计算，同时明确月末、时区、闰年和续订叠加规则。

不要让 Controller 承担业务协调

TransactionController 和 SubscriptionController 分开，并不妨碍数据库建立关联。Controller 只是入口，支付确认和订阅激活应由应用服务协调，例如：

PaymentWebhookController
    -> PaymentService
        -> OrderService
            -> SubscriptionService
            -> ExtraServiceService

确认订单付清后，可以同步履约，也可以发布 OrderPaid 事件：

event(new OrderPaid($order->id));

对应的监听器再分别处理订阅、额外服务和付款凭证：

ActivateSubscription
ProvisionExtraService
SendPaymentReceipt

如果使用异步队列，建议配合 outbox pattern，避免数据库已经提交订单状态，事件却因进程中断而未能发出。

需要特别注意的事项

支付 webhook 必须具备幂等性，重复通知不能导致订阅被重复延长。
provider_transaction_id 应设置唯一约束。
激活订阅前应锁定 order，防止并发回调造成重复履约。
不要用一个 is_active 字段承载完整生命周期，应优先使用含义明确的 status。
支付完成前，valid_from 和 valid_until 不应表示订阅已经生效。
首次购买、续订、升级和额外服务，都应保存为明确的订单项类型或操作类型。
浏览器回跳页面只负责查询订单状态，不能把订单改成 paid。
对账应以支付平台记录和本地 transactions 为依据，并处理退款、拒付和回调丢失。
如果支付平台支持 Payment Intent、Invoice 或 Checkout Session，应保存对应的外部 ID，并通过唯一约束防止重复绑定。

最终，subscription 是否激活不再取决于某个 Controller 的执行顺序，而是由清晰的状态转换决定：

订单创建
-> 支付平台确认到账
-> 本地核对金额与币种
-> order 变为 paid
-> 根据 order_items 履约
-> subscription 变为 active

这套关系也适用于首次购买、续订和额外服务，无需为每一种商品类型在 transactions 中增加 nullable foreign key。