# Products Page Query Findings

Date: 2026-08-21

## Purpose

This document records findings from comparing the proposed Products page query in `study digital twin schema.linq` with the current Digital Twin implementation. The system is an MVP under active development, so existing designs are considered malleable and replaceable. These findings are intended to inform upcoming sprint planning.

> **Baseline note:** Implementation-status claims were revalidated read-only against `HCC.Inventory.DigiTwin` `origin/integration` commit `525e91da` and the relevant upstream `origin/integration` baselines. The `327055-receiving-ui-counts` working tree contains extensive modified and untracked experimental work. In particular, tenant-aware receiving, direct Formulary event-to-SQL handling, migration `006`, and synthetic grouping are not integrated capability. No repository branch was switched or reset during this investigation.

## Products Query Execution Path

The current Products request follows this path:

1. `GET /api/digitwin/products` is defined in `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Api/Endpoints/DigiTwinEndpoints.cs`.
2. `ProductScopeResolver` validates the tenant/root and selected facilities.
3. `ProductQueryFactory` maps request parameters into `ProductQuery`.
4. `DigiTwinService.GetProductsAsync` calls the product repository.
5. `SqlProductRepository.QueryAsync` builds and executes the Products SQL query.

The SQL in `SqlProductRepository.QueryAsync` reads quantities and location counts primarily from `dbo.FacilityFormularyAggregate` and joins `dbo.Formulary` for product grouping and display information.

## Products Page Requirements

The investigated Products page requires:

- A required tenant scope.
- A required list of facility IDs.
- Product grouping by formulary equivalency group.
- A decision on whether missing equivalency is valid and, if so, the stable grouping behavior.
- Total, serialized, and non-serialized quantities.
- Container count for a single-facility selection.
- Facility count for a multi-facility selection.
- Description search.
- Deterministic pagination.
- Optional dispensability filtering.
- All virtual-bin inventory classified as dispensable, regardless of its destination.
- Physical inventory in Returns or Waste container categories classified as non-dispensable.
- Physical inventory in Dispense or 340B container categories classified as dispensable.
- Inventory becomes non-dispensable only after it is committed to a physical Returns or Waste location.

## Confirmed Dispensability Model

Dispensability must be derived at inventory-container grain before product aggregation. The rule order is significant:

1. If `ContainerInventorySummary.IsVirtual = 1`, the inventory is dispensable. Its destination does not participate in classification.
2. If the inventory is physical, its current container category determines dispensability.
3. Physical `Dispense` and `340B` categories are dispensable.
4. Physical `Returns` and `Waste` categories are non-dispensable.
5. A physical container with missing or unknown category metadata cannot be classified safely.

Container Management's `ContainerCategory` is the authoritative business-use classification. Its canonical wire values are `Dispense`, `340B`, `Returns`, and `Waste`.

`LocationNode.ContainerKind` and `LocationNode.VirtualPurpose` are not valid substitutes:

- `ContainerKind` is associated with physical-versus-virtual shape in the existing architecture artifacts.
- `VirtualPurpose` describes a virtual workflow such as in-transit receiving.
- Neither field is populated by a current Digital Twin writer.
- The singular value `Return` is not the canonical Container Management category; the category is `Returns`.

Recall inventory follows the same location rule for this Products classification: it remains dispensable while in a virtual bin and becomes non-dispensable when committed to its physical Returns holding location.

A product can have both dispensable and non-dispensable inventory simultaneously. Therefore, one product-level `IsDispensable` boolean cannot describe an unfiltered aggregate faithfully. A dispensability filter must select inventory rows before quantities and locations are aggregated.

## Current Implementation Findings

### Functionality That Aligns

- The request and query are scoped by tenant and selected facilities, although integrated receiving state does not yet carry a reliable tenant identity.
- Products are grouped by the integrated, non-null `Formulary.EquivalencyGroupId`.
- Quantity, serialized quantity, and non-serialized quantity are aggregated.
- Description search is applied after aggregation, preserving the full quantity of a matching product group.
- Search wildcard characters are escaped before they are passed to SQL.
- Sorting supports quantity, name, and location count.
- Distinct NDC count is returned as additional Products page information.
- The integrated endpoint and repository provide the basic Products read substrate.

