Jira QA Status Summary – Automation

Automatically fetches Jira issue counts and generates a formatted Excel report that matches the QA Status Summary table format used in daily PDF email reports.

---

Quick Start

# 1. Install dependencies (exceljs is already added)
npm install

# 2. Set up environment variables
cp .env.example .env.local
# Edit .env.local and fill in your Jira credentials

# 3. Start the dev server
npm run dev

Open http://localhost:3000 — you will be redirected to the dashboard automatically.

---

Environment Variables

Copy .env.example to .env.local and fill in:

Variable	Required	Description
JIRA_EMAIL	✅	Your Atlassian account email
JIRA_API_TOKEN	✅	API token from id.atlassian.com/manage-profile/security/api-tokens
JIRA_BASE_URL	✅	e.g. https://your-org.atlassian.net
JIRA_PROJECT_KEY	✅	Jira project key, e.g. BJB_E2U5
JIRA_MODULE_FIELD	—	Field used for grouping (default: component)
JIRA_PRODUCT_COMPONENT	—	Component name for Product issues (default: Product)
JIRA_PLATFORM_COMPONENT	—	Component name for Platform issues (default: Platform)
JIRA_MODULES	—	Comma-separated module list; omit to fetch dynamically

---

How to Get a Jira API Token

1. Go to id.atlassian.com/manage-profile/security/api-tokens
2. Click Create API token
3. Copy the token and paste it into JIRA_API_TOKEN in .env.local

---

Project Structure

jira_automation/
├── app/
│   ├── api/
│   │   └── export-jira-excel/
│   │       └── route.ts          ← GET endpoint; returns .xlsx file
│   ├── dashboard/
│   │   └── page.tsx              ← Dashboard UI (Preview + Download)
│   └── page.tsx                  ← Root redirect → /dashboard
├── config/
│   └── env.ts                    ← Lazy env var validation
├── services/
│   ├── jqlQueries.ts             ← JQL query builders
│   └── jiraService.ts            ← Jira REST API v3 client
├── logic/
│   └── issueCalculator.ts        ← Business logic & data aggregation
├── utils/
│   └── excelGenerator.ts         ← ExcelJS workbook builder
└── .env.example                  ← Template — copy to .env.local

---

API Endpoint

GET /api/export-jira-excel

Downloads the Excel file (.xlsx).

GET /api/export-jira-excel?preview=1

Returns JSON instead of a file — useful for debugging:

{
  "ok": true,
  "projectKey": "BJB_E2U5",
  "generatedAt": "2026-04-06T...",
  "modules": [ { "module": "GRC", "internalIssues": 7, ... } ],
  "commonIssuesRow": { "commonPlatformIssues": 5, ... },
  "totalRow": { "internalIssues": 33, ... }
}

---

Excel Output

File name: jira_issue_summary_<PROJECT>_<DATE>.xlsx

Sheet: QA Status Summary

Column	Source
Module	Fetched from Jira
Assignee	Manual entry (yellow)
CEC Status	Manual entry (yellow)
Regression	Manual entry (yellow)
Internal Issues	JQL: component NOT IN (Platform, Product)
Open Internal Issues	Above + status ≠ Closed
Product Issues	JQL: component = Product
Open Product Issues	Above + status ≠ Closed
Platform Issues	JQL: component = Platform
Open Platform Issues	Above + status ≠ Closed
Common Platform Issues	Platform issues spanning multiple modules
Open Common Platform Issues	Above + status ≠ Closed
Total Issues Raised	All issues in module
Total Issues Closed	status = Closed
Open Issues Remarks	Manual entry (yellow)

Special rows:

• Common Issues – platform issues with no assigned module
• Total – sums of all numeric columns (bold, highlighted)

---

Jira Field Mapping Notes

Default (component field)

By default the app groups issues by Jira Component. Each component becomes a Module row. The special Product and Platform component names are used to classify issues.

Custom field

If your project uses a custom field for Module, set:

JIRA_MODULE_FIELD=customfield_10020

The service will page through all issues and extract unique values from that field.

Hard-coded module list

To skip dynamic discovery and always include a fixed list:

JIRA_MODULES=GRC,ISM,GMS/GMC,QSX,LSM,CMP

---

Architecture Notes

• Parallel JQL queries – All 10 count queries per module run in parallel via Promise.all, keeping total fetch time proportional to the slowest single module rather than the sum of all modules.
• maxResults: 0 – Every count query uses maxResults: 0 so Jira returns only the total without transferring issue data.
• Lazy config – Environment variables are validated at first request, not at module load, so next build works without a .env.local file.
• force-dynamic – The API route is marked dynamic = "force-dynamic" to prevent Next.js from attempting to pre-render it at build time.