Catalog Product - Backend Documentation

1. Backend Scope and Boundaries

The product backend owns catalog product lifecycle, slug continuity, product-tag linking, and customer product reads. It does not own checkout/order payment orchestration, but it provides pricing and sellability source-of-truth used by those modules.

2. Module Composition (Aggregate + Leaf)

• Aggregate admin composition: apps/api/src/modules/catalog/catalog-admin.module.ts
  • Includes ProductAdminModule
• Leaf admin module: apps/api/src/modules/catalog/admin/product/product-admin.module.ts
• Leaf customer module: apps/api/src/modules/catalog/customer/product/product-customer.module.ts
• Mobile composition: apps/api/src/modules/mobile/mobile.module.ts mounts customer module under /mobile.

3. Data Model (Drizzle / PostgreSQL)

Primary tables and joins:

• product (packages/db/src/schema/product/product.ts)
• product_slug_history (packages/db/src/schema/product/product-slug-history.ts)
• product_tag join (packages/db/src/schema/product/product-tag.ts)
• Related lookup tables: category, tag, featured_product, product_type

Key constraints/indexes used by runtime:

• product.slug unique
• product.product_type_id — FK to product_type.id (indexed via product_type_id_idx)
• product.product_kind enum: physical | digital | care_package (nullable; existing rows default to NULL, treated as non-physical)
• product_kind_idx index on product.product_kind
• product.brand_id — nullable FK to brand.id (indexed via brand_id_idx)
• product_tag composite primary key (product_id, tag_id)
• money columns mrp and sp are stored as integer minor units (for Stripe, USD cents)
• trigram indexes on product.title and product.description

IT Mart product columns on product:

• product_kind (enum product_kind, nullable) — physical | digital | care_package
• product_type_id (integer, FK to product_type.id, required) — references product type reference table
• specifications (jsonb, nullable) — flexible per-product-type specs object
• is_best_seller (boolean, required, default false) — storefront merchandising flag

Additional columns added in migration 0003_brand_series_and_product_extensions:

Index additions:

• product_trending_idx on product.is_trending
• product_trending_expires_idx on product.trending_expires_at
• product_series_id_idx on product.series_id

Dropped columns (removed in migration 0001_itmart_product_foundation):

• delivery, materials, certificate, is_master_grade, size, age_disclaimer, course_id

4. Runtime Rules and Domain Invariants

• sp cannot exceed mrp.
• productKind is required on create; validated as physical | digital | care_package.
• productTypeId is required on create; must reference an existing product_type.id.
• specifications is optional; stored as-is in JSONB without schema validation.
• Physical check uses isPhysicalProductKind(productKind) — returns true only for physical.
• Non-physical (digital, care_package) and null productKind rows return always-available in availability endpoint.
• Status transitions are constrained:
  • draft -> published
  • published -> unlisted
  • unlisted -> archived
• Customer list/detail include only published and isSellable=true products.
• Slug uniqueness check includes both current products and slug-history rows.
• Multi-table writes (product + slug history + product_tag) are transaction-wrapped.
• isTrending is admin-set; trendingExpiresAt is computed from trendingDays param at create/update time.
• isBestSeller (existing) gains an expiry column isBestSellerExpiresAt; computed from popularDays at create/update.
• Expiry check logic: or(isNull(expiresAt), gt(expiresAt, NOW())) — both boolean AND expiry must pass for a product to appear in discovery filters.
• Setting a flag to false (e.g., isTrending=false) nullifies the expiry column.
• averageRating and reviewCount are computed via scalar subqueries in the SELECT clause on every list query. Only status='approved' reviews are counted.
• minRating filter uses a correlated AVG subquery in the WHERE clause. Only applied when minRating param is present.
• parentCategoryId filter uses an IN subquery: category WHERE id=$parent OR parent_id=$parent (2-level only; no CTE).
• seriesId filter uses direct FK equality: eq(products.seriesId, brandSeriesId).

5. Caching Strategy

Customer read cache keyspaces:

• catalog:products:list:
• catalog:product:slug:
• catalog:products:featured:

All keys use CacheKeyUtil.build(...) with deterministic segments.

Additional discovery cache keyspaces:

catalog:products:new-arrivals:
catalog:products:best-sellers:
catalog:products:popular:
catalog:products:trending:

All new filter params (brandId, minPrice, maxPrice, isBestSeller, minRating, parentCategoryId, brandSeriesId, isTrending) are included in buildCacheKey() — each unique filter combination produces its own cache entry.

Admin write invalidation uses prefix patterns via invalidatePattern(...) for all above keyspaces.

5.1 Discovery Query Behavior

Customer product discovery supports filtering by brand and brand-series parameters:

• brandId — exact match filter on product.brand_id
• brandSlug — resolves brand by slug, then filters products by the resolved brand ID
• seriesId — exact match filter on product.series_id
• These filters can be combined with existing categoryId, productTypeId, and tagId filters
• Brand-series discovery (public) returns only visible series (isVisible=true)
• Brand-based filtering also respects the brand's visibility flag

5.1.1 Customer discovery flow

brand -> brand-series (optional) -> products filtered by series/brand

The discovery chain flows from brand selection → optional series drill-down → product list. Each step narrows the product result set:

• Brand-scoped: WHERE product.brand_id = :brandId
• Series-scoped: WHERE product.series_id = :seriesId
• Both: WHERE product.brand_id = :brandId AND product.series_id = :seriesId

6. Rate Limiting Strategy

Redis sorted-set sliding-window limiter is implemented in controllers for list/search endpoints:

• Admin list route limiter key prefix: rl:catalog:admin:products:search:
• Customer list route limiter key prefix: rl:catalog:customer:products:search:
• Exceed path throws RateLimitExceededException.

7. Error and Resilience Contracts

Primary error codes:

• PRODUCT_NOT_FOUND
• PRODUCT_CREATE_FAILED
• PRODUCT_CATEGORY_NOT_FOUND
• PRODUCT_TAG_NOT_FOUND
• PRODUCT_SP_EXCEEDS_MRP
• PRODUCT_INVALID_STATUS_TRANSITION
• PRODUCT_INVALID_SORT
• PRODUCT_BRAND_NOT_FOUND
• PRODUCT_SERIES_NOT_FOUND
• BRAND_SERIES_NOT_FOUND
• RATE_LIMIT_EXCEEDED

Redis read/write/cache-invalidation failures are logged and gracefully fall back to DB reads where implemented.

8. Performance Notes

• List queries use projection selects instead of loading full rows.
• Search combines trigram similarity + ILIKE with escaped patterns.
• Pagination uses PaginationUtil.getDrizzleParams(...).
• Count queries are executed in parallel with list query on customer list paths.

9. Backend Diagram

10. Release/QA Checklist

• Validate CRUD and permission gates for Products_*.
• Validate slug-history insertion and old-slug resolution.
• Validate rate-limiter thresholds through env values.
• Validate cache invalidation for create/update/delete.
• Validate transaction integrity for product + tags + slug-history updates.

11. File Map

12. Environment Variables

See Also

• Feature Guide: See Catalog Product - Feature List Section 6 (State Models) for product lifecycle diagrams and status transitions.
• API Reference: See Catalog Product - API & Integration Guide Section 7 (Endpoint Reference + Payload Cheatsheet) for API contracts.
• Product Type Module: See Product Type - Backend Documentation for product type reference table backend.
• Product Type API: See Product Type - API & Integration Guide for product type API contracts.