# Catalog Sync and Normalisation — Design **Date:** 2026-08-10 **Status:** Approved, ready for implementation plan **Scope:** Sub-project B of three. Depends on A (Shopify OAuth connector), which is built. --- ## 1. Purpose Pull a merchant's product catalog from two kinds of source — the Shopify Admin API and an authenticated HTTP product API — normalise both into one shape, and land them in `strategist_products` so the chatbot recommends real products with real prices, stock and categories. **Success criterion:** after a sync, asking the chat about snowboards returns cards for products that exist in `galaxiq-braexaal.myshopify.com`, and no DRAFT, ARCHIVED or out-of-stock product is ever recommended. **Deliberately excluded:** all LLM inference. Every pass in B is deterministic, so it is fast, free, reproducible, and testable against fixtures. Enrichment moves to C. ## 2. Sequencing | | Scope | Status | |---|---|---| | **A** | OAuth connector: install, callback, encrypted credentials | built, verified live | | **B** | Both ingestion sources, schema reshape, deterministic normalisation | this document | | **C** | LLM field-map authoring, enrichment, quality gating, taxonomy tree | not yet specced | C stays small because of a deliberate seam in B: the declarative field map is data, and B builds its executor. C's LLM only *authors* that same config. Nothing in C re-implements normalisation. ## 3. Evidence base Profiled from the live store on 2026-08-10 via the token A issued. 17 products. | Field | Fill rate | Design consequence | |---|---|---| | `onlineStoreUrl` | **0/17 (0%)** | URL must be built from `handle`; this is the only path, not a fallback | | `category.fullName` | 1/17 (5%) | native taxonomy effectively absent; the chain must work without it | | `productType` | 16/17 (94%) | the real category source | | `vendor` | 17/17 (100%) | but 8 are `Galaxiq`, the shop name | | `tags` | 13/17 (76%) | richest structured signal available | | `description` | 2/17 (11%) | almost nothing to embed | | `collections` | 10/17 (58%) | low value — see below | | `metafields` | 6/17 (35%) | | Distributions that drive specific rules: - **Status:** 15 ACTIVE, 1 DRAFT, 1 ARCHIVED. - **Vendors:** `Galaxiq` ×8, `Snowboard Vendor` ×5, `Hydrogen Vendor` ×3, `Multi-managed Vendor` ×1. - **Collections:** `Automated Collection` ×8, `Hydrogen` ×3, `Home page` ×1. None is a category. `Hydrogen` is a vendor name; `Automated Collection` and `Home page` are merchandising. - **Variant option names:** `Title` ×17, `Color` ×5, `Denominations` ×4. `Title` is Shopify's placeholder for single-variant products, value `Default Title`. - **Variants:** 26 total, 1–5 per product, **23 with no SKU**. - **Commercial:** 1 variant with `compareAtPrice`; 2 products fully out of stock; 0 negative inventory quantities; price range 9.95–2629.95; two genuine multi-variant spreads (Gift Card 10–100, Ski Wax 9.95–49.95). - **Currency: INR.** Not USD. This store is a test fixture, not a representative catalog — 14 of 17 products are snowboards. It is used to prove correct handling of sparse and awkward data, not to tune ranking behaviour. ## 4. The hazard that shapes the design `strategist_products` already holds crawled products. B adds two more producers to the same table. The existing stale-delete is not scoped by producer: ```python "DELETE FROM {}.strategist_products WHERE product_url <> ALL(%s)" ``` Run after a Shopify sync with `crawl_complete=True`, this deletes **every crawled product**, because none of their URLs appear in the Shopify list. `products.save_products` therefore cannot be reused for B. Every write and every delete in B is scoped by `source_kind` and `source_ref`. `save_products` keeps its current behaviour for the crawler and is left alone apart from the new columns. ## 5. Architecture Five modules, flat, matching the existing `app/services/*.py` convention. | Module | Responsibility | |---|---| | `app/services/catalog_fieldmap.py` | Executes a declarative field map against a JSON record. Pure, no I/O. | | `app/services/catalog_shopify.py` | Cursor-paginated GraphQL fetch → `SourceProduct` list. Shopify's schema is fixed, so its mapping is code, not config. | | `app/services/catalog_http.py` | Offset-paginated authenticated fetch → `SourceProduct` list, via the field map. | | `app/services/catalog_normalise.py` | Deterministic passes over `SourceProduct`. Source-agnostic; no network, no DB. | | `app/services/catalog_sync.py` | Orchestration: fetch → normalise → validate → persist → report. | Plus `app/api/endpoints.py`: one route, `POST /catalog/sync`, taking `tenant_id` and an optional `source_ref`. ### The seam Both adapters emit the same `SourceProduct`, so normalisation is written and tested once. `SourceProduct` is field-mapped but **not** normalised — raw merchant values, target field names: ``` SourceProduct { external_id, title, description, brand, product_url, image_url, raw_category, product_type, tags[], collections[], status, variants[ {sku, price, compare_at_price, available, options{name: value}} ], attributes_raw[ {key, value, source} ], source_kind, source_ref, currency } ``` ## 6. Data model ### Reshaped `strategist_products` New columns, added to the existing table: ```sql source_kind TEXT NOT NULL DEFAULT 'crawl', -- 'shopify' | 'http_api' | 'crawl' source_ref TEXT, -- shop domain, API base URL, or site external_id TEXT, brand TEXT, taxonomy_path TEXT[] NOT NULL DEFAULT '{}', taxonomy_source TEXT, -- native|product_type|collection|tag|none raw_category TEXT, price_cents INT, price_max_cents INT, compare_at_cents INT, currency TEXT, on_sale BOOLEAN NOT NULL DEFAULT false, in_stock BOOLEAN NOT NULL DEFAULT true, status TEXT, -- ACTIVE | DRAFT | ARCHIVED attributes JSONB NOT NULL DEFAULT '[]', quality_score REAL, content_hash TEXT, record_hash TEXT, synced_at TIMESTAMPTZ ``` Retained unchanged so existing consumers keep working: `product_key`, `name`, `description`, `image_url`, `product_url`, `category`, `ctas`, `options`, `raw`, `related_keys`, `extracted_at`. `category` is kept as a **denormalised leaf** of `taxonomy_path`, so `products.list_products(category=…)`, `match_products()` and `to_card()` need no change. Indexes: `(source_kind, source_ref)`, `(status)`, `(in_stock)`. ### Migration, not creation `bootstrap_tenant` uses `CREATE TABLE IF NOT EXISTS`, which will not alter an existing table. Existing tenants already hold crawled rows. A migration function adds each column with `ALTER TABLE … ADD COLUMN IF NOT EXISTS`, runs inside one transaction per tenant, and is idempotent. `source_kind` defaults to `'crawl'`, which correctly labels every pre-existing row. `bootstrap_tenant`'s `CREATE TABLE` is updated in the same change so new tenants get the full shape directly. This is the riskiest part of B. It runs against live tenant data and is therefore covered by its own test that creates a schema with the old shape, populates it, migrates, and asserts no row was lost and every pre-existing row is labelled `crawl`. ### `product_key` `{source_kind}:{external_id}` — for example `shopify:gid://shopify/Product/9298522996962`. Stable across syncs, cannot collide with the crawler's URL-derived keys, and does not depend on SKU, which 23 of 26 variants lack. ## 7. Declarative field map Stored in the source's `config` JSONB in `product_sources` (created by A). Shopify does not use one; its mapping is code. ```json { "records_path": "$.products", "pagination": {"style": "offset", "limit_param": "limit", "offset_param": "skip", "page_size": 30, "total_path": "$.total"}, "fields": { "external_id": "$.id", "title": "$.title", "description": "$.description", "brand": "$.brand", "raw_category": "$.category", "image_url": "$.thumbnail", "price": "$.price", "discount_percentage": "$.discountPercentage", "stock": "$.stock", "availability": "$.availabilityStatus", "tags": "$.tags" }, "url_template": "https://shop.example.com/product/{external_id}", "currency": "USD" } ``` Two fields are **required** for an HTTP source and cannot be inferred: - **`url_template`** — the reference API returns no product URL at all, and §10 rejects a product without one. Without a template the source yields zero valid products. - **`currency`** — the reference API carries no currency anywhere. Never defaulted. The executor supports a deliberately small path syntax: `$.a.b`, array indexing `$.a[0]`, and nothing else. Anything richer belongs in C, where the LLM authors these maps. ## 8. Normalisation passes All deterministic. Applied in order. ### 8.1 Text `NFKC` normalise, strip zero-width characters (`​–‍`, ``), convert non-breaking spaces to spaces, collapse whitespace, trim. **Empty string becomes `None`, always** — otherwise `""` and `None` produce different hashes and two code paths. ### 8.2 Title Strip a leading brand prefix (`Salomon - Trail Runner` → `Trail Runner`), guarded so it never yields an empty or single-character title. Strip trailing SKU noise. Convert an ALL-CAPS title to title case, since uppercase embeds measurably worse. ### 8.3 Brand Null it when it matches the shop name above a similarity threshold. **This is not an edge case: 8 of 17 products in the live store carry the shop name as vendor**, which is Shopify's default when a merchant leaves the field blank. Without this rule half the catalog shares one brand and any brand-match signal becomes noise. Also null known placeholders (`n/a`, `none`, `unknown`, `default`, `generic`, `-`), and canonicalise casing against brands already seen for the tenant so `salomon`, `SALOMON` and `Salomon` collapse. ### 8.4 Taxonomy First match wins; the winner is recorded in `taxonomy_source`: | Order | Source | Confidence | |---|---|---| | 1 | `category.fullName` split on `" > "` | 1.0 | | 2 | `productType` | 0.8 | | 3 | collection titles, merchandising titles excluded | 0.6 | | 4 | tags | 0.5 | | 5 | unresolved — `taxonomy_source = 'none'` | 0 | Merchandising collections are excluded by pattern. The list must include **`automated collection`** and `home page` — 9 of the live store's 12 collection memberships are one of those two, and resolving to them would make unrelated products category siblings. Paths are truncated to 4 levels. A deeper path fragments the catalog into single-product leaves, which makes category affinity useless. B does **not** build a canonical taxonomy tree. Paths are stored as the source expressed them, with `taxonomy_source` recording provenance. Mapping onto a shared cross-platform tree is C's job, and is better decided once several merchants' real distributions are visible. ### 8.5 Attributes Keys canonicalised through an alias table (`colour`/`color`/`shade` → `color`, `fabric`/`composition` → `material`, and so on). Values canonicalised per key: colours to a base palette keeping `raw_value`; sizes parsed for system and unit with measured values converted to a base unit so `500ml` and `0.5L` agree; gender to a small closed set. **Shopify's `Title` option is dropped, always.** It appears on all 17 live products with the value `Default Title` — it is a placeholder for single-variant products, not data. Retained, every product gains a meaningless attribute, which inflates `attributes.length` and therefore `quality_score` for products that have no real attributes at all. Each attribute keeps `raw_value` alongside `value`, plus `source` and `confidence`, so a wrong rule can be diagnosed and re-run without re-fetching. Source precedence when the same key arrives twice: `metafield (1.0) > variant option (1.0) > key:value tag (0.9) > bare tag (0.7)`. ### 8.6 Commercial Money as **integer minor units**, never floats. `price_cents` and `price_max_cents` are the min and max across variants — the live store has two products with genuine spreads, so a single price would be wrong for both. `currency` comes from the source record; A stored `INR` for this shop. Never assumed. `on_sale` is true when any variant has `compareAtPrice` greater than `price`. For an HTTP source expressing `discountPercentage` instead, `compare_at_cents` is derived as `price / (1 - d/100)`. `in_stock` derives from `availableForSale`, **never from inventory quantity**. Merchants with overselling enabled carry negative quantities on perfectly purchasable products. When a product does not track inventory it is in stock by definition. ### 8.7 Product URL Chain: `onlineStoreUrl` → `{primary_domain}/products/{handle}` → `url_template` for HTTP sources. `primary_domain` was captured at install by A. **All 17 live products have `onlineStoreUrl: null`**, so the `handle` branch is not a fallback here — it is the only working path. ### 8.8 Quality score B **computes and stores** `quality_score`, and reports its distribution per sync run. B does **not** gate anything on it — no product is excluded, and no enrichment is triggered. The purpose is measurement before policy. On the live store, `category` is 5% filled, `description` 11%, and brand is nulled for 47% of products, so nearly every product would score below the conventional 0.5 threshold. Setting an enrichment gate against that guess would either enrich everything or nothing. B produces the real distribution across real merchants; C sets the threshold from it. Scored as: title present and at least 10 characters (0.25), taxonomy path at least 2 levels (0.25), brand present (0.15), attribute count capped at 4 (0.20), image (0.10), description (0.05). ### 8.9 Media Strip Shopify CDN size suffixes and query parameters. They change between syncs and would churn `record_hash` for no reason. ## 9. Change detection ``` content_hash = sha256(title | brand | taxonomy_path | sorted tags | sorted attributes | description) record_hash = sha256(every field, keys sorted) ``` **Every array is sorted before hashing.** Tags and attributes arrive in arbitrary order; unsorted hashing changes the hash on every sync and re-embeds the entire catalog nightly. This is invisible until the embedding bill arrives, and is covered by an explicit stability test. `content_hash` changing means re-embed and rebuild neighbours. `record_hash` changing alone — a price or stock change — updates the row only. ## 10. Validation A row is rejected outright when it has no `external_id`, no title or a title under three characters, no price or a negative price, no resolvable `product_url`, or no variants. Everything else degrades rather than rejects. A product with no brand, no category and no attributes is still ingested; it simply scores low. Rejections are counted per run and reported. Above 5% indicates a normaliser bug rather than bad merchant data, so the ratio is what matters, not individual rejections. ## 11. Sync semantics One full sync per source per run. 1. Fetch every page. A failure at any page aborts the run. 2. Normalise and validate. 3. Upsert on `product_key`, setting `synced_at` to the run's start timestamp. 4. **Only if every page fetched successfully**, delete stale rows: `WHERE source_kind = %s AND source_ref = %s AND synced_at < %s`. A partial fetch deletes nothing — the same reasoning as the crawler's `crawl_complete` flag, correctly scoped to one producer this time. 5. Update `product_sources.last_synced_at`. ## 12. Recommendations — explicitly not touched B changes **nothing** about recommendation behaviour. `app/services/products.py` is not modified: not `list_products`, not `search_products`, not `match_products`, not `related_products`, not `to_card`. Which products are eligible and how they rank is owned by separate work. `to_card` therefore stays price-free, and its existing rationale still holds: B has no webhooks, so a price can be a full sync cycle stale, and a wrong price shown to a customer is the worst failure this feature has. What B owes that work is accurate data. It stores `status` (ACTIVE / DRAFT / ARCHIVED), `in_stock`, `price_cents`, `price_max_cents`, `on_sale` and `currency` correctly, so whoever builds recommendations can filter and rank on them. The live store contains one DRAFT, one ARCHIVED and two out-of-stock products, which makes that accuracy verifiable immediately even though nothing acts on it yet. ## 13. Error handling | Condition | Behaviour | |---|---| | Source not found or inactive | 404, nothing written | | Credentials fail to decrypt | 500, logged without the blob | | Token rejected (401/403) | mark source `status='error'`, 502, delete nothing | | Any page fetch fails | abort run, keep what was upserted, delete nothing | | Individual product fails validation | skip it, count it, continue | | Rejection ratio above 5% | complete the run, flag it in the report | Nothing in B logs a credential, a token, or a full API response body. ## 14. Testing **Golden fixtures**, captured from the live store, committed, asserted as exact normalised output. The live catalog already covers the awkward cases: ARCHIVED, DRAFT, two out-of-stock, 16 with no category, 8 with shop-name vendor, one with no image, one with empty `productType`, two multi-variant price spreads, 23 variants with no SKU, and the `Title` placeholder option on every product. A dummyjson fixture covers the HTTP path, including its absent URL and absent currency. **Property tests:** - Normalising twice produces an identical result. - Re-normalising unchanged input produces an identical `content_hash`. - No output field is ever an empty string, only `None`. - Every attribute key appears in the canonical key set. - `price_cents <= price_max_cents`. **Migration test:** create a schema with the pre-B table shape, populate it, migrate, assert no row lost and every pre-existing row labelled `source_kind='crawl'`. **Isolation test:** with crawled and Shopify rows present for one tenant, run a Shopify sync and assert no crawled row is deleted. This is the §4 hazard, and it gets a test. ## 15. Known limitations **No webhooks.** Catalog changes are picked up only on the next sync. Prices may be one cycle stale, which is why cards stay price-free. **Product URLs will 404 on the current dev store.** All 17 products lack `onlineStoreUrl` because the Online Store channel is not publishing. The `handle`-derived URLs are structurally correct but will not resolve until the channel is published. This does not block pipeline testing; it does block judging recommendation quality. **No canonical taxonomy.** Paths are stored as the source expressed them. Cross-source and cross-tenant comparison waits for C. **Single-category test data.** 14 of 17 live products are snowboards, so category affinity cannot be meaningfully evaluated on this store. ## 16. Out of scope LLM field-map authoring and enrichment; canonical taxonomy tree and synonym dictionary; quality-score *gating* and tenant health reporting — B computes and reports the score but acts on it in no way; webhooks and incremental sync; re-embedding orchestration beyond emitting `content_hash`; the `TODO(auth)` from A.