Every month India’s drug regulator publishes batches that failed quality testing. Bad Batch Tracer extracts them, matches them against what a hospital has already dispensed — even hand-typed or missing batch numbers — and lists exposed patients whose labs suggest harm, for clinical follow-up.
CDSCO publishes “Not of Standard Quality” and spurious-drug lists as PDFs every month. Hospitals rarely cross-check them against what they have already dispensed, so exposed patients are never contacted.
The team plan fixes a few principles that shape the diagram:
If Snowflake can do it, do it there: AI_PARSE_DOCUMENT, AI_COMPLETE with JSON schema, Dynamic Tables, Tasks, Snowpark UDFs, Cortex Search, Semantic Views, Cortex Agents.
Every alert-derived fact shown to a user links back to source_file + page.
Flags say “needs clinician review”, never a diagnosis.
X-Small warehouses, 60 s auto-suspend, a resource monitor; parse each file once and store the result.
Batch numbers on alerts are printed; batch numbers at the pharmacy are scanned, hand-typed or missing. The matcher therefore produces tiers rather than a boolean.
A synthetic hospital with a ground-truth answer key lets the matcher’s accuracy be measured instead of eyeballed. The key (data/synthetic/ground_truth/) is evaluation-only and must never be loaded into a Snowflake schema the app can read.
nsq_baseline_parsed.csv is a pdfplumber baseline, not the product. A hand-verified gold template (nsq_2025-06_central_gold.csv) scores the Snowflake extraction.Seeded exposure: 6 exposed patients, 3 with a signal, all children. The signal is creatinine rising above 2× baseline within 3–10 days — wording is “needs clinician review”, never a diagnosis.
12 exposed, 4 with a signal: fever > 38.5 °C plus a WBC rise within 48 h, matched for 4/4 seeded patients.
scripts/validate_data.py writes docs/validation_report.txt: manifest sha256s, PDF page counts, parse sanity, foreign keys, dispensing inside batch validity, blank-batch share 14.9% (13–17%), seeded NSQ share 5.6% (4–6%), 204 decoy batches.