# Ticket Table Data Dictionary

**Table Name**: `gcp-cp-bigquery-dev.jira.ticket`

**Partitioning**: Partitioned by `created_at` field by day

**Update Frequency**: Daily full sync

## Core Fields

### Ticket Identification Information

| Field Name | Type | Description | Example |
| ---------- | ---- | ----------- | ------- |
| `ticket_key` | STRING | Ticket unique identifier | "TCOC-1204" |
| `project` | STRING | Project code | "TCOC", "APID", "FRM", "ST", "TCID" |
| `ticket_link` | STRING | Direct URL to the ticket in Jira | "https://tidb.atlassian.net/browse/TCOC-1204" |
| `tenant_id` | STRING | TiDB Cloud tenant/organization identifier | "org_123456" |
| `cluster_id` | STRING | TiDB Cloud cluster identifier | "cluster_789012" |
| `tidb_versions` | ARRAY<STRING> | TiDB versions affected by this ticket | ["v8.5.1", "v7.5.6"] |
| `customer` | STRING | Customer name (see values below) | "Wetech", "BingoPlus" |
| `severity` | STRING | Severity level | S1/S2/S3/S4 (see description below) |
| `status` | STRING | Ticket status | "In Progress", "Resolved", "Closed" |
| `created_at` | TIMESTAMP | Creation time | 2024-01-15 10:30:00 |
| `updated_at` | TIMESTAMP | Last update time | 2024-01-16 14:22:00 |

**Customer Enum Values**:
- `Wetech`: Primary customer for ST, APID, TCOC projects
- `BingoPlus`
- `UE`
- `Manus`

**Severity Enum Values Description**:
- `S1`: Urgent - System unusable, affecting business
- `S2`: High - Function severely impaired
- `S3`: Medium - Function slightly affected
- `S4`: Low - General issues or suggestions

### Project Description

| Project Code | Full Name | Project Description |
| ------------ | --------- | ------------------- |
| `ST` | Support Ticket | Customer-facing fast-path support project for selected key accounts. |
| `TCOC` | TiDB Cloud On-Call | Global internal Jira OnCall project for TiDB Cloud escalations after GS triage. |
| `FRM` | Feature Request Management | Internal engineering backlog for feature requests and product fixes converted from support tickets. |
| `APID` | APAC-Ticket | Global JSM customer-facing project for APAC Self-Managed tickets. |
| `TCID` | TiDB-Cloud-Ticket | Global JSM customer-facing project for general TiDB Cloud technical support tickets. |

## AI Enriched Content & Status

Fields containing AI-generated content and current ticket status information.

| Field Name | Type | Description | Example |
| ---------- | ---- | ----------- | ------- |
| `ai_generated_summary` | STRING | Concise summary of the ticket's issue, generated by AI model | "Customer experiencing slow query performance on TiDB cluster after upgrade" |

## AI Prediction Fields

Classification results automatically predicted by AI models, used for automatic ticket classification and analysis.

### Issue Type

Field: `ai_predicted_issue_type`

| Value | Description |
| ----- | ----------- |
| `bug` | Bug defect |
| `performance` | Performance issue |
| `feature_gap` | Feature gap |
| `configuration` | Configuration issue |
| `connectivity` | Connectivity issue |
| `user_error` | User operation error |
| `documentation` | Documentation issue |
| `unclear` | Unclassifiable/unclear |

Reference Document: [context/category_issue_type.md](../context/category_issue_type.md)

### Service Type

Field: `ai_predicted_service_type`

Description: AI predicted service support type required for the ticket.

Reference Document: [context/category_service_type.md](../context/category_service_type.md)

### Effort Hours

Field: `ai_predicted_effort_hours`

Estimated hours required to resolve this ticket.

| Value | Estimated Hours |
| ----- | --------------- |
| `"0-1"` | 0-1 hours |
| `"1-2"` | 1-2 hours |
| `"2-4"` | 2-4 hours |
| `"4-8"` | 4-8 hours |
| `"8+"` | More than 8 hours |

### Problem Domains

Field: `ai_predicted_problem_domains` (ARRAY<STRING>)

Description: AI predicted technical domains the problem belongs to. A ticket may involve multiple domains.

Reference Document: [context/category_problem_domain.md](../context/category_problem_domain.md)

**Example Values**:
```json
["scalability", "database", "query_performance"]
```

**Important**: When querying, use `UNNEST` to expand the array:
```sql
SELECT * FROM `gcp-cp-bigquery-dev.jira.ticket`
WHERE EXISTS (SELECT 1 FROM UNNEST(ai_predicted_problem_domains) AS d WHERE d = 'scalability')
```

### Problem Classification

#### Level 1

Field: `ai_predicted_problem_l1`

Description: Level 1 problem classification, the highest level problem type.

| Value | Description |
| ----- | ----------- |
| `Data Management` | Data management issues |
| `System` | System issues |
| `Network` | Network issues |
| `Security` | Security issues |
| `Performance` | Performance issues |
| `unknown` | Unclassifiable |

#### Level 2

Field: `ai_predicted_problem_l2`

Description: Level 2 problem classification, more specific classification.

Field: `ai_problem_confidence`

Description: Confidence score (0.0 to 1.0) of the AI's problem pattern prediction.

Reference Document: [context/category_problem.md](../context/category_problem.md)

### Root Cause Classification

#### Level 1/2/3

Fields: `ai_predicted_root_cause_l1`, `ai_predicted_root_cause_l2`, `ai_predicted_root_cause_l3`

Description: AI predicted root cause of the problem, divided into three levels. l1 is most general, l3 is most specific.

Field: `ai_root_cause_confidence`

Description: Confidence score (0.0 to 1.0) of the AI's root cause prediction.

Reference Document: [context/category_root_cause.md](../context/category_root_cause.md)

