线上商城到店自提、同城配送的订单状态怎么设计？一套双履约状态机实现

-- 订单主表：只表达交易进度
CREATE TABLE orders (
  id            BIGINT PRIMARY KEY,
  order_no      VARCHAR(32) NOT NULL UNIQUE,
  status        VARCHAR(16) NOT NULL,  -- WAIT_PAY/PAID/CANCELED/FINISHED/REFUNDING
  pay_amount    DECIMAL(10,2) NOT NULL,
  version       INT NOT NULL DEFAULT 0,
  updated_at    DATETIME NOT NULL
);

-- 履约表：一次履约一行，履约类型区分自提/配送
CREATE TABLE order_fulfillment (
  id            BIGINT PRIMARY KEY,
  order_id      BIGINT NOT NULL,
  type          VARCHAR(8)  NOT NULL, -- PICKUP/DELIVERY
  status        VARCHAR(16) NOT NULL, -- PREPARING/PENDING_PICKUP/OUT_FOR_DELIVERY/DELIVERED/TIMEOUT
  store_id      BIGINT,               -- 自提门店
  pickup_code   VARCHAR(16),          -- 自提码
  pickup_at     DATETIME,             -- 实际核销时间
  timeout_at    DATETIME,             -- 超时判定时间点
  version       INT NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL
);

// 自提核销：带 version 条件更新，影响行数为 0 说明状态已被别人推进
int rows = jdbc.update(
  "UPDATE order_fulfillment SET status='DELIVERED', pickup_at=NOW(), version=version+1 " +
  "WHERE id=? AND status='PENDING_PICKUP' AND version=?",
  fulfillmentId, currentVersion);
if (rows == 0) {
  throw new IllegalStateException("核销失败：该自提码状态已变更");
}

// 支付成功后，为履约单注册一个超时任务：自提单 24h、配送单 30min
TimeoutTask task = TimeoutTask.builder()
    .fulfillmentId(f.getId())
    .triggerAt(now.plus(f.getType().equals("PICKUP") ? 24 : 1, HOURS))
    .build();
delayQueue.publish(task);

// 超时回调：仍是条件更新，避免超时通知与正常核销竞态
int rows = jdbc.update(
  "UPDATE order_fulfillment SET status='TIMEOUT', version=version+1 " +
  "WHERE id=? AND status IN ('PENDING_PICKUP','OUT_FOR_DELIVERY') AND version=?",
  fId, version);
if (rows > 0) {
  notifyService.timeout(fId); // 给用户发提醒，自提订单可重新预约
}

-- 核销流水表：一次核销一行，order_id + type 唯一，重复核销自然插入失败
CREATE TABLE fulfillment_pickup_log (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  fulfillment_id  BIGINT NOT NULL,
  operator_id     BIGINT NOT NULL,
  created_at      DATETIME NOT NULL,
  UNIQUE KEY uk_fulfillment (fulfillment_id)
);

// 先写流水（唯一键兜底），成功才推进状态；流水失败说明已核销过
try {
  jdbc.update("INSERT INTO fulfillment_pickup_log(fulfillment_id,operator_id) VALUES (?,?)",
      fId, operatorId);
} catch (DuplicateKeyException e) {
  throw new IllegalStateException("该自提码已完成核销，不能重复使用");
}
jdbc.update("UPDATE order_fulfillment SET status='DELIVERED', pickup_at=NOW(), " +
    "version=version+1 WHERE id=? AND status='PENDING_PICKUP' AND version=?",
    fId, version);