Skip to content
AgentSurvey

数据模型设计

数据模型设计

实体关系概览

users
workspaces
workspace_members
projects
studies
study_versions
knowledge_bases
knowledge_documents
study_kb_links
agent_configs
agent_config_versions
tool_definitions
study_enabled_tools
output_schemas
output_schema_versions
participants
sessions
transcript_turns
tool_calls
interaction_results
extracted_fields
evidence_refs
study_insights
exports
audit_logs

Core Tables

users

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  password_hash TEXT,            -- 本地认证;或留空、由外部 auth provider 托管(见 02_product/prd/01_auth_workspace.md)
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

workspaces

CREATE TABLE workspaces (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

workspace_members

CREATE TABLE workspace_members (
  id UUID PRIMARY KEY,
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  user_id UUID NOT NULL REFERENCES users(id),
  role TEXT NOT NULL DEFAULT 'researcher',   -- admin / researcher / viewer
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, user_id)
);

projects

CREATE TABLE projects (
  id UUID PRIMARY KEY,
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  name TEXT NOT NULL,
  description TEXT,
  research_context TEXT,
  default_language TEXT DEFAULT 'zh-CN',
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

studies

CREATE TABLE studies (
  id UUID PRIMARY KEY,
  project_id UUID NOT NULL REFERENCES projects(id),
  name TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'draft',
  active_version_id UUID,
  public_slug TEXT UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

study_versions

CREATE TABLE study_versions (
  id UUID PRIMARY KEY,
  study_id UUID NOT NULL REFERENCES studies(id),
  version_number INTEGER NOT NULL,
  research_brief JSONB NOT NULL,
  objectives JSONB NOT NULL,
  interview_guide JSONB,
  agent_config_version_id UUID,
  output_schema_version_id UUID,
  enabled_tool_ids JSONB,
  kb_version_refs JSONB,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Knowledge Base

knowledge_bases

CREATE TABLE knowledge_bases (
  id UUID PRIMARY KEY,
  project_id UUID REFERENCES projects(id),
  workspace_id UUID REFERENCES workspaces(id),
  name TEXT NOT NULL,
  description TEXT,
  scope TEXT NOT NULL DEFAULT 'project',
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

knowledge_documents

CREATE TABLE knowledge_documents (
  id UUID PRIMARY KEY,
  knowledge_base_id UUID NOT NULL REFERENCES knowledge_bases(id),
  title TEXT NOT NULL,
  source_type TEXT NOT NULL,
  source_uri TEXT,
  content_hash TEXT,
  status TEXT NOT NULL DEFAULT 'processing',
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

knowledge_chunks

CREATE TABLE knowledge_chunks (
  id UUID PRIMARY KEY,
  document_id UUID NOT NULL REFERENCES knowledge_documents(id),
  chunk_index INTEGER NOT NULL,
  content TEXT NOT NULL,
  embedding vector,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Agent Config

agent_configs

CREATE TABLE agent_configs (
  id UUID PRIMARY KEY,
  project_id UUID REFERENCES projects(id),
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

agent_config_versions

CREATE TABLE agent_config_versions (
  id UUID PRIMARY KEY,
  agent_config_id UUID NOT NULL REFERENCES agent_configs(id),
  version_number INTEGER NOT NULL,
  model_config JSONB NOT NULL,
  system_prompt TEXT,
  interviewer_policy JSONB,
  safety_policy JSONB,
  tool_policy JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Tools

tool_definitions

CREATE TABLE tool_definitions (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  tool_type TEXT NOT NULL,
  input_schema JSONB NOT NULL,
  output_schema JSONB NOT NULL,
  version TEXT NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

study_enabled_tools

CREATE TABLE study_enabled_tools (
  id UUID PRIMARY KEY,
  study_version_id UUID NOT NULL REFERENCES study_versions(id),
  tool_definition_id UUID NOT NULL REFERENCES tool_definitions(id),
  config JSONB,
  enabled BOOLEAN NOT NULL DEFAULT true
);

Output Schema

output_schemas

CREATE TABLE output_schemas (
  id UUID PRIMARY KEY,
  project_id UUID REFERENCES projects(id),
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

output_schema_versions

CREATE TABLE output_schema_versions (
  id UUID PRIMARY KEY,
  output_schema_id UUID NOT NULL REFERENCES output_schemas(id),
  version_number INTEGER NOT NULL,
  schema_json JSONB NOT NULL,
  field_definitions JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Sessions

participants

CREATE TABLE participants (
  id UUID PRIMARY KEY,
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  external_id TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

sessions

CREATE TABLE sessions (
  id UUID PRIMARY KEY,
  study_id UUID NOT NULL REFERENCES studies(id),
  study_version_id UUID NOT NULL REFERENCES study_versions(id),
  participant_id UUID REFERENCES participants(id),
  status TEXT NOT NULL DEFAULT 'created',
  current_phase TEXT,
  session_state JSONB,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  last_activity_at TIMESTAMPTZ,
  quality_score NUMERIC,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

transcript_turns

CREATE TABLE transcript_turns (
  id UUID PRIMARY KEY,
  session_id UUID NOT NULL REFERENCES sessions(id),
  turn_index INTEGER NOT NULL,
  role TEXT NOT NULL,
  content TEXT,
  content_json JSONB,
  objective_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

tool_calls

CREATE TABLE tool_calls (
  id UUID PRIMARY KEY,
  session_id UUID NOT NULL REFERENCES sessions(id),
  turn_id UUID REFERENCES transcript_turns(id),
  tool_name TEXT NOT NULL,
  tool_input JSONB NOT NULL,
  tool_output JSONB,
  status TEXT NOT NULL DEFAULT 'created',
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  error TEXT
);

interaction_results

CREATE TABLE interaction_results (
  id UUID PRIMARY KEY,
  session_id UUID NOT NULL REFERENCES sessions(id),
  tool_call_id UUID NOT NULL REFERENCES tool_calls(id),
  interaction_id TEXT NOT NULL,
  interaction_type TEXT NOT NULL,
  status TEXT NOT NULL,
  value JSONB,
  client_context JSONB,
  submitted_at TIMESTAMPTZ
);

Extraction and Evidence

extracted_fields

CREATE TABLE extracted_fields (
  id UUID PRIMARY KEY,
  session_id UUID NOT NULL REFERENCES sessions(id),
  study_id UUID NOT NULL REFERENCES studies(id),
  schema_field_id TEXT NOT NULL,
  field_name TEXT NOT NULL,
  value JSONB,
  normalized_value JSONB,
  confidence NUMERIC,
  status TEXT NOT NULL DEFAULT 'draft',
  extracted_by TEXT NOT NULL DEFAULT 'agent',
  model TEXT,
  schema_version_id UUID NOT NULL REFERENCES output_schema_versions(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

evidence_refs

CREATE TABLE evidence_refs (
  id UUID PRIMARY KEY,
  extracted_field_id UUID REFERENCES extracted_fields(id),
  insight_id UUID,
  session_id UUID NOT NULL REFERENCES sessions(id),
  turn_id UUID REFERENCES transcript_turns(id),
  tool_call_id UUID REFERENCES tool_calls(id),
  quote TEXT,
  evidence_type TEXT NOT NULL,
  strength TEXT NOT NULL,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

study_insights

CREATE TABLE study_insights (
  id UUID PRIMARY KEY,
  study_id UUID NOT NULL REFERENCES studies(id),
  title TEXT NOT NULL,
  summary TEXT NOT NULL,
  theme TEXT,
  severity TEXT,
  frequency NUMERIC,
  confidence NUMERIC,
  status TEXT NOT NULL DEFAULT 'draft',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Index 建议

CREATE INDEX idx_projects_workspace ON projects(workspace_id);
CREATE INDEX idx_studies_project ON studies(project_id);
CREATE INDEX idx_sessions_study ON sessions(study_id);
CREATE INDEX idx_turns_session ON transcript_turns(session_id, turn_index);
CREATE INDEX idx_tool_calls_session ON tool_calls(session_id);
CREATE INDEX idx_extracted_fields_session ON extracted_fields(session_id);
CREATE INDEX idx_extracted_fields_study_field ON extracted_fields(study_id, schema_field_id);
CREATE INDEX idx_evidence_session ON evidence_refs(session_id);

数据模型原则

  1. Config version 和 runtime record 必须绑定。
  2. Transcript 是原始事实源。
  3. Tool call/result 是 transcript 的结构化扩展。
  4. Extracted field 是可修正、可审计的派生数据。
  5. Evidence 是 extracted field 和 transcript/tool result 的桥。
  6. Study insight 是跨 session 派生数据，也必须绑定 evidence。
  7. sessions.session_state 与 LangGraph checkpointer 互补：前者是业务可查询状态，后者（PostgresSaver）存 graph 执行快照，用于会话重放 / 崩溃恢复 / time-travel（见 05_tech_stack_decision.md）。