Created
October 27, 2025 22:22
-
-
Save Sdy603/346f8de273ceaee1195d1f464692e368 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // incidents_upsert.js | |
| // Classic Node.js (CommonJS) | |
| const { Client } = require('pg'); | |
| const axios = require('axios'); | |
| // Config | |
| const DEBUG = process.env.DEBUG === 'true'; // set to "true" to skip POSTs | |
| const DB_URL = process.env.DX_DB_URL; // e.g. postgres://user:pass@host:5432/dbname | |
| const API_URL = 'https://activecampaign.getdx.net/api/incidents.upsert'; | |
| const API_TOKEN = process.env.DX_API_TOKEN; // bearer token | |
| if (!DB_URL) { | |
| console.error('Missing env DX_DB_URL'); | |
| process.exit(1); | |
| } | |
| if (!API_TOKEN && !DEBUG) { | |
| console.error('Missing env DX_API_TOKEN'); | |
| process.exit(1); | |
| } | |
| const client = new Client({ connectionString: DB_URL }); | |
| function toIsoOrNull(value) { | |
| if (!value) return null; | |
| const d = new Date(value); | |
| return isNaN(d.getTime()) ? null : d.toISOString(); | |
| } | |
| // Accepts string, CSV, JSON array, or single value; returns string[] | |
| function normalizeServices(svc) { | |
| if (!svc) return []; | |
| if (Array.isArray(svc)) return svc.map(String).map(s => s.trim()).filter(Boolean); | |
| const s = String(svc).trim(); | |
| if (!s) return []; | |
| // Split on common delimiters, but if it looks like JSON array try to parse first | |
| if ((s.startsWith('[') && s.endsWith(']')) || (s.startsWith('"') && s.endsWith('"'))) { | |
| try { | |
| const parsed = JSON.parse(s); | |
| if (Array.isArray(parsed)) return parsed.map(String).map(x => x.trim()).filter(Boolean); | |
| if (parsed) return [String(parsed).trim()].filter(Boolean); | |
| } catch (_) { | |
| // fall through to delimiter split | |
| } | |
| } | |
| return s.split(/[,;|]/).map(x => x.trim()).filter(Boolean); | |
| } | |
| async function run() { | |
| try { | |
| await client.connect(); | |
| const query = ` | |
| SELECT DISTINCT | |
| ji.key AS reference_id, | |
| cf_service.value AS services, | |
| ji.summary AS name, | |
| cf_start_date.value AS started_at, | |
| cf_resolution_date.value AS finished_at, | |
| ji.source_url, | |
| CASE | |
| WHEN cf_itsm_change.value IS NOT NULL THEN | |
| jsonb_build_object('ITSM Change Related', cf_itsm_change.value) | |
| ELSE | |
| jsonb_build_object() | |
| END AS metadata | |
| 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_incident_severity | |
| ON cf_incident_severity.issue_id = ji.id | |
| AND cf_incident_severity.custom_field_id = 21 | |
| LEFT JOIN jira_issue_custom_field_values cf_start_date | |
| ON cf_start_date.issue_id = ji.id | |
| AND cf_start_date.custom_field_id = 723 | |
| LEFT JOIN jira_issue_custom_field_values cf_resolution_date | |
| ON cf_resolution_date.issue_id = ji.id | |
| AND cf_resolution_date.custom_field_id = 449 | |
| LEFT JOIN jira_issue_custom_field_values cf_service | |
| ON cf_service.issue_id = ji.id | |
| AND cf_service.custom_field_id = 481 | |
| LEFT JOIN jira_issue_custom_field_values cf_itsm_change | |
| ON cf_itsm_change.issue_id = ji.id | |
| AND cf_itsm_change.custom_field_id = 429 | |
| LEFT JOIN dx_users du ON LOWER(du.email) = LOWER(ju.email) | |
| WHERE jit.id = 273 | |
| AND cf_service.value IS NOT NULL | |
| AND ji.key NOT IN (SELECT source_id FROM incidents) | |
| ORDER BY cf_start_date.value DESC; | |
| `; | |
| const { rows } = await client.query(query); | |
| console.log(`Fetched ${rows.length} incident candidates`); | |
| for (const row of rows) { | |
| const payload = { | |
| reference_id: row.reference_id, | |
| services: normalizeServices(row.services), | |
| name: row.name, | |
| started_at: toIsoOrNull(row.started_at), | |
| finished_at: toIsoOrNull(row.finished_at), | |
| source_url: row.source_url || null, | |
| metadata: row.metadata || {}, | |
| }; | |
| console.log(`Prepared payload for ${row.reference_id}: ${JSON.stringify(payload)}`); | |
| if (DEBUG) { | |
| console.log(`DEBUG on, skipping POST for ${row.reference_id}`); | |
| continue; | |
| } | |
| try { | |
| const resp = await axios.post(API_URL, payload, { | |
| headers: { | |
| Authorization: `Bearer ${API_TOKEN}`, | |
| 'Content-Type': 'application/json', | |
| }, | |
| timeout: 30000, | |
| validateStatus: s => s >= 200 && s < 300, | |
| }); | |
| console.log(`Upserted ${row.reference_id}: ${resp.status}`); | |
| } catch (err) { | |
| const status = err.response ? err.response.status : 'no-status'; | |
| const data = err.response ? JSON.stringify(err.response.data) : err.message; | |
| console.error(`POST failed for ${row.reference_id} status=${status} body=${data}`); | |
| } | |
| } | |
| } catch (e) { | |
| console.error('Fatal error', e); | |
| } finally { | |
| await client.end(); | |
| } | |
| } | |
| run(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment