JACoB PR for Issue Convert New Jira Issues into JACoB Todos

Summary:

We want to enhance our integration with Jira by converting new Jira issues into JACoB todos. The goal is to allow users to sync specific Jira boards with our application so that any new issues created in those boards are automatically converted into todos within JACoB.

Requirements:

OAuth Enhancements:
- Update the current Jira OAuth flow to collect the refresh token and the user's cloudId.
- Store the refresh token in the users table.
- Store the cloudId in the projects table, associated with the relevant project.
Settings Page Updates:
- Update the Settings page to display a dropdown of all available boards for the user's Jira domain.
- Allow the user to select a board and click a "Sync Board" button.
- When a board is selected and synced, set its isActive status to true.
Database Updates:
- Create a new issueSources table with the following fields:
- Standard fields: id, timestamps (created_at, updated_at).
- provider: Enum limited to "GitHub" or "Jira".
- projectId: Foreign key referencing the projects table.
- boardId, boardUrl, boardName: Details of the Jira board.
- isActive: Boolean indicating if the board is currently synced.
API Endpoint:
- Set up a new Jira TRPC route to handle syncing boards and fetching issues.
Periodic Issue Retrieval:
- Implement a cron job that runs hourly to fetch new issues from synced Jira boards.
- For each new issue, create a new todo and associated research in JACoB using the same workflow as for new GitHub issues.
Token Refresh Handling:
- Ensure the application can refresh expired Jira access tokens using the stored refresh tokens.

Expected Outcome:

Users can select and sync Jira boards from the Settings page.
New issues created in synced Jira boards are automatically converted into todos within JACoB.
The system handles token refresh seamlessly, ensuring continuous synchronization.

Additional Considerations:

Implement robust error handling for API requests, including retries and logging.
Be mindful of Jira's API rate limits and handle HTTP 429 responses appropriately.
Ensure the OAuth token has the necessary scopes to access board and issue data.

Here is more detailed information gathered from the internet to help with this request.

To integrate your application with Jira and allow users to select a specific board for issue analysis, follow these detailed steps:

1. Determine the User's Jira Domain

Each Jira Cloud instance is hosted under a unique subdomain in the format https://{user-domain}.atlassian.net. To identify the correct domain for API requests:

During OAuth Authorization: Capture the cloudid parameter from the redirect URL after the user authorizes your app. This cloudid corresponds to the user's Jira instance.
Retrieve Base URL Using cloudid: Use the cloudid to fetch the base URL of the user's Jira instance.

Request:

```http GET https://api.atlassian.com/ex/jira/{cloudid}/rest/api/3/serverInfo Authorization: Bearer {access_token} Accept: application/json
```
**Response**:

```json
{
"baseUrl": "https://{user-domain}.atlassian.net",
...
}
```

Extract the `baseUrl` from the response for subsequent API calls.
```

2. Retrieve Available Boards

With the baseUrl determined, fetch the list of boards accessible to the user:

Request:

```http GET {baseUrl}/rest/agile/1.0/board Authorization: Bearer {access_token} Accept: application/json



**Response**:

```json
{
  "maxResults": 50,
  "startAt": 0,
  "total": 2,
  "values": [
    {
      "id": 1,
      "self": "https://{user-domain}.atlassian.net/rest/agile/1.0/board/1",
      "name": "Sample Scrum Board",
      "type": "scrum"
    },
    {
      "id": 2,
      "self": "https://{user-domain}.atlassian.net/rest/agile/1.0/board/2",
      "name": "Sample Kanban Board",
      "type": "kanban"
    }
  ]
}
```

Present this list to the user, allowing them to select a specific board.

**3. Fetch Issues from the Selected Board**

Once a board is selected, retrieve its issues:

**Request**:

```http
GET {baseUrl}/rest/agile/1.0/board/{boardId}/issue
Authorization: Bearer {access_token}
Accept: application/json
```

Replace `{boardId}` with the ID of the selected board.

**Response**:

```json
{
  "startAt": 0,
  "maxResults": 50,
  "total": 100,
  "issues": [
    {
      "id": "10001",
      "key": "PROJ-1",
      "fields": {
        "summary": "Issue summary",
        "status": {
          "name": "To Do"
        },
        ...
      }
    },
    ...
  ]
}
```

This response provides the issues on the selected board, which you can then analyze using your AI tools.

**4. Implement Periodic Issue Retrieval**

To monitor new issues hourly:

- **Store the Timestamp of the Last Check**: Maintain the timestamp of the last successful issue retrieval.

- **Schedule Hourly Checks**: Set up a cron job to run every hour. there is already support for cron jobs in the project, look to it for examples. 

