Inventory Tracking
Relevant source files
The following files were used as context for generating this wiki page:
- app/(app)/inventory/intake/page.tsx
- db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.sql
- db/migrations/20251207000001_inventory_intake_cost_tracking.sql
- lib/hooks/use-realtime.ts (Referenced via inventory loading logic)
- scripts/help-center-data.ts
- tests/data-processing-logic.test.ts
Inventory Tracking in DRX is a multi-layered system designed for high-precision retail operations, specifically optimized for weight-based products (e.g., cannabis). It encompasses real-time stock monitoring, sophisticated cost-per-gram (CPG) calculations, and immutable audit trails. The system supports both unit-based (count) and weight-based (grams) tracking, allowing for fractional quantities and complex stock movements such as shrinkage, intakes, and random inventory checks.
Sources: scripts/help-center-data.ts:258-270, README.md:120-125
Core Data Architecture
The database schema utilizes a consolidated set of tables to handle the lifecycle of a product, from intake to sale or loss. The inventory table serves as the source of truth for current stock levels, while specialized tables like inventory_intakes, stock_movements, and shrinkage_events provide granular traceability.
Entity Relationship Model
The following diagram illustrates the relationships between the core inventory entities and the auditing system.
The ER diagram shows how the central inventory record is supported by intake, movement, and audit tables.
Sources: db/migrations/20251207000001_inventory_intake_cost_tracking.sql:22-68, db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.sql:110-125
Inventory Schema Fields
| Field | Type | Description |
|---|---|---|
base_unit | VARCHAR(10) | The primary unit of measure (default: 'g'). |
inventory_in_grams | DECIMAL(16, 4) | High-precision weight for solids. |
quantity_on_hand | INTEGER | Unit count for discrete items. |
price_per_gram | DECIMAL(12, 4) | Current selling price used in POS calculations. |
unit_cost | DECIMAL(12, 2) | Historical cost per base unit. |
Sources: db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.sql:47-52
Inventory Intake Workflow
The Intake system manages the entry of new stock with a focus on Cost of Goods Sold (COGS) accuracy. It separates product costs from delivery costs and implements an automated approval gate for unusual cost deviations.
Intake Process Flow
This flow represents the logic used to process new stock and ensure cost accuracy via manager intervention.
Sources: app/(app)/inventory/intake/page.tsx:87-140, db/migrations/20251207000001_inventory_intake_cost_tracking.sql:22-45
Cost Calculations
The system calculates the calculated_cost_per_gram during intake using the following logic:
// Calculated from total product cost divided by weight received
const costPerGram = total_cost / total_weight_grams;
// Total expenditure includes separate delivery fees
const totalIntakeCost = total_cost + delivery_cost;
Sources: app/(app)/inventory/intake/page.tsx:55-63, tests/data-processing-logic.test.ts:74-85
Precision and Weight-Based Tracking
DRX supports fractional weights to accommodate small-quantity retail sales. The system uses the DECIMAL(12, 4) type for weight fields to ensure precision up to four decimal places.
Weight vs. Unit Behavior
- Liquids: Measured in ounces (oz).
- Solids: Measured in grams (g).
- Conversions: The system can deduct a "canonical" weight (e.g., 3.5g) when a specific unit (e.g., "1/8th") is sold.
Sources: scripts/help-center-data.ts:265-270, README.md:120-125
Traceability and Audit Logs
Every change to inventory is recorded in the stock_movements table and the system_audit_log.
Stock Movement Types
| Movement Type | Trigger | Effect on Stock |
|---|---|---|
intake | Inventory Intake Page | Increase |
sale | POS Transaction | Decrease |
shrinkage | Manual Adjustment (Damage/Loss) | Decrease |
inventory_variance | Discrepancy in Random Check | Increase/Decrease |
Sources: db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.sql:110-125, db/migrations/20251207000001_inventory_intake_cost_tracking.sql:128-142
Shrinkage and Variance Logic
Shrinkage is handled via the record_shrinkage_event RPC function. It requires a type (e.g., 'personal_use', 'damaged', 'inventory_variance') and automatically calculates the cost value of the loss based on the current unit cost.
Sources: db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.sql:194-255
Random Inventory Checks
To maintain accuracy, the system prompts for random inventory checks based on a configurable transaction frequency.
The sequence diagram details the automated trigger for auditing physical stock against system records.
Sources: db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.sql:271-335
Summary
Inventory Tracking in DRX combines high-precision decimal storage with a robust intake and audit workflow. By integrating automated cost calculations, manager approval gates for anomalies, and random periodic checks, the system ensures that physical stock levels and financial values (COGS) remain synchronized across the retail lifecycle.
Sources: app/(app)/inventory/intake/page.tsx, db/migrations/20251207000001_inventory_intake_cost_tracking.sql