Skip to main content

Phase 3: Product Catalog

Depends on: Phase 2 (Core Entities & Membership) New Endpoints: 20 New Files: 22

Goal​

Implement the full product catalog: product CRUD, product categories (hierarchical), suppliers, and product-supplier relationships. After this phase, org admins can manage categories and suppliers org-wide, and store managers can create/update products within their stores.

Endpoints​

Organization-Scoped — All Org Members (Read)​

MethodPathHandlerDescription
GET/orgs/:org_id/categoriesCategoryController.listList categories (paginated)
GET/orgs/:org_id/categories/treeCategoryController.listTreeList category tree (hierarchical)
GET/orgs/:org_id/categories/:category_idCategoryController.getGet single category
GET/orgs/:org_id/suppliersSupplierController.listList suppliers (paginated)
GET/orgs/:org_id/suppliers/:supplier_idSupplierController.getGet single supplier
GET/orgs/:org_id/suppliers/:supplier_id/productsSupplierController.listProductsList supplier's products

Organization-Scoped — Org Admin (Write)​

MethodPathHandlerDescription
POST/orgs/:org_id/categoriesCategoryController.createCreate category
PUT/orgs/:org_id/categories/:category_idCategoryController.updateUpdate category
DELETE/orgs/:org_id/categories/:category_idCategoryController.deleteDelete category
POST/orgs/:org_id/suppliersSupplierController.createCreate supplier
PUT/orgs/:org_id/suppliers/:supplier_idSupplierController.updateUpdate supplier
DELETE/orgs/:org_id/suppliers/:supplier_idSupplierController.deleteDelete supplier
POST/orgs/:org_id/suppliers/:supplier_id/productsSupplierController.addProductLink supplier to product
DELETE/orgs/:org_id/suppliers/:supplier_id/products/:product_idSupplierController.removeProductUnlink supplier

Store-Scoped — All Store Members (Read)​

MethodPathHandlerDescription
GET/stores/:store_id/productsProductController.listList products (paginated)
GET/stores/:store_id/products/:idProductController.getGet single product
GET/stores/:store_id/products/barcode/:barcodeProductController.getByBarcodeBarcode lookup
GET/stores/:store_id/products/typesProductController.getTypesGet distinct product types

Store-Scoped — Store Manager+ (Write)​

MethodPathHandlerDescription
POST/stores/:store_id/productsProductController.createCreate product
PUT/stores/:store_id/products/:idProductController.updateUpdate product

Store-Scoped — Store Admin Only​

MethodPathHandlerDescription
DELETE/stores/:store_id/products/:idProductController.deleteSoft-delete product

SQLDelight Tables & Queries​

product_categories table​

Key queries:

  • findByOrgId(org_id, limit, offset) - Paginated list
  • findTreeByOrgId(org_id) - Full tree (ordered by depth + sort_order)
  • findById(category_id) - Single lookup
  • countByOrgId(org_id) - Pagination
  • insert(...) - Create
  • update(...) - Update
  • delete(category_id) - Delete (soft delete via active = false)
  • findChildren(parent_id) - Get child categories

suppliers table​

Key queries:

  • findByOrgId(org_id, limit, offset) - Paginated list
  • findById(supplier_id) - Single lookup
  • countByOrgId(org_id) - Pagination
  • insert(...) - Create
  • update(...) - Update
  • delete(supplier_id) - Soft delete

product_suppliers junction table​

Key queries:

  • findBySupplierId(supplier_id) - List products for a supplier
  • findByProductId(product_id) - List suppliers for a product
  • insert(product_id, supplier_id, supplier_sku, cost, is_preferred) - Link
  • delete(product_id, supplier_id) - Unlink

products table​

Key queries:

  • findByStoreId(store_id, limit, offset) - Paginated list
  • findById(product_id) - Single lookup
  • findByBarcode(store_id, barcode) - Barcode lookup
  • findDistinctTypes(store_id) - Distinct product types
  • countByStoreId(store_id) - Pagination
  • searchByName(store_id, query, limit, offset) - ILIKE search
  • insert(...) - Create
  • update(...) - Update
  • softDelete(product_id) - Set active = false

Files to Create​

Controllers (3)​

controller/org/CategoryController.java
  @RequestMapping("/orgs/{orgId}/categories")
  GET    /              -> categoryService.list(orgId, page, size)
  GET    /tree           -> categoryService.listTree(orgId)
  GET    /{categoryId}  -> categoryService.get(categoryId)
  POST   /              -> categoryService.create(orgId, request)
  PUT    /{categoryId}  -> categoryService.update(categoryId, request)
  DELETE /{categoryId}  -> categoryService.delete(categoryId)

controller/org/SupplierController.java
  @RequestMapping("/orgs/{orgId}/suppliers")
  GET    /                           -> supplierService.list(orgId, page, size)
  GET    /{supplierId}               -> supplierService.get(supplierId)
  GET    /{supplierId}/products      -> supplierService.listProducts(supplierId)
  POST   /                           -> supplierService.create(orgId, request)
  PUT    /{supplierId}               -> supplierService.update(supplierId, request)
  DELETE /{supplierId}               -> supplierService.delete(supplierId)
  POST   /{supplierId}/products      -> supplierService.addProduct(supplierId, request)
  DELETE /{supplierId}/products/{productId} -> supplierService.removeProduct(supplierId, productId)

