Products - Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• Product CRUD, the seven single-purpose facet PATCHes, lifecycle state machine and optimistic locking.
• Storefront discovery: multi-filter list, search, suggestions, feeds and cursor pagination.
• Product import/export jobs (enqueued through the outbox).
• Product slug ownership (via SlugOwnershipService, shared with catalog).
• Blog ↔ product linking (blog_post_product link table lives in the blog schema).
• Product↔tag membership (product_tag_link); the tag entity itself belongs to catalog.

Does Not Own

• The tag table — catalog owns it (catalog is the taxonomy module).
• Inventory counters — the inventory module owns them; product.stock_status is a synchronous projection maintained under inventory rules.
• promotions and inventory response groups — reserved null, populated additively by their modules.
• The outbox — generic infra in modules/outbox/.

Source of Truth

3. Module Composition

The four catalog customer leaves are composed into MobileModule at /api/mobile; ProductCustomerModule and ProductDiscoveryCustomerModule are listed directly in MOBILE_CHILDREN (the RouterModule does not recurse through two-level aggregates).

4. File and Directory Map

apps/api/src/modules/products/
  products.module.ts
  products-admin.module.ts
  products-customer.module.ts
  products-shared.module.ts
  products-worker.module.ts
  admin/
    product/
      product-admin.controller.ts        # 13 routes: read surface + single-entity writes
      product-admin-bulk.controller.ts   # 4 bulk routes (registered BEFORE the admin controller)
      product-write.service.ts           # create / general update / delete / restore
      product-write-facets.service.ts    # lifecycle / stock / pricing / media / seo / discovery / tags
      product-bulk.service.ts            # bulk delete / restore / lifecycle / discovery
      dto/
    job/
      product-job-admin.controller.ts    # import / export / cancel / list / detail
      product-job-admin.service.ts
      product-job-import-upload.config.ts
  customer/
    product/   product-customer.controller.ts + service + dto
    discovery/ product-discovery-customer.controller.ts + service + dto
  import-export/
    product-import-parser.ts             # CSV/XLSX streaming parse, file validation
    product-import-row.ts                # row validation + SKU matching semantics
    product-import.service.ts            # validate-all-then-write-all + job terminal write
    product-export.service.ts            # CSV write + row cap
  shared/
    product-filter.builder.ts            # the one filter predicate builder
    product-storefront-query.service.ts  # the one storefront listing engine
    product-storefront-filter-resolver.service.ts
    product-response.builder.ts          # both response shapes: detail + card
    product-response-assembler.service.ts  # detail shape — 10 batched reads
    product-card-assembler.service.ts    # card shape — 4 batched reads, thumbnail only
    product-card-response.dto.ts         # Swagger contract for listing items
    product-lifecycle.service.ts         # the one state machine
    product-write-support.service.ts     # classification memo cache, shared guards
    products.constants.ts                # caps: 20/100/25/30/10/20/100/50k
    products-activity-actions.ts         # typed activity literals
  workers/
    product-queue.processor.ts           # THE one @Processor on PRODUCTS
    product-import.processor.ts          # @Injectable handler
    product-export.processor.ts          # @Injectable handler
    product-job-state.ts                 # claim/complete/fail transitions
    product-job-sweep.processor.ts       # stalled-job sweep handler
    product-job-sweep.scheduler.ts       # cron entry

Key files:

5. Data Model

5.1 Schema Source

packages/db/src/schema/products/
  index.ts
  enums.ts
  product.ts
  product-tag-link.ts
  blog-post-product.ts      # lives in packages/db/src/schema/blog/ since the ownership fix
packages/db/src/schema/catalog/tag.ts

5.2 Tables and Collections

product

Lifecycle CHECKs: chk_product_live_requires_thumbnail (draft/archived exempt), chk_product_live_requires_published_at (published/unlisted require it), chk_product_series_requires_brand.

product_variant (media columns)

Full variant schema is not yet backfilled into this doc; this row covers only the columns the media work added or touches. Source: packages/db/src/schema/products/product-variant.ts.

product_slug

Identical shape to category_slug: slug varchar(260) UNIQUE across current and retired; partial UNIQUE uq_product_slug_current on product_id WHERE is_current; ON DELETE RESTRICT; soft delete never releases slugs.

