Competitor Pricing Match System — Architecture Revision Document¶

Status: Design complete for ingestion and matching logic. Two runtime architectures documented as open alternatives (see Section 7). Confidence/ranking logic for semantic candidates deliberately left unbuilt (see Section 8).

1. Problem Statement¶

Given a merchant's top-N best-selling products, find the equivalent product at each of that merchant's ~10 tracked competitors, and compare prices. The core technical challenge is product matching: nobody hands you a mapping of "your product X = competitor's product Y" — it has to be inferred from unstructured product data (title, description, price, identifiers).

Conceptual framing: the matching step is a semantic search problem (retrieval only). Turning a matched pair into a written price comparison for a human or an LLM to read is an optional RAG step layered on top — retrieval and generation are separate concerns, and this document is primarily about the retrieval side.

2. Data Model¶

One DuckDB file per merchant. The merchant boundary is the file boundary — no merchant_id column is needed anywhere in the schema, and no cross-merchant joins are possible even by accident. The nightly/runtime process loops over each merchant's file independently.

2.1 `products` (the merchant's own catalog)¶

Column	Notes
`product_id`	Primary key
`title`	Semantic field — part of the canonical string
`description`	Semantic field — part of the canonical string
`color`	Plain column — not part of hash or embedding
`size`	Plain column
`price`	Plain column
`brand`	Plain column
`gtin` / `upc` / `sku` / `mpn`	Plain columns — raw identifiers as received
`gtin_normalized`	Computed at ingestion: all identifier formats (UPC-12, EAN-13, GTIN-8/14) left-padded to a canonical 14-digit form, so different formats of the same identifier compare equal
`embedding`	`FLOAT[3072]` — `text-embedding-3-large` default dimension
`content_hash`	Hash of `title + description` only, normalized (trimmed, lowercased) before hashing
`embedding_model_version`	e.g. `text-embedding-3-large` — records what produced the vector, in case the model ever changes
`embedding_status`	`pending` / `ready` / `failed`
`retry_count`	Guards against infinite retry loops on a permanently bad row
`status`	Not yet implemented — flagged gap. Should filter to `active` at every ingestion and matching stage; discontinued products currently have no exclusion mechanism
`updated_at`	Last write to this row, any field

2.2 `competitor_products`¶

Same shape, plus:

Column	Notes
`comp_product_id`	Primary key — candidate: the crawler's own stable object ID (e.g. Diffbot's `diffbotUri`)
`competitor_id`	Which of the merchant's ~10 tracked competitors this row belongs to
`source_url`	The crawled page URL
`last_crawled_at`	When this row was last successfully refreshed by a crawl
...everything else identical to `products`	title, description, brand, color, price, identifiers + `gtin_normalized`, embedding, hash, status fields

2.3 `matches` (only if Option A — precomputed matching — is chosen; see Section 7)¶

Column	Notes
`product_id`	FK to `products`
`competitor_id`	Which competitor this candidate came from
`comp_product_id`	FK to `competitor_products`
`match_method`	`exact_identifier` or `semantic`
`match_confidence`	`1.0` for exact matches; raw cosine distance (lower = better) for semantic matches — not an invented score, see Section 8
`matched_at`	Timestamp of this matching run

3. Competitor Data Acquisition¶

3.1 Approach¶

Rather than building a custom scraper, competitor product data is pulled via a structured web-extraction service — point it at a URL or a whole domain, get back clean structured JSON (title, description, price, brand, identifiers, images) without writing per-site parsers.

Primary candidate: Diffbot (Product API + Crawlbot). Alternatives in the same category: Zyte (Automatic Extraction), Bright Data, Apify (per-platform scraper actors). All solve the same problem; Diffbot's Product API response shape is documented below since it directly drives the ingestion schema.

Explicitly ruled out for this role: Price2Spy and similar price-monitoring tools. These are URL-based price trackers, not discovery engines — you must already know and supply the exact competitor URL that matches your product (insertProduct() / insertUrl()), and they only report back price changes on that known URL. They do not solve the "which competitor product is the same as mine" discovery problem, which is the actual hard part of this system.

3.2 Diffbot Product API — response fields relevant to ingestion¶

title            — product title
text             — description
brand            — brand name
offerPrice       — current/actual price
regularPrice     — pre-discount price
sku, upc, mpn    — store/manufacturer identifiers
gtin             — covers all EAN/UPC/GTIN formats (8/12/13/14 digit)
isbn             — books only
category         — BETA, less reliable
availability     — BETA, less reliable
colors           — BETA, sparse — Diffbot has no dedicated size field at all
pageUrl          — source URL
diffbotUri       — stable dedup ID, candidate primary key

