Plan: Customer/Stage Management & Unused Versions CLI

Context

The Orchestrator CLI currently has operational commands (deploy, remove, service, iis, status, remove-stage) but no way to query the CommandCenter database for customer/stage data or identify versions that can be safely cleaned up.

Operators need to: 1. View customers and their stages — see which customers exist, what stages they have, what version each stage runs, active/online state. 2. Identify unused application versions — find versions with no customer stage pointing at them, including which servers still have them installed (from TblServerVersion), so they know what's safe to uninstall.

Database Schema (from ccmodel)

TblCustomer:       Key, Name (unique), SetupName, ChangeDate, CreateDate
TblCustomerStage:  Key, Name, Url, Instance, Database, Active, Online, Identifier (Guid, unique),
                   IsTestVersion, UseIpWhiteListing, CustomerKey → TblCustomer, VersionKey → TblApplicationVersion
TblApplicationVersion: Key, Identifier (e.g. "25.5.29"), Online, ChangeDate, CreateDate
TblServer:         Key, Name, Url, Online, Active, Type ("BenefitManager")
TblServerVersion:  Key, ServerKey → TblServer, VersionKey → TblApplicationVersion, Active, VirtualDirectory

---

Step 1: New DTOs in Contracts

File: src/BenefitManager.Contracts/Models/ManagementModels.cs

Add alongside the existing records:

public record CustomerSummary(string Name, string SetupName, int StageCount);

public record StageSummary(
    string CustomerName, string StageName, string Url, string Database,
    string Version, bool Active, bool Online, Guid Identifier);

public record ApplicationVersionInfo(
    string Version, bool Online, DateTime CreateDate, string? InstalledServers);

• [ ] DTOs added to ManagementModels.cs

---

Step 2: New ICommandCenterQueryService + Dapper implementation

New file: src/BenefitManager.Orchestrator/Services/ICommandCenterQueryService.cs New file: src/BenefitManager.Orchestrator/Services/CommandCenterQueryService.cs

Interface

public interface ICommandCenterQueryService
{
    Task<List<CustomerSummary>> GetCustomersAsync(string environment, CancellationToken ct = default);
    Task<List<StageSummary>> GetStagesAsync(string environment, string? customerName = null, CancellationToken ct = default);
    Task<List<ApplicationVersionInfo>> GetUnusedVersionsAsync(string environment, CancellationToken ct = default);
}

Implementation — Dapper queries

GetCustomersAsync:

SELECT c.[Name], c.[SetupName],
       (SELECT COUNT(1) FROM [dbo].[TblCustomerStage] cs WHERE cs.CustomerKey = c.[Key]) AS StageCount
FROM [dbo].[TblCustomer] c
ORDER BY c.[Name]

GetStagesAsync (optionally filtered by @CustomerName):

SELECT c.[Name] AS CustomerName, cs.[Name] AS StageName, cs.[Url],
       cs.[Database], av.[Identifier] AS Version,
       cs.[Active], cs.[Online], cs.[Identifier]
FROM [dbo].[TblCustomerStage] cs
JOIN [dbo].[TblCustomer] c ON c.[Key] = cs.CustomerKey
JOIN [dbo].[TblApplicationVersion] av ON av.[Key] = cs.VersionKey
WHERE (@CustomerName IS NULL OR c.[Name] = @CustomerName)
ORDER BY c.[Name], cs.[Name]

GetUnusedVersionsAsync:

SELECT av.[Identifier] AS Version, av.[Online], av.[CreateDate],
       STRING_AGG(s.[Name], ', ') AS InstalledServers
FROM [dbo].[TblApplicationVersion] av
LEFT JOIN [dbo].[TblServerVersion] sv ON sv.VersionKey = av.[Key] AND sv.Active = 1
LEFT JOIN [dbo].[TblServer] s ON s.[Key] = sv.ServerKey
WHERE NOT EXISTS (
    SELECT 1 FROM [dbo].[TblCustomerStage] cs WHERE cs.VersionKey = av.[Key]
)
GROUP BY av.[Identifier], av.[Online], av.[CreateDate]
ORDER BY av.[Identifier]

Constructor: CommandCenterQueryService(IConfiguration configuration) — same pattern as all existing SQL services, using GetConnectionString($"CommandCenter_{env}").

• [ ] ICommandCenterQueryService interface created
• [ ] CommandCenterQueryService implementation with all 3 Dapper queries
• [ ] Service registered in Program.cs

---

Step 3: New customers CLI command

New file: src/BenefitManager.Orchestrator/Commands/CustomersCommand.cs

public sealed class CustomersSettings : CommandSettings
{
    [CommandArgument(0, "<ENVIRONMENT>")]
    [Description("The target environment (e.g. Acceptatie or Productie)")]
    public string Environment { get; init; } = null!;
}

public sealed class CustomersCommand(ICommandCenterQueryService queryService) : AsyncCommand<CustomersSettings>

