# Jira → BigQuery (AI Classification)

Fetch Jira issues, classify them with Gemini, and load structured results into BigQuery.

This repo provides a small CLI plus scripts to run an Asia/Shanghai Jira sync pipeline.
It favors simple, explicit steps over magic.


## Pipeline Overview

- Fetch: pull raw issues from Jira and clean fields.

- Classify: add AI predictions (direct or batch) using Gemini.

- Merge: transform the classified issues into BigQuery row shape.

- Upload: load rows to a staging table and MERGE into the target table.


## Requirements

- Node.js 22.x and pnpm 10.x.

- Jira credentials: `JIRA_HOST`, `JIRA_USER_EMAIL`, `JIRA_API_TOKEN`.

- Gemini API key for classification: `GOOGLE_GENERATIVE_AI_API_KEY` (or pass `--api-key`).

- Google Cloud auth for BigQuery upload: either `GOOGLE_APPLICATION_CREDENTIALS` (SA JSON) or GitHub OIDC in CI.


## Setup

1) Install dependencies

```bash
pnpm install
```

2) Build TypeScript

```bash
pnpm build
```

3) Configure environment

```bash
cp .env.example .env
# Fill in JIRA_* and GOOGLE_GENERATIVE_AI_API_KEY
```


## Quickstart (Direct Mode)

Direct mode is simple and works well for scheduled sync runs.
It classifies each issue with the online Gemini API.

- 1) Fetch and clean issues

```bash
pnpm exec tsx lib/cli.ts fetch \
  -p TCOC,APID,FRM,ST \
  --from 2025-09-01 \
  --to   2025-09-02 \
  -o ./data/jira_cleaned.json
```

- 2) Classify with Gemini (direct)

```bash
pnpm exec tsx lib/cli.ts classify direct \
  -i ./data/jira_cleaned.json:issues \
  -p prompt/system_prompt_batch.md \
  -o ./data/jira_classified.json \
  --model-id gemini-3.7-flash \
  --concurrency 8
```

Notes:
- Provide `--api-key` or set `GOOGLE_GENERATIVE_AI_API_KEY`.
- Input format is `file.json:field` and the field must be an array.

- 3) Transform to BigQuery row format

```bash
pnpm exec tsx lib/cli.ts merge \
  -i ./data/jira_classified.json \
  -o ./data/bigquery_data.json
```

- 4) Upload to BigQuery

```bash
pnpm exec tsx lib/cli.ts upload \
  -i ./data/bigquery_data.json \
  --project-id gcp-cp-bigquery-dev \
  --dataset jira \
  --table ticket
```

Tip: use `--dry-run` to print rows without uploading.


## Batch Mode (Optional)

Batch mode uses Gemini’s batch APIs via the `gemini-batch` helper.
Use this when you need offline, large-scale classification and have a batch quota.

- Prepare batch input (JSONL)

```bash
pnpm exec tsx lib/cli.ts prepare \
  -i ./data/jira_cleaned.json \
  -p prompt/system_prompt_batch.md \
  -o ./data/jira_batch_input.jsonl
```

- Submit job, check status, and download results

```bash
pnpm exec tsx lib/cli.ts classify submit  -i ./data/jira_batch_input.jsonl -o ./data/jira_last_job.json
pnpm exec tsx lib/cli.ts classify status  -i ./data/jira_last_job.json --persist
pnpm exec tsx lib/cli.ts classify download -i ./data/jira_last_job.json -o ./data/jira_results.jsonl
```

Note: The `merge` command expects a JSON with an `issues` array containing AI fields.
If you use batch mode, convert the JSONL results back into that structure before running `merge`.
For most use cases, direct mode is simpler.


## Sync Scripts

- Local: loads `.env`, builds if needed, then runs the sync pipeline.

```bash
./scripts/local-sync.sh
```

- CI: GitHub Actions runs every 8 hours at `00:00`, `08:00`, and `16:00` UTC, plus manual dispatch.

File: `.github/workflows/daily_jira_sync.yml`.
It uploads artifacts for every run and writes a short summary.

Sync windows use `Asia/Shanghai` because Jira interprets API JQL datetime boundaries in that timezone.
`TARGET_END_AT` is a Jira local boundary, for example `2026-04-24 00:00`.

To override the sync window in CI, use workflow inputs `target_end_at`, `window_hours`, and `projects`.


## BigQuery Schema

The target table definition lives in `sql/0.bq_init.sql`.
Run it once to create the table before uploading.