- **Fetch New Issues**: During each run, use the Jira API to search for issues created or updated since the last check.

  **Request**:

  ```http
  GET {baseUrl}/rest/api/3/search?jql=project={projectKey} AND updated >= "{lastCheckTime}"
  Authorization: Bearer {access_token}
  Accept: application/json
  ```

  Replace `{projectKey}` with the key of the project associated with the board and `{lastCheckTime}` with the stored timestamp.

**5. Analyze and Process New Issues**

For each new issue retrieved:

-** Create New Todo": Use the same workflow as the webhook for new github issues and create a new Todo + Research for the project. 

**6. Handle Authentication and Token Refresh**

Ensure your application can handle token expiration:

- **Monitor Token Expiry**: Track the expiration time of the OAuth token.

- **Refresh Tokens as Needed**: If the token is expired or about to expire, use the refresh token to obtain a new access token.

**Additional Considerations**

- **Error Handling**: Implement robust error handling for API requests, including retries and logging.

- **Rate Limiting**: Be aware of Jira's API rate limits and design your application to handle HTTP 429 responses gracefully.

- **User Permissions**: Ensure that the OAuth token has the necessary scopes to access board and issue data.

By following these steps, you can effectively integrate your application with Jira, allowing users to select boards and enabling your AI tools to analyze new issues as they arise. 
@jacob-ai-bot --skip-build

## Plan:

### Step 1: Edit `/src/app/api/auth/jira/callback/route.ts`

**Task:** Update Jira OAuth callback to store refresh token and cloudId

**Instructions:**
Modify the Jira OAuth callback handler to collect the 'refresh_token' and the user's 'cloudid' from the token response. Update the code to parse these values from the response data (ensure you request 'offline_access' scope to receive refresh tokens). Store the 'refresh_token' in the 'jiraRefreshToken' column and the 'access_token' in the 'jiraToken' column of the 'users' table for the authenticated user. Also, store the 'cloudid' in the 'jiraCloudId' column of the 'users' table.

**Exit Criteria:**
Upon successful Jira OAuth authentication, the user's 'jiraToken', 'jiraRefreshToken', and 'jiraCloudId' are stored in the 'users' table.

### Step 2: Edit `/src/server/db/tables/users.table.ts`

**Task:** Add 'jiraRefreshToken' and 'jiraCloudId' columns to Users table

**Instructions:**
Modify the 'UsersTable' definition to add two new nullable columns: a text column named 'jiraRefreshToken' to store the Jira refresh token, and a varchar(255) column named 'jiraCloudId' to store the user's Jira cloudId.

**Exit Criteria:**
'UsersTable' has new 'jiraRefreshToken' and 'jiraCloudId' columns defined.

### Step 3: Create `/src/server/db/tables/issueSources.table.ts`

**Task:** Create IssueSourcesTable definition

**Instructions:**
Create a new ORM table definition 'IssueSourcesTable' in '/src/server/db/tables/issueSources.table.ts' to represent the 'issue_sources' table. Define the following columns: 'id' as primary key, 'provider' as an enum with values 'GitHub' and 'Jira', 'projectId' as a foreign key referencing the 'projects' table, 'boardId' as varchar(255), 'boardUrl' as text, 'boardName' as text, 'isActive' as boolean, and default timestamp columns.

**Exit Criteria:**
'IssueSourcesTable' class is defined with the specified columns.

### Step 4: Create `/src/server/db/migrations/20241101000000_addJiraRefreshTokenAndCloudIdToUsers.ts`

**Task:** Create migration to add 'jiraRefreshToken' and 'jiraCloudId' columns to Users table

**Instructions:**
Create a new database migration script to add two new nullable columns to the 'users' table: a text column 'jiraRefreshToken' and a varchar(255) column 'jiraCloudId'. Ensure that the migration properly adds the columns when migrating up and removes them when migrating down.

Here's an example of adding users, make sure to use this syntax: 
import { change } from "../dbScript";

change(async (db) => {
  await db.changeTable("users", (t) => ({
    jiraToken: t.text().nullable(),
  }));
});

**Exit Criteria:**
Migration script successfully adds and removes 'jiraRefreshToken' and 'jiraCloudId' columns in 'users' table.

### Step 5: Edit `/src/app/dashboard/[org]/[repo]/settings/Settings.tsx`

**Task:** Update Settings page to allow syncing Jira boards

