CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
slug VARCHAR(100) UNIQUE NOT NULL, -- 唯一标识
plan VARCHAR(50) DEFAULT 'free', -- free | pro | enterprise
settings JSONB DEFAULT '{}', -- 租户配置
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_tenants_slug ON tenants(slug) WHERE deleted_at IS NULL;
2.2 users（用户表）
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
email VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
display_name VARCHAR(100) NOT NULL,
avatar_url VARCHAR(500),
role VARCHAR(50) DEFAULT 'user', -- user | admin | super_admin
settings JSONB DEFAULT '{}',
last_login_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE,
UNIQUE(tenant_id, email)
);
CREATE INDEX idx_users_tenant ON users(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_email ON users(tenant_id, email) WHERE deleted_at IS NULL;
3. 工作区与项目模型
3.1 workspaces（工作区表）
CREATE TABLE workspaces (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
owner_id UUID NOT NULL REFERENCES users(id),
name VARCHAR(255) NOT NULL,
description TEXT,
visibility VARCHAR(20) DEFAULT 'private', -- private | team | public
settings JSONB DEFAULT '{}',
storage_used BIGINT DEFAULT 0, -- 已用存储（字节）
project_count INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_workspaces_tenant ON workspaces(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_workspaces_owner ON workspaces(owner_id) WHERE deleted_at IS NULL;
3.2 workspace_members（工作区成员表）
CREATE TABLE workspace_members (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
workspace_id UUID NOT NULL REFERENCES workspaces(id),
user_id UUID NOT NULL REFERENCES users(id),
role VARCHAR(20) DEFAULT 'member', -- owner | admin | member | viewer
joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(workspace_id, user_id)
);
CREATE INDEX idx_ws_members_workspace ON workspace_members(workspace_id);
CREATE INDEX idx_ws_members_user ON workspace_members(user_id);
3.3 projects（项目表）
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
workspace_id UUID NOT NULL REFERENCES workspaces(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
name VARCHAR(255) NOT NULL,
description TEXT,
cover_image_url VARCHAR(500),
source_type VARCHAR(20), -- manual | imported | copied
source_project_id UUID, -- 如果是从其他项目复制的
visibility VARCHAR(20) DEFAULT 'private', -- private | shared | public
share_token VARCHAR(100) UNIQUE, -- 分享链接 token
current_version INTEGER DEFAULT 1,
node_count INTEGER DEFAULT 0, -- 知识节点数（缓存）
status VARCHAR(20) DEFAULT 'active', -- active | archived | deleted
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_projects_workspace ON projects(workspace_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_projects_tenant ON projects(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_projects_share_token ON projects(share_token) WHERE share_token IS NOT NULL;
4. 资料与文档模型
4.1 documents（文档/资料表）
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
doc_type VARCHAR(20) NOT NULL, -- pdf | webpage | github | markdown | video
title VARCHAR(500) NOT NULL,
description TEXT,
source_url VARCHAR(2000), -- 原始 URL（网页/GitHub/视频）
file_path VARCHAR(1000), -- 对象存储路径
file_size BIGINT, -- 文件大小（字节）
file_hash VARCHAR(64), -- 文件 SHA-256，用于去重
page_count INTEGER, -- PDF 页数
status VARCHAR(20) DEFAULT 'pending', -- pending | processing | completed | failed
error_message TEXT,
processed_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_documents_project ON documents(project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_documents_file_hash ON documents(file_hash) WHERE file_hash IS NOT NULL;
CREATE TABLE pipelines (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
trigger_type VARCHAR(30) NOT NULL, -- document_upload | document_delete | manual_retry
status VARCHAR(20) DEFAULT 'pending', -- pending | processing | completed | failed
progress INTEGER DEFAULT 0, -- 0~100
current_stage VARCHAR(100), -- 当前阶段名称
error_message TEXT,
started_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_pipelines_project ON pipelines(project_id);
CREATE INDEX idx_pipelines_status ON pipelines(status);
6.2 pipeline_stages（流水线阶段表）
CREATE TABLE pipeline_stages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
pipeline_id UUID NOT NULL REFERENCES pipelines(id),
stage_name VARCHAR(100) NOT NULL, -- 如: document-parse
status VARCHAR(20) DEFAULT 'pending', -- pending | processing | completed | failed | skipped
sequence INTEGER NOT NULL, -- 阶段顺序
progress INTEGER DEFAULT 0, -- 0~100
started_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE,
retry_count INTEGER DEFAULT 0,
max_retries INTEGER DEFAULT 3,
error_message TEXT,
output_summary TEXT, -- 阶段输出摘要（如：发现 234 个知识节点）
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(pipeline_id, stage_name)
);
CREATE INDEX idx_pipeline_stages_pipeline ON pipeline_stages(pipeline_id);
7. 知识图谱模型
7.1 global_knowledge_nodes（公共知识节点）
CREATE TABLE global_knowledge_nodes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL, -- 节点名称（如 "EventLoop"）
label VARCHAR(100), -- 标签/分类
description TEXT,
aliases TEXT[], -- 别名（如 ["事件循环", "事件轮询"]）
category VARCHAR(50), -- topic | concept | technology | framework
vector_embedding vector(1536), -- 语义向量（pgvector）
metadata JSONB DEFAULT '{}', -- 扩展元数据
source VARCHAR(50) DEFAULT 'system', -- system | user_contributed | curated
confidence REAL DEFAULT 1.0, -- 置信度
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_global_nodes_name ON global_knowledge_nodes(name) WHERE deleted_at IS NULL;
CREATE INDEX idx_global_nodes_category ON global_knowledge_nodes(category) WHERE deleted_at IS NULL;
-- pgvector 索引（根据具体维度调整）
CREATE INDEX idx_global_nodes_vector ON global_knowledge_nodes USING ivfflat (vector_embedding vector_cosine_ops) WITH (lists = 100);
7.2 global_knowledge_relations（公共知识关系）
CREATE TABLE global_knowledge_relations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
source_node_id UUID NOT NULL REFERENCES global_knowledge_nodes(id),
target_node_id UUID NOT NULL REFERENCES global_knowledge_nodes(id),
relation_type VARCHAR(50) NOT NULL, -- prerequisite | subsequent | contains | relates_to | contrasts_with
weight REAL DEFAULT 1.0, -- 关系强度
description TEXT,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE,
UNIQUE(source_node_id, target_node_id, relation_type)
);
CREATE INDEX idx_global_relations_source ON global_knowledge_relations(source_node_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_global_relations_target ON global_knowledge_relations(target_node_id) WHERE deleted_at IS NULL;
7.3 project_knowledge_nodes（项目知识节点）
CREATE TABLE project_knowledge_nodes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
name VARCHAR(255) NOT NULL,
label VARCHAR(100),
description TEXT,
category VARCHAR(50), -- topic | concept | technology | code | function
global_node_id UUID REFERENCES global_knowledge_nodes(id), -- 关联的公共节点
source_document_id UUID REFERENCES documents(id), -- 来源文档
source_location TEXT, -- 在文档中的位置（页码/段落）
vector_embedding vector(1536),
confidence REAL DEFAULT 0.0, -- AI 抽取置信度
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_project_nodes_project ON project_knowledge_nodes(project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_project_nodes_global ON project_knowledge_nodes(global_node_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_project_nodes_vector ON project_knowledge_nodes USING ivfflat (vector_embedding vector_cosine_ops) WITH (lists = 100);
7.4 project_knowledge_relations（项目知识关系）
CREATE TABLE project_knowledge_relations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
source_node_id UUID NOT NULL REFERENCES project_knowledge_nodes(id),
target_node_id UUID NOT NULL REFERENCES project_knowledge_nodes(id),
relation_type VARCHAR(50) NOT NULL, -- prerequisite | subsequent | contains | relates_to | contrasts_with | references
weight REAL DEFAULT 1.0,
description TEXT,
confidence REAL DEFAULT 0.0,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE,
UNIQUE(project_id, source_node_id, target_node_id, relation_type)
);
CREATE INDEX idx_project_relations_project ON project_knowledge_relations(project_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_project_relations_source ON project_knowledge_relations(source_node_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_project_relations_target ON project_knowledge_relations(target_node_id) WHERE deleted_at IS NULL;
7.5 user_knowledge_states（用户知识状态 — 三层 L3）
CREATE TABLE user_knowledge_states (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
node_type VARCHAR(10) NOT NULL, -- 'project' | 'global'
project_node_id UUID REFERENCES project_knowledge_nodes(id),
global_node_id UUID REFERENCES global_knowledge_nodes(id),
mastery_level INTEGER DEFAULT 0, -- 掌握程度 0~100
confidence REAL DEFAULT 0.0, -- 置信度 0~1
review_count INTEGER DEFAULT 0, -- 复习次数
last_reviewed_at TIMESTAMP WITH TIME ZONE,
first_learned_at TIMESTAMP WITH TIME ZONE,
is_in_learning_path BOOLEAN DEFAULT false,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
-- 约束：必须有且仅有一个 node 引用
CHECK (
(node_type = 'project' AND project_node_id IS NOT NULL AND global_node_id IS NULL) OR
(node_type = 'global' AND global_node_id IS NOT NULL AND project_node_id IS NULL)
),
UNIQUE(user_id, project_node_id),
UNIQUE(user_id, global_node_id)
);
CREATE INDEX idx_user_knowledge_user ON user_knowledge_states(user_id);
CREATE INDEX idx_user_knowledge_project ON user_knowledge_states(project_node_id);
CREATE INDEX idx_user_knowledge_global ON user_knowledge_states(global_node_id);
8. 学习旅程模型
8.1 learning_paths（学习路线表）
CREATE TABLE learning_paths (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
user_id UUID NOT NULL REFERENCES users(id),
version_id UUID REFERENCES project_versions(id),
title VARCHAR(255),
description TEXT,
total_nodes INTEGER DEFAULT 0,
completed_nodes INTEGER DEFAULT 0,
estimated_days INTEGER,
is_active BOOLEAN DEFAULT true,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX idx_learning_paths_project ON learning_paths(project_id);
CREATE INDEX idx_learning_paths_user ON learning_paths(user_id);
8.2 learning_path_nodes（学习路线节点排序）
CREATE TABLE learning_path_nodes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
learning_path_id UUID NOT NULL REFERENCES learning_paths(id),
node_type VARCHAR(10) NOT NULL, -- 'project' | 'global'
project_node_id UUID REFERENCES project_knowledge_nodes(id),
global_node_id UUID REFERENCES global_knowledge_nodes(id),
sequence_order INTEGER NOT NULL, -- 学习顺序
estimated_minutes INTEGER, -- 预计学习时长
status VARCHAR(20) DEFAULT 'pending', -- pending | in_progress | completed | skipped
completed_at TIMESTAMP WITH TIME ZONE,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(learning_path_id, sequence_order)
);
CREATE INDEX idx_path_nodes_path ON learning_path_nodes(learning_path_id);
8.3 assessments（能力评估表）
CREATE TABLE assessments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
user_id UUID NOT NULL REFERENCES users(id),
title VARCHAR(255),
status VARCHAR(20) DEFAULT 'pending', -- pending | in_progress | completed
total_questions INTEGER DEFAULT 0,
answered_count INTEGER DEFAULT 0,
correct_count INTEGER DEFAULT 0,
score REAL, -- 0~100
started_at TIMESTAMP WITH TIME ZONE,
completed_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_assessments_project ON assessments(project_id);
CREATE INDEX idx_assessments_user ON assessments(user_id);
8.4 assessment_questions（评估题目表）
CREATE TABLE assessment_questions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
assessment_id UUID NOT NULL REFERENCES assessments(id),
node_type VARCHAR(10) NOT NULL, -- 'project' | 'global'
project_node_id UUID REFERENCES project_knowledge_nodes(id),
global_node_id UUID REFERENCES global_knowledge_nodes(id),
question_type VARCHAR(20) NOT NULL, -- multiple_choice | fill_blank | short_answer | true_false
question_text TEXT NOT NULL,
options JSONB, -- 选择题选项
correct_answer TEXT NOT NULL, -- 正确答案
user_answer TEXT,
is_correct BOOLEAN,
difficulty REAL DEFAULT 0.5, -- 难度 0~1
sequence_order INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_assessment_questions_assessment ON assessment_questions(assessment_id);
8.5 learning_records（学习记录表）
CREATE TABLE learning_records (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
project_id UUID NOT NULL REFERENCES projects(id),
record_type VARCHAR(20) NOT NULL, -- study | quiz | review | assessment
node_type VARCHAR(10), -- 'project' | 'global'
project_node_id UUID REFERENCES project_knowledge_nodes(id),
global_node_id UUID REFERENCES global_knowledge_nodes(id),
duration_seconds INTEGER, -- 学习时长
detail JSONB DEFAULT '{}', -- 记录详情
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_learning_records_user ON learning_records(user_id);
CREATE INDEX idx_learning_records_project ON learning_records(project_id);
CREATE INDEX idx_learning_records_user_project ON learning_records(user_id, project_id);
-- 按时间分表（按月/季度），适合记录型数据
9. 共享与协作模型
9.1 project_shares（项目分享表）
CREATE TABLE project_shares (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id),
shared_by UUID NOT NULL REFERENCES users(id),
share_token VARCHAR(100) UNIQUE NOT NULL,
permission VARCHAR(20) DEFAULT 'view', -- view | copy | edit
expires_at TIMESTAMP WITH TIME ZONE,
access_count INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_project_shares_token ON project_shares(share_token);
CREATE INDEX idx_project_shares_project ON project_shares(project_id);
9.2 project_copies（项目复制记录表）
CREATE TABLE project_copies (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
source_project_id UUID NOT NULL REFERENCES projects(id),
target_project_id UUID NOT NULL REFERENCES projects(id),
copied_by UUID NOT NULL REFERENCES users(id),
include_versions BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_project_copies_source ON project_copies(source_project_id);
CREATE INDEX idx_project_copies_target ON project_copies(target_project_id);
CREATE INDEX idx_project_copies_user ON project_copies(copied_by);
10. 事件存储（可选：事件溯源）
CREATE TABLE domain_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_type VARCHAR(100) NOT NULL,
aggregate_type VARCHAR(50) NOT NULL, -- project | pipeline | document | ...
aggregate_id UUID NOT NULL,
tenant_id UUID NOT NULL REFERENCES tenants(id),
data JSONB NOT NULL,
metadata JSONB DEFAULT '{}',
occurred_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_domain_events_type ON domain_events(event_type);
CREATE INDEX idx_domain_events_aggregate ON domain_events(aggregate_type, aggregate_id);
CREATE INDEX idx_domain_events_tenant ON domain_events(tenant_id);
CREATE INDEX idx_domain_events_occurred ON domain_events(occurred_at);
-- 按时间分区（按月）