216 lines
12 KiB
SQL
216 lines
12 KiB
SQL
DROP SCHEMA IF EXISTS ai CASCADE;
|
||
CREATE SCHEMA IF NOT EXISTS ai;
|
||
|
||
DROP TABLE IF EXISTS ai.tb_ai_knowledge_file CASCADE;
|
||
CREATE TABLE IF NOT EXISTS ai.tb_ai_knowledge_file (
|
||
file_id VARCHAR(64) PRIMARY KEY,
|
||
file_code VARCHAR(64) UNIQUE NOT NULL,
|
||
file_name VARCHAR(255) NOT NULL,
|
||
source_file_id VARCHAR(64),
|
||
file_type VARCHAR(32) NOT NULL DEFAULT 'DOCUMENT',
|
||
file_status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
|
||
graph_sync_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
||
vector_sync_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
||
graph_doc_ref VARCHAR(128),
|
||
vector_doc_ref VARCHAR(128),
|
||
content_checksum VARCHAR(128),
|
||
metadata_json JSONB NOT NULL DEFAULT '{}'::JSONB,
|
||
adcode VARCHAR(12),
|
||
tenant_id VARCHAR(64),
|
||
tenant_path VARCHAR(255),
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT chk_ai_knowledge_file_type
|
||
CHECK (file_type IN ('DOCUMENT', 'PDF', 'IMAGE', 'OTHER')),
|
||
CONSTRAINT chk_ai_knowledge_file_status
|
||
CHECK (file_status IN ('ACTIVE', 'DISABLED')),
|
||
CONSTRAINT chk_ai_knowledge_file_graph_status
|
||
CHECK (graph_sync_status IN ('PENDING', 'SYNCED', 'FAILED')),
|
||
CONSTRAINT chk_ai_knowledge_file_vector_status
|
||
CHECK (vector_sync_status IN ('PENDING', 'SYNCED', 'FAILED')),
|
||
CONSTRAINT chk_ai_knowledge_file_metadata_json
|
||
CHECK (jsonb_typeof(metadata_json) = 'object'),
|
||
CONSTRAINT fk_ai_knowledge_file_source_file
|
||
FOREIGN KEY (source_file_id) REFERENCES upms.tb_sys_file(file_id)
|
||
);
|
||
COMMENT ON TABLE ai.tb_ai_knowledge_file IS '知识文件主表（统一管理图谱和向量知识库入库对象）';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.file_id IS '知识文件ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.file_code IS '知识文件编码';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.file_name IS '知识文件名称';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.source_file_id IS '来源文件ID（upms.tb_sys_file.file_id）';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.file_type IS '文件类型';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.file_status IS '文件状态';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.graph_sync_status IS '图谱同步状态';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.vector_sync_status IS '向量库同步状态';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.graph_doc_ref IS '图谱侧文档/对象引用';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.vector_doc_ref IS '向量库侧文档/对象引用';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.content_checksum IS '内容校验值';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.metadata_json IS '文件元数据JSON';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.created_at IS '创建时间';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_file.updated_at IS '更新时间';
|
||
|
||
DROP TABLE IF EXISTS ai.tb_ai_knowledge_sync_task CASCADE;
|
||
CREATE TABLE IF NOT EXISTS ai.tb_ai_knowledge_sync_task (
|
||
task_id VARCHAR(64) PRIMARY KEY,
|
||
file_id VARCHAR(64) NOT NULL,
|
||
target_store VARCHAR(16) NOT NULL,
|
||
task_type VARCHAR(16) NOT NULL DEFAULT 'UPSERT',
|
||
task_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
||
retry_count INTEGER NOT NULL DEFAULT 0,
|
||
error_message TEXT,
|
||
payload_json JSONB NOT NULL DEFAULT '{}'::JSONB,
|
||
adcode VARCHAR(12),
|
||
tenant_id VARCHAR(64),
|
||
tenant_path VARCHAR(255),
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT chk_ai_knowledge_sync_task_store
|
||
CHECK (target_store IN ('GRAPH', 'VECTOR')),
|
||
CONSTRAINT chk_ai_knowledge_sync_task_type
|
||
CHECK (task_type IN ('UPSERT', 'DELETE', 'REBUILD')),
|
||
CONSTRAINT chk_ai_knowledge_sync_task_status
|
||
CHECK (task_status IN ('PENDING', 'RUNNING', 'SUCCESS', 'FAILED')),
|
||
CONSTRAINT chk_ai_knowledge_sync_task_retry
|
||
CHECK (retry_count >= 0),
|
||
CONSTRAINT chk_ai_knowledge_sync_task_payload_json
|
||
CHECK (jsonb_typeof(payload_json) = 'object'),
|
||
CONSTRAINT fk_ai_knowledge_sync_task_file
|
||
FOREIGN KEY (file_id) REFERENCES ai.tb_ai_knowledge_file(file_id) ON DELETE CASCADE
|
||
);
|
||
COMMENT ON TABLE ai.tb_ai_knowledge_sync_task IS '知识文件同步任务表（同步到图谱或向量知识库）';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.task_id IS '同步任务ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.file_id IS '知识文件ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.target_store IS '目标存储（GRAPH/VECTOR）';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.task_type IS '任务类型（UPSERT/DELETE/REBUILD）';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.task_status IS '任务状态';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.retry_count IS '重试次数';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.error_message IS '错误信息';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.payload_json IS '任务载荷JSON';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.created_at IS '创建时间';
|
||
COMMENT ON COLUMN ai.tb_ai_knowledge_sync_task.updated_at IS '更新时间';
|
||
|
||
DROP TABLE IF EXISTS ai.tb_ai_graph_entity CASCADE;
|
||
CREATE TABLE IF NOT EXISTS ai.tb_ai_graph_entity (
|
||
entity_id VARCHAR(64) PRIMARY KEY,
|
||
file_id VARCHAR(64),
|
||
entity_type VARCHAR(64) NOT NULL,
|
||
entity_name VARCHAR(255) NOT NULL,
|
||
normalized_name VARCHAR(255),
|
||
graph_vertex_id VARCHAR(128),
|
||
entity_status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
|
||
sync_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
||
properties_json JSONB NOT NULL DEFAULT '{}'::JSONB,
|
||
adcode VARCHAR(12),
|
||
tenant_id VARCHAR(64),
|
||
tenant_path VARCHAR(255),
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT uk_ai_graph_entity_file_name_type
|
||
UNIQUE (file_id, entity_name, entity_type),
|
||
CONSTRAINT chk_ai_graph_entity_status
|
||
CHECK (entity_status IN ('ACTIVE', 'DISABLED')),
|
||
CONSTRAINT chk_ai_graph_entity_sync_status
|
||
CHECK (sync_status IN ('PENDING', 'SYNCED', 'FAILED')),
|
||
CONSTRAINT chk_ai_graph_entity_properties_json
|
||
CHECK (jsonb_typeof(properties_json) = 'object'),
|
||
CONSTRAINT fk_ai_graph_entity_file
|
||
FOREIGN KEY (file_id) REFERENCES ai.tb_ai_knowledge_file(file_id) ON DELETE SET NULL
|
||
);
|
||
COMMENT ON TABLE ai.tb_ai_graph_entity IS '图谱实体表（用于实体绑定）';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.entity_id IS '实体ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.file_id IS '来源知识文件ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.entity_type IS '实体类型';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.entity_name IS '实体名称';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.normalized_name IS '标准化实体名称';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.graph_vertex_id IS '图数据库顶点ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.entity_status IS '实体状态';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.sync_status IS '同步状态';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.properties_json IS '实体属性JSON';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.created_at IS '创建时间';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_entity.updated_at IS '更新时间';
|
||
|
||
DROP TABLE IF EXISTS ai.tb_ai_graph_relation CASCADE;
|
||
CREATE TABLE IF NOT EXISTS ai.tb_ai_graph_relation (
|
||
relation_id VARCHAR(64) PRIMARY KEY,
|
||
src_entity_id VARCHAR(64) NOT NULL,
|
||
dst_entity_id VARCHAR(64) NOT NULL,
|
||
relation_type VARCHAR(64) NOT NULL,
|
||
relation_name VARCHAR(128),
|
||
graph_edge_id VARCHAR(128),
|
||
relation_weight NUMERIC(6,4) NOT NULL DEFAULT 1.0000,
|
||
relation_status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
|
||
sync_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
|
||
properties_json JSONB NOT NULL DEFAULT '{}'::JSONB,
|
||
adcode VARCHAR(12),
|
||
tenant_id VARCHAR(64),
|
||
tenant_path VARCHAR(255),
|
||
dept_id VARCHAR(64),
|
||
dept_path VARCHAR(255),
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT uk_ai_graph_relation_src_dst_type
|
||
UNIQUE (src_entity_id, dst_entity_id, relation_type),
|
||
CONSTRAINT chk_ai_graph_relation_src_dst
|
||
CHECK (src_entity_id <> dst_entity_id),
|
||
CONSTRAINT chk_ai_graph_relation_weight
|
||
CHECK (relation_weight > 0 AND relation_weight <= 1),
|
||
CONSTRAINT chk_ai_graph_relation_status
|
||
CHECK (relation_status IN ('ACTIVE', 'DISABLED')),
|
||
CONSTRAINT chk_ai_graph_relation_sync_status
|
||
CHECK (sync_status IN ('PENDING', 'SYNCED', 'FAILED')),
|
||
CONSTRAINT chk_ai_graph_relation_properties_json
|
||
CHECK (jsonb_typeof(properties_json) = 'object'),
|
||
CONSTRAINT fk_ai_graph_relation_src
|
||
FOREIGN KEY (src_entity_id) REFERENCES ai.tb_ai_graph_entity(entity_id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_ai_graph_relation_dst
|
||
FOREIGN KEY (dst_entity_id) REFERENCES ai.tb_ai_graph_entity(entity_id) ON DELETE CASCADE
|
||
);
|
||
COMMENT ON TABLE ai.tb_ai_graph_relation IS '图谱实体关联表（用于实体关系绑定）';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.relation_id IS '关系ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.src_entity_id IS '源实体ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.dst_entity_id IS '目标实体ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.relation_type IS '关系类型';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.relation_name IS '关系名称';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.graph_edge_id IS '图数据库边ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.relation_weight IS '关系权重';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.relation_status IS '关系状态';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.sync_status IS '同步状态';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.properties_json IS '关系属性JSON';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.adcode IS '行政区划编码';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.tenant_id IS '租户ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.tenant_path IS '租户路径';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.dept_id IS '部门ID';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.dept_path IS '部门路径';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.created_at IS '创建时间';
|
||
COMMENT ON COLUMN ai.tb_ai_graph_relation.updated_at IS '更新时间';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_ai_knowledge_file_sync_status
|
||
ON ai.tb_ai_knowledge_file(graph_sync_status, vector_sync_status, updated_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_ai_knowledge_sync_task_status
|
||
ON ai.tb_ai_knowledge_sync_task(task_status, target_store, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_ai_graph_entity_name
|
||
ON ai.tb_ai_graph_entity(entity_type, entity_name);
|
||
CREATE INDEX IF NOT EXISTS idx_ai_graph_relation_src_dst
|
||
ON ai.tb_ai_graph_relation(src_entity_id, dst_entity_id, relation_type);
|