04-entity-resolution-trgm.sql

-- Pattern 04 — Fuzzy entity resolution with pg_trgm + a review queue
-- ============================================================================
--
-- Problem: ingest pipelines create near-duplicate entities ("Cassette Ghost"
-- vs "Casette Ghost", "DJ Marrow" vs "Marrow"). You need to (a) find likely
-- duplicates cheaply, and (b) never auto-merge — route candidates through a
-- human/agent review queue whose decisions stick.
--
-- pg_trgm gives you indexed string similarity: similarity(a, b) ∈ [0,1] over
-- shared 3-grams, accelerated by a GIN gin_trgm_ops index (schema.sql).
-- ============================================================================

-- ----------------------------------------------------------------------------
-- (a) Point lookup: "does anything similar to this incoming name exist?"
-- The % operator uses the index under pg_trgm.similarity_threshold; the
-- explicit similarity() in the WHERE keeps the cutoff visible and tunable.
-- 0.3 is a sane default for names; raise it if your corpus is short strings.
-- ----------------------------------------------------------------------------
SELECT id, name, round(similarity(name, 'Cassete Ghost')::numeric, 3) AS score
FROM nodes
WHERE similarity(name, 'Cassete Ghost') >= 0.3
ORDER BY score DESC
LIMIT 5;
-- Expected: both "Cassette Ghost" and "Casette Ghost" surface with scores
-- well above 0.3; nothing else comes close.

-- ----------------------------------------------------------------------------
-- (b) Pairwise dedup sweep: find duplicate CANDIDATE PAIRS across the corpus.
-- `a.id < b.id` halves the work and prevents (A,B)/(B,A) double-reporting.
-- Restrict to same kind — a venue named like a person is not a duplicate.
-- On big tables: bound it (LIMIT / statement_timeout) and run it as a
-- background sweep, not a request-path query.
-- ----------------------------------------------------------------------------
SELECT a.name AS name_a, b.name AS name_b,
       round(similarity(a.name, b.name)::numeric, 3) AS score
FROM nodes a
JOIN nodes b ON a.id < b.id AND a.kind = b.kind
WHERE similarity(a.name, b.name) >= 0.45
ORDER BY score DESC
LIMIT 10;
-- Expected: (Cassette Ghost, Casette Ghost) on top at 0.813 — but the rest of
-- the top 10 is "Session Artist NN" cross-matching itself at 0.800, and the
-- real duplicate (DJ Marrow, Marrow), at 0.700, doesn't even make the cut.
-- That inversion is the lesson: trigram score ranks CANDIDATES, it does not
-- make merge DECISIONS. Hence the review queue below (and pattern 05, which
-- blends in embeddings to separate template-similar from semantically-same).

-- ----------------------------------------------------------------------------
-- (c) Review queue with sticky decisions.
-- Same LEAST/GREATEST canonicalization as pattern 03, so a pair enqueued from
-- either direction lands on one row. Two rules encoded in the upsert:
--   * score only ratchets upward (GREATEST) — re-detection with a weaker
--     score must not erase a strong prior signal;
--   * a human 'rejected' is FINAL — re-detection must not resurrect the pair
--     into 'pending' and nag the reviewer forever.
-- ----------------------------------------------------------------------------
INSERT INTO match_review_queue (node_a, node_b, score)
VALUES ('00000000-0000-0000-0000-000000000002',
        '00000000-0000-0000-0000-000000000012', 0.82)
ON CONFLICT (LEAST(node_a, node_b), GREATEST(node_a, node_b))
DO UPDATE SET score      = GREATEST(match_review_queue.score, EXCLUDED.score),
              status     = CASE WHEN match_review_queue.status = 'rejected'
                                THEN 'rejected'          -- sticky
                                ELSE match_review_queue.status END,
              updated_at = now();

-- Reviewer rejects the pair…
UPDATE match_review_queue SET status = 'rejected', updated_at = now()
WHERE LEAST(node_a, node_b)    = '00000000-0000-0000-0000-000000000002'
  AND GREATEST(node_a, node_b) = '00000000-0000-0000-0000-000000000012';

-- …and a later sweep re-detects it from the opposite direction with a higher
-- score. The row must absorb the score but STAY rejected.
INSERT INTO match_review_queue (node_a, node_b, score)
VALUES ('00000000-0000-0000-0000-000000000012',   -- reversed order on purpose
        '00000000-0000-0000-0000-000000000002', 0.91)
ON CONFLICT (LEAST(node_a, node_b), GREATEST(node_a, node_b))
DO UPDATE SET score      = GREATEST(match_review_queue.score, EXCLUDED.score),
              status     = CASE WHEN match_review_queue.status = 'rejected'
                                THEN 'rejected'
                                ELSE match_review_queue.status END,
              updated_at = now();

SELECT count(*) AS rows, min(status) AS status, max(score) AS score
FROM match_review_queue
WHERE LEAST(node_a, node_b)    = '00000000-0000-0000-0000-000000000002'
  AND GREATEST(node_a, node_b) = '00000000-0000-0000-0000-000000000012';
-- Expected: rows = 1, status = 'rejected', score = 0.91

-- Cleanup so the file reruns cleanly.
DELETE FROM match_review_queue;