Fetches JIRA incidents from DB and POSTs to the Incidents API

fetch_jira_incidents_and_POST_incidents_API.js

const { Client } = require('pg');
const axios = require('axios');

// Set debug mode
const debug = true;  // Set to false to make actual API calls

// Database connection string
const connectionString = [CONNECTION STRING FROM DX]

// Create a new PostgreSQL client
const client = new Client({
  connectionString: connectionString,
});

async function runQueryAndSendRequests() {
  try {
    // Connect to the database
    await client.connect();

    // SQL query
    const query = `
      SELECT DISTINCT
        ji.key AS reference_id,  
        cf_service.value as services,
        ji.summary as name,
        ji.created_at as started_at,
        ji.completed_at as finished_at,
        ji.source_url
      FROM 
        jira_issues ji
      JOIN 
        jira_issue_types jit ON ji.issue_type_id = jit.id
      JOIN 
        jira_projects jp ON ji.project_id = jp.id
      LEFT JOIN 
        jira_users ju ON ji.user_id = ju.id
      LEFT JOIN 
        jira_issue_custom_field_values cf_service ON cf_service.issue_id = ji.id 
        AND cf_service.custom_field_id = 406
      LEFT JOIN dx_users du ON LOWER(du.email) = LOWER(ju.email)
       WHERE jit.id = 1053
       AND ji.completed_at IS NOT NULL
       AND cf_service IS NOT NULL
       AND ji.key NOT IN (
    SELECT source_id
    FROM incidents
  )
      ORDER BY 
        ji.created_at DESC;
    `;

    // Execute the query
    const res = await client.query(query);

    // Iterate over each row in the result
    for (const row of res.rows) {
      const apiPayload = {
        reference_id: row.reference_id,
        services: row.services ? row.services.split(',') : [], // Assuming services are comma-separated
        name: row.name,
        started_at: row.started_at,
        finished_at: row.finished_at,
        source_url: row.source_url
      };

      // Log the payload for debugging
      console.log(`Prepared payload for ${row.reference_id}:`, JSON.stringify(apiPayload, null, 2));

      if (!debug) {
        // Send POST request to the DX Incidents API
        try {
          const apiResponse = await axios.post('https://api.getdx.com/incidents', apiPayload, {
            headers: {
              Authorization: `Bearer YOUR_API_TOKEN`,  // Replace with your actual API token
              'Content-Type': 'application/json'
            }
          });

          console.log(`Successfully sent incident: ${row.reference_id}`, apiResponse.data);
        } catch (error) {
          console.error(`Error sending incident ${row.reference_id}:`, error.message);
        }
      } else {
        console.log(`Debug mode is ON. No actual API call made for ${row.reference_id}.`);
      }
    }

  } catch (err) {
    console.error('Database query error:', err.stack);
  } finally {
    // Close the database connection
    await client.end();
  }
}

// Run the function
runQueryAndSendRequests();