WFDASH-WEBAPP/docs/superpowers/specs/2026-08-07-order-item-context-block-design.md

Order Item Context Block — Design

Date: 2026-08-07 Status: Approved (brainstorming → design phase) Scope: Sprint process modal + orders detail page

1. Goal

Replace the AI Insights section in the order-sprint process modal with a deterministic, PocketBase-derived context block that surfaces the historical record of every comparable item the operator has touched. Surface the same data on the orders detail page as a popover trigger, replacing the existing AI-history ⓘ icon on that screen.

The block answers four questions for the operator before they pick a supplier and cost:

1. Last time we quoted this item — most recent quote_item with matching SKU.
2. Last order on this item — most recent order_item (other than the current row) with matching SKU.
3. Last invoice checked in — most recent order_item where invoiced_date is set and supplier_invoice_number is not empty, matching SKU.
4. Stock state — matching stock_items row: current qty, min qty, status.

Each row exposes the historical cost_price, selling_price, and computed markup_percent as read-only context, plus an Apply button that copies that row's cost (and supplier, where the row carries one) into the modal's pricing inputs. Selling stays locked to the order. Markup is always re-derived live from the (possibly just-applied) cost and the locked selling.

2. Architecture

   Sprint modal                          Orders page items table
   ┌───────────────────────────────┐     ┌───────────────────────────────┐
   │ <OrderItemContextPanel        │     │ <OrderItemContextPopover-    │
   │   itemId={item.id}            │     │    Trigger itemId={row.id}/> │
   │   onApply={applyContext}/>    │     │ (ⓘ icon, click → popover)    │
   └─────────────┬─────────────────<E29480>     └─────────────────┬─────────────┘
                 │ fetch on open                            │ fetch on first open
                 ▼                                          ▼
              ┌──────────────────────────────────────────────────┐
              │  GET /api/v1/order-items/[id]/context            │
              │  → getOrderItemContext(orderItemId)              │
              │  (thin route, runtime=nodejs, dynamic=force-...) │
              └──────────────────────────┬───────────────────────┘
                                         │
                                         ▼
                  src/lib/server/order-item-context-service.ts
                    ├─ pure helpers imported from item-history-service:
                    │     normaliseSkuForHistory, findBestDescriptionMatch,
                    │     aggregateSuppliers, computeCheapest, daysAgo,
                    │     isValidPrice
                    └─ 5 PB reads in parallel (see §3)
                                         │
                                         ▼
                          PocketBase: order_items (target + joins),
                                      quote_items, stock_items,
                                      suppliers

Single backend, two render surfaces. The pure helpers already exported by item-history-service.ts are reused; no logic is duplicated.

3. Backend service

3.1 File: src/lib/server/order-item-context-service.ts

import { getAdminPocketBase } from "@/lib/server/pocketbase-admin";
import {
  normaliseSkuForHistory,
  findBestDescriptionMatch,
  aggregateSuppliers,
  computeCheapest,
  daysAgo,
  isValidPrice,
} from "@/lib/server/item-history-service";
import type {
  ItemHistoryMatch,
  ItemHistorySupplier,
  ItemHistoryCheapest,
} from "@/lib/types";

// Re-export the shared types so callers can import from one place.
export type { ItemHistoryMatch, ItemHistorySupplier, ItemHistoryCheapest };

export interface OrderItemLastQuote {
  quote_item_id: string;
  quote_id: string;
  quote_number: string;
  customer_name: string;
  company: string;
  status: string;
  cost_price: number;
  selling_price: number;
  markup_percent: number;
  days_ago: number;
}

export interface OrderItemLastOrder {
  order_item_id: string;
  order_id: string;
  order_number: string;
  company: string;
  cost_price: number;
  selling_price: number;
  markup_percent: number;
  item_status: string;
  updated_at: string;
  days_ago: number;
}

export interface OrderItemLastInvoiced {
  order_item_id: string;
  order_id: string;
  order_number: string;
  supplier_invoice_number: string;
  customer_invoice_number: string;
  invoiced_date: string;
  cost_price: number;
  quantity: number;
  qty_invoiced: number;
  days_ago: number;
}

