5.1 核心数据表结构
CREATE TABLE tickets (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_number VARCHAR(20) UNIQUE NOT NULL,
title VARCHAR(200) NOT NULL,
description TEXT NOT NULL,
priority VARCHAR(10) NOT NULL CHECK (priority IN ('P0', 'P1', 'P2', 'P3')),
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
region VARCHAR(20) NOT NULL CHECK (region IN ('domestic', 'overseas')),
reporter_id UUID NOT NULL,
reporter_type VARCHAR(20) NOT NULL,
reporter_contact JSONB,
root_cause TEXT,
affected_services JSONB,
similar_case_ids UUID[],
solution_plan JSONB,
assigned_to UUID,
estimated_minutes INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
resolved_at TIMESTAMP WITH TIME ZONE,
closed_at TIMESTAMP WITH TIME ZONE,
INDEX idx_tickets_status (status),
INDEX idx_tickets_priority (priority),
INDEX idx_tickets_assigned (assigned_to),
INDEX idx_tickets_created (created_at)
);
CREATE TABLE ticket_progress (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_id UUID NOT NULL REFERENCES tickets(id),
handler_id UUID NOT NULL,
progress_percentage INTEGER NOT NULL CHECK (progress_percentage BETWEEN 0 AND 100),
feedback_text TEXT,
attachments JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
INDEX idx_progress_ticket (ticket_id),
INDEX idx_progress_handler (handler_id)
);
CREATE TABLE skill_profiles (
user_id UUID PRIMARY KEY,
skills JSONB NOT NULL,
expertise_levels JSONB,
current_load INTEGER DEFAULT 0,
max_load INTEGER DEFAULT 10,
available_hours JSONB,
timezone VARCHAR(50) DEFAULT 'UTC',
INDEX idx_skills ((skills->>'categories'))
);
CREATE TABLE historical_cases (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title VARCHAR(200) NOT NULL,
description TEXT NOT NULL,
root_cause TEXT NOT NULL,
solution TEXT NOT NULL,
resolution_time_minutes INTEGER,
services_affected JSONB,
tags TEXT[],
embedding vector(1536),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
INDEX idx_cases_tags USING GIN (tags),
INDEX idx_cases_embedding USING ivfflat (embedding vector_cosine_ops)
);
5.2 LangGraph 记忆存储设计
💾 长短期记忆策略
短期记忆(Short-term Memory):使用 LangGraph Checkpoint 保存当前工单处理状态,支持断点续处理
长期记忆(Long-term Memory):使用 InMemoryStore 或外部向量数据库存储历史案例、处理经验、最佳实践
记忆检索:基于语义相似度检索相似历史案例,辅助当前问题处理