Renders a Spectre.Console table:

┌──────────────────┬────────────┬────────┐
│ Name             │ SetupName  │ Stages │
├──────────────────┼────────────┼────────┤
│ ACME Corp        │ ACME       │      3 │
│ BCS Benefits     │ BCS        │      5 │
└──────────────────┴────────────┴────────┘

Register as:

cli.AddCommand<CustomersCommand>("customers")
   .WithDescription("Lists all customers in the CommandCenter database.")
   .WithExample(["customers", "Productie"]);

• [ ] CustomersSettings + CustomersCommand created
• [ ] Command registered in Program.cs

---

Step 4: New stages CLI command

New file: src/BenefitManager.Orchestrator/Commands/StagesCommand.cs

public sealed class StagesSettings : CommandSettings
{
    [CommandArgument(0, "<ENVIRONMENT>")]
    [Description("The target environment (e.g. Acceptatie or Productie)")]
    public string Environment { get; init; } = null!;

    [CommandOption("--customer")]
    [Description("Filter stages by customer name")]
    public string? Customer { get; init; }
}

public sealed class StagesCommand(ICommandCenterQueryService queryService) : AsyncCommand<StagesSettings>

Renders a Spectre.Console table with columns: Customer, Stage, Url, Database, Version, Active, Online, Identifier.

Color coding: Active/Online true = green check, false = red cross.

Register as:

cli.AddCommand<StagesCommand>("stages")
   .WithDescription("Lists customer stages in the CommandCenter database.")
   .WithExample(["stages", "Productie"])
   .WithExample(["stages", "Productie", "--customer", "ACME"]);

• [ ] StagesSettings + StagesCommand created
• [ ] Command registered in Program.cs

---

Step 5: New versions CLI command

New file: src/BenefitManager.Orchestrator/Commands/VersionsCommand.cs

public sealed class VersionsSettings : CommandSettings
{
    [CommandArgument(0, "<ENVIRONMENT>")]
    [Description("The target environment (e.g. Acceptatie or Productie)")]
    public string Environment { get; init; } = null!;
}

public sealed class VersionsCommand(ICommandCenterQueryService queryService) : AsyncCommand<VersionsSettings>

Renders a Spectre.Console table of versions not used by any customer stage:

┌──────────┬────────┬─────────────┬─────────────────────────────┐
│ Version  │ Online │ Created     │ Installed                   │
├──────────┼────────┼─────────────┼─────────────────────────────┤
│ 25.4.12  │ ✗      │ 2025-11-03  │ vmbenefitsbmp1, vmbenefits2 │
│ 25.5.1   │ ✓      │ 2026-01-15  │ vmbenefitsbmp1              │
│ 25.5.28  │ ✗      │ 2026-03-20  │                             │
└──────────┴────────┴─────────────┴─────────────────────────────┘

The "Installed" column comes from STRING_AGG(TblServer.Name) via TblServerVersion — shows which servers still have this version deployed.

Register as:

cli.AddCommand<VersionsCommand>("versions")
   .WithDescription("Lists application versions not assigned to any customer stage.")
   .WithExample(["versions", "Productie"]);

• [ ] VersionsSettings + VersionsCommand created
• [ ] Command registered in Program.cs

---

Step 6: Tests

New file: tests/BenefitManager.Orchestrator.Tests/CommandCenterQueryServiceTests.cs

Mock ICommandCenterQueryService and test each command:

• CustomersCommand: returns list → return 0. Empty → "No customers found".
• StagesCommand: returns list → return 0. With --customer → verify param passed.

• VersionsCommand: returns list with InstalledServers → return 0. Empty → "No unused versions".

• [ ] Tests for CustomersCommand

• [ ] Tests for StagesCommand
• [ ] Tests for VersionsCommand

---

Files Summary

Action	File
Modify	src/BenefitManager.Contracts/Models/ManagementModels.cs
Modify	src/BenefitManager.Orchestrator/Program.cs
New	src/BenefitManager.Orchestrator/Services/ICommandCenterQueryService.cs
New	src/BenefitManager.Orchestrator/Services/CommandCenterQueryService.cs
New	src/BenefitManager.Orchestrator/Commands/CustomersCommand.cs
New	src/BenefitManager.Orchestrator/Commands/StagesCommand.cs
New	src/BenefitManager.Orchestrator/Commands/VersionsCommand.cs
New	tests/BenefitManager.Orchestrator.Tests/CommandCenterQueryServiceTests.cs

---

Verification

1. dotnet build — 0 errors, 0 warnings
2. dotnet test — all existing + new tests pass
3. orchestrator customers Productie — shows customer table
4. orchestrator stages Productie — shows all stages
5. orchestrator stages Productie --customer "ACME" — shows filtered stages
6. orchestrator versions Productie — shows unused versions with Installed server column
7. orchestrator --help — all new commands appear