Key fields the pipeline writes:
- `ticket_key`, `project`, `ticket_link`.
- `tenant_id`, `cluster_id`, `tidb_versions`.
- `ai_generated_summary`, `status`, `created_at`, `updated_at`.
- `ai_predicted_problem_l1`, `ai_predicted_problem_l2`, `ai_problem_confidence`.
- `ai_predicted_root_cause_l1`, `ai_predicted_root_cause_l2`, `ai_predicted_root_cause_l3`, `ai_root_cause_confidence`.
- Placeholders for human verification (`verified_*`, `*_is_verified`, `*_verified_at`).

Uploads go through a temporary staging table, then a MERGE into the target table.
If MERGE fails, the SQL is saved under `data/` for debugging.


## CLI Reference

All commands run under `pnpm exec tsx lib/cli.ts ...` during development, or `node dist/cli.js ...` after build.

- `fetch`
  - `-p, --project <keys>`: comma-separated Jira projects (required).
  - `--from <YYYY-MM-DD|YYYY-MM-DD HH:mm>`, `--to <YYYY-MM-DD|YYYY-MM-DD HH:mm>`: date or datetime boundary.
  - Datetime windows use the timezone Jira applies to the API user and an exclusive `--to` boundary.
  - `-o, --output <file>`: output JSON (contains `issues`).

- `prepare`
  - `-i, --input <file>`: cleaned JSON from `fetch` (default `jira-cleaned.json`).
  - `-p, --prompt <text|path>`: prompt text or file path.
  - `-o, --output <file>`: JSONL for batch.

- `classify submit|status|download`
  - Manage Gemini batch jobs.

- `classify direct`
  - `-i, --input <file:field>`: e.g. `jira-cleaned.json:issues`.
  - `-p, --prompt <file>`: prompt file path.
  - `-o, --output <file>`: result JSON with classified `issues`.
  - `--concurrency <n>`: parallel requests (default 5).
  - `--api-key <key>` or env `GOOGLE_GENERATIVE_AI_API_KEY`.
  - `--model-id <model>`: default `gemini-3.7-flash`.

- `merge`
  - `-i, --input <file>`: classified JSON from direct/batch.
  - `-o, --output <file>`: array of rows for BigQuery.

- `upload`
  - `-i, --input <file>`: rows JSON.
  - `--project-id <id> --dataset <name> --table <name>`.
  - `--dry-run` to print instead of uploading.

Package scripts you can reuse:

```json
{
  "jira:fetch": "tsx ./lib/cli.ts fetch -p APID -o ./jira-cleaned-apid.json",
  "jira:prepare": "tsx ./lib/cli.ts prepare -p prompt/system_prompt_batch.md -i ./jira-cleaned.json:issues -o ./jira-batch-input.jsonl",
  "jira:classify:submit": "tsx ./lib/cli.ts classify submit -i ./jira-batch-input.jsonl -o ./jira-last-job.json",
  "jira:classify:status": "tsx ./lib/cli.ts classify status -i ./jira-last-job.json",
  "jira:classify:direct": "tsx ./lib/cli.ts classify direct -i ./jira-cleaned.json:issues -p prompt/system_prompt_batch.md -o ./jira-results-direct.json",
  "jira:merge": "tsx ./lib/cli.ts merge -i ./jira-results-direct.json -o ./bigquery-data.json",
  "jira:upload": "tsx ./lib/cli.ts upload -i ./bigquery-data.json --project-id gcp-cp-bigquery-dev --dataset jira --table ticket",
  "jira:sync": "TARGET_END_AT=\"2025-09-02 16:00\" WINDOW_HOURS=\"8\" JIRA_PROJECTS=\"TCOC\" ./scripts/local-sync.sh"
}
```


## Environment Variables

Required for Jira:
- `JIRA_HOST` (e.g. `https://your-domain.atlassian.net`)
- `JIRA_USER_EMAIL`
- `JIRA_API_TOKEN`

Required for Gemini:
- `GOOGLE_GENERATIVE_AI_API_KEY` (unless you pass `--api-key` to `classify direct`).

Required for BigQuery upload (any one auth method):
- Local: `GOOGLE_APPLICATION_CREDENTIALS=/path/to/sa.json`.
- CI: OIDC via `google-github-actions/auth@v2`.

Optional:
- `JIRA_API_EXPIRE_AT` (YYYY-MM-DD). The CLI warns as it approaches expiry.


## Troubleshooting

- Classification fails with API errors: reduce `--concurrency`, verify API key, and retry.

- `Field X is not an array or does not exist`: check your `-i file:field` path (must be an array of issues).

- BigQuery upload fails: re-run `upload --dry-run` to inspect rows and check that your table matches `sql/0.bq_init.sql`.

- No issues fetched: confirm project keys and Asia/Shanghai window boundaries.


## Development

- Type-check: `pnpm exec tsc --noEmit`.

- Lint: `pnpm exec biome check .`.

- Format: `pnpm exec biome format --write .`.

- Run single-file lint quickly: `pnpm exec biome check lib/ai.ts`.