tag (catalog) and product_tag_link (products)

tag: flat facet, slug unique among live rows only (no history table — a tag is a filter facet, not an SEO landing page). product_tag_link: composite PK (product_id, tag_id), RESTRICT both sides, plus idx_product_tag_link_tag for the filter path.

blog_post_product (blog schema)

(blog_post_id, product_id) PK; blog_post_id CASCADEs (blog hard-deletes), product_id RESTRICTs (products soft-delete); display_order non-negative CHECK.

5.3 Relationship Diagram

5.4 Variant media resolution

Implemented once, in apps/api/src/modules/products/shared/product-response.builder.ts, and shared — not duplicated — between the product and the variant:

resolveThumbnailSource(thumbnailKey, thumbnailAlt, gallery):
  thumbnailKey, TRIMMED, when non-empty
    else the gallery entry with the lowest `order`, ties broken by array position (stable sort)
    else null

resolveVariantThumbnailSource is a documented alias of the same function — not a second implementation — because the selection rule is identical for both callers. The two responses differ only in what a null result means to the caller: for a product, no thumbnail exists at all; for a variant, this variant adds no media of its own and the caller falls back to the product's thumbnail, never a placeholder.

The trim is load-bearing, not defensive padding: two of three seeded product_variant rows hold thumbnail_key = '' rather than NULL, so ?? does not fire and would return the empty string. ' ' survives a naive nullif(..., '') and would resolve to the asset-origin root. Both were probe-proven (probe-variant-gallery.sql, case F2) before this column existed.

Ordering is done in TypeScript (sortByOrder, ascending order, ties by array position — a stable sort), never in SQL. (gallery->>'order')::int raises Postgres 22P02 on a malformed value, which would 500 a public product-detail read for one bad row.

Customer contract — variants[].media (see API §Variants):

{
  thumbnail: { url: string; alt: string | null } | null;
  gallery: { url: string; alt: string | null; order: number }[];
}

thumbnail: null with an empty gallery is the normal "this variant carries no media of its own" case, not an error. An unresolvable key (the publicOnly guard refused it, or signing failed) is dropped from the gallery and never emitted as { url: "" } — an empty string is truthy, so a caller checking if (media.thumbnail) would render <img src=""> and the browser would re-request the page URL as an image instead of falling back to the product thumbnail. Found by probing a private key against the live endpoint: type-check and the full unit and integration suite all pass with the empty string, because "" satisfies url: string.

Variant media keys resolve through StorageUrlResolver.resolveMany(keys, { publicOnly: true }) — see §12 for the guard. product-variant-customer.service.ts collects one deduplicated Set of keys per variant batch (thumbnail sources plus every gallery item) and resolves it once, rather than once per occurrence.

5.5 Video source: key vs embedUrl

product.videos[] items carry two mutually exclusive sources — never both mean the same thing:

Exactly one must be present. Neither, or both, is a 400 at write time — PRODUCT_VIDEO_SOURCE_REQUIRED / PRODUCT_VIDEO_SOURCE_CONFLICT (IsExactlyOneVideoSource, product-media-item.dto.ts).

youtu.be is deliberately excluded from the allowlist — it is a share host, not an embed host, and is absent from both apps' CSP frame-src. Accepting it would pass write-time validation and then render nothing under CSP, with only a console line to explain why.

The control that matters is read-side, in resolveVideoMedia (same file as §5.4): every read re-checks the host allowlist against item.embedUrl, and a legacy row whose key still holds an absolute value (written before the split existed) is promoted to embedUrl only when its host is allowlisted, and dropped — never rendered anywhere — otherwise. A write-time validator can be bypassed by a data fix-up; a read-time guard cannot, because it runs on every request.

6. Services and Responsibilities

6.1 ProductWriteService

Transaction boundaries: create writes product + slug + inventory row + SEO link in one transaction. Optimistic lock: every write bumps version and rejects a stale caller-supplied version.

6.2 ProductWriteFacetsService

Seven single-purpose PATCHes, each: assert editable (not archived → 409 PRODUCT_ARCHIVED_NOT_EDITABLE), apply, bump version, activity + cache. updateStock reads the inventory row FOR SHARE inside the transaction (inventory locked before product is the repo-wide ordering) and narrows via resolveStockStatusForWrite.

