AiDOPWarehouse/doc/db/mdp/mdp_outbox_and_stg_external.sql

-- MDP 出站 Outbox + 通用贴源表（双模式对接执行任务书 P-B/P-E）
-- 幂等可重复执行

CREATE TABLE IF NOT EXISTS mdp_outbox (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT NOT NULL,
  target_source_code VARCHAR(100) NOT NULL,
  action_code VARCHAR(100) NOT NULL,
  idem_key VARCHAR(200) NOT NULL,
  payload_json TEXT,
  status TINYINT NOT NULL DEFAULT 0,
  retry_count INT NOT NULL DEFAULT 0,
  response_json TEXT,
  error_msg VARCHAR(1000),
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_idem (tenant_id, target_source_code, action_code, idem_key),
  KEY idx_status (status, tenant_id)
);

CREATE TABLE IF NOT EXISTS mdp_stg_external_row (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT NOT NULL,
  source_table VARCHAR(200),
  source_row_id VARCHAR(200),
  raw_data JSON,
  sync_batch_id VARCHAR(100),
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_batch (sync_batch_id),
  KEY idx_src (source_table, source_row_id),
  KEY idx_tenant (tenant_id)
);