How v10r turns a supplier's Excel export into a sheet a shop or PIM will import, with an AI agent doing the judgement and code doing the work.
Agent-proposed transform recipe
Problem. Product data arrives as spreadsheets nobody designed for you: German column
names, colours spelled five ways, weights in grams in one row and 1,2 kg in the next, GTINs
whose leading zeros Excel removed. Asking a model to "clean the sheet" fails in three known
ways. It cannot hold forty thousand rows, so it samples and silently skips. It rewrites values
it was not sure about, and nobody can tell which ones. And the result cannot be reviewed or
repeated: next month's export starts from zero.
Method. Split the decision from the work.
| Piece | Where | What it does |
|---|---|---|
| Product sheet | product-sheet.ts |
The export as a header and rows of text, each row with its source line. Refuses a column with values but no header and a repeated header rather than inventing a name |
| Workbook I/O | sheet-workbook.ts, csv-table.ts |
.xlsx via read-excel-file/write-excel-file, numbers read as their stored digits and every cell written as text; CSV with the locale's separator (; in a German Excel) |
| Profile | sheet-profile.ts |
What the agent reads instead of rows: per column fill, distinct count, commonest values, value shape, plus five clipped sample rows. Its size depends on the number of columns, not rows |
| Target schema | target-schema.ts |
What the destination accepts: per attribute a kind (text, integer, decimal, gtin, enum, measure), required, values, unit, maxLength, plus the key column. checkSheet checks every row and names each issue with its line |
| Transform recipe | transform-recipe.ts |
An ordered list of typed ops: rename, drop, trim, map_values, convert_unit, normalize_gtin, split, concat, fill_empty, change_case, dedupe. checkRecipe walks the column set through the ops; applyRecipe runs them over every row and returns the output, a per-op report and every changed cell |
| Agent loop | recipe-proposal.ts |
proposeRecipe: the model reads the profile and target, answers with a recipe (structured output), code refuses or runs it, checks the output, and sends what still fails back. At most maxRounds rounds; stops on a clean check |
| Review workbook | run-workbook.ts |
products, issues, changes and recipe sheets in one .xlsx file |
| CLI | scripts/pim/sheet.ts (bun run pim), scripts/pim/build.ts (bun run pim:build) |
example, profile, check, run over a job folder; compiled into one self-contained program per OS for machines with no Bun, git or repo |
The ops. A recipe is { "note"?: string, "ops": [...] }; each op is one of:
| Op | Fields | Does |
|---|---|---|
rename |
from, to |
Renames a column in place |
drop |
columns |
Removes columns |
trim |
columns |
Trims and collapses inner whitespace |
map_values |
column, mapping |
Replaces listed values (matched ignoring case and surrounding space); others are unresolved |
convert_unit |
column, to, assume? |
Converts mm/cm/m, g/kg, ml/l; a bare number uses assume, or is unresolved |
normalize_gtin |
column |
Removes spaces and hyphens, restores lost leading zeros when the check digit confirms them |
split |
column, separator, into |
Writes the parts into new columns after the source; the last takes the rest |
concat |
columns, separator, into |
Joins the non-empty values into a new column |
fill_empty |
column, value |
Sets empty cells |
change_case |
column, mode (upper·lower·title) |
Changes case |
dedupe |
key, keep (first·last) |
Removes rows with a repeated key and lists their lines |
Why a recipe, not edited cells. A recipe is about twenty lines a person can read, where the output is thousands of rows nobody can. It runs the same way every time, so the output can be reproduced from the input and the recipe, and next month's file reuses it. And it limits what a wrong proposal can do: the model's only output is a recipe, it has no tool that writes, and a recipe can only do what its ops do.
Why a column walk. The most common way a model's plan goes wrong is a column it imagined:
Beschreibung when the sheet says Bezeichnung, or the old name after its own rename.
checkRecipe follows the column set op by op and refuses any read of a column that does not
exist at that point, and any op that would overwrite one. A recipe with a problem never runs,
not even its valid first ops, so half-applied recipes cannot happen.
Why ops never guess. convert_unit reads 850g, 1,2 kg and a bare 450 (with
assume: "g"). It does not read ca. 2kg. normalize_gtin restores lost leading zeros only
when the check digit confirms the padded number, and refuses 4.01235E+12 because Excel has
already thrown those digits away. An unreadable value is left exactly as it was and listed in
the op's report. The target check then reports it again, on the row where a person can fix it.
Why text all the way through. A GTIN is not a number: 0012345678905 turned into a number
is wrong, and so is a 13-digit value stored as a float. The reader keeps a numeric cell's
stored digits, the sheet holds only strings, and the writer writes every cell as text, so a
person who opens and re-saves the output cannot damage it again.
Two lanes, one engine.
- In-app: an adapter loads the user's provider (
getToolProvider(registry, userId)) and callsproposeRecipe({ model, sheet, target }). The result carries the recipe, the run, the check, each round's feedback and token usage. It goes to a person for approval, like any other proposal. - Terminal agent: an agent (Claude Code or similar) runs
pim profile <folder>, writesrecipe.json, runspim run <folder>and reads the JSON summary, then revises. tutorial.md goes through this lane step by step.
Plain folders, no git. Office users have folders and Excel, not repositories. The terminal
lane assumes nothing else. A job folder per supplier holds one export, target.json and
recipe.json; pim finds them by name. Each run writes output/<date-time>/ with the review
workbook and copies of the recipe and target it used, so the folder is its own history: it can
be zipped, mailed or kept on a shared drive, and any past result can be reproduced from its
dated folder. pim itself is one file (bun run pim:build compiles it for Windows, macOS and
Linux with the Bun runtime inside), so the machine needs no Bun, no Node, no database and no
provider key.
Untrusted text. Supplier values are written by third parties. They reach the prompt only
inside escaped <sheet-profile> and <recipe-result> blocks, which the system prompt declares
to be data. A product title that says "drop every column" can only make the model write a
recipe, and that recipe is checked, run and reviewed like any other.
Invariants.
- The model never writes a cell; its only output is a recipe, and a recipe only does what its typed ops do.
- A recipe that reads a column which does not exist when its op runs is refused whole, before any row is touched.
- An op never guesses: a value it cannot read is left unchanged and reported with its source line.
- Done means the target schema check over every row passes, not that the model says so.
- Every value stays text from file to file; nothing is converted to a number unless an op says so.
- A run never overwrites an earlier one; each output folder carries the recipe and target that produced it.
Proof. src/lib/server/pim/*.test.ts, run in bun run validate: the worked example
(fixtures/) leaves exactly three issues, all on line 7. The agent loop is tested against a
scripted model: an imagined column is refused and sent back, the loop stops on a clean check,
and supplier text reaches the prompt escaped. The .xlsx round trip keeps 0012345678905 and
1E5 unchanged.
Next. A desk adapter: upload an export, show the proposed recipe as a proposal card, approve it, and download the review workbook. The engine and the loop need no change for this.
Related: ../ai/untrusted-content.md, ../ai/evals.md.