export interface OrderItemStock {
  stock_item_id: string;
  name: string;
  sku: string;
  current_qty: number;
  min_qty: number | null;
  selling_price: number | null;
  markup_percent: number | null;
  status: "in_stock" | "low" | "out";
}

export interface OrderItemContext {
  generated_at: string;
  match: ItemHistoryMatch;
  last_quote: OrderItemLastQuote | null;
  last_order: OrderItemLastOrder | null;
  last_invoiced: OrderItemLastInvoiced | null;
  stock: OrderItemStock | null;
  suppliers: ItemHistorySupplier[];
  cheapest: ItemHistoryCheapest | null;
}

export interface OrderItemContextResponse extends OrderItemContext {
  error?: string;
}

const SUPPLIERS_PAGE_SIZE = 200;

export async function getOrderItemContext(
  orderItemId: string,
): Promise<OrderItemContextResponse> {
  const pb = await getAdminPocketBase();
  const generated_at = new Date().toISOString();
  const response: OrderItemContextResponse = {
    generated_at,
    match: { sku: "", match_kind: "none", match_confidence: 0 },
    last_quote: null,
    last_order: null,
    last_invoiced: null,
    stock: null,
    suppliers: [],
    cheapest: null,
  };

  // 1. Load the target row. SKU + description drive matching.
  const target = await pb.collection("order_items").getOne<{
    id: string;
    sku?: string;
    description?: string;
    quote_item?: string;
  }>(orderItemId, { fields: "id,sku,description,quote_item" });
  response.match.sku = target.sku || "";

  // Bail early if there's nothing to match on.
  if (!target.sku && !target.description) return response;

  // 2. Description-fuzzy candidate fetch (only when SKU is empty).
  const fuzzyCandidates = !target.sku && target.description
    ? pb.collection("order_items").getList<{
        id: string;
        sku: string;
        description: string;
      }>(1, 500, {
        filter: `id != "${orderItemId}" && description != "" && sku != ""`,
        sort: "-created",
        fields: "id,sku,description",
      })
    : Promise.resolve({
        items: [] as Array<{ id: string; sku: string; description: string }>,
      });

  // 3. Resolve effective SKU.
  const effectiveSku = target.sku
    || (findBestDescriptionMatch(
        { id: orderItemId, description: target.description },
        (await fuzzyCandidates).items,
        0.6,
      )?.item.sku ?? "");

  if (target.sku) {
    response.match.match_kind = "sku_exact";
    response.match.match_confidence = 1;
  } else if (effectiveSku) {
    response.match.match_kind = "description_fuzzy";
    response.match.match_confidence = 1; // updated below if found
    const fm = findBestDescriptionMatch(
      { id: orderItemId, description: target.description },
      (await fuzzyCandidates).items,
      0.6,
    );
    if (fm) response.match.match_confidence = fm.confidence;
    response.match.sku = effectiveSku;
  }

  const sku = effectiveSku;
  const skuFilter = sku ? `sku = "${sku.replace(/"/g, '\\"')}"` : "";

  // 4. Fan out 5 PB reads in parallel.
  const [quoteRows, orderRows, invoicedRows, stockRows, suppliersRows] = await Promise.all([
    sku
      ? pb.collection("quote_items").getList<{
          id: string;
          quote: string;
          cost_price: number;
          selling_price: number;
          created: string;
          expand?: {
            quote?: {
              quote_number: string;
              status: string;
              customer_name?: string;
              company?: string;
            };
          };
        }>(1, 5, {
          filter: `${skuFilter} && id != "${target.quote_item ?? ""}"`,
          sort: "-created",
          expand: "quote",
        })
      : Promise.resolve({
          items: [] as Array<{
            id: string; quote: string; cost_price: number; selling_price: number; created: string;
          }>,
        }),

    sku
      ? pb.collection("order_items").getList<{
          id: string;
          order: string;
          supplier: string;
          cost_price: number;
          selling_price: number;
          markup_percent?: number;
          item_status: string;
          updated: string;
          expand?: { order?: { order_number: string; company?: string } };
        }>(1, 5, {
          filter: `${skuFilter} && id != "${orderItemId}"`,
          sort: "-updated",
          expand: "order",
        })
      : Promise.resolve({
          items: [] as Array<{
            id: string; order: string; supplier: string; cost_price: number;
            selling_price: number; item_status: string; updated: string;
          }>,
        }),

    sku
      ? pb.collection("order_items").getList<{
          id: string;
          order: string;
          supplier_invoice_number: string;
          customer_invoice_number: string;
          invoiced_date: string;
          cost_price: number;
          quantity: number;
          qty_invoiced: number;
          expand?: { order?: { order_number: string } };
        }>(1, 1, {
          filter: `${skuFilter} && invoiced_date != null && invoiced_date != "" && supplier_invoice_number != ""`,
          sort: "-invoiced_date",
          expand: "order",
        })
      : Promise.resolve({
          items: [] as Array<{
            id: string; order: string; supplier_invoice_number: string;
            customer_invoice_number: string; invoiced_date: string; cost_price: number;
            quantity: number; qty_invoiced: number;
          }>,
        }),

    sku
      ? pb.collection("stock_items").getList<{
          id: string;
          name: string;
          sku: string;
          current_qty: number;
          min_qty: number | null;
          selling_price: number | null;
          markup: number | null;
        }>(1, 5, { filter: skuFilter })
      : Promise.resolve({
          items: [] as Array<{
            id: string; name: string; sku: string; current_qty: number;
            min_qty: number | null; selling_price: number | null; markup: number | null;
          }>,
        }),

    pb.collection("suppliers").getList<{ id: string; suppliername: string }>(1, SUPPLIERS_PAGE_SIZE),
  ]);

  const supplierNameById = new Map(suppliersRows.items.map((s) => [s.id, s.suppliername]));

  // 5. Last quote — most recent with non-zero cost or selling.
  const q = quoteRows.items.find((r) => isValidPrice(r.cost_price) || isValidPrice(r.selling_price));
  if (q) {
    const cost = q.cost_price || 0;
    const sell = q.selling_price || 0;
    response.last_quote = {
      quote_item_id: q.id,
      quote_id: q.quote,
      quote_number: q.expand?.quote?.quote_number || "—",
      customer_name: q.expand?.quote?.customer_name || "—",
      company: q.expand?.quote?.company || "—",
      status: q.expand?.quote?.status || "",
      cost_price: cost,
      selling_price: sell,
      markup_percent:
        cost > 0 && sell > 0
          ? Math.round(((sell - cost) / cost) * 10000) / 100
          : 0,
      days_ago: daysAgo(q.created),
    };
  }

  // 6. Last order — most recent other row with non-zero cost or selling.
  const o = orderRows.items.find(
    (r) => isValidPrice(r.cost_price) || isValidPrice(r.selling_price),
  );
  if (o) {
    const cost = o.cost_price || 0;
    const sell = o.selling_price || 0;
    response.last_order = {
      order_item_id: o.id,
      order_id: o.order,
      order_number: o.expand?.order?.order_number || "—",
      company: o.expand?.order?.company || "—",
      cost_price: cost,
      selling_price: sell,
      markup_percent: o.markup_percent ?? (
        cost > 0 && sell > 0
          ? Math.round(((sell - cost) / cost) * 10000) / 100
          : 0
      ),
      item_status: o.item_status,
      updated_at: o.updated,
      days_ago: daysAgo(o.updated),
    };
  }

  // 7. Last invoiced.
  const i = invoicedRows.items[0];
  if (i) {
    response.last_invoiced = {
      order_item_id: i.id,
      order_id: i.order,
      order_number: i.expand?.order?.order_number || "—",
      supplier_invoice_number: i.supplier_invoice_number,
      customer_invoice_number: i.customer_invoice_number,
      invoiced_date: i.invoiced_date,
      cost_price: i.cost_price,
      quantity: i.quantity,
      qty_invoiced: i.qty_invoiced,
      days_ago: daysAgo(i.invoiced_date),
    };
  }

  // 8. Stock.
  const s = stockRows.items[0];
  if (s) {
    let status: OrderItemStock["status"];
    if (s.current_qty <= 0) status = "out";
    else if (s.min_qty != null && s.current_qty <= s.min_qty) status = "low";
    else status = "in_stock";
    response.stock = {
      stock_item_id: s.id,
      name: s.name,
      sku: s.sku,
      current_qty: s.current_qty,
      min_qty: s.min_qty,
      selling_price: s.selling_price,
      markup_percent: s.markup,
      status,
    };
  }

  // 9. Suppliers list — derived from the order_rows we already fetched.
  //     Each row already carries `supplier` (see the read shape above),
  //     so we can pass them straight to `aggregateSuppliers`.
  response.suppliers = aggregateSuppliers(
    orderRows.items.map((r) => ({
      id: r.id,
      supplier: r.supplier,
      cost_price: r.cost_price,
      updated: r.updated,
    })),
    supplierNameById,
  ).filter((s) => s.last_cost > 0);

  response.cheapest = computeCheapest(response.suppliers);

  return response;
}

