Skip to content
MerchOS Engineering Handbook

Catalog pipeline

The pipeline at a glance

SupplierVariantRaw      ─→  Raw Catalog mode
  (~177K rows mirrored                │
   nightly from Gelato +              ▼ Joy clicks "Triage"
   Printful catalogs)         Inbox mode (per-supplier triage)
                                       │
                                       ▼ Joy clicks "Promote"
                              Master mode (curated Skus —
                                           one per RawStyle)
                                       │
                                       ▼ Joy adds to vertical drafts
                              Verticals mode (one published catalog per
                                              of the 9 active verticals)
                                       │
                                       ▼
                              Storefronts render `SkuVertical` rows

Four conceptual modes, all served by services under apps/api/src/modules/catalog-pipeline/. The flow is one-way under normal operations: Raw → Inbox → Master → Verticals. The exceptions (and there are two important ones) live below.

Raw Catalog — the bottom of the pipeline

SupplierVariantRaw is YCM’s local mirror of every variant the suppliers carry. Refreshed nightly per supplier (RawSyncService for Gelato, PrintfulRawSyncService for Printful). 177K rows across both suppliers at the time of writing.

The Raw Catalog UI in internal-admin shows these grouped by style — for apparel, (brand, styleCode); for non-apparel, productUid. The grouping happens in a materialized view (raw_catalog_styles) because aggregating 177K rows in app code was OOM-killing the API on the original 512MB Fly machines. Phase 0119 architectural debt; the matview is the durable fix.

Joy’s day-to-day touches three filters on the Raw Catalog UI:

  • Vertical — narrows to a curation backlog.
  • CA-fulfillable — only styles with at least one variant that can ship to Canada from Gelato’s Toronto hub or Printful’s CA-friendly catalog subset. The matview’s caFulfillableVariantCount column (added Phase 0155 / 2026-05-19) powers this.
  • Category — t-shirts vs hoodies vs mugs vs etc.

Each style row carries a CuratedStyle (Joy’s curation state) and a representative image. Promote → see Inbox/Master sections.

The matview is refreshed:

  • Nightly via cron (RawSyncService post-hook).
  • After every supplier-sync completion.
  • On operator demand from internal-admin (POST /v1/raw-catalog/refresh-matview).

Inbox mode — the triage layer

InboxService (apps/api/src/modules/catalog-pipeline/inbox.service.ts) is the bucket-management surface. Each SupplierProduct row sits in one of six buckets:

BucketMeaning
NEWJust arrived from supplier sync; Joy hasn’t looked yet.
MAYBEJoy deferred — interesting but not promoting yet.
NO_AI_MATCHAI classifier couldn’t suggest a vertical. Joy reviews manually.
DEFERREDPromoted but on hold (e.g. waiting for design assets).
REJECTEDNot a fit — won’t promote. Stored with RejectReason.
ARCHIVEDOff the active triage queue but retained for history.

Triage actions (all bulk + idempotent):

  • acceptMany([ids], actorUserId) — promotes to DRAFT Sku. Auto-prices at 2.5× supplier cost; copies sizes/colors/image; carries over ≥ MID-confidence AI vertical suggestions into SkuVerticalDraft with assignedBy = "ai".
  • rejectMany([ids], reason, note, actorUserId) — structured RejectReason enum (NOT_MERCH / POOR_QUALITY / DUPLICATE / UNSHIPPABLE / WRONG_PRICE_POINT / WRONG_AUDIENCE / OTHER). OTHER requires a note (server-side enforced).
  • deferMany([ids], actorUserId) — moves to MAYBE.
  • pullBackMany([ids], actorUserId) — REJECTED/MAYBE → NEW (re-triage).

The Inbox is per-supplier. Gelato and Printful each have their own queue. The promote endpoint is supplier-agnostic from the operator’s perspective — Joy clicks Promote, the system figures out which supplier the row came from and threads it through the right path.

Master mode — the canon

MasterService (apps/api/src/modules/catalog-pipeline/master.service.ts) owns the Sku table — every promoted, curated product that YCM offers.

A Sku has three independent axes (ADR-0077):

  • SkuColorChoice[] — per-Sku rows like “Arctic White” with a displayName, hex, position.
  • SkuSizeChoice[] — per-Sku rows like “L” with displayName, position.
  • SkuPrintOption[] — per-Sku rows like “Front DTG” with a label and placement metadata.

Why three tables, not one matrix of (color × size × print)? The axis model is the right granularity for Joy’s workflow: add a color once → it’s available across every size + print. Retire a print method → it disappears from every color + size automatically. A matrix model would force her to add or retire each combination row-by-row.

The fulfillability gap (sparse Cartesian product — some (color × size × print) tuples genuinely don’t exist at the supplier, e.g. Gildan 64000 in banana-cream / XS / 4-4 isn’t actually fulfillable) is solved by SkuVariantCellState as an additive override layer on top of axes. Phase 0153 / ADR-0115 shipped this.

If you find yourself wanting to “just use a matrix model” because axes feel limiting, read ADR-0077 and ADR-0115 in order before proposing it.

Mapping tables (ADR-0116) — the per-supplier code layer

For each axis, a mapping table links the YCM-curated axis row to the supplier’s code for that axis:

