Product & BOM Review Management System

Overview

The Product & BOM Review Management System provides asynchronous, intelligent review capabilities for product and bill-of-materials data. It runs independently from the regular S&OP process, allowing the product team to manage product reviews on their own cadence (annual, semi-annual, ad-hoc) while the system automatically detects when forced reviews are needed.

Key Features

1. Asynchronous Review Processing

• Reviews are queued for background processing via pgmq
• Doesn't block the regular S&OP planning cycle
• Can be scheduled during off-peak hours
• Independent of monthly forecast/planning updates

2. Last Review Tracking

• Every product shows when it was last reviewed
• Calculated as daysSinceReview = now - last_reviewed_at
• Visible in product dashboards for transparency
• Helps identify stale product information

3. Automatic Change Detection

• Monitors products for material changes
• Triggers forced reviews when changes exceed thresholds
• Tracks all product modifications in change log
• Distinguishes between major and minor changes

4. Review Types

INITIAL

• First product/BOM review after upload
• Required for all new products
• Baseline analysis for pricing, supply chain, BOM structure

PERIODIC

• Scheduled reviews (annual, semi-annual, quarterly)
• Triggered by system based on days since last review
• Typical cadence: annual review for stable products
• Can be configured per product category

FORCED

• Triggered when material changes detected
• Prevents stale product information affecting decisions
• Examples:
  • Lifecycle status change (Active → EOL)
  • Cost increase >5%
  • Price change >10%
  • Supply risk level change
  • BOM structure modification

ADHOC

• On-demand reviews initiated by product team
• Triggered manually when needed
• Useful when significant market/operational changes occur

5. Notification & Action Items

• Reviews generate specific, actionable tasks
• Examples:
  • Pricing optimization opportunity
  • Supply chain risk mitigation
  • BOM structure validation
  • Cost reduction initiative
• Action items are:
  • Assigned to product/sourcing/finance teams
  • Tracked with due dates
  • Linked to S&OP/financial/pricing decisions

Material Change Detection

The system identifies material changes that require management attention:

Pricing Changes

• Cost increase >5% - impacts gross margin, may trigger pricing review
• Price change >10% - market signal, requires justification
• Action: Review margin optimization, competitive positioning

Product Lifecycle

• Status change (Active, EOL, New) - material change
• Any status transition triggers forced review
• Action: Evaluate demand planning, discontinuation planning

Supply Chain

• Risk level change (Low → High, etc.) - operational risk
• Requires mitigation strategy review
• Action: Diversify suppliers, increase safety stock

BOM Structure

• Component changes - affects cost, lead time, quality
• Quantity changes - changes production requirements
• Action: Update supply plans, validate cost impact

Material vs. Minor Changes

Material Changes (trigger forced review):

• Lifecycle status changes
• Cost changes >5%
• Price changes >10%
• Supply risk level changes
• BOM structure modifications

