Part of: AI Assistants ↗

Twelve bug reports after an AI-agent PoC, and one root cause

· engineering

TL;DR. We built an AI-agent proof of concept to automate integration of data from POS systems into a client’s analytics layer. We shipped it. The client replied with exactly twelve points: here are the inaccuracies. We went through them line by line. Ten of the twelve had real substance, two were formal. The interesting part came next: eight of the ten valid points were caused by one missing step in the agent’s workflow. We share the review without names, without marketing, and without a lecturing tone, simply because this failure produced a better engineering output than a success would have.

All system names, the client, and some field names are anonymized. The structure of the bugs and the logic of the review are real.

1. What we built and why

The client is a restaurant SaaS group with several dozen locations. It runs analytics on sales, average check, shifts, and dishes. The data sources are two different POS systems (call them POS-A and POS-B) and one inventory system that holds cost of goods. Every integration of a new source had been a separate mini-project of two to three months of development.

The business question: can we cut integration time by a large factor without losing quality? And, importantly, can we do it without replacing the engineer with a code generator that works from a description, keeping the person where they have real expertise (business semantics, edge cases) and handing the agent the routine (boilerplate ETL, mapping of obvious fields, generating unit tests)?

The PoC ran for one week. We agreed on the scope up front: the deliverable was not working production code but a demonstration report. Can the approach work at all, where is its ceiling, and what is the next step. The money was small and the risk was clear to the client’s team lead.

The approach was semi-automatic. A pipeline of five steps, with explicit human checkpoints between them:

[POS API] -> explore-api -> {source}_schema.md
                                  |
                                  v
                             generate-etl -> loader.py
                                  |
                                  v
                             validate-code -> syntax / imports / types
                                  |
                                  v
                             run-validate -> canonical.db
                                  |
                                  v
                             build-vitrina -> vitrina.db

The heart of the system is a canonical intermediate schema (SQLite tables locations / receipts / receipt_items / etl_state) that any source has to map into. Plus a playbook of about fifty pages with the systematized quirks of six POS systems: amount units, deletion policy, time formats, pagination, and the usual traps. This is a separate document, not a prompt, and the agent reads it at the start of every step.

On top of the canonical schema sits the client’s analytics layer, about fifty fields per record, with enrichments: shifts, day types (weekday or weekend), revenue inclusion, and per-check aggregates.

2. What we shipped

In one week the agent ran the pipeline over both sources. The client received:

  • The canonical schema (canonical.sql) with DDL and design-choice comments.
  • An adapter for POS-A: loader.py (about 200 lines), {source}_schema.md (a field mapping with HIGH, MEDIUM, and LOW confidence levels), and a sample vitrina .csv.
  • The same for POS-B.
  • build_vitrina.py, which builds the analytics layer from the canonical schema (about 300 lines, roughly 95 percent identical between sources, differing only in a source_system filter).
  • The playbook (PROBLEMS.md, pos_reference.md) describing seven common integration problems plus per-vendor specifics.
  • A mock-API harness: the client’s data served as a local FastAPI server with an OpenAPI schema, so the pipeline can run end to end on any machine in about five minutes.

The numbers: about 1,200 checks for POS-A and about 1,700 for POS-B, with roughly 12,000 and 26,000 line items. The vitrina was about 38,000 rows across 66 columns.

We shipped. A few days later the client wrote back.

3. The client’s bug report (verbatim, anonymized)

Reproduced as received, with only field names renamed. The semantics, structure, and tone are the originals.

On POS-A:

  1. The real source API was not researched. No attempt was made to find suitable API methods. The mock API that was built does not imitate any real API.
  2. The mapping is not fully documented. The md file describes fewer fields than the vitrina has, and at least some of the undocumented fields are populated.
  3. dish_ext_id was filled with the dish_code field.
  4. receipt_ext_id was filled with the check number.
  5. waiter_invoice_id is empty.
  6. waiter_item_id is empty.
  7. Some ids are formed incorrectly, with no restaurant-id prefix. We did not pass you the rules for forming these ids. Shift dates were used instead of shift ids, and the rules for forming shift ids were also not communicated.

On POS-B:

  1. The source API was also not researched.
  2. It was not accounted for that a check consists of sub-checks joined by the parent_account_id field. All sub-checks became separate standalone checks.
  3. The vitrina includes line items with negative cost and negative portion counts, while is_deleted is false everywhere. That is, the transaction_type field was not taken into account.
  4. The devices that created the bill (three different terminals) were mistaken for three different locations, so the vitrina has three restaurant_id values for one restaurant.
  5. The existence of the inventory system, and the cost of goods it holds, were not taken into account.

