lingyue-zhibao/backend/sql/graph_tables.sql

-- 灵越智报 v2.0 图数据库表结构
-- PostgreSQL 15+
-- 用于 NER 实体识别结果存储

-- ============================================
-- 1. 图节点表（graph_nodes）
-- ============================================
-- 存储从文档中提取的命名实体
CREATE TABLE IF NOT EXISTS graph_nodes (
    id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36),  -- 可为空，自动提取时可能没有用户上下文
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- text/table/image/number/date/ORG/PERSON/LOC/TIME/DEVICE/PROJECT/METHOD等
    value TEXT,
    position JSONB, -- {charStart, charEnd, line}
    parent_id VARCHAR(36),
    level INTEGER DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 添加外键约束（如果关联表存在）
-- 注意：user_id 不添加外键约束，因为自动 NER 提取时可能没有用户上下文
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'documents') THEN
        ALTER TABLE graph_nodes DROP CONSTRAINT IF EXISTS fk_graph_nodes_document;
        ALTER TABLE graph_nodes ADD CONSTRAINT fk_graph_nodes_document
            FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE;
    END IF;
    
    -- user_id 外键约束已移除，允许任意值或 NULL
    -- 如需恢复，取消下面注释：
    -- IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'users') THEN
    --     ALTER TABLE graph_nodes DROP CONSTRAINT IF EXISTS fk_graph_nodes_user;
    --     ALTER TABLE graph_nodes ADD CONSTRAINT fk_graph_nodes_user
    --         FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;
    -- END IF;
END $$;

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 VARCHAR(36) PRIMARY KEY,
    from_node_id VARCHAR(36) NOT NULL,
    to_node_id VARCHAR(36) NOT NULL,
    relation_type VARCHAR(50) NOT NULL, -- BELONGS_TO/USES/LOCATED_IN/EXECUTES/MONITORS等
    action_type VARCHAR(50),
    action_config JSONB,
    order_index INTEGER DEFAULT 0,
    condition_expr TEXT,
    metadata JSONB DEFAULT '{}',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_graph_relations_from_node FOREIGN KEY (from_node_id) REFERENCES graph_nodes(id) ON DELETE CASCADE,
    CONSTRAINT fk_graph_relations_to_node FOREIGN KEY (to_node_id) REFERENCES graph_nodes(id) ON DELETE CASCADE
);

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);

-- ============================================
-- 创建更新时间触发器函数（如果不存在）
-- ============================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
DROP TRIGGER IF EXISTS update_graph_nodes_updated_at ON graph_nodes;
CREATE TRIGGER update_graph_nodes_updated_at 
    BEFORE UPDATE ON graph_nodes
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

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

-- ============================================
-- 说明
-- ============================================
-- 执行此脚本:
-- psql -U lingyue -d lingyue_zhibao -f graph_tables.sql
--
-- 或在服务器上:
-- psql -U postgres -d lingyue_zhibao -f graph_tables.sql