ShopFloor - Data Model

Data Model

How the ~225 entities are organized. This doc breaks the model into 13 clusters and draws a focused ERD per cluster instead of one mega-diagram nobody reads.

Read 03-glossary.md for term definitions. Items marked ⚠️ are inferences worth confirming.

1. How to read this doc

One cluster per ERD. Each ERD shows the spine of a subsystem (5–15 entities) and its foreign keys. Less important attributes are omitted.
Bridge entities appear in multiple clusters; they’re called out in §15.
Projects is the universal scoping entity — assume nearly every entity in clusters A, B, D, E, F, J, K, L, M has a ProjectID FK even if not drawn.
Tenant scoping is enforced by Finbuckle’s global query filter on RepositoryDbContext; TenantId is on every multi-tenant entity but rarely drawn explicitly.

2. The central hub: `WorkTracking`

Before each cluster, here’s the spine. WorkTracking is the entity that ties almost everything together — one row per physical unit, the system’s central state.

erDiagram
    WorkTracking ||--o{ WorkTrackingOperations : "audit movements"
    WorkTracking ||--o{ WorkTrackingDetails : "per-scan data"
    WorkTracking }o--|| WorkOrders : belongs-to
    WorkTracking }o--|| BOMHeader : "is-this-model"
    WorkTracking }o--|| Projects : scoped-to
    WorkTracking }o--o| Station : "currently-at"
    WorkTracking }o--o| GradingTypes : graded-as
    WorkTracking }o--o| OutboundPallets : on-pallet
    WorkTracking }o--o| BoxPallet : in-box
    WorkTracking ||--o| SystemInformation : has-specs
    WorkTracking ||--o{ RmaItemsRelation : returned-via
    WorkTracking ||--o{ KittingLog : kitted-with
    WorkTracking ||--o{ ImagingAndTestingLog : imaged-as
    WorkTracking ||--o{ TestFilesResults : tested-as
    WorkTracking ||--o{ ItemConditionTest : condition-checks

If you only learn one entity, learn this one.

3. Cluster A — Unit Tracking Core

The core of “what unit exists, what model is it, what order did it come in on.”

erDiagram
    Projects ||--o{ WorkOrders : has
    Projects ||--o{ WorkTracking : has
    Projects ||--o{ BOMHeader : "via-BOMHeaderByProject"
    WorkOrders }o--|| Route : runs-on
    WorkOrders }o--|| ProductLineCategory : for
    WorkOrders }o--o| ProgramType : in-program
    WorkOrders ||--o{ WorkTracking : contains
    WorkOrders ||--o{ WorkOrderBol : links-to-BOL
    BOMHeader ||--o{ BOMHeaderDetails : "spec-rows"
    BOMHeader ||--o{ BOMHeaderByProject : "per-project-config"
    BOMHeader }o--|| BOMHeaderCategory : classified-as
    BOMHeader }o--|| Manufacturer : made-by
    BOMHeader }o--|| ProductLine : product-line
    BOMHeader ||--o{ CustomSKUMapping : "external SKU aliases"
    WorkTracking ||--o{ WorkTrackingOperations : audit-log
    WorkTracking ||--o{ WorkTrackingDetails : station-data

Key entities

Entity	Role
`WorkOrders`	A batch — usually one inbound BOL. Has `RouteID` (defines stations its units traverse) and `BOLFileIdentifier`. `IsRMA=true` for returns.
`WorkTracking`	The per-serial unit record. Central to everything.
`WorkTrackingOperations`	Audit log of station-to-station movements. ⚠️ Write path not fully traced.
`WorkTrackingDetails`	Per-station supplementary data tied to a `WorkTracking` × `Station` pair.
`BOMHeader`	The product model — a specific laptop/desktop SKU family.
`BOMHeaderByProject`	Per-project availability/config of a `BOMHeader`.
`BOMHeaderCategory`	Classification of `BOMHeader`s.
`BOMHeaderDetails`	Spec rows under a `BOMHeader`.
`Projects`	The scoping entity — every multi-tenant entity refers to one.
`ProductLine` / `ProductLineCategory`	Product family taxonomy (laptops/desktops/monitors). Routes are scoped to ProductLineCategory.
`WorkOrderBol`	Join row between `WorkOrders` and `BOMHeader`.
`CustomSKUMapping`	(New table per recent commits.) Maps external custom SKU strings to internal `BOMHeader`. See §14.

4. Cluster B — Workflow / Routing

The route engine that drives station-to-station movement. See 13-workflow-engine.md for runtime mechanics.

erDiagram
    Route ||--o{ RouteFlow : has-steps
    Route ||--o{ ProductionFlow : instantiated-as
    Route }o--|| ProductLineCategory : for
    RouteFlow }o--|| StationType : "is-step-of-type"
    ProductionFlow ||--o{ Station : contains
    ProductionFlow }o--|| Projects : scoped-to
    ProductionFlow ||--o| PackingConfig : packing-rules
    Station }o--|| StationType : is-of-type
    StationType }o--|| StationCategory : in-phase
    Station ||--o{ StationSettings : configured-with
    Station ||--o{ StationPrinterConfig : printers
    StationType ||--o{ TriageEvaluation : "has-eval (if Triage)"

Key entities

Entity	Role
`Route`	Ordered template of `StationType`s. Scoped to `ProductLineCategory`.
`RouteFlow`	One step in a Route — `(RouteID, StationTypeID, ScanOrder)`.
`ProductionFlow`	A physical instantiation of a Route inside a Project.
`Station`	A physical workstation. Has a `StationTypeID` (its role) and a `ProductionFlowID` (which line).
`StationType`	The role (Dock, Triage, Grading, …). Belongs to a `StationCategory`.
`StationCategory`	One of the 7 lifecycle phases (`StationCategoryEnum`).
`StationSettings`, `StationPrinterConfig`	Per-station configuration.
`SubStations`	⚠️ Sub-station hierarchy — usage not fully traced.

5. Cluster C — System / Spec Capture

Hardware spec data captured at the Windows Test System and during imaging/testing.

erDiagram
    WorkTracking ||--o| SystemInformation : has
    SystemInformation ||--o{ SystemInformationDisk : disks
    SystemInformation ||--o{ SystemInformationGraphic : gpus
    SystemInformation ||--o{ EngineeringCpuData : "engineering CPU"
    SystemInformation ||--o{ EngineeringMemoryData : "engineering RAM"
    SystemInformation ||--o{ EngineeringDiskData : "engineering disk"
    SystemInformation ||--o{ EngineeringDisplayData : "engineering display"
    SystemInformation ||--o{ EngineeringGpuData : "engineering GPU"
    SystemInformation ||--o{ EngineeringBatteryData : "engineering battery"
    SystemInformation ||--o{ EngineeringNetworkData : "engineering NIC"
    SystemInformation ||--o{ EngineeringWebcamData : "engineering webcam"
    WorkTracking ||--o| SystemVariantCode : variance-coded
    WorkTracking ||--o{ SystemVarianceCodeAssignment : variance-history
    WorkTracking ||--o{ WindowsTestResultsUpload : raw-uploads
    WorkTracking ||--o{ TestFilesResults : test-files
    WorkTracking ||--o{ TestSnResults : test-results
    WorkTracking ||--o{ ImagingAndTestingLog : audit-log
    DigitalProductKey }o--|| WorkTracking : "AssignedSerialNumber FK"
    DpkSkusSpecifiedCatalog }o--|| Projects : scoped

Notes

SystemInformation is a structured spec; WorkTracking.SystemInformationBlob is the raw JSON of the same data. If the parser failed, IsSystemInformationBlobSynced = false flags it.
The 8 Engineering* tables capture finer-grained per-component telemetry beyond what SystemInformation stores.
SystemInformationVarianceCode flags units whose captured spec doesn’t match the expected SKU configuration. SystemVarianceCodeAssignment records the history.
DigitalProductKey links 1:1 to WorkTracking via AssignedSerialNumber once the key is injected.

🔧 Refactor finding: the 8 Engineering* tables and the SystemInformation* tables overlap. The TranslateEngineeringDataEntriesJob exists to migrate from one to the other but is disabled — see 14-jobs-and-integrations.md.

6. Cluster D — Triage / Grading

Quality assessment hierarchies and grading lookups.

D1 — Triage questionnaire

erDiagram
    TriageEvaluation }o--|| StationType : assigned-to
    TriageEvaluation ||--o{ TriageEvaluationCategory : has-categories
    TriageEvaluationCategory ||--o{ TriageEvaluationCategoryQuestion : has-questions
    TriageEvaluationCategoryQuestion }o--|| TriageEvaluationQuestion : "question text"
    TriageEvaluationQuestion ||--o{ TriageEvaluationOption : has-options
    TriageEvaluation ||--o{ TriageEvaluationGradesScore : "score → grade map"
    TriageEvaluation ||--o{ TriageEvaluationSubmission : submissions
    TriageEvaluationSubmission ||--o{ TriageErrorRecords : "errors → RC"
    TriageEvaluationOption ||--o{ TriageEvaluationErrorCodes : tied-error-codes

D2 — Grading + Condition

erDiagram
    GradingTypes }o--|| Projects : scoped-to
    GradingTypes ||--o{ SubGrading : sub-classes
    WorkTracking }o--o| GradingTypes : graded-as
    WorkTracking ||--o{ ItemConditionTest : condition-records
    ItemConditionTest }o--|| ItemCondition : selected
    ItemConditionTest }o--|| ItemVisualCondition : visual-state
    ItemConditionTest }o--|| ItemPackingCondition : packing-state
    WorkTracking ||--o{ AnswersHeaderTest : answer-headers
    AnswersHeaderTest ||--o{ AnswersTest : individual-answers
    AnswersTest }o--|| QuestionsTests : answers-a

Notes

Two parallel “questionnaire” systems exist: TriageEvaluation* (used by the modern Triage station) and QuestionsTests / AnswersTest / AnswersHeaderTest / TestByModel (⚠️ apparently older — confirm whether still active).
GradingTypes.GradingTextOnLabel is the short string that ends up on the printed label (e.g., “A”, “AS”, “I”).
SubGrading is optional refinement under a GradingTypes row.

🔧 Refactor finding: the legacy answers system (QuestionsTests, AnswersTest, AnswersHeaderTest, TestByModel) and the modern TriageEvaluation* system both exist. Confirm whether the legacy one is dead before refactor.

7. Cluster E — Pallets / Inventory Location

Physical container and warehouse-location entities.

erDiagram
    BasePallet <|-- InboundPallets : inherits
    BasePallet <|-- OutboundPallets : inherits
    BasePallet <|-- BoxPallet : inherits
    InboundPallets }o--|| WorkOrders : received-on
    InboundPallets }o--|| Projects : scoped
    InboundPallets }o--|| PalletStatus : status
    InboundPallets ||--o{ InboundPalletsLog : audit
    OutboundPallets }o--|| PalletPrefix : "name prefix"
    OutboundPallets ||--o{ OutboundPalletDetails : line-items
    OutboundPallets ||--o{ WorkTracking : units-on
    BoxPallet ||--o{ BoxPalletDetails : line-items
    BoxPallet ||--o{ WorkTracking : units-in-box
    StandByPallets }o--|| ProductionFlow : owned-by
    StandByPallets ||--o{ StandByPalletDetails : holds
    WareHouseLocations }o--|| Warehouse : in
    Warehouse ||--o{ WareHouseLocations : has
    InventoryTracker ||--o{ InventoryTransaction : ledger

Notes

BasePallet is the shared base for the three pallet kinds; same Warehouse + WareHouseLocation fields.
A BoxPallet is a sub-pallet inside an OutboundPallet for multi-box shipments (SF-220).
StandByPallets is a buffer/holding-area mechanism — isPallet distinguishes a real pallet from a flagged holding zone.

🔧 Refactor finding: OutboundPallets.WareHouseLocationID and OutboundPallets.WarehouseID have no [ForeignKey] attributes; the FK relationships are implicit. See 20-refactor-findings.md.

8. Cluster F — Repair Center / RMA

Returns processing and repair-station entities.

erDiagram
    RmaHeader }o--|| RmaTypes : "return reason"
    RmaHeader }o--|| Projects : scoped-to
    RmaHeader ||--o{ RmaItemsRelation : items
    RmaItemsRelation }o--|| WorkTracking : "FK to unit"
    RmaItemsRelation ||--o{ RmaImages : photos
    RmaNotInsystemPreAlert }o--|| RmaTypes : reason
    RepairCenter }o--|| Projects : scoped-to
    RepairCenter ||--o{ RepairCenterError : errors
    RepairCenter ||--o{ RCImages : photos
    RepairCenter ||--o{ RCNotes : notes
    RepairCenter ||--o{ RCReplacementParts : parts-used
    Quarantine }o--|| Projects : scoped-to
    Discrepancy }o--|| WorkOrders : "on WO"
    Discrepancy }o--|| BOMHeader : "of model"

Notes

RmaHeader ↔ RepairCenter have no FK between them. The linkage is implicit via string match on WorkTracking.SerialNumber == RepairCenter.SerialNumber. See 20-refactor-findings.md.
Discrepancy joins to other entities (WorkTracking, InboundPallets) by string columns (SerialNumber, BOLFileIdentifier, InboundPalletIdentifier) rather than FKs — major refactor target.
RmaNotInsystemPreAlert is a staging table for out-of-system RMA pre-alerts; rows are deleted after GenerateRMA() materializes them into WorkTracking + RmaHeader + RmaItemsRelation.

9. Cluster G — Parts / Procurement

Parts catalog, purchasing, harvesting, and consumption.

erDiagram
    KeypartsCategory ||--o{ KeypartsNumber : "category of"
    KeypartsCategory ||--o{ KeypartsErrorCodes : "error codes"
    KeypartsErrorCodeByProject }o--|| KeypartsErrorCodes : "per-project"
    KeypartsErrorCodeByProject }o--|| Projects : scoped
    Vendor ||--o{ PurchaseOrder : sells-to-us
    PurchaseOrder ||--o{ PurchaseOrderItem : line-items
    PurchaseOrder ||--o{ PurchaseOrderDocument : attachments
    PurchaseOrderItem }o--|| KeypartsNumber : "of part"
    PurchaseOrderItem ||--o{ Part : "creates Part rows on receipt"
    HarvestingEvent ||--o{ Part : "creates Part rows on harvest"
    Part }o--|| KeypartsNumber : "of part"
    Part ||--o| WorkTracking : "ConsumedByWorkTrackingID FK"
    Part ||--o| PartLocation : located-at
    ConsumptionRequest ||--o{ ConsumptionRequestItems : items
    PendingPartsCatalog }o--|| BOMHeader : "for model"
    PendingPartsCatalog }o--|| KeypartsNumber : "needs part"
    OemProduct }o--|| OemCategory : in
    OemProduct }o--o| BOMHeader : "links to model"
    OemProduct ||--o{ OemProductImage : images
    OemProduct ||--o{ OemProductSpec : specs
    Manufacturer ||--o{ ProductManufacturers : "per-project mfr config"

Notes

A Part is the physical inventory unit. Its Source field is "PO" (purchased) or "Harvesting" (cannibalized).
High-value parts get a unique PartSerialNumber formatted P{yyMMdd}A{00000} (purchased) or T{yyMMdd}A{00000} (harvested) — useful provenance.
Part.ConsumedByWorkTrackingID should mark a part as consumed in a repair, but the write path appears unwired — see Repair Center §6.6 in 02-unit-flow.md and 20-refactor-findings.md.
PendingPartsCatalog is misnamed — it’s a BOM-linked reference catalog, not a “parts to procure” list.

🔧 Refactor finding: RecoveryEvent is mostly parallel to HarvestingEvent but ties to WarehouseID instead of a unit. Distinction is unclear — see Repair Center §6.5 in unit-flow.

10. Cluster H — Kitting / Packing / Printing

Accessories, packing rules, and label printers.

erDiagram
    KittingBOM }o--|| BOMHeader : "for model"
    KittingBOM }o--|| KeypartsNumber : "of accessory"
    WorkTracking ||--o{ KittingLog : "scanned-at-kitting"
    KittingLog }o--|| KeypartsNumber : "scanned part"
    PackingConfig }o--|| ProductionFlow : "per flow"
    PrinterConfig }o--|| Projects : "per project"
    PrinterConfig ||--o{ StationPrinterConfig : "assigned to"
    StationPrinterConfig }o--|| Station : "at station"
    PrinterTest }o--|| WorkTracking : "test-run-against"
    SKUPrintersCatalog }o--|| PrinterConfig : "uses-printer"

Notes

KittingBOM defines required accessories per model.
KittingLog records each scanned accessory at the Kitting station per unit.
PrinterConfig / StationPrinterConfig / SKUPrintersCatalog together control which physical label printer fires for which SKU at which station.

11. Cluster I — Identity / Permissions

ASP.NET Core Identity with role-based authorization layered on top.

erDiagram
    AppUser ||--o{ AppUserRole : has-roles
    AppRole ||--o{ AppUserRole : assigned-to
    AppRole ||--o{ RolePermissions : grants
    Permissions ||--o{ RolePermissions : granted-via
    AppUser ||--o{ UserGlobalPermissions : "user-level grants"
    GlobalPermissions ||--o{ UserGlobalPermissions : granted-as
    AppUser }o--|| TenantUsersRelation : "user ↔ tenant"
    ExternalApiUser : "API key auth"
    SecurablePlatforms : "which areas need auth"

This is a stock ASP.NET Core Identity model extended with tenant-aware joins (TenantUsersRelation) and project-scoped permission lookups via the vwRolePermissions view.

12. Cluster J — Reference / Catalog

Lookup tables and supporting reference data.

Entity	Purpose
`Countries`, `State`	Geographic catalogs.
`Address`, `EntityAddress`	Address records (polymorphic via `EntityAddress`).
`ProductEOL`, `ProductEOLModels`	End-of-life tracking — feeds the `NotifyUsersOfProductEOLJob`.
`PriorityModels`	Per-project, per-model priority flag.
`ModelsForProduction`	Per-project models that are currently in production.
`ProgramType`	Project-scoped program classifications.
`Notes`, `Images`, `Versions`	Global attachment / image / version-tracking helpers (referenced from many other entities).
`Facility`, `Warehouse`, `Warehouses`, `WareHouseLocations`	Facility/warehouse hierarchy. ⚠️ `Warehouse` and `Warehouses` are both entities — likely a duplicate/legacy.
`LocationTypes`	Location classification (bin / shelf / rack).

🔧 Refactor finding: Warehouse.cs vs. Warehouses.cs — duplicate entities. Confirm which is canonical.

13. Cluster K — Reporting (DB Views)

vw*-prefixed entities are SQL Server views, not tables. They exist as EF entities only so the codebase can FindByCondition against them. Don’t draw ERDs — list them.

View	Purpose
`vwBERReport`	BER unit report.
`vwBinLocationReport`	Bin location occupancy.
`vwCTOInventoryReport`	Configure-to-order inventory.
`vwCondensedGrading`	Condensed grade rollup.
`vwDashboardCurrenStatus` (sic) / `vwDashboardHistoryStatus`	Dashboard summaries.
`vwFGReport`	Finished-good report.
`vwFalloutReport`	Fallout (rejection) report.
`vwGetStationPath`	Station-by-station path per unit.
`vwInboundPalletReceiving`	Inbound pallet receipt rollup.
`vwInboundShipments`	Inbound shipment rollup.
`vwInventory` (Physical, Reserved, FG, Inbound, Repair, WIP) and their `Info` variants	Inventory snapshots by state.
`vwInventoryReport`	Aggregate inventory report.
`vwPalletReport`, `vwRmaItemImages`, `vwRepairCenter`	RMA / pallet / repair rollups.
`vwProjects`	Project metadata rollup.
`vwReceivingReconciliationByModelReport`, `vwReceivingReconciliationReport`	Receiving reconciliation.
`vwRolePermissions`, `vwRolesByUser`, `vwRolesToAssign`	Security flattening views.
`vwSettlement`	Settlement / financial summary.
`vwSpecificStationActivityReport`	Per-station activity.
`vwStatusByWO`	Status grouped by work order.
`vwTestFileResults`	Test file results rollup.
`vwWorkOrdersByModel`, `vwWorkTracking`, `vwWorkTrackingRMA`	Work tracking rollups.

Most are read-only feeds for Syncfusion EJ2 grids and reports.

14. Cluster L — Sales / Fulfillment

Outbound orders, sales import, and internal transfers.

erDiagram
    SalesOrders }o--|| Projects : scoped
    SalesOrders }o--o| WorkTracking : "⚠️ string-FK orphaned"
    ProductSalesOrder ||--o{ ProductSalesOrderLineItem : items
    ProductSalesOrderLineItem ||--o{ ProductSalesOrderLineItemDetail : details
    SalesImport }o--o| WorkTracking : "matched-on serial"
    SalesImport }o--|| AppUser : created-by
    SoldFulfilledOrScrapedLog }o--|| WorkTracking : disposition
    OrderRequest }o--|| Projects : scoped
    InternalTransfer ||--o{ InternalTransferPallet : pallets
    InternalTransferPallet ||--o{ InternalTransferSerial : units
    InternalTransferSerial }o--|| WorkTracking : "transfers unit"
    AsIsShipmentsRoutes }o--|| Projects : scoped

Notes

SalesImport is the staging table for marketplace sales (Mercado Libre, Fishbowl, Channel Advisor). SalesInventoryUpdateJob matches its rows back to WorkTracking by serial.
ProductSalesOrder / line-items / line-item-details are the canonical sales-order tree.
InternalTransferSerial lets WorkTracking compute its original BOL identifier via the calculated OriginalWorkOrderID / OriginalBolIdentifier properties on WorkTracking itself.

🔧 Refactor finding: SalesOrders.WorkTrackingID has no [ForeignKey] attribute. Treat as a real FK and add it.

`CustomSKU` and `CustomSKUMapping`

Recent commits (976e5dfd wip: initial custom sku mapping setup, d6ac84e1 new custom sku mapping table) introduce custom-SKU handling:

CustomSKU.cs in Core.Domain — a value object (not an entity) that parses a full SKU string into components: FullSKU, BaseSKU, Grading, Region, Facility, Marketplace, Oem, VarianceCode.
CustomSKUMapping.cs in Core.Domain.Entities — a lookup table that maps an external CustomSKU string to an internal BOMHeaderID. Fields: BOMHeaderID (FK), FullInternalSKU, CustomSKU, ExternalReference, SKUSource.

Purpose: replace brittle string-parsing logic that previously decided which BOMHeader an incoming SKU belonged to. Multiple incoming SKU formats from different partners can now all map to the same internal BOM.

15. Cluster M — Pre-Alert / BOL

Inbound shipping documents and pre-alert validation.

erDiagram
    PrealertBol ||--o{ PreAlertBolDetails : line-items
    PrealertBol }o--|| BOMHeader : "expects model"
    PrealertBol }o--|| Projects : scoped
    KtyPrealertBol ||--o{ KtyPreAlertBolDetails : line-items
    TempPrealertBol ||--o{ TempPreAlertBolDetails : "staging"
    WorkOrderBol }o--|| WorkOrders : "links WO to BOM"
    WorkOrderBol }o--|| BOMHeader : "via WorkOrderBol"
    WorkOrdersBlobs : "uploaded files"
    PreAlertSkusPresenceToNotify }o--|| Projects : scoped
    PreAlertSkusPresenceToNotify ||--o{ PreAlertSkusPresenceToNotifyAlertRecipients : recipients
    SKUsInPrealertToNotify }o--|| Projects : scoped
    NewBomHeaderItemEmailAlertRecipients }o--|| Projects : scoped

Notes

Three parallel pre-alert spines exist: standard (PrealertBol), Kty-specific (KtyPrealertBol), and temp/staging (TempPrealertBol). ⚠️ Confirm which are still active.
The *ToNotify and NewBomHeaderItemEmailAlertRecipients entities feed the email notification system on pre-alert events (new SKU detected, etc.).
WorkOrdersBlobs stores raw uploaded files (Excel pre-alerts, supporting docs).

16. Cross-cluster bridges

These entities span multiple clusters. Keep them in mind when refactoring — touching them ripples broadly.

Bridge entity	Connects	Why it matters
`WorkTracking`	A ↔ B ↔ C ↔ D ↔ E ↔ F ↔ H ↔ L ↔ M	Central state — touched by every operational flow.
`BOMHeader`	A ↔ G ↔ H ↔ M	Identifies the product family used in BOMs, kits, pre-alerts, OEM lookups.
`Projects`	A, B, D, E, F, J, L, M	Scoping dimension — almost every entity has a `ProjectID`.
`Station` / `StationType`	B ↔ D ↔ H	Drives routing, triage assignment, and printer config.
`Part.ConsumedByWorkTrackingID`	G ↔ A	The (incomplete) consumption link between parts and the unit they were used on.
`SalesImport.WorkTrackingID`	L ↔ A	Sales reconciliation hands off the unit’s disposition.

17. Data-quality findings

Cross-references to 20-refactor-findings.md.

String columns that should be foreign keys

Entity.Column	Should reference	Risk
`Discrepancy.SerialNumber`	`WorkTracking.SerialNumber`	No referential integrity; can orphan.
`Discrepancy.BOLFileIdentifier`	`WorkOrders.BOLFileIdentifier`	Same.
`Discrepancy.InboundPalletIdentifier`	`InboundPallets.InboundPalletIdentifier`	Same.
`WorkTracking.VendorID` (string)	`Vendor`	Inconsistent type — Vendor PKs are Guid elsewhere.
`RepairCenter.SerialNumber`	`WorkTracking.SerialNumber`	Implicit string-match join; no FK.
`RCReplacementParts.HarvastingSN` (sic)	`WorkTracking.SerialNumber` (donor)	Misspelled too.

Missing `[ForeignKey]` attributes

Entity.Column	Implied target
`OutboundPallets.WareHouseLocationID`	`WareHouseLocations`
`OutboundPallets.WarehouseID`	`Warehouse`
`SalesOrders.WorkTrackingID`	`WorkTracking`

Likely-missing indexes

Column	Why
`WorkTracking.SerialNumber`	Hub of every string-match join above.
`WorkTracking.SKU` / `ArrivedSKU` / `LabelSKU`	High-cardinality, queried frequently in reports.
`InboundPallets.InboundPalletIdentifier`, `OutboundPallets.OutboundPalletIdentifier`	Pallet lookups.
`RepairCenter.SerialNumber`	Repair-history lookups.

Duplicate / overlapping entities

Entities	Concern
`Warehouse.cs` and `Warehouses.cs`	Two entities with near-identical names. Confirm which is canonical.
`SystemInformation` vs. `Engineering`	Two parallel spec hierarchies. `TranslateEngineeringDataEntriesJob` is disabled — migration incomplete.
Legacy `QuestionsTests` / `AnswersTest` vs. modern `TriageEvaluation*`	Two questionnaire models. Confirm legacy is dead.
`Notification` vs. `AlertEmailRecipients` / `AlertTypes`	Two notification mechanisms — confirm both are still wired.

18. Reading on from here

For what each station does to a unit → 12-station-catalog.md
For how the route engine executes movements → 13-workflow-engine.md
For what the jobs do → 14-jobs-and-integrations.md
For what to fix first → 20-refactor-findings.md

ShopFloor - Data Model

Data Model

1. How to read this doc

2. The central hub: WorkTracking

3. Cluster A — Unit Tracking Core

Key entities

4. Cluster B — Workflow / Routing

Key entities

5. Cluster C — System / Spec Capture

Notes

6. Cluster D — Triage / Grading

D1 — Triage questionnaire

D2 — Grading + Condition

Notes

7. Cluster E — Pallets / Inventory Location

Notes

8. Cluster F — Repair Center / RMA

Notes

9. Cluster G — Parts / Procurement

Notes

10. Cluster H — Kitting / Packing / Printing

Notes

11. Cluster I — Identity / Permissions

12. Cluster J — Reference / Catalog

13. Cluster K — Reporting (DB Views)

14. Cluster L — Sales / Fulfillment

Notes

CustomSKU and CustomSKUMapping

15. Cluster M — Pre-Alert / BOL

Notes

16. Cross-cluster bridges

17. Data-quality findings

String columns that should be foreign keys

Missing [ForeignKey] attributes

Likely-missing indexes

Duplicate / overlapping entities

18. Reading on from here

2. The central hub: `WorkTracking`

`CustomSKU` and `CustomSKUMapping`

Missing `[ForeignKey]` attributes