支持按月分期付款的发票 Database Design

结论

ScheduledPayments + Payments + PaymentAllocations 的设计是合理的，可以支持：

• 首付和按月分期；
• 单期部分付款；
• 逾期补付；
• 一笔付款覆盖多期；
• 一笔付款覆盖多个 invoice；
• 一期由多笔付款组成。

三张表的职责要分清：ScheduledPayments 记录计划应付金额，Payments 记录实际收到的资金，PaymentAllocations 记录资金的具体分配方式。因此，中间表不能只记录关联关系，还必须保存每次分配的 allocated_amount。

中间表不建议同时保存 invoice_id 和 scheduled_payment_id。通过后者已经可以确定所属 invoice，重复存储会增加数据不一致的风险。

推荐的数据关系

Invoices
    1
    |
    N
ScheduledPayments
    N
    |
    N
Payments

多对多关系通过 PaymentAllocations 实现：

Invoices
- invoice_id
- customer_id
- total_amount
- currency_code
- issued_at

ScheduledPayments
- scheduled_payment_id
- invoice_id
- installment_no
- payment_type
- due_date
- scheduled_amount

Payments
- payment_id
- customer_id
- received_at
- amount
- currency_code
- status
- external_reference

PaymentAllocations
- payment_allocation_id
- payment_id
- scheduled_payment_id
- allocated_amount

ScheduledPayments 保存合同约定的付款计划，不应因客户的实际付款情况而修改计划金额或到期日。实际支付情况应根据分配记录计算。

PostgreSQL 表结构示例

下面假设金额使用 numeric(19, 4)，且同一 invoice 只使用一种货币。

CREATE TABLE invoices (
    invoice_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id       bigint NOT NULL,
    invoice_number    varchar(50) NOT NULL UNIQUE,
    issued_at         timestamptz NOT NULL,
    total_amount      numeric(19, 4) NOT NULL
                      CHECK (total_amount >= 0),
    currency_code     char(3) NOT NULL,
    status            varchar(20) NOT NULL
                      CHECK (status IN ('open', 'paid', 'void'))
);

CREATE TABLE scheduled_payments (
    scheduled_payment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    invoice_id            bigint NOT NULL
                          REFERENCES invoices(invoice_id),
    installment_no        integer NOT NULL
                          CHECK (installment_no >= 0),
    payment_type           varchar(20) NOT NULL
                          CHECK (payment_type IN (
                              'down_payment',
                              'installment',
                              'full_payment'
                          )),
    due_date               date NOT NULL,
    scheduled_amount       numeric(19, 4) NOT NULL
                           CHECK (scheduled_amount > 0),

    UNIQUE (invoice_id, installment_no)
);

CREATE TABLE payments (
    payment_id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id         bigint NOT NULL,
    received_at         timestamptz NOT NULL,
    amount              numeric(19, 4) NOT NULL
                        CHECK (amount > 0),
    currency_code       char(3) NOT NULL,
    status              varchar(20) NOT NULL
                        CHECK (status IN (
                            'pending',
                            'settled',
                            'failed',
                            'reversed'
                        )),
    external_reference  varchar(100)
);

CREATE TABLE payment_allocations (
    payment_allocation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_id            bigint NOT NULL
                          REFERENCES payments(payment_id),
    scheduled_payment_id  bigint NOT NULL
                          REFERENCES scheduled_payments(scheduled_payment_id),
    allocated_amount      numeric(19, 4) NOT NULL
                          CHECK (allocated_amount > 0),

    UNIQUE (payment_id, scheduled_payment_id)
);

CREATE INDEX ix_scheduled_payments_invoice_due
    ON scheduled_payments (invoice_id, due_date);

CREATE INDEX ix_payment_allocations_scheduled_payment
    ON payment_allocations (scheduled_payment_id);

如果同一笔 payment 可以分多次分配到同一期，可以去掉：

UNIQUE (payment_id, scheduled_payment_id)

多数情况下，保留这一约束并更新已有记录的分配金额会更容易管理。

如何生成分期计划

假设 invoice 总额为 1,000.00，客户先支付 100.00，剩余金额分 3 期支付：

首付：100.00
第 1 期：300.00
第 2 期：300.00
第 3 期：300.00

对应的数据可以写成：