### Requirement Gaps

- Pagination is not implemented in `ProductsQueryRequest`, `ProductQuery`, or the SQL query.
- The repository reads the complete matching result set into memory.
- The integrated receiving event and canonical inventory position have no tenant ID, and SQL projection substitutes `Guid.Empty`; tenant-scoped reads are therefore not end-to-end reliable.
- There is no integrated `GroupingKey`, `IsSyntheticGroup`, or fallback when equivalency is unavailable.
- No container-type or dispensability filter is accepted by the endpoint.
- Digital Twin does not currently project the authoritative Container Management `ContainerCategory` required to classify physical inventory.
- Every product is currently returned with the sentinel value `IsDispensable = true`.
- Multi-facility location count is calculated by summing per-facility container counts instead of counting distinct facilities containing the product.
- The maintained aggregate excludes virtual containers from its location count.
- The existing aggregate does not contain a dispensability dimension and therefore cannot answer filtered quantity or location-count questions by Return/Waste classification.
- Direct integration coverage for the Products repository does not cover quantities, search, sorting, pagination, dispensability, or multi-facility location semantics.

## FacilityFormularyAggregate Assessment

`dbo.FacilityFormularyAggregate` is an application-maintained materialized read projection. It is derived from `ContainerInventorySummary`, NDC mappings, and formulary reference data.

### Current Write Behavior

`ContainerProjectionRepository.ProjectContainerAsync` currently performs the following work in one SQL transaction:

1. Acquires an exclusive application lock for the facility aggregate.
2. Reads the container's old formulary contributions.
3. Deletes stale detailed inventory lines.
4. Inserts or updates current detailed inventory lines.
5. Reads the container's new formulary contributions.
6. Applies contribution deltas to `FacilityFormularyAggregate`.
7. Updates the container projection watermark.
8. Updates the projection checkpoint.
9. Commits the transaction.

The working-tree-only `FormularyReferenceRepository.ApplySnapshotAsync` also maintains the aggregate transactionally by deleting and rebuilding the entire facility aggregate after updating Formulary reference data and NDC mappings. That direct external-event-to-SQL path is experimental, bypasses canonical Cosmos, and is not part of the integrated baseline.

### Benefits

- Products reads operate on substantially fewer rows.
- Quantity aggregation has predictable read cost.
- Sorting by quantity or location count is inexpensive.
- The Products endpoint is isolated from lot-level and container-level row volume.

### Costs

- Every relevant container event causes write amplification.
- Facility-level application locking serializes otherwise independent container projections within a facility.
- Delta calculation and aggregate maintenance increase implementation complexity.
- The experimental direct-SQL Formulary path demonstrates an additional facility-wide delete/rebuild cost that must not be adopted into the target architecture.
- The aggregate can drift from the detailed projection if a write path or edge case does not preserve every invariant.
- The write model is coupled to read requirements that are still changing during MVP development.
- The current aggregate dimensions do not support the newly clarified Products requirements.

The number of tables in a transaction is not itself the primary concern. The more important measures are rows touched, lock scope, transaction duration, contention, and the number of invariants that application code must preserve.

## Local Data Observation

For the investigated two-facility working-tree fixture, quantity totals agree between the detailed inventory and the maintained aggregate. This validates local query behavior only; it does not establish integrated event-to-read-model capability:

| Product | Quantity | Serialized | Non-serialized |
|---|---:|---:|---:|
| Amoxicillin | 276 | 226 | 50 |
| Ibuprofen | 200 | 0 | 200 |

Location semantics differ:

| Product | Detailed container count | Required multi-facility count | Current aggregate result |
|---|---:|---:|---:|
| Amoxicillin | 6 | 2 facilities | 5 locations |
| Ibuprofen | 1 | 1 facility | 1 location |

The Downtown Amoxicillin aggregate currently reports four non-virtual locations while the current detailed inventory appears to contain three positive, non-virtual containers. This may be a fixture or projection-history artifact rather than a defect in the current code, but it illustrates the need for reconciliation whenever a derived aggregate is maintained.

