lingyue-zhibao/backend/schema.sql

-- Lingyue Zhibao Database Schema
-- PostgreSQL

-- =============================================
-- Graph Core Tables
-- =============================================

-- Node Types
CREATE TABLE IF NOT EXISTS node_types (
    id SERIAL PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Edge Types
CREATE TABLE IF NOT EXISTS edge_types (
    id SERIAL PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100) NOT NULL,
    from_node_type VARCHAR(50),
    to_node_type VARCHAR(50),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Nodes
CREATE TABLE IF NOT EXISTS nodes (
    id BIGSERIAL PRIMARY KEY,
    node_type VARCHAR(50) NOT NULL,
    node_key VARCHAR(255),
    name VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_nodes_type ON nodes(node_type);
CREATE INDEX idx_nodes_key ON nodes(node_key);

-- Edges
CREATE TABLE IF NOT EXISTS edges (
    id BIGSERIAL PRIMARY KEY,
    edge_type VARCHAR(50) NOT NULL,
    from_node_id BIGINT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    to_node_id BIGINT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_edges_from ON edges(from_node_id);
CREATE INDEX idx_edges_to ON edges(to_node_id);
CREATE INDEX idx_edges_type ON edges(edge_type);

-- Node Properties
CREATE TABLE IF NOT EXISTS node_properties (
    id BIGSERIAL PRIMARY KEY,
    node_id BIGINT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
    prop_key VARCHAR(100) NOT NULL,
    prop_value TEXT,
    prop_json JSONB,
    prop_number NUMERIC(20, 6),
    prop_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(node_id, prop_key)
);

CREATE INDEX idx_node_props_node ON node_properties(node_id);
CREATE INDEX idx_node_props_key ON node_properties(prop_key);

-- Edge Properties
CREATE TABLE IF NOT EXISTS edge_properties (
    id BIGSERIAL PRIMARY KEY,
    edge_id BIGINT NOT NULL REFERENCES edges(id) ON DELETE CASCADE,
    prop_key VARCHAR(100) NOT NULL,
    prop_value TEXT,
    prop_json JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(edge_id, prop_key)
);

CREATE INDEX idx_edge_props_edge ON edge_properties(edge_id);

-- =============================================
-- Auth Tables
-- =============================================

-- Users
CREATE TABLE IF NOT EXISTS sys_users (
    id BIGSERIAL PRIMARY KEY,
    node_id BIGINT REFERENCES nodes(id),
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    salt VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    avatar VARCHAR(255),
    real_name VARCHAR(50),
    department VARCHAR(100),
    position VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active',
    last_login_at TIMESTAMP,
    last_login_ip VARCHAR(50),
    login_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Roles
CREATE TABLE IF NOT EXISTS sys_roles (
    id SERIAL PRIMARY KEY,
    role_code VARCHAR(50) NOT NULL UNIQUE,
    role_name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Permissions
CREATE TABLE IF NOT EXISTS sys_permissions (
    id SERIAL PRIMARY KEY,
    perm_code VARCHAR(100) NOT NULL UNIQUE,
    perm_name VARCHAR(100) NOT NULL,
    perm_type VARCHAR(20),
    parent_id INTEGER,
    path VARCHAR(255),
    icon VARCHAR(50),
    component VARCHAR(255),
    sort_order INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User Roles
CREATE TABLE IF NOT EXISTS sys_user_roles (
    id SERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES sys_users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES sys_roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, role_id)
);

-- Role Permissions
CREATE TABLE IF NOT EXISTS sys_role_permissions (
    id SERIAL PRIMARY KEY,
    role_id INTEGER NOT NULL REFERENCES sys_roles(id) ON DELETE CASCADE,
    permission_id INTEGER NOT NULL REFERENCES sys_permissions(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(role_id, permission_id)
);

-- Sessions
CREATE TABLE IF NOT EXISTS sys_sessions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES sys_users(id) ON DELETE CASCADE,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    refresh_token VARCHAR(255),
    device_type VARCHAR(50),
    device_info VARCHAR(255),
    ip_address VARCHAR(50),
    user_agent TEXT,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_sessions_user ON sys_sessions(user_id);
CREATE INDEX idx_sessions_token ON sys_sessions(session_token);

-- Config
CREATE TABLE IF NOT EXISTS sys_configs (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    config_type VARCHAR(20),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Dict Types
CREATE TABLE IF NOT EXISTS sys_dict_types (
    id SERIAL PRIMARY KEY,
    dict_code VARCHAR(50) NOT NULL UNIQUE,
    dict_name VARCHAR(100) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Dict Items
CREATE TABLE IF NOT EXISTS sys_dict_items (
    id SERIAL PRIMARY KEY,
    dict_type_id INTEGER NOT NULL REFERENCES sys_dict_types(id) ON DELETE CASCADE,
    item_value VARCHAR(100) NOT NULL,
    item_label VARCHAR(100) NOT NULL,
    sort_order INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Login Logs
CREATE TABLE IF NOT EXISTS sys_login_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(50),
    login_type VARCHAR(20),
    ip_address VARCHAR(50),
    user_agent TEXT,
    status VARCHAR(20),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Operation Logs
CREATE TABLE IF NOT EXISTS sys_operation_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(50),
    operation VARCHAR(100),
    method VARCHAR(200),
    params TEXT,
    result TEXT,
    ip_address VARCHAR(50),
    duration INTEGER,
    status VARCHAR(20),
    error_msg TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =============================================
-- File Tables
-- =============================================

CREATE TABLE IF NOT EXISTS sys_files (
    id BIGSERIAL PRIMARY KEY,
    file_key VARCHAR(100) NOT NULL UNIQUE,
    original_name VARCHAR(255) NOT NULL,
    storage_name VARCHAR(255),
    storage_path VARCHAR(500),
    file_type VARCHAR(50),
    file_size BIGINT,
    md5_hash VARCHAR(32),
    storage_type VARCHAR(20) DEFAULT 'local',
    bucket VARCHAR(100),
    url VARCHAR(500),
    thumbnail_url VARCHAR(500),
    uploaded_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_files_key ON sys_files(file_key);

-- =============================================
-- Task Tables
-- =============================================

CREATE TABLE IF NOT EXISTS sys_tasks (
    id BIGSERIAL PRIMARY KEY,
    task_type VARCHAR(50) NOT NULL,
    task_key VARCHAR(100),
    payload TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    priority INTEGER DEFAULT 0,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    result TEXT,
    error_msg TEXT,
    started_at TIMESTAMP,
    finished_at TIMESTAMP,
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_tasks_status ON sys_tasks(status);
CREATE INDEX idx_tasks_type ON sys_tasks(task_type);

-- =============================================
-- Initial Data
-- =============================================

-- Node Types
INSERT INTO node_types (type_code, type_name, description) VALUES
('project', '项目', '评审项目'),
('element', '要素', '报告要素'),
('value', '值', '要素值'),
('attachment', '附件', '项目附件'),
('rule', '规则', '自动化规则'),
('user', '用户', '系统用户')
ON CONFLICT (type_code) DO NOTHING;

-- Edge Types
INSERT INTO edge_types (type_code, type_name, from_node_type, to_node_type) VALUES
('has_element', '包含要素', 'project', 'element'),
('has_value', '包含值', 'project', 'value'),
('has_attachment', '包含附件', 'project', 'attachment'),
('has_rule', '包含规则', 'project', 'rule'),
('element_value', '要素值', 'element', 'value'),
('rule_input', '规则输入', 'rule', 'attachment'),
('rule_output', '规则输出', 'rule', 'element')
ON CONFLICT (type_code) DO NOTHING;

-- Default Admin User (password: admin123)
INSERT INTO sys_users (username, password_hash, salt, real_name, status) VALUES
('admin', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', '', '管理员', 'active')
ON CONFLICT (username) DO NOTHING;

-- Default Roles
INSERT INTO sys_roles (role_code, role_name, description) VALUES
('admin', '管理员', '系统管理员'),
('user', '普通用户', '普通用户')
ON CONFLICT (role_code) DO NOTHING;