Twelve points. No emotion, all to the point.

4. Review: who is right on each point

Our first reaction was defensive. It was a mock API, we agreed on that. After an hour of staring at the ceiling, we sat down and checked line by line against the code. The result is a table grouped by cost of fix, from A (trivial) to D (structural data-model interpretation problems).

Group A: trivial, a workflow gap, not the model

POS-A #2 (mapping not fully documented). Valid. schema.md documents only source to canonical, while the vitrina adds about forty more enriched fields whose mapping lives only in the build_vitrina.py code. There was simply no pipeline step for “after building the vitrina, document its mapping in markdown.” Half an hour of work to add.

POS-A #1 and POS-B #1 (the real API was not researched). Formally valid. The agent did call the mock API rather than the client’s live endpoint. That was a deliberate scope decision for the PoC, and the playbook (pos_reference.md) contains a detailed description of both systems’ real APIs: authentication, pagination, cents versus units, tokens. In production mode the mock is replaced by the real endpoint with no changes to loader.py. It is not a code bug, but the client ended up seeing no proof that the agent could research a source, only proof that this time it did not. That is positioning, not code.

Group B: knowledge-base extension, one or two iterations

POS-A #3 and #4 (dish_ext_id from dish_code, receipt_ext_id from order_num). Valid. The _ext_id suffix in the client’s schema means a stable primary key from the source. For POS-A that key is the check UUID (uniq_order_id in the JSON export), while order_num is a sequence number within a shift, not unique across shifts. The same goes for the dish: dish_id is the stable catalog UUID, and dish_code is a text code that can be reassigned (which our own pos_reference.md even documents). The agent swapped the roles, putting the text code where the UUID belongs. This is fixed with an explicit vitrina spec: _ext_id is the stable source PK, a UUID, not a text code.

POS-B #3 (transaction_type not applied to items). Valid. This one stings, because the knowledge was there. PROBLEMS.md contains:

Lightspeed: type=VOID/CANCEL/REFUND is a new reversal record

But in the loader code the agent applied this exactly once, to the check status:

status = ReceiptStatus.storned if sale.get("type") == "REFUND" else ReceiptStatus.closed

It did not apply it to is_deleted on the items. As a result, line items with a negative quantity reach the vitrina marked as live. The knowledge is in the playbook, but it was applied selectively in the code. This is fixed by extending the instruction: apply deletion rules to every level of the hierarchy, not just the receipt.

POS-B #5 (inventory system as the source of cost). Partially valid. The inventory system is loaded. There is a load_inventory() in loader.py that fills store.menu_items with BOMPrice mapped to cost_price. But in the vitrina, cost_price is taken directly from the POS source (unitCostPrice for POS-B, cost_price in the POS-A fields), and inventory is not joined as a cost-of-goods source. An architectural gap: we saw the data but did not connect its role. Fixed with a section in pos_reference.md: the inventory system is the COGS source, and when an inventory cost exists it takes priority over the POS cost.

Group C: needs rules from the client

POS-A #5 and #6 (waiter_invoice_id, waiter_item_id empty). The semantics of these fields were not disclosed by the client in any artifact. Any model in that situation will set NULL, because there is nothing to put there. This is fixed once the client sends a description of their vitrina fields.

POS-A #7 (rules for forming restaurant ids and shift ids). The client’s own words: we did not pass you the rules for forming these ids. We put in predictable placeholders (location id as it comes from the source, shift id as an ISO date). Give us the rules and we fix it in minutes.

Group D: structural data-model interpretation errors

These are the heaviest.

POS-B #2 (sub-checks not grouped by parent_account_id). A grep -rn parent_account_id in our code returns zero matches. The field is present in the source JSON, but the agent did not notice it at all. Each Sale became a separate receipt. In the real POS-B, one logical check can split into several Sale records that share a parent_account_id, for split bills and partial payments. For analytics they have to be merged.

POS-B #4 (three terminals read as three restaurants). The most painful error. The agent saw device_id and device_name in the JSON, reasonably assumed that different devices meant different locations, and wrote in the loader:

location_id = f"loc_{device_id}"

In the real POS-B a restaurant is defined at the level of tenant_id or business_id (the OAuth account context), and device_id is a POS terminal. A tablet on the counter is not a restaurant. The data-model semantics were not read: the model built a hypothesis from field names without testing it against the data.

These two points are the most telling. It is not that the model did not know something. The model did not look at the data.

5. Root cause: a missing data-profile step

