Chat agent on legacy CodeIgniter: a Zaandam wholesaler case

Monday morning at a Zaandam loading dock

Half past ten. The order desk at a family-owned bakery-supplies wholesaler in Zaandam has 47 unread chat messages, 18 unread emails and three phone lines blinking. A bakery in Purmerend wants to know whether the 25 kg sacks of patent flour will make the Wednesday truck. A patisserie in Hoorn is asking for 12% off the chocolate couverture if they raise the order from 60 to 100 kg. An apprentice in Zaanstad has sent a photo of a label and wants to know the artikelnummer.

The company has 21 people. Two of them work the order desk. The artikelen catalogue has lived on the same database for twelve years, inside a CodeIgniter 2.2 voorraadsysteem that the founder's nephew wrote in 2014 and that nobody has touched since. Today the chat widget on the website handles 1,840 of these questions a week, in under three seconds each, without writing a single row to the legacy database, and without ever quoting a discount above 18% on its own.

This is the story of how we wired that up, what the agent is allowed to read but never write, and where we drew the line for the accountmanagers.

The 12-year-old voorraadsysteem

The stack the agent sits on top of: PHP 5.6, CodeIgniter 2.2, MySQL 5.7, one big artikelen table with 14,200 SKUs, a separate prijsstaffel table with customer-specific tiers, and a bestelling table the company has not pruned in eleven years.

PHP 5.6 went end of life in January 2019. The PHP EOL table still shows it sitting quietly in the unsupported column. The legacy controllers use deprecated mysql_* style helpers, a custom session library, and three different ways of escaping strings depending on which developer wrote which file in which year.

We could have rewritten it. The owner did not want to. The system works, the accountmanagers know it cold, and the maandrapport ties out to the cent. The brief was to put a chat agent on top of it without changing a single legacy controller.

Read paths only, never write

Rule one, set in week one: the chat agent issues no INSERTs or UPDATEs against the legacy MySQL. Every write goes through the SAP Business One Service Layer the company already uses for ERP bookkeeping. The voorraadsysteem is read-only from the agent's perspective. That single rule kept the legacy team off our backs for the entire build.

We put a small Node service in front of a read-replica of the MySQL instance and exposed a typed API the agent can call as tools. Three endpoints, no writes, indexed reads:

// tools/artikel.ts
export const lookupArtikel = {
  name: "lookup_artikel",
  description: "Return SKU, name, unit, stock and base price for an artikelcode.",
  input_schema: {
    type: "object",
    properties: { code: { type: "string", pattern: "^[A-Z0-9-]{4,12}$" } },
    required: ["code"],
  },
  async handler({ code }: { code: string }) {
    const row = await db.one(
      `SELECT a.code, a.naam, a.eenheid, v.voorraad, a.basisprijs
         FROM artikelen a
         JOIN voorraad v ON v.artikel_id = a.id
        WHERE a.code = ? AND a.actief = 1
        LIMIT 1`,
      [code]
    );
    return row ?? { error: "not_found" };
  },
};

The schema is the contract. The agent cannot ask for "all flour SKUs starting with PA" because that tool does not exist. It can only look up a code it has already resolved from the customer's words. That constraint forces a clean retrieval step earlier in the pipeline and keeps MySQL from being asked for the world.

Keeping artikel-lookup under 480 ms

The order desk does not care that the agent is intelligent. They care that it is fast. The internal SLA we set: an artikel-lookup, end to end, including the tool call round trip, finishes inside 480 ms at the 95th percentile. Anything slower and the chat starts to feel like the old PHP backend, and the agent loses the trust it just earned.

Three things make that number reachable.

First, an in-memory cache of the full artikelen table on the agent service. 14,200 rows is nothing. We hydrate on boot and refresh every five minutes from a single SELECT against the read replica. Lookups by code resolve in microseconds before the agent ever touches MySQL.

Second, a MySQL ngram fulltext index on the naam column for fuzzy matches. "patent bloem 25kg" from a customer becomes a sub-millisecond search against the cached catalogue, with the database only consulted to confirm the candidate is still active.

Third, the prijsstaffel join is denormalised into a per-customer JSON document, refreshed nightly. The customer's tier is resolved once at the start of the conversation and pinned for the session. The agent does not re-query it on every turn.

The 18% rule and the accountmanager queue

The wholesaler's margins on bulk flour are thin. The owner has held a rule since 1998: no discount above 18% goes out without a human signing off. The accountmanagers know which customers earn 20% on couverture and which ones get a flat refusal. That tacit knowledge is not in the database. It lives in their heads.

So the agent never offers a discount above 18% on its own. The rule lives in the tool definition for the discount calculation, not in a system prompt:

// tools/kortingsafspraak.ts
export const proposeKorting = {
  name: "propose_korting",
  description:
    "Propose a discount percentage for a line. Above 18% parks the order in the accountmanager queue.",
  input_schema: {
    type: "object",
    properties: {
      artikelcode: { type: "string" },
      qty: { type: "number" },
      voorgesteld_pct: { type: "number", minimum: 0, maximum: 35 },
      klantcode: { type: "string" },
    },
    required: ["artikelcode", "qty", "voorgesteld_pct", "klantcode"],
  },
  async handler(input) {
    if (input.voorgesteld_pct > 18) {
      await queue.push("accountmanager", {
        ...input,
        reden: "korting_boven_18",
      });
      return { status: "geparkeerd", queue: "accountmanager" };
    }
    return { status: "ok", korting_pct: input.voorgesteld_pct };
  },
};

The agent literally cannot bypass the rule. If the model decides the customer really earned a 22% discount, the tool returns a parked status and the conversation pivots to "ik leg dit even voor aan je accountmanager, je hoort binnen een werkdag van ons." The accountmanager sees a clean queue with the artikel, the qty, the customer history and the model's reasoning, and approves or rejects from a simple Slack-like dashboard.

In the first eight weeks, 11.4% of conversations ended in the queue. The owner expected 30%. He was the most surprised of anyone in the office.

Handing off to SAP Business One

When a conversation does close cleanly, the agent fires a sales order against the SAP Business One Service Layer the company already uses. The SAP Business One documentation covers the Orders endpoint we hit; it accepts a clean JSON body and returns the DocEntry on success.

// integrations/sapb1.ts
async function createSalesOrder(order: ParkedOrder) {
  const res = await fetch(`${process.env.B1_BASE}/Orders`, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Cookie: await getSessionCookie(),
    },
    body: JSON.stringify({
      CardCode: order.klantcode,
      DocDate: order.datum,
      DocumentLines: order.regels.map((r) => ({
        ItemCode: r.artikelcode,
        Quantity: r.qty,
        DiscountPercent: r.korting_pct,
      })),
    }),
  });
  if (!res.ok) throw new SapError(await res.text(), res.status);
  return (await res.json()).DocEntry as number;
}

The Service Layer's session cookie expires every 30 minutes. We refresh it lazily on a 401, which sounds clean but cost us a week when the first refresh raced under load and posted two duplicate orders within the same minute. The fix was a mutex around the refresh and an idempotency key on the order metadata. Standard distributed-systems fare, but worth a callout.

What the customer sees

The chat widget on the wholesaler's website does not look like a bot. It says "Stel een vraag aan de orderbalie" and shows a small avatar of a real accountmanager. The customer types in Dutch, the agent answers in Dutch, and at no point does it announce that it is a language model.

A typical conversation: "Hoi, kunnen jullie woensdag 25 zakken patent bloem leveren in Purmerend?" The agent resolves "patent bloem" to artikelcode PAT-25KG, reads the voorraad, checks the regular Wednesday route to Purmerend, confirms a slot, and asks the customer to confirm. Three turns, eight seconds end to end, no human in the loop.

When the customer pushes on price, the agent looks up the prijsstaffel for that klantcode and offers the discount the database says they are entitled to, then stops. If the customer asks for more, the tool returns a parked status and the conversation pivots to the accountmanager.

What HN was arguing about while we built this

The week we shipped the cache layer, a thread on Hacker News asked whether anyone had replaced Claude or GPT with a local model for daily coding. It sat near the top of the front page for two days. The honest answer for an agent like this one is: partially.

The intent-matching step that maps customer phrases like "twee zakken patent" to an artikelcode runs on a small local embedding model on a single Hetzner box. It is cheap, fast, and never leaves the company's network. The reasoning step that drafts the reply, calls the tools and decides when to park an order runs on a hosted model with tool use. Mixing the two cut our per-conversation cost by a factor of four without measurable quality loss.

If you read every "local model" post on HN as a binary choice, you will pick wrong. The interesting question is which step of your pipeline can move local without breaking the others.

Eight weeks in, by the numbers

Numbers from week eight of production, taken from the agent service's own metrics:

• 1,840 chat-driven bestellingen-vragen per week
• 312 ms median artikel-lookup, 471 ms p95
• 11.4% of conversations parked in the accountmanager queue
• Zero writes to the legacy MySQL from the agent path
• Two duplicate SAP orders, both before the session-mutex fix
• One incident: a fulltext index rebuild during business hours caused a 14-minute slowdown

The order desk now handles outbound calls and exceptions. They did not lose a job. They lost the part of their job that involved typing the same answer about flour delivery 60 times a day.

The smallest version of this you could ship next week

If you have a legacy stack you are not allowed to rewrite, the playbook is short. One: pick a read-only API surface and never let the agent write through it. Two: cache the catalogue in memory and refresh on a schedule. Three: encode the human rules ("no discount above 18%") in the tool definitions, not the prompt. Four: hand the parked work to a queue a real person already watches.

When we built the chat agent for the Zaandam wholesaler the awkward bit was the SAP B1 session refresh under load, not the CodeIgniter age. We fixed it with a mutex and an idempotency key. If you are sitting on a similar stack and a similar pile of repeat questions, our AI agents page has the longer version of how we approach this kind of work.

Open your customer-service inbox tomorrow morning. Count how many of the first 50 messages ask the same five questions. That is your shortlist.