INSERT INTO scheduled_payments (
    invoice_id,
    installment_no,
    payment_type,
    due_date,
    scheduled_amount
)
VALUES
    (42, 0, 'down_payment', DATE '2026-09-01', 100.00),
    (42, 1, 'installment',  DATE '2026-10-01', 300.00),
    (42, 2, 'installment',  DATE '2026-11-01', 300.00),
    (42, 3, 'installment',  DATE '2026-12-01', 300.00);

同一 invoice 下所有 scheduled_amount 的总和应等于 invoices.total_amount：

invoice.total_amount
=
SUM(scheduled_payments.scheduled_amount)

普通 CHECK 通常无法完整实现这种跨行约束。可以在创建或修改付款计划的事务中进行验证，也可以使用数据库 trigger、deferred constraint 等机制。

一次性付款不需要单独设计数据模型，只需创建一条计划记录：

INSERT INTO scheduled_payments (
    invoice_id,
    installment_no,
    payment_type,
    due_date,
    scheduled_amount
)
VALUES (
    43,
    1,
    'full_payment',
    DATE '2026-09-30',
    1000.00
);

这样，一次付清和分期付款可以共用同一套查询和核销逻辑。

一笔付款覆盖多个月

假设客户一次支付 600.00，用于支付第 2 期和第 3 期：

BEGIN;

INSERT INTO payments (
    customer_id,
    received_at,
    amount,
    currency_code,
    status,
    external_reference
)
VALUES (
    7,
    CURRENT_TIMESTAMP,
    600.00,
    'CNY',
    'settled',
    'BANK-20261201-001'
)
RETURNING payment_id;

假设返回的 payment_id 为 9001：

INSERT INTO payment_allocations (
    payment_id,
    scheduled_payment_id,
    allocated_amount
)
VALUES
    (9001, 102, 300.00),
    (9001, 103, 300.00);

COMMIT;

如果这笔 600.00 分别用于不同的 invoices，只需让两条分配记录指向各自 invoice 下的 scheduled_payment_id，表结构不需要调整。

部分付款如何记录

假设某一期应付 300.00，客户先支付 100.00，之后又支付 200.00。系统应创建两笔 payment，并将它们分别分配到同一期：

INSERT INTO payment_allocations (
    payment_id,
    scheduled_payment_id,
    allocated_amount
)
VALUES
    (9002, 102, 100.00),
    (9003, 102, 200.00);

可以用下面的查询计算该期已付金额：

SELECT
    sp.scheduled_payment_id,
    sp.scheduled_amount,
    COALESCE(SUM(pa.allocated_amount), 0) AS paid_amount,
    sp.scheduled_amount
        - COALESCE(SUM(pa.allocated_amount), 0) AS outstanding_amount
FROM scheduled_payments AS sp
LEFT JOIN payment_allocations AS pa
    ON pa.scheduled_payment_id = sp.scheduled_payment_id
LEFT JOIN payments AS p
    ON p.payment_id = pa.payment_id
   AND p.status = 'settled'
WHERE sp.scheduled_payment_id = 102
GROUP BY
    sp.scheduled_payment_id,
    sp.scheduled_amount;

不要在每一期中直接维护一个会被反复覆盖的 paid_amount 字段。这类数据可以通过明细计算，单独存储很容易与 allocation 明细产生偏差。如果因性能要求需要缓存该值，也应以 allocation 明细为真实来源，并建立可靠的同步机制。

查询每一期的状态

每期状态可以根据到期日、计划金额和已分配金额计算：

WITH installment_balances AS (
    SELECT
        sp.scheduled_payment_id,
        sp.invoice_id,
        sp.installment_no,
        sp.due_date,
        sp.scheduled_amount,
        COALESCE(
            SUM(pa.allocated_amount)
                FILTER (WHERE p.status = 'settled'),
            0
        ) AS paid_amount
    FROM scheduled_payments AS sp
    LEFT JOIN payment_allocations AS pa
        ON pa.scheduled_payment_id = sp.scheduled_payment_id
    LEFT JOIN payments AS p
        ON p.payment_id = pa.payment_id
    GROUP BY
        sp.scheduled_payment_id,
        sp.invoice_id,
        sp.installment_no,
        sp.due_date,
        sp.scheduled_amount
)
SELECT
    *,
    CASE
        WHEN paid_amount >= scheduled_amount THEN 'paid'
        WHEN paid_amount > 0 AND due_date < CURRENT_DATE THEN 'partially_paid_overdue'
        WHEN paid_amount > 0 THEN 'partially_paid'
        WHEN due_date < CURRENT_DATE THEN 'overdue'
        ELSE 'unpaid'
    END AS payment_status
