WFDASH-WEBAPP/docs/pricelist-extraction.md

Pricelist extraction — schema + prompt

Companion doc for the AI-powered step that converts a raw supplier price-list PDF into structured price_list rows.

Two-collection flow

WFDASH has two related collections, both already in PB:

• raw_price_list — one row per uploaded PDF. Holds the file, the status (Pending → Completed / Failed), and a log summary. Schema (live): id, supplier_file, supplier_name (relation → suppliers), status (select), log_summary, created, updated.

• price_list — one row per line item. The agent-readable catalogue the quote-filler looks up against. Schema (live, this is what gets filled):

  Field	Type	Source
  sku	text	AI: normalized product code
  description	text	AI: full product name
  category	text	AI: product family (snake_case)
  subcategory	text	AI: sub-family if PDF has it
  brand	text	AI: manufacturer
  supplier	text	denormalized from raw_price_list.supplier_name
  cost_price	number	AI: supplier's price
  unit	text	AI: each / pack / metre / kg / set
  markup	number	workflow default (25%) — operator override later
  selling_price	number	workflow computes: cost_price × (1 + markup/100)
  stock_qty	number	workflow default (0) — inventory updates later
  specifications	json	AI: rich JSON (attributes + synonyms)
  source_file	text	workflow: raw_price_list.id this row was extracted from
  search_text	text	workflow: denormalised "search everything" blob — every text column (sku, description, brand, supplier, category, subcategory, unit, source_file), the resolved supplier name, the four number columns tokenised (cost_price, selling_price, markup, stock_qty), AND the full specifications JSON (every key, every string value, every number value, every array value, and one level of nested-object keys). PB can't substring-match JSON, so the Catalogue tab ORs against this column directly.
  created / updated	autodate	—

  Indexed on sku, category, subcategory, brand, supplier — so the agent's "find me all HSS cutters 12mm from JEISA" becomes a single PB filter, not a full-table scan.

Per-item JSON shape (what the AI outputs)

One price list → one record → N line items. The LLM returns ONE outer object with an items array; the workflow splits it and POSTs each item as a price_list row.

{
  "items": [
    {
      "sku": "TS-DRL-12-55",
      "description": "HSS Twist Drill 12×55mm",
      "category": "twist_drill",
      "subcategory": "jobber",
      "brand": "Dormer",
      "cost_price": 24.50,
      "currency": "ZAR",
      "unit": "each",
      "specifications": {
        "material": "HSS",
        "diameter_mm": 12,
        "length_mm": 55,
        "shank_mm": 12,
        "point_angle_deg": 118,
        "coating": "bright",
        "synonyms": [
          "drill bit",
          "12mm drill",
          "HSS drill",
          "twist drill"
        ]
      }
    }
  ],
  "extracted_at": "2026-06-30T10:00:00Z",
  "extraction_model": "gpt-4o-2024-08-06",
  "extraction_confidence": "high",
  "page_count": 4,
  "raw_text_excerpt": "first 500 chars of OCR'd text, for debugging"
}

specifications.attributes per category

attributes is the rich, flexible part. LLM fills whatever the PDF gives it. Common keys by category:

Category	Typical attributes
twist_drill	material, diameter_mm, length_mm, shank_mm, point_angle_deg, coating
step_drill	material, min_diameter_mm, max_diameter_mm, steps, coating
hss_cutter	material, diameter_mm, length_mm, shank_mm, type
countersink	material, diameter_mm, angle_deg, shank_mm
reamer	material, diameter_mm, flutes, shank_mm
burr	material, shape, diameter_mm, shank_mm
tct_cutter	material, diameter_mm, teeth, shank_mm
hole_saw	material, diameter_mm, shank_mm, depth_mm
rail_cutter	material, diameter_mm, shank_mm

For categories not in the table, the LLM invents attribute names from the PDF content. Snake_case, units in the key.

specifications.synonyms — the matching bridge

Always include inside specifications:

• The category name (hss_cutter, twist_drill)
• The material + category combo (HSS cutter, HSS slugger)
• 1–3 colloquial variants (slugger, TCT cutter, annular cutter)
• The full description with units stripped (HSS Twist Drill 12×55)

Don't include:

• The supplier's brand (the buyer doesn't know it)
• The supplier's SKU
• Made-up words not in the PDF

Defaults the workflow applies AFTER extraction

