电商系统如何设计快递物流查询功能：轨迹同步、异常状态和 API 接入实践

CREATE TABLE shipments (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  express_company VARCHAR(64) NOT NULL,
  tracking_no VARCHAR(128) NOT NULL,
  status VARCHAR(32) NOT NULL,
  last_track_time DATETIME NULL,
  last_sync_time DATETIME NULL,
  sync_count INT DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_company_tracking (express_company, tracking_no)
);

CREATE TABLE shipment_tracks (
  id BIGINT PRIMARY KEY,
  shipment_id BIGINT NOT NULL,
  track_time DATETIME NOT NULL,
  content VARCHAR(1024) NOT NULL,
  location VARCHAR(128) NULL,
  raw_status VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_shipment_time (shipment_id, track_time)
);

function mapExpressStatus(rawStatus) {
  const statusMap = {
    collected: 'PICKED',
    transit: 'IN_TRANSIT',
    delivering: 'DELIVERING',
    signed: 'SIGNED',
    exception: 'EXCEPTION',
    returning: 'RETURNING'
  };

  return statusMap[rawStatus] || 'IN_TRANSIT';
}

async function syncShipment(shipment) {
  const result = await expressClient.query({
    company: shipment.expressCompany,
    trackingNo: shipment.trackingNo
  });

  const normalizedStatus = mapExpressStatus(result.status);
  await saveTrackNodes(shipment.id, result.traces);

  await updateShipmentStatus(shipment.id, {
    status: normalizedStatus,
    lastTrackTime: result.lastTrackTime,
    lastSyncTime: new Date(),
    syncCount: shipment.syncCount + 1
  });

  if (normalizedStatus === 'EXCEPTION') {
    await createShipmentEvent(shipment.id, {
      type: 'EXPRESS_EXCEPTION',
      reason: result.reason || '物流状态异常',
      rawStatus: result.status
    });
  }
}

function isNoUpdateTooLong(lastTrackTime, status) {
  if (['SIGNED', 'RETURNING'].includes(status)) return false;

  const hours = (Date.now() - new Date(lastTrackTime).getTime()) / 3600000;
  return hours >= 24;
}

if (isNoUpdateTooLong(shipment.lastTrackTime, shipment.status)) {
  await createShipmentEvent(shipment.id, {
    type: 'NO_UPDATE_TOO_LONG',
    level: 'warning',
    message: '物流超过 24 小时未更新'
  });
}

export interface TrackingQuery {
  company?: string;
  trackingNo: string;
  receiverPhoneTail?: string;
}

export interface TrackingNode {
  time: string;
  content: string;
  location?: string;
  rawStatus?: string;
}

export interface TrackingResult {
  trackingNo: string;
  company?: string;
  status: 'PICKED' | 'IN_TRANSIT' | 'DELIVERING' | 'SIGNED' | 'EXCEPTION' | 'RETURNING';
  lastTrackTime?: string;
  traces: TrackingNode[];
  raw?: unknown;
}

export interface ExpressTrackingClient {
  query(input: TrackingQuery): Promise<TrackingResult>;
}

function getNextSyncDelayMinutes(status, syncCount) {
  if (status === 'SIGNED' || status === 'CLOSED') return null;
  if (status === 'EXCEPTION') return 20;
  if (status === 'DELIVERING') return 30;
  if (status === 'PICKED') return 60;
  if (status === 'IN_TRANSIT') return syncCount < 6 ? 60 : 120;
  return 30;
}

function shouldSync(shipment, now = new Date()) {
  if (!shipment.nextSyncAt) return false;
  return new Date(shipment.nextSyncAt).getTime() <= now.getTime();
}

ALTER TABLE shipment_events ADD COLUMN level VARCHAR(16) NOT NULL DEFAULT 'warning';
ALTER TABLE shipment_events ADD COLUMN handler_id BIGINT NULL;
ALTER TABLE shipment_events ADD COLUMN handled_at DATETIME NULL;
ALTER TABLE shipment_events ADD COLUMN handle_note VARCHAR(512) NULL;
ALTER TABLE shipment_events ADD COLUMN suggested_action VARCHAR(128) NULL;

[ ] API Key 不出现在前端代码和客户端包里
[ ] 查询失败不会影响订单详情页主体加载
[ ] 已签收、已关闭、已退款订单停止同步
[ ] 用户手动刷新有冷却时间
[ ] 物流异常会写入事件表并进入处理队列
[ ] 每日调用次数、失败率、缓存命中率可观测

curl -X POST "https://ai.huating-ai.cn/api/999" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
  "number": "SF1234567890",
  "company": "sf"
}'

const API_KEY = process.env.HUATING_API_KEY;
const API_URL = "https://ai.huating-ai.cn/api/999";

if (!API_KEY) {
  throw new Error("请先设置环境变量 HUATING_API_KEY");
}

async function queryExpress(number, company = "") {
  const payload = {
    number,
    company
  };

  const res = await fetch(API_URL, {
    method: "POST",
    headers: {
      "Authorization": `Bearer ${API_KEY}`,
      "Content-Type": "application/json"
    },
    body: JSON.stringify(payload)
  });

  if (!res.ok) {
    throw new Error(`HTTP ${res.status}`);
  }

  const data = await res.json();
  if (data.code && data.code !== 200) {
    throw new Error(data.message || "API 调用失败");
  }

  return data.data || data;
}

queryExpress("SF1234567890", "sf").then(console.log).catch(console.error);