jira-defect-analysis

Jira Defect Analysis Workflow

Trigger Conditions

Use this Skill when the user mentions the following keywords:

T-xxxx, defect analysis, 不具合, コード検証, bug triage
"Process the next one", "Continue analysis", "Backfill Sheet"

Core Principles

Every item must: read the Jira details (description + comments + attachments) + check the code to verify, never guess. Look at all attachments—use the Read tool to view images, and openpyxl to parse Excel files. You must wait for user confirmation before backfilling.

SOP Workflow

Step 0: Initialization (first use for each project)

Check whether /tmp/defect-config.json exists.If it does not exist, interactively collect the following information：

初始化采集 Checklist:
- [ ] 0a. Jira 配置
- [ ] 0b. Google Sheet 配置
- [ ] 0c. 代码仓库配置
- [ ] 0d. 回填规则配置
- [ ] 0e. 通知对象配置
- [ ] 0f. 写入配置文件

0a. Jira Configuration

Ask the user:

"What is the Jira instance URL?" (e.g. https://xxx.atlassian.net）
"What is the Jira project Key?" (e.g. AT、PROJ）
"How do I get the Jira credentials?" (KeyFlow project name / environment variables / provide directly)

If the user says "KeyFlow", use keyflow_get_env_snippet(project="xxx", mask_values=false) to get it.

0b. Google Sheet Configuration

Ask the user:

"What is the keyword for the defect management sheet?" (used for searchDriveFiles search)
"Which tab to use?" (or "take the latest tab")
"Which column is the management number in?" (usually column B)
"Which row is the header in? Which row does the data start from?"
"Are there gaps in the numbering? Which numbers do not exist?"

Alternatively, you can have the user directly provide the Sheet URL and extract the spreadsheetId from it.

0c. Code Repository Configuration

Ask the user:

"What is the path to the code repository?"
"Is it a monorepo or multiple repositories? What is the approximate directory structure?"
"Main tech stack?" (Java/TypeScript/Python, etc., determines the file extensions to search)

You can also directly Read the repository root directory to automatically discover the structure.

0d. Backfill Rule Configuration

Ask the user:

"Which columns need to be backfilled? What is the meaning and what are the candidate values for each column?"
"Which column is the analysis conclusion written in? What are the format requirements?"
"Do you need to set a background color? What color rules?"

Best approach:Have the user give a completed example row, and automatically infer the column mapping and format.
"给我一行已经完整填好的数据（行号），我来学习你的格式。"

0e. Notification Target Configuration

Ask the user:

"Who needs to be @mentioned in the Jira comment?"
"Give me their Jira accountId." (or search the Jira user API to get it)

Get accountId: GET /rest/api/3/user/search?query=姓名

0f. Write Configuration File

Store the collected results as JSON:

// /tmp/defect-config.json
{
  "jira": {
    "base_url": "https://xxx.atlassian.net",
    "project": "AT",
    "credentials": "keyflow:jira-dev-mcp"
  },
  "sheet": {
    "search_keyword": "不具合管理",
    "spreadsheet_id": "auto",
    "tab": "latest",
    "id_column": "B",
    "header_row": 6,
    "data_start_row": 7,
    "gaps": [69, 126, 187, 224]
  },
  "repo": {
    "path": "/path/to/code",
    "extensions": ["java", "ts", "vue", "tsx"],
    "structure": "monorepo"
  },
  "backfill": {
    "columns": {
      "N": {"name": "発生原因", "candidates": ["不具合", "仕様変更", "設計漏れ", "その他"]},
      "O": {"name": "解決方法", "candidates": ["修正", "追加開発", "設定変更", "対応不要"]},
      "P": {"name": "処置区分", "candidates": ["BUG修正", "仕様変更", "文言修正", "その他"]}
    },
    "analysis_column": "AF",
    "analysis_format": "詳細発生原因：{cause}\n詳細対応方法：{solution}\n詳細影響範囲：{scope}",
    "colors": {"done": "#90EE90", "on_hold": "#FFFACD"}
  },
  "mentions": [
    {"name": "徐哲", "account_id": "5b611abc1865dd73be10f688"}
  ]
}

After the configuration file exists, subsequent sessions load it directly and do not collect again. The user can say "update configuration" at any time to re-collect a certain part.

Step 1: Load Configuration & Get the Pending List

Step 1 Checklist:
- [ ] 加载 /tmp/defect-config.json
- [ ] 获取 Jira 凭证
- [ ] 搜索 Google Sheet（如 spreadsheet_id=auto）
- [ ] 读取管理编号列，建立编号→行号映射
- [ ] 搜索 Jira 获取我名下的所有 Issue
- [ ] 交叉比对：哪些已有分析结论（跳过），哪些待处理

Jira Search (must use the new API):

curl -s -X POST -u "$EMAIL:$TOKEN" \
  -H "Content-Type: application/json" \
  "$JIRA_BASE/rest/api/3/search/jql" \
  -d '{"jql":"project=$PROJECT AND assignee=currentUser()","fields":["key","summary","status"],"maxResults":100}' \
  -o /tmp/my-issues.json

rtk will filter curl JSON output—must -o save to a file and then parse with python.

Categorize the Pending List：

Type	Judgment Condition	Handling method
Normal item	Analysis conclusion column is empty	→ Step 2 Full SOP
Quick item	Duplicate / ON HOLD	→ Step 3 Quick channel
Completed	Analysis conclusion column is not empty	→ Skip

Step 2: Item-by-item analysis (core loop)

Each Issue must complete the following checklist,no skipping steps：

[编号] ([Jira Key]) 分析 Checklist:
- [ ] 2a. 读 Jira 完整信息（summary/description/comments/attachments）
- [ ] 2b. 下载并查看所有附件（图片用 Read，Excel 用 openpyxl）
- [ ] 2c. 搜索代码（Grep 关键词 → 定位相关文件）
- [ ] 2d. 阅读关键代码文件（验证问题原因）
- [ ] 2e. 给出分类结论（展示给用户，等待确认）
- [ ] 2f. 用户确认 OK 后执行回填（Sheet + Jira 评论）
- [ ] 2g. 验证回填成功（readSpreadsheet 确认值正确）

2a. Read Jira details

curl -s -u "$EMAIL:$TOKEN" \
  "$JIRA_BASE/rest/api/3/issue/$KEY?fields=summary,status,assignee,description,comment,attachment,labels" \
  -o /tmp/$KEY.json

Parse with python: see reference/templates.md。

2b. Download and view attachments

# 必须加 -L（Jira 附件 URL 有重定向）
curl -s -L -u "$EMAIL:$TOKEN" "$ATTACHMENT_CONTENT_URL" -o /tmp/filename.ext

Image: use Read tool to view, after downloading check file size (<1KB = failed)
Excel: parse with python + openpyxl (focus on detailed content and expected value fields)
No attachment may be skipped

2c. Search code

Determine search keywords based on the feature name/object system in the Issue:

Grep(pattern="关键词", path="$REPO_PATH", include="*.{extensions}")

2d. Read key code

After finding the relevant file, use Read to read the core logic and verify:

Does the problem actually exist? (Or was it misjudged by the tester?)
Has the fix already been implemented?
Could a batch job cause data regression?

2e. Give a classification conclusion

Must wait for user confirmationbefore writing back. Display as a table (column names and candidate values are read from configuration):

Field	Value	Basis
[Column name]	[One of the candidate values]	[One-sentence explanation]

Add detailed analysis text (according to the configured analysis_format）。

2f. Write back

After user confirmation, do three things:

Sheet batchWrite — Classification column
Sheet writeSpreadsheet + formatCells — Analysis conclusion column + background color
Jira POST comment — ADF format + @mention

For templates see reference/templates.md。

2g. Verify write-back

readSpreadsheet(range=f"{tab}!{col}{row}")  # 确认每个回填的值正确

Step 3: Quick channel

Scenario	Handling method	Analysis conclusion	Background color
Duplicate	Mark and reference the other party's number	`重複単 [引用编号]。[简述]`	done color
ON HOLD	Record the reason for waiting	`ON HOLD。[等待谁/什么]`	on_hold color
Non-BUG confirmed	Brief explanation	`対応不要。[原因]`	done color

Quick items still need to have Jira read to confirm status, butdo not require full code verification。

Step 4: Progress persistence

After processing each item, update /tmp/defect-progress.json。

Step 5: Report generation

After all are complete, generate a markdown report: composition ratio statistics + full item list + special findings + improvement proposals.

Reference files

File	Content	When to read
reference/templates.md	Jira parsing script, ADF comment template, Sheet write-back template	When performing write-back
reference/gotchas.md	Known pitfalls and solutions	When encountering problems

Quick reference

配置文件:      /tmp/defect-config.json（首次交互采集，后续复用）
进度文件:      /tmp/defect-progress.json
Jira 搜索:     POST /rest/api/3/search/jql（旧 /search 已废弃）
curl 必须:     -o /tmp/xxx.json（rtk 过滤） + -L（附件重定向）
回填后验证:    readSpreadsheet 确认值正确
核心铁律:      读 Jira + 看附件 + 查代码 + 等确认 → 才能回填

About this skill

Trigger Conditions

Core Principles

SOP Workflow

Step 0: Initialization (first use for each project)

0a. Jira Configuration

0b. Google Sheet Configuration

0c. Code Repository Configuration

0d. Backfill Rule Configuration

0e. Notification Target Configuration

0f. Write Configuration File

Step 1: Load Configuration & Get the Pending List

Step 2: Item-by-item analysis (core loop)

2a. Read Jira details

2b. Download and view attachments

2c. Search code

2d. Read key code

2e. Give a classification conclusion

2f. Write back

2g. Verify write-back

Step 3: Quick channel

Step 4: Progress persistence

Step 5: Report generation

Reference files

Quick reference