# Jira Ticket Classification System Design

## 1. Introduction

To achieve comprehensive and actionable insights from our ticket data, we employ a **multi-dimensional classification system**. This system consists of six complementary classification dimensions that work together to provide complete visibility:

1. **Problem Pattern (L1/L2)** - What symptom occurred? (Technical perspective)
2. **Root Cause (L1/L2/L3)** - What was the technical cause? (Engineering perspective)
3. **Issue Type** - Why did the user contact us? (User intent perspective)
4. **Service Type** - What service did we deliver? (Service depth perspective)
5. **Problem Domain** - What was the user trying to do? (Business scenario perspective)
6. **Wetech Classification (L1/L2/L3)** - What is Wetech team's classification? (ST/APID projects only)

This document analyzes the design philosophy, strengths, and intended purpose of each dimension, and explains how they work together to serve different stakeholders.

**For detailed specifications of each dimension, see:**
- [Problem Pattern](./category_problem.md) - Symptom classification (L1/L2 hierarchy)
- [Root Cause](./category_root_cause.md) - Technical cause classification (L1/L2/L3 hierarchy)
- [Issue Type](./category_issue_type.md) - User intent classification (6 flat categories)
- [Service Type](./category_service_type.md) - Service depth classification (8 flat categories)
- [Problem Domain](./category_problem_domain.md) - Business scenario classification (21 flat categories)
- [Wetech Classification](./category_wetech.md) - Wetech team's 3-level classification (ST/APID only)

## 2. Design Philosophy

### Complementary, Not Replacement

All dimensions are **complementary**, not replacements. Each serves a distinct purpose and answers a different question:

| Classification | Core Question | Perspective | Timing | Audience | Structure |
|---------------|---------------|-------------|---------|----------|-----------|
| **Problem Pattern** | What symptom occurred? | Technical symptom | Ticket creation | Support, Engineering | Hierarchical (L1/L2) |
| **Root Cause** | What was the technical cause? | Deep technical | Ticket resolution | Engineering, SREs | Hierarchical (L1/L2/L3) |
| **Issue Type** | Why did user contact us? | User intent | Ticket creation | Management, Product | Flat (6 categories) |
| **Service Type** | What service did we deliver? | Service depth | Ticket resolution | Management, Service Ops | Flat (8 categories) |
| **Problem Domain** | What was user trying to do? | Business scenario | Ticket creation | Management, Product | Flat (21 categories) |
| **Wetech** | Wetech team's classification? | Wetech-specific | Ticket resolution | Wetech Team | Hierarchical (L1/L2/L3) |

**Note:** Wetech Classification only applies to **ST** and **APID** projects.

### Core Design Principles

1. **Hierarchical for Technical Depth**: Problem Pattern (L1/L2) and Root Cause (L1/L2/L3) use hierarchical structures to capture technical details at different granularity levels.

2. **Flat for Business Intelligence**: Issue Type, Service Type, and Problem Domain use flat, single-level structures ideal for executive reporting and pie chart visualization.

3. **Business-Oriented**: New dimensions (Issue Type, Service Type, Problem Domain) are designed to be understandable by non-technical stakeholders while remaining useful for technical teams.

4. **Value-Focused**: Service Type explicitly captures and quantifies service value, particularly expert-level work that customers cannot perform themselves.

5. **Scenario-Based**: Problem Domain focuses on user activities/scenarios rather than technical components, providing actionable business insights.

6. **Timing Separation**: Some dimensions are filled at ticket creation (symptom-level), others at resolution (after investigation).

## 3. Analysis of Problem Pattern (L1 -> L2)

The two-level Problem Pattern system answers: **"From a user and operational perspective, what symptom occurred?"**

