896 lines
77 KiB
SQL
896 lines
77 KiB
SQL
-- 本地服务结构补齐；仅创建缺失表、添加缺失字段，不删除现有数据。
|
||
SET NAMES utf8mb4;
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN product_name VARCHAR(255) NULL COMMENT '商品名称';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN estimated_pallet_count INT NULL COMMENT '客户申报预计托数，不代表实际收货';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN origin_contact_name VARCHAR(128) NULL COMMENT '起运地联系人';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN origin_contact_phone VARCHAR(128) NULL COMMENT '起运地联系电话';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN destination_contact_name VARCHAR(128) NULL COMMENT '目的地联系人';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN destination_contact_phone VARCHAR(128) NULL COMMENT '目的地联系电话';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN pickup_at DATETIME(3) NULL COMMENT '取货时间（当地时间，不转换时区）';
|
||
|
||
ALTER TABLE `ll_local_looseboard_order` ADD COLUMN delivery_at DATETIME(3) NULL COMMENT '送货时间（当地时间，不转换时区）';
|
||
|
||
CREATE TABLE ll_appointment_followup (
|
||
id BIGINT NOT NULL COMMENT '预约筹划跟进记录ID',
|
||
source_key VARCHAR(160) NOT NULL COMMENT '稳定ORDER、BOOKING或CONTAINER及接仓点来源键',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '货源客户；整柜共享记录由服务端固定为平台作用域0',
|
||
source_order_id BIGINT NULL COMMENT '已形成的真实尾程货源ID，尚未生成时为空',
|
||
stage VARCHAR(32) NOT NULL COMMENT '跟进对象：DRAYAGE_CONTACT/TERMINAL_APPOINTMENT/WAREHOUSE_RECEIVING/FBA_RECEIVING',
|
||
version INT NOT NULL COMMENT '同一货源及跟进对象的记录版本',
|
||
contacted_party VARCHAR(200) NULL COMMENT '本次联系对象，不替代承运主档',
|
||
result VARCHAR(1000) NOT NULL COMMENT '本次反馈事实，不自动确认预约或推进实物作业',
|
||
next_action VARCHAR(500) NULL COMMENT '下一步安排',
|
||
followup_date DATE NULL COMMENT '按业务地点当地日期记录的下次跟进日',
|
||
operator_id VARCHAR(128) NOT NULL COMMENT '登录平台操作者ID',
|
||
operator_name VARCHAR(200) NULL COMMENT '操作人姓名快照',
|
||
command_key VARCHAR(64) NOT NULL COMMENT '持久幂等键',
|
||
request_hash CHAR(64) NOT NULL COMMENT '含操作者身份的请求SHA256摘要',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC记录时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_appointment_followup_version (source_key,stage,version),
|
||
UNIQUE KEY uk_appointment_followup_command (source_key,stage,command_key),
|
||
KEY idx_appointment_followup_date (followup_date,source_order_id),
|
||
CONSTRAINT fk_appointment_followup_order FOREIGN KEY (source_order_id) REFERENCES ll_order(id),
|
||
CONSTRAINT chk_appointment_followup_stage CHECK (stage IN ('DRAYAGE_CONTACT','TERMINAL_APPOINTMENT','WAREHOUSE_RECEIVING','FBA_RECEIVING'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='真实在途货源的预约筹划联系记录，追加留痕';
|
||
|
||
CREATE TABLE ll_dispatch_command (
|
||
id BIGINT NOT NULL COMMENT '持久命令内部主键',
|
||
dispatch_id BIGINT NOT NULL COMMENT '命令所属整车排车主单ID',
|
||
dispatch_no VARCHAR(64) NOT NULL COMMENT '命令所属真实排车业务号',
|
||
action VARCHAR(40) NOT NULL COMMENT '本次整车作业动作',
|
||
operator_id VARCHAR(128) NOT NULL COMMENT '原始可信操作主体ID',
|
||
idempotency_key VARCHAR(255) NOT NULL COMMENT '跨操作主体全局唯一的原始请求键',
|
||
record_version BIGINT NOT NULL COMMENT '原请求明确提交的排车版本，零为合法首版',
|
||
command_hash CHAR(64) NOT NULL COMMENT '绑定原主体动作业务号版本和完整载荷的严格摘要',
|
||
result_json LONGTEXT NOT NULL COMMENT '按动作类型恢复的不可变成功结果JSON',
|
||
created_at DATETIME(3) NOT NULL COMMENT '成功命令记录时间，使用UTC',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_dispatch_command_key (idempotency_key),
|
||
KEY idx_dispatch_command_history (dispatch_id,created_at,id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='整车排车公共持久命令账本';
|
||
|
||
CREATE TABLE ll_dispatch_delivery_arrangement (
|
||
id BIGINT NOT NULL COMMENT '安排事实内部主键',
|
||
dispatch_order_id BIGINT NOT NULL COMMENT '唯一整车排车主单ID',
|
||
dispatch_no VARCHAR(128) NOT NULL COMMENT '真实排车业务号',
|
||
arrangement_key VARCHAR(128) NOT NULL COMMENT '本次唯一安排业务事实标识',
|
||
business_hash CHAR(64) NOT NULL COMMENT '排除操作者及纯并发版本的安排业务摘要',
|
||
cargo_hash CHAR(64) NOT NULL COMMENT '本车实际拣货来源摘要',
|
||
loading_completion_hash CHAR(64) NOT NULL COMMENT '装车完成事实摘要',
|
||
original_command_key VARCHAR(255) NOT NULL COMMENT '首次安排的全局请求键',
|
||
original_operator_id VARCHAR(128) NOT NULL COMMENT '首次安排操作主体',
|
||
result_json JSON NOT NULL COMMENT '全部客户出库及客户目的地派送不可变结果',
|
||
cargo_json JSON NOT NULL COMMENT '逐客户逐拣货归属的不可变真实来源',
|
||
created_at DATETIME(3) NOT NULL COMMENT '本次安排实际创建时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_dispatch_arrangement_dispatch (dispatch_order_id),
|
||
UNIQUE KEY uk_dispatch_arrangement_key (arrangement_key),
|
||
UNIQUE KEY uk_dispatch_arrangement_command (original_command_key)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='整车排车唯一派送安排事实';
|
||
|
||
CREATE TABLE ll_delivery_facts_changed (
|
||
id BIGINT NOT NULL COMMENT '持久信号内部主键',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '真实签收货物客户ID',
|
||
dispatch_id BIGINT NOT NULL COMMENT '整车排车主单ID',
|
||
dispatch_no VARCHAR(128) NOT NULL COMMENT '真实排车业务号',
|
||
delivery_id BIGINT NOT NULL COMMENT '本客户派送单ID',
|
||
delivery_no VARCHAR(128) NOT NULL COMMENT '真实派送业务号',
|
||
delivery_version BIGINT NOT NULL COMMENT '触发信号的真实派送版本',
|
||
facts_hash CHAR(64) NOT NULL COMMENT '该版本真实签收事实摘要',
|
||
occurred_at DATETIME(3) NOT NULL COMMENT '签收事实实际发生时间',
|
||
state VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT '待核对PENDING或已核对PROCESSED',
|
||
attempt_count INT NOT NULL DEFAULT 0 COMMENT '已记录的汇总失败次数',
|
||
next_attempt_at DATETIME(3) NOT NULL COMMENT '下次可重试时间',
|
||
last_error_code VARCHAR(64) NULL COMMENT '最近失败诊断码不保存敏感异常正文',
|
||
processed_at DATETIME(3) NULL COMMENT '与主阶段汇总同事务确认时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_delivery_fact_version (customer_id,delivery_id,delivery_version),
|
||
KEY idx_delivery_fact_due (state,next_attempt_at,id),
|
||
KEY idx_delivery_fact_dispatch (dispatch_id,state,id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='正常签收事实变化的本地持久待汇总信号';
|
||
|
||
ALTER TABLE `ll_outbound_pallet_item` ADD COLUMN dispatch_item_id BIGINT NULL COMMENT '本次整车排车作业明细ID';
|
||
|
||
ALTER TABLE `ll_outbound_pallet_item` ADD COLUMN source_attribution_id BIGINT NULL COMMENT '不可变PDA真实拣货归属ID';
|
||
|
||
ALTER TABLE `ll_outbound_pallet_item` ADD COLUMN source_order_id BIGINT NULL COMMENT '本行真实来源尾程订单ID';
|
||
|
||
ALTER TABLE `ll_outbound_pallet_item` ADD COLUMN source_inventory_position_id BIGINT NULL COMMENT '本行实际拣货库存仓位ID';
|
||
|
||
ALTER TABLE `ll_outbound_pallet_item` ADD COLUMN source_return_receipt_id BIGINT NULL COMMENT '本行实际退回再派实收记录ID';
|
||
|
||
ALTER TABLE `ll_outbound_pallet_item` ADD COLUMN source_return_carton_id BIGINT NULL COMMENT '本行实际退货换标来源箱ID';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN purpose VARCHAR(32) NULL COMMENT '费用业务用途；由履约节点确定';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN confirmation_origin VARCHAR(16) NOT NULL DEFAULT 'BUSINESS' COMMENT '核定入口：BUSINESS业务节点、FINANCIAL财务操作';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN fee_version INT NULL COMMENT '同用途费用确认版本';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否当前有效版本';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN determination VARCHAR(24) NULL COMMENT 'POSITIVE正额、ZERO零元、NOT_APPLICABLE不适用';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN original_currency CHAR(3) NULL COMMENT '核定原币CNY或USD';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN original_amount DECIMAL(20,2) NULL COMMENT '核定原币金额';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN exchange_rate DECIMAL(20,6) NULL COMMENT '已确认汇率快照';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN exchange_rate_month CHAR(7) NULL COMMENT '已确认汇率所属月份';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN business_date DATE NULL COMMENT '实际费用业务日期';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN wallet_business_id BIGINT NULL COMMENT '资金冻结对应采购费用记录';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN wallet_business_no VARCHAR(128) NULL COMMENT '资金冻结业务号；实际国际运费引用原预估';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN wallet_hold_id VARCHAR(64) NULL COMMENT 'Finance冻结引用';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN wallet_ledger_id VARCHAR(64) NULL COMMENT 'Finance实际处理流水引用';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN settled_command_event_id VARCHAR(64) NULL COMMENT '成功实扣或退款的资金命令事件';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN source_fee_id BIGINT NULL COMMENT '已实扣费用来源ID';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN parent_fee_id BIGINT NULL COMMENT '原费用或预估引用';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_fee` ADD COLUMN request_hash CHAR(64) NULL COMMENT '确认请求摘要';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_status_history` ADD COLUMN operation_type varchar(32) COLLATE utf8mb4_unicode_ci NULL COMMENT '操作类型：CREATE、REVIEW或UPDATE';
|
||
|
||
ALTER TABLE `ll_overseas_procurement_status_history` ADD COLUMN change_detail json DEFAULT NULL COMMENT '字段变更前后值JSON';
|
||
|
||
ALTER TABLE `ll_receiving_container_session` ADD COLUMN source_container_booking_id varchar(128) COLLATE utf8mb4_unicode_ci NULL COMMENT '稳定来源柜预订ID';
|
||
|
||
ALTER TABLE `ll_receiving_container_session` ADD COLUMN container_fact_hash varchar(64) COLLATE utf8mb4_unicode_ci NULL COMMENT '领取时整柜订单与计划事实摘要';
|
||
|
||
ALTER TABLE `ll_receiving_container_session` ADD COLUMN overseas_warehouse_code varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '海外接柜仓业务编码快照';
|
||
|
||
ALTER TABLE `ll_return_inventory_outbound_movement` ADD COLUMN return_sku_quantity BIGINT NULL COMMENT '退货单SKU本来源计划出库件数快照，NULL为历史整箱出库';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN vehicle_type VARCHAR(32) NULL COMMENT '车辆类型：PALLET_15为15拖，PALLET_30为30拖；拼车未提供时为空';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN origin_contact_name VARCHAR(128) NULL COMMENT '起运地联系人';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN origin_contact_phone VARCHAR(128) NULL COMMENT '起运地联系电话';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN destination_contact_name VARCHAR(128) NULL COMMENT '目的地联系人';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN destination_contact_phone VARCHAR(128) NULL COMMENT '目的地联系电话';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN pickup_at DATETIME NULL COMMENT '取货时间，按填写的日期时分保存，不转换时区';
|
||
|
||
ALTER TABLE `ll_us_truck_order` ADD COLUMN delivery_at DATETIME NULL COMMENT '送货时间，按填写的日期时分保存；拼车来源未提供时为空';
|
||
|
||
CREATE TABLE event_delivery_retry_audit (
|
||
request_id VARCHAR(36) NOT NULL COMMENT '人工重试请求UUID和幂等键',
|
||
producer_table VARCHAR(64) NOT NULL COMMENT '本服务发件箱表名',
|
||
entry_id VARCHAR(64) NOT NULL COMMENT '发件箱记录主键',
|
||
event_id VARCHAR(128) NOT NULL COMMENT '原事件全局标识',
|
||
event_type VARCHAR(64) NOT NULL COMMENT '原事件类型',
|
||
payload_hash VARCHAR(71) NOT NULL COMMENT '原事件不可变负载摘要',
|
||
operator_name VARCHAR(128) NOT NULL COMMENT '持有运维凭据的操作人标识',
|
||
reason VARCHAR(500) NOT NULL COMMENT '人工重试原因',
|
||
previous_failures INT NOT NULL COMMENT '重试前累计失败次数',
|
||
previous_error VARCHAR(1000) NULL COMMENT '重试前最后一次脱敏错误',
|
||
requested_at DATETIME(3) NOT NULL COMMENT '重试申请提交时间',
|
||
PRIMARY KEY (request_id),
|
||
KEY idx_event_retry_entry (producer_table,entry_id,requested_at),
|
||
KEY idx_event_retry_event (event_id,requested_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务事件人工重试审计，与恢复待发送状态同事务提交';
|
||
|
||
CREATE TABLE ll_procurement_wallet_attempt (
|
||
id BIGINT NOT NULL COMMENT '请求尝试ID',
|
||
order_id BIGINT NOT NULL COMMENT '采购订单ID',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
fee_id BIGINT NOT NULL COMMENT '不可变费用版本ID',
|
||
attempt_no BIGINT NOT NULL COMMENT '费用版本内请求序号',
|
||
operation VARCHAR(32) NOT NULL COMMENT '资金操作：FREEZE冻结、SETTLE结算、SETTLE_ACTUAL实际结算、RELEASE释放、REFUND退款',
|
||
business_no VARCHAR(128) NOT NULL COMMENT 'Finance资金业务号',
|
||
amount DECIMAL(20,2) NULL COMMENT '请求金额；全额结算或释放可空',
|
||
command_event_id VARCHAR(64) NOT NULL COMMENT '资金指令事件唯一引用',
|
||
status VARCHAR(16) NOT NULL COMMENT '资金请求结果：PENDING未决、SUCCEEDED成功、FAILED明确失败',
|
||
result_hash CHAR(64) NULL COMMENT '原业务回执摘要',
|
||
wallet_hold_id VARCHAR(64) NULL COMMENT 'Finance冻结引用',
|
||
wallet_ledger_id VARCHAR(64) NULL COMMENT 'Finance处理流水引用',
|
||
error_code VARCHAR(64) NULL COMMENT '业务失败码',
|
||
error_message VARCHAR(500) NULL COMMENT '内部失败诊断，不直接对客展示',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '请求时间',
|
||
completed_at DATETIME(3) NULL COMMENT '最终结果时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_attempt_event(command_event_id),
|
||
UNIQUE KEY uk_procurement_attempt_number(fee_id,attempt_no),
|
||
KEY idx_procurement_attempt_order(customer_id,order_id,status),
|
||
CONSTRAINT chk_procurement_attempt_status CHECK(status IN('PENDING','SUCCEEDED','FAILED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购费用资金命令尝试与不可变结果';
|
||
|
||
CREATE TABLE ll_procurement_flow (
|
||
order_id BIGINT NOT NULL COMMENT '采购订单ID，一单一个履约批次',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
purchased_at DATETIME(3) NULL COMMENT '真实购买时间',
|
||
purchased_product_name VARCHAR(255) NULL COMMENT '实际购买商品',
|
||
purchased_specification VARCHAR(500) NULL COMMENT '实际购买规格',
|
||
purchased_quantity BIGINT NULL COMMENT '实际购买件数',
|
||
purchase_amount DECIMAL(20,2) NULL COMMENT '供应商实付原币金额',
|
||
purchase_currency CHAR(3) NULL COMMENT '供应商实付原币CNY或USD',
|
||
purchase_voucher_id BIGINT NULL COMMENT '购买凭证引用',
|
||
purchase_issue VARCHAR(500) NULL COMMENT '购买未核定或异常说明',
|
||
purchase_confirmed_at DATETIME(3) NULL COMMENT '货款核定时间，不代表扣费成功',
|
||
actual_warehouse_id BIGINT NULL COMMENT '首次实际收货冻结的仓库ID',
|
||
actual_warehouse_code VARCHAR(128) NULL COMMENT '实际收货仓编码快照',
|
||
actual_warehouse_name VARCHAR(255) NULL COMMENT '实际收货仓名称快照',
|
||
overseas_received_at DATETIME(3) NULL COMMENT '海外整批收货完成时间',
|
||
packing_version INT NULL COMMENT '当前确认装箱清单版本',
|
||
overseas_shipped_at DATETIME(3) NULL COMMENT '国际段整批实际交寄时间',
|
||
domestic_received_at DATETIME(3) NULL COMMENT '国内整批收货完成时间',
|
||
charges_confirmed_at DATETIME(3) NULL COMMENT '国内整单费用核定时间',
|
||
customer_shipped_at DATETIME(3) NULL COMMENT '国内寄客户实际交寄时间',
|
||
domestic_receiver_name VARCHAR(128) NULL COMMENT '国际段国内收件人',
|
||
domestic_receiver_phone VARCHAR(64) NULL COMMENT '国际段国内收件电话',
|
||
domestic_receiver_address VARCHAR(1000) NULL COMMENT '国际段国内接收地址',
|
||
cancelled_at DATETIME(3) NULL COMMENT '购买前取消时间',
|
||
closed_at DATETIME(3) NULL COMMENT '异常关闭时间',
|
||
control_reason VARCHAR(1000) NULL COMMENT '取消或关闭原因',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
||
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
||
PRIMARY KEY(order_id), KEY idx_procurement_flow_customer(customer_id,order_id),
|
||
CONSTRAINT chk_procurement_purchase_quantity CHECK(purchased_quantity IS NULL OR purchased_quantity>0),
|
||
CONSTRAINT chk_procurement_purchase_amount CHECK(purchase_amount IS NULL OR purchase_amount>=0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购单一批次履约事实';
|
||
|
||
CREATE TABLE ll_procurement_command (
|
||
id BIGINT NOT NULL COMMENT '操作记录ID', order_id BIGINT NOT NULL COMMENT '采购订单ID',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键', action VARCHAR(48) NOT NULL COMMENT '采购节点动作',
|
||
idempotency_key VARCHAR(128) NOT NULL COMMENT '操作幂等键', request_hash CHAR(64) NOT NULL COMMENT '请求摘要',
|
||
operator_id VARCHAR(128) NOT NULL COMMENT '执行人', reason VARCHAR(1000) NOT NULL COMMENT '操作依据',
|
||
detail_json JSON NOT NULL COMMENT '内部操作及更正依据快照，不直接对客展示',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '执行时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_command(order_id,action,idempotency_key),
|
||
KEY idx_procurement_command_customer(customer_id,order_id,created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购节点幂等与不可变审计';
|
||
|
||
CREATE TABLE ll_procurement_budget_authorization (
|
||
id BIGINT NOT NULL COMMENT '预算授权ID', order_id BIGINT NOT NULL COMMENT '采购订单ID',
|
||
customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
previous_amount DECIMAL(20,2) NULL COMMENT '原货款预算总上限；未授权保持空', previous_currency CHAR(3) NOT NULL COMMENT '原预算币种',
|
||
authorized_amount DECIMAL(20,2) NOT NULL COMMENT '客户授权的新货款总上限', currency CHAR(3) NOT NULL COMMENT '客户授权币种',
|
||
voucher_file_id BIGINT NOT NULL COMMENT '客户确认书面或聊天凭证', reason VARCHAR(1000) NOT NULL COMMENT '授权依据',
|
||
reviewed_by VARCHAR(128) NOT NULL COMMENT '核验人员', reviewed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '核验时间',
|
||
PRIMARY KEY(id), KEY idx_procurement_budget_order(customer_id,order_id,id),
|
||
CONSTRAINT chk_procurement_budget_amount CHECK(authorized_amount>=0 AND previous_amount>=0),
|
||
CONSTRAINT chk_procurement_budget_currency CHECK(currency IN('CNY','USD') AND previous_currency IN('CNY','USD'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购货款预算授权核验历史';
|
||
|
||
CREATE TABLE ll_procurement_receipt (
|
||
id BIGINT NOT NULL COMMENT '实收明细ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
receipt_no VARCHAR(64) NOT NULL COMMENT '采购收货业务编号', leg VARCHAR(16) NOT NULL COMMENT 'OVERSEAS海外或DOMESTIC国内',
|
||
source_carton_id BIGINT NULL COMMENT '国内实收关联原国际交寄箱，海外为空', warehouse_id BIGINT NULL COMMENT '海外实际收货仓',
|
||
good_quantity BIGINT NOT NULL COMMENT '可发合格件数', damaged_quantity BIGINT NOT NULL COMMENT '破损待处置件数', extra_quantity BIGINT NOT NULL COMMENT '多到件数，不增加可发数量',
|
||
received_at DATETIME(3) NOT NULL COMMENT '实际收货时间', active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '当前有效实收事实',
|
||
supersedes_id BIGINT NULL COMMENT '受控更正所替代的原实收记录', created_by VARCHAR(128) NOT NULL COMMENT '登记人员',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '登记时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_receipt_no(receipt_no), KEY idx_procurement_receipt_source(order_id,leg,source_carton_id,active),
|
||
CONSTRAINT chk_procurement_receipt_quantity CHECK(good_quantity>=0 AND damaged_quantity>=0 AND extra_quantity>=0 AND good_quantity+damaged_quantity+extra_quantity>0),
|
||
CONSTRAINT chk_procurement_receipt_leg CHECK((leg='OVERSEAS' AND source_carton_id IS NULL) OR (leg='DOMESTIC' AND source_carton_id IS NOT NULL))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购分次实收与受控更正事实';
|
||
|
||
CREATE TABLE ll_procurement_disposition (
|
||
id BIGINT NOT NULL COMMENT '差异处理ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
leg VARCHAR(16) NOT NULL COMMENT 'OVERSEAS海外或DOMESTIC国内', source_carton_id BIGINT NULL COMMENT '国内原交寄箱来源',
|
||
receipt_id BIGINT NULL COMMENT '破损或多货对应实收记录，短少为空', kind VARCHAR(24) NOT NULL COMMENT 'SHORTAGE短少、DAMAGED破损、EXTRA多货',
|
||
quantity BIGINT NOT NULL COMMENT '已批准处置件数', decision VARCHAR(32) NOT NULL COMMENT 'ACCEPT_SHORTAGE确认短少、DISPOSE_DAMAGED拒收或处置、RETURN_EXTRA退回多货',
|
||
active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '当前有效处置；撤销只改变有效性并保留原事实',
|
||
voucher_file_id BIGINT NOT NULL COMMENT '本单客户确认或实际处置凭证', reason VARCHAR(1000) NOT NULL COMMENT '处置依据',
|
||
created_by VARCHAR(128) NOT NULL COMMENT '采购管理人员', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '确认时间',
|
||
PRIMARY KEY(id), KEY idx_procurement_disposition_source(order_id,leg,source_carton_id,receipt_id,kind),
|
||
CONSTRAINT chk_procurement_disposition_quantity CHECK(quantity>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购单内差异处置，不自动退款';
|
||
|
||
CREATE TABLE ll_procurement_manifest (
|
||
id BIGINT NOT NULL COMMENT '装箱清单ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
version INT NOT NULL COMMENT '订单内装箱版本', status VARCHAR(16) NOT NULL COMMENT 'DRAFT草稿、CONFIRMED已确认、SUPERSEDED已替代',
|
||
confirmed_at DATETIME(3) NULL COMMENT '确认时间', created_by VARCHAR(128) NOT NULL COMMENT '制单人',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '版本保存时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_manifest_version(order_id,version),
|
||
CONSTRAINT chk_procurement_manifest_status CHECK(status IN('DRAFT','CONFIRMED','SUPERSEDED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购装箱清单不可变版本';
|
||
|
||
CREATE TABLE ll_procurement_carton (
|
||
id BIGINT NOT NULL COMMENT '版本内箱记录ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', manifest_id BIGINT NOT NULL COMMENT '装箱清单版本',
|
||
carton_no VARCHAR(64) NOT NULL COMMENT '采购订单内业务箱号', length_cm DECIMAL(12,2) NOT NULL COMMENT '外箱长厘米',
|
||
width_cm DECIMAL(12,2) NOT NULL COMMENT '外箱宽厘米', height_cm DECIMAL(12,2) NOT NULL COMMENT '外箱高厘米',
|
||
gross_weight_kg DECIMAL(12,3) NOT NULL COMMENT '封箱毛重千克', quantity BIGINT NOT NULL COMMENT '本箱合格商品总件数',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_carton_version(manifest_id,carton_no), KEY idx_procurement_carton_order(order_id,manifest_id),
|
||
CONSTRAINT chk_procurement_carton_measurement CHECK(length_cm>0 AND width_cm>0 AND height_cm>0 AND gross_weight_kg>0 AND quantity>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购版本化逐箱实测与件数';
|
||
|
||
CREATE TABLE ll_procurement_carton_source (
|
||
carton_id BIGINT NOT NULL COMMENT '版本内箱ID', receipt_id BIGINT NOT NULL COMMENT '海外实收明细来源', quantity BIGINT NOT NULL COMMENT '从该实收装入本箱的件数',
|
||
PRIMARY KEY(carton_id,receipt_id), KEY idx_procurement_carton_receipt(receipt_id), CONSTRAINT chk_procurement_carton_source_quantity CHECK(quantity>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购箱内货物的实收来源与数量';
|
||
|
||
CREATE TABLE ll_procurement_shipment (
|
||
id BIGINT NOT NULL COMMENT '采购交寄ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
shipment_no VARCHAR(64) NOT NULL COMMENT '交寄业务编号', leg VARCHAR(16) NOT NULL COMMENT 'OVERSEAS国际交寄或DOMESTIC寄客户',
|
||
manifest_id BIGINT NOT NULL COMMENT '原海外装箱清单版本', shipped_at DATETIME(3) NOT NULL COMMENT '整批真实交承运方时间',
|
||
address_snapshot JSON NOT NULL COMMENT '本段交寄时的收寄姓名电话地址快照', created_by VARCHAR(128) NOT NULL COMMENT '交寄确认人',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '确认时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_shipment_leg(order_id,leg), UNIQUE KEY uk_procurement_shipment_no(shipment_no),
|
||
CONSTRAINT chk_procurement_shipment_leg CHECK(leg IN('OVERSEAS','DOMESTIC'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购两段各一次整批真实交寄';
|
||
|
||
CREATE TABLE ll_procurement_shipment_item (
|
||
shipment_id BIGINT NOT NULL COMMENT '采购交寄ID', carton_id BIGINT NOT NULL COMMENT '原海外装箱版本内箱ID', quantity BIGINT NOT NULL COMMENT '本次实际交寄件数',
|
||
carrier VARCHAR(128) NOT NULL COMMENT '承运商快照', tracking_no VARCHAR(128) NOT NULL COMMENT '运单号快照',
|
||
label_snapshot JSON NOT NULL COMMENT '当次箱规、面单及货物来源快照',
|
||
PRIMARY KEY(shipment_id,carton_id), CONSTRAINT chk_procurement_shipment_item_quantity CHECK(quantity>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购整批交寄逐箱事实';
|
||
|
||
CREATE TABLE ll_procurement_label_set (
|
||
id BIGINT NOT NULL COMMENT '面单批版本ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
leg VARCHAR(16) NOT NULL COMMENT 'OVERSEAS国际段或DOMESTIC寄客户段', version INT NOT NULL COMMENT '订单段内面单版本',
|
||
manifest_id BIGINT NOT NULL COMMENT '原海外确认装箱版本', cargo_hash CHAR(64) NOT NULL COMMENT '箱规及有效货物来源摘要',
|
||
sender_snapshot JSON NOT NULL COMMENT '寄件人姓名电话地址快照', receiver_snapshot JSON NOT NULL COMMENT '收件人姓名电话地址快照',
|
||
created_by VARCHAR(128) NOT NULL COMMENT '制单操作人', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '上传确认时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_label_version(order_id,leg,version),
|
||
CONSTRAINT chk_procurement_label_leg CHECK(leg IN('OVERSEAS','DOMESTIC'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购两段面单不可变版本和地址快照';
|
||
|
||
CREATE TABLE ll_procurement_label_item (
|
||
label_set_id BIGINT NOT NULL COMMENT '面单批版本ID', carton_id BIGINT NOT NULL COMMENT '原海外清单箱ID',
|
||
file_id BIGINT NOT NULL COMMENT '本采购订单面单文件', page_no INT NOT NULL COMMENT '该箱对应文件页码，从1开始',
|
||
carrier VARCHAR(128) NOT NULL COMMENT '承运商', tracking_no VARCHAR(128) NOT NULL COMMENT '箱主或子运单号',
|
||
applied_at DATETIME(3) NULL COMMENT '真实贴单时间', applied_by VARCHAR(128) NULL COMMENT '贴单确认人',
|
||
PRIMARY KEY(label_set_id,carton_id), CONSTRAINT chk_procurement_label_page CHECK(page_no>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购逐箱文件页关联及贴单事实';
|
||
|
||
CREATE TABLE ll_procurement_estimate_review (
|
||
id BIGINT NOT NULL COMMENT '预估核对ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', label_set_id BIGINT NOT NULL COMMENT '已核对国际面单版本',
|
||
fee_id BIGINT NOT NULL COMMENT '唯一有效预估费用版本', reviewed_by VARCHAR(128) NOT NULL COMMENT '核价人员',
|
||
reviewed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '核对时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_estimate_review(label_set_id,fee_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='面单清单与预估冻结的显式核对关联';
|
||
|
||
CREATE TABLE ll_procurement_repack (
|
||
id BIGINT NOT NULL COMMENT '换包装版本ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', carton_id BIGINT NOT NULL COMMENT '原国际交寄箱ID，保持一对一来源',
|
||
length_cm DECIMAL(12,2) NOT NULL COMMENT '新外箱长厘米', width_cm DECIMAL(12,2) NOT NULL COMMENT '新外箱宽厘米',
|
||
height_cm DECIMAL(12,2) NOT NULL COMMENT '新外箱高厘米', gross_weight_kg DECIMAL(12,3) NOT NULL COMMENT '新封箱毛重千克',
|
||
voucher_file_id BIGINT NOT NULL COMMENT '本单换包装证据', reason VARCHAR(1000) NOT NULL COMMENT '换包装依据',
|
||
created_by VARCHAR(128) NOT NULL COMMENT '国内作业人员', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '登记时间',
|
||
PRIMARY KEY(id), KEY idx_procurement_repack_carton(order_id,carton_id,id),
|
||
CONSTRAINT chk_procurement_repack_measurement CHECK(length_cm>0 AND width_cm>0 AND height_cm>0 AND gross_weight_kg>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购国内原箱一对一换包装不可变历史';
|
||
|
||
CREATE TABLE ll_procurement_fee_correction (
|
||
id BIGINT NOT NULL COMMENT '费用链更正ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
correction_no VARCHAR(64) NOT NULL COMMENT '财务更正业务号', root_fee_id BIGINT NOT NULL COMMENT '原实际费用链根',
|
||
idempotency_key VARCHAR(128) NOT NULL COMMENT '本单更正请求幂等键', request_hash CHAR(64) NOT NULL COMMENT '更正目标及凭证请求摘要',
|
||
target_original_amount DECIMAL(20,2) NOT NULL COMMENT '本次正确原币总额', original_currency CHAR(3) NOT NULL COMMENT '沿用原费用币种',
|
||
exchange_rate DECIMAL(20,6) NOT NULL COMMENT '沿用原费用汇率快照', exchange_rate_month CHAR(7) NOT NULL COMMENT '原汇率月份，不取本次操作月份',
|
||
target_amount DECIMAL(20,2) NOT NULL COMMENT '正确总额折算CNY', previous_net_amount DECIMAL(20,2) NOT NULL COMMENT '确认前整链净实扣CNY',
|
||
difference_amount DECIMAL(20,2) NOT NULL COMMENT 'CNY正差额追加、负差额退款、零差额仅留核对记录',
|
||
voucher_file_id BIGINT NOT NULL COMMENT '本订单财务更正凭证', reason VARCHAR(500) NOT NULL COMMENT '更正依据',
|
||
created_by VARCHAR(128) NOT NULL COMMENT '授权财务操作人', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '确认时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_correction_no(correction_no), UNIQUE KEY uk_procurement_correction_request(order_id,idempotency_key),
|
||
KEY idx_procurement_correction_root(order_id,root_fee_id,id),
|
||
CONSTRAINT chk_procurement_correction_amount CHECK(target_original_amount>=0 AND target_amount>=0 AND previous_net_amount>=0 AND exchange_rate>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购费用链更正目标与原汇率快照';
|
||
|
||
CREATE TABLE ll_procurement_fee_correction_item (
|
||
correction_id BIGINT NOT NULL COMMENT '本次费用链更正ID', fee_id BIGINT NOT NULL COMMENT '本次产生的独立正差额或退款费用ID',
|
||
PRIMARY KEY(correction_id,fee_id), UNIQUE KEY uk_procurement_correction_fee(fee_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购费用目标更正与逐笔资金事实关联';
|
||
|
||
CREATE TABLE ll_procurement_control_note (
|
||
id BIGINT NOT NULL COMMENT '采购更正说明ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
note_no VARCHAR(64) NOT NULL COMMENT '更正说明业务号', target_type VARCHAR(24) NOT NULL COMMENT '购买、仓库、实收或交寄事实类型',
|
||
target_id BIGINT NULL COMMENT '本单事实对象引用，购买为空', blocking TINYINT(1) NOT NULL COMMENT '是否阻断后续计划履约',
|
||
voucher_file_id BIGINT NOT NULL COMMENT '本单内部事实更正凭证', reason VARCHAR(1000) NOT NULL COMMENT '内部更正说明',
|
||
client_summary VARCHAR(500) NULL COMMENT '人工确认的对客摘要，不复制内部原始内容',
|
||
original_value_json JSON NULL COMMENT '服务端记录的原事实快照', corrected_value_json JSON NULL COMMENT '服务端记录的更正事实快照',
|
||
created_by VARCHAR(128) NOT NULL COMMENT '采购管理操作人', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '登记时间',
|
||
resolved_by VARCHAR(128) NULL COMMENT '处理确认人', resolved_at DATETIME(3) NULL COMMENT '阻断解除时间',
|
||
resolution_reason VARCHAR(1000) NULL COMMENT '内部处理结果', resolution_voucher_id BIGINT NULL COMMENT '本单处理凭证',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_note_no(note_no), KEY idx_procurement_note_block(order_id,blocking,resolved_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购不可覆盖事实的更正说明及阻断异常';
|
||
|
||
CREATE TABLE ll_procurement_closure (
|
||
id BIGINT NOT NULL COMMENT '采购异常关闭ID', order_id BIGINT NOT NULL COMMENT '采购订单ID', customer_id VARCHAR(128) NOT NULL COMMENT '客户隔离键',
|
||
closure_no VARCHAR(64) NOT NULL COMMENT '异常关闭业务号', customer_voucher_file_id BIGINT NOT NULL COMMENT '客户同意停止履约凭证',
|
||
financial_conclusion VARCHAR(1000) NOT NULL COMMENT '已知费用及后续无需收费的处理结论，不改变资金事实',
|
||
reason VARCHAR(1000) NOT NULL COMMENT '内部关闭原因', client_summary VARCHAR(500) NOT NULL COMMENT '人工确认的对客关闭摘要',
|
||
created_by VARCHAR(128) NOT NULL COMMENT '采购管理确认人', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '关闭确认时间',
|
||
PRIMARY KEY(id), UNIQUE KEY uk_procurement_closure_order(order_id), UNIQUE KEY uk_procurement_closure_no(closure_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='采购实物资金收口后的异常关闭事实';
|
||
|
||
CREATE TABLE ll_procurement_closure_item (
|
||
id BIGINT NOT NULL COMMENT '关闭货物处置ID', closure_id BIGINT NOT NULL COMMENT '采购关闭ID',
|
||
source_kind VARCHAR(32) NOT NULL COMMENT '购买未达、在途未达、合格、破损或多货来源', source_id BIGINT NULL COMMENT '原实收或国际交寄箱，购买未达为空',
|
||
quantity BIGINT NOT NULL COMMENT '本来源最终明确去向件数', decision VARCHAR(40) NOT NULL COMMENT '供应商保管、退还、报失、客户授权保管或处置',
|
||
destination VARCHAR(500) NOT NULL COMMENT '实际去向及接收方说明', voucher_file_id BIGINT NOT NULL COMMENT '本单实物处置证明',
|
||
PRIMARY KEY(id), KEY idx_procurement_closure_source(closure_id,source_kind,source_id),
|
||
CONSTRAINT chk_procurement_closure_quantity CHECK(quantity>0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='异常关闭逐来源剩余货物最终去向，不产生新发运';
|
||
|
||
CREATE TABLE ll_selfop_outbound_discovery_scope (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
catalog_from_utc DATETIME(3) NULL COMMENT '账号完整目录连续覆盖UTC下界，不等同销量覆盖',
|
||
catalog_until_utc DATETIME(3) NULL COMMENT '账号完整目录连续覆盖UTC上界，不等同销量覆盖',
|
||
next_sequence BIGINT NOT NULL DEFAULT 1 COMMENT '新发现任务的单调调度序号；重试沿用，只安排任务，不判断对象内容的新旧',
|
||
next_observation_sequence BIGINT NOT NULL DEFAULT 1 COMMENT '每次实际出库详情请求前分配的单调采集代次；与 task 调度序号独立，重试请求也分配新代次',
|
||
active_task_id BIGINT NULL COMMENT '当前共享发现／详情补齐任务',
|
||
lease_owner VARCHAR(128) NULL COMMENT '账号发现执行者',
|
||
lease_until_utc DATETIME(3) NULL COMMENT '账号发现租约到期时刻',
|
||
fence_token BIGINT NOT NULL DEFAULT 0 COMMENT '防止过期发现进程继续写共享暂存或派发任务',
|
||
request_budget INT NOT NULL COMMENT '一次调度允许的目录、详情请求总预算，必须为正',
|
||
time_budget_seconds INT NOT NULL COMMENT '一次调度允许占用的执行时长，必须为正',
|
||
min_request_interval_ms INT NOT NULL COMMENT '同账号请求最小间隔，按已核准配额配置',
|
||
api_budget_json JSON NULL COMMENT '账号所有采集族共用的预算、统计日、保守预留及确认消耗、尝试许可和独占交接依据',
|
||
api_cooldown_until_utc DATETIME(3) NULL COMMENT '账号全部真实请求共用冷却UTC截止，重启与跨日不清除',
|
||
api_pause_reason VARCHAR(64) NULL COMMENT '账号停止或等待的固定原因码，不保存供应商原始错误或凭据',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_discovery_account (source_account),
|
||
CONSTRAINT chk_selfop_outbound_discovery_scope CHECK (request_budget > 0 AND time_budget_seconds > 0 AND min_request_interval_ms > 0 AND fence_token >= 0 AND next_sequence > 0 AND next_observation_sequence > 0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='账号共享出库目录与详情发现协调作用域';
|
||
|
||
CREATE TABLE ll_selfop_sync_scope (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
data_family VARCHAR(16) NOT NULL COMMENT 'OUTBOUND 或 INVENTORY；按账号＋仓库＋任务族互斥',
|
||
scope_json JSON NOT NULL COMMENT '本仓范围规则，customerMode=ALL_AUTHORIZED，库存为全部品质；当前账号全部授权货主均自营，不将当天出现的货主列表当白名单',
|
||
scope_hash CHAR(64) NOT NULL COMMENT '仓库、货主范围规则、品质、时区及单位口径的规范化摘要；不包含当天观测到的客户／SKU 集合',
|
||
business_timezone VARCHAR(64) NOT NULL COMMENT '本仓报表及调度时区，初始建议值为 Asia/Shanghai，各仓独立核准',
|
||
source_timezone VARCHAR(64) NULL COMMENT '本仓已核准的出库源时间时区',
|
||
schedule_cron VARCHAR(128) NOT NULL COMMENT '本仓、本任务族的调度表达式，按 business_timezone 执行',
|
||
stale_after_seconds INT NOT NULL COMMENT '本仓、本任务族数据过期阈值，必须为正；库存初始可采用 26 小时，后续按仓调整',
|
||
warehouse_policy_ref VARCHAR(128) NULL COMMENT '未来按仓告警／补货策略的稳定引用；一期为空，不建告警规则表',
|
||
initial_from_utc DATETIME(3) NULL COMMENT '首采固定UTC下界，按本仓业务时区减一个月',
|
||
initial_until_utc DATETIME(3) NULL COMMENT '首采固定UTC截止，重试不重新取当前时间',
|
||
discovery_from_utc DATETIME(3) NULL COMMENT '本仓已发布的连续销量覆盖UTC下界',
|
||
discovery_until_utc DATETIME(3) NULL COMMENT '本仓已发布的连续销量覆盖UTC上界，不含端点',
|
||
fact_revision BIGINT NOT NULL DEFAULT 0 COMMENT '出库事实归并修订号；内容实际变化才增加',
|
||
published_fact_revision BIGINT NOT NULL DEFAULT 0 COMMENT '本仓最后已发布销量版所使用的事实修订号',
|
||
rebuild_required TINYINT NOT NULL DEFAULT 0 COMMENT '事实变化待重建标记；跨仓修订同时置旧、新仓为 1',
|
||
invalidated_at_utc DATETIME(3) NULL COMMENT '最近一次因事实修订使当前版待重建的时刻',
|
||
published_run_id BIGINT NULL COMMENT '唯一对外可见版本，指向本任务族已发布执行批次',
|
||
lease_owner VARCHAR(128) NULL COMMENT '当前执行者令牌，不使用仅进程内锁',
|
||
lease_until_utc DATETIME(3) NULL COMMENT '租约到期时刻',
|
||
fence_token BIGINT NOT NULL DEFAULT 0 COMMENT '每次取得租约递增，事实提交和发布均校验',
|
||
known_order_cursor_id BIGINT NULL COMMENT '本仓已可靠处理的稳定订单内部标识游标',
|
||
known_order_round_max_id BIGINT NULL COMMENT '本轮已知单复查固定最大内部标识',
|
||
known_order_round_started_at_utc DATETIME(3) NULL COMMENT '本仓已知单复查轮次开始UTC时间',
|
||
known_order_round_finished_at_utc DATETIME(3) NULL COMMENT '本仓已知单完整轮转完成UTC时间',
|
||
history_round_from_utc DATETIME(3) NULL COMMENT '本仓历史核对固定UTC下界',
|
||
history_round_until_utc DATETIME(3) NULL COMMENT '本仓历史核对固定UTC上界',
|
||
history_slice_cursor_utc DATETIME(3) NULL COMMENT '本仓下一待核对历史切片UTC位置',
|
||
history_round_finished_at_utc DATETIME(3) NULL COMMENT '上一轮历史时间片全部完成时刻',
|
||
review_request_budget INT NOT NULL COMMENT '本仓单次复查最大请求数，必须为正',
|
||
review_time_budget_seconds INT NOT NULL COMMENT '本仓单次复查最大执行秒数，必须为正',
|
||
review_target_days INT NOT NULL DEFAULT 7 COMMENT '建议每 7 天轮转一遍，属于待配额验证的目标，不保证首次大量详情在单次调度内完成',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_scope (source_account,warehouse_code,data_family),
|
||
KEY idx_selfop_scope_lease (lease_until_utc),
|
||
CONSTRAINT chk_selfop_sync_scope CHECK (data_family IN ('OUTBOUND','INVENTORY') AND stale_after_seconds > 0 AND review_request_budget > 0 AND review_time_budget_seconds > 0 AND review_target_days > 0 AND fact_revision >= 0 AND published_fact_revision >= 0 AND fence_token >= 0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='分仓任务族租约及发布指针';
|
||
|
||
CREATE TABLE ll_selfop_sync_task (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
task_no VARCHAR(64) NOT NULL COMMENT '稳定采集业务任务号，重试沿用，不展示内部数据库标识',
|
||
execution_purpose VARCHAR(16) NOT NULL COMMENT 'BUSINESS或VALIDATION；由服务端冻结用途，子任务继承',
|
||
validation_root_task_id BIGINT NULL COMMENT '验证会话唯一预算根任务，根引用自身，正式任务为空',
|
||
validation_budget_json JSON NULL COMMENT '验证根冻结硬预算、累计消耗、库存分仓页数、截止时刻及停止原因',
|
||
business_root_task_id BIGINT NULL COMMENT '测试人工正式采集共享预算根任务；生产任务为空',
|
||
business_budget_json JSON NULL COMMENT '仅测试正式采集根保存冻结请求上限、截止时间、消耗及停止原因',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
task_role VARCHAR(24) NOT NULL COMMENT 'ACCOUNT_DISCOVERY 或 WAREHOUSE_SYNC',
|
||
scope_id BIGINT NULL COMMENT '分仓任务必须引用分仓作用域；仅账号共享发现为空',
|
||
warehouse_code VARCHAR(64) NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
discovery_scope_id BIGINT NULL COMMENT '共享发现必填，引用账号发现协调器',
|
||
upstream_task_id BIGINT NULL COMMENT '出库分仓任务引用已持久化目录的共享发现切片；库存无此上游',
|
||
discovery_sequence BIGINT NULL COMMENT '共享任务及分仓子任务继承的调度顺序；重试保持不变，不用于事实覆盖判定',
|
||
observation_baseline BIGINT NULL COMMENT '共享任务建立时已分配的最大详情采集代次，分仓子任务继承；用于识别旧任务恢复期间出现的其他任务新观测',
|
||
request_key VARCHAR(128) NOT NULL COMMENT '共享发现包含切片／计划槽位；分仓任务包含仓库、任务族及切片或计划槽位；人工附请求幂等键',
|
||
request_hash CHAR(64) NOT NULL COMMENT '请求类型、查询范围、范围摘要的指纹；同键不同请求拒绝',
|
||
task_type VARCHAR(24) NOT NULL COMMENT '计划、补采、复核、重建与验证；INVENTORY_FLOW_HISTORY为流水片，INVENTORY_HISTORY_BUILD为本地历史还原',
|
||
planned_at_utc DATETIME(3) NULL COMMENT '计划时点；人工请求可空',
|
||
query_from_utc DATETIME(3) NULL COMMENT '出库查询区间UTC下界，库存不填',
|
||
query_until_utc DATETIME(3) NULL COMMENT '出库查询区间UTC上界，不含端点，库存不填',
|
||
query_scope_json JSON NOT NULL COMMENT '不变的账号、货主、仓库、品质和查询条件快照；按单补查在此记录单号',
|
||
routing_manifest_json JSON NULL COMMENT '共享切片目录完整标记、每仓对象键集合／数量和详情就绪状态；各对象记录 effectiveStageId，需新代次复查时记录 refreshTaskId，完成后指向新有效暂存；不改写旧载荷',
|
||
scope_hash CHAR(64) NOT NULL COMMENT '建任务时的业务范围摘要',
|
||
status VARCHAR(20) NOT NULL COMMENT 'WAITING_SOURCE、READY、RUNNING、RETRY_WAIT、SUCCEEDED、DEAD、SAMPLE_DONE；样本结束不等于正式成功',
|
||
apply_state VARCHAR(20) NOT NULL COMMENT '正式出库归并NOT_STARTED、APPLYING、BLOCKED、APPLIED；共享发现、库存、重建及验证为NOT_APPLICABLE',
|
||
affected_warehouse_codes_json JSON NULL COMMENT '本次归并可能改变的仓集合，包含原仓与新仓；首段事实写入前登记，扩大集合须先取得新增仓租约并锁 scope，不能写完再补',
|
||
apply_started_at_utc DATETIME(3) NULL COMMENT '首段事实提交UTC时间',
|
||
apply_completed_at_utc DATETIME(3) NULL COMMENT '全部必需事实归并完成UTC时间，失败不填',
|
||
attempt_count INT NOT NULL DEFAULT 0 COMMENT '已创建的执行尝试数',
|
||
next_retry_at_utc DATETIME(3) NULL COMMENT '有限退避后下一次可执行时刻',
|
||
requested_by VARCHAR(128) NULL COMMENT '人工请求的平台操作人，仅内部审计',
|
||
result_published_run_id BIGINT NULL COMMENT '正式成功任务实际发布或复用的版本；验证和共享发现为空',
|
||
progress_json JSON NULL COMMENT '与可靠检查点一起提交的有界阶段、单位、尝试和质量进度，不保存单据集合',
|
||
progress_updated_at_utc DATETIME(3) NULL COMMENT '最后可靠业务检查点UTC时间，与执行心跳分开',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_task_no (task_no),
|
||
UNIQUE KEY uk_selfop_task_request (source_account,request_key),
|
||
KEY idx_selfop_task_poll (status,next_retry_at_utc,id),
|
||
KEY idx_selfop_task_upstream (upstream_task_id,warehouse_code),
|
||
KEY idx_selfop_task_apply (apply_state,id),
|
||
KEY idx_selfop_task_scope_list (scope_id,created_at,id),
|
||
KEY idx_selfop_task_account_list (source_account,task_role,created_at,id),
|
||
KEY idx_selfop_task_business_root (business_root_task_id,status,id),
|
||
CONSTRAINT chk_selfop_business_budget CHECK ((business_root_task_id IS NULL AND business_budget_json IS NULL) OR (execution_purpose='BUSINESS' AND business_root_task_id IS NOT NULL AND ((business_root_task_id=id AND business_budget_json IS NOT NULL) OR (business_root_task_id<>id AND business_budget_json IS NULL)))),
|
||
CONSTRAINT chk_selfop_sync_task CHECK (attempt_count >= 0 AND execution_purpose IN ('BUSINESS','VALIDATION') AND ((execution_purpose = 'BUSINESS' AND validation_root_task_id IS NULL AND validation_budget_json IS NULL) OR (execution_purpose = 'VALIDATION' AND validation_root_task_id IS NOT NULL AND apply_state = 'NOT_APPLICABLE' AND result_published_run_id IS NULL AND status <> 'SUCCEEDED')) AND ((task_role = 'ACCOUNT_DISCOVERY' AND scope_id IS NULL AND warehouse_code IS NULL AND discovery_scope_id IS NOT NULL) OR (task_role = 'WAREHOUSE_SYNC' AND scope_id IS NOT NULL AND warehouse_code IS NOT NULL)))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='一次计划或人工请求';
|
||
|
||
CREATE TABLE ll_selfop_sync_run (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
task_id BIGINT NOT NULL COMMENT '本次执行请求',
|
||
execution_purpose VARCHAR(16) NOT NULL COMMENT '从逻辑任务冻结继承，验证尝试不能作为正式统计版本',
|
||
scope_id BIGINT NULL COMMENT '仅账号共享发现为空；分仓执行必须有作用域',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
warehouse_code VARCHAR(64) NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
run_role VARCHAR(24) NOT NULL COMMENT 'ACCOUNT_DISCOVERY 或 WAREHOUSE_SYNC',
|
||
upstream_task_id BIGINT NULL COMMENT '分仓出库计算引用的共享切片及其稳定暂存集合',
|
||
attempt_no INT NOT NULL COMMENT '同一请求从 1 起递增',
|
||
scope_hash CHAR(64) NOT NULL COMMENT '本版本的业务范围摘要',
|
||
scope_snapshot_json JSON NOT NULL COMMENT '分仓版只含本仓货主／品质、固定首采 [B,E0)、时区、调度与过期阈值；账号发现保存待路由仓集合，不冒充业务版',
|
||
observed_dimensions_json JSON NULL COMMENT '本次实际出现的仓库／货主集合及数量摘要，仅作采集证据；例如已观测 70 货主不等于固定授权范围',
|
||
fence_token BIGINT NOT NULL COMMENT '本尝试持有的栅栏版本',
|
||
base_published_run_id BIGINT NULL COMMENT '执行开始时的上一可见版本',
|
||
reference_sales_run_id BIGINT NULL COMMENT '库存构建开始时固定的本仓已发布销量版，用其不可变 SKU 维度生成出库已知商品占位；没有已发布销量版时为空，不读取半归并订单事实',
|
||
inventory_daily_sample_date DATE NULL COMMENT '北京时间当日首个合格计划库存发布代表日期，非代表为空',
|
||
inventory_sample_rule_version VARCHAR(32) NULL COMMENT '日库存代表选择规则 DAILY_SCHEDULED_FIRST_V1，与日标记原子保存',
|
||
status VARCHAR(24) NOT NULL COMMENT 'FETCHING、VALIDATED、ROUTED、FACTS_APPLIED、BUILDING、READY、PUBLISHED、REUSED、YIELDED、FAILED、ABORTED、SAMPLE_DONE；复用和验证不是版本头',
|
||
last_phase VARCHAR(24) NOT NULL COMMENT '失败／中止前所在阶段，支持判断是否已经归并事实',
|
||
fetch_started_at_utc DATETIME(3) NULL COMMENT '实际采集开始UTC时间',
|
||
fetch_finished_at_utc DATETIME(3) NULL COMMENT '实际采集完成UTC时间，未完成为空',
|
||
build_started_at_utc DATETIME(3) NULL COMMENT '实际开始有界候选构建UTC时间，用于构建耗时及首采合并间隔',
|
||
warehouse_daily_ready TINYINT NOT NULL DEFAULT 0 COMMENT '仓库日汇总已完整构建并校验，含完整空版本',
|
||
finished_at_utc DATETIME(3) NULL COMMENT '本次尝试终止UTC时间',
|
||
published_at_utc DATETIME(3) NULL COMMENT '实际原子切换发布指针UTC时间',
|
||
superseded_at_utc DATETIME(3) NULL COMMENT '被新发布版替代的首次UTC时间，同事务填写且重试不重置',
|
||
page_count INT NOT NULL DEFAULT 0 COMMENT '已完整获取页数',
|
||
source_total_count BIGINT NULL COMMENT '上游可靠返回的总数；未返回则为空',
|
||
unique_object_count BIGINT NOT NULL DEFAULT 0 COMMENT '规范化去重后对象数',
|
||
progress_snapshot_json JSON NULL COMMENT '尝试终止时冻结的有界可靠进度摘要，历史尝试不随逻辑任务改写',
|
||
heartbeat_at_utc DATETIME(3) NULL COMMENT '有效执行者最新UTC心跳，不代表业务检查点推进',
|
||
coverage_status VARCHAR(16) NOT NULL COMMENT 'UNKNOWN、PARTIAL、COMPLETE；分页完整与数值有效性分别判断',
|
||
covered_from_utc DATETIME(3) NULL COMMENT '本仓销量版本实际连续覆盖UTC下界',
|
||
covered_until_utc DATETIME(3) NULL COMMENT '本仓销量版本实际连续覆盖UTC上界，不含端点',
|
||
complete_day_from DATE NULL COMMENT '本仓完整自然日区间首日，包含',
|
||
complete_day_until DATE NULL COMMENT '本仓完整自然日区间末日，不含',
|
||
applied_fact_revision BIGINT NULL COMMENT '本次尝试归并后的事实修订号，库存为空',
|
||
calculated_fact_revision BIGINT NULL COMMENT '候选销量版本计算所依据的固定事实修订号',
|
||
error_code VARCHAR(64) NULL COMMENT '内部可诊断错误分类，不保存认证信息',
|
||
error_message VARCHAR(500) NULL COMMENT '脱敏后的固定错误摘要',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_run_attempt (task_id,attempt_no),
|
||
UNIQUE KEY uk_selfop_inventory_daily (source_account,warehouse_code,inventory_daily_sample_date),
|
||
KEY idx_selfop_run_scope (scope_id,id),
|
||
KEY idx_selfop_run_status (status,updated_at),
|
||
CONSTRAINT chk_selfop_inventory_daily CHECK ((inventory_daily_sample_date IS NULL AND inventory_sample_rule_version IS NULL) OR (inventory_daily_sample_date IS NOT NULL AND inventory_sample_rule_version IS NOT NULL AND inventory_sample_rule_version = 'DAILY_SCHEDULED_FIRST_V1' AND execution_purpose = 'BUSINESS' AND run_role = 'WAREHOUSE_SYNC' AND status = 'PUBLISHED' AND coverage_status = 'COMPLETE')),
|
||
CONSTRAINT chk_selfop_sync_run CHECK (attempt_no > 0 AND page_count >= 0 AND unique_object_count >= 0 AND execution_purpose IN ('BUSINESS','VALIDATION') AND (execution_purpose <> 'VALIDATION' OR status NOT IN ('PUBLISHED','REUSED')) AND (source_total_count IS NULL OR source_total_count >= 0) AND ((run_role = 'ACCOUNT_DISCOVERY' AND scope_id IS NULL AND warehouse_code IS NULL) OR (run_role = 'WAREHOUSE_SYNC' AND scope_id IS NOT NULL AND warehouse_code IS NOT NULL)))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='执行尝试、覆盖证明和版本头';
|
||
|
||
CREATE TABLE ll_selfop_sync_stage (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
run_id BIGINT NOT NULL COMMENT '首次写入该暂存对象的执行尝试',
|
||
stage_batch_id BIGINT NOT NULL COMMENT '出库取共享发现 task.id，便于断点续取；库存取本次 run.id，严禁跨尝试混页',
|
||
last_fetch_run_id BIGINT NOT NULL COMMENT '最后一次补齐该对象的执行尝试，保留重试来源',
|
||
observation_sequence BIGINT NULL COMMENT '该完整出库详情实际请求的采集代次；有效出库对象必填，目录待补对象和库存为空；不能填 task 调度序号',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
previous_warehouse_code VARCHAR(64) NULL COMMENT '路由时从已有单据识别的原仓库；归并前再校验，跨仓时通知两仓',
|
||
object_type VARCHAR(16) NOT NULL COMMENT 'OUTBOUND_ORDER 或 PRODUCT_STOCK',
|
||
object_key_hash CHAR(64) NOT NULL COMMENT '规范化业务键摘要；原业务键保留在载荷中并核对',
|
||
first_page_no INT NOT NULL COMMENT '首次发现页码',
|
||
observed_at_utc DATETIME(3) NOT NULL COMMENT '实际接收该对象的时刻',
|
||
content_hash CHAR(64) NOT NULL COMMENT '完整规范化对象摘要',
|
||
payload_json JSON NOT NULL COMMENT '目录阶段可先存订单头，详情齐后补完整 SKU；VALID 后不再修改；库存保存产品库存字段',
|
||
before_payload_json JSON NULL COMMENT '订单归并事务内记录归并前的完整规范化事实；新增单为空',
|
||
validation_status VARCHAR(16) NOT NULL COMMENT '暂存校验状态：PENDING待核验、VALID有效、INVALID异常',
|
||
validation_code VARCHAR(64) NULL COMMENT '字段缺失、冲突、未知时区等原因',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_stage_object (stage_batch_id,object_type,object_key_hash),
|
||
KEY idx_selfop_stage_history (object_type,object_key_hash,stage_batch_id),
|
||
KEY idx_selfop_stage_observation (object_type,object_key_hash,validation_status,observation_sequence),
|
||
KEY idx_selfop_stage_fact_observation (observation_sequence,warehouse_code,object_type,validation_status,run_id,observed_at_utc),
|
||
KEY idx_selfop_stage_route (stage_batch_id,warehouse_code,validation_status),
|
||
CONSTRAINT chk_selfop_sync_stage CHECK (first_page_no > 0 AND (object_type <> 'OUTBOUND_ORDER' OR validation_status <> 'VALID' OR observation_sequence IS NOT NULL))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='待验证事实与修订证据';
|
||
|
||
CREATE TABLE ll_selfop_outbound_order (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
customer_code VARCHAR(64) NOT NULL COMMENT '领星货主原始编码，不等同平台客户标识',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
delivery_no VARCHAR(128) NOT NULL COMMENT 'OMP 发货单号，采用列表及详情同一身份',
|
||
source_no VARCHAR(128) NULL COMMENT 'WMS 来源出库单号，缺失不以内部 ID 回退展示',
|
||
source_status INT NOT NULL COMMENT '原始状态，100 已出库、99 已取消；不改造成内部状态码',
|
||
source_created_time VARCHAR(64) NULL COMMENT '来源创建时间原始字符串，不使用采集时间替代',
|
||
source_outbound_time VARCHAR(64) NULL COMMENT '来源实际出库时间原始字符串，不使用采集时间替代',
|
||
source_timezone VARCHAR(64) NULL COMMENT '解析采用的源时区',
|
||
outbound_at_utc DATETIME(3) NULL COMMENT '已核准的实际出库时间点；非出库状态可空',
|
||
business_date DATE NULL COMMENT '已核准业务时区下的出库日',
|
||
business_timezone VARCHAR(64) NOT NULL COMMENT '该事实当前归属仓所用业务时区，迁仓后重新确定业务日',
|
||
sales_eligible TINYINT NOT NULL DEFAULT 0 COMMENT '仅已出库、时间与全部数量有效时为 1',
|
||
revision_no BIGINT NOT NULL DEFAULT 1 COMMENT '该单内容变更次数，不等于拉取次数',
|
||
content_hash CHAR(64) NOT NULL COMMENT '订单头及完整 SKU 明细规范化摘要',
|
||
last_run_id BIGINT NOT NULL COMMENT '最后一次确认或修订的执行尝试',
|
||
last_observation_sequence BIGINT NOT NULL COMMENT '最后接受的完整详情采集代次；仅防旧观测重放，不按 task 调度序号拒绝较晚实际读取的事实，不冒充领星源版本号',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_order (source_account,customer_code,delivery_no),
|
||
KEY idx_selfop_order_daily (source_account,warehouse_code,sales_eligible,business_date,id),
|
||
KEY idx_selfop_order_run (last_run_id),
|
||
CONSTRAINT chk_selfop_outbound_order CHECK (revision_no > 0 AND last_observation_sequence > 0 AND sales_eligible IN (0,1) AND (sales_eligible = 0 OR (source_status = 100 AND source_timezone IS NOT NULL AND outbound_at_utc IS NOT NULL AND business_date IS NOT NULL)))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='最后确认的单据事实';
|
||
|
||
CREATE TABLE ll_selfop_outbound_item (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
order_id BIGINT NOT NULL COMMENT '对应外部出库事实',
|
||
sku_code VARCHAR(128) NOT NULL COMMENT '原始 productSku',
|
||
product_name VARCHAR(255) NULL COMMENT '来源商品名称，仅作展示',
|
||
actual_quantity DECIMAL(20,6) NOT NULL COMMENT '详情 actualDeliveryQty，必须为非负有效数值',
|
||
quantity_unit VARCHAR(32) NOT NULL COMMENT '已核准业务单位；未确认单位不发布正式数量统计',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_order_sku (order_id,sku_code),
|
||
KEY idx_selfop_item_sku (sku_code,order_id),
|
||
CONSTRAINT chk_selfop_outbound_item CHECK (actual_quantity >= 0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='整单替换的实际出库明细';
|
||
|
||
CREATE TABLE ll_selfop_inventory_snapshot (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
snapshot_run_id BIGINT NOT NULL COMMENT 'INVENTORY 执行尝试；发布后该批明细不可修改',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
customer_code VARCHAR(64) NOT NULL COMMENT '领星货主原始编码，不等同平台客户标识',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
sku_code VARCHAR(128) NOT NULL COMMENT '原始产品 SKU',
|
||
product_quality SMALLINT NOT NULL COMMENT '来源品质，当前 0 正品、1 次品；全量均保存，不套用整箱库存',
|
||
product_name VARCHAR(255) NULL COMMENT '来源名称',
|
||
physical_quantity DECIMAL(20,6) NULL COMMENT '来源stkQty实物库存，未知为空',
|
||
available_quantity DECIMAL(20,6) NULL COMMENT '来源useStkQty可用库存，未知为空，不再次扣冻结',
|
||
frozen_quantity DECIMAL(20,6) NULL COMMENT '来源freezeQty冻结库存，未知为空',
|
||
transit_quantity DECIMAL(20,6) NULL COMMENT '来源transitQty在途数量，未知为空，不计可售库存',
|
||
source_unique_key VARCHAR(512) NULL COMMENT '原 uniqueKey4tenant，只作来源证据',
|
||
observed_at_utc DATETIME(3) NOT NULL COMMENT '所在页实际采集时间；缺行占位取本批采集完成时间',
|
||
quality_status VARCHAR(16) NOT NULL COMMENT 'VALID、UNKNOWN、INVALID；STALE 按批次年龄实时派生',
|
||
quality_reason VARCHAR(64) NULL COMMENT 'SOURCE_ROW_MISSING、FIELD_MISSING、NEGATIVE_QUANTITY 等',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_stock_snapshot (snapshot_run_id,source_account,customer_code,warehouse_code,sku_code,product_quality),
|
||
KEY idx_selfop_stock_lookup (source_account,customer_code,warehouse_code,sku_code,product_quality,snapshot_run_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='产品库存全量批次明细';
|
||
|
||
CREATE TABLE ll_selfop_sku_daily_sales (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
sales_run_id BIGINT NOT NULL COMMENT 'OUTBOUND 执行尝试，同时是计算版本',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
customer_code VARCHAR(64) NOT NULL COMMENT '领星货主原始编码，不等同平台客户标识',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
sku_code VARCHAR(128) NOT NULL COMMENT '原始产品 SKU',
|
||
business_date DATE NOT NULL COMMENT '已核准业务日',
|
||
outbound_quantity DECIMAL(20,6) NOT NULL COMMENT '有效出库实际件数之和，非负',
|
||
outbound_order_count BIGINT NOT NULL COMMENT '该维度去重发货单数，非负',
|
||
quantity_unit VARCHAR(32) NOT NULL COMMENT '同一统计维度内一致的业务单位',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_daily_version (sales_run_id,source_account,customer_code,warehouse_code,sku_code,business_date),
|
||
KEY idx_selfop_daily_window_cover (sales_run_id,source_account,warehouse_code,business_date,customer_code,sku_code,outbound_quantity,outbound_order_count,quantity_unit),
|
||
KEY idx_selfop_daily_sku_cover (sales_run_id,source_account,warehouse_code,customer_code,sku_code,business_date,outbound_quantity,outbound_order_count,quantity_unit),
|
||
CONSTRAINT chk_selfop_sku_daily_sales CHECK (outbound_quantity >= 0 AND outbound_order_count >= 0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='完整销量版本的稀疏日事实';
|
||
|
||
CREATE TABLE ll_selfop_warehouse_daily_sales (
|
||
sales_run_id BIGINT NOT NULL COMMENT '完整销量计算版本标识',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码',
|
||
business_date DATE NOT NULL COMMENT '已核准业务日',
|
||
observed_quantity DECIMAL(38,6) NULL COMMENT '该日PCS非负有效出库量之和，无有效行时为空',
|
||
invalid_row_count BIGINT NOT NULL COMMENT '该日数量为空或负数或单位非PCS的日事实行数',
|
||
source_row_count BIGINT NOT NULL COMMENT '该日参与仓库汇总的SKU日事实总行数',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
PRIMARY KEY (sales_run_id,source_account,warehouse_code,business_date),
|
||
CONSTRAINT chk_selfop_warehouse_daily_sales CHECK (source_row_count > 0 AND invalid_row_count >= 0 AND invalid_row_count <= source_row_count AND (observed_quantity IS NULL OR observed_quantity >= 0))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='完整销量版本按仓按日汇总，随SKU日事实批次原子构建';
|
||
|
||
CREATE TABLE ll_selfop_sku_profile (
|
||
id BIGINT NOT NULL COMMENT '应用生成的记录标识',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC创建时间',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
profile_run_id BIGINT NOT NULL COMMENT '本仓销量或库存候选版本；与对应 daily／snapshot 使用同一 run，发布后该版本全部维度行不可修改',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号别名，不保存密钥',
|
||
customer_code VARCHAR(64) NOT NULL COMMENT '领星货主原始编码，不等同平台客户标识',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库原始编码，按仓独立统计',
|
||
sku_code VARCHAR(128) NOT NULL COMMENT '原始产品 SKU',
|
||
product_name VARCHAR(255) NULL COMMENT '本版本确认的名称，不实时连接其他版本名称',
|
||
quantity_unit VARCHAR(32) NULL COMMENT '本版本已核准单位；未知或两版本单位冲突时不计算混合指标',
|
||
first_seen_at_utc DATETIME(3) NULL COMMENT '本证据族真实观测到本仓商品的最早接收时刻；缺行占位不能生成观察证据',
|
||
earliest_evidence_at_utc DATETIME(3) NULL COMMENT '库存版取真实库存首次观察时刻；销量版从该版有效事实重新求最早已核准出库时间，可随改期、取消或迁仓撤回／后移',
|
||
evidence_type VARCHAR(24) NOT NULL COMMENT 'INVENTORY_OBSERVATION、VALID_OUTBOUND、NONE；不得将销量证据复制成库存真实观察证据',
|
||
evidence_run_id BIGINT NULL COMMENT '本证据族真实依据的采集批次，无证据占位为空',
|
||
last_seen_run_id BIGINT NULL COMMENT '本证据族最近真实观测批次，无证据占位为空',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_profile_version (profile_run_id,source_account,customer_code,warehouse_code,sku_code),
|
||
KEY idx_selfop_profile_evidence (profile_run_id,earliest_evidence_at_utc,customer_code,sku_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='随发布版本固定的 SKU 维度与观察依据';
|
||
|
||
CREATE TABLE ll_appointment_impact (
|
||
container_booking_id VARCHAR(64) NOT NULL COMMENT '稳定柜次关联键，不作业务编号展示',
|
||
impact_no VARCHAR(40) NOT NULL COMMENT '船期核对事项业务号',
|
||
baseline_json LONGTEXT NOT NULL COMMENT '已核对基准或首次变化前观察，原来源时间口径',
|
||
latest_json LONGTEXT NOT NULL COMMENT '最新已读取观察，未知可为JSON null',
|
||
forecast_token CHAR(64) NOT NULL COMMENT '最新观察摘要，处理时防止旧预测结案',
|
||
status VARCHAR(20) NOT NULL COMMENT '事项状态：MONITORING监测/OPEN待处理/RESOLVED已完成',
|
||
reason VARCHAR(64) NULL COMMENT '预测变化或资料待核实原因',
|
||
owner_id VARCHAR(128) NULL COMMENT '认领平台人员ID',
|
||
owner_name VARCHAR(200) NULL COMMENT '负责人姓名快照',
|
||
version INT NOT NULL COMMENT '观察及人工处理共同版本',
|
||
updated_at DATETIME(3) NOT NULL COMMENT 'UTC更新时间',
|
||
PRIMARY KEY (container_booking_id),
|
||
UNIQUE KEY uk_appointment_impact_no (impact_no),
|
||
KEY idx_appointment_impact_work (status,updated_at),
|
||
CONSTRAINT chk_appointment_impact_status CHECK (status IN ('MONITORING','OPEN','RESOLVED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='一柜一个稳定船期变化核对事项，不改写预约或运输';
|
||
|
||
CREATE TABLE ll_appointment_impact_history (
|
||
id BIGINT NOT NULL COMMENT '历史记录标识',
|
||
container_booking_id VARCHAR(64) NOT NULL COMMENT '关联柜次标识',
|
||
version INT NOT NULL COMMENT '记录版本',
|
||
action VARCHAR(20) NOT NULL COMMENT '操作类型：OBSERVED观察/CLAIM认领/RECORD记录/RESOLVE完成',
|
||
operator_id VARCHAR(128) NOT NULL COMMENT '操作人标识',
|
||
operator_name VARCHAR(200) NULL COMMENT '操作人姓名',
|
||
result VARCHAR(1000) NULL COMMENT '人工处理结果；观察记录为空',
|
||
next_action VARCHAR(500) NULL COMMENT '下一步处理事项',
|
||
followup_date DATE NULL COMMENT '业务跟进日期',
|
||
command_key VARCHAR(64) NULL COMMENT '人工命令幂等键；观察记录为空',
|
||
request_hash CHAR(64) NULL COMMENT '请求内容摘要',
|
||
response_json LONGTEXT NOT NULL COMMENT '该版本完整响应快照，支持精确重放',
|
||
created_at DATETIME(3) NOT NULL COMMENT 'UTC记录时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_appointment_impact_version (container_booking_id,version),
|
||
UNIQUE KEY uk_appointment_impact_command (container_booking_id,operator_id,command_key),
|
||
CONSTRAINT fk_appointment_impact_history FOREIGN KEY (container_booking_id) REFERENCES ll_appointment_impact(container_booking_id),
|
||
CONSTRAINT chk_appointment_impact_action CHECK (action IN ('OBSERVED','CLAIM','RECORD','RESOLVE'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='船期观察及人工处理追加历史';
|
||
|
||
CREATE TABLE ll_selfop_owner (
|
||
id BIGINT NOT NULL COMMENT '主键',
|
||
run_id BIGINT NOT NULL COMMENT '完整资料采集版本',
|
||
source_account VARCHAR(64) COLLATE utf8mb4_bin NOT NULL COMMENT '来源账号',
|
||
customer_code VARCHAR(64) COLLATE utf8mb4_bin NOT NULL COMMENT '领星货主编码',
|
||
customer_name VARCHAR(255) NULL COMMENT '领星货主名称',
|
||
source_status VARCHAR(32) NULL COMMENT '来源启停状态',
|
||
observed_at_utc DATETIME(6) NOT NULL COMMENT '资料观测时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_owner_version (run_id,source_account,customer_code),
|
||
KEY idx_selfop_owner_lookup (source_account,customer_code,run_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自营领星货主资料版本';
|
||
|
||
CREATE TABLE ll_selfop_product (
|
||
id BIGINT NOT NULL COMMENT '主键',
|
||
run_id BIGINT NOT NULL COMMENT '完整资料采集版本',
|
||
source_account VARCHAR(64) COLLATE utf8mb4_bin NOT NULL COMMENT '来源账号',
|
||
customer_code VARCHAR(64) COLLATE utf8mb4_bin NOT NULL COMMENT '领星货主编码',
|
||
sku_code VARCHAR(128) COLLATE utf8mb4_bin NOT NULL COMMENT '货主商品编码',
|
||
product_name VARCHAR(255) NULL COMMENT '商品名称',
|
||
declared_length DECIMAL(24,6) NULL COMMENT '商品资料长度',
|
||
declared_width DECIMAL(24,6) NULL COMMENT '商品资料宽度',
|
||
declared_height DECIMAL(24,6) NULL COMMENT '商品资料高度',
|
||
declared_size_unit VARCHAR(32) NULL COMMENT '商品资料尺寸单位',
|
||
measured_length DECIMAL(24,6) NULL COMMENT '仓库实测长度',
|
||
measured_width DECIMAL(24,6) NULL COMMENT '仓库实测宽度',
|
||
measured_height DECIMAL(24,6) NULL COMMENT '仓库实测高度',
|
||
measured_size_unit VARCHAR(32) NULL COMMENT '仓库实测尺寸单位',
|
||
observed_at_utc DATETIME(6) NOT NULL COMMENT '资料观测时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_product_version (run_id,source_account,customer_code,sku_code),
|
||
KEY idx_selfop_product_lookup (source_account,customer_code,sku_code,run_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自营领星商品资料版本';
|
||
|
||
CREATE TABLE ll_selfop_inventory_flow (
|
||
id BIGINT NOT NULL COMMENT '应用生成的流水记录标识',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库编码',
|
||
customer_code VARCHAR(64) NOT NULL COMMENT '来源货主编码',
|
||
sku_code VARCHAR(128) NOT NULL COMMENT '来源商品编码',
|
||
source_flow_id VARCHAR(64) NOT NULL COMMENT '来源流水字符串标识',
|
||
product_quality INT NOT NULL COMMENT '来源品质，零正品一次品，其他未知',
|
||
cell_no VARCHAR(128) NULL COMMENT '来源库位编码',
|
||
inner_batch_no VARCHAR(128) NULL COMMENT '来源内部批次号',
|
||
bill_no VARCHAR(128) NULL COMMENT '来源业务单号',
|
||
source_no VARCHAR(128) NULL COMMENT '来源关联单号',
|
||
bill_type VARCHAR(32) NULL COMMENT '来源业务类型，未验证类型不得参与正式还原',
|
||
book_qty DECIMAL(20,6) NULL COMMENT '来源记账增减量，未知为空',
|
||
book_change_qty_raw VARCHAR(64) NULL COMMENT '来源带符号变动量原文',
|
||
delta_quantity DECIMAL(20,6) NULL COMMENT '解析后的有符号实物变动量',
|
||
close_quantity DECIMAL(20,6) NULL COMMENT '对应库位批次品质链的结存',
|
||
source_create_time VARCHAR(64) NULL COMMENT '来源北京时间原文',
|
||
event_at_utc DATETIME(3) NULL COMMENT '流水发生UTC时刻，无法解析时为空',
|
||
business_date DATE NULL COMMENT '北京时间业务日期',
|
||
quantity_unit VARCHAR(16) NULL COMMENT '已确认数量单位，未知为空',
|
||
content_hash CHAR(64) NOT NULL COMMENT '当前事实内容摘要',
|
||
revision_no BIGINT NOT NULL COMMENT '同来源流水单调增加的修订号',
|
||
stage_id BIGINT NOT NULL COMMENT '当前事实对应不可变暂存证据',
|
||
first_run_id BIGINT NOT NULL COMMENT '首次观察执行尝试',
|
||
last_run_id BIGINT NOT NULL COMMENT '最近确认执行尝试',
|
||
last_observed_at DATETIME(3) NOT NULL COMMENT '最近观察UTC时刻',
|
||
validation_status VARCHAR(16) NOT NULL COMMENT '字段资格，有效或未知',
|
||
validation_reason VARCHAR(64) NULL COMMENT '数量或身份待核验原因',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_flow_source (source_account,warehouse_code,source_flow_id),
|
||
KEY idx_selfop_flow_sku (source_account,warehouse_code,customer_code,sku_code,event_at_utc),
|
||
KEY idx_selfop_flow_day (source_account,warehouse_code,business_date),
|
||
CONSTRAINT chk_selfop_flow_revision CHECK (revision_no > 0)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='自营历史库存流水最新事实，旧版本保留于暂存证据';
|
||
|
||
CREATE TABLE ll_selfop_inventory_day_version (
|
||
id BIGINT NOT NULL COMMENT '不可变历史日版本标识',
|
||
source_account VARCHAR(64) NOT NULL COMMENT '受控来源账号',
|
||
warehouse_code VARCHAR(64) NOT NULL COMMENT '来源仓库编码',
|
||
business_date DATE NOT NULL COMMENT '北京时间日末所属日期',
|
||
scope_hash CHAR(64) NOT NULL COMMENT '业务范围摘要',
|
||
rule_version VARCHAR(32) NOT NULL COMMENT '日末库存还原规则版本',
|
||
build_run_id BIGINT NOT NULL COMMENT '历史重建执行尝试',
|
||
publication_group_id BIGINT NOT NULL COMMENT '必须一起切换的发布组标识',
|
||
sales_run_id BIGINT NULL COMMENT '构建时固定的销量证据版本',
|
||
anchor_run_id BIGINT NOT NULL COMMENT '固定的独立库存锚点版本',
|
||
input_hash CHAR(64) NOT NULL COMMENT '冻结流水修订与覆盖及锚点输入摘要',
|
||
evidence_json JSON NOT NULL COMMENT '固定输入清单及不可变证据引用',
|
||
status VARCHAR(16) NOT NULL COMMENT '构建中、就绪、已发布、已替代或失败',
|
||
head_slot TINYINT NULL COMMENT '当前日头为一，非当前版本为空',
|
||
valid_sku_count BIGINT NOT NULL DEFAULT 0 COMMENT '合格商品数',
|
||
unknown_sku_count BIGINT NOT NULL DEFAULT 0 COMMENT '待核验商品数',
|
||
created_at DATETIME(3) NOT NULL COMMENT '版本创建UTC时刻',
|
||
published_at DATETIME(3) NULL COMMENT '发布组提交UTC时刻',
|
||
superseded_at DATETIME(3) NULL COMMENT '被新组替代的UTC时刻',
|
||
invalidated_at DATETIME(3) NULL COMMENT '已确认输入失效的UTC时刻',
|
||
invalidation_reason VARCHAR(64) NULL COMMENT '旧版本失效原因',
|
||
rebuild_run_id BIGINT NULL COMMENT '处理失效版本的重建尝试',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_selfop_history_head (source_account,warehouse_code,business_date,head_slot),
|
||
UNIQUE KEY uk_selfop_history_build_day (build_run_id,business_date),
|
||
KEY idx_selfop_history_group (publication_group_id,status),
|
||
CONSTRAINT chk_selfop_history_head CHECK (head_slot IS NULL OR (head_slot=1 AND status='PUBLISHED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='历史日库存不可变版本及原子发布日头';
|
||
|
||
CREATE TABLE ll_selfop_inventory_daily (
|
||
day_version_id BIGINT NOT NULL COMMENT '所属历史日版本',
|
||
customer_code VARCHAR(64) NOT NULL COMMENT '来源货主编码',
|
||
sku_code VARCHAR(128) NOT NULL COMMENT '来源商品编码',
|
||
product_quality INT NOT NULL COMMENT '来源库存品质',
|
||
physical_quantity DECIMAL(20,6) NULL COMMENT '日末实物库存，未知为空，已确认零为零',
|
||
quality_status VARCHAR(16) NOT NULL COMMENT '日末资格，有效或未知',
|
||
quality_reason VARCHAR(64) NULL COMMENT '缺覆盖或锚点等未知原因',
|
||
anchor_reference BIGINT NULL COMMENT '独立库存锚点版本',
|
||
evidence_hash CHAR(64) NOT NULL COMMENT '该日计算证据摘要',
|
||
source VARCHAR(32) NOT NULL COMMENT '库存来源标记，流水还原',
|
||
PRIMARY KEY (day_version_id,customer_code,sku_code,product_quality),
|
||
KEY idx_selfop_daily_identity (customer_code,sku_code,day_version_id),
|
||
CONSTRAINT chk_selfop_daily_quantity CHECK ((quality_status='VALID' AND physical_quantity IS NOT NULL AND physical_quantity>=0) OR (quality_status='UNKNOWN' AND physical_quantity IS NULL))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='历史日末分货主商品品质实物库存';
|