6.3 ProductStorefrontQueryService

The one listing engine. Builds filters via buildProductFilterConditions with storefrontLive: true (inlined status = 'published' AND deleted_at IS NULL literal), keyset-paginates on (sortKey, id), over-fetches limit + 1 to compute hasMore without a COUNT. Computed sorts (discount, relevance) use a hand-rolled keyset WHERE; a cursor replayed against a different sort throws 400 PAGINATION_CURSOR_INVALID.

6.4 ProductWriteSupportService

Memoised classification lookups: three queries warm the whole batch instead of three per row. null is a cached miss (distinct from undefined = not looked up). The import write pass warms its own cache inside its transaction so the soft-delete re-check reads the transaction's snapshot.

6.5 ProductLifecycleService

Pure state machine — see Runtime Flows §7.1. Returns what the caller must apply (nextStatus, setPublishedAtNow, robotsIndex); the caller applies it inside its own transaction.

7. Runtime Flows

7.1 Lifecycle transition

Legal transitions: draft → published | archived; published → unlisted | archived | draft; unlisted → published | archived | draft; archived → draft (read-only otherwise). Into published/unlisted requires a thumbnail; published_at is stamped once and never cleared; robots_index is true only for published, forced on every write path.

7.2 Storefront listing

Listings call ProductCardAssembler.assembleCards, not ProductResponseAssembler.assembleMany. That is 4 batched reads instead of 10 — no category, category-slug, brand-series, brand-series-slug, tag-join or seo read — and one signed storage URL per product instead of one per media item across all four media kinds. GET /:slug still uses the detail assembler, which is why the product page keeps its gallery, specifications and SEO block.

7.3 Import job

7.4 Cooperative cancellation

A processing import re-reads its own job status inside the write transaction and aborts (rolls back) the moment the row is no longer processing; the completing write returns its affected-row count and throws PRODUCT_JOB_NOT_PROCESSING on zero.

8. Money

All amounts are integer minor units (1000 = NPR 10.00), stored as bigint, never float or string. There is no tax field and none is to be added — tax is already included in MRP and selling price. discount/discountPercentage are derived (mrp - sellingPrice), never stored; "on sale" means sellingPrice < mrp. The money utility (utils/money/money.util.ts) is the first and only money representation in the codebase; CLAUDE.md forbids a second.

9. Filtering and Cursor Pagination

• Every filter optional, AND-composed by buildProductFilterConditions: categorySlug (includes descendants via recursive CTE), brandSlug, seriesSlug, tags (AND semantics — every tag required; empty resolved set ⇒ empty page), minPrice/maxPrice (inclusive minor units), stockStatus array, onSale, four discovery flags, q (name/description/sku, min 2 chars).
• Sorts: newest (default), priceAsc, priceDesc, discount (computed mrp - selling_price), relevance (search only; falls back to newest without a usable term).
• Cursor pagination on (sortKey, id); limit default 20, max 100; opaque cursor, sort-bound (400 on mismatch).
• Literal-predicate rule: every storefront index is partial on status = 'published' AND deleted_at IS NULL; the query must inline the literal because a bound parameter is proven (EXPLAIN, 20k rows, force_generic_plan) to seq-scan the whole table. Never bind the status on a storefront read.

9.1 onSale is a union, not a single predicate

onSale=true returns catalogue markdown (sellingPrice < mrp) union any product covered by a live special-deal campaign — a product priced at MRP whose whole discount comes from a campaign target (product, brand, or brand series) is included even though it fails the price comparison. buildOnSaleCondition (apps/api/src/modules/products/shared/product-filter.builder.ts:254-287, called from buildProductFilterConditions:172-181) resolves the live deal index before the query and injects the ids as SQL predicates — it does not post-filter query results, because post-filtering can only remove rows the database already returned and a campaign-only product is never returned by a bare price comparison in the first place.

onSale=true   selling_price < mrp
                OR id = ANY($dealProductIds)
                OR brand_id = ANY($dealBrandIds)
                OR brand_series_id = ANY($dealSeriesIds)