3.2 Endpoint: GET /api/v1/order-items/[id]/context

File: src/app/api/v1/order-items/[id]/context/route.ts

import { NextRequest, NextResponse } from "next/server";
import { getOrderItemContext } from "@/lib/server/order-item-context-service";

export const runtime = "nodejs";
export const dynamic = "force-dynamic";

export async function GET(
  _req: NextRequest,
  { params }: { params: Promise<{ id: string }> },
) {
  const { id } = await params;
  if (!id || typeof id !== "string") {
    return NextResponse.json({ error: "Missing id" }, { status: 400 });
  }
  try {
    return NextResponse.json(await getOrderItemContext(id));
  } catch (err) {
    return NextResponse.json(
      { error: err instanceof Error ? err.message : "Lookup failed" },
      { status: 500 },
    );
  }
}

4. UI components

4.1 <OrderItemContextPanel /> (L1 — inline expandable)

File: src/components/order-item-context-panel.tsx

Props:

interface Props {
  itemId: string;
  /** Default open. */
  defaultOpen?: boolean;
  onApply: (payload: {
    source: "last_quote" | "last_order" | "last_invoiced" | "stock";
    cost_price: number;
    supplier_id?: string;
    source_label: string;
  }) => void;
}

Behaviour:

• Section header with chevron + "Context" label + count of non-null rows.
• 4 rows in this fixed order: Last quote, Last order, Last invoiced, Stock.
• Each row, when present:
  • Left: data source label + relative date (Q-2606-007 · 14d, ORD-2605-018 · 32d, INV-S-4421 · 5d, Stock · 142 units).
  • Middle: 3 monospace cells. R 1 230.00 (cost), R 1 845.00 (selling), 50.0% (markup).
  • Selling cell is dimmed (greyed, text-muted-foreground/50) on the invoice row because invoices don't carry a selling price.
  • Right: Apply button. Disabled when cost_price <= 0.