| Strengths | Weaknesses |
|-----------|------------|
| ✅ **Simple & Easy to Use**: Limited set of options allows support personnel to quickly classify tickets at creation | ❌ **Lacks Technical Depth**: Describes the symptom (e.g., `slow_query`) but not the specific technical reason |
| ✅ **Ideal for High-Level Reporting**: Broader categories perfect for management dashboards and trend reports | ❌ **Not Directly Actionable for Engineers**: Engineers still need full investigation to find root cause |
| ✅ **High Data Consistency**: Fewer, clearer options reduce ambiguity and improve consistency | ❌ **Cannot Drive Specific Engineering Decisions**: Knowing `slow_query` is top issue doesn't specify whether to fix optimizer, storage engine, or hotspots |

**Intended Use:**
- **Audience**: Support Teams, Operations, Product Managers, Management
- **Timing**: Filled at ticket creation or initial triage
- **Purpose**: Track user-facing issues, manage support workloads, inform high-level priorities

---

## 4. Analysis of Root Cause (L1 -> L2 -> L3)

The three-level Root Cause system answers: **"From a technical perspective, which specific component was the ultimate cause?"**

| Strengths | Weaknesses |
|-----------|------------|
| ✅ **Highly Specific & Granular**: Pinpoints problems to precise technical modules (e.g., `kernel->optimizer_executor->stale_stats`) | ❌ **Complex & High Barrier**: Requires deep technical knowledge, not suitable for non-engineers |
| ✅ **Directly Actionable for Engineers**: Provides immediate context for investigation and helps identify recurring bugs | ❌ **Data Can Be Sparse**: Many specific categories may have few tickets each (long-tail effect) |
| ✅ **Drives Data-Driven Engineering**: Enables engineering leads to identify systemic weaknesses and prioritize technical debt | ❌ **Higher Maintenance**: Needs to stay in sync with evolving product architecture |

**Intended Use:**
- **Audience**: Software Engineers, SREs, Technical Leads, Architects
- **Timing**: Filled at ticket resolution or closure
- **Purpose**: Deep root cause analysis, track component-specific bugs, inform engineering roadmap

---

## 5. Analysis of Issue Type (Flat, 6 Categories)

Issue Type answers: **"Why did the user contact us?"** (User intent perspective)

| Strengths | Use Cases |
|-----------|-----------|
| ✅ **Business-Oriented**: Clear to executives and product managers | Product team prioritization: "Are users struggling with bugs or UX?" |
| ✅ **Simple Classification**: 6 categories, quick to assign at creation | Management reporting: Simple pie chart of user needs |
| ✅ **Actionable for Product**: Directly guides product improvements | Cross-analysis: "For Lightning, is it bugs (14%) or UX (30%)?" |

**Categories**: `bug`, `performance`, `feature_gap`, `user_experience`, `operational_support`, `consultation`

**Intended Use:**
- **Audience**: Management, Product Managers, Support
- **Timing**: Filled at ticket creation
- **Purpose**: Understand user intent, prioritize product improvements, executive reporting

---

## 6. Analysis of Service Type (Flat, 8 Categories)

Service Type answers: **"What level of service did we deliver?"** (Service depth perspective)

| Strengths | Use Cases |
|-----------|-----------|
| ✅ **Quantifies Expert Work**: Makes "invisible" diagnostic work visible and measurable | Service value quantification: "70% of tickets required expert-level services" |
| ✅ **Value-Oriented**: Highlights services customers cannot do themselves (⭐⭐⭐ rated) | Resource planning: Understand where expert time is spent |
| ✅ **Orthogonal to Problem Pattern**: Describes *how deep* service was, not *what* domain | Customer communication: Demonstrate value delivered |

**Categories** (with value ratings):
- ⭐⭐⭐ High Value: `deep_troubleshooting`, `bug_investigation`, `performance_tuning`
- ⭐⭐ Medium Value: `operational_guidance`, `data_tool_support`, `technical_consulting`, `workaround_mitigation`
- ⭐ Standard Value: `connectivity_resolution`

**Intended Use:**
- **Audience**: Management, Service Operations, Executives
- **Timing**: Filled at ticket resolution
- **Purpose**: Quantify service value, justify pricing, resource planning

---

## 7. Analysis of Problem Domain (Flat, 18 Categories, ARRAY)