Minor Changes (tracked but don't force review):

• Product name/description changes
• Attribute updates not affecting cost/price
• Metadata updates
• Non-core domain attribute changes

Review Process

1. Review Initiation

Product Upload / Product Update / Scheduled Trigger
        ↓
   Create Review Record (PENDING)
        ↓
   Queue for Processing
        ↓
   Return to User

User sees: "Review pending" status with queue position estimate

2. Async Processing

Background Worker
        ↓
   Fetch Review & Product Data
        ↓
   Update Status → IN_PROGRESS
        ↓
   Perform Analysis:
   - Pricing analysis
   - Supply chain assessment
   - BOM structure review
   - Market position analysis
        ↓
   Generate Findings & Recommendations
        ↓
   Create Action Items
        ↓
   Update Status → COMPLETED
        ↓
   Notify Team

3. Team Notification

When review completes with action items:

• Dashboard notification: "5 action items from {Product} review"
• Email to assigned teams
• In-app task queue updated
• Action items linked to product for context

Data Model

product_bom_reviews Table

CREATE TABLE product_bom_reviews (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  product_id UUID NOT NULL,
  sku VARCHAR NOT NULL,

  -- Review lifecycle
  review_type VARCHAR NOT NULL, -- INITIAL, PERIODIC, FORCED, ADHOC
  status VARCHAR NOT NULL,       -- PENDING, IN_PROGRESS, COMPLETED, FAILED
  is_forced_review BOOLEAN DEFAULT false,

  -- Timestamps
  triggered_at TIMESTAMP,
  triggered_by VARCHAR,          -- User ID or 'SYSTEM'
  scheduled_for TIMESTAMP,       -- When review should process
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  last_reviewed_at TIMESTAMP,

  -- Review findings
  review_findings JSONB,         -- Analysis results
  action_items JSONB ARRAY,      -- Generated tasks
  notes TEXT,

  -- Error tracking
  error_message TEXT,

  -- Audit
  created_at TIMESTAMP,
  updated_at TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (product_id) REFERENCES core_entities(entity_id),
  INDEX (tenant_id, status),
  INDEX (tenant_id, sku),
  INDEX (triggered_at)
);

product_bom_change_log Table

CREATE TABLE product_bom_change_log (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  product_id UUID NOT NULL,
  sku VARCHAR NOT NULL,

  -- Change details
  change_type VARCHAR NOT NULL,  -- 'major' or 'minor'
  changes JSONB ARRAY,           -- Array of field changes

  -- Audit
  changed_at TIMESTAMP,
  changed_by VARCHAR,            -- User ID or 'SYSTEM'

  created_at TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (product_id) REFERENCES core_entities(entity_id),
  INDEX (tenant_id, product_id),
  INDEX (change_type),
  INDEX (changed_at)
);

API Integration Points

1. Product Upload

After successful CSV import, automatically initiate INITIAL review:

// In ProductService.importProductsFromCsv
const review = await ProductBomReviewManagementService.initiateReview({
  tenantId,
  productId: product.entity_id,
  sku: product.sku,
  reviewType: 'INITIAL',
  triggeredBy: userId
});

2. Product Update

When product data changes, track changes and trigger FORCED review if material:

// In product update endpoint
const changeAnalysis = await ProductBomReviewManagementService.trackProductChange(
  tenantId,
  previousData,
  newData
);

if (changeAnalysis.forcedReviewTriggered) {
  // Notify user of forced review
}

3. Periodic Review Scheduling

Daily/weekly cron job to check for products needing periodic review:

// In cron job
const productsNeedingReview = await ProductBomReviewRepository
  .findNeedingPeriodicReview(tenantId, 365); // 365 days = annual

for (const product of productsNeedingReview) {
  await ProductBomReviewManagementService.initiateReview({
    tenantId,
    productId: product.product_id,
    sku: product.sku,
    reviewType: 'PERIODIC',
    triggeredBy: 'SYSTEM'
  });
}

4. Dashboard Display

Show review status in product pages:

// In product detail page
const lastReview = await ProductBomReviewManagementService.getLastReviewInfo(
  tenantId,
  sku
);

// Display:
// "Last reviewed 45 days ago" (green if <90 days, yellow if <180, red if older)
// "Forced review pending" (if needed)
// Action items count

Review Findings Structure

Pricing Analysis

{
  unit_cost: 100,
  unit_price: 150,
  margin: 33.3,
  gaps: [
    {
      issue: 'Low margin',
      margin: '33.3%',
      recommendation: 'Consider price increase or cost reduction'
    }
  ]
}

Supply Chain Analysis

{
  supply_risk: {
    overall_risk_score: 0.6,
    risk_level: 'High',
    primary_risk_factors: ['Single-source supplier', 'APAC geopolitical risk']
  },
  risks: [
    {
      issue: 'High supply risk detected',
      factors: ['Single-source supplier'],
      recommendation: 'Diversify suppliers'
    }
  ]
}

BOM Analysis

{
  component_count: 12,
  issues: [],
  recommendations: [
    {
      issue: 'Component cost inflation',
      recommendation: 'Evaluate design alternatives'
    }
  ]
}

Market Position

{
  lifecycle_stage: 'Growth',
  demand_pattern: 'Stable',
  key_competitors: ['CompetitorA', 'CompetitorB'],
  market_position: 'Mid-to-high tier, competing on quality'
}

Action Items

Each action item is a specific, trackable task:

{
  title: 'Review pricing for SKU-12345',
  description: 'Price optimization opportunity identified',
  category: 'PRICING', // PRICING, SUPPLY_CHAIN, BOM, MARKET
  priority: 'HIGH', // HIGH, MEDIUM, LOW
  status: 'OPEN', // OPEN, IN_PROGRESS, COMPLETED
  assigned_to: null, // Will be assigned by team
  due_date: '2025-11-22',
  related_data: {
    current_margin: '15.2%',
    target_margin: '25%',
    recommendation: 'Price increase or cost reduction'
  }
}

Integration with S&OP & Financial Decisions

Impact on Demand Planning

• Product lifecycle changes (EOL) → adjust demand forecasts
• Cost changes → flag for supply chain review
• New products → ensure demand plan includes them

Impact on Financial Planning

• Price changes → revenue impact analysis
• Cost changes → COGS, gross margin impact
• Supply risk → inventory/safety stock impact

Impact on Pricing Strategy

• Margin analysis → pricing optimization
• Market positioning → value-based pricing
• Competitive analysis → price strategy review

Implementation Roadmap

Phase 1: Core Infrastructure

• ✓ Service: ProductBomReviewManagementService
• ✓ Repositories: ProductBomReviewRepository, ProductBomChangeLogRepository
• Database migrations for review tables
• Change detection integration with ProductService

Phase 2: API Endpoints

• GET /api/products/{id}/reviews - Review history
• GET /api/products/{id}/last-review - Last review info
• GET /api/products/reviews/pending - Pending reviews
• POST /api/products/{id}/review/initiate - Manual review trigger
• POST /api/products/{id}/review/{reviewId}/action - Action item updates

Phase 3: Background Processing

• pgmq integration for async review processing
• ReviewProcessorWorker for background review execution
• Periodic review scheduler (cron jobs)
• Notification integration

Phase 4: Frontend

• Product detail page: Last review display
• Forced review indicator
• Action items dashboard
• Review history view
• Periodic review configuration

Phase 5: Advanced Features

• ML-based anomaly detection for auto-flagging
• Comparative analysis (price vs. market)
• Historical trend analysis
• Compliance tracking (required review certifications)

Example Scenarios

Scenario 1: New Product Upload

1. Product team uploads 50 new products via CSV
2. System creates 50 INITIAL reviews in PENDING status
3. User sees "50 products queued for review"
4. Background worker processes reviews over next few hours
5. Team receives notifications with findings
6. Action items appear in product workbench

Scenario 2: Price Increase Detection

1. Product Cost increases from $100 → $110 (10% increase)
2. System detects material change, triggers FORCED review
3. User sees "Forced review pending" in product detail
4. Review processes, identifies pricing strategy issue
5. Finance team gets action item: "Validate price increase strategy"
6. Price adjustment decision feeds into S&OP

Scenario 3: Annual Review Cycle

1. Cron job runs daily, identifies products last reviewed >365 days ago
2. System initiates PERIODIC reviews for 100 products
3. Reviews distribute across week via queue
4. Finance team reviews findings weekly in their dashboard
5. Act on pricing, supply chain, or BOM recommendations
6. Use insights for annual cost reduction initiative

Scenario 4: End-of-Life Product

1. Product lifecycle_status changed: Active → EOL
2. System detects material change, triggers FORCED review
3. Review flags demand plan impact
4. Demand planner gets action item: "Validate EOL timeline in forecast"
5. Supply chain gets action item: "Transition last-buy inventory"
6. Finance gets action item: "Update revenue forecast"

Configuration

Review Cadence by Product Category

const reviewCadence = {
  'Electronics': 180,      // Semi-annual
  'Raw Materials': 365,    // Annual
  'Strategic Components': 90, // Quarterly
  'Commodity': 365         // Annual
};

Material Change Thresholds

const materialChangeThresholds = {
  cost_increase_percent: 5,
  price_change_percent: 10,
  supply_risk_level_change: true, // Any change
  lifecycle_status_change: true,  // Any change
  bom_structure_change: true      // Any change
};

Notification Audience

const notificationAudience = {
  'PRICING': ['Finance', 'Product'],
  'SUPPLY_CHAIN': ['Procurement', 'Supply Chain'],
  'BOM': ['Engineering', 'Manufacturing'],
  'MARKET': ['Sales', 'Marketing', 'Product']
};

Testing

Tests cover:

• Change detection algorithms
• Material change thresholds
• Review lifecycle (PENDING → IN_PROGRESS → COMPLETED)
• Finding generation for each analysis type
• Action item creation
• Notification triggers
• Repository queries (pending, completed, forced reviews)
• Periodic review identification

FAQ

Q: What if a product never gets reviewed? A: Periodic review scheduler will flag it for annual review. Status will show as "Overdue" in dashboard.

Q: Can reviews be cancelled? A: PENDING reviews can be cancelled if initiated by mistake. IN_PROGRESS reviews should complete for audit trail.

Q: What if a review takes a long time to process? A: Review status shows as "IN_PROGRESS" so team knows it's being analyzed. Large products with complex BOMs may take hours.

Q: How do action items relate to S&OP tasks? A: Action items are separate from S&OP tasks. They feed into the product/pricing workbench. Can be linked to S&OP decisions.

Q: Can I schedule reviews for specific times? A: Yes, use scheduled_for timestamp. Cron job respects this scheduling.

Q: What happens if review analysis fails? A: Status changes to FAILED with error message. Team can retry or initiate new review.

---

Last Updated: 2025-10-23 Status: Design Complete, Ready for Implementation