### Wetech Classification

#### Level 1/2/3

Fields: `ai_predicted_wetech_l1`, `ai_predicted_wetech_l2`, `ai_predicted_wetech_l3`

Description: AI predicted Wetech team's classification. L1 is Issue Investigation vs Service Support, L2 is the domain (TiDB/Hardware/Workload for investigation, Service Support for support), L3 is the specific root cause pattern or service type.

Field: `ai_wetech_confidence`

Description: Confidence score (0.0 to 1.0) of the AI's Wetech classification prediction.

Reference Document: [context/category_wetech.md](../context/category_wetech.md)

## Manual Verification Fields

When manually reviewed and corrected AI prediction results, these verification fields will be filled in to override AI predictions.

**Important Notes**:
- **verified_* fields are manually filled by humans and are often NULL/empty**
- **ai_predicted_* fields are automatically filled by AI and are usually populated**
- **Priority**: verified_* fields > ai_predicted_* fields (when verified_* is not NULL)
- **Fallback Strategy**: Use verified_* when available, otherwise use ai_predicted_* fields

| Verification Field | Corresponding AI Field | Description |
| ------------------ | ---------------------- | ----------- |
| `verified_issue_type` | `ai_predicted_issue_type` | Manually verified issue type |
| `verified_service_type` | `ai_predicted_service_type` | Manually verified service type |
| `verified_effort_hours` | `ai_predicted_effort_hours` | Manually verified effort hours |
| `verified_problem_domains` | `ai_predicted_problem_domains` | Manually verified problem domains (ARRAY) |
| `verified_problem_l1` | `ai_predicted_problem_l1` | Manually verified problem level 1 classification |
| `verified_problem_l2` | `ai_predicted_problem_l2` | Manually verified problem level 2 classification |
| `verified_root_cause_l1` | `ai_predicted_root_cause_l1` | Manually verified root cause level 1 classification |
| `verified_root_cause_l2` | `ai_predicted_root_cause_l2` | Manually verified root cause level 2 classification |
| `verified_root_cause_l3` | `ai_predicted_root_cause_l3` | Manually verified root cause level 3 classification |
| `verified_wetech_l1` | `ai_predicted_wetech_l1` | Manually verified Wetech level 1 classification |
| `verified_wetech_l2` | `ai_predicted_wetech_l2` | Manually verified Wetech level 2 classification |
| `verified_wetech_l3` | `ai_predicted_wetech_l3` | Manually verified Wetech level 3 classification |
| `verified_severity` | - | Manually verified severity level |

**Verification Flag Fields**:
- `problem_is_verified`: BOOLEAN - Whether problem classification has been manually verified
- `problem_verified_at`: TIMESTAMP - Problem classification verification time
- `root_cause_is_verified`: BOOLEAN - Whether root cause classification has been manually verified
- `root_cause_verified_at`: TIMESTAMP - Root cause classification verification time
- `wetech_is_verified`: BOOLEAN - Whether Wetech classification has been manually verified
- `wetech_verified_at`: TIMESTAMP - Wetech classification verification time
- `issue_type_is_verified`: BOOLEAN - Whether issue type has been manually verified
- `issue_type_verified_at`: TIMESTAMP - Issue type verification time
- `service_type_is_verified`: BOOLEAN - Whether service type has been manually verified
- `service_type_verified_at`: TIMESTAMP - Service type verification time
- `effort_hours_is_verified`: BOOLEAN - Whether effort hours estimate has been manually verified
- `effort_hours_verified_at`: TIMESTAMP - Effort hours verification time
- `problem_domains_is_verified`: BOOLEAN - Whether problem domains have been manually verified
- `problem_domains_verified_at`: TIMESTAMP - Problem domains verification time
- `severity_is_verified`: BOOLEAN - Whether severity has been manually verified
- `severity_verified_at`: TIMESTAMP - Severity verification time

## Query Examples

### Example 1: Count tickets in the last 30 days

```sql
SELECT COUNT(*) as total
FROM `gcp-cp-bigquery-dev.jira.ticket`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
```

### Example 2: Count high priority tickets (S1/S2) by project

```sql
SELECT
  project,
  COUNTIF(severity IN ('S1', 'S2')) as high_priority_count
FROM `gcp-cp-bigquery-dev.jira.ticket`
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01'
GROUP BY project
```

### Example 3: Query tickets in specific problem domain

```sql
SELECT *
FROM `gcp-cp-bigquery-dev.jira.ticket`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(ai_predicted_problem_domains) AS domain
  WHERE domain = 'scalability'
)
AND created_at >= '2024-01-01'
```

### Example 4: Calculate manual verification coverage rate

```sql
SELECT
  COUNTIF(problem_is_verified = TRUE) as verified_count,
  COUNT(*) as total_count,
  ROUND(COUNTIF(problem_is_verified = TRUE) / COUNT(*) * 100, 2) as verification_rate
FROM `gcp-cp-bigquery-dev.jira.ticket`
WHERE created_at >= '2024-01-01'
```

## Notes

1. **Must include time range**: All queries must include `created_at` conditions to utilize partitioning optimization
2. **Array field handling**: `problem_domains` field is ARRAY type, queries need to use `UNNEST`
3. **Enum value strict matching**: Fields like `project`, `severity`, `status` are case-sensitive
4. **Verification field priority**: verified_* fields take priority when available (higher accuracy), but are often NULL/empty. Fall back to ai_predicted_* fields when verified_* fields are NULL
5. **NULL value handling**: AI prediction fields may be NULL, indicating unclassifiable. Manual verification fields are also often NULL due to limited human review capacity

## Related Documents

- [Data Schema Design](./data_schema_design.md)
- [Jira to BigQuery Pipeline](./jira_to_bigquery_pipeline.md)