Skip to main content

Stock Count Snapshot Implementation

Overview​

The stock count snapshot functionality creates a point-in-time snapshot of inventory levels for stock count sessions. This implementation follows the pattern from the provided SQL function but adapts it to work with the existing NestJS/Kysely codebase structure.

Key Features​

1. Scope Resolution​

The snapshot creation supports multiple scope types:

  • All products: Includes all products at the specified location
  • Product-specific: Includes only specified product IDs
  • Category-based: Includes all products in specified categories
  • Brand-based: Includes all products from specified brands

2. Dual Snapshot Types​

The implementation creates two types of snapshots:

Tracked Inventory (Batch/Serial)​

  • Creates snapshot records for products with batch numbers or serial numbers
  • Aggregates quantities from inventory_detail table
  • Links to specific inventory detail records
  • Calculates weighted average cost (WAC) from the main inventory record

Untracked Inventory​

  • Creates snapshot records for products without batch/serial tracking
  • Uses quantities directly from the main inventory table
  • Calculates WAC for cost valuation

3. Cost Calculation​

  • Uses Weighted Average Cost (WAC) calculation: cost / quantity
  • Handles zero quantity cases gracefully (sets unit cost to 0)
  • Rounds to 6 decimal places for precision

Implementation Details​

Database Transaction​

All snapshot operations are wrapped in a database transaction to ensure data consistency.

Performance Optimizations​

  • Uses efficient JOINs instead of complex subqueries
  • Batches insert operations for better performance
  • Avoids complex WHERE conditions that could cause type issues

Error Handling​

  • Validates session status before creating snapshots
  • Checks for scope definitions
  • Handles empty product lists gracefully
  • Provides detailed logging for debugging

Usage​

// Create a snapshot for an active session
await stockCountService.createSnapshot(
  sessionId,
  { at: new Date().toISOString() }
);

Database Schema Requirements​

The implementation expects the following tables:

  • stock_count_session: Session management
  • stock_count_scope: Scope definitions
  • stock_count_snapshot: Snapshot records
  • inventory: Main inventory records
  • inventory_detail: Detailed inventory records (batch/serial)
  • product: Product information

Migration from SQL Function​

This implementation adapts the provided SQL function to work with:

  • Kysely query builder instead of raw SQL
  • NestJS dependency injection
  • TypeScript type safety
  • Existing repository patterns

The core logic remains the same, but the implementation is more maintainable and type-safe.