• Clicking Apply fires onApply({ source, cost_price, supplier_id, source_label }).
• Empty rows render an inline — with the source label greyed. No per-row "no data" message.
• When all 4 rows are null AND suppliers.length === 0: render the same dashed empty-state card that ItemHistoryPopover uses today.
• Visual treatment: indigo border + gradient (matches DeterministicCard so the two products feel like one family). Distinct from the warn-toned AI section we're replacing.

4.2 <OrderItemContextPopoverTrigger /> (L2 — orders page)

File: src/components/order-item-context-popover-trigger.tsx

• Same shape as AiItemHistoryTrigger: ⓘ icon button, lazy fetch on first open.
• Reuses the Popover / PopoverTrigger / PopoverContent primitives from @/components/ui/popover.
• Popover body renders <OrderItemContextPanel> minus the outer collapse chrome (the popover handles visibility).
• Hidden when fetch returns 404.

5. Sprint modal wire-in

File: src/components/order-sprint-process-modal.tsx

Changes:

1. Delete the AI Insights <section> (current lines 410-465).
2. Delete the applyAiSuggestion callback (current lines 209-243).
3. Delete the aiInsightsExpanded state and the Sparkles, Wand2 imports.
4. Replace with:

   <OrderItemContextPanel
     itemId={item.id}
     onApply={applyContext}
   />
   

5. Add the applyContext handler:

   const applyContext = (p: ApplyPayload) => {
     if (p.cost_price > 0) setCost(p.cost_price);
     if (p.supplier_id) setSupplierId(p.supplier_id);
     toast.success(`Applied cost from ${p.source_label}: R${p.cost_price.toFixed(2)}`);
   };
   

