DEV Community

Cover image for Debugging & Refactoring Spaghetti Code - The A.I Special 🤖
Brandon Lozano
Brandon Lozano

Posted on

Debugging & Refactoring Spaghetti Code - The A.I Special 🤖

#ai #programming #dataengineering #api

Given the fact that people have varying and mostly static opinions on Artificial Intelligence, I feel like I must start by stating my own. The following is merely opinion based on my experience and overall understanding of the world and technology.

Short & Sweet - My Opinion on A.I

I think every engineer should utilize A.I to some extent: writing docs, debugging, comparing technologies, and writing code are all good use cases. However, it is not, nor will it be, the end all and be all for great software. We do not have the compute nor the data for A.I to reach Artificial General Intelligence (AGI) - where A.I becomes human-like. The people who are selling the dream of A.I doing everything while we hang back like the people in WALL-E are directly benefiting from the stock surges caused by this speculation.

WALL-E People In Chairs

Problem Statement

Once upon a time, not too long ago I was handed over a project for Data APIs that were responsible for collecting and organizing all sales data for the company. The APIs had a ~80% success rate, not great for sales data that would mold and shift marketing and sales tactics.

On top of the bug infestation, the application was hosted in PipeDream which basically provides an env for your code to run, and handles things like gateways. Awesome for viability testing, but not great for real world applications. There was essentially no version control, config access, or proper way to manage dependencies.

Why All Of The Issues?

All of the architecture, code, and schema was built while heavily relying on A.I, and when I say heavily rely I mean, A.I drove - the old employee hit yes.

Overall there were around 50 data points that needed to be tracked, to get them there were countless if else statements, nested for loops, and API calls for each individual order instead of batch calls.

The project was the utter representation of spaghetti code - but I honestly learned more about APIs, debugging, databases, and clean code during this project than any other single project.

To Gain An Understanding

Plainly looking at the code and digesting the limited documentation did not provide enough context to give me any true indication as to what data points were required and where they came from.

I at least had a general understanding that sales data originated in Shopify, we were utilizing HubSpot to associate sales to team members, PostgreSQL was our combined source of truth, and the whole thing relied on PipeDream APIs. I started from the DB viewpoint to understand how data should be represented (if the pipeline ran successfully).

Below is the example schema for the deals table, this is the main entry point for tracking all sales

CREATE TABLE IF NOT EXISTS DEALS (
  deal_number INT,
  price INT,
  disc_price INT,
  shipped BOOLEAN,
  ...
);
Enter fullscreen mode Exit fullscreen mode

Once I thoroughly understood data requirements, I began to use pen and paper to map out data points, their origin source, and data type that was preferred. This allowed me to make very rough sketches of the data pipeline. To start, the entire system was of a very ambiguous state - no beginning and no end.

Console Line Debugging

A real advantage here was that Pipedream saved each event with its full request payload for 30 days, meaning I could replay the exact same request through the broken pipeline repeatedly while making fixes. This made isolating failures significantly faster than traditional debugging — rather than waiting for real events to reproduce a bug, I could trigger it on demand.

Below is an example of what the code base looked like.

// Rough Example of what we were dealing with
const { Client } = require('pg');
export default function EventHandler() {
  const fetchShopifyOrders = async () => {
    const response = await fetch(process.env.SHOPIFY_URL, {
      headers: {
        API_KEY: process.env.SHOPIFY_API_KEY,
      }
    });
    return response;
  }

  const fetchHubspotData = async (orderId) => {
    const response = await fetch(process.env.HUBSPOT_URL, {
      headers: {
        API_KEY: process.env.HUBSPOT_API_KEY,
      },
      body: {
        orderNumber: orderId
      }
    });
    return response;
  }

  const client = new Client({
    user: 'your_user',
    host: 'localhost',
    database: 'your_db',
    password: 'your_password',
    port: 5432,
  });

  const orders = await fetchShopifyOrders();

  for (const order of orders) {
    const orderNumber = order.details.orderNumber;
    let currency = '';
    let price = 0;
    let delivered;
    let hubspotId;
    let hubspotOwner;
    // image 30 more variable likes this 

    if (order.details.country === 'us') {
      currency = 'USD';
    } else if (order.details.country === 'ca') {
      currency = 'CAD';
    } else if (order.details.country === 'au') {
      currency = 'AUD';
    }

    if (order.details.discountPrice) {
      price = order.details.discountPrice;
    } else {
      price = order.details.price;
    }

    if (order.details.deliveredStatus === 'delivered') {
      delivered = true;
    } else {
      delivered = false;
    }

    // and 30 more if else statements to assign variable values

    const hubspotData = await fetchHubspotData(orderNumber);

    if (hubspotData.dealOwner !== '') {
      hubspotOwner = hubspotData.dealOwner;
    }

    if (hubspotData.id) {
      hubspotId = hubspotData.id
    }

    await client.connect();

    const query = `INSERT INTO DEALS (orderNumber, currency, price, delivered, hubspotId, hubspotOwner) VALUES (${orderNumber}, ${currency}, ${price}, ${delivered}, ${hubspotId}, ${hubspotOwner}) RETURNING *`
    await client.query(query);
  }
}
Enter fullscreen mode Exit fullscreen mode

