# SDD Schema Validation Report

**Generated:** 2026-02-05
**Spec ID:** 033 - Replenishment Reporting System

## Executive Summary

I validated the SDD against the actual MySQL schemas and found **5 critical issues** (1 resolved), **3 moderate issues**, and **4 minor inconsistencies** that needed to be addressed before implementation.

**SDD has been updated** with corrections for all issues. Remaining work is implementation-time (creating new methods, tables per the migrations).

---

## Critical Issues (Must Fix)

### 1. ~~`kiosk_sales.sales` vs `kiosk_sales.buys` Schema Mismatch~~ ✅ RESOLVED

**SDD Claim (line 79):**
> `kiosk_sales.buys` - has same structure as `kiosk_sales.sales`

**Local Environment:**
- `kiosk_sales.sales.subCatID`: `int(11)` with values like `1011`
- `kiosk_sales.buys.subCatID`: `varchar(20)` with values like `TS1`

**Resolution:** User confirmed a production migration has already converted `kiosk_sales.sales.subCatID` to VARCHAR to match buys table. Local environment will be updated when prod changes are pulled.

**SDD Updated:** Changed line 78 to note subCatID is VARCHAR in both tables.

---

### 2. ~~`storeSettings` Table Does Not Exist~~ ✅ FIXED IN SDD

**SDD Claims (lines 522-527):**
> Store-level defaults in storeSettings table (existing)

**Reality:**
No `storeSettings` table exists. Project uses `fpSettings` (key-value pairs).

**Resolution:** SDD updated to reference `fpSettings` table. Also added `replenishment_threshold_critical` key.

---

### 3. ~~`calculatePercentileRange` is Private~~ ✅ FIXED IN SDD

**SDD Claims (line 765):**
> endpoints: [getReplenishmentHeatmapData, calculatePercentileRange]

**Reality:**
`calculatePercentileRange` is a private method in HeatmapService.

**Resolution:** SDD updated to clarify that `ReplenishmentHeatmapService` is a **NEW** class (per ADR-3) that will implement its own `calculatePercentileRange`. Integration section now correctly references the new service.

---

### 4. ~~TaskListManager Methods Don't Match SDD~~ ✅ FIXED IN SDD

**SDD Claims (lines 776-778):**
> endpoints: [createTask, completeTask]

**Reality:** TaskListManager has `createTaskList()`, not `createTask()`.

**Resolution:** SDD integration section updated to clarify:
- `TaskListManager.createTaskList()` creates task lists
- `TaskCompletion` model handles individual task creation/completion
- Both will be used together for replenishment tasks

---

### 5. ~~BackstockFactory Missing Required Method~~ ✅ FIXED IN SDD

**SDD Claims (line 769):**
> endpoints: [getBinsByPOSCategory, getBinByID]

**Reality:** `getBinsByPOSCategory()` doesn't exist yet (to be created).

**Resolution:** SDD integration section updated to explicitly mark `getBinsByPOSCategory` as `(NEW)` - making it clear this must be implemented as part of the feature.

---

## Moderate Issues

### 6. bsBins.mainCategory is Integer, Not String

**SDD Claims (lines 489-491):**
> ADD COLUMN: mainPOSCategory VARCHAR(10) NULL
> Purpose: POS subcategory code (replaces mainCategory)

**Reality:**
```sql
mainCategory  int(11)  NULL
```

The existing `mainCategory` is an integer FK, not a varchar. The migration needs to:
1. Add new `mainPOSCategory VARCHAR(10)` column
2. NOT replace the existing `mainCategory` (which may still be in use)

---

### 7. fpSocketAssignments Missing Columns

**SDD Claims (lines 483-486):**
> ADD COLUMN: rackUnitsAllocated DECIMAL(5,2) NULL

**Current Schema:**
```sql
id               int(10) unsigned
layoutId         int(10) unsigned
socketId         int(10) unsigned
subcategoryCode  varchar(10)
sortOrder        int(10) unsigned
created_at       timestamp
```

**Missing Columns:** `rackUnitsAllocated` (expected - this is a migration to be created)

This is noted correctly in the SDD as a modification, just documenting for completeness.

---

### 8. fpRackTypes Missing Columns

**SDD Claims (lines 474-476):**
> ADD COLUMN: defaultRackUnits DECIMAL(5,2) DEFAULT 2.0

**Current Schema:**
- No `defaultRackUnits` column

This is noted correctly as a migration, documenting for completeness.

---

## Minor Inconsistencies

### 9. Variable Naming Inconsistency: `subcategoryCode` vs `subCatCode`

**In SDD:**
- API uses `subcategoryCode` (line 702, 740, etc.)
- DB references use `subcategoryCode` for new tables

**In Database:**
- `drsSubCategories` uses `subCatCode` (varchar(10))
- `fpSocketAssignments` uses `subcategoryCode` (varchar(10))