controller/store/ProductController.java
  @RequestMapping("/stores/{storeId}/products")
  GET    /                  -> productService.list(storeId, page, size)
  GET    /{id}              -> productService.get(id)
  GET    /barcode/{barcode} -> productService.getByBarcode(storeId, barcode)
  GET    /types             -> productService.getTypes(storeId)
  POST   /                  -> productService.create(storeId, request)
  PUT    /{id}              -> productService.update(id, request)
  DELETE /{id}              -> productService.delete(id)

Services (3)​

service/CategoryService.java
  - list, listTree, get, create, update, delete
  - Tree building: flat list ordered by depth/sort_order, assembled into nested structure
  - Validates: no circular parent references, depth <= 5

service/SupplierService.java
  - list, get, listProducts, create, update, delete, addProduct, removeProduct
  - Validates: supplier belongs to org

service/ProductService.java
  - list, get, getByBarcode, getTypes, create, update, delete
  - Validates: product belongs to store, barcode unique per store

Repositories (4)​

repository/CategoryRepository.java
repository/SupplierRepository.java
repository/ProductSupplierRepository.java
repository/ProductRepository.java

Request DTOs (5)​

dto/request/CreateCategoryRequest.java
  { name: @NotBlank String, slug: @NotBlank String, parentId: UUID, sortOrder: int }

dto/request/UpdateCategoryRequest.java
  { name: @NotBlank String, slug: @NotBlank String, parentId: UUID, sortOrder: int, active: boolean }

dto/request/CreateSupplierRequest.java
  { name: @NotBlank String, contactEmail: @Email String, contactPhone: String }

dto/request/UpdateSupplierRequest.java
  { name: @NotBlank String, contactEmail: @Email String, contactPhone: String, active: boolean }

dto/request/AddProductSupplierRequest.java
  { productId: @NotNull UUID, supplierSku: String, cost: BigDecimal, isPreferred: boolean }

dto/request/CreateProductRequest.java
  { barcode: @NotBlank String, name: @NotBlank String, description: String,
    price: @NotNull BigDecimal, cost: @NotNull BigDecimal,
    over18: boolean, over21: boolean, taxRate: BigDecimal,
    stock: int, productType: @NotBlank String, active: boolean }

dto/request/UpdateProductRequest.java
  (same fields as CreateProductRequest)

Response DTOs (4)​

dto/response/CategoryResponse.java
  { categoryId: UUID, orgId: UUID, name: String, slug: String,
    parentId: UUID, isChild: boolean, depth: int, sortOrder: int,
    active: boolean, createdAt: Instant, updatedAt: Instant }

dto/response/CategoryTreeResponse.java
  { categoryId: UUID, name: String, slug: String, depth: int,
    sortOrder: int, active: boolean, children: List<CategoryTreeResponse> }

dto/response/SupplierResponse.java
  { supplierId: UUID, orgId: UUID, name: String, contactEmail: String,
    contactPhone: String, active: boolean, createdAt: Instant, updatedAt: Instant }

dto/response/ProductSupplierResponse.java
  { productId: UUID, supplierId: UUID, supplierSku: String,
    cost: BigDecimal, isPreferred: boolean }

dto/response/ProductResponse.java
  { productId: UUID, storeId: UUID, barcode: String, name: String,
    description: String, price: BigDecimal, cost: BigDecimal,
    requiresSerial: boolean, over18: boolean, over21: boolean,
    taxRate: BigDecimal, stock: int, productType: String,
    active: boolean, createdAt: Instant, updatedAt: Instant }

Mappers (3)​

mapper/CategoryMapper.java
  - static toResponse(ProductCategory) -> CategoryResponse
  - static toTree(List<ProductCategory>) -> List<CategoryTreeResponse>

mapper/SupplierMapper.java
  - static toResponse(Supplier) -> SupplierResponse
  - static toProductSupplierResponse(ProductSupplier) -> ProductSupplierResponse

mapper/ProductMapper.java
  - static toResponse(Product) -> ProductResponse

Business Rules​

  1. Categories support nesting up to depth 5 (parent_id references another category)
  2. Category slugs must be unique within an org
  3. Deleting a parent category should either fail if children exist or cascade-deactivate
  4. Product barcodes must be unique within a store
  5. Products reference a product_type string (not an enum - user-definable)
  6. Soft-delete pattern: set active = false, filter inactive from default list queries
  7. Supplier-product links are many-to-many via product_suppliers junction table
  8. One supplier per product can be marked is_preferred = true

Acceptance Criteria​

  1. Category CRUD works including hierarchical tree endpoint
  2. Supplier CRUD works with product linking/unlinking
  3. Product CRUD works with barcode lookup and type listing
  4. All list endpoints support pagination
  5. Product search by name works via ILIKE
  6. Soft-delete is used for products, categories, and suppliers
  7. Barcode uniqueness is enforced per store
  8. Category depth is validated (<= 5 levels)
  9. Bazel build passes
  10. Unit tests for CategoryService, SupplierService, ProductService