onSale=false  selling_price >= mrp
                AND id                            <> ALL($dealProductIds)
                AND coalesce(brand_id, -1)        <> ALL($dealBrandIds)
                AND coalesce(brand_series_id, -1) <> ALL($dealSeriesIds)

brand_id and brand_series_id are nullable FKs. A bare brand_id <> ALL($ids) evaluates to NULL for a brandless product, which drops that row from both the onSale=true and onSale=false pages — so the onSale=false branch wraps both columns in coalesce(..., -1). A fixture with brand_id IS NULL exists in the filter-builder spec specifically so this regresses loudly if the coalesce is ever removed.

MAX_ON_SALE_DEAL_IDS = 500 (apps/api/src/modules/products/shared/products.constants.ts:134) caps the combined product+brand+series id count injected into the query. On overflow, or on any Redis/DB failure resolving the live-deal index, product-storefront-query.service.ts degrades to the plain selling_price < mrp predicate and logs a warn — it never throws. A degraded sale page (fewer results) is preferred over a 500.

A degraded page must never be written to the cache, and the result carries a degraded: boolean so a caller can tell. Caching it would keep serving a /sale page missing every campaign-only product for the full 300-second TTL after the dependency recovered, when the very next request could have produced the correct page for free — turning one transient Redis blip into five minutes of wrong pricing.

All three surfaces that cache one of these results go through RedisCacheService.getOrSetIf(key, produce, isCacheableQueryResult, ttl): the storefront list() and search() and the discovery feeds. getOrSet writes its callback's return unconditionally — that is its contract, not a defect — so it is the wrong primitive for a producer that can return a knowingly wrong answer.

isCacheableQueryResult lives beside the degraded flag it tests (product-storefront-query.service.ts) rather than at each call site, because the rule is a property of the result and not of any one endpoint. Duplicating the !degraded test is how one of the three eventually forgets — which is exactly what had happened: the discovery feeds got it right and the two storefront surfaces did not.

sort=discount still orders by mrp - selling_price, so a campaign-only product (which has no price gap) sorts last under "biggest saving" even though its card shows the campaign discount. This is accepted, not a bug — recorded here rather than left silent.

idx_product_live_on_sale was dropped in migration 0035_drop_unusable_on_sale_index.sql (DROP INDEX "idx_product_live_on_sale"). That partial index's predicate was selling_price < mrp; a disjunction does not imply that predicate — a campaign-only row satisfies the query while failing the comparison the index was built on — so Postgres cannot use a partial index in a disjunctive query, at any data volume. Measured with EXPLAIN (enable_seqscan = off) against the seeded test DB: before this change the old predicate used the index via a bitmap scan; after, the planner ignores it entirely and falls back to idx_product_live_brand_created, applying the disjunction as a post-scan filter. buildOnSaleCondition has exactly one caller (buildProductFilterConditions), and that function has exactly three production call sites — admin, storefront query, and customer product services — so the index had no other consumer and was pure write amplification once the predicate it matched stopped being produced. See packages/db/src/schema/products/product.ts:477-499 for the removal note and what to index instead if /sale performance needs it later.

10. Jobs

• Outbox enqueued — the job row and its scheduling commit together; direct queue.add() in a handler is a defect.
• Import: CSV/XLSX, 25 MB / 50,000 rows; sku required and matches a live product ⇒ update, else create as draft; category/brand/series referenced by public id; all-or-nothing validation.
• Export: CSV; > 50,000 matching rows fails (PRODUCT_EXPORT_TOO_LARGE); zero-row export succeeds with a header-only file.
• Lease-based claim; stalled sweep after the claim TTL; cooperative cancellation; isStalled derived flag on detail.
• PRODUCTS is registered in REGISTERED_QUEUES (unlike CATALOG/OUTBOX) so relayed jobs get env-configured defaultJobOptions instead of BullMQ's bare attempts: 1.

11. Cache

Every admin mutation triggers triggerForWrite after commit; storefront reads are Redis-cached with CACHE_TTL.STANDARD (300s), and every key is built by CacheKeyUtil.build(PRODUCT_CACHE_PREFIX, …) with an op segment (list, search, suggest, detail, discovery). They are cleared by product and tag writes.

