lingyue-zhibao/backend/sql/supplement_tables.sql

-- 灵越智报 v2.0 补充表结构
-- PostgreSQL 15+
-- 根据产品设计方案补充的表结构
-- 注意: ID 使用 VARCHAR(36) 以兼容 MyBatis-Plus 的 ASSIGN_UUID 策略
-- 注意: graph_nodes 和 graph_relations 表已在 graph_tables.sql 中定义，此处不再重复

-- ============================================
-- 1. 规则表（rules）
-- ============================================
CREATE TABLE IF NOT EXISTS rules (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    entry_node_id VARCHAR(36) REFERENCES graph_nodes(id) ON DELETE SET NULL,
    exit_node_id VARCHAR(36) REFERENCES graph_nodes(id) ON DELETE SET NULL,
    rule_chain JSONB NOT NULL DEFAULT '[]', -- 规则链（节点ID序列）
    status VARCHAR(20) DEFAULT 'active', -- active/inactive
    metadata JSONB DEFAULT '{}',
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_rules_user_id ON rules(user_id);
CREATE INDEX IF NOT EXISTS idx_rules_entry_node ON rules(entry_node_id);
CREATE INDEX IF NOT EXISTS idx_rules_exit_node ON rules(exit_node_id);
CREATE INDEX IF NOT EXISTS idx_rules_status ON rules(status);

-- ============================================
-- 4. 数据源表（data_sources）
-- ============================================
CREATE TABLE IF NOT EXISTS data_sources (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    document_id VARCHAR(36) REFERENCES documents(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- table/text/image
    source_type VARCHAR(50) NOT NULL, -- file/manual/rule_result
    node_ids TEXT[] DEFAULT '{}', -- 关联的节点ID数组（VARCHAR数组）
    config JSONB DEFAULT '{}', -- 数据源配置
    metadata JSONB DEFAULT '{}',
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_data_sources_user_id ON data_sources(user_id);
CREATE INDEX IF NOT EXISTS idx_data_sources_document_id ON data_sources(document_id);
CREATE INDEX IF NOT EXISTS idx_data_sources_type ON data_sources(type);

-- ============================================
-- 5. 模板表（templates）
-- ============================================
CREATE TABLE IF NOT EXISTS templates (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    content TEXT NOT NULL, -- 模板内容（带占位符）
    placeholder_mapping JSONB DEFAULT '{}', -- 占位符到数据源的映射
    source_template_id VARCHAR(36) REFERENCES templates(id) ON DELETE SET NULL, -- 复制来源
    status VARCHAR(20) DEFAULT 'active', -- active/inactive
    metadata JSONB DEFAULT '{}',
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_templates_user_id ON templates(user_id);
CREATE INDEX IF NOT EXISTS idx_templates_source_template ON templates(source_template_id);
CREATE INDEX IF NOT EXISTS idx_templates_status ON templates(status);

-- ============================================
-- 6. 文本存储路径表（text_storage）
-- ============================================
CREATE TABLE IF NOT EXISTS text_storage (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    file_path VARCHAR(500) NOT NULL, -- TXT文件路径
    file_size BIGINT,
    checksum VARCHAR(64), -- 文件校验和
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_text_storage_document_id ON text_storage(document_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_text_storage_document_unique ON text_storage(document_id);

-- ============================================
-- 7. 向量存储表（vector_embeddings）
-- ============================================
-- 注意：需要先安装pgvector扩展
-- CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS vector_embeddings (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    chunk_text TEXT NOT NULL,
    chunk_index INTEGER NOT NULL, -- 分块索引
    embedding vector(768), -- 向量（需要pgvector扩展，如果未安装可先注释）
    file_name VARCHAR(255),
    file_hash VARCHAR(64), -- MD5哈希
    metadata JSONB DEFAULT '{}',
    create_by VARCHAR(36),
    create_by_name VARCHAR(100),
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(36),
    update_by_name VARCHAR(100),
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_vector_embeddings_document_id ON vector_embeddings(document_id);
CREATE INDEX IF NOT EXISTS idx_vector_embeddings_file_hash ON vector_embeddings(file_hash);
-- 向量相似度搜索索引（需要pgvector扩展）
-- CREATE INDEX IF NOT EXISTS idx_vector_embeddings_embedding ON vector_embeddings USING ivfflat (embedding vector_cosine_ops);

-- ============================================
-- 创建更新时间触发器
-- ============================================
-- 注意: graph_nodes 和 graph_relations 的触发器已在 graph_tables.sql 中定义

DROP TRIGGER IF EXISTS update_rules_update_time ON rules;
CREATE TRIGGER update_rules_update_time BEFORE UPDATE ON rules
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_data_sources_update_time ON data_sources;
CREATE TRIGGER update_data_sources_update_time BEFORE UPDATE ON data_sources
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_templates_update_time ON templates;
CREATE TRIGGER update_templates_update_time BEFORE UPDATE ON templates
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_text_storage_update_time ON text_storage;
CREATE TRIGGER update_text_storage_update_time BEFORE UPDATE ON text_storage
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

DROP TRIGGER IF EXISTS update_vector_embeddings_update_time ON vector_embeddings;
CREATE TRIGGER update_vector_embeddings_update_time BEFORE UPDATE ON vector_embeddings
    FOR EACH ROW EXECUTE FUNCTION update_update_time_column();

-- ============================================
-- 安装pgvector扩展（可选，用于向量检索）
-- ============================================
-- 如果需要使用pgvector，请执行：
-- CREATE EXTENSION IF NOT EXISTS vector;
-- 
-- 安装方法：
-- 1. 下载pgvector: https://github.com/pgvector/pgvector
-- 2. 编译安装
-- 3. 在数据库中执行: CREATE EXTENSION vector;