← back to AbramsOS

FDA recall cross-reference: clean drug names, derive generics, openFDA match into Recalls tab

78b80e6fb4ba8598576998e847e690c38e08f3cf · 2026-07-08 15:22:33 -0700 · Steve

- scripts/check-fda-recalls.js: cleans OCR junk from fill drug names (95 fixed), rebuilds
  deduped medication list (83->36 real meds) with generic names, cross-references each vs
  openFDA drug-enforcement (free); 33/36 matched, 300 recall records into medication_recall
- routes/recalls.js + views/recalls.ejs: new 'Medication recalls (FDA)' section, grouped per
  med, Class I/II/III severity badges, worst-first; labeled advisory (ingredient-level match)

Files touched

• M routes/recalls.js
• A scripts/check-fda-recalls.js
• M views/recalls.ejs

Diff

commit 78b80e6fb4ba8598576998e847e690c38e08f3cf
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Jul 8 15:22:33 2026 -0700

    FDA recall cross-reference: clean drug names, derive generics, openFDA match into Recalls tab
    
    - scripts/check-fda-recalls.js: cleans OCR junk from fill drug names (95 fixed), rebuilds
      deduped medication list (83->36 real meds) with generic names, cross-references each vs
      openFDA drug-enforcement (free); 33/36 matched, 300 recall records into medication_recall
    - routes/recalls.js + views/recalls.ejs: new 'Medication recalls (FDA)' section, grouped per
      med, Class I/II/III severity badges, worst-first; labeled advisory (ingredient-level match)
---
 routes/recalls.js            |  21 ++++++++-
 scripts/check-fda-recalls.js | 106 +++++++++++++++++++++++++++++++++++++++++++
 views/recalls.ejs            |  42 +++++++++++++++++
 3 files changed, 168 insertions(+), 1 deletion(-)

