Objective

When a shipment goes off-plan, an ops analyst spends ~30 minutes joining 4 systems: which SKUs, which customers, which alternates, who to notify. 50–100 exceptions per shift. This recipe wires a shipment-exception agent that does the full join + drafts the comms in one call.

Step 1: Create shipments, customer SLAs, prior exceptions

CREATE TABLE IF NOT EXISTS recipe_lo_shipments (
  shipment_id    TEXT PRIMARY KEY,
  customer_id    INTEGER,
  carrier        TEXT,
  origin         TEXT,
  destination    TEXT,
  current_loc    TEXT,
  status         TEXT,          -- 'in_transit' | 'delayed' | 'delivered'
  promised_eta   TIMESTAMP,
  updated_at     TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recipe_lo_customer_slas (
  customer_id    INTEGER PRIMARY KEY,
  customer_name  TEXT,
  sla_hours      INTEGER,       -- grace window before penalty
  contact_email  TEXT,
  tier           TEXT           -- 'platinum' | 'gold' | 'silver'
);

CREATE TABLE IF NOT EXISTS recipe_lo_carriers (
  carrier_id    INTEGER PRIMARY KEY,
  name          TEXT,
  reliability   DOUBLE,         -- 0..1
  next_pickup   TEXT
);

CREATE TABLE IF NOT EXISTS recipe_lo_past_exceptions (
  exc_id      INTEGER PRIMARY KEY,
  summary     TEXT,             -- what happened + how it was resolved
  embedding   VECTOR(384)
);

Step 2: Seed

INSERT INTO recipe_lo_shipments VALUES
 ('SH-7281', 1,'DHL',     'Shanghai','Hamburg','Hamburg','delayed',  '2026-05-26 12:00:00','2026-05-27 09:00:00'),
 ('SH-7282', 2,'Maersk',  'Rotterdam','Houston','Atlantic','in_transit','2026-05-30 18:00:00','2026-05-27 06:00:00'),
 ('SH-7283', 1,'FedEx',   'Memphis','Toronto','Memphis','in_transit','2026-05-28 09:00:00','2026-05-27 02:00:00');

INSERT INTO recipe_lo_customer_slas VALUES
 (1,'Acme Auto Parts',     24,'ops@acme.example',     'platinum'),
 (2,'BlueRiver Foods',     48,'logistics@blue.example','gold'),
 (3,'Crown Manufacturing', 72,'shipping@crown.example','silver');

INSERT INTO recipe_lo_carriers VALUES
 (1,'DHL Express',        0.94, 'next pickup window: 2026-05-27 16:00'),
 (2,'DB Schenker',        0.91, 'next pickup window: 2026-05-28 09:00'),
 (3,'Kuehne+Nagel',       0.89, 'next pickup window: 2026-05-28 14:00');

INSERT INTO recipe_lo_past_exceptions (exc_id, summary) VALUES
 (1,'2024 — Hamburg customs hold on auto parts. Resolved by rebooking through DB Schenker road-freight to final destination, 36-hour delay. Customer compensated with 2-week credit.'),
 (2,'2025 — Rotterdam port congestion. Maersk vessel diverted. Resolved by air-freighting platinum-tier orders only; gold/silver waited. Cost: 3x air-freight surcharge for 8 shipments.'),
 (3,'2025 — FedEx hub mis-sort in Memphis. Resolved by rebooking via UPS overnight. Customer notified within 1 hour. No comp needed (under-SLA).'),
 (4,'2024 — Suez canal blockage. Resolved by rerouting around Cape of Good Hope, +12-day delay. All affected customers compensated based on tier. Lesson: pre-negotiated platinum air-freight authorization saved $40k in escalations.');

Step 3: Embed

UPDATE recipe_lo_past_exceptions SET embedding = EMBED(summary);

Step 4: Ground truth — past-exception retrieval

SELECT exc_id, summary, COSINE_SIMILARITY(embedding, EMBED('shipment held at Hamburg customs from Shanghai')) AS sim
FROM recipe_lo_past_exceptions ORDER BY sim DESC LIMIT 1;

Expected: exception 1 (2024 Hamburg customs) wins.

Step 5: Ground truth — customer state

SELECT s.shipment_id, s.current_loc, s.status, s.promised_eta,
       c.customer_name, c.sla_hours, c.tier, c.contact_email,
       (julianday('now') - julianday(s.promised_eta)) * 24 AS hours_late
FROM recipe_lo_shipments s
JOIN recipe_lo_customer_slas c ON c.customer_id = s.customer_id
WHERE s.shipment_id = 'SH-7281';

Expected: Acme Auto Parts, platinum, 24h SLA, ~21 hours late on a critical SLA.

Step 6: Run the exception

SELECT AGENT_RUN(
  'aidb-assistant',
  'You are a logistics-exception agent. Handle shipment SH-7281. Use execute_query to join recipe_lo_shipments with recipe_lo_customer_slas to find the customer, SLA window, and how late the shipment currently is. Use execute_query to list available alternate carriers and their next-pickup windows. Use rag_search on recipe_lo_past_exceptions to find how we resolved similar holds before. Output: (a) one-sentence situation summary, (b) recommended action (escalate to air freight, rebook carrier, customer comp), (c) draft customer email — short, specific, with realistic revised ETA, (d) draft carrier dispatch instruction.'
) AS exception_handling;

Cleanup

DROP TABLE IF EXISTS recipe_lo_shipments;
DROP TABLE IF EXISTS recipe_lo_customer_slas;
DROP TABLE IF EXISTS recipe_lo_carriers;
DROP TABLE IF EXISTS recipe_lo_past_exceptions;

Use it from your agent

REST/SDK: trigger Step 6 from your carrier-webhook handler — every "shipment delayed" event arrives with an agent-drafted action plan attached.
MCP: call query from your ops console with the Step-6 SQL.
Why in-DB: shipment state, customer SLA, and carrier capacity must be queryable in one transaction. Stitched stacks lose 30s of latency exactly when ops needs it least.

Key Concepts Learned

Exception handling is structurally agentic — same 4-step lookup every time, with a domain-specific decision at the end.
Tier-aware action (platinum = air freight, silver = wait) is best expressed as JOIN + an LLM that knows the precedents.
The agent doesn't replace the analyst — it prepares the next-action draft so the analyst only edits, not researches.

Agentic Logistics — Shipment Exception