6.1 核心表结构
CREATE TABLE tickets (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_number VARCHAR(30) 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(30) NOT NULL DEFAULT 'NEW',
region VARCHAR(20) NOT NULL CHECK (region IN ('domestic', 'overseas')),
reporter_id VARCHAR(100) NOT NULL,
reporter_type VARCHAR(20) NOT NULL,
reporter_contact JSONB DEFAULT '{}',
root_cause TEXT,
affected_services TEXT[] DEFAULT '{}',
similar_case_ids UUID[] DEFAULT '{}',
log_analysis JSONB,
solution_plan JSONB,
assigned_to VARCHAR(100),
assigned_team VARCHAR(100),
estimated_minutes INTEGER,
sla_deadline TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
resolved_at TIMESTAMPTZ,
closed_at TIMESTAMPTZ,
INDEX idx_tickets_status (status),
INDEX idx_tickets_priority (priority),
INDEX idx_tickets_assigned (assigned_to),
INDEX idx_tickets_created (created_at DESC),
INDEX idx_tickets_region (region)
);
CREATE TABLE ticket_progress (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_id UUID NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
handler_id VARCHAR(100) NOT NULL,
progress_percentage INTEGER NOT NULL CHECK (progress_percentage BETWEEN 0 AND 100),
feedback_text TEXT,
attachments JSONB DEFAULT '[]',
created_at TIMESTAMPTZ DEFAULT NOW(),
INDEX idx_progress_ticket (ticket_id),
INDEX idx_progress_handler (handler_id),
INDEX idx_progress_created (created_at DESC)
);
CREATE TABLE skill_profiles (
user_id VARCHAR(100) PRIMARY KEY,
username VARCHAR(100) NOT NULL,
skills JSONB NOT NULL,
expertise_levels JSONB NOT NULL,
current_load INTEGER DEFAULT 0,
max_load INTEGER DEFAULT 10,
available_hours JSONB,
timezone VARCHAR(50) DEFAULT 'UTC',
region VARCHAR(20) DEFAULT 'domestic',
updated_at TIMESTAMPTZ DEFAULT NOW(),
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 TEXT[] DEFAULT '{}',
tags TEXT[] DEFAULT '{}',
priority VARCHAR(10),
region VARCHAR(20),
embedding vector(1536),
created_at TIMESTAMPTZ DEFAULT NOW(),
INDEX idx_cases_tags USING GIN (tags),
INDEX idx_cases_embedding USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100)
);
CREATE TABLE notifications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id VARCHAR(100) NOT NULL,
ticket_id UUID REFERENCES tickets(id),
type VARCHAR(30) NOT NULL,
channel VARCHAR(30) NOT NULL,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
is_read BOOLEAN DEFAULT FALSE,
sent_at TIMESTAMPTZ DEFAULT NOW(),
read_at TIMESTAMPTZ,
INDEX idx_notifications_user (user_id),
INDEX idx_notifications_unread (user_id, is_read) WHERE is_read = FALSE
);
CREATE TABLE agent_execution_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
ticket_id UUID NOT NULL,
agent_name VARCHAR(50) NOT NULL,
action VARCHAR(50) NOT NULL,
input_data JSONB,
output_data JSONB,
execution_time_ms INTEGER,
status VARCHAR(20) DEFAULT 'success',
error_message TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
INDEX idx_agent_logs_ticket (ticket_id),
INDEX idx_agent_logs_agent (agent_name),
INDEX idx_agent_logs_created (created_at DESC)
);