9c3c258f16
All checks were successful
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Successful in 9m27s
- 每卡 Redis 采集队列（默认 N=3、跨天清空、回退容差 1 MB），攒满且不下降才写最后一个点 - 测试环境全量切换日快照计量，下线灰度名单、切换水位与卡表基线扣费；全部卡置待建起点 - 读数区分没给值与 0；有套餐的卡读数 0 也写快照与日用量 0 行；重置日比较基准为 0 照常防旧值 - 新增采集记录（按月分区保留 90 天）、人工冲正流水与后台接口、采集告警 - 扣减按采集时刻有效套餐查找，只对生效中套餐做状态迁移，查不到套餐按成功跳过 - 换货新卡（新设备下的卡）打待建起点；采集间隔上限只校验启用中的轮询配置 Change: add-card-traffic-queue-and-observed-deduction Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
39 lines
2.7 KiB
SQL
39 lines
2.7 KiB
SQL
-- 套餐流量人工冲正流水（add-card-traffic-queue-and-observed-deduction）。
|
||
-- 冲正只通过本表进行：事务内锁套餐行、按当前已用量加减，退回后不得小于 0；冲正不改卡日快照与已结算。
|
||
-- 只新增不修改、不删除；不使用数据库外键（ENG-MODEL-001）。
|
||
CREATE TABLE tb_package_usage_adjustment (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
package_usage_id BIGINT NOT NULL,
|
||
iot_card_id BIGINT NOT NULL DEFAULT 0,
|
||
device_id BIGINT NOT NULL DEFAULT 0,
|
||
delta_mb BIGINT NOT NULL,
|
||
before_usage_mb BIGINT NOT NULL,
|
||
after_usage_mb BIGINT NOT NULL,
|
||
before_status INTEGER NOT NULL,
|
||
after_status INTEGER NOT NULL,
|
||
reason VARCHAR(500) NOT NULL,
|
||
ref_observation_id VARCHAR(128) NOT NULL DEFAULT '',
|
||
ref_event_id VARCHAR(128) NOT NULL DEFAULT '',
|
||
operator_id BIGINT NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT ck_package_usage_adjustment_delta CHECK (delta_mb <> 0),
|
||
CONSTRAINT ck_package_usage_adjustment_after CHECK (after_usage_mb >= 0)
|
||
);
|
||
|
||
CREATE INDEX idx_package_usage_adjustment_usage ON tb_package_usage_adjustment (package_usage_id, created_at DESC);
|
||
CREATE INDEX idx_package_usage_adjustment_card ON tb_package_usage_adjustment (iot_card_id, created_at DESC);
|
||
|
||
COMMENT ON TABLE tb_package_usage_adjustment IS '套餐流量人工冲正流水：正数补扣、负数退回';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.package_usage_id IS '套餐使用记录ID';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.iot_card_id IS '单卡套餐的卡ID，设备套餐为 0';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.device_id IS '设备套餐的设备ID，单卡套餐为 0';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.delta_mb IS '调整量(MB)：正数补扣，负数退回';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.before_usage_mb IS '锁定后调整前的套餐已用量(MB)';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.after_usage_mb IS '调整后的套餐已用量(MB)';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.before_status IS '调整前套餐状态';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.after_status IS '调整后套餐状态';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.reason IS '冲正原因（必填）';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.ref_observation_id IS '关联的采集 ID（可选）';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.ref_event_id IS '关联的流量事件 ID（可选）';
|
||
COMMENT ON COLUMN tb_package_usage_adjustment.operator_id IS '操作人账号ID';
|