评估跑腿系统稳定性，提交超时后的幂等键与结果回读

CREATE TABLE submit_request (
 tenant_id text NOT NULL, actor_id text NOT NULL,
 request_id text NOT NULL, payload_sha char(64) NOT NULL,
 state text NOT NULL CHECK(state IN ('reserved','accepted')),
 task_id text UNIQUE,
 PRIMARY KEY(tenant_id,actor_id,request_id),
 CHECK(payload_sha ~ '^[0-9a-f]{64}$'),
 CHECK((state='accepted')=(task_id IS NOT NULL))
);
CREATE TABLE delivery_task (
 task_id text PRIMARY KEY, tenant_id text NOT NULL,
 actor_id text NOT NULL, request_id text NOT NULL,
 state text NOT NULL CHECK(state IN ('waiting','assigned','done','cancelled')),
 UNIQUE(tenant_id,actor_id,request_id),
 FOREIGN KEY(tenant_id,actor_id,request_id)
 REFERENCES submit_request(tenant_id,actor_id,request_id)
);
-- 应用驱动绑定参数，以下为一次提交事务的核心语句。
BEGIN;
INSERT INTO submit_request
 (tenant_id,actor_id,request_id,payload_sha,state)
VALUES (:tenant,:actor,:request,:payload_sha,'reserved')
ON CONFLICT(tenant_id,actor_id,request_id) DO NOTHING;
SELECT payload_sha,state,task_id FROM submit_request
 WHERE tenant_id=:tenant AND actor_id=:actor AND request_id=:request
 FOR UPDATE;
-- 驱动必须在此比较payload_sha：不同则ROLLBACK并返回冲突；
-- accepted且哈希相同则COMMIT后返回原task_id，不继续下面插入。
-- reserved且哈希相同：绑定本次生成的全局唯一task_id。
INSERT INTO delivery_task(task_id,tenant_id,actor_id,request_id,state)
 VALUES (:task,:tenant,:actor,:request,'waiting');
UPDATE submit_request SET task_id=:task,state='accepted'
 WHERE tenant_id=:tenant AND actor_id=:actor AND request_id=:request
 AND state='reserved' AND payload_sha=:payload_sha;
-- 驱动核UPDATE恰好一行，否则回滚整个事务。
COMMIT;
-- 超时后的只读查询，仍从已认证上下文限定租户与用户。
SELECT state,task_id FROM submit_request
 WHERE tenant_id=:tenant AND actor_id=:actor AND request_id=:request;