# Data Schema for AI-Driven Ticket Classification and Analysis

## 1. Introduction

This document defines the standardized BigQuery schema for storing, enriching, and analyzing support tickets. This schema is designed to function as an analytical data layer, which is populated by an AI-driven pipeline that classifies tickets and is subsequently enriched by a human verification process.

Jira remains the single source of truth for all operational ticket data. This schema is intentionally lean, storing only the data required for trend analysis, operational reporting, and AI model performance evaluation. A direct link to the Jira ticket is included in every record for full traceability.

## 2. Design Philosophy

The core design philosophy remains built upon two key distinctions:

* **Problem Pattern vs. Root Cause**: We maintain the clear separation between the observable *symptom* of an issue (the "Problem Pattern," e.g., `slow_query`) and the underlying technical reason (the "Root Cause," e.g., `TIKV GC Issue`). Both classifications will now follow an AI-first, human-review workflow.

* **AI Prediction vs. Verified Ground Truth**: This principle is fundamental. We explicitly separate the AI model's raw output from the final, human-confirmed classification for both Problem Pattern and Root Cause.
    * **AI Prediction**: This is the classification suggested by the AI model, stored with a confidence score. It provides real-time, automated insights.
    * **Verified Ground Truth**: This is the definitive, correct classification, confirmed by a human expert. This data serves as the "source of truth" for all official business reporting and is used as the benchmark for measuring AI accuracy.

This schema enables us to answer not only "What are our main issues?" but also "How accurate are the AI's initial assessments of both the problem and its root cause?"

## 3. BigQuery Schema Structure

The data will be stored in a single, wide table in BigQuery, designed for analytical performance.

* **Table Name**: `ticket_classification_analysis`
* **Partitioning**: It is recommended to partition this table by `created_at` or `updated_at` (e.g., daily) to optimize query performance and manage costs.

The schema is divided into four logical groups of columns:
1.  **Core Ticket Information**: A minimal set of fields to identify and link to the source Jira ticket.
2.  **AI Enriched Content & Status**: AI-generated content and current status information.
3.  **Problem Pattern Classification**: Fields for the AI-predicted and human-verified problem pattern.
4.  **Root Cause Classification**: Fields for the AI-predicted and human-verified root cause.

## 4. Data Flow & Usage Guidelines

1.  **Ingestion**: When a ticket is created or updated in Jira, a pipeline extracts the necessary information.
2.  **AI Enrichment & Classification**: The AI model processes the ticket's content. It then populates the BigQuery table with:
    * The `ai_generated_summary`.
    * The `ai_predicted_problem_*` fields.
    * The `ai_predicted_root_cause_*` fields.
3.  **Human Verification**: A review process is used to validate the AI's classifications. Upon review, an expert populates the `verified_*` fields for both Problem Pattern and Root Cause. This creates the ground truth.
4.  **Analysis & Reporting**:
    * For official business KPIs, queries **must** be based on the `verified_*` fields.
    * To evaluate AI model accuracy, compare the `ai_predicted_*` fields against their corresponding `verified_*` fields.
    * The `status` and `updated_at` fields should be used to track the ticket lifecycle for operational reporting.

## 5. Schema Definition Table

The following is the complete, revised schema definition for the `ticket_classification_analysis` table.

