wren
209 lines
10 KiB
PL/PgSQL
209 lines
10 KiB
PL/PgSQL
-- ============================================================================
|
||
-- govdoc 模块建表 DDL
|
||
-- 用途：
|
||
-- 1. 创建 govdoc 模块专用结果域表（run / rule_result / report_artifact）
|
||
-- 2. 给 leaudit_documents 补充 engine_type 字段
|
||
-- 3. 幂等执行，重复跑不报错
|
||
--
|
||
-- 设计原则：
|
||
-- - 复用 leaudit_documents / leaudit_document_files 作为文档主档
|
||
-- - 新建 govdoc 结果域表，不与 leaudit 引擎结果表混用
|
||
-- - 后续规则平台化时再补 govdoc_rule_sets / govdoc_rule_versions
|
||
-- ============================================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 1. govdoc_runs —— 公文审查运行主表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS public.govdoc_runs (
|
||
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
document_id BIGINT NOT NULL ,
|
||
document_file_id BIGINT,
|
||
run_no INTEGER NOT NULL DEFAULT 1,
|
||
trigger_source VARCHAR(64) NOT NULL DEFAULT 'upload',
|
||
trigger_user_id BIGINT,
|
||
task_id VARCHAR(128),
|
||
|
||
-- 运行状态
|
||
status VARCHAR(64) NOT NULL DEFAULT 'pending',
|
||
phase VARCHAR(32),
|
||
|
||
-- 引擎快照
|
||
engine_version VARCHAR(64),
|
||
llm_provider VARCHAR(64),
|
||
llm_model VARCHAR(128),
|
||
rules_path VARCHAR(1024),
|
||
|
||
-- 结果汇总
|
||
total_score NUMERIC(10, 2),
|
||
passed_count INTEGER,
|
||
failed_count INTEGER,
|
||
skipped_count INTEGER,
|
||
result_status VARCHAR(32),
|
||
result_summary_json TEXT,
|
||
error_message TEXT,
|
||
|
||
-- 时间
|
||
started_at TIMESTAMPTZ,
|
||
finished_at TIMESTAMPTZ,
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
deleted_at TIMESTAMPTZ DEFAULT NULL
|
||
);
|
||
|
||
ALTER TABLE public.govdoc_runs
|
||
ADD COLUMN IF NOT EXISTS rules_path VARCHAR(1024);
|
||
|
||
COMMENT ON TABLE public.govdoc_runs IS '公文审查运行主表';
|
||
COMMENT ON COLUMN public.govdoc_runs.id IS '自增主键';
|
||
COMMENT ON COLUMN public.govdoc_runs.document_id IS '关联 leaudit_documents.id';
|
||
COMMENT ON COLUMN public.govdoc_runs.document_file_id IS '输入文件 ID，关联 leaudit_document_files.id';
|
||
COMMENT ON COLUMN public.govdoc_runs.run_no IS '同一文档第几次执行';
|
||
COMMENT ON COLUMN public.govdoc_runs.trigger_source IS '触发来源：upload/manual/retry/migration';
|
||
COMMENT ON COLUMN public.govdoc_runs.trigger_user_id IS '触发人 user_id';
|
||
COMMENT ON COLUMN public.govdoc_runs.task_id IS 'Celery 任务 ID';
|
||
COMMENT ON COLUMN public.govdoc_runs.status IS 'pending/processing/completed/failed/cancelled';
|
||
COMMENT ON COLUMN public.govdoc_runs.phase IS '当前阶段：parsing/executing/reporting';
|
||
COMMENT ON COLUMN public.govdoc_runs.engine_version IS '引擎版本号';
|
||
COMMENT ON COLUMN public.govdoc_runs.llm_provider IS 'LLM 提供商';
|
||
COMMENT ON COLUMN public.govdoc_runs.llm_model IS 'LLM 模型名';
|
||
COMMENT ON COLUMN public.govdoc_runs.rules_path IS '本次运行使用的规则文件路径';
|
||
COMMENT ON COLUMN public.govdoc_runs.total_score IS '总分';
|
||
COMMENT ON COLUMN public.govdoc_runs.passed_count IS '通过规则数';
|
||
COMMENT ON COLUMN public.govdoc_runs.failed_count IS '未通过规则数';
|
||
COMMENT ON COLUMN public.govdoc_runs.skipped_count IS '跳过规则数';
|
||
COMMENT ON COLUMN public.govdoc_runs.result_status IS '综合结果：pass/fail/partial/error';
|
||
COMMENT ON COLUMN public.govdoc_runs.result_summary_json IS '结构化结果摘要 JSON';
|
||
COMMENT ON COLUMN public.govdoc_runs.error_message IS '运行失败时错误描述';
|
||
COMMENT ON COLUMN public.govdoc_runs.started_at IS '开始执行时间';
|
||
COMMENT ON COLUMN public.govdoc_runs.finished_at IS '结束执行时间';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_runs_document_id ON public.govdoc_runs(document_id) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_runs_status ON public.govdoc_runs(status) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_runs_trigger_user_id ON public.govdoc_runs(trigger_user_id);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2. govdoc_rule_results —— 单条规则执行结果
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS public.govdoc_rule_results (
|
||
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
run_id BIGINT NOT NULL,
|
||
|
||
-- 规则标识
|
||
rule_id VARCHAR(128) NOT NULL,
|
||
rule_name VARCHAR(256),
|
||
severity VARCHAR(32),
|
||
category VARCHAR(128),
|
||
|
||
-- 结果内容
|
||
message TEXT,
|
||
suggestion TEXT,
|
||
actual TEXT,
|
||
expected TEXT,
|
||
evidence TEXT,
|
||
|
||
-- 文档定位
|
||
paragraph_index INTEGER,
|
||
paragraph_text TEXT,
|
||
location_path VARCHAR(512),
|
||
|
||
-- 判定
|
||
result VARCHAR(32) NOT NULL DEFAULT 'pass',
|
||
skip_reason TEXT,
|
||
score NUMERIC(10, 2),
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
deleted_at TIMESTAMPTZ DEFAULT NULL
|
||
);
|
||
|
||
ALTER TABLE public.govdoc_rule_results
|
||
ADD COLUMN IF NOT EXISTS skip_reason TEXT;
|
||
|
||
COMMENT ON TABLE public.govdoc_rule_results IS '公文规则执行结果明细表';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.id IS '自增主键';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.run_id IS '关联 govdoc_runs.id';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.rule_id IS '规则标识';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.rule_name IS '规则名称';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.severity IS '严重等级：error/warning/info';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.category IS '规则分类';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.message IS '结果描述';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.suggestion IS '修改建议';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.actual IS '实际值';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.expected IS '期望值';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.evidence IS '证据文本';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.paragraph_index IS '段落索引';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.paragraph_text IS '段落原文';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.location_path IS '文档结构位置路径';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.result IS '执行结果：pass/fail/skipped/error';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.skip_reason IS '跳过原因，仅 skipped/error 时使用';
|
||
COMMENT ON COLUMN public.govdoc_rule_results.score IS '本条得分';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_rule_results_run_id ON public.govdoc_rule_results(run_id) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_rule_results_rule_id ON public.govdoc_rule_results(rule_id) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_rule_results_result ON public.govdoc_rule_results(result) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_rule_results_paragraph ON public.govdoc_rule_results(run_id, paragraph_index) WHERE deleted_at IS NULL;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 3. govdoc_report_artifacts —— 报告产物索引
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS public.govdoc_report_artifacts (
|
||
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
run_id BIGINT NOT NULL,
|
||
|
||
artifact_type VARCHAR(64) NOT NULL,
|
||
file_name VARCHAR(512) NOT NULL,
|
||
file_ext VARCHAR(32),
|
||
mime_type VARCHAR(128),
|
||
file_size BIGINT,
|
||
sha256 VARCHAR(64),
|
||
oss_url VARCHAR(2048),
|
||
storage_provider VARCHAR(32),
|
||
description VARCHAR(512),
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
deleted_at TIMESTAMPTZ DEFAULT NULL
|
||
);
|
||
|
||
COMMENT ON TABLE public.govdoc_report_artifacts IS '公文审查报告产物索引表';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.id IS '自增主键';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.run_id IS '关联 govdoc_runs.id';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.artifact_type IS '产物类型：html_report/annotated_docx/paragraph_html/json_report/original';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.file_name IS '文件名';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.file_ext IS '扩展名';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.mime_type IS 'MIME 类型';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.file_size IS '文件大小（字节）';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.sha256 IS '文件 SHA256';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.oss_url IS 'OSS 访问地址';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.storage_provider IS '存储提供商：oss/minio/local';
|
||
COMMENT ON COLUMN public.govdoc_report_artifacts.description IS '产物说明';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_report_artifacts_run_id ON public.govdoc_report_artifacts(run_id) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS idx_govdoc_report_artifacts_type ON public.govdoc_report_artifacts(run_id, artifact_type) WHERE deleted_at IS NULL;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 4. leaudit_documents —— 补充 engine_type 字段
|
||
-- ---------------------------------------------------------------------------
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM information_schema.columns
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'leaudit_documents'
|
||
AND column_name = 'engine_type'
|
||
) THEN
|
||
ALTER TABLE public.leaudit_documents
|
||
ADD COLUMN engine_type VARCHAR(32) NOT NULL DEFAULT 'leaudit';
|
||
COMMENT ON COLUMN public.leaudit_documents.engine_type IS '引擎类型：leaudit/govdoc/rag';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 为 engine_type 加索引，方便按模块过滤文档列表
|
||
CREATE INDEX IF NOT EXISTS idx_leaudit_documents_engine_type ON public.leaudit_documents(engine_type) WHERE deleted_at IS NULL;
|
||
|
||
COMMIT;
|