fsds/shipment-lifecycle-and-return-tracking-enhancement.md

Shipment Lifecycle and Return Tracking Enhancement

Problem Summary

Bug: ReturnDoorProcessor marks entire shipment as RETURNED when any single basket returns
No active shipment visibility: Dashboard shows STALE/PENDING/RETURNED but no "in transit" state
No return percentage tracking: Can't answer "what % of returnable assets came back?"
Non-returnables clutter tracking: Products and virtual stacks shouldn't count toward returns

Implementation Phases

Phase 1: Core Reference Data - ItemLifecycleDefinition

New Entity: item_lifecycle_definitions in core schema (global reference data)

ID	Code	Name	ExpectReturn
1	CONSUMABLE	Consumable	false
2	RTI	Returnable Transport Item	true
3	POOLED	Pooled Asset	true
4	VIRTUAL	Virtual/Logical	false

Files to Create:

engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Database/ItemLifecycleDefinition.cs
engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Database/Seeding/ReferenceData/ItemLifecycleDefinitions.sql

Files to Modify:

engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Database/CoreDataDb.cs - Add DbSet
engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Abstractions/Primitives/PTIDS.cs - Add ITEM_LIFECYCLE_DEFINITION = 50
engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Database/Seeding/CoreDataReferenceDataSeeder.cs - Add seeding

Migration: AddItemLifecycleDefinitions

Phase 2: Catalog - Link ItemCategory to LifecycleDefinition

Files to Modify:

engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemCategory.cs
- Add LifecycleDefinitionId (short?, Order = 7)
engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/CatalogDb.cs
- Configure external reference to core.item_lifecycle_definitions

Migration: AddLifecycleDefinitionToItemCategory

Add column
Seed existing data:
- "Storage Containers" → RTI (2)
- "Physical Grouping" → VIRTUAL (4)
- "BBU Items" → CONSUMABLE (1)

Phase 3: Transport - Return Tracking Fields + Views

Files to Modify:

engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/ShipmentItemAllocation.cs
- Add LifecycleDefinitionId (short?) - cached from item's category
- Add ExpectReturn (bool) - cached for query performance

NO stored columns for counts/percentages - use calculated views instead.

Migration: AddReturnTrackingAndViews

Add lifecycle_definition_id and expect_return to allocations table
Backfill existing allocations from item → itemType → itemCategory → lifecycleDefinition
Create database VIEWs for return calculations:

-- Shipment-level return summary
CREATE VIEW transport.shipment_return_summary AS
SELECT
    s.id AS shipment_id,
    s.tracking_number,
    s.status,
    COUNT(*) FILTER (WHERE a.expect_return = true) AS total_returnable,
    COUNT(*) FILTER (WHERE a.expect_return = true AND a.status = 'RECEIVED') AS received_returnable,
    CASE
        WHEN COUNT(*) FILTER (WHERE a.expect_return = true) > 0
        THEN ROUND(
            COUNT(*) FILTER (WHERE a.expect_return = true AND a.status = 'RECEIVED')::numeric /
            COUNT(*) FILTER (WHERE a.expect_return = true) * 100, 2
        )
        ELSE NULL
    END AS return_percentage
FROM transport.shipments s
LEFT JOIN transport.shipment_item_allocations a ON a.shipment_id = s.id
GROUP BY s.id, s.tracking_number, s.status;

-- Delivery-level return summary (for "where did baskets go missing?" analysis)
CREATE VIEW transport.delivery_return_summary AS
SELECT
    d.id AS delivery_id,
    d.shipment_id,
    d.location_id,
    d.status,
    COUNT(*) FILTER (WHERE a.expect_return = true) AS total_returnable,
    COUNT(*) FILTER (WHERE a.expect_return = true AND a.status = 'RECEIVED') AS received_returnable,
    CASE
        WHEN COUNT(*) FILTER (WHERE a.expect_return = true) > 0
        THEN ROUND(
            COUNT(*) FILTER (WHERE a.expect_return = true AND a.status = 'RECEIVED')::numeric /
            COUNT(*) FILTER (WHERE a.expect_return = true) * 100, 2
        )
        ELSE NULL
    END AS return_percentage
FROM transport.deliveries d
LEFT JOIN transport.shipment_item_allocations a ON a.delivery_id = d.id
GROUP BY d.id, d.shipment_id, d.location_id, d.status;

These views can be:

Queried directly by Superset dashboards
Used by API layer for shipment/delivery responses
Always accurate without sync/recalculation burden

Phase 4: Fix ReturnDoorProcessor Bug