Reliability caveats, explicit: - Identifier fields (gtin/upc/sku) are only populated if the source site publishes them anywhere on the page — many competitor sites will have empty values, not extraction failures. - Extraction quality correlates with how well-structured the source site's HTML/schema markup is — sites with proper schema.org markup extract near-perfectly; messy sites rely on Diffbot's heuristic ML extraction, with a real (if small) error rate. - Fields marked BETA should not be treated as load-bearing for matching logic.

3.3 Crawl scheduling¶

Crawlbot supports native recurring crawls — no custom scheduler needed on our side. A crawl job is configured once with a repeat frequency (1.0 = daily); Diffbot's own infrastructure runs each "round" on that cadence. Our system's job is reactive: detect when a round finished, pull that round's result collection.

Cap: 1,000 active crawl jobs per API token. At 10 competitors × 32 merchants = 320 jobs, comfortably under the cap.
All crawling happens on Diffbot's infrastructure, against the competitor's site — no load is placed on our own servers or IPs, and their infrastructure (not ours) is responsible for not looking abusive to the target site.
Crawl duration is the actual bottleneck in the nightly window — realistically tens of minutes to a couple of hours per full-catalog crawl round, dwarfing every other step in this pipeline.

4. Ingestion Pipeline (Nightly, Batch — No Queue)¶

Explicitly rejected: an event-driven RabbitMQ + 10-minute watchdog design. This was the original design direction but was abandoned once it became clear the actual constraint is a 24-hour SLA, not near-real-time freshness. A single nightly batch job is simpler, has fewer moving parts (no standing watchdog process, no queue infrastructure), and comfortably meets the actual requirement.

4.1 Steps, per merchant file, every night¶

Staging load (own products): truncate + bulk-append from the ERP source, one transaction.
Staging load (competitor products): truncate + bulk-append from the latest completed Diffbot crawl round, one transaction.
Canonical sync (own): for each staging row, build the canonical string (title + description, naturally phrased — not a label:value template), hash it, compare to the canonical table's stored hash. Same hash → update non-semantic columns only (price, color, size, brand, identifiers), no embedding touched. Different hash or new row → update everything, mark embedding_status = pending.
Canonical sync (competitor): identical logic against the competitor canonical table.
Embed home: batch every pending row in products to the OpenAI embeddings API in as few calls as possible (the API accepts an array of inputs per call), write vectors back, flip to ready.
Embed away: identical for competitor_products.

Staging vs. canonical, explicit distinction: staging tables are fully disposable — wiped and reloaded every run, no history. Canonical tables are never wiped — only ever upserted row by row, since they carry accumulated state (embedding, embedding_status) that must persist across nights.

4.2 Why title+description only (not title+description+color+size)¶

Originally the canonical string included color and size. This was revised once the asymmetry between the two data sources became clear: the merchant's own schema always has these fields populated consistently; scraped competitor data frequently has them sparse or missing (Diffbot has no dedicated size field at all). A consistently-present-but-empty field on one side and a populated field on the other introduces structural noise into the embedding that has nothing to do with actual product meaning.

Resolution: embed only title + description, phrased as natural text (e.g. "Wireless Mouse — 2.4GHz ergonomic design, black", not Title: ... | Description: ...). Color, size, brand, and identifiers remain plain columns, used only for exact-match logic and (if built) post-retrieval filtering — never embedded, never part of the change-detection hash.

5. Embedding Model¶

