Documentation

fsds/shipment-type-and-shipment-status-normalization.md

Normalize ShipmentType and ShipmentStatus in Transport Component

Problem Summary

The Transport component uses raw strings for shipment_type and status columns in Shipment, Delivery, and ShipmentItemAllocation entities. This should be normalized into proper reference tables with foreign key relationships.

Current State

Entity Column Current Type Current Values
Shipment shipment_type varchar(32) "Asset", "Move", "Volume"
Shipment status varchar(64) "DRAFT", "SHIPPED", "IN_TRANSIT", etc.
Delivery status varchar(64) Same status values
ShipmentItemAllocation status varchar(64) Same status values

Target State

Two new reference tables following the short ID pattern (like MovementType/MovementState):

  • transport.shipment_types - Types of shipments (Asset, Move, Volume)
  • transport.shipment_statuses - Shared status values for all three entities

Files to Create

New Entity Files

  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/ShipmentTypeRef.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/ShipmentStatusRef.cs

New Seeding Files

  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/Seeding/TransportReferenceDataSeeder.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/Seeding/ReferenceData/ShipmentTypes.sql
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/Seeding/ReferenceData/ShipmentStatuses.sql

Files to Modify

  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/Shipment.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/Delivery.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/ShipmentItemAllocation.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport.Database/TransportDb.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport/Program.cs (register seeder)

Implementation Steps

Step 1: Create ShipmentTypeRef Entity

// ShipmentTypeRef.cs
[Table("shipment_types")]
[NoReorder]
public class ShipmentTypeRef
{
    public const string TABLE_NAME = "shipment_types";

    // Deterministic IDs
    public const short ASSET_ID = 1;
    public const short MOVE_ID = 2;
    public const short VOLUME_ID = 3;

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("id", Order = 1)]
    public short Id { get; set; }

    [Required]
    [StringLength(32)]
    [Column("name", Order = 2)]
    public string Name { get; set; } = null!;

    [StringLength(256)]
    [Column("description", Order = 3)]
    public string? Description { get; set; }

    // Navigation
    [JsonIgnore]
    [InverseProperty(nameof(Shipment.ShipmentTypeRef))]
    public virtual ICollection<Shipment> Shipments { get; set; } = new List<Shipment>();
}

Step 2: Create ShipmentStatusRef Entity

// ShipmentStatusRef.cs
[Table("shipment_statuses")]
[NoReorder]
public class ShipmentStatusRef
{
    public const string TABLE_NAME = "shipment_statuses";

    // Deterministic IDs
    public const short DRAFT_ID = 1;
    public const short SUBMITTED_ID = 2;
    public const short IN_TRANSIT_ID = 3;
    public const short DELIVERED_ID = 4;
    public const short RECEIVED_ID = 5;
    public const short COMPLETED_ID = 6;
    public const short CANCELLED_ID = 7;
    public const short MISSING_ID = 8;
    public const short PARTIALLY_RECEIVED_ID = 9;
    public const short ALLOCATED_ID = 10;

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("id", Order = 1)]
    public short Id { get; set; }

    [Required]
    [StringLength(64)]
    [Column("code", Order = 2)]
    public string Code { get; set; } = null!;  // e.g., "IN_TRANSIT"

    [Required]
    [StringLength(64)]
    [Column("name", Order = 3)]
    public string Name { get; set; } = null!;  // e.g., "In Transit"

    [StringLength(256)]
    [Column("description", Order = 4)]
    public string? Description { get; set; }

    // Navigation properties
    [JsonIgnore]
    [InverseProperty(nameof(Shipment.StatusRef))]
    public virtual ICollection<Shipment> Shipments { get; set; } = new List<Shipment>();

    [JsonIgnore]
    [InverseProperty(nameof(Delivery.StatusRef))]
    public virtual ICollection<Delivery> Deliveries { get; set; } = new List<Delivery>();

    [JsonIgnore]
    [InverseProperty(nameof(ShipmentItemAllocation.StatusRef))]
    public virtual ICollection<ShipmentItemAllocation> Allocations { get; set; } = new List<ShipmentItemAllocation>();
}

Step 3: Update Shipment Entity

Add new FK columns alongside existing string columns (for migration compatibility):

// Add to Shipment.cs

[Column("shipment_type_id", Order = 5)]  // New column
public short ShipmentTypeId { get; set; }

[Column("status_id", Order = 7)]  // New column
public short StatusId { get; set; }

// Navigation properties
[JsonIgnore]
[ForeignKey(nameof(ShipmentTypeId))]
public virtual ShipmentTypeRef ShipmentTypeRef { get; set; } = null!;

[JsonIgnore]
[ForeignKey(nameof(StatusId))]
public virtual ShipmentStatusRef StatusRef { get; set; } = null!;

Step 4: Update Delivery Entity

// Add to Delivery.cs

[Column("status_id", Order = X)]  // New column
public short? StatusId { get; set; }  // Nullable because current status is nullable

[JsonIgnore]
[ForeignKey(nameof(StatusId))]
public virtual ShipmentStatusRef? StatusRef { get; set; }

