文件与大件跑腿报价验收：确认价快照与重复确认约束

CREATE TABLE price_quote (
 quote_id text PRIMARY KEY,
 input_digest text NOT NULL,
 rule_version text NOT NULL,
 kind text NOT NULL CHECK (kind IN ('document','bulk')),
 meters integer NOT NULL CHECK (meters >= 0),
 grams integer NOT NULL CHECK (grams > 0),
 base_fen bigint NOT NULL CHECK (base_fen >= 0),
 distance_fen bigint NOT NULL CHECK (distance_fen >= 0),
 weight_fen bigint NOT NULL CHECK (weight_fen >= 0),
 time_fen bigint NOT NULL CHECK (time_fen >= 0),
 total_fen bigint NOT NULL CHECK (total_fen >= 0),
 carry_approved boolean NOT NULL CHECK (carry_approved),
 expires_at timestamptz NOT NULL,
 CHECK (total_fen = base_fen + distance_fen + weight_fen + time_fen)
);
CREATE TABLE quote_confirmation (
 quote_id text PRIMARY KEY REFERENCES price_quote(quote_id),
 order_id text NOT NULL UNIQUE,
 request_key text NOT NULL UNIQUE,
 confirmed_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

BEGIN;
SELECT * FROM price_quote WHERE quote_id = :quote_id FOR UPDATE;
SELECT * FROM quote_confirmation WHERE quote_id = :quote_id;
-- 应用按上文先处理已确认重试，再核对当前输入与有效期。
INSERT INTO quote_confirmation(quote_id,order_id,request_key)
SELECT quote_id,:order_id,:request_key FROM price_quote
WHERE quote_id=:quote_id AND input_digest=:current_digest
  AND expires_at>clock_timestamp() AND carry_approved
ON CONFLICT DO NOTHING
RETURNING quote_id,order_id,confirmed_at;
COMMIT;