diff --git a/routes/recalls.js b/routes/recalls.js
index 35b4d45..cfe267c 100644
--- a/routes/recalls.js
+++ b/routes/recalls.js
@@ -32,7 +32,26 @@ router.get('/recalls', async (req, res) => {
     [DEV_USER_ID]
   );
 
-  res.render('recalls', { matches: r.rows, counts: counts.rows, activeStatus: status });
+  // FDA medication recalls (openFDA drug-enforcement), cross-referenced to your med list and
+  // grouped per medication. 'Class I' sorts before 'Class II'/'Class III' so MIN = worst severity.
+  // Matching is by drug NAME (ingredient) → advisory ("this drug has been recalled"), not lot-specific.
+  const medRecalls = (await db.query(
+    `SELECT m.name, m.generic_name, coalesce(pp.full_name,'—') AS person,
+            count(*)::int AS n, min(mr.classification) AS worst,
+            max(mr.recall_initiation_date) AS latest,
+            (array_agg(mr.url    ORDER BY mr.recall_initiation_date DESC NULLS LAST))[1] AS sample_url,
+            (array_agg(mr.reason ORDER BY mr.recall_initiation_date DESC NULLS LAST))[1] AS sample_reason
+       FROM medication_recall mr
+       JOIN medication m ON m.id = mr.medication_id
+  LEFT JOIN person pp ON pp.id = m.person_id
+      WHERE mr.user_id = $1
+      GROUP BY m.name, m.generic_name, pp.full_name
+      ORDER BY min(mr.classification) ASC, count(*) DESC`,
+    [DEV_USER_ID]
+  )).rows;
+  const medRecallTotal = medRecalls.reduce((s, x) => s + x.n, 0);
+
+  res.render('recalls', { matches: r.rows, counts: counts.rows, activeStatus: status, medRecalls, medRecallTotal });
 });
 
 router.get('/api/recalls', async (_req, res) => {
diff --git a/scripts/check-fda-recalls.js b/scripts/check-fda-recalls.js
new file mode 100644
index 0000000..5f976ca
--- /dev/null
+++ b/scripts/check-fda-recalls.js
@@ -0,0 +1,106 @@
+#!/usr/bin/env node
+// Clean the imported pharmacy drug names, rebuild a deduped medication list with proper
+// GENERIC names, then cross-reference every med against the FDA openFDA drug-enforcement
+// (recall) database. Matches land in medication_recall → the Recalls tab.
+//
+// openFDA is a FREE public API ($0). A drug NAME is not PHI. Idempotent.
+// Usage: node scripts/check-fda-recalls.js
+
+const db = require('../lib/db');
+const audit = require('../lib/audit');
+const fda = require('../lib/fda-fetcher');
+const { id } = require('../lib/ids');
+
+const USER = 'user_steve';
+const SOURCE = 'shangoo-pharmacy-tax-2023-2026';
+const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
+
+// ── name cleaning ────────────────────────────────────────────────────────────────
+// Strip the trailing Rx-number / qty / doctor / OCR-junk that leaked into some drug
+// strings, and normalize internal OCR noise ("750.-MG" → "750 MG").
+function cleanDrug(raw) {
+  let s = String(raw || '').toUpperCase();
+  s = s.split(/\s+\d{5,6}\b/)[0];              // drop from the Rx number onward
+  s = s.split(/\s+[A-Z][A-Z'.]+,\s/)[0];       // drop from a "LASTNAME, " doctor onward
+  s = s.replace(/[|~©"“”‘’]/g, ' ');           // OCR junk chars
+  s = s.replace(/(\d)\.\s*-?\s*(MG|MCG|UNIT)/g, '$1 $2'); // "750.-MG"/"200. MG" → "750 MG"
+  s = s.replace(/\s+/g, ' ').replace(/^[.\-\s]+|[.\-\s]+$/g, '').trim();
+  return s;
+}
+// core active-ingredient name for the FDA search (brand/generic both work in openFDA)
+const OVERRIDE = { 'HYDROCODON-APAP': 'HYDROCODONE', 'HYDROCODONE-APAP': 'HYDROCODONE',
+  'FOLIC': 'FOLIC ACID', 'ACCU-CHEK': null, 'NA': null, 'GLUTOSE': null };
+function genericOf(name) {
+  const w = String(name).trim().split(/\s+/)[0].replace(/[^A-Z\-]/gi, '').toUpperCase();
+  if (Object.prototype.hasOwnProperty.call(OVERRIDE, w)) return OVERRIDE[w];
+  return w || null;
+}
+const strengthOf = (name) => {
+  const m = String(name).match(/(\d[\d.\/-]*\s?(?:MG|MCG|UNIT|%))/i);
+  return m ? m[1].toUpperCase().replace(/\s/g, '') : '';
+};
+
+async function main() {
+  // 1. clean the fill drug names for this source (raw_text keeps the original)
+  const fills = (await db.query(
+    `SELECT id, drug_name FROM prescription_fill WHERE user_id=$1 AND source=$2`, [USER, SOURCE])).rows;
+  let cleaned = 0;
+  for (const f of fills) {
+    const c = cleanDrug(f.drug_name);
+    if (c && c !== f.drug_name) { await db.query(`UPDATE prescription_fill SET drug_name=$1 WHERE id=$2`, [c, f.id]); cleaned++; }
+  }
+  console.log(`cleaned ${cleaned} of ${fills.length} fill drug names`);
+
+  // 2. rebuild the deduped medication list from cleaned fills (dedup by generic+strength)
+  await db.query(`DELETE FROM medication WHERE user_id=$1 AND notes LIKE '%fill history%'`, [USER]);
+  const rows = (await db.query(
+    `SELECT pf.person_id, pf.drug_name, pf.doctor, pf.rx_number, pf.fill_date
+       FROM prescription_fill pf WHERE pf.user_id=$1 AND pf.source=$2
+        AND pf.drug_name NOT ILIKE '%DELIVERY FEE%'
+      ORDER BY pf.fill_date`, [USER, SOURCE])).rows;
+  const meds = new Map();
+  for (const r of rows) {
+    const g = genericOf(r.drug_name);
+    const key = `${r.person_id}|${g || r.drug_name}|${strengthOf(r.drug_name)}`;
+    const m = meds.get(key) || { person_id: r.person_id, name: r.drug_name, generic: g, dosage: strengthOf(r.drug_name), doctor: r.doctor, rx: r.rx_number, start: r.fill_date };
+    if (r.fill_date >= m.start) { m.doctor = r.doctor || m.doctor; m.rx = r.rx_number || m.rx; } // latest wins for prescriber
+    if (r.fill_date < m.start) m.start = r.fill_date;
+    meds.set(key, m);
+  }
+  for (const m of meds.values()) {
+    await db.query(
+      `INSERT INTO medication (id,user_id,person_id,name,generic_name,dosage,prescriber,pharmacy,rx_number,start_date,is_active,notes)
+       VALUES ($1,$2,$3,$4,$5,$6,$7,'Shangoo Pharmacy',$8,$9,true,'Auto-derived from Shangoo Pharmacy fill history.')`,
+      [id('medication'), USER, m.person_id, m.name, m.generic, m.dosage || null, m.doctor || null, m.rx || null, m.start || null]);
+  }
+  console.log(`rebuilt ${meds.size} deduped medications`);
+
+  // 3. FDA recall cross-reference for every med (openFDA, free)
+  const list = (await db.query(`SELECT * FROM medication WHERE user_id=$1 AND is_active`, [USER])).rows;
+  let checked = 0, withHits = 0, totalRecalls = 0; const hitList = [];
+  for (const med of list) {
+    const term = med.generic_name || med.name;
+    if (!term) continue;
+    let hits = [];
+    try { hits = await fda.fetchDrugRecalls(term); } catch (e) { console.error(`  ${term}: ${e.message}`); }
+    checked++;
+    for (const h of hits) {
+      await db.query(
+        `INSERT INTO medication_recall (id,user_id,medication_id,source,recall_number,classification,status,reason,product_description,recall_initiation_date,url,raw_jsonb)
+         VALUES ($1,$2,$3,'openFDA',$4,$5,$6,$7,$8,$9,$10,$11)
+         ON CONFLICT (medication_id, recall_number) DO UPDATE SET status=EXCLUDED.status, reason=EXCLUDED.reason, matched_at=now()`,
+        [id('medrecall'), USER, med.id, h.recall_number, h.classification, h.status, h.reason, h.product_description, h.recall_initiation_date, h.url, JSON.stringify(h.raw)]);
+    }
+    if (hits.length) { withHits++; totalRecalls += hits.length; hitList.push(`${med.name} (${med.person_id ? '' : ''}) → ${hits.length} recall(s) [${[...new Set(hits.map((h) => h.classification))].join(', ')}]`); }
+    await sleep(250); // be gentle on the free API
+  }
+  await audit.log({ actorType: 'user', actorId: USER, objectType: 'medication_recall', objectId: SOURCE,
+    eventType: 'fda_recall_crossref', metadata: { medical: true, source: 'openFDA', checked, medications_with_recalls: withHits, recalls: totalRecalls } });
+
+  console.log(`\nFDA cross-reference DONE — checked ${checked} meds`);
+  console.log(`  ${withHits} medications matched an FDA recall · ${totalRecalls} recall records stored`);
+  if (hitList.length) { console.log('\nMatches:'); hitList.forEach((h) => console.log('  ⚠ ' + h)); }
+  else console.log('  ✓ No FDA recalls matched any of your medications.');
+  process.exit(0);
+}
+main().catch((e) => { console.error('FAILED:', e.message); process.exit(1); });
diff --git a/views/recalls.ejs b/views/recalls.ejs
index 5874466..715d348 100644
--- a/views/recalls.ejs
+++ b/views/recalls.ejs
@@ -5,6 +5,48 @@
   <p class="subtle" style="margin:0;color:var(--text-dim);font-size:13px">CPSC recalls auto-matched to your purchases. Confidence ≥ 0.60 lands here for review.</p>
 </section>
 
+<% const medR = (typeof medRecalls !== 'undefined') ? medRecalls : []; %>
+<% if (medR.length) { %>
+<section class="page-head" style="margin-top:18px">
+  <div>
+    <h1 style="font-size:16px">💊 Medication recalls <span class="subtle" style="font-weight:400">(FDA openFDA)</span></h1>
+    <p class="subtle" style="margin:0;color:var(--text-dim);font-size:13px">
+      Your <strong><%= medR.length %></strong> medications cross-referenced against the FDA drug-enforcement database
+      (<strong><%= medRecallTotal %></strong> recall records). Matched by <em>drug name</em>, so these are
+      <strong>advisory</strong> — “this drug has had recalls,” not necessarily your specific lot.
+    </p>
+  </div>
+</section>
+<section class="glass" style="padding:8px 6px;overflow:auto">
+  <table class="mr-table">
+    <thead><tr><th>Severity</th><th>Medication</th><th>Person</th><th class="num">Recalls</th><th>Latest</th><th>Most-recent reason</th></tr></thead>
+    <tbody>
+      <% medR.forEach(function(x){
+           var badge = x.worst==='Class I' ? '#f87171' : x.worst==='Class II' ? '#fbbf24' : '#9aa0aa'; %>
+        <tr>
+          <td><span class="sev" style="background:<%= badge %>22;color:<%= badge %>;border:1px solid <%= badge %>55"><%= x.worst || '—' %></span></td>
+          <td><%= x.name %><% if (x.generic_name && x.generic_name.toUpperCase() !== (x.name||'').toUpperCase()) { %> <span class="subtle">· <%= x.generic_name %></span><% } %></td>
+          <td><%= x.person %></td>
+          <td class="num"><a href="<%= x.sample_url || 'https://www.fda.gov/drugs/drug-recalls' %>" target="_blank" rel="noopener noreferrer"><%= x.n %></a></td>
+          <td class="mono"><%= x.latest ? String(x.latest).slice(0,10) : '' %></td>
+          <td class="reason subtle"><%= (x.sample_reason || '').slice(0,90) %></td>
+        </tr>
+      <% }) %>
+    </tbody>
+  </table>
+</section>
+<style>
+  .mr-table{width:100%;border-collapse:collapse;font-size:12.5px}
+  .mr-table th,.mr-table td{text-align:left;padding:6px 10px;border-bottom:1px solid rgba(255,255,255,.06);vertical-align:top}
+  .mr-table th{font-size:11px;text-transform:uppercase;letter-spacing:.04em;color:var(--text-dim)}
+  .mr-table td.num,.mr-table th.num{text-align:right}
+  .mr-table td.mono{font-variant-numeric:tabular-nums;white-space:nowrap}
+  .mr-table td.reason{max-width:340px}
+  .sev{display:inline-block;padding:1px 8px;border-radius:999px;font-size:11px;font-weight:600;white-space:nowrap}
+</style>
+<% } %>
+
+<section class="page-head" style="margin-top:22px"><h1 style="font-size:16px">🛒 Product recalls <span class="subtle" style="font-weight:400">(CPSC · purchases)</span></h1></section>
 <section class="glass" style="padding:14px 18px">
   <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center;font-size:12px">
     <span style="color:var(--text-dim);margin-right:6px">Filter:</span>

← 542a632 Import Shangoo Pharmacy tax profile: prescription_fill table  ·  back to AbramsOS  ·  NDC-precise FDA recall match: flag fills whose actual dispen b02722e →