Variant writes count as product writes, and this is the part that was missing. ProductVariantAdminService create / update / delete now call ProductWriteSupportService.afterMutation after the transaction commits, exactly as every other product write path does. Variants are where the price lives — product.selling_price and product.mrp are rollups maintained by trg_product_variant_rollup_* — and where the stock row lives, so a variant write moves precisely what this domain exists to clear. Until this was wired, changing a price through the variant editor cleared neither Redis nor the storefront's Next cache, and the site kept quoting the old price for a full TTL with nothing in any log to say so.

The domain named is product, not inventory. Both registrations clear identical tags and identical Redis patterns today — deliberately duplicated rather than aliased so they can diverge later — so naming both would do the same work twice and clear nothing extra. The writer names its own domain.

The call sits after the commit. Inside the transaction it would fire for a write that may still roll back, and a network call inside a transaction is forbidden anyway. The variant spec asserts both directions: a purge on each successful write, and no purge when the write is refused by the optimistic lock or by the last-live-variant rule — those negative cases are what prove the placement rather than merely the presence.

PRODUCT_CACHE_PREFIX is product:v2: (shared/products.constants.ts), which sits inside the product:* invalidation glob above — so versioning the prefix does not break clearing.

The v2 versions the SERIALIZED SHAPE, not the data. Bump it whenever a product read's response shape changes. These endpoints cache a serialized response body, so a shape change with no bump means the same endpoint answers warm keys with the old shape and cold keys with the new one for a full 300-second TTL after deploy — non-deterministic, indistinguishable to the client, and invisible to the integration specs, which substitute a pass-through cache. v1 was the fat detail shape on listings; v2 is the card shape. Orphaned v1 keys are not deleted, they expire.

12. Security and Authorization

• Admin routes: JwtAuthGuard + RoleGuard with Products_CREATE/READ/UPDATE/DELETE/RESTORE and Tags_* permission codes; superadmin bypasses.
• Import submit re-checks the entity-specific permission pair (Products_CREATE and Products_UPDATE) because the route-level token alone would let a non-editor mass-create/update through a file (403 PRODUCT_JOB_ENTITY_PERMISSION_DENIED).
• Idempotency: create/restore/bulk/job-submit require an Idempotency-Key header; replay returns the original response.
• Rate limits per surface: ADMIN_READ 30/min, ADMIN_WRITE 10/min, ADMIN_BULK_WRITE 5/min, ADMIN_ASYNC_JOB_SUBMIT 10/hour, PUBLIC_READ 60/min, PUBLIC_SEARCH 60/min (guest-facing), PUBLIC_SUGGEST 300/min (guest-facing), PUBLIC_HIGH_FREQUENCY 300/min (feeds).
• Public surfaces never expose the integer PK; slugs are owned by product_slug so a retired URL cannot be re-pointed.
• Every storage key field (thumbnailKey, gallery/video/attachment key) is validated by IsStorageKey() (apps/api/src/common/validators/storage-key.validator.ts) against STORAGE_KEY_PATTERN — anchored, case-sensitive, public/-only: ^public\/[A-Za-z0-9][A-Za-z0-9._-]*(?:\/[A-Za-z0-9][A-Za-z0-9._-]*)*$. A path segment must start [A-Za-z0-9], so .. is unrepresentable by construction. Case matters because the S3 driver tests startsWith("public/") byte-exactly — a Public/… key would miss the public branch and fall through to getSignedUrl, becoming a valid pre-signed URL for a key that was never meant to be public.
• StorageUrlResolver.resolve() / resolveMany() take { publicOnly: true } on every customer-facing call site. A key that fails the full STORAGE_KEY_PATTERN (not just a public/ prefix check) resolves to null and is never signed — logged at WARN, since a guard rejection is a security control firing, not a silent failure. Every admin call site deliberately omits it: an operator legitimately previews objects that are not yet public, and the response there carries the raw key alongside the resolved url for exactly that reason — customer responses never do.
• resolveMany() isolates failures per key: one signing failure no longer rejects the whole Promise.all and 500s an entire product page. It also deduplicates the key set with a Set before resolving, so a repeated key (a shared placeholder thumbnail, the same swatch across several variants) is signed once.