wulf-pulse/scripts/diagnose-ticket-varchar-overflow.ts

/**
 * diagnose-ticket-varchar-overflow.ts
 *
 * One-shot: identify Autotask Tickets whose string fields exceed the
 * varchar() limits declared on the local postgres `tickets` table.
 *
 * Why: every full tickets sync since 2026-05-21 16:14 fails with
 *   `value too long for type character varying(255)`
 * during bulkUpsert. The pg error doesn't name the column, so we replicate
 * the real sync's Autotask query EXACTLY (createDate filter, no IncludeFields)
 * and walk the response checking every string field on every record.
 *
 * Stops after first 10 violations found.
 *
 * Usage:
 *   npx tsx scripts/diagnose-ticket-varchar-overflow.ts
 */

import { config } from 'dotenv';
import { resolve } from 'path';

config({ path: resolve(__dirname, '../.env.local') });

const API_BASE = process.env.AUTOTASK_API_URL!;
const USERNAME = process.env.AUTOTASK_USERNAME!;
const SECRET = process.env.AUTOTASK_SECRET!;
const INT_CODE = process.env.AUTOTASK_API_INTEGRATION_CODE!;

if (!API_BASE || !USERNAME || !SECRET || !INT_CODE) {
  console.error('Missing Autotask credentials in env (.env.local)');
  process.exit(1);
}

// Postgres tickets table varchar column limits (keep in sync with schema).
// Autotask camelCase field name -> postgres column limit.
const FIELD_LIMITS: Record<string, number> = {
  title: 255,
  purchaseOrderNumber: 100,
  ticketNumber: 100,
  changeInfoField1: 255,
  changeInfoField2: 255,
  changeInfoField3: 255,
  changeInfoField4: 255,
  changeInfoField5: 255,
};

function authHeaders(): Record<string, string> {
  return {
    Username: USERNAME,
    Secret: SECRET,
    APIIntegrationcode: INT_CODE,
    'Content-Type': 'application/json',
    Accept: 'application/json',
  };
}

interface Violation {
  ticketId: number;
  ticketNumber?: string;
  field: string;
  length: number;
  limit: number;
  preview: string;
  createDate?: string;
}

const MAX_VIOLATIONS = 10;

async function main() {
  const since = new Date();
  since.setUTCFullYear(since.getUTCFullYear() - 2);
  const sinceIso = since.toISOString();

  console.log(`Scanning Tickets with createDate >= ${sinceIso} (matches real sync filter exactly)`);
  console.log(`Field limits: ${JSON.stringify(FIELD_LIMITS)}`);
  console.log(`Will also report ANY string field >255 chars even if not in known limit list.`);
  console.log('');

  // Real sync sends ONLY {MaxRecords, filter} — no IncludeFields. Match exactly.
  const baseBody = {
    MaxRecords: 500,
    filter: [{ field: 'createDate', op: 'gte', value: sinceIso }],
  };

  const violations: Violation[] = [];
  const unknownFieldOverflows: Violation[] = [];
  let totalScanned = 0;
  let pageIdx = 0;

  let url: string | null = `${API_BASE}/Tickets/query`;
  while (url) {
    pageIdx += 1;
    const res: Response = await fetch(url, {
      method: 'POST',
      headers: authHeaders(),
      body: JSON.stringify(baseBody),
    });
    if (!res.ok) {
      const t = await res.text();
      throw new Error(`Autotask query failed (page ${pageIdx}): ${res.status} ${t.slice(0, 500)}`);
    }
    const data = await res.json();
    const items: any[] = data.items || [];
    totalScanned += items.length;

    for (const rec of items) {
      for (const [field, v] of Object.entries(rec)) {
        if (typeof v !== 'string') continue;
        const limit = FIELD_LIMITS[field];
        if (limit !== undefined && v.length > limit) {
          violations.push({
            ticketId: rec.id,
            ticketNumber: rec.ticketNumber,
            field,
            length: v.length,
            limit,
            preview: v.slice(0, 100) + (v.length > 100 ? '…' : ''),
            createDate: rec.createDate,
          });
        } else if (limit === undefined && v.length > 255) {
          // Any other string field >255 (could explain why we missed it earlier).
          unknownFieldOverflows.push({
            ticketId: rec.id,
            ticketNumber: rec.ticketNumber,
            field,
            length: v.length,
            limit: -1,
            preview: v.slice(0, 100) + '…',
            createDate: rec.createDate,
          });
        }
      }
    }

    process.stdout.write(
      `  page ${pageIdx}: scanned ${items.length} (total ${totalScanned}, hits known=${violations.length} other>255=${unknownFieldOverflows.length})\n`
    );

    if (violations.length >= MAX_VIOLATIONS) {
      console.log(`\nReached ${MAX_VIOLATIONS} known-field violations, stopping early.`);
      break;
    }

    url = data.pageDetails?.nextPageUrl || null;
  }

  console.log('');
  console.log(`Scan ended. Pages: ${pageIdx}. Tickets scanned: ${totalScanned}.`);
  console.log(`Violations on KNOWN columns: ${violations.length}`);
  console.log(`Other string fields >255 chars: ${unknownFieldOverflows.length}`);
  console.log('');

  if (violations.length === 0 && unknownFieldOverflows.length === 0) {
    console.log('NO violations found. The offending record may have been modified since the failed sync.');
    process.exit(0);
  }

  if (violations.length > 0) {
    const byField: Record<string, { count: number; maxLen: number }> = {};
    for (const v of violations) {
      const b = byField[v.field] || { count: 0, maxLen: 0 };
      b.count += 1;
      b.maxLen = Math.max(b.maxLen, v.length);
      byField[v.field] = b;
    }
    console.log('Known-column violations by field:');
    for (const [field, { count, maxLen }] of Object.entries(byField)) {
      console.log(`  ${field.padEnd(24)} count=${count}  maxLen=${maxLen}  limit=${FIELD_LIMITS[field]}`);
    }
    console.log('');
    console.log('First 10 violations:');
    for (const v of violations.slice(0, 10)) {
      console.log(
        `  ticket ${v.ticketId} (${v.ticketNumber ?? '?'}) field=${v.field} length=${v.length} createDate=${v.createDate}`
      );
      console.log(`    preview: ${v.preview.replace(/\s+/g, ' ')}`);
    }
  }

  if (unknownFieldOverflows.length > 0) {
    console.log('');
    console.log('Other (unmapped) string fields >255 chars — these would NOT cause the postgres error but worth noting:');
    const byOther: Record<string, number> = {};
    for (const v of unknownFieldOverflows) byOther[v.field] = (byOther[v.field] || 0) + 1;
    for (const [f, c] of Object.entries(byOther)) console.log(`  ${f.padEnd(28)} count=${c}`);
  }
}

main().catch((err) => {
  console.error('FATAL:', err);
  process.exit(1);
});