**Instructions:**
Modify the Settings page component to include a dropdown that displays all available Jira boards for the user's Jira domain. Use the Jira API to fetch the list of boards using the user's 'jiraToken' and 'jiraCloudId' stored in the 'users' table. Allow the user to select a board and click a 'Sync Board' button. When the user syncs a board, create a new record in the 'issue_sources' table with the 'provider' set to 'Jira', 'projectId' set to the current project, 'boardId', 'boardUrl', 'boardName' set from the selected board's information, and 'isActive' set to 'true'.

**Exit Criteria:**
Settings page displays Jira boards, allows syncing a board, and successfully stores the synced board in the 'issue_sources' table.

### Step 6: Create `/src/server/api/routers/jira.ts`

**Task:** Create TRPC router for Jira integration

**Instructions:**
Create a new TRPC router 'jiraRouter' in '/src/server/api/routers/jira.ts'. Implement procedures to fetch Jira boards using the Jira API, using the user's 'jiraToken' and 'jiraCloudId'. Implement a procedure to sync a selected board by creating an entry in the 'issue_sources' table. Ensure proper error handling, including token refresh if the access token has expired.

**Exit Criteria:**
'jiraRouter' provides API procedures to fetch Jira boards and sync a selected board, interacting with the database and handling errors appropriately.

### Step 7: Create `/src/server/utils/jira.ts`

**Task:** Implement Jira OAuth token refresh utility

**Instructions:**
Create a new utility function 'refreshJiraAccessToken' in '/src/server/utils/jira.ts'. This function should accept a user's 'jiraRefreshToken' and exchange it for a new access token using the Jira OAuth token refresh endpoint. Update the user's 'jiraToken' and 'jiraRefreshToken' in the 'users' table with the new tokens. Ensure the function handles errors and retries as needed.

**Exit Criteria:**
'refreshJiraAccessToken' function successfully refreshes Jira access tokens and updates the 'users' table.

### Step 8: Edit `/src/server/messaging/listener.ts`

**Task:** Add periodic retrieval of new Jira issues from synced boards

**Instructions:**
In 'listener.ts', modify the cron job to include logic to fetch new Jira issues from synced boards every hour. For each active 'issue_sources' record with 'provider' as 'Jira', use the stored 'boardId', 'boardUrl', and the user's 'jiraToken' and 'jiraCloudId' to fetch issues from the Jira API. Implement token refresh logic using 'refreshJiraAccessToken' if necessary. For each new issue, use the existing workflow to create a new todo and associated research item in JACoB. Handle rate limiting and errors appropriately.

**Exit Criteria:**
Cron job successfully retrieves new Jira issues from synced boards, creates todos and research items in JACoB, and handles token refresh and errors.

### Step 9: Edit `/src/server/utils/todos.ts`

**Task:** Update 'getOrCreateTodo' function to handle Jira issues

**Instructions:**
Modify the 'getOrCreateTodo' function in '/src/server/utils/todos.ts' to support creating todos from Jira issues. Adjust the function to accept Jira issue data, including mapping fields like 'issue.key', 'issue.fields.summary', and 'issue.fields.description' from the Jira issue structure. Ensure that the 'projectId' is correctly associated, and that the todo and associated research are created using the existing workflow.

**Exit Criteria:**
'getOrCreateTodo' function can create todos from Jira issues, integrating with the existing system.

### Step 10: Create `/src/server/db/migrations/20241101000001_createIssueSourcesTable.ts`

**Task:** Create migration to add 'issue_sources' table

**Instructions:**
Create a new database migration script in '/src/server/db/migrations' to create the 'issue_sources' table with the specified columns from the 'IssueSourcesTable' definition. Ensure that 'provider' is an enum with values 'GitHub' and 'Jira', 'projectId' is a foreign key, and the migration handles adding and removing the table on migrate up and down.

Here's an example, follow this almost exactly but change the values:
import { change } from "../dbScript";

const researchTypeValues = [
  "ResearchCodebase",
  "ResearchAgent",
  "AskProjectOwner",
  "ReasearchComplete",
] as [string, ...string[]];

change(async (db, up) => {
  if (up) {
    await db.createEnum("research_type_values", researchTypeValues);
  }

  await db.createTable("research", (t) => ({
    id: t.identity().primaryKey(),
    todoId: t.integer().foreignKey("todos", "id", {
      onDelete: "CASCADE",
    }),
    issueId: t.integer(),
    type: t.enum("research_type_values"),
    question: t.text(),
    answer: t.text(),
    ...t.timestamps(),
  }));

  if (!up) {
    await db.dropEnum("research_type_values", researchTypeValues);
  }
});

**Exit Criteria:**
Migration script successfully creates and drops the 'issue_sources' table with the specified columns.

jacob-ai-bot / jacob

JACoB PR for Issue Convert New Jira Issues into JACoB Todos #245

Summary:

Next Steps