Catch Duplicate & Related Adverse-Event Reports by Meaning

Objective

Individual Case Safety Report (ICSR) volume grows 20–30% a year, and a large fraction are duplicates or closely related reports of the same event — the same reaction, described in different words, arriving through different channels. Keyword matching misses them because the words differ ("muscle pain" vs "bilateral leg aches"). This recipe catches them by meaning, so a reviewer sees the whole cluster instead of processing the same case three times.

Everything runs in the same database that holds the cases — no export to a separate vector store.

Step 1: A tiny ICSR table with an embedding column

The VECTOR(384) column holds the 384-dimensional embedding of each report narrative. EMBED() computes it inline at insert time using the built-in all-minilm model.

DROP TABLE IF EXISTS pv_icsr;

CREATE TABLE pv_icsr (
    case_id   TEXT PRIMARY KEY,
    product   TEXT,
    reaction  TEXT,
    narrative TEXT,
    emb       VECTOR(384)
);

INSERT INTO pv_icsr VALUES
 ('US-2026-001','Atorvastatin','myalgia',
  'Patient reported muscle pain in both legs after two weeks on atorvastatin.',
  EMBED('Patient reported muscle pain in both legs after two weeks on atorvastatin.')),
 ('US-2026-002','Atorvastatin','muscle pain',
  'Male patient, bilateral leg muscle aches, began about 14 days into atorvastatin therapy.',
  EMBED('Male patient, bilateral leg muscle aches, began about 14 days into atorvastatin therapy.')),
 ('US-2026-003','Atorvastatin','headache',
  'Mild transient headache, resolved without intervention.',
  EMBED('Mild transient headache, resolved without intervention.'));

Step 2: Find likely duplicates of a new case

Score every other case against case US-2026-001 by cosine similarity of the narrative embedding. The subquery pulls the reference embedding; COSINE_SIMILARITY ranks the rest.

SELECT case_id,
       reaction,
       COSINE_SIMILARITY(emb, (SELECT emb FROM pv_icsr WHERE case_id = 'US-2026-001')) AS sim
FROM   pv_icsr
WHERE  case_id <> 'US-2026-001'
ORDER  BY sim DESC;

Result — the second report is flagged as a near-duplicate; the headache case is clearly distinct:

Why it matters

A single similarity query separates "the same event again" (0.89) from "a different event" (0.31). Route anything above your threshold to duplicate review, and you stop paying to process the same case twice — the first lever for keeping up with rising ICSR volume. The same query, run across the whole series instead of one reference case, is the basis of signal clustering and related-case grouping.

Pair it with a tamper-evident audit ledger so every dedup decision is inspection-ready.