Issues with the above

  • aysnc typo would crash immediately at runtime
  • fetchShopifyOrders called without await — iterating a Promise
  • client.connect() called on every loop iteration, never released
  • Raw template literal SQL query is a textbook SQL injection vulnerability
  • Hardcoded DB credentials in the source code
  • No error handling anywhere — a single failure crashes the entire batch
  • Dozens of verbose if/else chains that could each be a single expression
  • Variables declared and mutated individually per order with no shared shape or structure

To The Refactor

After quite a bit of work and understanding, I was able to get to a place like this:

  • Batch cleaned all order data first with a single map before any async work
  • Bulk fetched all HubSpot data in parallel with Promise.all instead of sequential awaits in a loop
  • Replaced verbose if/else chains with a CURRENCY_MAP lookup and ternary operators
  • Moved DB credentials to environment variables
  • Introduced a single pool connection reused across all inserts, released in a finally block
  • Parameterized SQL queries eliminating injection risk
  • RETURNING id gives back insert IDs for debugging and reduces load for returning all data - which is not necessary
  • Wrapped everything in try/catch for graceful error handling 
import { Pool } from 'pg';

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

const CURRENCY_MAP = {
  us: 'USD',
  ca: 'CAD',
  au: 'AUD',
};

export default async function EventHandler() {
  try {
    const orders = await fetchShopifyOrders();

    const cleanedOrders = orders.map((order) => ({
      orderNumber: order.details.orderNumber,
      currency: CURRENCY_MAP[order.details.country] ?? 'USD',
      price: order.details.discountPrice ?? order.details.price,
      delivered: order.details.deliveredStatus === 'delivered',
      hubspotId: null,
      hubspotOwner: null,
    }));

    const hubspotResults = await Promise.all(
      cleanedOrders.map((order) => fetchHubspotData(order.orderNumber))
    );

    const enrichedOrders = cleanedOrders.map((order, i) => ({
      ...order,
      hubspotId: hubspotResults[i].id ?? null,
      hubspotOwner: hubspotResults[i].dealOwner || null,
    }));

    const client = await pool.connect();

    try {
      const results = await Promise.all(
        enrichedOrders.map((order) =>
          client.query(
            `INSERT INTO DEALS (orderNumber, currency, price, delivered, hubspotId, hubspotOwner)
             VALUES ($1, $2, $3, $4, $5, $6)
             RETURNING id`,
            [order.orderNumber, order.currency, order.price, order.delivered, order.hubspotId, order.hubspotOwner]
          )
        )
      );

      return results.map((r) => r.rows[0].id);
    } finally {
      client.release();
    }
  } catch (error) {
    console.error('EventHandler failed:', error);
    throw error;
  }
}

const fetchShopifyOrders = async () => {
  const response = await fetch(process.env.SHOPIFY_URL, {
    headers: { API_KEY: process.env.SHOPIFY_API_KEY },
  });
  return response;
};

const fetchHubspotData = async (orderId) => {
  const response = await fetch(process.env.HUBSPOT_URL, {
    headers: { API_KEY: process.env.HUBSPOT_API_KEY },
    body: { orderNumber: orderId },
  });
  return response;
};
Enter fullscreen mode Exit fullscreen mode

The above code examples are brief indications of what the code-base looked like, in reality there were five other scripts and 5 other DB tables that were being populated. Each of these scripts contained at least 400 lines of spaghetti code. All code followed the same patterns above and each had to be rebuilt from the same foundation up.

The irony wasn't lost on me that I used AI tools during the refactor — for documentation, sanity checking logic, and comparing approaches. The difference was that I was driving. Understanding the problem space deeply enough to catch what the AI got wrong, knowing when to trust it and when to question it, is the skill. The code doesn't lie, and an 80% success rate on sales data will always find its way back to you.

Top comments (0)