Problem Domain answers: **"What was the user trying to do?"** (Business scenario perspective)

| Strengths | Use Cases |
|-----------|-----------|
| ✅ **Scenario-Oriented**: Focuses on user activities, not technical components | Executive reporting: "Which business scenarios have most issues?" |
| ✅ **Directly Actionable**: Maps to product investment areas | Product prioritization: "Query optimization is #1 → Invest in diagnostics" |
| ✅ **Business-Understandable**: Clear to non-technical stakeholders | Cross-analysis: "For data import, 60% performance, 30% UX" |
| ✅ **Array Field**: Captures complex scenarios (e.g., scaling + network) | Trend tracking: Are improvements reducing issues over time? |

**Categories** (18 total, grouped into 5 themes):
- Database Query & Transaction (4): `query_optimization`, `write_performance`, `ddl_execution`, `transaction_locking`
- Data Migration & Sync (4): `data_import`, `data_migration`, `realtime_replication`, `backup_recovery`
- Cluster Operations (5): `cluster_scaling`, `version_upgrade`, `configuration_management`, `resource_exhaustion`, `cluster_stability`
- Connectivity & Access (2): `connection_authentication`, `network_routing`
- Monitoring & Consultation (3): `monitoring_alerting`, `platform_console`, `product_knowledge`

**Intended Use:**
- **Audience**: Management, Product Managers, Executives
- **Timing**: Filled at ticket creation
- **Purpose**: Business intelligence, product roadmap, investment prioritization

---

## 8. How Dimensions Work Together

### Complete Classification System

| Dimension | Type | Structure | Filled When | Filled By | Purpose |
|-----------|------|-----------|-------------|-----------|---------|
| **Customer** | STRING | Single value | Creation | System (auto) | Customer filtering |
| **Issue Type** | STRING | Flat (6 categories) | Creation | Support/AI | User intent |
| **Problem Domain** | ARRAY<STRING> | Flat (18 categories) | Creation | Support/AI | Business scenario |
| **Problem Pattern** | STRING × 2 | Hierarchical (L1/L2) | Creation | Support | Technical symptom |
| **Service Type** | STRING | Flat (8 categories) | Resolution | Engineer/AI | Service depth |
| **Effort Hours** | STRING | Flat (5 ranges) | Resolution | Engineer/AI | Workload estimate |
| **Root Cause** | STRING × 3 | Hierarchical (L1/L2/L3) | Resolution | Engineer | Technical cause |

### Example: Multi-Dimensional Analysis

**Scenario:** "Wetech customer reports slow data import with TiDB Lightning"

| Dimension | Value | Interpretation |
|-----------|-------|----------------|
| `customer` | `wetech` | Ticket belongs to customer Wetech |
| `issue_type` | `performance` | User wants better performance |
| `problem_domains` | `['data_import', 'resource_exhaustion']` | User was importing data, encountered resource issues |
| `problem_pattern_l1/l2` | `data_utils/tidb_lightning` | Technical symptom: Lightning performance issue |
| `service_type` | `data_tool_support` | We provided data tool troubleshooting |
| `effort_hours` | `4-8` | Required 4-8 hours of engineering time |
| `root_cause_l1/l2/l3` | `kernel/storage_engine/write_stall` | Root cause: storage engine write stall |

**Insights from Multiple Dimensions:**
- **Issue Type + Problem Domain**: Performance issue during data import → Invest in Lightning performance
- **Service Type + Effort Hours**: Required high-effort data tool support → Lightning is complex for users
- **Root Cause**: Caused by storage engine write stall → Kernel improvement needed

---

## 9. Common Analysis Patterns

### Pattern 1: Product Prioritization

**Question:** "What should we invest in next quarter?"