Chosen: OpenAI text-embedding-3-large, called directly (not via Azure OpenAI — explicitly ruled out; product text is sent to OpenAI's infrastructure directly).

Default output: 3072 dimensions. Matryoshka truncation to a shorter dimension (e.g. 1024) is supported if storage/query-speed ever becomes a real constraint — not adopted here, but the option exists without needing to swap models.
Cost is a non-factor at this catalog scale — full bulk re-embed of a large combined catalog runs to cents, not dollars; ongoing nightly cost (only changed rows re-embedded, via the hash check) is negligible.
.NET integration: official OpenAI NuGet package (not Azure.AI.OpenAI). OpenAIClient constructed with a dedicated API key, EmbeddingClient scoped directly to the model name string (no deployment-name indirection, unlike Azure).
Auth: a separate, dedicated API key from whatever key powers any LLM/chat calls elsewhere in the stack — not shared. Ideally scoped/restricted to the embeddings endpoint only, so a leak of this key cannot be used to run up chat-completion costs.

6. Matching Logic¶

Runs after ingestion is current (embeddings up to date for both tables). Fully local — no network calls, no API cost — once embeddings already exist.

6.1 Tier 1 — Exact identifier match¶

-- Only shown conceptually here; see Section 7 for the runtime-specific version
SELECT p.product_id, c.competitor_id, c.comp_product_id, 1.0 AS confidence
FROM products p
JOIN competitor_products c ON p.gtin_normalized = c.gtin_normalized
WHERE p.gtin_normalized IS NOT NULL;

Certain, free, no vector search involved. Identifiers are normalized to a common 14-digit form before comparison, since the same physical product can appear as a UPC-12 on one site and an EAN-13 on another.

6.2 Tier 2 — Semantic candidate retrieval¶

Per product, per competitor — not pooled across competitors. A pooled top-5-overall design was explicitly rejected: it let one competitor with many close matches crowd out weaker-but-real matches from other competitors. The requirement is that every one of the merchant's ~10 competitors gets its own top-10, always, so the report can show "here's what Competitor A has" and "here's what Competitor B has" independently, even for a product with a confirmed exact match at one competitor and only fuzzy candidates elsewhere.

SELECT
    p.product_id,
    comp.competitor_id,
    cand.comp_product_id,
    cand.distance
FROM
    products p
CROSS JOIN
    (SELECT DISTINCT competitor_id FROM competitor_products) comp,
LATERAL (
    SELECT
        c.comp_product_id,
        array_cosine_distance(p.embedding, c.embedding) AS distance
    FROM competitor_products c
    WHERE c.competitor_id = comp.competitor_id
      AND c.embedding_status = 'ready'
      AND NOT EXISTS (
          -- exclude anything already captured by Tier 1 for this exact pairing,
          -- so a top-10 search returns 10 genuinely NEW candidates, not 9 + 1 wasted duplicate
          SELECT 1 FROM matches m
          WHERE m.product_id = p.product_id
            AND m.competitor_id = comp.competitor_id
            AND m.comp_product_id = c.comp_product_id
      )
    ORDER BY distance ASC
    LIMIT 10
) AS cand;

Why LATERAL: a plain join has no way to express "top 10, freshly, per group" — it can only match rows together based on a fixed condition. LATERAL re-runs the inner subquery once per outer row (here, once per product+competitor pair), each time scoped to that specific pair, which is exactly the "for each X, find X's own top N" pattern a regular join cannot express.

Result shape: up to 11 rows per product-per-competitor pairing (1 exact + 10 semantic), fewer if no exact match exists or the competitor's catalog has fewer than 10 relevant products.

6.3 `array_cosine_distance` — what it measures and returns¶

Range: 0 to 2. 0 = identical meaning, 1 = unrelated, 2 = opposite. This is distance, not similarity — DuckDB's function already applies 1 - cosine_similarity before returning a value, so ORDER BY distance ASC correctly sorts closest-first.
Symmetric — argument order does not affect the result.
Not a universal convention across systems: pgvector's <=> operator matches DuckDB's convention (distance, 0–2, lower is closer). Pinecone's cosine metric returns similarity by default (higher is better) — the opposite direction. Always check a new system's docs before writing ORDER BY; do not assume based on habit.
Chosen deliberately over Euclidean distance or dot product because cosine ignores vector magnitude and measures direction (meaning) only — the right property for comparing texts of different length (a short title vs. a longer description) without letting length itself leak into the similarity signal.

6.4 HNSW index¶

INSTALL vss;
LOAD vss;

CREATE INDEX comp_embedding_idx
ON competitor_products
USING HNSW (embedding)
WITH (metric = 'cosine');

What it is: a graph-based index (Hierarchical Navigable Small World) purpose-built for "find nearest neighbors in high-dimensional space" — a problem a normal sorted index (B-tree) cannot help with at all, since "closeness" is relative to a different query vector every time, not a fixed sortable property of each row.
Mechanism, briefly: the index links each vector to a small set of actual nearby neighbors, in layers — a sparse top layer for long-range jumps, denser lower layers for local refinement. A search hops through the graph toward the query point instead of scanning every row.
DuckDB-specific setup notes: vss is a core extension — INSTALL vss downloads it once over the network (cached on disk afterward, works offline from then on); LOAD vss must run every session/connection. Via DuckDB.NET, both are plain SQL executed through the standard DuckDBCommand — no separate installation mechanism.
Performance impact at actual current scale (~15,000 active products): brute-force live search was independently calculated at single-digit milliseconds per product even without the index (roughly 9M floating-point operations per competitor, ~90M across all 10). The index is not strictly required to hit acceptable latency at this catalog size — it becomes necessary insurance if/when the catalog scales toward the ~100k+ range, where a hypothetical full brute-force nightly cross-computation was estimated at potentially hours without it, versus low single-digit minutes with it.

7. Runtime — Two Open Architectural Options¶

This is the one major decision still explicitly left open in this design. Both are documented here as live alternatives; a final choice has not been locked in.

Option A — Precomputed nightly match table¶

Extends the nightly batch job with additional steps: snapshot the previous night's matches table, wipe it, re-run Tier 1 + Tier 2 for every product against every competitor, insert the full result set fresh each night.

Runtime query becomes a plain lookup, no vector computation at ask time:

SELECT
    p.product_id, p.title, p.price,
    m.competitor_id, m.match_method, m.match_confidence,
    c.title AS competitor_title, c.price AS competitor_price
FROM products p
JOIN matches m ON m.product_id = p.product_id
JOIN competitor_products c ON c.comp_product_id = m.comp_product_id
WHERE p.product_id IN (/* the requested product_ids */)
ORDER BY
    p.product_id, m.competitor_id,
    CASE WHEN m.match_method = 'exact_identifier' THEN 0 ELSE 1 END,
    m.match_confidence ASC;

Pros: simplest possible runtime query; matches are always pre-computed and ready; natural audit trail if matches_previous snapshots are retained. Cons: computes and stores matches for every product every night regardless of whether anyone ever asks about it — genuine wasted compute given the actual usage pattern (occasional queries, not daily checks per product). Matches are only as fresh as the last nightly run; requires explicit invalidation/snapshot reasoning.

Option B — Live, on-demand matching (current lean)¶

Drop the matches table and the nightly matching steps entirely. The nightly job stops after ingestion (Section 4) — embeddings stay fresh, nothing else runs on a schedule for the matching side.

The agent's tool call runs Tier 1 + Tier 2 live, per requested product, at ask time — the same SQL from Sections 6.1/6.2, executed directly against products and competitor_products when a product is actually asked about, with no matches table to write into or read from.

Pros: zero wasted compute on products nobody queries; always computed against current data, no staleness or snapshot/invalidation logic needed; one less table and one less nightly stage to maintain. Cons: no persisted record of "what did we tell the merchant last time" unless a separate logging step is deliberately added; every ask re-runs the computation (cheap at current scale, per Section 6.4, but worth re-confirming if catalog size grows significantly).

Why this became the leaning choice: the original justification for nightly precomputation was "vector search is too slow to run live" — this was empirically wrong at the actual ~15,000-product scale (milliseconds per product, per the math in Section 6.4). The parts of the pipeline that must stay scheduled (embedding generation, competitor crawling) already do, independent of this decision — so the marginal benefit of also precomputing matches nightly, given that nobody checks pricing daily, does not clearly outweigh the wasted-compute cost of doing so.

8. Deliberately Unbuilt: Candidate Filtering / Scoring¶

Status: explicitly rejected in its first form, no replacement built yet.

An early design proposed a weighted scoring formula combining semantic distance with brand-match and color-match bonuses and a price-implausibility penalty (e.g. (1 - distance) + 0.1×brand_match + 0.05×color_match - 0.3×extreme_price_gap). This was rejected on the grounds that the weight values (0.1, 0.05, 0.3) were invented placeholders with no empirical justification — a confidence number that looks precise but isn't backed by anything defensible.

Two legitimate paths forward were identified but not built:

Hard filter rules — explicit, inspectable, non-scored logic (e.g. "discard if price differs by more than Nx," "discard if brand is present on both sides and disagrees"), ranking whatever survives purely by semantic distance.
Data-driven weights — label a sample of real match/no-match outcomes once real data exists, derive actual weights (e.g. via logistic regression) from that labeled data rather than guessing.

Current state: no filtering happens. Every Tier 2 candidate (up to 10 per product per competitor) is surfaced as-is, including likely false positives (bundles, wrong category, wildly mismatched price). The raw cosine distance is stored/returned as the only signal — an honest, real measurement, just not filtered or reweighted. This is a known, named gap, not an oversight — revisit once real labeled outcome data exists to justify either path above.

9. Known Open Items — Summary¶

Item	Status
Active/discontinued product filtering	Not implemented — should filter to `status = 'active'` at every ingestion and matching stage; currently absent
Candidate filtering/scoring (Section 8)	Deliberately unbuilt — rejected an unsubstantiated scoring formula, no replacement chosen
Runtime architecture (Section 7)	Open — two documented options, no final decision recorded
Match audit/history logging	Only exists under Option A (via `matches_previous` snapshot); no equivalent designed for Option B
HNSW index necessity	Built and recommended, but not strictly required for acceptable latency at current (~15k product) scale — becomes necessary at significantly larger catalog sizes

10. Performance Reference (for future scale decisions)¶

Scale	Brute-force estimate	With HNSW
~15,000 products, ~10 competitors (current)	Milliseconds per product live search	Not required, but harmless insurance
~140,000 products, ~140,000 competitor rows (hypothetical, full nightly cross-computation)	Potentially hours (naive) to ~6–7 min (vectorized)	Low single-digit minutes

The nightly crawl step (Section 3.3) — not vector search — is the actual bottleneck in the nightly window at any realistic scale discussed here.