Documentation

fsds/catalog-item-status-scoping-and-gates.md

Item Status Scoping & Gates Implementation Plan

Overview

Implement enforcement of item status restrictions based on category/type and add status transition gates.

Key Decisions:

  • Scope levels are mutually exclusive: Global, Category (multiple), or ItemType (multiple)
  • Use collection_level values from prism.platform_types (0=Global, 10=Category, 20=Type)
  • Single junction table item_status_scopes with FK to prism.passports for polymorphic entity references
  • Migrate existing junction table data, then remove old tables
  • Status gates are permissive by default (no gates = all transitions allowed)

Stage 1: Database Schema Changes

1.1 Update ItemStatus Entity

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemStatus.cs

Add column:

  • scope_level (smallint, default 0)
    • 0 = Global (applies to any item)
    • 10 = Category level (matches platform_types.collection_level for ITEM_CATEGORY)
    • 20 = Type level (matches platform_types.collection_level for ITEM_TYPE)

Add navigation properties:

  • Scopes (collection of ItemStatusScope)
  • OutgoingGates / IncomingGates (collections for status gates)

1.2 Create ItemStatusScope Entity (replaces both old junction tables)

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemStatusScope.cs

[Table("item_status_scopes")]
[PrimaryKey(nameof(ItemStatusId), nameof(ScopePassportId))]
[Index(nameof(ScopePassportId))]
public class ItemStatusScope
{
    public const string TABLE_NAME = "item_status_scopes";

    [Column("item_status_id", Order = 0)]
    [Required]
    public Guid ItemStatusId { get; set; }

    /// <summary>
    /// FK to prism.passports.global_id - can reference ItemCategory or ItemType
    /// </summary>
    [Column("scope_passport_id", Order = 1)]
    [Required]
    public Guid ScopePassportId { get; set; }

    // Navigation
    [ForeignKey(nameof(ItemStatusId))]
    public virtual ItemStatus ItemStatus { get; set; } = null!;

    // Note: FK to prism.passports configured as external reference
}

1.3 Update ItemStatusGate Entity

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/ItemStatusGate.cs

Current entity exists but needs:

  • Add [PrimaryKey(nameof(CurrentItemStatusId), nameof(CanUpdateToItemStatusId))]
  • Add TenantId column (currently missing)
  • Add InverseProperty attributes for navigation properties
  • Add indexes

1.4 Update CatalogDb

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/CatalogDb.cs

  • Add DbSet<ItemStatusScope>
  • Add DbSet<ItemStatusGate>
  • Configure external reference to prism.passports for ScopePassportId
  • Configure cascade delete behaviors

1.5 Create Journal Entities (in Prism schema)

Journal tables follow ADR 035 - all journal tables reside in the prism schema.

File: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/ItemStatusLog.cs

[Table(TABLE_NAME, Schema = "prism")]
[NoReorder]
public class ItemStatusLog : JournalTable
{
    public const string TABLE_NAME = "catalog_item_statuses_journal";

    [Column("id", Order = 0)]
    public Guid Id { get; set; }

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

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

    [Column("reason_code_required", Order = 3)]
    public bool ReasonCodeRequired { get; set; }

    [Column("destroy_date_required", Order = 4)]
    public bool DestroyDateRequired { get; set; }

    [Column("point_of_use_prohibited", Order = 5)]
    public bool PointOfUseProhibited { get; set; }

    [Column("indicates_expiration", Order = 6)]
    public bool IndicatesExpiration { get; set; }

    [Column("scope_level", Order = 7)]
    public short ScopeLevel { get; set; }

    [Column("tenant_id", Order = 8)]
    public Guid TenantId { get; set; }

    [Column("platform_type_id", Order = 9)]
    public short PlatformTypeId { get; set; }
}

File: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/ItemStatusScopeLog.cs

[Table(TABLE_NAME, Schema = "prism")]
[NoReorder]
public class ItemStatusScopeLog : JournalTable
{
    public const string TABLE_NAME = "catalog_item_status_scopes_journal";

    [Column("item_status_id", Order = 0)]
    public Guid ItemStatusId { get; set; }

    [Column("scope_passport_id", Order = 1)]
    public Guid ScopePassportId { get; set; }
}

File: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/ItemStatusGateLog.cs

[Table(TABLE_NAME, Schema = "prism")]
[NoReorder]
public class ItemStatusGateLog : JournalTable
{
    public const string TABLE_NAME = "catalog_item_status_gates_journal";

    [Column("current_item_status_id", Order = 0)]
    public Guid CurrentItemStatusId { get; set; }

    [Column("can_update_to_item_status_id", Order = 1)]
    public Guid CanUpdateToItemStatusId { get; set; }

