ad7fba6021
- core：配置（prod 拒绝默认密钥 / dev 短信 / 假模型）、租户事务 + 行级安全策略、 problem+json 错误码、信封加密与签名下载、对象存储、事务内入队、Valkey stream 事件流、权限点 - identity：手机号 HMAC 存储、注册、登录（挤下线需确认，API3）、服务端会话 - billing：额度流水只追加，预扣 / 结算 / 退回，一笔预扣只有一个结局 - document：分片上传、按文件头识别格式、Gotenberg 转换、文字层与稳定句子编号 - checklist：标注规则识别、模型抽取、三道校验、标注类条款规则兜底、重跑对齐 - ai：提示词注册表（extract.substantive v1 来自 S4 v2）、假模型、OpenAI 兼容供应商、ai_call 流水 - 迁移：data-model 基线、套餐、Procrastinate、会话状态函数 - 测试 30 个：租户隔离、额度、失败退回、对象加密、契约一致性 - Compose（含 migrate）、镜像、CI Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
142 lines
8.1 KiB
PL/PgSQL
142 lines
8.1 KiB
PL/PgSQL
-- 4. document：文档、页、出处、解析任务（FR-M2-01、02、06，FR-M3-01）
|
||
-- 来源：docs/architecture/schema-draft.sql（2026-11-03 验证过的草案）
|
||
CREATE TABLE source_document (
|
||
id uuid PRIMARY KEY,
|
||
tenant_id uuid NOT NULL,
|
||
project_id uuid NOT NULL,
|
||
role text NOT NULL
|
||
CHECK (role IN ('tender', 'addendum', 'clarification', 'bid_volume')),
|
||
seq_no int NOT NULL, -- 招标侧：补遗 N 的 N；投标侧：分册内的版本号
|
||
volume_name text, -- 投标分册：商务 / 技术 / 报价 / 资格证明
|
||
lineage_id uuid NOT NULL, -- 同一分册的各个版本共用；招标侧等于 id
|
||
original_filename text NOT NULL,
|
||
detected_format text NOT NULL
|
||
CHECK (detected_format IN ('pdf', 'docx', 'doc', 'wps', 'ofd', 'encrypted_bid', 'unknown')),
|
||
size_bytes bigint NOT NULL,
|
||
original_key text NOT NULL, -- 对象存储键
|
||
original_sha256 bytea NOT NULL,
|
||
-- 规范化 PDF：只生成一次，写入后不再改（由服务层和触发器保证）
|
||
normalized_key text,
|
||
normalized_sha256 bytea,
|
||
page_count int,
|
||
text_layer_key text, -- 逐字坐标、格式、句子，存对象存储（DM1）
|
||
pipeline_profile jsonb, -- 转换器、字体包、切句算法的版本，复盘用
|
||
status text NOT NULL DEFAULT 'uploaded'
|
||
CHECK (status IN ('uploaded', 'normalizing', 'ready', 'failed')),
|
||
uploaded_by uuid NOT NULL REFERENCES user_account (id),
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
UNIQUE (tenant_id, id),
|
||
UNIQUE (tenant_id, project_id, role, lineage_id, seq_no),
|
||
FOREIGN KEY (tenant_id, project_id) REFERENCES project (tenant_id, id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE FUNCTION freeze_normalized_pdf() RETURNS trigger
|
||
LANGUAGE plpgsql AS $$
|
||
BEGIN
|
||
IF OLD.normalized_sha256 IS NOT NULL AND
|
||
(NEW.normalized_sha256 IS DISTINCT FROM OLD.normalized_sha256 OR
|
||
NEW.normalized_key IS DISTINCT FROM OLD.normalized_key) THEN
|
||
RAISE EXCEPTION 'normalized PDF of document % is immutable', OLD.id;
|
||
END IF;
|
||
RETURN NEW;
|
||
END $$;
|
||
CREATE TRIGGER source_document_freeze BEFORE UPDATE ON source_document
|
||
FOR EACH ROW EXECUTE FUNCTION freeze_normalized_pdf();
|
||
|
||
CREATE TABLE document_page ( -- 逐页摘要：覆盖率检查、OCR 决策、阅读器尺寸
|
||
tenant_id uuid NOT NULL,
|
||
document_id uuid NOT NULL,
|
||
page_no int NOT NULL,
|
||
width_pt real NOT NULL,
|
||
height_pt real NOT NULL,
|
||
rotation int NOT NULL DEFAULT 0,
|
||
text_source text NOT NULL CHECK (text_source IN ('text', 'ocr', 'mixed', 'empty')),
|
||
char_count int NOT NULL,
|
||
sentence_count int NOT NULL,
|
||
PRIMARY KEY (tenant_id, document_id, page_no),
|
||
FOREIGN KEY (tenant_id, document_id) REFERENCES source_document (tenant_id, id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE parse_job ( -- 招标文件 / 补遗 / 澄清的解析；重试是新的一行
|
||
id uuid PRIMARY KEY,
|
||
tenant_id uuid NOT NULL,
|
||
project_id uuid NOT NULL,
|
||
document_id uuid NOT NULL,
|
||
attempt_no int NOT NULL,
|
||
status text NOT NULL DEFAULT 'queued'
|
||
CHECK (status IN ('queued', 'running', 'succeeded', 'failed', 'cancelled')),
|
||
stage text CHECK (stage IN ('detect', 'convert', 'text_layer', 'ocr', 'structure',
|
||
'extract', 'verify', 'merge')),
|
||
checkpoints jsonb NOT NULL DEFAULT '{}', -- 每个阶段完成时写入，失败后从这里续跑
|
||
progress int NOT NULL DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
|
||
cancel_requested_at timestamptz,
|
||
error_class text CHECK (error_class IN ('infra', 'input', 'content', 'cancelled')),
|
||
error_code text,
|
||
error_detail text, -- 不含文档内容
|
||
prompt_versions jsonb, -- {"extract.qualification": "v3", ...}；逐次调用见 ai_call
|
||
quota_hold_id uuid,
|
||
queue_job_id bigint, -- Procrastinate 的任务编号
|
||
created_by uuid NOT NULL REFERENCES user_account (id),
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
started_at timestamptz,
|
||
finished_at timestamptz,
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
UNIQUE (tenant_id, id),
|
||
UNIQUE (tenant_id, document_id, attempt_no),
|
||
FOREIGN KEY (tenant_id, project_id) REFERENCES project (tenant_id, id) ON DELETE CASCADE,
|
||
FOREIGN KEY (tenant_id, document_id) REFERENCES source_document (tenant_id, id) ON DELETE CASCADE,
|
||
FOREIGN KEY (tenant_id, quota_hold_id) REFERENCES quota_entry (tenant_id, id)
|
||
);
|
||
-- 一份文档同时只能有一个在跑的解析
|
||
CREATE UNIQUE INDEX ON parse_job (tenant_id, document_id) WHERE status IN ('queued', 'running');
|
||
|
||
-- 出处：不可变。清单项、问题、项目字段、标注规则都引用它
|
||
CREATE TABLE citation (
|
||
id uuid PRIMARY KEY,
|
||
tenant_id uuid NOT NULL,
|
||
project_id uuid NOT NULL,
|
||
document_id uuid NOT NULL,
|
||
normalized_sha256 bytea NOT NULL, -- 和文档上的哈希一致才能渲染
|
||
page_no int NOT NULL,
|
||
sentence_keys text[] NOT NULL, -- 同一页上连续的句子，如 {<doc>:p12:s3, <doc>:p12:s4}
|
||
char_start int, -- 只引用句子的一部分时用，相对于首句
|
||
char_end int,
|
||
quads jsonb NOT NULL, -- 坐标框快照，阅读器直接画
|
||
quote text NOT NULL, -- 原句快照
|
||
created_by_kind text NOT NULL CHECK (created_by_kind IN ('ai', 'rule', 'human')),
|
||
checks_passed text[] NOT NULL DEFAULT '{}', -- {id, quote, basis}：三道校验（架构 §3.6）
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
UNIQUE (tenant_id, id),
|
||
CHECK (cardinality(sentence_keys) >= 1),
|
||
FOREIGN KEY (tenant_id, project_id) REFERENCES project (tenant_id, id) ON DELETE CASCADE,
|
||
FOREIGN KEY (tenant_id, document_id) REFERENCES source_document (tenant_id, id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX ON citation (tenant_id, document_id, page_no);
|
||
CREATE TRIGGER citation_immutable BEFORE UPDATE ON citation
|
||
FOR EACH ROW EXECUTE FUNCTION forbid_change();
|
||
|
||
-- 项目基本信息字段（FR-M2-03）：AI 值和人工值分开，每个字段带出处
|
||
CREATE TABLE project_field (
|
||
tenant_id uuid NOT NULL,
|
||
project_id uuid NOT NULL,
|
||
field_key text NOT NULL
|
||
CHECK (field_key IN ('name', 'code', 'purchaser', 'agency', 'procurement_method',
|
||
'legal_regime', 'budget_total', 'ceiling_total', 'eval_method',
|
||
'accept_consortium', 'accept_imported', 'sme_only', 'deadline_at')),
|
||
ai_value jsonb,
|
||
ai_citation_id uuid,
|
||
ai_document_id uuid, -- 最新一次提取来自哪份文件（补遗覆盖时变）
|
||
human_value jsonb,
|
||
human_set_by uuid REFERENCES user_account (id),
|
||
human_set_at timestamptz,
|
||
pending_value jsonb, -- 补遗带来的新值，等用户确认（FR-M2-10）
|
||
pending_citation_id uuid,
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
PRIMARY KEY (tenant_id, project_id, field_key),
|
||
FOREIGN KEY (tenant_id, project_id) REFERENCES project (tenant_id, id) ON DELETE CASCADE,
|
||
FOREIGN KEY (tenant_id, ai_citation_id) REFERENCES citation (tenant_id, id) ON DELETE SET NULL (ai_citation_id),
|
||
FOREIGN KEY (tenant_id, pending_citation_id) REFERENCES citation (tenant_id, id) ON DELETE SET NULL (pending_citation_id),
|
||
FOREIGN KEY (tenant_id, ai_document_id) REFERENCES source_document (tenant_id, id) ON DELETE SET NULL (ai_document_id)
|
||
);
|