Field	Default	How to apply
supplier	(denormalize from raw)	Code node reads raw_price_list.expand.supplier_name.suppliername
markup	25 (or operator override from notes)	Regex /markup\s*[:=]?\s*(\d+)\s*%?/i on the notes field; falls back to 25 if not found
selling_price	cost_price × (1 + markup/100)	Code node: Math.round(cost_price * (1 + markup/100) * 100) / 100
stock_qty	0	Hardcoded; inventory system updates later

The notes field (operator → AI)

raw_price_list.notes is a free-text field the operator fills in at upload time on the Pricelist page. It's the bridge between "what the operator knows about this price list" and "what the AI should pay attention to." Common uses:

• Brand override — Brand is JEI (when the PDF doesn't print the brand in every row)
• Markup override — Apply 30% markup (Code node regexes this out for selling_price)
• Currency — All prices in ZAR excl. VAT (or incl. VAT — affects the workflow)
• Quirks — Last 3 items are TCT, not HSS / Page 4 has discount codes / Header logo in row 1 is the supplier name
• Default behavior — Use defaults (no overrides; the workflow uses its 25% markup, ZAR, no brand)

The n8n workflow reads notes and injects it into the LLM user message as context. The LLM treats the notes as a per-upload override of the system prompt's defaults.

LLM user message shape

{
  "messages": [
    {
      "role": "system",
      "content": "[the system prompt from this doc]"
    },
    {
      "role": "user",
      "content": [
        {
          "type": "text",
          "text": "Supplier: JEISA - WESTSCOTT GROUP.\nOperator notes: {{ $json.notes }}\n\nExtract all line items from the attached PDF and return them in the JSON shape from your instructions."
        },
        {
          "type": "image_url",
          "image_url": { "url": "data:application/pdf;base64,..." }
        }
      ]
    }
  ]
}

If notes is empty, the LLM uses the system prompt defaults. If the operator wrote "Brand is JEI", the LLM stamps every item with brand: "JEI". If the operator wrote "Apply 30% markup", the system prompt's hardcoded "25%" gets overridden in the LLM's mind AND the Code node regexes the same number out for selling_price.

Markup regex in the Code node

const notes = $('Search raw_price_list').first().json.notes ?? '';
const markupMatch = notes.match(/markup\s*[:=]?\s*(\d+)\s*%?/i);
const DEFAULT_MARKUP = 25;
const markup = markupMatch ? Number(markupMatch[1]) : DEFAULT_MARKUP;

So notes = "Apply 30% markup" → markup = 30, notes = "Markup: 35%" → markup = 35, notes = "use defaults" → markup = 25. The regex is intentionally loose — matches 30%, 30 %, 30, markup 30, Markup: 30, markup=30, etc. field. The "user" message is the PDF as base64.

You are a price-list parser for Westcott Fasteners, a South African
industrial fastener and tool supplier. You receive a single supplier
price list as a PDF (image or text) and must return ONE strict JSON
object — no prose, no markdown fences.

The JSON must match this shape exactly:

{
  "items": [
    {
      "sku": "string",                  // normalized SKU, uppercase, dash-separated
      "description": "string",          // full product name as printed
      "category": "string",              // e.g. "twist_drill", "hss_cutter"
      "subcategory": "string" | null,
      "brand": "string" | null,
      "cost_price": number,              // supplier's price (no VAT)
      "currency": "ZAR" | "USD" | "EUR" | "GBP",
      "unit": "each" | "pack" | "metre" | "kg" | "set" | "box",
      "specifications": {
        // Flexible per-category. Use snake_case, units in the key.
        // Common keys:
        //   twist_drill:  material, diameter_mm, length_mm, shank_mm,
        //                 point_angle_deg, coating
        //   step_drill:   material, min_diameter_mm, max_diameter_mm,
        //                 steps, coating
        //   hss_cutter:   material, diameter_mm, length_mm, shank_mm, type
        //   countersink:  material, diameter_mm, angle_deg, shank_mm
        //   reamer:       material, diameter_mm, flutes, shank_mm
        //   burr:         material, shape, diameter_mm, shank_mm
        //   tct_cutter:   material, diameter_mm, teeth, shank_mm
        //   hole_saw:     material, diameter_mm, shank_mm, depth_mm
        //   rail_cutter:  material, diameter_mm, shank_mm
        // For anything else, invent the keys from the PDF.
        "material": "string",
        "diameter_mm": number | null,
        "synonyms": [ "string", ... ]    // see synonyms rules below
      }
    }
  ],
  "extracted_at": "ISO8601 timestamp",
  "extraction_model": "your model name + version",
  "extraction_confidence": "low" | "medium" | "high",
  "page_count": number,
  "raw_text_excerpt": "string"            // first 500 chars of OCR'd text
}

═══════════════════════════════════════════════════════════════════
HARD RULES
═══════════════════════════════════════════════════════════════════

1. NEVER invent data. If a value isn't in the PDF, set it to null.
   "Don't know" beats "guess wrong" — a wrong price on a quote is
   much worse than a missing price the operator fills in by hand.

2. PRICES — read them as printed. R245.50 → 245.50. R 1,245 → 1245
   (strip thousands separators). $12.99 → 12.99. If the currency
   isn't stated and the supplier is South African, default to ZAR.
   cost_price is the supplier's pre-VAT number unless the PDF
   explicitly shows VAT-inclusive.

3. SKUs — normalize to UPPERCASE, dash-separated, no spaces, no
   supplier prefixes. "ACME TS-12x55" → "TS-12X55". If the PDF
   doesn't show a SKU, fabricate one from the category + key
   attributes: e.g. "TS-DRL-12-55".

4. CURRENCY — the price_list collection only stores the numeric
   cost_price. The currency is captured in this JSON for the
   operator's reference; the workflow drops it on insert.

5. UNITS — normalize to the enum: "ea" / "pcs" / "PC" → "each",
   "m" → "metre", "KG" → "kg", "BOX" / "CTN" → "box", "SET" → "set".
   If unclear, default to "each".

6. ATTRIBUTES — fill whatever the PDF gives. Use snake_case, units
   in the key (e.g. `diameter_mm`, `point_angle_deg`). Leave missing
   ones out of the JSON entirely rather than setting to null.

7. SYNONYMS — generate 3-6 buyer-friendly aliases per item. Include:
   the category, the material + category combo, 1-3 colloquial
   variants, and the description with units stripped. Example for
   an HSS annular cutter:
     ["hss_cutter", "HSS cutter", "HSS slugger", "slugger cutter",
      "slugger", "annular cutter", "HSS Annular Cutter 12×55"]
   Never include: supplier brand, supplier SKU, made-up words.

8. CATEGORIES — snake_case, lowercase, from the table above when
   applicable. New categories are fine if the product family is
   genuinely new (don't force-fit).

9. CONFIDENCE — "high" if every line has a clear price and OCR is
   clean. "medium" if some values required inference. "low" if the
   PDF is scanned, blurry, or only partially readable — in that
   case leave `items: []` and put whatever you CAN see in
   `raw_text_excerpt`. The operator will re-upload or fix manually.

10. OUTPUT — return only the JSON object. No prose, no markdown
    fences, no leading "Here is the JSON:". Just the object.

═══════════════════════════════════════════════════════════════════
WHAT TO DO WHEN THINGS GO WRONG
═══════════════════════════════════════════════════════════════════

- PDF is a SCAN, not text: still try. If unreadable, set
  extraction_confidence: "low" and items: [].
- PDF is a PHOTO of a paper list: same as scan.
- PDF has multiple categories mixed: each item gets its own
  `category` per the table above. Don't try to guess a single
  category for the whole PDF.
- Price column shows "POA" / "TBA" / "On Request": set
  cost_price: null.
- Same product appears multiple times with different pack sizes
  (e.g. "each" and "pack of 10"): emit TWO items, one per
  pack size, with the `unit` field distinguishing them.
- PDF is a multi-page catalog with photos and prose: only emit
  items where you found an explicit price. Skip the marketing
  pages.

═══════════════════════════════════════════════════════════════════

Workflow (n8n)

Schedule Trigger (every 5 min)
   ↓
HTTP Request — GET /api/collections/raw_price_list/records
               ?filter=status="Pending"&perPage=1
   ↓
Limit (1)
   ↓
HTTP Request — GET /api/files/{collectionId}/{id}/{filename}
               (response format = file)
   ↓
OpenAI / Anthropic — vision call, system prompt above
   ↓
Code — parse JSON, validate shape, build N POST payloads
   ↓
SplitInBatches (one per item)
   ↓
HTTP Request — POST /api/collections/price_list/records
   ↓
(loop until SplitInBatches done)
   ↓
HTTP Request — PATCH /api/collections/raw_price_list/records/{id}
               { status: "Completed",
                 log_summary: "Extracted N items via gpt-4o, confidence: high" }

Code node (between LLM and SplitInBatches)

Parses the LLM response and applies the per-row defaults. The buildSearchText() helper is a vanilla-JS port of the shared TypeScript function in src/lib/price-list-search.ts — keep both in sync if you change one. Pseudocode:

// Vanilla-JS port of src/lib/price-list-search.ts#buildPriceListSearchText.
// Pushes every searchable token into a Set (deduped), then joins
// with spaces. See the TS source for the canonical reference.
function pushIfPresent(out, value) {
  if (value == null || value === '') return;
  if (typeof value === 'string') {
    const t = value.toLowerCase().trim();
    if (t) out.add(t);
    return;
  }
  if (typeof value === 'number' && !Number.isNaN(value)) {
    out.add(String(value).toLowerCase());
    const asInt = Math.trunc(value);
    if (asInt !== value) out.add(String(asInt));
    return;
  }
}
function pushSpecifications(out, spec) {
  if (!spec || typeof spec !== 'object' || Array.isArray(spec)) return;
  for (const [key, val] of Object.entries(spec)) {
    out.add(key.toLowerCase());
    if (val == null) continue;
    if (Array.isArray(val)) { for (const item of val) pushIfPresent(out, item); continue; }
    if (typeof val === 'object') {
      for (const [k2, v2] of Object.entries(val)) {
        out.add(k2.toLowerCase());
        pushIfPresent(out, v2);
      }
      continue;
    }
    pushIfPresent(out, val);
  }
}
function buildSearchText(row, supplierName) {
  const tokens = new Set();
  for (const k of ['sku','description','brand','category','subcategory','unit','source_file']) {
    pushIfPresent(tokens, row[k]);
  }
  pushIfPresent(tokens, supplierName ?? row.supplier);
  for (const k of ['cost_price','selling_price','markup','stock_qty']) {
    pushIfPresent(tokens, row[k]);
  }
  pushSpecifications(tokens, row.specifications);
  return [...tokens].join(' ');
}

const parsed = JSON.parse($input.first().json.message.content);
const rawRecord = $('Search raw_price_list').first().json;
const supplier = rawRecord.expand?.supplier_name?.suppliername || '';
const rawId = rawRecord.id;

const DEFAULT_MARKUP = 25;

const rows = parsed.items
  .filter(it => it.cost_price != null)   // skip POA / TBA
  .map(it => {
    // search_text: denormalised "search everything" blob. Includes
    // every text column on the row, the full specifications JSON
    // (keys + values, including synonyms and any ad-hoc attributes
    // the LLM invented), the four number columns tokenised, and the
    // resolved supplier name. PB can't substring-match JSON, so the
    // Catalogue tab's search box ORs against this column directly.
    const searchText = buildSearchText(it, supplier);

    return {
      sku: it.sku,
      description: it.description,
      category: it.category,
      subcategory: it.subcategory || null,
      brand: it.brand || null,
      supplier,                              // denormalized from raw
      cost_price: it.cost_price,
      markup: DEFAULT_MARKUP,
      selling_price: Math.round(it.cost_price * (1 + DEFAULT_MARKUP / 100) * 100) / 100,
      unit: it.unit || 'each',
      stock_qty: 0,
      specifications: it.specifications,
      source_file: rawId,                   // trace-back + verify key
      search_text: searchText,              // search index
    };
  });

return rows.map(r => ({ json: r }));

Returns N items, one per price_list row. SplitInBatches loops the HTTP POST over them. After the batch, the PATCH updates the raw_price_list status.

Error / confidence paths

• LLM returns invalid JSON → Code node catches, PATCH raw_price_list with {status: "Failed", log_summary: <error>}.
• extraction_confidence: "low" with empty items → PATCH raw_price_list with {status: "Failed", log_summary: "AI could not read PDF — re-upload or fix manually"}. Operator sees red badge in the Pricelist UI.
• Token limit hit on a long PDF → for >25-page lists, split the PDF in half via a Code node (using pdf-lib if available, or just two extractFromFile calls) and merge the items arrays in the Code node before POSTing.

What the agent does at quote time

When the operator types a quote line, the agent:

// Pseudocode for the agent's lookup step
const match = await pb.collection('price_list').getList(1, 5, {
  filter: `category = "hss_cutter"
            && supplier = "JEISA - WESTSCOTT GROUP"
            && specifications.diameter_mm = 12`,
  sort: '-updated',
});
// OR for fuzzy:
const fuzzy = await pb.collection('price_list').getList(1, 5, {
  filter: `specifications.synonyms ~ "slugger"`,
});

The agent returns the matches to the LLM with full context (price, brand, dimensions, supplier SKU), and the LLM picks the best fit or asks the operator to disambiguate.