    [Column("tenant_id", Order = 2)]
    public Guid TenantId { get; set; }
}

1.6 Update PrismDb for Journal Tables

File: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/PrismDb.cs

Add DbSets:

public virtual DbSet<ItemStatusLog> ItemStatusesJournal { get; set; }
public virtual DbSet<ItemStatusScopeLog> ItemStatusScopesJournal { get; set; }
public virtual DbSet<ItemStatusGateLog> ItemStatusGatesJournal { get; set; }

File: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/PrismDbModelConfiguration.cs

Add composite key configuration:

modelBuilder.Entity<ItemStatusLog>().HasKey(l => new { l.Id, l.SeqId });
modelBuilder.Entity<ItemStatusScopeLog>().HasKey(l => new { l.ItemStatusId, l.ScopePassportId, l.SeqId });
modelBuilder.Entity<ItemStatusGateLog>().HasKey(l => new { l.CurrentItemStatusId, l.CanUpdateToItemStatusId, l.SeqId });

1.7 Update CatalogDb for Journal Triggers

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/CatalogDb.cs

Configure HasJournal() on source entities:

modelBuilder.Entity<ItemStatus>().HasJournal();
modelBuilder.Entity<ItemStatusScope>().HasJournal();
modelBuilder.Entity<ItemStatusGate>().HasJournal();

1.8 Create Migrations

Migration 1: AddItemStatusScopingAndGates

-- Add scope_level to item_statuses
ALTER TABLE catalog.item_statuses
ADD COLUMN scope_level smallint NOT NULL DEFAULT 0;

-- Create item_status_scopes table
CREATE TABLE catalog.item_status_scopes (
    item_status_id uuid NOT NULL,
    scope_passport_id uuid NOT NULL,
    PRIMARY KEY (item_status_id, scope_passport_id),
    FOREIGN KEY (item_status_id) REFERENCES catalog.item_statuses(id) ON DELETE CASCADE,
    FOREIGN KEY (scope_passport_id) REFERENCES prism.passports(global_id) ON DELETE CASCADE
);
CREATE INDEX IX_item_status_scopes_scope_passport_id ON catalog.item_status_scopes(scope_passport_id);