SkuColorChoice("Arctic White", hex #F5F5F5)
       │
       ├── SkuColorChoiceSupplierMapping → Gelato: "pigment-arctic-white"
       └── SkuColorChoiceSupplierMapping → Printful: "white"

Same pattern for SkuSizeChoiceSupplierMapping and SkuPrintOptionSupplierMapping. The mapping table is the architectural answer to “Gelato and Printful disagree on what to call the same color” — Joy sees “Arctic White” everywhere; the order resolver looks up the right supplier code at fulfillment time.

ADR-0116 shipped this rebuild — pre-Phase 0154, the supplier code was a column on SkuColorChoice (gelatoCode). Worked when there was one supplier; broke immediately when Printful arrived. The mapping table is the right shape; the column was a shortcut.

Lifecycle: DRAFT → ACTIVE → DISCONTINUED

  • activate(skuId, actorUserId) — DRAFT → ACTIVE. Refuses if overlayZones is empty. Zone gate is enforced server-side, not just in the UI.
  • discontinue(skuId, actorUserId, reason?) — ACTIVE → DISCONTINUED. Removes from ALL vertical drafts AND published rows in one transaction so a discontinued Sku immediately disappears from storefronts. Historical orders preserve their FK to the Sku — never delete.
  • reactivate(skuId, actorUserId) — DISCONTINUED → DRAFT. Admin must explicitly re-add to verticals.

Demote — the operator escape hatch (Phase 0155, 2026-05-19)

previewDemoteImpact(skuId) + demote(skuId, actorUserId) ship the “drop a Sku back to the Raw Catalog queue” workflow.

  • Hard-deletes the Sku via CASCADE. Every child row (axis choices, mappings, variant matrix, cell-state, vertical drafts) goes with it.
  • Safety gate is the DB-level NoAction FK on Product.sku — Postgres refuses the delete if any Company has the Sku in their catalog. The service catches P2003 and throws ConflictException so the operator sees a list of blocking Products, not a 500.
  • Flips CuratedStyle.status back to NEW so the underlying RawStyle re-appears in Joy’s Inbox.
  • Writes a catalog.master.demoted AuditEvent with the deleted Sku’s identity snapshot.

Integration-tested against real Postgres (master.service.int.spec.ts — 7 tests).

Verticals mode — draft + publish

VerticalsService is the most architecturally interesting service in the pipeline. It owns the draft + publish atomic-replace transaction that powers per-vertical storefront catalogs.

Two layers of rows:

  • SkuVertical[verticalId, skuId, position] — published rows. What storefronts render.
  • SkuVerticalDraft[verticalId, skuId, position] — draft rows. Joy’s work-in-progress.

publish(code, actorUserId) is the load-bearing method. Inside one transaction:

  1. Snapshot the current published rows (for revertable history — Phase 0122).
  2. Delete every SkuVertical row for this vertical.
  3. Insert every SkuVerticalDraft row as a new SkuVertical (re-packing positions densely).
  4. Set Vertical.draftUpdatedAt = now and Vertical.publishedAt = now.

The transaction is atomic — readers either see the old set OR the new set, never a partial state. Critical: a botched mid-publish replace would leave the storefront empty for everyone in that vertical. Tested via integration tests + a verification cron that detects drift between draft and published row counts.

revertDraft(code, actorUserId) does the reverse — copies the published rows back into the draft layer, replacing what was there. Also atomic.

Per-vertical print-option curation (ADR-0071) is a sibling concern: each SkuVertical row can have an optional SkuVerticalPrintOption[] array that constrains which print options a shopper can pick on the storefront. Soft recommendation, not hard restriction — empty array falls back to “show all active options.”

Reads — the SuppliersViewService + SearchService

SuppliersViewService is the unified “which Skus came from which supplier” lens. Reads across all four modes.

SearchService is the ⌘K cross-mode palette in internal-admin. Searches Skus, RawStyles, CuratedStyles, SupplierProducts simultaneously and ranks by relevance.

CatalogDigestService powers the Monday 9am ET weekly email to Joy + Jess summarizing the previous week’s activity (promotes, rejects, drift, low-coverage areas). Sections are ordered by priority: outages first, then operational items, then trends.

Materialized views + caches

View / cachePurposeRefresh trigger
raw_catalog_stylesAggregate 177K SVRs into ~5K style-group rows for the Raw Catalog UINightly cron + post-sync hook + on-demand
master_sku_summaryDenormalized fulfillment-readiness flags for the Master listOn Sku activate / discontinue / demote
SkuVariantCellStateSparse (skuId, color, size, print, supplier) rows marking cells unavailable (supplier or operator)After choice mutations + 30-min cron + supplier sync
FulfillmentQuote24h-TTL cache of supplier price quotes (Gelato Quote API + Printful product API)Per quote lookup (lazy)

What’s next

  • Suppliers + fulfillment — Gelato + Printful integration, webhooks, FulfillmentTask, multi-supplier routing.
  • Internal admin (Chapter 9) — Joy + Jess’s daily workflow including the full pipeline UI walk.

Canonical sources

Triggers for update

Update this chapter if you:

  • Add or retire a mode (today: Raw, Inbox, Master, Verticals).
  • Add or retire an axis on Sku (today: color, size, print).
  • Change the draft/publish atomic-replace transaction.
  • Change CuratedStyle.status values or transitions.
  • Add or change a materialized view in the pipeline.
  • Add or retire a Sku lifecycle method (activate / discontinue / reactivate / demote).
  • Replace the mapping-table shape (ADR-0116) with something else.