FROM installment_balances
ORDER BY invoice_id, installment_no;

这样就不必在 ScheduledPayments 中维护容易失真的 paid 或 overdue 标记。overdue 会随日期变化，通常更适合实时计算。

必须实施的业务约束

主键和外键并不足以覆盖所有业务规则，至少还要验证以下内容。

分配金额不能超过实际付款

对于任意 payment：

SUM(payment_allocations.allocated_amount)
<= payments.amount

未分配余额为：

payments.amount
- SUM(payment_allocations.allocated_amount)

这部分余额可以留在客户账户中，等待以后核销。不要为了凑成等式，强行把多付金额分配到某一期。

同一期的累计分配不能超过应付金额

通常应满足：

SUM(payment_allocations.allocated_amount)
<= scheduled_payments.scheduled_amount

如果业务允许客户提前多付并形成信用余额，多出的金额仍应作为 payment 的未分配余额保留，不能让某一期出现负余额。

客户必须一致

被分配的 payment、scheduled payment 和 invoice 必须属于同一客户。由于这一规则涉及多张表，通常需要在分配事务中检查。

货币必须一致

payment 的 currency_code 必须与目标 invoice 一致。除非系统明确支持外汇结算，否则不能直接分配不同货币的金额。

只核销已结算付款

状态为 pending 或 failed 的 payment 不应计入已付金额。payment 何时从 pending 变为 settled，由支付渠道的结算规则决定。

这些累计约束涉及多行数据。执行分配时，最好在同一数据库事务中锁定相关 payment 和 scheduled payment，完成检查后再写入，避免并发请求导致超额分配。

退款、撤销和冲正

已经入账的财务记录不适合直接删除。支付失败、退款或银行撤销发生后，应保留原始 payment，并新增冲正记录或退款记录。

较完整的设计可以增加：

payment_reversals
- reversal_id
- payment_id
- amount
- reversed_at
- reason
- external_reference

如果还要准确记录撤销影响了哪些 installments，就需要配套的 allocation reversal 明细。也可以允许负数 allocation，但这样会明显增加约束和查询的复杂度，不能只通过放宽 CHECK (allocated_amount > 0) 来实现。

分期计划发生变更时的处理

如果 invoice 确认后仍允许协商修改付款计划，不建议直接覆盖已经发生核销的计划记录。可以根据业务情况选择：

• 只允许修改尚未付款的未来 installments；
• 将旧计划标记为 superseded，再创建新版本；
• 通过调整项增加或减少未来应付款；
• 记录计划变更的时间、原因和操作人。

具体做法取决于审计和会计要求。如果 invoice 确认后不可变，付款计划也应在确认时一并冻结，之后的变化通过调整记录表达。

日期和金额的注意事项

生成月度分期日期时，不能简单地反复增加 30 天。需要先确定业务规则，例如：

• 每月固定在 1 日；
• 沿用首期的日号；
• 如果目标月份没有该日，则使用当月最后一天；
• 遇到周末或节假日是否顺延。

拆分金额时还要处理舍入问题。例如，把 100.00 分成 3 期时，不能让每期都保留无限小数。常见做法是前两期各为 33.33，最后一期为 33.34，使各期金额之和严格等于 invoice 总额。

如果系统只支持固定小数位货币，也可以使用最小货币单位的整数存储金额，例如分：

100.00 CNY = 10000

无论选择整数还是 numeric，都不应使用 float 或 double precision 存储财务金额。

对更新方案的评价

更新方案的设计方向没有问题，但建议做以下调整：

1. 将关系表命名为 PaymentAllocations，明确它记录的是资金分配明细，而不是普通关联关系。
2. 在关系表中保存 allocated_amount。
3. 不要在关系表中重复保存 invoice_id，通过 scheduled_payment_id 就能找到 invoice。
4. 将首付也保存为一条 scheduled payment，只需设置不同的 payment_type 和 due_date。
5. 允许 payment 暂时保留未分配余额。
6. 在事务中检查 payment 总额、分期余额、客户和货币。
7. 保留撤销、退款及计划变更的历史记录，不直接删除财务明细。

落实这些约束后，该 schema 可以覆盖一次付清、首付加分期、部分付款、逾期补付，以及跨多期或多个 invoice 核销等场景。

备注：内容仅供参考。