-- Create item_status_gates table
CREATE TABLE catalog.item_status_gates (
    current_item_status_id uuid NOT NULL,
    can_update_to_item_status_id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    PRIMARY KEY (current_item_status_id, can_update_to_item_status_id),
    FOREIGN KEY (current_item_status_id) REFERENCES catalog.item_statuses(id) ON DELETE CASCADE,
    FOREIGN KEY (can_update_to_item_status_id) REFERENCES catalog.item_statuses(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES identity.tenants(id) ON DELETE CASCADE
);

Migration 2: MigrateItemStatusScopeData

-- Migrate category associations (scope_level = 10)
-- Set scope_level for statuses with ONLY category associations
UPDATE catalog.item_statuses s
SET scope_level = 10
WHERE EXISTS (SELECT 1 FROM catalog.item_status_categories c WHERE c.item_status_id = s.id)
  AND NOT EXISTS (SELECT 1 FROM catalog.item_status_types t WHERE t.item_status_id = s.id);

-- Insert category passport IDs into scopes table
INSERT INTO catalog.item_status_scopes (item_status_id, scope_passport_id)
SELECT c.item_status_id, c.item_category_id
FROM catalog.item_status_categories c
JOIN catalog.item_statuses s ON s.id = c.item_status_id
WHERE s.scope_level = 10;

-- Migrate type associations (scope_level = 20)
-- Set scope_level for statuses with type associations (takes precedence)
UPDATE catalog.item_statuses s
SET scope_level = 20
WHERE EXISTS (SELECT 1 FROM catalog.item_status_types t WHERE t.item_status_id = s.id);

-- Insert type passport IDs into scopes table
INSERT INTO catalog.item_status_scopes (item_status_id, scope_passport_id)
SELECT t.item_status_id, t.item_type_id
FROM catalog.item_status_types t
JOIN catalog.item_statuses s ON s.id = t.item_status_id
WHERE s.scope_level = 20;

Migration 3: DropOldItemStatusJunctionTables

DROP TABLE catalog.item_status_categories;
DROP TABLE catalog.item_status_types;

1.9 Create Prism Migration for Journal Tables

Migration (Prism): AddCatalogStatusJournalTables

-- Create journal tables
CREATE TABLE prism.catalog_item_statuses_journal (
    id uuid NOT NULL,
    name text NOT NULL,
    description text,
    reason_code_required boolean NOT NULL,
    destroy_date_required boolean NOT NULL,
    point_of_use_prohibited boolean NOT NULL,
    indicates_expiration boolean NOT NULL,
    scope_level smallint NOT NULL,
    tenant_id uuid NOT NULL,
    platform_type_id smallint NOT NULL,
    seq_id integer NOT NULL,
    valid_from timestamp NOT NULL,
    valid_to timestamp,
    initiated_by_user_id uuid NOT NULL,
    invalidated_by_user_id uuid,
    PRIMARY KEY (id, seq_id)
);

CREATE TABLE prism.catalog_item_status_scopes_journal (
    item_status_id uuid NOT NULL,
    scope_passport_id uuid NOT NULL,
    seq_id integer NOT NULL,
    valid_from timestamp NOT NULL,
    valid_to timestamp,
    initiated_by_user_id uuid NOT NULL,
    invalidated_by_user_id uuid,
    PRIMARY KEY (item_status_id, scope_passport_id, seq_id)
);

CREATE TABLE prism.catalog_item_status_gates_journal (
    current_item_status_id uuid NOT NULL,
    can_update_to_item_status_id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    seq_id integer NOT NULL,
    valid_from timestamp NOT NULL,
    valid_to timestamp,
    initiated_by_user_id uuid NOT NULL,
    invalidated_by_user_id uuid,
    PRIMARY KEY (current_item_status_id, can_update_to_item_status_id, seq_id)
);

-- Create triggers on source tables (in catalog schema)
CREATE TRIGGER trg_item_statuses_journal
    AFTER INSERT OR UPDATE ON catalog.item_statuses
    FOR EACH ROW EXECUTE FUNCTION prism.journal_writer();

CREATE TRIGGER trg_item_status_scopes_journal
    AFTER INSERT OR UPDATE ON catalog.item_status_scopes
    FOR EACH ROW EXECUTE FUNCTION prism.journal_writer();

CREATE TRIGGER trg_item_status_gates_journal
    AFTER INSERT OR UPDATE ON catalog.item_status_gates
    FOR EACH ROW EXECUTE FUNCTION prism.journal_writer();

Migration Order:

  1. Prism migration runs first (creates journal tables)
  2. Catalog migration runs second (creates source tables with triggers)

Stage 2: StatusDraft & API Updates

2.1 Update StatusDraft

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Abstractions/Drafts/StatusDraft.cs

public class StatusDraft {
    public Guid? Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Description { get; set; }

    // Scope configuration
    /// <summary>
    /// 0 = Global, 10 = Category, 20 = ItemType (from platform_types.collection_level)
    /// </summary>
    public short ScopeLevel { get; set; } = 0;

    /// <summary>
    /// Passport IDs for scoped entities (categories or types).
    /// All must have matching collection_level in platform_types.
    /// </summary>
    public List<Guid> ScopePassportIds { get; set; } = new();

    // DEPRECATED: Remove after migration
    // public List<Guid> CategoryIds { get; set; }
}

2.2 Create StatusGateDraft

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Abstractions/Drafts/StatusGateDraft.cs

public class StatusGateDraft {
    [Required] public Guid ToStatusId { get; set; }
}

2.3 Update StatusApi Endpoints

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog/ApiEngines/StatusApi.cs

Update existing:

  • POST /statuses - Handle ScopeLevel and ScopePassportIds
  • PUT /statuses/{id} - Handle scope updates

Add new endpoints:

  • GET /statuses/{id}/gates - Get allowed transitions from this status
  • POST /statuses/{id}/gates - Add allowed transition
  • DELETE /statuses/{id}/gates/{toStatusId} - Remove transition
  • GET /statuses/gates - Get all defined gates

Validation on Create/Update:

  1. If ScopeLevel = 0, ScopePassportIds must be empty
  2. If ScopeLevel > 0, ScopePassportIds must have at least one entry
  3. All passport IDs must exist in prism.passports
  4. All referenced passports must have platform_type with matching collection_level

Stage 3: Validation Service

3.1 Create StatusValidationService

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog/Services/StatusValidationService.cs

public class StatusValidationService(CatalogDb catalogDb, PrismDb prismDb) {

    /// <summary>
    /// Validates that a status can be assigned to an item of the given type.
    /// </summary>
    public async Task<StatusScopeValidationResult> ValidateStatusForItemAsync(
        Guid statusId,
        Guid itemTypeId,
        CancellationToken ct)
    {
        var status = await catalogDb.ItemStatuses
            .Include(s => s.Scopes)
            .FirstOrDefaultAsync(s => s.Id == statusId, ct);

        if (status is null)
            return StatusScopeValidationResult.StatusNotFound(statusId);

        // Global scope - always valid
        if (status.ScopeLevel == 0)
            return StatusScopeValidationResult.Valid();

        // Get item type with its category
        var itemType = await catalogDb.ItemTypes
            .Where(t => t.Id == itemTypeId)
            .Select(t => new { t.Id, t.ItemCategoryId })
            .FirstOrDefaultAsync(ct);

        if (itemType is null)
            return StatusScopeValidationResult.ItemTypeNotFound(itemTypeId);

        var scopePassportIds = status.Scopes.Select(s => s.ScopePassportId).ToHashSet();

        // Category-scoped (level 10): check if item's category is in scopes
        if (status.ScopeLevel == 10) {
            if (itemType.ItemCategoryId.HasValue && scopePassportIds.Contains(itemType.ItemCategoryId.Value))
                return StatusScopeValidationResult.Valid();
            return StatusScopeValidationResult.StatusNotAllowedForCategory(status.Name);
        }

        // Type-scoped (level 20): check if item's type is in scopes
        if (status.ScopeLevel == 20) {
            if (scopePassportIds.Contains(itemTypeId))
                return StatusScopeValidationResult.Valid();
            return StatusScopeValidationResult.StatusNotAllowedForType(status.Name);
        }

        return StatusScopeValidationResult.Valid();
    }

    /// <summary>
    /// Validates status transition (gate check). Permissive by default.
    /// </summary>
    public async Task<StatusGateValidationResult> ValidateStatusTransitionAsync(
        Guid currentStatusId,
        Guid newStatusId,
        CancellationToken ct)
    {
        if (currentStatusId == newStatusId)
            return StatusGateValidationResult.Valid();

        // Check if any gates are defined for current status
        var hasGates = await catalogDb.ItemStatusGates
            .AnyAsync(g => g.CurrentItemStatusId == currentStatusId, ct);

        // Permissive: no gates defined = all transitions allowed
        if (!hasGates)
            return StatusGateValidationResult.Valid();

        // Gates defined - check if this specific transition is allowed
        var allowed = await catalogDb.ItemStatusGates
            .AnyAsync(g => g.CurrentItemStatusId == currentStatusId
                        && g.CanUpdateToItemStatusId == newStatusId, ct);

        if (allowed)
            return StatusGateValidationResult.Valid();

        return StatusGateValidationResult.TransitionNotAllowed(currentStatusId, newStatusId);
    }

    /// <summary>
    /// Validates that all passport IDs have the same collection_level as specified.
    /// </summary>
    public async Task<bool> ValidateScopeConsistencyAsync(
        short expectedScopeLevel,
        IEnumerable<Guid> passportIds,
        CancellationToken ct)
    {
        if (expectedScopeLevel == 0)
            return !passportIds.Any(); // Global must have no scopes

        var passportList = passportIds.ToList();
        if (!passportList.Any())
            return false; // Non-global must have at least one scope

        // Check all passports have matching collection_level
        var mismatchCount = await prismDb.Passports
            .Where(p => passportList.Contains(p.GlobalId))
            .Where(p => p.PlatformType.CollectionLevel != expectedScopeLevel)
            .CountAsync(ct);

        return mismatchCount == 0;
    }
}

3.2 Create Result Types

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Abstractions/Validation/StatusValidationResults.cs

public record StatusScopeValidationResult(bool IsValid, string? ErrorCode, string? ErrorMessage) {
    public static StatusScopeValidationResult Valid() => new(true, null, null);
    public static StatusScopeValidationResult StatusNotFound(Guid id) =>
        new(false, "STATUS_NOT_FOUND", $"Status {id} not found");
    public static StatusScopeValidationResult ItemTypeNotFound(Guid id) =>
        new(false, "ITEM_TYPE_NOT_FOUND", $"Item type {id} not found");
    public static StatusScopeValidationResult StatusNotAllowedForCategory(string name) =>
        new(false, "STATUS_NOT_ALLOWED_FOR_CATEGORY", $"Status '{name}' is not allowed for this item's category");
    public static StatusScopeValidationResult StatusNotAllowedForType(string name) =>
        new(false, "STATUS_NOT_ALLOWED_FOR_TYPE", $"Status '{name}' is not allowed for this item type");
}

public record StatusGateValidationResult(bool IsValid, string? ErrorCode, string? ErrorMessage) {
    public static StatusGateValidationResult Valid() => new(true, null, null);
    public static StatusGateValidationResult TransitionNotAllowed(Guid from, Guid to) =>
        new(false, "STATUS_TRANSITION_NOT_ALLOWED", $"Transition from status {from} to {to} is not allowed");
}

Stage 4: ItemApi Integration

4.1 Update ItemsService

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog/Services/ItemsService.cs

Inject StatusValidationService and add validation to:

CreateItem (~line 93):

var scopeValidation = await statusValidation.ValidateStatusForItemAsync(
    request.StatusId, request.ItemTypeId, ct);
if (!scopeValidation.IsValid)
    return (false, scopeValidation.ErrorMessage);

UpdateItem (~line 953):

if (revision.StatusId.HasValue && revision.StatusId.Value != item.StatusId) {
    // Validate scope
    var scopeValidation = await statusValidation.ValidateStatusForItemAsync(
        revision.StatusId.Value, item.ItemTypeId, ct);
    if (!scopeValidation.IsValid)
        return (false, scopeValidation.ErrorMessage);

    // Validate gate
    var gateValidation = await statusValidation.ValidateStatusTransitionAsync(
        item.StatusId, revision.StatusId.Value, ct);
    if (!gateValidation.IsValid)
        return (false, gateValidation.ErrorMessage);
}

4.2 Register Service in DI

File: engines/catalog/src/Acsis.Dynaplex.Engines.Catalog/Program.cs

builder.Services.AddScoped<StatusValidationService>();

Stage 5: Cleanup

5.1 Remove Old Entity Files

After migration verification:

  • Delete ItemStatusCategory.cs
  • Delete ItemStatusType.cs
  • Remove StatusCategories and StatusTypes collections from ItemStatus.cs
  • Remove old DbSets from CatalogDb.cs

5.2 Update Tests

  • Add tests for scope validation (global, category, type)
  • Add tests for gate validation (permissive default, explicit gates)
  • Add tests for scope consistency validation
  • Update existing status tests for new schema

Critical Files Summary

Catalog Component

File Action
catalog/.../Database/ItemStatus.cs MODIFY (add scope_level, Scopes collection)
catalog/.../Database/ItemStatusScope.cs CREATE
catalog/.../Database/ItemStatusGate.cs MODIFY (add TenantId, PrimaryKey)
catalog/.../Database/CatalogDb.cs MODIFY (add DbSets, HasJournal(), external ref)
catalog/.../Drafts/StatusDraft.cs MODIFY (add ScopeLevel, ScopePassportIds)
catalog/.../Drafts/StatusGateDraft.cs CREATE
catalog/.../Validation/StatusValidationResults.cs CREATE
catalog/.../Services/StatusValidationService.cs CREATE
catalog/.../Services/ItemsService.cs MODIFY (add validation calls)
catalog/.../ApiEngines/StatusApi.cs MODIFY (handle scopes, add gate endpoints)
catalog/.../Program.cs MODIFY (register service)
catalog/.../Database/ItemStatusCategory.cs DELETE (after migration)
catalog/.../Database/ItemStatusType.cs DELETE (after migration)

Prism Component (Journal Tables)

File Action
prism/.../Database/ItemStatusLog.cs CREATE
prism/.../Database/ItemStatusScopeLog.cs CREATE
prism/.../Database/ItemStatusGateLog.cs CREATE
prism/.../Database/PrismDb.cs MODIFY (add journal DbSets)
prism/.../Database/PrismDbModelConfiguration.cs MODIFY (add composite keys)

Database Schema Summary

Final catalog.item_statuses Table

Column Type Notes
id uuid PK
name text Unique
description text Nullable
reason_code_required boolean
destroy_date_required boolean
point_of_use_prohibited boolean
indicates_expiration boolean
scope_level smallint NEW: 0=Global, 10=Category, 20=Type
tenant_id uuid FK to identity.tenants
platform_type_id smallint Always 312

New catalog.item_status_scopes Table

Column Type Notes
item_status_id uuid PK part 1, FK to item_statuses
scope_passport_id uuid PK part 2, FK to prism.passports

Stores the passport IDs (global IDs) of categories or types this status applies to.
The passport's platform_type_id → platform_types.collection_level determines the entity type.

New catalog.item_status_gates Table

Column Type Notes
current_item_status_id uuid PK part 1, FK to item_statuses
can_update_to_item_status_id uuid PK part 2, FK to item_statuses
tenant_id uuid FK to identity.tenants

Validation Rules

  1. Scope Level Consistency:

    • If scope_level = 0 (Global): No entries in item_status_scopes
    • If scope_level = 10 (Category): All scopes must reference ItemCategory passports (collection_level=10)
    • If scope_level = 20 (Type): All scopes must reference ItemType passports (collection_level=20)

  2. Gate Behavior (Permissive Default):

    • If no gates defined for a status: All transitions from that status are allowed
    • If gates defined: Only explicitly defined transitions are allowed