Read the table above again. The nastiest bugs (Group D) are not a knowledge gap in the model. They are not framework bugs. They are not a bad prompt.

They are bugs from hypotheses built on field names without verifying those hypotheses against the data itself.

The agent’s workflow ran like this:

explore API schema -> map to canonical -> build vitrina

Nowhere between the steps was there a “look at the data, check your assumptions about field roles.” There was an explore of the API, but no explore of the data.

If such a step had existed, at least four of the twelve bugs would have surfaced naturally, from the data itself:

BugThe SQL probe that would have caught it
POS-B split billsSELECT parent_account_id, COUNT(*) FROM sales GROUP BY 1 HAVING COUNT(*) > 1. The field exists and forms groups, so a check is not a Sale.
POS-B terminals as locationsSELECT DISTINCT device_id, tenant_id, accounting_group FROM sales. If all terminals share the same downstream fields (address, currency, tax), they are not locations.
POS-B transaction_typeSELECT type, COUNT(*), MIN(quantity), MAX(quantity) FROM sales_lines GROUP BY type. REFUND rows have negative quantity, so the reversal flag has to reach the items.
POS-A dish_id versus dish_codeA stability test across snapshots: if the code is reassigned while the UUID stays put, the UUID is the stable key.

These are not hard probes. They are the ordinary first steps an analyst takes on new data. Anyone sitting down to understand a new POS system would start with them. The agent did not.

The curious part is that the agent had the knowledge. pos_reference.md describes both sources in detail, including split bills and the terminal-versus-location distinction. But that knowledge sat in a read-only reference, not as triggers for checks against the concrete data. The model read the reference as context, switched into mapping mode, and carried on.

That is the main takeaway. Not “AI is bad,” not “we need a better prompt,” but the workflow needs an explicit step where the agent has to run SQL probes over cardinality, uniqueness, cross-field groupings, negative values, and null rate before it starts mapping.

6. What we change in the pipeline

The concrete edits to the agent’s SKILL files after this feedback.

A new mandatory step between explore-api and generate-etl, called explore-data. A checklist of probe queries the agent has to run on the raw source data before it writes a single line of mapping:

  1. Cardinality per id field, with COUNT DISTINCT plus examples of repeats if any. This catches “field X does not actually have unique values.”
  2. Cross-field groupings. For every pair (X, Y) where X looks like a foreign key or hierarchical key, check GROUP BY X with counts. This catches sub-checks, split bills, and order hierarchies.
  3. Distribution of categorical fields. For all enum fields (type, status, service), the distribution of values. This catches “there is a REFUND here, we have to account for it.”
  4. Negative and null values for all numeric and quantity fields. This catches “there are rows with negative totals while status is closed.”
  5. Stability between snapshots. If data spans several dates, check each id candidate for reassignment. This separates a stable UUID from an unstable code.

Each probe is one SQL query plus an explicit note in schema.md under a new “Data profile observations” section. That section is read by the human reviewer before the mapping is approved.

Playbook extension. For every piece of knowledge that sits in pos_reference.md, an explicit trigger is written: when you see field X in this system’s API, run probe Y. Knowledge becomes an instruction to act, not a reference.

Vitrina documentation. A new document-vitrina step after build-vitrina generates vitrina_mapping.md from the code, describing each column, its source, transformation, and edge cases.

By our estimate these three edits close eight of the ten valid feedback points. The remaining two (semantics of waiter_*_id, and the id-forming rules) are client input, which no workflow can close on its own.

7. What we take away

If there is one conclusion: in AI-agent pipelines, reference knowledge and a procedural instruction are different things, and having the first does not guarantee the second.

You can keep a fifty-page playbook with everything spelled out, and the agent will still build a hypothesis from field names, fail to verify it against the data, and ship work with data-model semantic errors. Not because the playbook is bad, but because the workflow has no step where that knowledge turns into a verifying action.

Adding a data-profile step between “saw the schema” and “started mapping” is not a sexy AI feature or a new prompt trick. It is routine engineering hygiene, carried over from the practice of a data analyst into the workflow of an AI agent. Yet its absence produces the heaviest bugs.

From the client’s side this story looks like “you wrote plausible code against a schema you invented for yourself.” Technically, yes. The PoC was shipped, rejected, and the deal closed lost. No hard feelings: the feedback gave us more than a clean sign-off would have, because it pointed us to a structural fix to the workflow that now lives in the SKILL files and will apply to the next integrations.

Thanks to the client for the twelve points. This post is, in effect, theirs.

More from the lab

all notes →