Warehouse management 定期计费的 Database Design and Logic

2026-10-24支付阿杰

结论

不要只在托盘表中保存 last_billed_at 或 next_billing_at。更稳妥的做法是分别保存三类数据：

托盘的实际存放时间，包括入库和出库时间。
客户适用的计费规则，包括单价、计费周期和是否按比例收费。
已生成的费用明细，包括计费起止时间、金额和账单状态。

费用明细应作为不会被重复生成的计费台账。要判断“哪些费用已经 billed”，应查询这份台账；要判断“哪些费用已经到期但尚未 billed”，则应根据存放时间和计费规则，找出尚未被台账覆盖的时间段。

这种设计可以处理长期存放、随机出入库、价格调整、补开账单和计费任务重复执行等情况。

先明确计费规则

“一个托盘每月 $X”可能有多种含义。在设计数据库前，需要先确定以下规则：

每个托盘从入库时间开始，每满一个月收费一次。
所有托盘统一按自然月收费。
入库当日立即收取整月费用。
不满一个月是否按天折算。
不满一个月是否仍收取整月费用。
出库当日是否计费。
月末入库时，下一个周期如何计算。

下面采用一组常见规则作为示例：

每个托盘按自己的入库时间划分计费周期。
每个完整周期收取固定月费。
托盘出库时，最后一个不足月的周期按天折算。
时间段使用左闭右开区间 [period_start, period_end)，避免相邻周期在边界时刻重复计费。

如果实际业务规则不同，只需替换周期和金额的计算方式，核心数据模型无需改变。

推荐的数据库结构

客户和托盘

CREATE TABLE customer (
    id              BIGINT PRIMARY KEY,
    name            VARCHAR(200) NOT NULL
);

CREATE TABLE pallet (
    id              BIGINT PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customer(id),
    pallet_code     VARCHAR(100) NOT NULL UNIQUE,
    status          VARCHAR(20) NOT NULL,
    created_at      TIMESTAMP NOT NULL
);

pallet.status 适合快速展示托盘的当前状态，但不能作为计算历史费用的唯一依据。历史计费必须以实际存放记录为准。

托盘存放记录

一个托盘可能出库后再次入库，因此不宜把 arrived_at 和 departed_at 固定在托盘主表中。单独建立存放记录会更灵活：

CREATE TABLE pallet_storage_period (
    id              BIGINT PRIMARY KEY,
    pallet_id       BIGINT NOT NULL REFERENCES pallet(id),
    arrived_at      TIMESTAMP NOT NULL,
    departed_at     TIMESTAMP NULL,
    CHECK (departed_at IS NULL OR departed_at > arrived_at)
);

CREATE INDEX idx_storage_period_open
    ON pallet_storage_period (pallet_id, departed_at);

CREATE INDEX idx_storage_period_arrived
    ON pallet_storage_period (arrived_at, departed_at);

departed_at IS NULL 表示托盘仍在仓库中。

业务层需要保证同一托盘的存放区间不会重叠。如果数据库支持排斥约束，也可以在数据库层直接阻止区间重叠。

计费规则

费率需要保留历史版本。否则，客户价格调整后，过去的账单可能会按新价格重新计算：

CREATE TABLE storage_rate (
    id                  BIGINT PRIMARY KEY,
    customer_id         BIGINT NOT NULL REFERENCES customer(id),
    amount_per_month    DECIMAL(12, 2) NOT NULL,
    currency_code       CHAR(3) NOT NULL,
    effective_from      DATE NOT NULL,
    effective_to        DATE NULL,
    CHECK (amount_per_month >= 0),
    CHECK (
        effective_to IS NULL
        OR effective_to > effective_from
    )
);

CREATE INDEX idx_storage_rate_customer_date
    ON storage_rate (customer_id, effective_from, effective_to);

如果所有客户使用相同费率，可以把费率配置成全局规则；如果每个客户价格不同，则按 customer_id 保存。

账单与费用明细

CREATE TABLE invoice (
    id              BIGINT PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customer(id),
    invoice_number  VARCHAR(100) NOT NULL UNIQUE,
    status          VARCHAR(20) NOT NULL,
    issued_at       TIMESTAMP NOT NULL,
    due_at          TIMESTAMP NULL,
    total_amount    DECIMAL(12, 2) NOT NULL,
    currency_code   CHAR(3) NOT NULL
);

CREATE TABLE storage_charge (
    id                  BIGINT PRIMARY KEY,
    customer_id         BIGINT NOT NULL REFERENCES customer(id),
    pallet_id           BIGINT NOT NULL REFERENCES pallet(id),
    storage_period_id   BIGINT NOT NULL
                            REFERENCES pallet_storage_period(id),
    rate_id             BIGINT NOT NULL REFERENCES storage_rate(id),
    period_start        TIMESTAMP NOT NULL,
    period_end          TIMESTAMP NOT NULL,
    quantity            DECIMAL(12, 6) NOT NULL,
    unit_price          DECIMAL(12, 2) NOT NULL,
    amount              DECIMAL(12, 2) NOT NULL,
    currency_code       CHAR(3) NOT NULL,
    invoice_id          BIGINT NULL REFERENCES invoice(id),
    status              VARCHAR(20) NOT NULL,
    created_at          TIMESTAMP NOT NULL,
    CHECK (period_end > period_start),
    CHECK (quantity >= 0),
    CHECK (amount >= 0),

    UNIQUE (
        storage_period_id,
        period_start,
        period_end
    )
);