| Field Name | Data Type | Description |
| :--- | :--- | :--- |
| **Core Ticket Information** | | |
| `ticket_key` | `STRING` | The unique, human-readable ticket key (e.g., "SUP-12345"). This is the primary key. |
| `project` | `STRING` | The key of the Jira Project the ticket belongs to (e.g., "SUPPORT", "ENGINEERING"). |
| `ticket_link` | `STRING` | A direct URL to the ticket in Jira, the single source of truth. |
| `tenant_id` | `STRING` | The tenant id of this ticket. |
| `cluster_id` | `STRING` | The cluster id of this ticket. |
| `tidb_versions` | `ARRAY<STRING>` | TiDB versions affected by this ticket. |
| **AI Enriched Content & Status** | | |
| `ai_generated_summary`| `STRING` | A concise summary of the ticket's issue, generated by the AI model. |
| `status` | `STRING` | The current status of the ticket in Jira (e.g., "Open", "In Progress", "Closed"). |
| `created_at` | `TIMESTAMP` | The timestamp when the ticket was created in Jira. |
| `updated_at` | `TIMESTAMP` | The timestamp of the last update to the ticket in Jira. |
| **Problem Pattern Classification** | | |
| `ai_predicted_problem_l1` | `STRING` | The L1 problem category predicted by the AI (e.g., "Kernel"). |
| `ai_predicted_problem_l2` | `STRING` | The L2 problem pattern predicted by the AI (e.g., "Slow Query"). |
| `ai_problem_confidence` | `FLOAT64` | The confidence score (0.0 to 1.0) of the AI's problem pattern prediction. |
| `verified_problem_l1` | `STRING` | The final, human-confirmed L1 problem category. **Source of truth for reporting.** |
| `verified_problem_l2` | `STRING` | The final, human-confirmed L2 problem pattern. |
| `problem_is_verified` | `BOOLEAN` | A flag (`true`/`false`) indicating if the problem pattern has been human-verified. |
| `problem_verified_at` | `TIMESTAMP` | The timestamp when the problem pattern verification was completed. |
| **Root Cause Classification** | | |
| `ai_predicted_root_cause_l1` | `STRING` | The L1 root cause category predicted by the AI. |
| `ai_predicted_root_cause_l2` | `STRING` | The L2 root cause category predicted by the AI. |
| `ai_predicted_root_cause_l3` | `STRING` | The L3 root cause category predicted by the AI. Can be NULL. |
| `ai_root_cause_confidence`| `FLOAT64` | The confidence score (0.0 to 1.0) of the AI's root cause prediction. |
| `verified_root_cause_l1` | `STRING` | The final, human-confirmed L1 root cause. **Source of truth for reporting.** |
| `verified_root_cause_l2` | `STRING` | The final, human-confirmed L2 root cause. |
| `verified_root_cause_l3` | `STRING` | The final, human-confirmed L3 root cause. Can be NULL. |
| `root_cause_is_verified` | `BOOLEAN` | A flag (`true`/`false`) indicating if the root cause has been human-verified. |
| `root_cause_verified_at` | `TIMESTAMP` | The timestamp when the root cause verification was completed. |
| **Wetech Classification (ST/APID only)** | | |
| `ai_predicted_wetech_l1` | `STRING` | The L1 Wetech category predicted by the AI: `issue_investigation` or `service_support`. |
| `ai_predicted_wetech_l2` | `STRING` | The L2 Wetech domain predicted by the AI: `tidb`, `hardware`, `workload`, or `service_support`. |
| `ai_predicted_wetech_l3` | `STRING` | The L3 Wetech specific type predicted by the AI. See category_wetech.md for full list. |
| `ai_wetech_confidence` | `FLOAT64` | The confidence score (0.0 to 1.0) of the AI's Wetech classification prediction. |
| `verified_wetech_l1` | `STRING` | The final, human-confirmed L1 Wetech category. **Source of truth for Wetech reporting.** |
| `verified_wetech_l2` | `STRING` | The final, human-confirmed L2 Wetech domain. |
| `verified_wetech_l3` | `STRING` | The final, human-confirmed L3 Wetech specific type. |
| `wetech_is_verified` | `BOOLEAN` | A flag (`true`/`false`) indicating if the Wetech classification has been human-verified. |
| `wetech_verified_at` | `TIMESTAMP` | The timestamp when the Wetech verification was completed. |