The fixture can prove that virtual inventory is dispensable from `ContainerInventorySummary.IsVirtual`, but it cannot prove physical-container classification because Digital Twin has no projected `ContainerCategory`. The existing `LocationNode.ContainerKind` and `VirtualPurpose` fields are neither populated nor semantically appropriate for this purpose.

## Normalization Direction

The MVP should default to a normalized relational model. Existing screen-specific aggregates and duplicated attributes should not be retained merely because anticipated reads might benefit from them. SQL Server can answer new relational questions through joins, grouping, indexes, and execution-plan tuning without requiring every future read shape to be encoded into event handlers.

The current SQL structures fall into distinct categories:

| Structure | Role | Direction |
|---|---|---|
| `ContainerInventorySummary` | Canonical SQL inventory fact at container/NDC/lot/expiry grain | Keep |
| `Formulary` and `NdcFormularyMap` | Product reference and attribution dimensions | Keep |
| `LocationNode` | Authoritative location and container metadata | Keep and correct |
| `LocationClosure` | Relational representation of arbitrary-depth hierarchy | Keep |
| `FacilityFormularyAggregate` | Products-oriented maintained materialization | Remove unless measurement later justifies it |
| Repeated `IsVirtual`, path, and attribution columns | Denormalized copies of facts available elsewhere | Reassess and remove unless each has a demonstrated need |

`LocationClosure` is not considered speculative screen-specific denormalization. A closure table is a standard relational model for efficient arbitrary-depth ancestor and descendant traversal. It supports general hierarchy behavior, including subtree queries and empty locations, rather than one predetermined UI view. Its maintenance is therefore part of preserving the location model itself.

The normalized direction should make:

- `ContainerInventorySummary` the inventory fact.
- `LocationNode` the source of location/container metadata, including `VirtualPurpose` and `ContainerCategory`.
- `LocationClosure` the hierarchy relationship model.
- `Formulary` and `NdcFormularyMap` the product attribution model.
- Recall and similar reference snapshots independent dimensions used to derive judgments at query time.
- Products and other pages ordinary SQL queries over these facts and dimensions.

The target `LocationNode` model should eliminate redundant virtuality columns:

| Column | Target meaning |
|---|---|
| `LocationType` | Facility, Unit, Room, StorageArea, or Container |
| `VirtualPurpose` | Non-null for a virtual container; the value records its workflow purpose, such as receiving or restock |
| `ContainerCategory` | Container Management category: `Dispense`, `340B`, `Returns`, or `Waste` |
| `ContainerKind` | Remove; it duplicates virtuality and conflicts with category terminology |
| `IsVirtual` | Remove from `LocationNode`; derive virtuality from `VirtualPurpose IS NOT NULL` |

Container Management hierarchy events should maintain physical container nodes and their category. Digital Twin receiving and movement workflows should maintain Digital Twin-owned virtual nodes and their purpose. `SourceUpdatedUtc` or an equivalent version guard must prevent stale metadata events from overwriting newer location state.

The current LINQPad proof uses `ContainerInventorySummary.IsVirtual` because the normalized `LocationNode` model has not yet been implemented, and uses a table variable as a stand-in for the missing authoritative `ContainerCategory` projection. It groups by the integrated non-null `EquivalencyGroupId`; any synthetic fallback remains a product/domain decision. In the target model, the Products query should derive virtuality from `LocationNode.VirtualPurpose` and physical dispensability from `LocationNode.ContainerCategory`.

This direction reduces write amplification and prevents read requirements from coupling event handlers to screen-specific tables. Each event handler should update the relational concept its source owns plus the operational state needed for reliable processing. Changing a Products query should not ordinarily require changing event-processing transactions.

Performance concerns remain legitimate, particularly aggregation and sorted paging over arbitrary facility sets. They should be addressed incrementally:

1. Implement the normalized query as the correctness reference.
2. Add conventional indexes based on execution plans.
3. Test representative cardinalities against an explicit latency objective.
4. Consider covering or columnstore indexes when evidence supports them.
5. Introduce a materialized aggregate only when a measured query cannot meet its objective.

Any future denormalization should identify a measured read problem, performance objective, owner, consistency semantics, rebuild procedure, reconciliation strategy, and equivalence tests against the normalized query.

## Recommendation

Use the detailed SQL projections directly for the MVP Products query:

- Read quantities and virtual status from `ContainerInventorySummary`.
- Read product attribution from `NdcFormularyMap` and `Formulary`.
- Project the authoritative Container Management `ContainerCategory` into Digital Twin for physical containers.
- Join physical inventory to that category projection; do not derive category from names, paths, `ContainerKind`, or `VirtualPurpose`.
- Treat missing physical category metadata as unknown rather than silently dispensable.
- Remove `FacilityFormularyAggregate` from the Products read path.
- Remove aggregate delta maintenance and aggregate-specific facility locking after all readers have stopped depending on the aggregate.
- Remove the facility-wide aggregate rebuild from formulary reference handling.
- Continue to update the detailed projection and the state or checkpoint records needed to establish projection-processing correctness.
- Add indexes based on the finalized query and measured execution plans.
- Validate the raw query using representative tenant, facility, container, NDC, and lot volumes.

For the MVP, the detailed projection should be the correctness reference. A more expensive read is preferable to speculative write-side complexity while query semantics remain unsettled.

If measured performance later fails an agreed latency or throughput objective, introduce a new aggregate shaped around the finalized requirements. The detailed query should remain the oracle used to verify that aggregate.

## Replacement Safety

Aggregate maintenance must not be removed while any reader still depends on `FacilityFormularyAggregate`. The safe replacement sequence is:

1. Implement the detailed Products query and required request contract.
2. Add equivalence and behavior tests for the required Products scenarios.
3. Move all Products readers away from `FacilityFormularyAggregate`.
4. Verify that no other code path reads the aggregate.
5. Remove aggregate write maintenance, locking, rebuild logic, and schema artifacts.

Partially maintaining the aggregate would retain its complexity without preserving correctness.

## Planning Considerations

Upcoming planning should account for:

- Products request and response pagination contracts.
- Total-result-count behavior for paginated responses.
- Dispensability filter contract and accepted values.
- Projection and bootstrap of Container Management `ContainerCategory` into Digital Twin.
- Category update, move, deletion, ordering, and reconciliation behavior.
- Exact treatment of missing or unknown category metadata for physical containers.
- Response behavior when an unfiltered product contains both dispensable and non-dispensable inventory.
- Whether zero or negative inventory contributes to product visibility and location counts.
- Single-facility versus multi-facility location-count semantics.
- Whether virtual containers count as locations for a single-facility selection.
- Active and inactive formulary behavior.
- Search behavior when display names differ within one product group.
- Required indexes and an agreed performance target.
- Integration tests comparing Products results with detailed inventory fixtures.
- Removal of the obsolete aggregate read and write paths.
- Migration cleanup appropriate for an MVP with no production compatibility requirement.

## Relevant Files

- `_workspace/study digital twin schema.linq`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Api/Endpoints/DigiTwinEndpoints.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Application/Dtos/ProductsQueryRequest.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Application/Mappings/ProductQueryFactory.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Application/Services/DigiTwinService.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Contracts/ProductQuery.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Sql/SqlProductRepository.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/ContainerProjectionRepository.cs`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/FormularyReferenceRepository.cs` (**experimental working tree**)
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/003_CreateProjectionTables.sql`
- `HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/006_AddFormularyGroupingKey.sql` (**experimental working tree**)
- `HCC.Inventory.ContainerManagement/src/HCC.Inventory.ContainerManagement.Core/Domain/Enums/ContainerCategory.cs`
- `HCC.Inventory.ContainerManagement/src/HCC.Inventory.ContainerManagement.Infra/Outbox/ContainerOutboxDocumentFactory.cs`
- `HCC.Inventory.ContainerManagement/docs/LocationHierarchyChanged-Contract.md`