CREATE INDEX idx_charge_unbilled
    ON storage_charge (customer_id, status, period_end);

storage_charge 是这套方案的核心。每条记录代表某个托盘在一个明确时间段内产生的一笔费用。

可以使用以下状态：

PENDING：费用已经计算，但尚未加入正式账单。
INVOICED：费用已经加入账单。
VOID：费用已经作废，但记录仍然保留。
ADJUSTED：费用已由调整项抵消或修正。

已经开票的费用不要直接删除或覆盖。发现错误时，应创建冲销或调整记录，保留完整的审计轨迹。

计费任务的处理逻辑

计费任务可以每天运行一次，也可以在需要开票时运行。每次执行都应使用固定的 billing_cutoff，例如任务开始时的 UTC 时间。

对于每条有效的存放记录，依次执行：

找到这条存放记录最后一个已生成费用区间的 period_end。
如果还没有费用记录，则从 arrived_at 开始。
按照计费规则计算下一个周期的结束时间。
只有周期已经结束，才生成对应费用。
如果托盘已经出库，周期结束时间不能晚于 departed_at。
继续处理，直到下一个周期尚未到期。
把生成的 PENDING 费用加入客户账单。

伪代码如下：

cutoff = current_billing_cutoff

for each active_or_recently_closed_storage_period:
    cursor = last_charge.period_end ?? storage_period.arrived_at
    storage_end = storage_period.departed_at ?? cutoff

    while cursor < storage_end:
        normal_end = add_one_billing_month(cursor)
        charge_end = min(normal_end, storage_end, cutoff)

        if charge_end < normal_end
           and storage_period.departed_at is null:
            break

        amount = calculate_amount(
            period_start = cursor,
            period_end = charge_end,
            normal_period_end = normal_end,
            rate = applicable_rate
        )

        insert storage_charge if it does not already exist

        cursor = charge_end

这里需要区分两种情况。托盘仍在仓库时，如果当前月尚未结束，通常不生成不完整周期的费用；托盘已经出库时，则可以立即结算最后一个不足月的周期。

月份计算不能简单使用固定天数

计费月不能直接写成 30 days，因为自然月份可能有 28、29、30 或 31 天。

如果托盘在 1 月 31 日入库，“加一个月”的结果必须由明确的业务规则决定。一种常见处理方式是：

1 月 31 日至 2 月最后一天；
2 月最后一天至 3 月 31 日；
后续周期继续以月末为锚点。

应用程序应保留原始计费锚点，例如入库日是否为月末。不能只对上一个周期的结束时间反复执行普通日期加法，否则日期可能逐月漂移。

例如：

2026-01-31 -> 2026-02-28
2026-02-28 -> 2026-03-31
2026-03-31 -> 2026-04-30

具体实现取决于使用的语言和日期库，但必须采用日历月份运算，不能用秒数或固定天数代替。

不足月费用的计算

如果按照实际天数折算，可以使用：

amount =
    monthly_rate
    × occupied_duration
    ÷ full_cycle_duration

例如，完整周期为 4 月 10 日至 5 月 10 日，托盘在 4 月 25 日出库：

quantity = 15 / 30
amount = monthly_rate × quantity

金额应使用 DECIMAL 计算，不能使用二进制浮点类型。还需要明确最终金额的舍入规则，例如四舍五入到两位小数：

amount = ROUND(monthly_rate × quantity, 2)

如果业务规定不足月也收整月费用，最后一个周期的 quantity 可以固定为 1。如果采用自然日单价，也可以直接保存 daily_rate，避免把月费折算规则藏在程序逻辑中。

如何查询待计费项目

如果费用已经提前生成并处于 PENDING 状态，可以直接查询：

SELECT
    customer_id,
    pallet_id,
    period_start,
    period_end,
    amount,
    currency_code
FROM storage_charge
WHERE status = 'PENDING'
  AND invoice_id IS NULL
  AND period_end <= :billing_cutoff
ORDER BY customer_id, pallet_id, period_start;

生成客户账单时，应在同一个事务内完成以下操作：

创建 invoice。
锁定该客户符合条件的 PENDING 费用。
将这些费用关联到新账单。
汇总金额并更新 invoice.total_amount。
把费用状态改为 INVOICED。
提交事务。

示意 SQL：

BEGIN;

SELECT id, amount
FROM storage_charge
WHERE customer_id = :customer_id
  AND status = 'PENDING'
  AND invoice_id IS NULL
  AND period_end <= :billing_cutoff
FOR UPDATE;