## 6. BigQuery Table
```sql
CREATE OR REPLACE TABLE `your-project-id.your-dataset-name.ticket_classification_analysis`
(
  -- Core Ticket Information
  ticket_key STRING OPTIONS(description="The unique, human-readable ticket key (e.g., 'SUP-12345'). This is the primary key."),
  project STRING OPTIONS(description="The key of the Jira Project the ticket belongs to (e.g., 'SUPPORT', 'ENGINEERING')."),
  ticket_link STRING OPTIONS(description="A direct URL to the ticket in Jira, the single source of truth."),
  tenant_id STRING OPTIONS(description="The tenant id of this ticket."),
  cluster_id STRING OPTIONS(description="The cluster id of this ticket."),
  tidb_versions ARRAY<STRING> OPTIONS(description="TiDB versions affected by this ticket."),

  -- AI Enriched Content & Status
  ai_generated_summary STRING OPTIONS(description="A concise summary of the ticket's issue, generated by the AI model."),
  status STRING OPTIONS(description="The current status of the ticket in Jira (e.g., 'Open', 'In Progress', 'Closed')."),
  created_at TIMESTAMP OPTIONS(description="The timestamp when the ticket was created in Jira."),
  updated_at TIMESTAMP OPTIONS(description="The timestamp of the last update to the ticket in Jira."),

  -- Problem Pattern Classification
  ai_predicted_problem_l1 STRING OPTIONS(description="The L1 problem category predicted by the AI (e.g., 'Kernel')."),
  ai_predicted_problem_l2 STRING OPTIONS(description="The L2 problem pattern predicted by the AI (e.g., 'Slow Query')."),
  ai_problem_confidence FLOAT64 OPTIONS(description="The confidence score (0.0 to 1.0) of the AI's problem pattern prediction."),
  verified_problem_l1 STRING OPTIONS(description="The final, human-confirmed L1 problem category. Source of truth for reporting."),
  verified_problem_l2 STRING OPTIONS(description="The final, human-confirmed L2 problem pattern."),
  problem_is_verified BOOL OPTIONS(description="A flag (true/false) indicating if the problem pattern has been human-verified."),
  problem_verified_at TIMESTAMP OPTIONS(description="The timestamp when the problem pattern verification was completed."),

  -- Root Cause Classification
  ai_predicted_root_cause_l1 STRING OPTIONS(description="The L1 root cause category predicted by the AI."),
  ai_predicted_root_cause_l2 STRING OPTIONS(description="The L2 root cause category predicted by the AI."),
  ai_predicted_root_cause_l3 STRING OPTIONS(description="The L3 root cause category predicted by the AI. Can be NULL."),
  ai_root_cause_confidence FLOAT64 OPTIONS(description="The confidence score (0.0 to 1.0) of the AI's root cause prediction."),
  verified_root_cause_l1 STRING OPTIONS(description="The final, human-confirmed L1 root cause. Source of truth for reporting."),
  verified_root_cause_l2 STRING OPTIONS(description="The final, human-confirmed L2 root cause."),
  verified_root_cause_l3 STRING OPTIONS(description="The final, human-confirmed L3 root cause. Can be NULL."),
  root_cause_is_verified BOOL OPTIONS(description="A flag (true/false) indicating if the root cause has been human-verified."),
  root_cause_verified_at TIMESTAMP OPTIONS(description="The timestamp when the root cause verification was completed."),

  -- Wetech Classification (ST/APID projects only)
  ai_predicted_wetech_l1 STRING OPTIONS(description="AI-predicted Wetech L1 category: issue_investigation or service_support."),
  ai_predicted_wetech_l2 STRING OPTIONS(description="AI-predicted Wetech L2 domain: tidb, hardware, workload, or service_support."),
  ai_predicted_wetech_l3 STRING OPTIONS(description="AI-predicted Wetech L3 specific type. See category_wetech.md for full list."),
  ai_wetech_confidence FLOAT64 OPTIONS(description="Confidence score (0.0 to 1.0) for Wetech classification prediction."),
  verified_wetech_l1 STRING OPTIONS(description="Human-verified Wetech L1 category. Source of truth for Wetech reporting."),
  verified_wetech_l2 STRING OPTIONS(description="Human-verified Wetech L2 domain."),
  verified_wetech_l3 STRING OPTIONS(description="Human-verified Wetech L3 specific type."),
  wetech_is_verified BOOL OPTIONS(description="Flag indicating if the Wetech classification has been human-verified."),
  wetech_verified_at TIMESTAMP OPTIONS(description="Timestamp when the Wetech verification was completed.")
)
PARTITION BY
  DATE(created_at)
CLUSTER BY
  project, status
OPTIONS(
  description="Stores AI-driven classification and enrichment data for support tickets for analysis and model evaluation."
);
```