Objective

Returns processing is high-volume and decision-rich: is the return policy-eligible? Is the customer abusing returns (multiple addresses, same SKU)? Is the SKU in stock for a swap? In this recipe a returns-triage agent makes all three calls in one SQL transaction, recommending refund / replace / escalate with citations.

Step 1: Create orders + returns + policy + inventory

CREATE TABLE IF NOT EXISTS recipe_rt_orders (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER,
  sku          TEXT,
  qty          INTEGER,
  total_usd    DOUBLE,
  ordered_at   TIMESTAMP,
  shipped_at   TIMESTAMP,
  ship_to_zip  TEXT
);

CREATE TABLE IF NOT EXISTS recipe_rt_returns (
  return_id    INTEGER PRIMARY KEY,
  order_id     INTEGER,
  reason       TEXT,
  status       TEXT,             -- 'received' | 'denied' | 'refunded'
  filed_at     TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recipe_rt_inventory (
  sku        TEXT PRIMARY KEY,
  in_stock   INTEGER,
  reorder_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recipe_rt_policy (
  doc_id     INTEGER PRIMARY KEY,
  section    TEXT,
  body       TEXT,
  embedding  VECTOR(384)
);

Step 2: Seed

INSERT INTO recipe_rt_orders VALUES
 (8001,77,'SKU-MOUSE-WL', 1,  49.50,'2026-05-01','2026-05-02','10001'),
 (8002,77,'SKU-MOUSE-WL', 1,  49.50,'2026-05-08','2026-05-09','94016'),
 (8003,77,'SKU-MOUSE-WL', 1,  49.50,'2026-05-14','2026-05-15','60606'),
 (8004,88,'SKU-MONITOR-4K',1, 399.00,'2026-05-12','2026-05-13','78701'),
 (8005,99,'SKU-DESK-STD', 1, 549.00,'2026-05-18','2026-05-21','30301');

INSERT INTO recipe_rt_returns VALUES
 (5001,8001,'arrived damaged','refunded','2026-05-04'),
 (5002,8002,'changed mind','refunded','2026-05-10'),
 (5003,8004,'wrong color','received','2026-05-20');

INSERT INTO recipe_rt_inventory VALUES
 ('SKU-MOUSE-WL', 118, NULL),
 ('SKU-MONITOR-4K',  3, '2026-06-15 00:00:00'),
 ('SKU-DESK-STD',    8, NULL);

INSERT INTO recipe_rt_policy (doc_id, section, body) VALUES
 (1,'Return Window','Unopened items may be returned within 30 days of delivery for a full refund. Opened items eligible for store credit only within 14 days.'),
 (2,'Damaged Goods','Damaged-in-transit returns are eligible for full refund or replacement at no cost to the customer, regardless of return window. Photographic evidence required.'),
 (3,'Restocking Fee','Opened electronics carry a 15 percent restocking fee unless damaged on arrival.'),
 (4,'Abuse Pattern','Customers filing 3 or more returns of the same SKU within 60 days, especially shipped to different addresses, are flagged for review. Refunds are held pending investigation.'),
 (5,'Swap Eligibility','In-stock items may be swapped for color or size variants at no charge within the original return window. Out-of-stock items receive store credit.');

Step 3: Embed

UPDATE recipe_rt_policy SET embedding = EMBED(body);

Step 4: Ground truth — abuse pattern

SELECT customer_id, sku, COUNT(*) AS n_orders, COUNT(DISTINCT ship_to_zip) AS distinct_zips
FROM recipe_rt_orders
GROUP BY customer_id, sku
HAVING COUNT(*) >= 3 AND COUNT(DISTINCT ship_to_zip) >= 2;

Expected: customer 77, SKU-MOUSE-WL, 3 orders, 3 different zips — matches abuse rule from §4.

Step 5: Ground truth — policy by meaning

SELECT section, COSINE_SIMILARITY(embedding, EMBED('customer wants to return an opened monitor for a different color')) AS sim
FROM recipe_rt_policy ORDER BY sim DESC LIMIT 2;

Expected: Restocking Fee and Swap Eligibility rank high.

Step 6: Hand the return to the agent

SELECT AGENT_RUN(
  'aidb-assistant',
  'You are a returns-triage agent. Process return 5003 in recipe_rt_returns. Use execute_query to look up the order, the customer''s prior returns and orders for the same SKU, and current inventory for the affected SKU. Use rag_search on recipe_rt_policy to find applicable clauses. Output: recommended action (refund, replace with swap, deny, or escalate for abuse review), the policy citations you used, and a one-sentence rationale.'
) AS triage_decision;

Cleanup

DROP TABLE IF EXISTS recipe_rt_orders;
DROP TABLE IF EXISTS recipe_rt_returns;
DROP TABLE IF EXISTS recipe_rt_inventory;
DROP TABLE IF EXISTS recipe_rt_policy;

Use it from your agent

REST/SDK: wrap Step 6 in your returns workflow — every RMA gets a triage recommendation before a human looks at it.
MCP: point your ops console at ws://<host>/mcp?token=<jwt> and call query with the Step-6 SQL.
Why in-DB: the inventory state changes per second. A recommendation that says "swap for navy" while the last navy is shipping = bad UX. SynapCores keeps inventory SQL inside the same transaction as the recommendation.

Key Concepts Learned

One SQL function can fuse policy lookup (RAG) + abuse detection (SQL) + inventory check (SQL) into one decision.
Returns-triage automation is a wedge use case: high volume, high cost-of-mistake, structurally agentic.

Agentic Retail Returns Triage