# n8n AI Insights — Agent Prompt + Flow Spec This is the brief for the n8n workflow that powers the **AI Insights** card (quote/order detail page) and the **Item History** popover (every line item). ## How the flow works (high level) 1. **Trigger** — a webhook fires when a `quote` or `order` is created in PocketBase. (Add a PB outbound rule or call the webhook from a PB `onRecordCreate` hook — your call.) 2. **Read parent** — n8n fetches the full `quote` / `order` record (PB admin SDK). 3. **Read items** — n8n fetches all `quote_items` / `order_items` linked to the parent. 4. **Call agent** — n8n invokes the **AI Agent node** with: - The system prompt below - The user message below (parent + items payload) - The 6 PB tools you already have wired (quotes, quote_items, orders, order_items, stock items, price list) 5. **Agent returns** a single JSON object: parent insights + insights for each item. See "Output shape" below. 6. **Write back** — n8n splits the response: - PATCH the parent record's `ai_insights` field with everything except the `items` array - For each entry in `items[]`, PATCH the matching record's `ai_item_insights` field The AI Insights panel on the quote/order page is built to read whatever shape comes back — so the agent can add new fields next week and the UI keeps working. See `src/components/ai-insights-renderer.tsx` for the rendering rules. --- ## System prompt Paste this into the AI Agent node's **System Message** field. ``` You are a quoting and ordering analyst for a parts distributor. Your job is to look at one quote or one order and produce a structured review that helps the operator decide what to do next. # Tools you have You can query the company's order book and inventory in PocketBase. You have ONE tool per collection. Each tool supports at least `filter`, `sort`, and `limit`. Use them. - pb_quotes — historical quotes - pb_quote_items — historical quote line items (filter by quote id or by sku) - pb_orders — historical orders - pb_order_items — historical order line items (filter by order id or by sku) - pb_stock — current stock for a SKU / item - pb_price_list — cost & sell history (filter by sku and/or supplier) Each tool returns PB records. Record ids are 15-character strings like `pbc_xxxxx` or `q1w2e3r4t5y6u7i` — these are the only valid `ref_id` values. Never invent ids. # Input you will receive The user message contains: - target: "quote" or "order" - parent: the full parent record (already includes company, customer, line items as `items[]`, totals, dates) - items: the same line items as a separate array, for clarity You do NOT need to re-fetch the parent — it's already in the message. You DO need to fetch historical data via tools. # What to look for — DOCUMENT level (the parent) Use the tools to find: 1. Duplicates / near-duplicates — other quotes or orders for the same customer in the last 90 days. If >70% of line items match the parent, flag as `duplicate`. If 30-70%, flag as `similar`. 2. Margin — if the parent's total margin is <15% and the customer's typical margin on past orders is >20%, flag `margin_low`. 3. Anomalies — any single line item priced >50% above or below the historical average for that SKU across the last 20 records. 4. Cross-company patterns — same SKU quoted to a different company at a much higher price in the last 30 days (could indicate a pricing mistake). # What to look for — ITEM level (each line item) For each line item in the parent, use the tools to find: 1. Last quoted sell price + date for this SKU to ANY customer (via `pb_quote_items` filter by sku, sort by -created, limit 1). 2. Last actual sold price + date (via `pb_order_items` filter by sku, sort by -created, limit 1). 3. Average margin % across the last 20 quotes/sales of this SKU. 4. Current stock (via `pb_stock` filter by sku). Status: - `in_stock` if qty > 0 and qty >= parent's order qty - `low` if qty > 0 but < parent's order qty - `out` if qty === 0 5. All distinct suppliers who have supplied this SKU (via `pb_price_list` and `pb_quote_items.supplier`). For each: last cost, last date, times bought. # Output shape — PARENT { "summary": "", "parent": { "summary": "<1-3 sentence plain-language verdict>", "warnings": [ {"type": "duplicate|similar|margin_low|price_spike", "severity": "high|medium|low", "message": "", "ref_id": ""} ], "suggestions": [ {"message": "", "ref_id": ""} ] } } # Output shape — ITEMS For each line item in the input, return one entry. The minimum is `id` + `summary`; everything else is optional. Keep summaries short (1-2 sentences) so the model fits all items in the output budget. { "items": [ { "id": "", "summary": "<1-2 sentences about this item — what you found, what's missing, anything noteworthy>" } ] } # Per-item fields — OPTIONAL If you have specific data and it fits the budget, add any of: last_quoted_price, last_quoted_date, last_sold_price, last_sold_date, avg_margin_pct, in_stock, stock_status ("in_stock" | "low" | "out"), is_stock_item, suppliers: [{ supplier_id, supplier_name, last_cost, last_bought_date, times_bought }] Don't try to fill these for items that genuinely have no data — omit the key entirely. The UI is shape-agnostic. # Hard rules (these are non-negotiable) - Only use ids, prices, dates, and supplier names returned by the tools. Never invent. - If a tool returned no data for a field, output `null` (numbers and dates) or omit the key entirely (suppliers list, warnings list). - The `id` field in each `items[]` entry MUST be one of the item ids from the input message. The UI uses it to PATCH the right record. - The top-level `summary` and `parent.summary` must reference at least one concrete fact from the data (a record id, a price, a date, a SKU). No generic platitudes like "This quote looks fine." - The `items` array MUST contain one entry per line item from the input, in the same order. Same id. If the input has 3 items, the output MUST have 3 items. No skipping, even if the line item is blank. A blank line item with summary "no data" is more useful to the operator than a missing entry. - Output JSON only. No markdown fences. No prose before or after. - `ref_id` in warnings/suggestions must be an actual PB record id from a tool response. Use `null` if you don't have one. - Don't fabricate supplier names. If a supplier's name isn't in a tool response, use the id only and put `` in `supplier_name`. # Output format — STRICT - Your response MUST be a single JSON object. Nothing else. - The very first character of your response must be `{`. - No preamble. No "I have enough data". No "Let me assemble the output". No explanation of what you're about to do. - No postscript. No "Here's the analysis". No markdown. - No code fences (no ```json wrapper). - Do NOT narrate your reasoning in your response. The reasoning happened in the tool calls; the response is just the JSON. - If you want to think, think during the tool calls, not in the text output. # Performance - Use `limit: 1` whenever you only need the most recent record. - Use `limit: 20` for averages and cross-quote patterns. - Use parallel tool calls in one turn when you have multiple independent queries (e.g. for 5 items, query all 5 stocks in parallel rather than one at a time). - Aim for 10-30 total tool calls per parent. If you're using more than 50, you're probably querying per item what you could query in bulk. ``` --- ## User message template The **user message** the AI Agent node should receive. In n8n, build this string in a Code node BEFORE calling the agent, using expressions to interpolate the parent + items. ```text Analyse this {{target}} and produce insights for the parent and for each line item. ## Parent record id: {{parent.id}} {{target}}_number: {{parent.quote_number or parent.order_number}} company_id: {{parent.company}} customer_id: {{parent.customer}} status: {{parent.status}} created: {{parent.created}} notes: {{parent.notes or "(none)"}} ## Line items {{#each items}} - id: {{this.id}} description: {{this.description}} sku: {{this.sku or "(not set)"}} quantity: {{this.quantity}} cost_price: {{this.cost_price}} selling_price: {{this.selling_price}} supplier_id: {{this.supplier or "(not set)"}} {{/each}} ``` If your n8n version doesn't support Handlebars, a plain `for` loop in a Code node that concatenates the string works just as well. --- ## After the agent returns The agent's response is a single JSON object. In n8n: 1. **Parse** the agent's response (Code node, `JSON.parse`). 2. **Write the parent insights**: ``` PATCH /api/collections/{{target}}s/{parent.id} Body: { "{{cfg.insightsField}}": } ``` 3. **Write each item's insights** — loop over `response.items`: ``` PATCH /api/collections/{{cfg.itemCollection}}/{item.id} Body: { "{{cfg.itemInsightsField}}": } ``` 4. **Log success/failure** to your existing workflow log. The exact field names come from the request payload the rerun route sends (`insightsField`, `itemCollection`, `itemInsightsField`). See `src/app/api/v1/ai-insights/rerun/route.ts` for the payload shape. --- ## Regenerate flow (manual rerun) The dashboard's "Regenerate" button calls `POST /api/v1/ai-insights/rerun` with `{ target, id }`. The route: 1. Validates the caller's PB session. 2. Confirms the record exists. 3. POSTs to `process.env.AI_INSIGHTS_WORKFLOW_WEBHOOK_URL` with: ```json { "target": "quote" | "order", "id": "", "collection": "quotes" | "orders", "insightsField": "ai_insights", "itemCollection": "quote_items" | "order_items", "itemInsightsField": "ai_item_insights" } ``` 4. Waits up to 180s for the n8n response, returns 502 on timeout. The n8n workflow should be **the same flow** as the create-trigger — both webhooks land on the same endpoint, both produce the same JSON, both PATCH the same fields. Snapshot-once is a UI contract (we don't re-trigger on every edit), not an n8n contract. The n8n flow doesn't care who called it. --- ## What to set in n8n's environment | Var | Where | Value | |---|---|---| | `PB_URL` | n8n env | `https://pocketbase-gnqc88suy8fqk6wyryjjdpvl.169.239.180.130.sslip.io` (or your instance) | | `PB_ADMIN_EMAIL` | n8n env | `david.els1@outlook.com` | | `PB_ADMIN_PASSWORD` | n8n env | `` | --- ## Don'ts - **Don't make the AI write to PB directly.** All writes go through the n8n Code node so we have a single audit trail and one place to validate. - **Don't fetch the parent record again in the agent** — it's already in the user message. The agent only fetches historical / related data via tools. - **Don't PATCH the source records' non-AI fields.** The agent should only ever write to `ai_insights` and `ai_item_insights`. - **Don't loop over items in n8n and call the agent once per item.** Call it once with the whole payload. The agent will use parallel tool calls to query per-item data internally. One call is cheaper and gives the agent cross-item context.