File: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/ReturnDoorProcessor.cs

Current Bug (lines 417-426):

// BUG: Sets entire shipment to RETURNED on ANY basket return
allocation.Shipment.Status = "RETURNED";

Fixed Logic:

Mark only the specific allocation as RECEIVED
Count all returnable allocations for the shipment
Count received returnable allocations
Calculate return percentage
Update shipment status based on counts:
- 0 received → stay DELIVERED
- Some received → PARTIALLY_RETURNED
- All received → COMPLETED
Do the same calculation per-delivery for delivery-level tracking

Phase 5: Non-Returnable Item Handling

File: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/ShipmentLifecycleProcessor.cs

Add new method: ProcessNonReturnableItemsAsync

When shipment reaches DELIVERED:
- Stack allocations (VIRTUAL) → status = DISSOLVED
- Product allocations (CONSUMABLE) → status = CONSUMED

Call in ExecuteAsync after AdvanceCompletedShipmentsAsync

Phase 6: Status Enum Updates

File: engines/core-data/src/Acsis.Dynaplex.Engines.CoreData.Abstractions/Primitives/ShipmentStatus.cs

Ensure these statuses exist:

PENDING - waiting for basket match
IN_TRANSIT - baskets matched, shipment active
DELIVERED - arrived at destination
PARTIALLY_RETURNED - some returnable items received (NEW)
COMPLETED - all returnable items received (NEW)
MISSING - grace period expired with missing items (renamed from CLOSED_MISSING)
STALE - never matched
RETURNED - keep for backward compatibility

Data migration: Update existing CLOSED_MISSING values to MISSING in shipments/deliveries tables.

Migration Execution Order

CRITICAL - Must run in this order:

core-data migration (creates reference table)
catalog migration (adds FK to reference table)
transport migration (adds tracking fields, backfills data)

Key Files Summary

Component	File	Changes
core-data	`ItemLifecycleDefinition.cs`	NEW - entity
core-data	`CoreDataDb.cs`	Add DbSet
core-data	`PTIDS.cs`	Add constant (50)
core-data	`ItemLifecycleDefinitions.sql`	NEW - seed data
catalog	`ItemCategory.cs`	Add LifecycleDefinitionId
catalog	`CatalogDb.cs`	External reference config
transport	`ShipmentItemAllocation.cs`	Add lifecycle cache fields
transport	Migration	Create shipment_return_summary + delivery_return_summary VIEWS
bbu	`ReturnDoorProcessor.cs`	FIX BUG - partial return logic
bbu	`ShipmentLifecycleProcessor.cs`	Add non-returnable handling, rename CLOSED_MISSING→MISSING

Data Migration SQL (in transport migration)

-- Backfill allocation lifecycle data
UPDATE transport.shipment_item_allocations sia
SET
    lifecycle_definition_id = ic.lifecycle_definition_id,
    expect_return = ild.expect_return
FROM catalog.items i
JOIN catalog.item_types it ON i.item_type_id = it.id
JOIN catalog.item_categories ic ON it.item_category = ic.id
JOIN core.item_lifecycle_definitions ild ON ic.lifecycle_definition_id = ild.id
WHERE sia.item_id = i.id;

-- Rename CLOSED_MISSING → MISSING
UPDATE transport.shipments SET status = 'MISSING' WHERE status = 'CLOSED_MISSING';
UPDATE transport.deliveries SET status = 'MISSING' WHERE status = 'CLOSED_MISSING';

(No stored return counts/percentages - the views calculate these on-demand)

Testing Checklist

Reference data seeds correctly (4 lifecycle definitions)
Existing item categories get correct lifecycle assignments
Single basket return → shipment becomes PARTIALLY_RETURNED (not RETURNED)
All baskets return → shipment becomes COMPLETED
Database views calculate return percentages correctly
Non-returnables marked CONSUMED/DISSOLVED when shipment reaches DELIVERED
Delivery-level return summary view works for "where did baskets go missing?" analysis
Existing CLOSED_MISSING data migrated to MISSING
Superset dashboards can query the new views directly

Documentation

Shipment Lifecycle and Return Tracking Enhancement

Problem Summary

Implementation Phases

Phase 1: Core Reference Data - ItemLifecycleDefinition

Phase 2: Catalog - Link ItemCategory to LifecycleDefinition

Phase 3: Transport - Return Tracking Fields + Views

Phase 4: Fix ReturnDoorProcessor Bug

Phase 5: Non-Returnable Item Handling

Phase 6: Status Enum Updates

Migration Execution Order

Key Files Summary

Data Migration SQL (in transport migration)

Testing Checklist