```sql
-- Step 1: Find top problem domains
SELECT domain, COUNT(*) as count
FROM jira.ticket, UNNEST(ai_predicted_problem_domains) as domain
GROUP BY domain ORDER BY count DESC LIMIT 5;

-- Step 2: For each domain, understand issue types
SELECT domain, ai_predicted_issue_type, COUNT(*) as count
FROM jira.ticket, UNNEST(ai_predicted_problem_domains) as domain
WHERE domain IN ('query_optimization', 'data_import')
GROUP BY domain, ai_predicted_issue_type;
```

**Insight:** "Query optimization is #1 (30%), mostly Performance issues (60%) → Invest in query diagnostic tools and auto-tuning."

### Pattern 2: Service Value Quantification

**Question:** "What percentage of work demonstrates our core value?"

```sql
SELECT
  CASE
    WHEN ai_predicted_service_type IN ('deep_troubleshooting', 'bug_investigation', 'performance_tuning')
    THEN 'High Value'
    ELSE 'Standard'
  END as service_category,
  COUNT(*) as count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM jira.ticket
WHERE customer = 'wetech'
  AND created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)
GROUP BY service_category;
```

**Insight:** "70% of Wetech tickets required high-value expert services → Justify premium support pricing."

### Pattern 3: Customer Analysis

**Question:** "What does customer X struggle with?"

```sql
-- Problem domains
SELECT domain, COUNT(*) as count
FROM jira.ticket, UNNEST(ai_predicted_problem_domains) as domain
WHERE customer = 'wetech'
GROUP BY domain ORDER BY count DESC LIMIT 5;

-- Issue types
SELECT ai_predicted_issue_type, COUNT(*) as count
FROM jira.ticket
WHERE customer = 'wetech'
GROUP BY ai_predicted_issue_type ORDER BY count DESC;

-- Service types required
SELECT ai_predicted_service_type, COUNT(*) as count
FROM jira.ticket
WHERE customer = 'wetech'
GROUP BY ai_predicted_service_type ORDER BY count DESC;
```

**Insight:** Build customer profile → Tailor support, training, or product improvements.

---

## 10. Benefits by Stakeholder

### For Executives
- ✅ Simple pie charts showing business priorities (Problem Domain)
- ✅ Clear ROI of support services (Service Type value quantification)
- ✅ Data-driven product investment decisions (Issue Type + Problem Domain)

### For Product Managers
- ✅ Direct mapping to product features and roadmap (Problem Domain)
- ✅ Understand if issues are bugs vs UX vs missing features (Issue Type)
- ✅ Prioritize improvements based on frequency and impact
- ✅ Track if product improvements reduce issues over time

### For Engineering Teams
- ✅ Detailed Root Cause analysis for technical work (L1/L2/L3)
- ✅ Link user scenarios to technical causes (Problem Domain + Root Cause)
- ✅ Identify systemic issues through cross-dimensional analysis
- ✅ Data-driven technical debt prioritization

### For Support Teams
- ✅ Easier classification at ticket creation (flat categories for new dimensions)
- ✅ Better visibility into service impact and value delivered
- ✅ Data-driven evidence for resource requests and hiring
- ✅ Consistent classification with clear guidelines

---

## 11. Conclusion: The Power of Multi-Dimensional Classification

The optimal approach is to utilize **all five dimensions as complementary fields** for each ticket. This creates a rich, multi-dimensional dataset that serves all stakeholders:

1. **Problem Pattern (L1/L2)** - What symptom occurred? (Support, Operations)
2. **Root Cause (L1/L2/L3)** - What was the technical cause? (Engineering, SREs)
3. **Issue Type** - Why did user contact us? (Management, Product)
4. **Service Type** - What service did we deliver? (Management, Service Ops)
5. **Problem Domain** - What was user trying to do? (Management, Product, Executives)

**Key Principle:** Each dimension answers a different question. Together, they provide:
- **Support and Product** get clear views of user-facing problems and business priorities
- **Engineering** gets precise, actionable data to find and fix root causes
- **Management** gets quantified service value and investment guidance
- **Executives** get business-oriented insights for strategic decisions

This comprehensive multi-dimensional approach enables truly data-driven decision-making across all levels of the organization.