lingyue-zhibao/backend/sql/supplement_tables.sql

-- 灵越智报 v2.0 补充表结构
-- PostgreSQL 15+
-- 根据产品设计方案补充的表结构

-- ============================================
-- 1. 图节点表（graph_nodes）
-- ============================================
CREATE TABLE IF NOT EXISTS graph_nodes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- text/table/image/number/date/company/person等
    value TEXT,
    position JSONB, -- {file_id, page, line, char_start, char_end}
    parent_id UUID REFERENCES graph_nodes(id) ON DELETE SET NULL,
    level INTEGER DEFAULT 0, -- 层级
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_graph_nodes_document_id ON graph_nodes(document_id);
CREATE INDEX IF NOT EXISTS idx_graph_nodes_user_id ON graph_nodes(user_id);
CREATE INDEX IF NOT EXISTS idx_graph_nodes_type ON graph_nodes(type);
CREATE INDEX IF NOT EXISTS idx_graph_nodes_parent_id ON graph_nodes(parent_id);
CREATE INDEX IF NOT EXISTS idx_graph_nodes_position ON graph_nodes USING GIN(position);

-- ============================================
-- 2. 图关系表（graph_relations）
-- ============================================
CREATE TABLE IF NOT EXISTS graph_relations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_node_id UUID NOT NULL REFERENCES graph_nodes(id) ON DELETE CASCADE,
    to_node_id UUID NOT NULL REFERENCES graph_nodes(id) ON DELETE CASCADE,
    relation_type VARCHAR(50) NOT NULL, -- DEP/ADD/SUB/MUL/DIV/UNION/INTERSECT/AI等
    action_type VARCHAR(50), -- 动作类型
    action_config JSONB, -- 动作配置（如AI模型ID）
    order_index INTEGER DEFAULT 0, -- 顺序
    condition_expr TEXT, -- 条件表达式
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_graph_relations_from_node ON graph_relations(from_node_id);
CREATE INDEX IF NOT EXISTS idx_graph_relations_to_node ON graph_relations(to_node_id);
CREATE INDEX IF NOT EXISTS idx_graph_relations_type ON graph_relations(relation_type);

-- ============================================
-- 3. 规则表（rules）
-- ============================================
CREATE TABLE IF NOT EXISTS rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    entry_node_id UUID NOT NULL REFERENCES graph_nodes(id) ON DELETE CASCADE,
    exit_node_id UUID NOT NULL REFERENCES graph_nodes(id) ON DELETE CASCADE,
    rule_chain JSONB NOT NULL DEFAULT '[]', -- 规则链（节点ID序列）
    status VARCHAR(20) DEFAULT 'active', -- active/inactive
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at 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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    document_id UUID 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 UUID[] DEFAULT '{}', -- 关联的节点ID数组
    config JSONB DEFAULT '{}', -- 数据源配置
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at 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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    content TEXT NOT NULL, -- 模板内容（带占位符）
    placeholder_mapping JSONB DEFAULT '{}', -- 占位符到数据源的映射
    source_template_id UUID REFERENCES templates(id) ON DELETE SET NULL, -- 复制来源
    status VARCHAR(20) DEFAULT 'active', -- active/inactive
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at 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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    file_path VARCHAR(500) NOT NULL, -- TXT文件路径
    file_size BIGINT,
    checksum VARCHAR(64), -- 文件校验和
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at 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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID 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 '{}',
    created_at 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);

-- ============================================
-- 创建更新时间触发器
-- ============================================
CREATE TRIGGER update_graph_nodes_updated_at BEFORE UPDATE ON graph_nodes
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_graph_relations_updated_at BEFORE UPDATE ON graph_relations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_rules_updated_at BEFORE UPDATE ON rules
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_data_sources_updated_at BEFORE UPDATE ON data_sources
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_templates_updated_at BEFORE UPDATE ON templates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_text_storage_updated_at BEFORE UPDATE ON text_storage
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

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