Step 5: Update ShipmentItemAllocation Entity

// Add to ShipmentItemAllocation.cs

[Column("status_id", Order = X)]  // New column
public short StatusId { get; set; }

[JsonIgnore]
[ForeignKey(nameof(StatusId))]
public virtual ShipmentStatusRef StatusRef { get; set; } = null!;

Step 6: Update TransportDb

// Add to TransportDb.cs
public DbSet<ShipmentTypeRef> ShipmentTypes => Set<ShipmentTypeRef>();
public DbSet<ShipmentStatusRef> ShipmentStatuses => Set<ShipmentStatusRef>();

Step 7: Create Seeding SQL Files

ShipmentTypes.sql:

INSERT INTO transport.shipment_types (id, name, description)
VALUES
    (1, 'Asset', 'Individual tracked assets'),
    (2, 'Move', 'Internal transfers/relocations'),
    (3, 'Volume', 'Bulk quantities')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, description = EXCLUDED.description;

ShipmentStatuses.sql:

INSERT INTO transport.shipment_statuses (id, code, name, description)
VALUES
    (1, 'DRAFT', 'Draft', 'Shipment is being prepared'),
    (2, 'SUBMITTED', 'Submitted', 'Shipment has been submitted'),
    (3, 'IN_TRANSIT', 'In Transit', 'Shipment is in transit'),
    (4, 'DELIVERED', 'Delivered', 'Shipment has been delivered'),
    (5, 'RECEIVED', 'Received', 'Shipment has been received'),
    (6, 'COMPLETED', 'Completed', 'Shipment lifecycle is complete'),
    (7, 'CANCELLED', 'Cancelled', 'Shipment was cancelled'),
    (8, 'MISSING', 'Missing', 'Shipment or items are missing'),
    (9, 'PARTIALLY_RECEIVED', 'Partially Received', 'Some items received'),
    (10, 'ALLOCATED', 'Allocated', 'Items allocated to shipment')
ON CONFLICT (id) DO UPDATE
SET code = EXCLUDED.code, name = EXCLUDED.name, description = EXCLUDED.description;

Step 8: Create Migration

The migration must:

  1. Create the reference tables first
  2. Seed the reference data
  3. Add the new FK columns
  4. Populate FK values from existing string columns
  5. Add foreign key constraints
  6. (Optional) Drop old string columns in a later migration
-- Migration SQL (conceptual)

-- 1. Create reference tables
CREATE TABLE transport.shipment_types (...);
CREATE TABLE transport.shipment_statuses (...);

-- 2. Seed data (via seeder or inline)

-- 3. Add new columns
ALTER TABLE transport.shipments ADD COLUMN shipment_type_id smallint;
ALTER TABLE transport.shipments ADD COLUMN status_id smallint;

-- 4. Populate from existing data
UPDATE transport.shipments SET shipment_type_id =
    CASE shipment_type
        WHEN 'Asset' THEN 1
        WHEN 'Move' THEN 2
        WHEN 'Volume' THEN 3
    END;

UPDATE transport.shipments SET status_id =
    CASE status
        WHEN 'DRAFT' THEN 1
        WHEN 'SUBMITTED' THEN 2
        WHEN 'IN_TRANSIT' THEN 3
        -- etc.
    END;

-- 5. Add NOT NULL and FK constraints
ALTER TABLE transport.shipments ALTER COLUMN shipment_type_id SET NOT NULL;
ALTER TABLE transport.shipments ADD CONSTRAINT FK_shipments_shipment_type
    FOREIGN KEY (shipment_type_id) REFERENCES transport.shipment_types(id);

Step 9: Update Code References

Update all places that set ShipmentType = "Asset" to use ShipmentTypeId = ShipmentTypeRef.ASSET_ID:

Key files to update:

  • engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/OracleProcessor.cs
  • engines/bbu/src/Acsis.Dynaplex.Engines.Bbu/Services/UnknownDestinationProcessor.cs
  • engines/transport/src/Acsis.Dynaplex.Engines.Transport/TransportApi.cs

Step 10: Backward Compatibility (Optional)

Keep the old string properties as computed/unmapped for API compatibility:

[NotMapped]
[JsonPropertyName("shipmentType")]
public string ShipmentType => ShipmentTypeRef?.Name ?? "";

[NotMapped]
[JsonPropertyName("status")]
public string Status => StatusRef?.Code ?? "";

Migration Strategy

Phase 1 (This PR):

  1. Create reference tables and entities
  2. Add new FK columns (nullable initially)
  3. Populate FK values from string columns
  4. Make FK columns NOT NULL
  5. Add FK constraints
  6. Keep old string columns for now (read-only)

Phase 2 (Future PR):

  1. Remove old string columns after all code is updated
  2. Update all API contracts

Testing Verification

  1. Run existing tests - they should still pass with backward-compatible properties
  2. Verify new FK columns are populated correctly
  3. Verify seeding works on fresh database
  4. Verify joins work with new navigation properties