# 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)    │
   └─────────────┬─────────────────�     └─────────────────┬─────────────┘
                 │ 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`

```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`

```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:
```ts
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:
   ```tsx
   <OrderItemContextPanel
     itemId={item.id}
     onApply={applyContext}
   />
   ```
5. **Add** the `applyContext` handler:
   ```tsx
   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.