Return Reason Coder & Defect Tracker

The problem this kills

Your returns and warranty claims arrive as free text. One person types "screen cracked," another writes "arrived broken display," a third logs "DOA - glass shattered in box." To a spreadsheet these are three different things, so nobody can see that they are all the same defect - and that 80% of them came from one lot.

That blindness is expensive. A batch defect that should have triggered a supplier conversation in week one instead drips in as scattered, miscoded one-offs for a whole quarter. By the time someone notices the pattern, you have shipped thousands more of the bad lot, eaten the return shipping, and burned the customer trust.

The fix isn't a bigger spreadsheet. It's a tool that reads every messy reason, maps it to a controlled list of reason codes, and then watches for one SKU or one lot lighting up faster than the rest.

What you'll build

A private, login-protected web app for your quality, product, and support-ops team that:

• Loads returns/claims from a CSV or Google Sheet (return/claim ID, free-text reason, SKU, and lot/batch if you have it).
• Codes every free-text reason to your controlled reason taxonomy using AI - and flags anything it isn't sure about, plus proposes new reason codes when the text doesn't fit.
• Computes trends by SKU and by lot/batch, so concentration in a single batch jumps out as the key defect signal.
• Flags spikes - a SKU or lot whose return rate or volume is running hot versus its own baseline.
• Puts a human in charge: a quality analyst reviews the AI's coding and any proposed new codes, and nothing feeds reporting or fires an alert until they approve.
• Exports a defect report (and can email a digest via Resend) for your supplier conversations and quality reviews.

What's inside the Implementation Plan

• A copy-paste runbook you drop into Claude Code - no prior coding needed.
• It opens by interviewing you about your business - your returns process, the systems and sheets you use, your real field names and SKU/lot conventions, your volumes, and your messy edge cases - so the tool is tailored to how you actually work, not a generic template. The agent reads a short spec back to you and waits for your thumbs-up before it builds anything.
• Step-by-step build prompts, each ending in a ready-to-paste instruction.
• A controlled reason-taxonomy data model you can edit, with confidence scoring and a review queue.
• SKU and lot/batch trend math with spike flagging.
• A "No API yet?" fallback so you can build and run the whole thing today from a Sheet/CSV and export clean results in the columns your system expects.

The governance it includes (this is the point)

This isn't a toy script - it's built like an internal system you can trust:

• Login so only your team can get in.
• Row-level security so a user only ever sees their own organization's data.
• A complete audit trail - who coded what, who approved which alert, and when.
• A hard human-in-the-loop approval gate - the AI drafts the reason codes and the spike alerts; a quality analyst reviews and approves, and only then do the figures feed reporting or raise a defect alert.
• Duplicate guards keyed on return/claim ID so the same return can't be counted twice and skew a trend.

Who it's for

Quality, product, and support-ops teams hunting for systemic defects - anyone who suspects there's a batch problem hiding inside a pile of inconsistent return notes and wants to find it before the next container lands.

You've got this. Paste the first prompt and let the interview tailor it to your business.