INSERT INTO invoice (
    id,
    customer_id,
    invoice_number,
    status,
    issued_at,
    due_at,
    total_amount,
    currency_code
)
VALUES (
    :invoice_id,
    :customer_id,
    :invoice_number,
    'OPEN',
    :issued_at,
    :due_at,
    :total_amount,
    :currency_code
);

UPDATE storage_charge
SET invoice_id = :invoice_id,
    status = 'INVOICED'
WHERE customer_id = :customer_id
  AND status = 'PENDING'
  AND invoice_id IS NULL
  AND period_end <= :billing_cutoff;

COMMIT;

实际实现时，参与汇总的费用必须与最终更新的记录完全一致。可以先锁定费用 ID，再按这些 ID 执行更新，避免并发插入造成账单总额与费用明细不一致。

如何确定下一次应计费时间

next_billing_at 可以作为缓存字段，提高查询速度，但不能作为唯一事实来源。入库、出库、价格或计费规则发生变化时，这个字段都可能需要更新，很容易与实际数据不同步。

下一次计费时间可以通过以下数据重新计算：

next_billing_at =
    next_cycle_end(
        last_charge.period_end
        ?? storage_period.arrived_at
    )

为了高效查找大量即将到期的托盘，可以在存放记录中保存这个派生字段：

ALTER TABLE pallet_storage_period
ADD COLUMN next_billing_at TIMESTAMP NULL;

CREATE INDEX idx_storage_next_billing
    ON pallet_storage_period (next_billing_at)
    WHERE departed_at IS NULL;

每次生成费用后，在同一事务中更新该字段：

UPDATE pallet_storage_period
SET next_billing_at = :following_cycle_end
WHERE id = :storage_period_id;

即使这个字段因故失准，也能根据存放记录和费用台账重新构建。

防止重复收费

计费任务必须具备幂等性。同一个任务无论执行一次还是多次，结果都应相同。

至少需要采取以下措施：

为费用时间段建立唯一约束。
在同一事务中生成费用并更新 next_billing_at。
捕获唯一键冲突，并把冲突视为该周期已经处理。
多个计费进程并行运行时，对待处理的存放记录加行锁。
每次任务使用固定的 billing_cutoff，不要在循环中反复读取当前时间。
生成账单时锁定费用明细，防止同一笔费用进入两张账单。

如果一个计费周期可能因为价格变化而拆成多条费用，唯一键还应包含费用分段或费率版本。例如：

UNIQUE (
    storage_period_id,
    period_start,
    period_end,
    rate_id
)

价格在周期中途变化

如果某个托盘的计费周期是 4 月 10 日至 5 月 10 日，而费率在 5 月 1 日发生变化，应把这个周期拆成两个费用区间：

[2026-04-10, 2026-05-01) 使用旧费率
[2026-05-01, 2026-05-10) 使用新费率

因此，费用明细需要同时保存 rate_id、unit_price 和时间范围。即使以后修改费率配置，已经生成的费用仍会保留当时实际采用的单价。

时间和时区

仓储计费通常以当地营业日期为准，数据库时间则常以 UTC 保存。以下时间需要明确区分：

实际入库、出库时刻；
计费所属的当地日期；
账单生成时刻；
计费截止时刻。

如果费用按天计算，应先按照仓库所在时区确定日期边界，再转换为 UTC 进行查询。不能默认 UTC 午夜就是仓库营业日的开始。

出库边界也要明确。采用 [arrived_at, departed_at) 后，departed_at 这个时刻本身不产生仓储费用，可以避免边界重复计费。

不建议采用的做法

通常不能只保存以下字段：

pallet.last_billed_at
pallet.next_billing_at
pallet.total_billed

仅靠这些字段无法可靠回答以下问题：

某张账单具体覆盖了哪个时间段；
计费任务是否重复执行；
历史费用使用了哪个费率；
托盘中途出库时如何计算费用；
已开账单为何被调整；
托盘再次入库后对应哪一段存放记录；
某个时间段是否漏收或重复收费。

这些字段可以用作缓存或汇总，但不能代替带有明确时间范围的费用台账。

实际落地建议

这套模型可以归纳为：

存放记录负责描述事实；
费率记录负责描述规则；
费用明细负责描述计算结果；
账单负责描述向客户发出的应收款。

即使每个客户有上千个托盘，这样的数据规模也不算大。为 customer_id、pallet_id、next_billing_at、status 和计费时间范围建立合适的索引后，就可以按批次处理。

上线前应重点测试以下边界情况：

月末入库；
闰年 2 月；
入库后当天出库；
跨时区或夏令时变化；
计费周期中途调整价格；
出库记录补录或更正；
计费任务执行到一半失败后重试；
两个计费任务同时运行；
已开票后发现入库或出库时间错误；
托盘出库后再次入库。

最终，应以 storage_charge 中的时间范围和状态作为“已经收费到哪里”的权威记录，将 next_billing_at 作为可以重新构建的调度缓存。这样既能快速找到待收费托盘，也能追溯每一笔费用的来源。