**Impact:** Minor - both are varchar(10), but naming is inconsistent. When joining, be explicit about column aliases.

---

### 10. Model Field Name Mismatch

**In SDD Entity (line 838):**
```
$tracking->lastReplenishmentDate
```

**In DB Schema (line 503):**
```
lastReplenishmentDate: TIMESTAMP NOT NULL
```

**Match:** YES - this is consistent. (Just noting the verification)

---

### 11. Urgency Level Enum Values

**SDD Entity (line 711):**
> urgencyLevel: enum (adequate|monitor|high|urgent|critical)

**SDD Algorithm (line 1126-1132):**
```
WHEN score <= thresholds.adequate: 'adequate'
WHEN score <= thresholds.monitor: 'monitor'
WHEN score <= thresholds.urgent: 'high'
WHEN score <= thresholds.critical: 'urgent'
ELSE: 'critical'
```

**Issue:** The threshold named `urgent` produces level `high`, and there's an implied `critical` threshold that isn't defined in the settings.

**Resolution:** Add `thresholdCritical` to settings or clarify the threshold-to-level mapping.

---

### 12. Integration Point: HeatmapService vs ReplenishmentHeatmapService

**SDD Claims (line 762-766):**
> from: ReplenishmentService
> to: HeatmapService
> endpoints: [getReplenishmentHeatmapData, calculatePercentileRange]

**But SDD Also States (line 342):**
> Heatmap service: New ReplenishmentHeatmapService (clean separation from existing)

**Confusion:** The integration section mentions HeatmapService, but ADR-3 decided on a NEW ReplenishmentHeatmapService.

**Resolution:** Update integration section to reference `ReplenishmentHeatmapService` instead.

---

## Verified Correct

These items were verified as accurate:

| Item | SDD Location | Status |
|------|--------------|--------|
| `drsSubCategories.subCatCode` is VARCHAR(10) | line 85 | VERIFIED |
| `drsCategories.catCode` is VARCHAR(10) | Referenced | VERIFIED |
| `fpSettings` key-value structure | Implied | VERIFIED |
| `bsBin_Cat` junction table exists | line 495 | VERIFIED |
| Migration files don't exist yet | line 461-464 | VERIFIED (expected) |
| Replenishment module doesn't exist yet | Directory map | VERIFIED (expected) |

---

## Recommended Actions

### ✅ Completed (SDD Updated)

1. ~~**CRITICAL:** Investigate `kiosk_sales.buys.subCatID` format~~ - User confirmed prod has been migrated; local will sync
2. ~~**CRITICAL:** Update SDD to use `fpSettings` instead of `storeSettings`~~ - Done
3. ~~**CRITICAL:** Update integration section to reference correct method names~~ - Done
4. ~~**MODERATE:** Document new vs existing methods in integration section~~ - Done

### During Implementation

1. Pull production database changes to sync `kiosk_sales.sales.subCatID` to VARCHAR
2. Create `BackstockFactory::getBinsByPOSCategory()` method (marked as NEW in SDD)
3. Create `ReplenishmentHeatmapService` with its own `calculatePercentileRange()` implementation
4. Use `TaskListManager` + `TaskCompletion` model together for task handling

### Documentation Updates Applied

All SDD sections have been updated:
- ✅ Line 78: Updated to note subCatID is VARCHAR
- ✅ Lines 522-528: Changed to `fpSettings`, added `threshold_critical`
- ✅ Lines 762-793: Updated integration endpoints with (NEW) markers and notes
- ✅ Algorithm section: Already had `thresholds.critical` reference

---

## Files Checked

- `kiosk_sales.sales` - DESCRIBE output verified
- `kiosk_sales.buys` - DESCRIBE output verified
- `kiosk_ou00.fpRackTypes` - DESCRIBE output verified
- `kiosk_ou00.fpRacks` - DESCRIBE output verified
- `kiosk_ou00.fpSocketAssignments` - DESCRIBE output verified
- `kiosk_ou00.bsBins` - DESCRIBE output verified
- `kiosk_ou00.bsBin_Cat` - DESCRIBE output verified
- `kiosk_ou00.fpSettings` - DESCRIBE output verified
- `kiosk_ou00.drsSubCategories` - DESCRIBE output verified
- `kiosk_ou00.drsCategories` - DESCRIBE output verified
- `userfrosting/src/BuyerKiosk/FloorPlan/Services/HeatmapService.php` - Method signatures verified
- `userfrosting/src/BuyerKiosk/Backstock/BackstockFactory.php` - Method signatures verified
- `userfrosting/src/BuyerKiosk/Workbook/TaskListManager.php` - Method signatures verified
- `userfrosting/src/BuyerKiosk/Core/Store.php` - Properties verified