284 lines
17 KiB
SQL
284 lines
17 KiB
SQL
-- =====================================================================
|
|
-- tradingSystem (PMS) V1 建表 DDL
|
|
-- 目标库: 153 代理侧 (与 decision_ledger 等同库), 一律经代理严格单表访问
|
|
-- 字符集: utf8mb4; 代码格式: Tushare 点式 (600000.SH); 时区: Asia/Shanghai
|
|
-- 对应设计: POSITION_MGMT_DESIGN.md V0.4 §11 (1~10 号表)
|
|
-- QMT_WS_PROTOCOL.md V1.0 (11~13 号表, 2026-07-28 追加)
|
|
-- 注: pms_order_request (原表轮询通道) 已作废 —— 双方改定 WebSocket 直连, 见文件尾部
|
|
-- 三张 ws 通道表。该表本就未在此文件建立, 无需处理。
|
|
-- =====================================================================
|
|
|
|
-- 1. 命令表 (参数命令 + 任务命令; 参数命令当前值 = 该类型最新一条 EFFECTIVE 记录)
|
|
CREATE TABLE IF NOT EXISTS pms_command (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
command_id VARCHAR(64) NOT NULL UNIQUE COMMENT '幂等键: CMD_{ymd}_{seq}',
|
|
cmd_class VARCHAR(8) NOT NULL COMMENT 'param(参数命令) / task(任务命令)',
|
|
cmd_type VARCHAR(32) NOT NULL COMMENT 'SET_SCALE / REDUCE_EXPOSURE / OPEN_TARGET / T0_ENABLE ...',
|
|
ts_code VARCHAR(16) NULL COMMENT '个股级命令的标的; 组合级为 NULL',
|
|
params_json TEXT NOT NULL COMMENT '命令参数 (比例/窗口/价格等)',
|
|
status VARCHAR(16) NOT NULL DEFAULT 'PENDING'
|
|
COMMENT '参数命令: EFFECTIVE/SUPERSEDED; 任务命令: PENDING/PLANNING/EXECUTING/PARTIAL/DONE/CANCELLED',
|
|
progress_json TEXT NULL COMMENT '任务进度 (目标额/已完成额/明细指针/回执)',
|
|
issued_by VARCHAR(32) NOT NULL DEFAULT 'user',
|
|
issued_at DATETIME NOT NULL,
|
|
done_at DATETIME NULL,
|
|
note VARCHAR(500) NULL,
|
|
KEY idx_type_status (cmd_type, status),
|
|
KEY idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户命令与进度';
|
|
|
|
-- 2. 方案明细 (任务命令展开的分股行动清单)
|
|
CREATE TABLE IF NOT EXISTS pms_plan (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
plan_id VARCHAR(64) NOT NULL UNIQUE COMMENT 'PLAN_{command_id}_{seq}',
|
|
command_id VARCHAR(64) NOT NULL,
|
|
ts_code VARCHAR(16) NOT NULL,
|
|
action VARCHAR(16) NOT NULL COMMENT 'OPEN/FILL/ADD/DCA/TRIM/EXIT/HALT/T0_ROUND',
|
|
qty INT NULL COMMENT '目标股数 (与 amount 二选一)',
|
|
amount DECIMAL(14,2) NULL COMMENT '目标金额 (元)',
|
|
priority INT NOT NULL DEFAULT 100 COMMENT '越小越先执行',
|
|
deadline DATE NULL COMMENT '执行窗口截止日',
|
|
status VARCHAR(16) NOT NULL DEFAULT 'PENDING'
|
|
COMMENT 'PENDING/GATED(建仓补足加仓批,待动作引擎解锁)/EXECUTING/DONE/PARTIAL/CANCELLED',
|
|
filled_qty INT NOT NULL DEFAULT 0,
|
|
reason VARCHAR(300) NULL COMMENT '进方案的理由 (弱票清仓/收利润/等比减 等)',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
KEY idx_cmd (command_id),
|
|
KEY idx_code_status (ts_code, status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='方案明细';
|
|
|
|
-- 3. 持仓主档 (一票一行; 实际持仓以下游 trading_position 为准, 本表是结构解释账本)
|
|
CREATE TABLE IF NOT EXISTS pms_position (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
ts_code VARCHAR(16) NOT NULL UNIQUE,
|
|
status VARCHAR(16) NOT NULL DEFAULT 'HOLDING'
|
|
COMMENT 'PLANNED/OPENING/HOLDING/EXITING/CLOSED',
|
|
frozen_reason VARCHAR(16) NOT NULL DEFAULT 'NONE'
|
|
COMMENT 'NONE/COMMAND_HALT/BRAKE/MANUAL (只禁增持不禁减持)',
|
|
total_qty INT NOT NULL DEFAULT 0,
|
|
avail_qty INT NOT NULL DEFAULT 0 COMMENT 'T+1 可卖数, 日初重置',
|
|
base_qty INT NOT NULL DEFAULT 0,
|
|
fill_qty INT NOT NULL DEFAULT 0,
|
|
add_qty INT NOT NULL DEFAULT 0,
|
|
dca_qty INT NOT NULL DEFAULT 0,
|
|
t0_qty INT NOT NULL DEFAULT 0 COMMENT '日内T仓 (收盘应为0)',
|
|
avg_cost DECIMAL(10,3) NULL COMMENT '摊薄成本 (含已实现T利润与批次盈亏)',
|
|
realized_t_profit DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
cushion_pct DECIMAL(8,4) NULL COMMENT '安全垫 = 现价/摊薄成本 - 1',
|
|
cushion_state VARCHAR(8) NOT NULL DEFAULT 'NONE' COMMENT 'NONE/THIN/SOLID',
|
|
cushion_peak DECIMAL(8,4) NOT NULL DEFAULT 0,
|
|
pct_of_scale DECIMAL(8,4) NULL COMMENT '市值占总规模比',
|
|
target_pct DECIMAL(8,4) NULL COMMENT '目标仓位比 (建仓命令设定)',
|
|
stop_ref DECIMAL(10,2) NULL COMMENT '止损参考位',
|
|
support_ref DECIMAL(10,2) NULL,
|
|
pressure_ref DECIMAL(10,2) NULL,
|
|
ref_source VARCHAR(16) NULL COMMENT 'bionic / self_calc(兜底自算) / user(命令覆盖)',
|
|
t0_enabled TINYINT NOT NULL DEFAULT 0,
|
|
t0_ratio DECIMAL(6,4) NULL,
|
|
opened_date DATE NULL,
|
|
fill_count INT NOT NULL DEFAULT 0,
|
|
last_add_date DATE NULL,
|
|
dca_count INT NOT NULL DEFAULT 0,
|
|
t0_count_today INT NOT NULL DEFAULT 0,
|
|
updated_at DATETIME NOT NULL,
|
|
KEY idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='持仓主档';
|
|
|
|
-- 4. 批次账
|
|
CREATE TABLE IF NOT EXISTS pms_lot (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
ts_code VARCHAR(16) NOT NULL,
|
|
lot_type VARCHAR(8) NOT NULL COMMENT 'BASE/FILL/ADD/DCA/T0/RECON(对账调整)',
|
|
qty INT NOT NULL,
|
|
open_price DECIMAL(10,3) NOT NULL,
|
|
open_date DATE NOT NULL,
|
|
closed_qty INT NOT NULL DEFAULT 0,
|
|
close_avg_price DECIMAL(10,3) NULL,
|
|
realized_pnl DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
status VARCHAR(8) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN/CLOSED',
|
|
instruction_id VARCHAR(64) NULL COMMENT '来源指令; NULL=外部成交并入',
|
|
note VARCHAR(200) NULL,
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
KEY idx_code_status (ts_code, status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='批次账';
|
|
|
|
-- 5. 指令状态机 (父指令; 分日出手的子单记 progress_json)
|
|
CREATE TABLE IF NOT EXISTS pms_instruction (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
instruction_id VARCHAR(64) NOT NULL UNIQUE COMMENT '幂等键: INS_{ymd}_{code}_{action}_{seq}',
|
|
origin_type VARCHAR(8) NOT NULL COMMENT 'plan(命令方案) / proposal(自主提议) / system(保垫等自动)',
|
|
origin_id VARCHAR(64) NULL,
|
|
ts_code VARCHAR(16) NOT NULL,
|
|
action VARCHAR(16) NOT NULL,
|
|
side VARCHAR(8) NOT NULL COMMENT 'buy/sell',
|
|
qty INT NOT NULL,
|
|
limit_price DECIMAL(10,2) NULL,
|
|
window_tdays INT NOT NULL DEFAULT 3,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'PROPOSED'
|
|
COMMENT 'PROPOSED/RULE_PASSED/JUDGE_PASSED/DISPATCHED/CONFIRMED/REJECTED/EXPIRED/CANCELLED',
|
|
dispatch_ref VARCHAR(64) NULL COMMENT '下游通道引用 (pms_order_request.instruction_id)',
|
|
exec_qty INT NOT NULL DEFAULT 0,
|
|
exec_avg_price DECIMAL(10,3) NULL,
|
|
progress_json TEXT NULL COMMENT '分日子单与出手记录',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
KEY idx_code_status (ts_code, status),
|
|
KEY idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='指令状态机';
|
|
|
|
-- 6. 自主提议待确认队列 (propose_only 档)
|
|
CREATE TABLE IF NOT EXISTS pms_proposal (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
proposal_id VARCHAR(64) NOT NULL UNIQUE,
|
|
ts_code VARCHAR(16) NOT NULL,
|
|
action VARCHAR(16) NOT NULL,
|
|
qty INT NOT NULL,
|
|
hard_numbers_json TEXT NOT NULL COMMENT '安全垫/浮亏/参考位距离/敞口等, 页面展示',
|
|
judge_verdict VARCHAR(16) NULL COMMENT '决策系统研判结论 (接通后)',
|
|
judge_reason VARCHAR(500) NULL,
|
|
status VARCHAR(16) NOT NULL DEFAULT 'WAIT_USER'
|
|
COMMENT 'WAIT_USER/ACCEPTED/DECLINED/EXPIRED',
|
|
expire_at DATETIME NOT NULL,
|
|
decided_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL,
|
|
KEY idx_status (status),
|
|
KEY idx_code (ts_code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自主提议待确认';
|
|
|
|
-- 7. 评审账本 (规则闸/研判闸/人工确认 全量留痕, 判分锚)
|
|
CREATE TABLE IF NOT EXISTS pms_action_ledger (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
ts_code VARCHAR(16) NOT NULL,
|
|
decided_at DATETIME NOT NULL,
|
|
action VARCHAR(16) NOT NULL,
|
|
arbiter VARCHAR(8) NOT NULL COMMENT 'rule/judge/user',
|
|
verdict VARCHAR(8) NOT NULL COMMENT 'PASS/REJECT',
|
|
price_at DECIMAL(10,3) NOT NULL COMMENT '评审时现价 (反事实判分锚, 必填)',
|
|
hard_numbers_json TEXT NULL,
|
|
failed_checks_json TEXT NULL COMMENT '未通过项列表 (规则闸)',
|
|
reason VARCHAR(500) NULL COMMENT '理由 (研判闸/人工)',
|
|
ref_id VARCHAR(64) NULL COMMENT '关联 instruction/proposal/plan',
|
|
outcome_scored TINYINT NOT NULL DEFAULT 0 COMMENT '判分位图: 1=T+1, 2=T+5, 4=T+20',
|
|
KEY idx_code_time (ts_code, decided_at),
|
|
KEY idx_scored (outcome_scored)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评审账本';
|
|
|
|
-- 8. 运营日报 (可重跑覆盖)
|
|
CREATE TABLE IF NOT EXISTS pms_daily_report (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
ymd INT NOT NULL UNIQUE COMMENT 'YYYYMMDD',
|
|
report_json MEDIUMTEXT NOT NULL,
|
|
created_at DATETIME NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运营日报';
|
|
|
|
-- 9. 行业映射 (行业划分接口的 custom_table 数据源; 用户后续灌数, 灌何种划分不限)
|
|
CREATE TABLE IF NOT EXISTS pms_industry_map (
|
|
ts_code VARCHAR(16) PRIMARY KEY,
|
|
industry VARCHAR(64) NOT NULL,
|
|
updated_at DATETIME NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行业映射 (自定义数据源)';
|
|
|
|
-- 10. 运行参数持久层 (页面修改的参数落此, 优先于 settings 文件初值)
|
|
CREATE TABLE IF NOT EXISTS pms_runtime_param (
|
|
param_key VARCHAR(64) PRIMARY KEY,
|
|
param_value VARCHAR(200) NOT NULL,
|
|
updated_by VARCHAR(32) NOT NULL DEFAULT 'user',
|
|
updated_at DATETIME NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='页面可调参数当前值';
|
|
|
|
-- =====================================================================
|
|
-- ws 直连通道三表 (2026-07-28 追加; 协议 QMT_WS_PROTOCOL.md V1.0)
|
|
-- ---------------------------------------------------------------------
|
|
-- 为什么是三张而不是塞进已有表:
|
|
-- * 出口队列必须**持久**。ws 是常驻进程、executor 在 celery worker 里, 两个进程之间
|
|
-- 递指令得有个落点; 落在业务表上则「先记账后动作」这条铁律字面成立 —— 落表即记账,
|
|
-- ws 进程崩了重启队列还在, 页面也能直接看到在途委托。
|
|
-- * 上行消息必须**先落库再 ack** (协议 §4.5)。收到就 ack、然后崩在落库前, 那段数据
|
|
-- QMT 那边已经清了, 永久丢失。所以 inbox 是独立表且 ack 在它之后。
|
|
-- * seq 水位跨重启不能回退, 且更新频率高, 不适合塞 pms_runtime_param
|
|
-- (那张表有 5 秒缓存, 且是给页面调参用的)。
|
|
-- =====================================================================
|
|
|
|
-- 11. QMT 委托表 (子单) —— 兼下发出口队列
|
|
-- 一行 = 协议里的一条 place_order = 交易所的一张委托 (§8「一条指令 = 一张委托」)。
|
|
-- pms_instruction 是父指令 (承载一个方案条目的总量), 本表是它分日/分笔出手的子单。
|
|
CREATE TABLE IF NOT EXISTS pms_qmt_order (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
instruction_id VARCHAR(64) NOT NULL UNIQUE COMMENT '协议幂等键 (§2.2), 也是子单主键',
|
|
parent_id VARCHAR(64) NOT NULL COMMENT '父指令 pms_instruction.instruction_id',
|
|
ts_code VARCHAR(16) NOT NULL COMMENT '点式 600000.SH',
|
|
side VARCHAR(8) NOT NULL COMMENT 'buy/sell',
|
|
qty INT NOT NULL COMMENT '股数; 买入整百, 清仓允许零股尾数',
|
|
limit_price DECIMAL(10,2) NOT NULL COMMENT '限价, 2位小数 (协议不接受 null)',
|
|
valid_until BIGINT NOT NULL COMMENT '有效期截止 epoch 毫秒, 到点 QMT 自动撤',
|
|
intent VARCHAR(8) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN/FILL/ADD/DCA/TRIM/EXIT/T0',
|
|
note VARCHAR(200) NULL,
|
|
status VARCHAR(16) NOT NULL DEFAULT 'QUEUED'
|
|
COMMENT '本地: QUEUED待发/SENDING已认领/SENT已发出/SEND_FAILED/ABORTED未发即作废; '
|
|
'协议: ACCEPTED/SUBMITTED/PARTIAL/FILLED/CANCELLED/EXPIRED/REJECTED',
|
|
broker_order_id VARCHAR(64) NULL COMMENT 'QMT 回的委托号',
|
|
cum_qty INT NOT NULL DEFAULT 0 COMMENT '本委托累计成交股数 (§5.4 口径)',
|
|
cum_avg_price DECIMAL(10,3) NULL COMMENT '本委托累计成交均价',
|
|
leaves_qty INT NULL COMMENT '未成交剩余',
|
|
cancel_state VARCHAR(12) NOT NULL DEFAULT 'NONE'
|
|
COMMENT 'NONE/REQUESTED(已请求撤)/SENT(撤单已发出) —— 区分 CANCELLED 与 EXPIRED 的本地依据',
|
|
cancel_id VARCHAR(64) NULL COMMENT 'CXL-yyyymmdd-8位随机',
|
|
cancel_req_at DATETIME NULL,
|
|
reject_code VARCHAR(32) NULL COMMENT '协议 §7.3 拒绝码',
|
|
reject_reason VARCHAR(300) NULL,
|
|
send_attempts INT NOT NULL DEFAULT 0,
|
|
sent_at DATETIME NULL,
|
|
final_at DATETIME NULL COMMENT '进终态时刻',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
KEY idx_status (status),
|
|
KEY idx_parent (parent_id),
|
|
KEY idx_cancel (cancel_state),
|
|
KEY idx_broker (broker_order_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='QMT 委托子单 (兼下发出口队列)';
|
|
|
|
-- 12. 上行消息收件箱 —— 「确认前必须已落库」的那个"库"
|
|
-- seq 作主键 = 协议 §6.1 第一层去重 (补发时同一条消息 seq 不变);
|
|
-- dedup_key 唯一 = 第二层去重 (trade_no)。任一层命中即丢弃。
|
|
CREATE TABLE IF NOT EXISTS pms_qmt_inbox (
|
|
seq BIGINT PRIMARY KEY COMMENT 'QMT 侧全局单调递增序号 (第一层去重)',
|
|
msg_id VARCHAR(64) NOT NULL,
|
|
msg_type VARCHAR(24) NOT NULL COMMENT 'trade/order_update/ack/reject/snapshot/...',
|
|
corr_id VARCHAR(64) NULL COMMENT '关联的 instruction_id 或 cancel_id',
|
|
dedup_key VARCHAR(80) NULL UNIQUE COMMENT '第二层去重键, 目前只有 trade:{trade_no}',
|
|
payload_json TEXT NOT NULL COMMENT '原样存 payload, 供事后追溯与下批入账消费',
|
|
msg_ts BIGINT NOT NULL COMMENT '对端发送时刻 epoch 毫秒',
|
|
received_at DATETIME NOT NULL,
|
|
processed TINYINT NOT NULL DEFAULT 0
|
|
COMMENT '0=待入账(仅 trade) / 1=已入账 / 2=通道自处理完毕无需入账',
|
|
processed_at DATETIME NULL,
|
|
process_note VARCHAR(300) NULL,
|
|
KEY idx_pending (processed, seq),
|
|
KEY idx_corr (corr_id),
|
|
KEY idx_type (msg_type)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='QMT 上行消息收件箱';
|
|
|
|
-- 13. 通道状态 (恒一行, id=1)
|
|
-- last_seq 是**连续前缀水位**, 不是收到的最大 seq —— 中间缺一条就不能往前跨,
|
|
-- 否则那条就永久丢了 (§4.5 累积确认语义)。真值可由 inbox 重算, 本行是缓存。
|
|
CREATE TABLE IF NOT EXISTS pms_ws_state (
|
|
id TINYINT PRIMARY KEY COMMENT '恒为 1',
|
|
last_seq BIGINT NOT NULL DEFAULT 0 COMMENT '已落库的连续水位 (hello.last_seq 取它)',
|
|
acked_seq BIGINT NOT NULL DEFAULT 0 COMMENT '已发出 ack_seq 的水位',
|
|
server_seq BIGINT NOT NULL DEFAULT 0 COMMENT 'hello_ack 里对端自报的最新序号',
|
|
conn_state VARCHAR(12) NOT NULL DEFAULT 'INIT'
|
|
COMMENT 'INIT/CONNECTING/ONLINE/OFFLINE/STOPPED —— dispatcher 据此决定放不放行',
|
|
connected_at DATETIME NULL,
|
|
heartbeat_at DATETIME NULL COMMENT 'ws 进程存活心跳; 陈旧即视为进程已死, 一律拒发',
|
|
resync_flag TINYINT NOT NULL DEFAULT 0 COMMENT '1=对端补不齐, 须走全量对账 (§6.2)',
|
|
last_error VARCHAR(300) NULL,
|
|
stat_json TEXT NULL COMMENT '收发计数/重连次数等, 页面运维抽屉展示',
|
|
updated_at DATETIME NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ws 通道状态与序号水位';
|
|
|
|
INSERT INTO pms_ws_state (id, last_seq, acked_seq, server_seq, conn_state, updated_at)
|
|
VALUES (1, 0, 0, 0, 'INIT', NOW())
|
|
ON DUPLICATE KEY UPDATE id = id;
|