Catalog

Crates: platyn-catalog, platyn-vendors, platyn-xml · Migrations: 0002_catalog.sql, 0007_size_reference_data.sql · Status: Schema built

The catalog is the blank goods a shop decorates: roughly a million variants across S&S Activewear and SanMar, with prices and inventory that move daily. Everything a quote is built from starts here.

It is the one schema in Platyn that is global. catalog.* contains no tenant_id column anywhere, and never will — see ADR-04. Tenants hold SELECT on it and nothing more.

Domain model

erDiagram
    VENDORS ||--o{ BRANDS : supplies
    VENDORS ||--o{ STYLES : lists
    BRANDS  ||--o{ STYLES : "brands"
    STYLES  ||--o{ STYLE_COLORS : "comes in"
    STYLE_COLORS ||--o{ VARIANTS : "sized as"
    SIZES   ||--o{ VARIANTS : "canonical size"
    SIZES   ||--o{ SIZE_ALIASES : "spelled"
    VARIANTS ||--o{ VARIANT_PRICES : "priced"
    VARIANTS ||--o{ VARIANT_INVENTORY : "stocked"
    VENDORS ||--o{ SYNC_RUNS : "synced by"
    SYNC_RUNS ||--o{ SYNC_EVENTS : logs

The hierarchy in the terms a decorator uses:

• Style — PC61, the Port & Company Essential Tee. What you search for.
• Style color — that tee in Athletic Heather. What you show on a proof.
• Variant — that tee, in Athletic Heather, in 2XL. What actually has a SKU, a price, and a quantity on a shelf.

Tables

catalog.vendors

Seeded, not synced. Two rows today.

id	code	display_name	cdn_base_url
1	ss_activewear	S&S Activewear	https://cdn.ssactivewear.com/
2	sanmar	SanMar	https://cdn.sanmar.com/

catalog.styles

Column	Type	Notes
id	BIGINT identity
vendor_id	SMALLINT
vendor_style_id	TEXT	The vendor’s own key. UNIQUE (vendor_id, vendor_style_id) — the upsert target.
style_code	TEXT	What a human types: PC61, G500, DT6000.
brand_id	BIGINT
title, description	TEXT
base_category, categories	TEXT, TEXT[]	Array is GIN-indexed for faceting.
brand_image_url, style_image_url, spec_sheet_url	TEXT
is_discontinued	BOOLEAN
country_of_origin	TEXT
search_tsv	tsvector generated stored	Weighted: code A, title B, description D.
raw	JSONB	The vendor payload as received. Reprocess without re-crawling.
content_hash	BYTEA	Short-circuits an unchanged row before it becomes an UPDATE.
first_seen_at, last_synced_at, retired_at	TIMESTAMPTZ

Why the search column is generated

search_tsv is GENERATED ALWAYS AS ... STORED. It cannot drift from the row it describes, because there is no code path that writes one without the other. The weighting means a search for PC61 ranks the style whose code is PC61 above one that merely mentions it in prose.

catalog.sizes and the alias registry

A canonical size registry, global reference data rather than per-tenant configuration — 2XL means the same thing in every shop in the country.

CREATE TYPE catalog.size_group AS ENUM
    ('adult','youth','infant','toddler','womens','numeric','one_size','other');

catalog.sizes holds code, label, size_group, and sort_order. That sort_order is why a size run renders S · M · L · XL · 2XL · 3XL instead of alphabetically, everywhere, without each call site re-deriving it. The registry is seeded in 0007_size_reference_data.sql across five groups — adult XS–6XL, youth, toddler and infant, women’s, and one-size.

Reference data lives in a migration, not a seed script

Code branches on sizes.code = '2XL'. That makes it structure, not sample data, and it must be byte-identical in dev, CI, staging, and production. A seed script that someone forgets to run in one environment produces a size that resolves in three places and not the fourth.

catalog.size_aliases maps every vendor spelling onto a canonical size. Aliases are stored upper-trimmed and resolved through one function:

api/migrations/0007_size_reference_data.sql

CREATE OR REPLACE FUNCTION catalog.resolve_size(p_alias TEXT, p_vendor SMALLINT DEFAULT NULL)
RETURNS SMALLINT LANGUAGE sql STABLE AS $$
  SELECT size_id FROM catalog.size_aliases
   WHERE alias = upper(btrim(p_alias))
     AND (vendor_id IS NULL OR vendor_id = p_vendor)
   ORDER BY vendor_id NULLS LAST
   LIMIT 1
$$;

vendor_id NULLS LAST means a vendor-specific mapping wins over the global one when both exist, so a distributor with a genuinely odd spelling can be handled without disturbing everyone else. Every canonical code is also inserted as an identity alias, so 2XL and XXL resolve through the same code path rather than one being a special case.

`XXL` and `2XL` are the same size — quantities must be SUMMED

This is the alias collision that actually costs money. A feed that spells the same size two ways across two records must merge into one line_item_sizes row with the quantities added, not two rows that each look plausible. UNIQUE (line_item_id, size_id) on the deal side is what forces the merge to be explicit rather than accidental.

The seeded map covers the families that collide: XXL/2X/XX-LARGE/2 XL → 2XL, and the same pattern up through 6XL, plus 1XL → XL.

Unmapped sizes are recorded, never guessed

resolve_size() returns NULL for an unknown alias — deliberately, so the caller records it instead of guessing. The alias lands in catalog.size_alias_review with a seen_count and first/last timestamps, and the SKU is kept. Guessing that XXL-T means 2XL would silently merge a tall into a regular, and a shop would find out at the press.

This replaces a hardcoded SIZE_NORMALIZATION_MAP in sizeUtils.js and a per-product mapped_sizes JSON blob in the predecessor system.

catalog.variants

The SKU-level row. Two unique constraints, doing different jobs:

• UNIQUE (vendor_id, vendor_sku) — the sync’s upsert target.
• UNIQUE (style_color_id, size_id) — a style color cannot have two 2XL rows, which is what an alias mapping mistake would otherwise produce.

Column	Type	Notes
vendor_sku, gtin	TEXT
size_id	SMALLINT	Canonical.
vendor_size_label	TEXT	The vendor’s raw spelling, kept for support conversations.
case_qty	INTEGER
unit_weight_oz	NUMERIC(8,3)	Shipping estimates.
is_closeout, is_discontinued	BOOLEAN
content_hash, last_synced_at, retired_at

catalog.variant_prices

amount NUMERIC(12,4) NOT NULL,
PRIMARY KEY (variant_id, price_tier, effective_from)

NUMERIC, never REAL. A predecessor stored piecePrice, dozenPrice, and casePrice as binary floating point. The cent that goes missing on a 5,000-piece order is a support ticket, not a rounding curiosity. Decimal in Rust, NUMERIC in Postgres, string over JSON.

Prices are temporal: effective_from / effective_to, with a partial index (WHERE effective_to IS NULL) for the current row. A quote saved last month can still be explained.

catalog.variant_inventory

(variant_id, warehouse_code) with qty_available and observed_at. The highest-churn table in the system, isolated into its own table precisely so its vacuum profile never touches the tables you actually join against.

observed_at matters: inventory is a reading, not a fact. The UI shows the age, because “412 in IL” from six hours ago is a different claim than the same number from six minutes ago.

Sync

Runs

CREATE TYPE catalog.sync_status AS ENUM
    ('queued','running','succeeded','failed','failed_sanity','cancelled');

catalog.sync_runs is a lease, not a status flag: lease_owner, lease_expires_at, heartbeat_at, plus a JSONB cursor resume point and counters for rows_seen, rows_changed, rows_unchanged, rows_retired, error_count.

Single-flight is a database invariant rather than a read-then-check race:

CREATE UNIQUE INDEX sync_runs_one_active
    ON catalog.sync_runs (vendor_id, scope)
    WHERE status IN ('queued','running');

A duplicate enqueue fails with a unique violation the caller handles, instead of two workers fighting over one vendor’s rate limit.

The loop

1. Claim a run and take a lease. Heartbeat while working.
2. Fetch a page from the vendor, inside the rate limit. S&S is JSON; SanMar is SOAP/XML, parsed by platyn-xml.
3. Normalise. Resolve sizes through the alias registry. Unmapped aliases go to review, and the affected variants are held back.
4. Hash and compare. If content_hash matches, count it in rows_unchanged and write nothing. Most rows on most runs take this path.
5. Upsert changed rows on the vendor-key unique constraint.
6. Checkpoint the cursor. This is the interruption boundary.
7. Retire anything not seen this run by setting retired_at, rather than deleting it. A quote from last year must still resolve its line items.
8. Sanity-gate. A run that would retire an implausible share of a vendor’s styles lands in failed_sanity and changes nothing.
9. Bump catalog.version, which rotates every cache key.

Nothing is ever deleted

Rows are retired, not removed. A deal from 2023 references variants that may have been discontinued since; deleting them would corrupt historical orders. Search filters on retired_at IS NULL; historical lookups do not.

Interruption

Killing a sync is cheap by design — ADR-08. SIGTERM sets a cancellation token; the loop checks it at every item boundary, checkpoints, marks the run queued, and exits. The compose service allows 90 seconds: generous for one item, far too short for a run. Deploys never wait for a sync.

Caching

Every catalog cache key embeds catalog.version:

catalog:v{version}:style:{style_id}
catalog:v{version}:search:{query_hash}:{cursor}
catalog:v{version}:facets:{vendor}:{category}

Bumping the version rotates every key at once, so invalidation is not a code path and cannot have bugs. Old keys age out under allkeys-lru. No tenant dimension, because the data has none — one warm cache serves every shop.

Search

The predecessor drove col ILIKE '%term%' across seven columns against plain B-trees. A leading wildcard cannot use a B-tree, so every search sequentially scanned about a million rows.

The replacement is two indexes doing what each is good at:

Index	Type	Serves
styles_search_tsv	GIN on tsvector	Word queries: ring spun tee
styles_code_trgm	GIN trigram on style_code	Partial codes: PC6
styles_title_trgm	GIN trigram on title	Substring and fuzzy title matching
styles_categories	GIN on TEXT[]	Category facets
styles_brand_cat	B-tree, WHERE NOT is_discontinued	Brand and category browse

Pagination is keyset, not offset. Offset pagination on a million rows makes page 400 quadratic and lets a concurrent sync shift the window under the reader.

API surface

Method	Path	Permission	Notes
GET	/v1/catalog/styles	catalog.read	Search and browse. Keyset cursor.
GET	/v1/catalog/styles/{id}	catalog.read	Detail with colors and variants.
GET	/v1/catalog/styles/{id}/variants	catalog.read	Variants with prices and inventory.
GET	/v1/catalog/brands	catalog.read	For filters.
GET	/v1/catalog/sizes	catalog.read	Canonical registry with sort_order.
GET	/v1/catalog/sync-runs	vendor.manage	Operational visibility.
POST	/v1/catalog/sync-runs	vendor.manage	Enqueue. 409 if one is already active.
DELETE	/v1/catalog/sync-runs/{id}	vendor.manage	Cooperative cancel.
GET	/v1/catalog/size-alias-review	vendor.manage	Unmapped vendor spellings.
POST	/v1/catalog/size-aliases	vendor.manage	Map one.

Planned

• app.custom_styles — tenant-scoped house-brand and customer-supplied blanks, unioned into search results. The one thing a global catalog genuinely cannot do.
• Per-tenant cost overlays resolving app.vendor_accounts tier pricing against global variants at quote time.
• Two more vendors (alphabroder, AS Colour), which is the load that makes worker count scale with vendor count — see ADR-07.
• Cross-vendor style matching, so G500 from two distributors is offered as one product with two sources.
• Live inventory checks at quote confirmation, bypassing the cached reading.