Public demo viewer

Scope

Data completeness across all 4,062 active SKUs, pulled from the catalogue health dashboard on 2026-08-12.

Completeness

Trend

Imageless SKUs down from 94 in July to 61 - Tom's photography push is working. Allergen 'unknown' flat because new deli lines keep arriving faster than spec sheets.

Risks

• The 214 allergen-unknown lines are the real concern - they're a safety surface, not cosmetic. Prioritised in Meeting: catalogue data-quality review, 2026-08-04.
• 12 suspect weights are almost all decimal/unit slips at ingest; Ben auditing against supplier pack sizes.

Actions

Femi: clear 38 stale drafts by 2026-09-01. Tom: top 30 imageless. Ben: close out weight audit and the MOQ unit backfill (Gotcha: supplier MOQ unit is inconsistent in older rows).

Purpose

How Storefront product search ranks results. Postgres full-text plus a few business boosts.

Index

catalogue.products has a generated tsvector over name, brand, category name, and supplier name, refreshed on write. GTIN and SKU get exact-match lookups that short-circuit ranking (scan a barcode, get the product).

Ranking signals

1. Exact SKU/GTIN match - always top.
2. Text relevance (ts_rank).
3. Boosts: in-stock lines above out-of-stock; active above seasonal-out; own-shot image above placeholder (a small nudge, since imageless lines look worse - see Gotcha: products with no image fall back to placeholder).
4. Tie-break: trailing-30-day sales volume from the Data Platform, refreshed nightly.

Exclusions

• status in ('draft','discontinued') never appear.
• Allergen-filtered searches apply the Reference: allergen data model rules strictly - unknown is excluded from 'free from' filters (see Gotcha: allergen 'unknown' must never render as free-from).

Known gaps

• No synonym handling yet - 'aubergine' won't find 'eggplant'. Backlog.
• Misspellings aren't fuzzy-matched. Trigram search is scoped but not built.

Status

Accepted, 2026-03-02.

Context

SKU/product data and supplier/procurement data were originally one schema. As we grew past ~3,000 SKUs and ~100 suppliers, coupling started to hurt: price-list ingest churn was locking product reads, and the two domains change for different reasons and at different rates.

Decision

Split into two services with separate schemas: catalogue.* (products, prices as seen by customers, images, allergens, GTINs) and suppliers.* (suppliers, contracts, price lists, intake, scorecards). They share Postgres but not tables; cross-references are by ID only (catalogue.products.supplier_id -> suppliers.suppliers.id).

Consequences

• Supplier price-list ingest no longer contends with Storefront product reads.
• The sell-price recompute is the one hot path that spans both - it reads supplier price lists and writes catalogue prices. It runs as an async job, not inline (see How-to: recalculate sell price from cost).
• There is no FK across schemas; referential integrity on supplier_id is enforced in application code, not the database. This is a known sharp edge - a deleted supplier can orphan products, so we soft-delete suppliers only.
• Reporting joins the two in the Data Platform, not in the app.

Alternatives considered

Keep one schema (rejected - coupling). Fully separate databases (rejected - the reporting joins and shared transactions in the recompute weren't worth the operational cost yet).

Purpose

How we store and validate barcodes so the WMS scan-to-pick and customer barcode search both work.

Storage

• catalogue.products.gtin holds the primary barcode as a 14-char string, left-padded with zeros (GTIN-14 canonical form). A GTIN-13 is stored as 0 + the 13 digits.
• A product can have multiple barcodes (inner + outer case, supplier re-pack). Extras live in catalogue.product_barcodes with a barcode_type (each | inner | case).

Validation

• Check-digit validated on entry (mod-10). A failing check digit is rejected, not stored.
• Duplicates across different SKUs are blocked - a GTIN identifies one product. Exception: a genuine supplier re-pack of the same product keeps the same GTIN and that's fine because it's the same SKU.

Gotchas

• Weighed goods from suppliers sometimes carry variable-weight EAN-13s starting 02 or 2 - the last digits encode price/weight, not identity. We do NOT store these as the product GTIN. See Gotcha: variable-weight barcodes are not product identifiers.
• Some deli lines have no barcode at all. gtin is nullable; the WMS falls back to SKU pick.

Review

Femi owns this; changes to GTIN normalisation need Cara's sign-off because the WMS scanner config depends on the padding rule.

When

A supplier drops a product, a line stops selling, or we deliberately range it out.

Steps

1. Check live stock in the WMS. If there's sellable stock, decide: sell-through (keep active until zero) or immediate pull.
2. Set catalogue.products.status = 'discontinued' and discontinued_at = now(). This removes it from search and blocks new basket adds. The row stays forever - see Gotcha: never reuse a discontinued SKU number.
3. Check for substitutes. If there's a like-for-like replacement, set replaced_by_sku so the Storefront can suggest it. See How-to: set up a product substitution.
4. Notify customers with the SKU in a saved order template - Dev's support flow emails them the substitute.
5. Tell Cara so the WMS pick-face can be freed once stock hits zero.
6. Leave the supplier link intact. Discontinuing a product doesn't off-board the supplier.

Gotchas

• Don't discontinue a product that's a component of an active bundle - the bundle breaks. Check catalogue.bundle_items first.
• Seasonal lines are NOT discontinued, they're set status = 'seasonal_out'. See Decision: seasonal ranges use a status, not deletion.