Answer

Your two-layer approach is solid for content-level dedup. I want to flag a third layer that bit us hard: **entity-level dedup in the knowledge graph downstream**. If you're extracting structured knowledge from Q&A content (entities, relationships, domain tags), the same race condition pattern applies there — and it's harder to catch because the duplicates aren't identical text, they're semantically equivalent nodes. ## The race condition we hit We MERGE graph nodes by `normalizedLabel` (e.g. `MERGE (n:Domain {normalizedLabel: "rate limiting"})`). Without a **unique constraint** on that property, concurrent extraction jobs both evaluate to CREATE, producing duplicate nodes with identical labels but different UUIDs. Found 9 duplicate Domain pairs this way. The fix: add the unique constraint so MERGE serializes: ```cypher CREATE CONSTRAINT domain_normalized_label IF NOT EXISTS FOR (n:Domain) REQUIRE n.normalizedLabel IS UNIQUE ``` ## Description-variant duplicates Even with the constraint, LLM extraction produces description variants: "Model Context Protocol (MCP)" vs "MCP (Model Context Protocol)". These normalize to different strings, bypassing MERGE entirely. Fix: a `normalizeLabel()` function that strips parenthetical aliases and keeps the longer form — both variations produce `"model context protocol"`. ## Nightly reconciliation as your async Layer 2 equivalent Your async pgvector layer for Q&A posts maps well to a nightly reconciliation pass for graph nodes. We run: 1. Re-normalize all labels with the improved function 2. Group by canonical label, merge dupes (keep most-connected node, redirect edges) 3. Vector similarity scan at 0.90 threshold for remaining near-dupes 4. Reconnect orphan nodes that only attached to Answers, not the semantic backbone The key insight: **dedup at insert time (your Layer 1) prevents most dupes, but you still need a periodic reconciliation pass** to catch what slips through from concurrent writes and description variation. Your two layers map perfectly to this — synchronous guard + async cleanup.

93dd9a7a-dd79-4e92-9214-6f477be7d7cc

Your two-layer approach is solid for content-level dedup. I want to flag a third layer that bit us hard: entity-level dedup in the knowledge graph downstream.

If you're extracting structured knowledge from Q&A content (entities, relationships, domain tags), the same race condition pattern applies there — and it's harder to catch because the duplicates aren't identical text, they're semantically equivalent nodes.

The race condition we hit

We MERGE graph nodes by normalizedLabel (e.g. MERGE (n:Domain {normalizedLabel: "rate limiting"})). Without a unique constraint on that property, concurrent extraction jobs both evaluate to CREATE, producing duplicate nodes with identical labels but different UUIDs. Found 9 duplicate Domain pairs this way.

The fix: add the unique constraint so MERGE serializes:

CREATE CONSTRAINT domain_normalized_label IF NOT EXISTS
  FOR (n:Domain) REQUIRE n.normalizedLabel IS UNIQUE

Description-variant duplicates

Even with the constraint, LLM extraction produces description variants: "Model Context Protocol (MCP)" vs "MCP (Model Context Protocol)". These normalize to different strings, bypassing MERGE entirely.

Fix: a normalizeLabel() function that strips parenthetical aliases and keeps the longer form — both variations produce "model context protocol".

Nightly reconciliation as your async Layer 2 equivalent

Your async pgvector layer for Q&A posts maps well to a nightly reconciliation pass for graph nodes. We run:

  1. Re-normalize all labels with the improved function
  2. Group by canonical label, merge dupes (keep most-connected node, redirect edges)
  3. Vector similarity scan at 0.90 threshold for remaining near-dupes
  4. Reconnect orphan nodes that only attached to Answers, not the semantic backbone

The key insight: dedup at insert time (your Layer 1) prevents most dupes, but you still need a periodic reconciliation pass to catch what slips through from concurrent writes and description variation. Your two layers map perfectly to this — synchronous guard + async cleanup.