6. Keep the existing applyFromHistory(h) and applySupplier(s) handlers and the inline Recent Orders + Available Suppliers sections below the context panel — they cover slightly different data (no description match, broader supplier list) and removing them would be a separate decision.

Selling stays locked (per the brainstormed decision: "Apply copies only cost"). The existing read-only Selling (ZAR) and Markup fields remain untouched.

6. Orders page wire-in

File: src/app/(app)/orders/[id]/page.tsx

Changes:

1. Find every <AiItemHistoryTrigger itemId={...}/> in the items table.
2. Replace with <OrderItemContextPopoverTrigger itemId={...}/>.
3. Delete the now-unused AiItemHistoryTrigger import.

If AiItemHistoryTrigger is referenced elsewhere (sprint list, approvals, etc.), leave those alone — they continue to render the AI history popover, which is unaffected.

7. Edge cases

Case	Behaviour
Target order_item has no SKU and no description	Service returns { match: { kind: 'none' }, ...everything null/empty }. Panel renders dashed empty-state card.
SKU exists but no other rows match	Each row renders the label + — in all 3 numeric cells. No error.
PB read throws on one collection	Other reads still resolve. Service returns the partial context with error: <message>. Panel shows a small bg-destructive/5 strip at the top.
invoiced_date set but supplier_invoice_number empty	Row excluded by the read filter (filter requires supplier_invoice_number != ""). Panel renders — for that row.
Stock current_qty <= 0	status: "out".
Stock current_qty <= min_qty (and min_qty set)	status: "low".
Supplier row references a deleted ID	supplier_name falls back to the ID with a ? marker (same pattern as the current applyAiSuggestion toast).
match.match_kind === "description_fuzzy"	Panel renders a small ⚠ best-guess · NN% confidence chip above the rows (matches the existing pattern in DeterministicCard).

8. Testing

8.1 Backend — order-item-context-service.test.ts

• Pure helper tests already exist in item-history-service.test.ts — we don't re-test them.
• New tests for getOrderItemContext:
  • Returns the expected OrderItemContext shape with a mocked PB.
  • Description-fuzzy fallback returns match_kind: "description_fuzzy" with the correct confidence.
  • Empty PB (target has no SKU, no description) → all-null response.
  • One-collection-fails case → partial context with error set, other reads still resolved.
  • cheapest computed correctly across suppliers.
  • Stock status flips to low when current_qty <= min_qty, out when <= 0.

8.2 Endpoint — order-items/[id]/context/route.test.ts

• 400 on missing id.
• 200 with empty context for an id-less item.
• 500 propagates a thrown service error.

8.3 UI — order-item-context-panel.test.tsx

• Renders all 4 rows when context is fully populated.
• Renders the dashed empty-state card when context is all-null.
• Apply button on each row fires onApply with the right source, cost_price, source_label.
• Selling cell carries the dimmed class on the invoice row.

8.4 UI — order-item-context-popover-trigger.test.tsx

• Hidden when fetch returns 404.
• Renders the panel inside the popover when fetch resolves.
• Fetch fires exactly once on first open (not on every toggle).

10. Files touched (summary)

New:

• src/lib/server/order-item-context-service.ts
• src/lib/server/order-item-context-service.test.ts
• src/app/api/v1/order-items/[id]/context/route.ts
• src/app/api/v1/order-items/[id]/context/route.test.ts
• src/components/order-item-context-panel.tsx
• src/components/order-item-context-panel.test.tsx
• src/components/order-item-context-popover-trigger.tsx
• src/components/order-item-context-popover-trigger.test.tsx

Modified:

• src/components/order-sprint-process-modal.tsx (swap AI Insights section for context panel; remove applyAiSuggestion).
• src/app/(app)/orders/[id]/page.tsx (replace AiItemHistoryTrigger instances with OrderItemContextPopoverTrigger).

Not touched (intentionally):

• src/components/ai-insights-renderer.tsx — still used by item-history-popover.tsx and ai-item-history-trigger.tsx on other screens.
• src/lib/server/item-history-service.ts — only its exports are imported; no behaviour change.
• src/lib/types.ts — OrderItemContext lives in the service file (next to where it's shaped) until something else needs it; the existing ItemHistory* types already in types.ts are reused.