# Products Read Model and Event Map

> Detailed technical appendix. For the current human-readable summary and backlog map, see `products-read-model-event-map.md`.

Date: 2026-08-21

## Purpose

This document maps the integrated source-to-storage-to-read baseline for the Digital Twin Products list and Product Details, separates that baseline from experimental working-tree prototypes, identifies Digital Twin integration gaps, and proposes a dependency-ordered target architecture for MVP sprint planning.

Architecture documents are proposals unless executable code at the stated baseline confirms them. Source-path citations without line ranges identify the relevant integration-baseline file or symbol; citations explicitly marked **experimental** refer to unintegrated working-tree evidence and are not sprint credit.

## Provenance and Status

The `HCC.Inventory.DigiTwin` baseline is `origin/integration` commit `525e91da`. Upstream `origin/integration` baselines were also inspected read-only. No repository branch was switched or reset, and no service repository was modified.

Status terms used throughout:

- **Integrated**: executable code exists at the inspected `origin/integration` baseline for the stated boundary.
- **Source integrated / DigiTwin missing**: the source publisher exists, but the corresponding Digital Twin consumer or write path does not.
- **Experimental**: code exists only in an unintegrated working tree. It may inform design or tests but is not delivered capability.
- **Missing**: no executable integration-baseline path was found.
- **Target**: a confirmed decision or proposed implementation direction, not current capability.

### Integrated vs Experimental Capability Matrix

| Capability | Integrated baseline | Experimental working tree | Sprint-planning interpretation |
|---|---|---|---|
| Products APIs and SQL reads | Products list, details, locations, facility, and location endpoints route to `SqlProductRepository`. The list reads `FacilityFormularyAggregate` and `Formulary`, groups only by non-null `EquivalencyGroupId`, and has no pagination or dispensability behavior. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Api/Endpoints/DigiTwinEndpoints.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Sql/SqlProductRepository.cs`] | Working-tree query changes may reference later schema or grouping behavior. | Credit the integrated endpoints and current aggregate-backed query only. Plan paging, dispensability, normalized reads, and corrected location counts as gaps. |
| Serialized receiving intake | `DigitalTwinReceivingOutcomeEvent` route, handler, Cosmos `InventoryPosition`, inventory CFP/projector, `ContainerInventorySummary`, `FacilityFormularyAggregate`, operational state, and `ContainerProjectionRepository` are integrated. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Handlers/Program.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Handlers/Services/DigitalTwinReceivingOutcomeEventHandler.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/ContainerProjectionRepository.cs`] | Tenant-aware event handling, Cosmos state, and CFP behavior. | Credit the integrated tenantless receiving path; do not credit end-to-end tenant propagation. |
| Formulary publication | Formulary transactionally writes a Cosmos outbox and publishes `FormularyItemUpsertedEvent` through Dapr plus GCP CDC. [`HCC.Inventory.Formulary/src/HCC.Inventory.Formulary.Infra/Repositories/FormularyItemRepository.cs`] [`HCC.Inventory.Formulary/src/HCC.Inventory.Formulary.Handlers/Services/FormularyCdcChangeFeedProcessor.cs`] | None needed to establish the source publisher. | Source-team publication is already integrated. Do not create a story to invent another Formulary publisher. |
| Formulary consumption in DigiTwin | No integrated route, handler, repository, Formulary Cosmos model, Formulary CFP, or direct external-event SQL writer. | A route/handler and `FormularyReferenceRepository` write directly to SQL and rebuild facility aggregates. This path bypasses canonical Cosmos. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/FormularyReferenceRepository.cs`] **Experimental.** | Implement the missing Cosmos-first DigiTwin consumer. Do not adopt or count the experimental direct-SQL path. |
| SQL schema | Migrations `001`-`005` create reference, location, projection, operational, `FacilityFormularyAggregate`, `ContainerDim`, and related baseline structures. `Formulary.EquivalencyGroupId` is non-null. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026`] | Migration `006_AddFormularyGroupingKey.sql` adds `GroupingKey` and synthetic-group support. **Experimental.** | Plan from migrations `001`-`005`. There is no integrated `GroupingKey` or `IsSyntheticGroup`. |
| Tenant propagation | The receiving baseline has `FacilityId` but no `TenantId`; the Cosmos position document has no tenant ID; downstream projection uses `Guid.Empty` as `UnknownTenantId`. [`HCC.Inventory.Serialization/src/HCC.Inventory.Serialization.Contracts/Events/DigitalTwinReceivingOutcomeEvent.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Models/Documents/InventoryPositionDocument.cs`] | Tenant is propagated end to end. **Experimental.** | Tenant ownership and backfill are unresolved integrated gaps, not completed work. |
| Seed data | Local seeders directly provision/seed Cosmos setup and SQL fixtures. [`HCC.Inventory.DigiTwin/LocalSeeder/DigiTwinLocalSeeder.cs`] [`HCC.Inventory.DigiTwin/LocalSeeder/DigiTwinSqlSeeder.cs`] | Working-tree fixtures exercise experimental fields and flows. | Integrated seeds prove local fixtures, not external-event-to-read-model completeness. |

## Sprint Planning Interpretation

- **Integrated substrate:** Products endpoints and `SqlProductRepository`; serialized receiving intake into Cosmos; inventory change-feed projection into `ContainerInventorySummary`, `FacilityFormularyAggregate`, and operational state; migrations `001`-`005`; and direct local fixtures.
- **Experimental prototypes are not creditable:** direct Formulary-to-SQL handling, facility aggregate rebuild on Formulary events, migration `006`, synthetic grouping, and tenant-aware receiving/CFP behavior.
- **Source publishers already integrated:** Serialization receiving, Formulary upserts, Container Management hierarchy/container CDC, CycleCount completion, Recalls snapshots, and TenantConfig tenant-node snapshots. Sprint stories should target missing DigiTwin consumers and write paths rather than recreate source-team work.
- **Missing DigiTwin work:** Formulary Cosmos-first intake/projection, location/category intake/projection, CycleCount true-up, Recall intake, facility-reference intake/reconciliation, durable quarantine fanout, tenant resolution, non-serialized/movement paths, and normalized Products reads.
- **Decisions required before stories:** tenant identity ownership, synthetic grouping behavior when equivalency is unavailable, location event transport/wrapper handling and reconciliation, unknown category behavior, virtual 340B attribution, CycleCount fetch/true-up semantics, and the minimal Product Details contract.

## Confirmed Decisions and Requirements

These are target decisions and requirements, not claims about current implementation:

- Cosmos DB is the Digital Twin canonical record.
- External events first mutate Digital Twin state in Cosmos DB.
- Cosmos change-feed processors project canonical state into SQL Server.
- SQL holds normalized facts and dimensions by default; screen-specific maintained aggregates require measured justification.
- `LocationClosure` remains because it is a general hierarchy relationship model, not a Products-only aggregate.
- Target `LocationNode` uses non-null `VirtualPurpose` to identify a virtual container, stores authoritative `ContainerCategory`, and removes redundant `ContainerKind` and `IsVirtual` columns.
- The Products list has one row per product grouping across selected facilities.
- The Products list supports total, serialized, and non-serialized quantities; search; deterministic paging; and a dispensability filter.
- For one selected facility, location count means containers. For multiple selected facilities, it means facilities containing the product.
- Virtual inventory is always dispensable. Physical `Dispense` and `340B` inventory is dispensable. Physical `Returns` and `Waste` inventory is non-dispensable.
- Product Details is addressed by the same grouping key as the Products list and returns one row per NDC.
- Product Details fields beyond the NDC breakdown remain requirements-uncertain and must not be inferred from placeholder DTOs.

The executable proof query records the target count, paging, and dispensability semantics and identifies `ContainerCategory` as absent from the Digital Twin baseline. [`_workspace/study digital twin schema.linq`]

## Authority Boundary

The hybrid architecture document is a proposed plan, not evidence of integrated behavior. [`digital_twin_architecture/hybrid-approach-implementation-plan.md`]

The relevant proposal drift is now:

| Source capability | Integrated source status | Integrated DigiTwin status |
|---|---|---|
| Serialized receiving outcome | Serialization publishes `DigitalTwinReceivingOutcomeEvent` through Dapr from its products Cosmos CFP. | Intake, canonical inventory write, and inventory projection are integrated, but tenant identity is absent. |
| Formulary item upsert | Formulary publishes `FormularyItemUpsertedEvent` through Dapr and GCP CDC from a transactional Cosmos outbox. | Consumer, canonical Formulary model, and projection are missing. Direct SQL handling is experimental only. |
| Location hierarchy/container change | Container Management publishes per-node GCP CDC with `evt.LocationHierarchyChanged`. | Consumer and canonical/projection paths are missing. |
| Cycle count completion | CycleCount publishes completion from its integrated outbox flow. | Consumer and true-up path are missing. |
| Recall alert snapshot | Recalls publishes active/deactive target snapshots. | Consumer and canonical/projection paths are missing. |
| Tenant/facility metadata | TenantConfig publishes tenant-node full snapshots via GCP CDC. | Consumer and local canonical facility state are missing; current scope resolution calls TenantConfig directly. |

## Current Integrated Architecture

```mermaid
flowchart LR
    SER[Serialization products Cosmos CFP] -->|Dapr: DigitalTwinReceivingOutcomeEvent| RH[DigiTwin receiving handler]
    RH --> POS[(Cosmos inventory positions<br/>facility/container; no tenant)]
    POS -->|change feed| CFP[DigiTwin inventory projection CFP<br/>UnknownTenantId = Guid.Empty]
    CFP --> CIS[(ContainerInventorySummary)]
    CFP --> FFA[(FacilityFormularyAggregate)]
    FFA --> PR[SqlProductRepository]
    CIS --> PR
    FORMDB[(Integrated SQL Formulary fixtures/state)] --> PR
    PR --> API[Products list/details/locations/facility/location APIs]

    FORM[Integrated Formulary publisher] -. missing DigiTwin consumer .-> FC[(Target Formulary Cosmos state)]
    CM[Integrated Container Management CDC] -. missing DigiTwin consumer .-> LC[(Target location Cosmos state)]
    CC[Integrated CycleCount completion] -. missing DigiTwin consumer .-> POS
    REC[Integrated Recall snapshot publisher] -. missing DigiTwin consumer .-> RC[(Target recall Cosmos state)]
    TC[Integrated TenantConfig tenant-node CDC] -. missing DigiTwin consumer .-> FAC[(Target facility Cosmos state)]
    QR[Best-effort RxTransparent quarantine operation] -. no Digital Twin event or consumer .-> POS

    EXP[Experimental Formulary handler/repository] -. not integrated: direct SQL + aggregate rebuild .-> FORMDB

    classDef integrated fill:#dff2e1,stroke:#2f6f3e,color:#111;
    classDef missing fill:#fff4d6,stroke:#9a6b00,color:#111;
    classDef experimental fill:#f7dfe7,stroke:#8c3757,color:#111,stroke-dasharray: 5 5;
    class SER,RH,POS,CFP,CIS,FFA,PR,FORMDB,API,FORM,CM,CC,REC,TC integrated;
    class FC,LC,RC,FAC,QR missing;
    class EXP experimental;
```

The diagram deliberately shows the direct Formulary SQL path as experimental and disconnected from integrated credit. Integrated SQL Formulary data can exist through fixtures, but no integrated external-event writer maintains it.

## Current Cosmos and Projection Baseline

### Inventory Canonical State

The integrated local/Terraform definitions provision `inventory-positions` with a facility/container hierarchical partition key. [`HCC.Inventory.DigiTwin/terraform/main.tf`] [`HCC.Inventory.DigiTwin/LocalSeeder/DigiTwinLocalSeeder.cs`]

The integrated `InventoryPositionDocument` stores facility, container, serialized/non-serialized identity, quantity, barcode identity, status, confidence, and observation identity. It does **not** store `TenantId`. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Models/Documents/InventoryPositionDocument.cs`]

The repository performs read-compare-write behavior, stale/redelivery checks, and ETag retries. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/InventoryPositionRepository.cs`]

Only serialized receiving has an integrated producer. Non-serialized positions are represented by baseline domain/projection structures but have no integrated inbound writer. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Domain/InventoryPosition.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Handlers/Services/DigitalTwinReceivingOutcomeEventHandler.cs`]

Tenant-aware `InventoryPositionDocument`, receiving handling, and CFP coalescing seen in the working tree are **experimental**. In the integrated baseline, downstream SQL projection substitutes `Guid.Empty` as `UnknownTenantId`. This can merge unrelated tenants and means current tenant-scoped Products behavior is not end-to-end reliable until identity is resolved and state is backfilled.

### Cosmos-to-SQL Inventory Projection

The integrated inventory CFP/projector:

1. Reads changed inventory positions for a facility/container.
2. Builds absolute container inventory at NDC/lot/expiry grain with serialized and non-serialized quantity columns.
3. Transactionally replaces container SQL rows.
4. Maintains `FacilityFormularyAggregate` and projection operational state through `ContainerProjectionRepository`.

Relevant baseline paths: [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Handlers/Services/DigiTwinInventoryProjectionProcessor.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Application/Services/ContainerInventoryProjector.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/ContainerProjectionRepository.cs`]

There is no integrated Formulary Cosmos document family or Formulary CFP. The experimental `FormularyReferenceRepository` directly mutates SQL and rebuilds the facility aggregate; it is evidence that remap/rebuild behavior was explored, not an acceptable or delivered canonical path.

## Event Inventory

### Source Publisher vs DigiTwin Consumer Matrix

| Source/change | Integrated source publisher and payload | Integrated DigiTwin consumer/storage | Products impact | Planning status |
|---|---|---|---|---|
| Serialized receiving outcome | Serialization publishes `DigitalTwinReceivingOutcomeEvent` through Dapr from the products Cosmos CFP. Payload includes product/source identity, NDC/lot/expiry and optional `FacilityId`, but no tenant/root ID. Delivery is at least once; the CFP retries, while dedupe is in-memory and same-process only. [`HCC.Inventory.Serialization/src/HCC.Inventory.Serialization.Handlers/Services/ProductsChangeFeedProcessor.cs`] [`HCC.Inventory.Serialization/src/HCC.Inventory.Serialization.Contracts/Events/DigitalTwinReceivingOutcomeEvent.cs`] | Route and handler write Cosmos inventory position state; inventory CFP projects to `ContainerInventorySummary`, `FacilityFormularyAggregate`, and operational state. Tenant is represented downstream as `Guid.Empty`. | Supplies serialized quantities, initially at receiving state/location semantics. | **Integrated end to end for tenantless serialized receiving.** Plan tenant resolution/backfill and durable idempotency; do not create a source-publication story. |
| Formulary create/update/archive/remap | Formulary transactionally writes item/outbox state and publishes integrated `FormularyItemUpsertedEvent` through Dapr plus GCP CDC. Payload carries Formulary identity, identifiers, display/grouping data, active/archive state, and source ordering information. [`HCC.Inventory.Formulary/src/HCC.Inventory.Formulary.Infra/Repositories/FormularyItemRepository.cs`] [`HCC.Inventory.Formulary/src/HCC.Inventory.Formulary.Handlers/Services/FormularyCdcChangeFeedProcessor.cs`] | No integrated DigiTwin route, handler, repository, canonical Formulary document, CFP, or direct writer. | Live grouping, display name, active mapping, and NDC attribution are not maintained from the source event. | **Source integrated / DigiTwin missing.** Implement Formulary Cosmos-first intake and projection; do not adopt the experimental direct-SQL path. |
| Unit/room/storage/container hierarchy, category, Formulary/equivalency, and PAR changes | Container Management publishes integrated per-node GCP topic messages with contract attribute `evt.LocationHierarchyChanged`. Container data includes category, Formulary/equivalency, and PAR; category changes emit `Updated`. The actual body is a `ContainerManagementDataSyncCommand` wrapper with the payload nested, which differs from documentation and must be handled/tested explicitly. [`HCC.Inventory.ContainerManagement/src/HCC.Inventory.ContainerManagement.Infra/Outbox/ContainerOutboxDocumentFactory.cs`] [`HCC.Inventory.ContainerManagement/src/HCC.Inventory.ContainerManagement.Infra/Services/GcpPubsubService.cs`] | No integrated subscriber, route, canonical location writer, or `LocationNode`/`LocationClosure` projector. | Physical dispensability, live hierarchy, names, moves, and exact location filtering cannot be reliable. | **Source integrated / DigiTwin missing.** Build the consumer against the actual wrapper, with bootstrap/reconciliation and stale-event behavior. Do not ask Container Management to republish work it already performs unless a contract change is explicitly agreed. |
| Cycle count completion | Integrated `CycleCountCompletedEvent` contains `RecordId`, facility, container, and total only; it has no item lines. Full item data is available from the CycleCount GET record API. [`HCC.Inventory.CycleCount/src/HCC.Inventory.CycleCount.Handlers/Services/CycleCountChangeFeedProcessor.cs`] [`HCC.Inventory.CycleCount/src/HCC.Inventory.CycleCount.Contracts/Api/Events/CycleCountCompletedEvent.cs`] [`HCC.Inventory.CycleCount/src/HCC.Inventory.CycleCount.Api/Endpoints/CycleCountReadEndpoints.cs`] | No integrated DigiTwin consumer or canonical true-up. | Completed physical counts do not correct Digital Twin inventory. | **Source integrated / DigiTwin missing.** Use completion as an advisory trigger to fetch the final record, then apply an idempotent set-based Cosmos true-up, unless the contract is deliberately enriched. |
| Recall activation/deactivation | Integrated `RecallAlertIngestedEvent` is a snapshot with active/deactive identifier/lot targets. [`HCC.Inventory.Recalls/src/HCC.Inventory.Recalls.Core/Application/Services/RecallIngestionService.cs`] [`HCC.Inventory.Recalls/src/HCC.Inventory.Recalls.Contracts/Api/Events/RecallAlertIngestedEvent.cs`] | No integrated DigiTwin consumer or canonical/projected recall state. | Recall reference data is unavailable to details/alerts. It does not directly define Products dispensability. | **Source integrated / DigiTwin missing.** Persist stateful snapshots keyed so a newer event ID replaces the prior target set and deactivation removes its effect. |
| Tenant/facility metadata | TenantConfig publishes integrated tenant-node CDC via GCP as a full snapshot containing facility metadata, timezone, and GLN data. There is no dedicated integrated `FacilityUpsertedEvent` baseline. The snapshot has no event ID, ordering key, or deletion signal. [`HCC.Inventory.TenantConfig/src`] | No DigiTwin consumer or local canonical facility reference. Current Products scope resolution calls TenantConfig directly. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Adapters/TenantHierarchyClient.cs`] | Facility authorization can work through the live dependency, but local naming/reference projection and replayable tenant ownership are absent. | **Source snapshot integrated / DigiTwin missing.** Decide whether to consume and reconcile full snapshots or retain the direct resolver; do not plan against a nonexistent dedicated facility event. |
| Quarantine removal | Serialization sends an integrated, best-effort, one-to-one RxTransparent `QuarantineItemRemovedOperation`. Failures are swallowed; there is no outbox or Digital Twin fanout. [`HCC.Inventory.Serialization/src/HCC.Inventory.Serialization.Infra/Services/RxTransparentServiceBusClient.cs`] [`HCC.Inventory.Serialization/src/HCC.Inventory.Serialization.Infra/Services/RxTransparent/QuarantineItemRemovedOperation.cs`] | No Digital Twin event or consumer. | A receiving position can remain visible after quarantine workflow removal/classification. | **Digital Twin publication and consumer missing.** Define a durable Digital Twin-facing contract/outbox path and race-safe canonical update; the RxTransparent operation is not sufficient. |
| Putaway, ad hoc add/remove, container movement, restock scan-out/scan-in | No integrated Digital Twin mutation event/API was found. | No canonical mutation path. | Received stock cannot progress reliably into physical containers; physical quantities remain incomplete. | **Missing.** Implement idempotent Cosmos-first operations after location identity/state decisions. |
| Omnicell/PAR/task lifecycle | Proposal documents mention these inputs; no integrated DigiTwin executable path was found. | No current path. | Not required for confirmed Products list fields; potentially relevant to future detail/alert requirements. | **Deferred/unspecified.** Do not couple MVP Products stories to proposal-only inputs. |

## Critical Integrated-Baseline Risks

### 1. Tenant Identity Is Absent End to End

The integrated receiving event and Cosmos position lack tenant/root identity, while SQL projection substitutes `Guid.Empty`. The current scope resolver obtains tenant/facility authorization separately, so canonical inventory and authorized reads do not share a reliable integrated tenant key.

**Required decision:** define the authoritative tenant derivation at intake, update the canonical document and partition/read behavior if required, backfill existing canonical/SQL state, and reject or quarantine events whose tenant cannot be resolved. The experimental tenant-aware path can inform design/tests but is not integrated credit.

### 2. Formulary Has a Publisher but No Canonical DigiTwin Path

The source outbox/publication is integrated. DigiTwin has no integrated consumer, Cosmos model, CFP, or SQL writer. The experimental direct-SQL repository violates the confirmed Cosmos-first rule.

**Required correction:** implement ordered Formulary snapshot intake into Cosmos and a CFP that projects `Formulary` and `NdcFormularyMap`. Do not phrase this as replacing current integrated direct SQL; no such integrated path exists.

### 3. Location Metadata Is Schema-Only

Migrations create `LocationNode`, `LocationClosure`, and `ContainerDim`, but no integrated Container Management consumer/writer populates live hierarchy/category state. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/002_CreateLocationTables.sql`]

Direct SQL fixtures can make location endpoints appear functional without proving source-to-DigiTwin behavior. The actual source message wrapper also differs from documentation, creating an immediate contract-test requirement.

### 4. The Maintained Aggregate Cannot Serve Target Location Semantics

The integrated list reads `FacilityFormularyAggregate`. It cannot represent both target rules from one stored count: one selected facility needs distinct containers, while multiple facilities need distinct facilities. It also lacks container category at the row grain needed to apply dispensability before aggregation.

### 5. Source Delivery and Consumer Idempotency Must Be Designed Together

Serialization is at least once and has only same-process in-memory dedupe before DigiTwin. TenantConfig snapshots lack event identity/order/deletion. Recalls require event-ID replacement semantics. Container Management sends per-node CDC with source-specific wrapper/attributes. Each consumer needs source-appropriate ordering, idempotency, bootstrap, and reconciliation rather than one generic event-handler pattern.

## Current SQL Model

The integrated baseline contains migrations `001`-`005`. Migration `006`, `GroupingKey`, and synthetic-group fields/behavior are experimental and excluded from current capability.

| Structure | Integrated role/evidence | Target direction |
|---|---|---|
| `Formulary` | Facility-local Formulary item with non-null `EquivalencyGroupId`, display name, 340B, and active state. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/001_CreateReferenceTables.sql`] | Keep as a normalized dimension projected from canonical Cosmos Formulary state. Decide whether a separate stable `GroupingKey` is needed for missing-equivalency inputs. |
| `NdcFormularyMap` | Maps facility/NDC/program attribution to a Formulary row. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/001_CreateReferenceTables.sql`] | Keep, with explicit tenant and 340B/program semantics. |
| `LocationNode` | Baseline hierarchy/container schema, without authoritative live source projection. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/002_CreateLocationTables.sql`] | Keep; add checked `ContainerCategory`; use `VirtualPurpose IS NOT NULL` for virtuality; remove redundant `ContainerKind` and `IsVirtual`. |
| `LocationClosure` | Ancestor/descendant hierarchy relation. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/002_CreateLocationTables.sql`] | Keep for general subtree traversal and exact hierarchy behavior. |
| `ContainerDim` | Schema duplicates container metadata; no integrated live writer was found. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/002_CreateLocationTables.sql`] | Remove rather than establish a second container-metadata authority. |
| `ContainerInventorySummary` | Integrated SQL inventory fact at container/NDC/lot/expiry grain. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/003_CreateProjectionTables.sql`] | Keep as the normalized fact; join hierarchy/category dimensions for reads. |
| `FacilityFormularyAggregate` | Integrated maintained aggregate read by Products. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/003_CreateProjectionTables.sql`] | Remove eventually after readers move to the normalized query and representative performance is measured. |
| Recall/operational tables | Baseline migrations contain recall reference and projection/reconciliation structures. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/001_CreateReferenceTables.sql`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Migrations/Scripts/2026/004_CreateOperationalTables.sql`] | Keep useful normalized/operational state; add actual consumers and repair behavior. |

### Synthetic Grouping Decision

The integrated list groups only by non-null `Formulary.EquivalencyGroupId`. There is no integrated `GroupingKey`, `IsSyntheticGroup`, migration `006`, or synthetic fallback. Therefore stable grouping when an upstream equivalency group is unavailable is **missing**, not supported.

The experimental migration/repository uses a Formulary-derived synthetic key. Treat that as a candidate requiring a product/domain decision, event-contract compatibility analysis, canonical Cosmos representation, and migration design. It must not be silently carried into stories as established behavior.

### 340B Attribution Caveat

The target normalized query needs program attribution to choose the correct NDC/Formulary mapping. Physical `ContainerCategory = '340B'` may supply container attribution. Virtual inventory is always dispensable, but its Formulary variant may still require program attribution independent of destination. The integrated receiving event does not carry that value.

Do not remove existing fact-level 340B attribution until the authoritative virtual-inventory rule is confirmed.

## Current Products Read Path

Integrated Products list/details/locations/facility/location endpoints call `SqlProductRepository`. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Api/Endpoints/DigiTwinEndpoints.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Sql/SqlProductRepository.cs`]

The integrated list query:

- reads quantities from `FacilityFormularyAggregate` and labels/grouping from `Formulary`;
- groups only by non-null `EquivalencyGroupId`;
- has no `GroupingKey` or synthetic fallback;
- supports existing search/sort/location behavior present in `SqlProductRepository`;
- has no deterministic pagination/total-count contract; and
- has no dispensability classification or filter.

### Products List Requirement Matrix

| Field/behavior | Integrated status | Gap/target |
|---|---|---|
| Authorized selected-facility scope | **Partially supported.** `ProductScopeResolver` calls TenantConfig directly. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Application/Services/ProductScopeResolver.cs`] | Canonical inventory is tenantless/`Guid.Empty`; align authorization identity with stored facts. |
| One row per equivalency group | **Supported only when integrated Formulary rows have non-null `EquivalencyGroupId`.** | Confirm cross-facility identity semantics and canonical Formulary population. |
| Stable group when equivalency is missing | **Missing.** Integrated schema requires equivalency and has no synthetic fields. | Decide whether missing equivalency is valid. If valid, design canonical and SQL grouping; experimental synthetic grouping is only a candidate. |
| Display description | **Available from integrated SQL state/fixtures.** | Define conflict policy across facility-local names and add live Formulary projection. |
| Total/serialized quantity | **Partially supported.** Serialized receiving projects into aggregate/facts. | Tenant identity, unresolved attribution, and other mutation flows limit completeness. |
| Non-serialized quantity | **Projection substrate exists; writer missing.** | Add an authoritative non-serialized canonical write path if required for MVP. |
| Single-facility container count | **Not reliable for target semantics.** | Compute distinct containers from filtered normalized facts, including an explicit virtual-container decision. |
| Multi-facility facility count | **Not supported by the aggregate semantics.** | Compute distinct facilities after the same fact-level filters. |
| Search and sorting | **Integrated in current repository shape.** | Preserve behavior while adding deterministic tie-breaking and confirming NDC search requirements. |
| Deterministic paging and total count | **Missing.** | Add bounded paging contract, stable secondary key, and total count. |
| Dispensability classification/filter | **Missing.** | Project category/virtual purpose and classify at inventory-container grain before aggregation. |
| Distinct NDC count | **Present only to the extent current active SQL mappings are populated.** | Confirm inactive/zero-stock rules and supply mappings through canonical Formulary projection. |

## Current Product Details Read Path

The integrated `GET /api/digitwin/products/{id}` uses the list's `EquivalencyGroupId`. [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Api/Endpoints/DigiTwinEndpoints.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Sql/SqlProductRepository.cs`]

The integrated detail query currently returns one row per **inventory-bearing NDC** because it starts from `ContainerInventorySummary`. It does not apply active `NdcFormularyMap` or active `Formulary` filtering in the NDC query, and it returns total quantity only at NDC grain. Zero-stock mapped NDCs are omitted. Placeholder DTO fields are not evidence of requirements or available data.

### Product Details Field-Provenance Matrix

| Field/behavior | Requirement certainty | Integrated status |
|---|---|---|
| Product identity | Confirmed: same group as list | **Supported for `EquivalencyGroupId`;** no integrated generalized/synthetic grouping key. |
| Display name | Confirmed for identification | **Supported from SQL Formulary rows;** cross-facility conflict policy remains open and live source projection is missing. |
| One row per NDC | Confirmed | **Partially supported:** one row per inventory-bearing NDC only; no active map/Formulary filter; zero-stock mapped NDCs omitted. |
| Quantity per NDC | Confirmed at least as total | **Supported for attributed inventory facts as total quantity only.** Serialized/non-serialized/location splits remain open requirements. |
| Total/serialized/non-serialized header quantities | Consistent with list, subject to confirmation | **Same integrated limitations as the list.** |
| Proprietary name and ProductMaster fields | Not confirmed | **Requirement/source missing.** Current placeholders are not a contract. |
| NDC cost/value/financial fields | Not confirmed | **Requirement/source missing.** |
| Formulary category | Not confirmed | **Requirement/source missing.** |
| Dispensability on details | List semantics confirmed; detail presentation not confirmed | **Missing.** A mixed product cannot be represented by one boolean without explicit scope semantics. |
| Usage/order/waste/savings | Not confirmed | **Requirement/source missing.** |

### Product Details Questions Still Requiring Product Decisions

Confirm before creating implementation stories:

- Whether each NDC row includes total, serialized, and non-serialized quantity.
- Whether each NDC row includes the same single-facility container or multi-facility facility count.
- Whether the selected dispensability scope from the Products list carries into Product Details.
- Whether an unfiltered detail page shows separate dispensable/non-dispensable quantities when one NDC is present in both classes.
- Whether the table contains every active mapped NDC in the grouping or only NDCs with nonzero inventory.
- Whether duplicate mappings for the same NDC across facilities collapse into one row.
- Whether the NDC table needs sorting, searching, and paging.
- Whether proprietary name or other ProductMaster enrichment is in MVP scope.
- Whether lot, expiry, facility, and container information belongs on this page or in a subsequent details view.
- Whether inactive Formulary/mapping rows must always be excluded and how archived groups resolve.

## Target Architecture

### Target Flow

```mermaid
flowchart LR
    S[Integrated source events and DigiTwin mutation APIs] --> H[Validated idempotent DigiTwin consumers]
    H --> C[(Canonical Cosmos documents)]
    C -->|change feed| P[Projection processors]
    P --> N[(Normalized SQL facts and dimensions)]
    N --> Q[Products list and Product Details queries]
```

Every source-owned change lands in an ordered, replayable Cosmos representation before SQL. SQL can be rebuilt from canonical Digital Twin state without replaying experimental direct-SQL handlers.

### Logical Canonical Document Families

| Logical state | Canonical purpose | Partitioning/ordering constraint |
|---|---|---|
| Inventory positions | Current serialized or quantity position state, active/removed status, source observation identity, and resolved tenant ownership. | Preserve efficient facility/container work while resolving the integrated tenant gap and migration/backfill. |
| Location hierarchy/container attribution | Current node, parent/path, category, virtual purpose, delete state, and source ordering. | Facility-scoped; ingest the actual nested Container Management wrapper and support bootstrap/reconciliation. |
| Formulary references | Current facility-local Formulary snapshot, NDC identifiers, equivalency/grouping, program variant, active/archive, and source version. | Facility-scoped; consume the already-integrated publisher and project through CFP. |
| Recall references | Current event/identifier/lot targets and active state. | Preserve event identity so a later snapshot replaces prior targets and deactivation removes effect. |
| Facility references | Tenant/facility identity, display name, timezone, and GLNs. | Full-snapshot reconciliation must compensate for absent event ID/order/deletion semantics. |
| Idempotent operations | Adjustment/move/restock/true-up operation state. | Support retries and source/destination changes without duplicate quantity. |

### Target SQL Model

| Concept | Target representation | Key behavior |
|---|---|---|
| Inventory fact | `ContainerInventorySummary` at tenant/facility/container/NDC/lot/expiry grain | Absolute projection from canonical positions; no screen-specific totals. |
| Product dimension | `Formulary` | Facility-local state projected from Cosmos. Add a stable grouping key only if the missing-equivalency decision requires it. |
| Product identifier mapping | `NdcFormularyMap` | Active NDC-to-Formulary mapping with explicit program semantics. |
| Location dimension | `LocationNode` | Type, parent, name/path, nullable `VirtualPurpose`, checked `ContainerCategory`, delete state, and source ordering. |
| Hierarchy relation | `LocationClosure` | Ancestor/descendant/direct-child/depth maintained transactionally. |
| Recall reference | `RecallTargetSnapshot` | Event/identifier/lot/active state projected independently of inventory. |
| Operational state | Checkpoints, projection debt, and reconciliation state | Freshness/repair metadata, not business facts. |

Target `LocationNode` rules:

```sql
VirtualPurpose IS NOT NULL  -- virtual container
ContainerCategory IN ('Dispense', '340B', 'Returns', 'Waste')
```

`ContainerKind` and `LocationNode.IsVirtual` should be removed. `ContainerCategory` is Container Management's business-use classification and is not interchangeable with virtual workflow purpose. [`HCC.Inventory.ContainerManagement/src/HCC.Inventory.ContainerManagement.Core/Domain/Enums/ContainerCategory.cs`]

After normalized hierarchy reads are correct and measured, remove duplicated hierarchy/virtual columns from the inventory fact. Retain fact-level program attribution until virtual 340B behavior is defined.

### Target Products Query Shape

1. Select `ContainerInventorySummary` rows for the resolved tenant and authorized facilities.
2. Join active `NdcFormularyMap` and active `Formulary` rows.
3. Join inventory containers to `LocationNode`.
4. Classify each inventory-container row before aggregation:

```text
VirtualPurpose IS NOT NULL                         -> Dispensable
VirtualPurpose IS NULL and category Dispense/340B -> Dispensable
VirtualPurpose IS NULL and category Returns/Waste -> NonDispensable
VirtualPurpose IS NULL and category missing       -> Unknown
```

5. Apply the optional dispensability filter at that grain.
6. Group first by product grouping, facility, and container so lot/NDC rows do not multiply counts.
7. Aggregate product quantities.
8. Return distinct containers for one facility or distinct facilities for multiple facilities.
9. Apply escaped search, deterministic sorting with product identity as a tie-break, total count, and bounded paging.

Unknown physical category must not be silently classified as dispensable. Decide whether `Unknown` is included only in unfiltered results, exposed as a filter, or blocks/flags incomplete projection.

### Target Product Details Query Shape

1. Resolve the same product grouping and authorized facility set as the list.
2. Start from active scoped `NdcFormularyMap` rows if Product confirms that zero-stock mapped NDCs belong.
3. Left join inventory facts and apply the same classification/filter policy as the list.
4. Aggregate the confirmed quantity fields by NDC.
5. Add ProductMaster, cost, usage, order, waste, or savings data only after requirements and authoritative contracts exist.

## Integrated-Baseline Gap Register

| ID | Integrated gap | Consequence | Dependency/planning note |
|---|---|---|---|
| G1 | Receiving/canonical inventory has no tenant; projection uses `Guid.Empty`. | Tenant-scoped facts can collide or fail to align with authorized reads. | Decide identity derivation and migration before relying on end-to-end Products totals. |
| G2 | Formulary publisher is integrated, but DigiTwin intake, Cosmos model, and CFP are missing. | Live grouping/mapping/display state does not reach the read model. | Implement Cosmos-first consumption; do not adopt experimental direct SQL. |
| G3 | Container Management publisher is integrated, but DigiTwin location/category consumer and projection are missing. | Dispensability and live hierarchy/location behavior are unavailable. | Blocks normalized Products query; account for actual wrapper body and reconciliation. |
| G4 | No CycleCount consumer/true-up; completion has no item lines. | Canonical inventory cannot converge to completed physical counts. | Design GET-record fetch, idempotency, and race policy. |
| G5 | Quarantine removal has no durable Digital Twin publication/consumer. | Removed/classified items can remain active. | Requires source durable fanout plus DigiTwin race-safe handling. |
| G6 | No integrated adjustment, movement, putaway, restock, or non-serialized writer. | Inventory lifecycle and total quantity remain incomplete. | Implement after location and identity decisions. |
| G7 | Recall publisher is integrated, but DigiTwin consumer is missing. | Recall details/alerts cannot use live canonical reference state. | Independent of core list; requires event-ID replacement semantics. |
| G8 | TenantConfig full snapshots are not consumed and lack event ID/order/deletion. | Local facility metadata cannot be replayed reliably; current scope resolver remains a live dependency. | Decide consume/reconcile versus retain direct service dependency. |
| G9 | Integrated schema/query has only non-null `EquivalencyGroupId`; no synthetic grouping. | Missing-equivalency behavior is undefined. | Product/domain decision before schema and canonical contract story. |
| G10 | Products lacks paging, total count, category-based dispensability, and correct one-vs-many facility location counts. | Confirmed list behavior is not served. | Implement after G1-G3 and normalized schema changes. |
| G11 | `FacilityFormularyAggregate` encodes insufficient read grain. | It cannot apply classification before aggregation or represent both count semantics. | Move readers to normalized facts; remove FFA eventually after measurement. |
| G12 | Product Details returns inventory-bearing NDCs only, does not filter active map/Formulary rows, and returns total quantity only. | Current behavior does not settle the confirmed one-row-per-NDC contract. | Clarify open fields/zero-stock behavior, then implement the minimal active result. |
| G13 | Integrated seeds directly populate Cosmos setup and SQL fixtures. | Local UI/query success does not prove source-event integration. | Add end-to-end tests while retaining focused SQL fixtures. |

## Dependency-Ordered Planning Candidates

These candidates target integrated gaps. They do not assign already-integrated publisher work to source teams unless a contract change is explicitly required.

### P1. Freeze Identity, Grouping, and Product Contracts

Depends on: none.

- Decide tenant derivation for receiving and canonical backfill behavior.
- Decide whether missing equivalency is valid and, if so, the stable synthetic grouping rule.
- Confirm unknown physical-category, virtual-container count, zero/negative inventory, and virtual 340B behavior.
- Confirm the minimal Product Details response and zero-stock/active-NDC semantics.
- Decide physical Cosmos document families and partition keys for reference state.

### P2. Repair Tenant Identity in the Integrated Receiving Path

Depends on: P1.

- Extend/derive tenant identity at DigiTwin intake using an agreed authoritative source.
- Persist tenant in canonical position state and project it without `Guid.Empty` fallback.
- Define migration/backfill and unresolved-identity quarantine behavior.
- Prove at-least-once redelivery and retries remain idempotent across process restarts.

### P3. Implement Formulary Cosmos-First Intake

Depends on: P1.

- Consume the already-integrated `FormularyItemUpsertedEvent` publisher.
- Persist ordered canonical Formulary snapshots in Cosmos.
- Project `Formulary` and `NdcFormularyMap` transactionally through change feed.
- Preserve active/archive/remap/stale-event behavior.
- Add grouping fields only if P1 requires them.
- Do not adopt the experimental direct-SQL repository or facility aggregate rebuild path.

### P4. Implement Location and Facility Canonical State

Depends on: P1; can proceed with P2/P3 where contracts permit.

- Consume the integrated Container Management CDC using the actual `ContainerManagementDataSyncCommand` wrapper and attributes.
- Persist canonical location/category/virtual-purpose state in Cosmos.
- Project `LocationNode` and `LocationClosure` for create/update/move/delete/category changes.
- Add bootstrap/drift reconciliation and stale-event handling.
- Decide whether TenantConfig full snapshots feed canonical facility state or remain a direct scope dependency; handle absent order/deletion semantics explicitly.

### P5. Replace Products with the Normalized Query

Depends on: P2-P4.

- Add paging, total count, and dispensability contracts.
- Query inventory facts plus active Formulary/NDC mappings and `LocationNode`.
- Implement one-facility container count and multi-facility facility count.
- Apply classification/filtering before aggregation.
- Measure and add indexes from execution plans rather than assumptions.

### P6. Complete Canonical Inventory Mutation and True-Up Paths

Depends on: P1, P2, and sufficient P4 location state.

- Implement idempotent adjustment and source/destination movement operations.
- Implement receiving putaway/restock transitions.
- Consume integrated CycleCount completion, fetch the final record, and apply an idempotent set-based true-up.
- Add durable Digital Twin quarantine publication and consumption with race-safe convergence.
- Add an authoritative non-serialized write path if in MVP scope.

### P7. Add Independent Recall Reference Flow

Depends on: P1; can run in parallel with P5/P6.

- Consume integrated recall snapshots into canonical Cosmos state.
- Replace prior targets by event ID and handle deactivation.
- Project recall references without inventory reprojection.

### P8. Implement the Minimal Product Details Contract

Depends on: P5 and Product Details decisions from P1.

- Return one row per confirmed NDC scope using active mappings and the agreed zero-stock behavior.
- Reuse list grouping, tenant/facility scope, and classification semantics.
- Return only confirmed quantity/detail fields.
- Remove or explicitly defer fields without authoritative requirements/sources.

### P9. Retire Aggregate and Duplicate Metadata After Proof

Depends on: P5 and P8, plus a reader search and representative performance results.

- Remove `FacilityFormularyAggregate` reads, maintenance, and table.
- Remove unused `ContainerDim`.
- Remove duplicated inventory-fact hierarchy/virtual columns only after normalized joins are correct and measured.
- Keep the normalized query as the correctness oracle for any future measured aggregate.

### P10. Complete Reliability, Seed, and End-to-End Coverage

Depends on: P2-P9 incrementally.

- Add source-contract tests for exact payloads/wrappers and replay behavior.
- Add canonical-to-SQL reconciliation and projection recovery.
- Seed representative canonical documents for end-to-end tests while retaining focused direct-SQL query fixtures.
- Measure normalized query latency at representative facility/container/NDC/lot cardinality.
- Introduce a materialized aggregate only if measured performance misses an agreed objective.

Each implementation story should include Research, Implementation, Testing, Code Review/PR, and QA tasks. QA assignment remains open for the QA team.

## Verification and Test Plan

### Integrated Coverage That Can Be Credited

| Area | Baseline evidence |
|---|---|
| Receiving route/handler and canonical inventory repository | [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Handlers/Services/DigitalTwinReceivingOutcomeEventHandler.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/InventoryPositionRepository.cs`] |
| Inventory CFP/projector and SQL projection repository | [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Handlers/Services/DigiTwinInventoryProjectionProcessor.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Core/Application/Services/ContainerInventoryProjector.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Repositories/ContainerProjectionRepository.cs`] |
| Products endpoint/repository substrate | [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Api/Endpoints/DigiTwinEndpoints.cs`] [`HCC.Inventory.DigiTwin/src/HCC.Inventory.DigiTwin.Infra/Sql/SqlProductRepository.cs`] |
| Direct local Cosmos/SQL fixture setup | [`HCC.Inventory.DigiTwin/LocalSeeder/DigiTwinLocalSeeder.cs`] [`HCC.Inventory.DigiTwin/LocalSeeder/DigiTwinSqlSeeder.cs`] |

Tests for tenant-aware CFP behavior, Formulary handlers/repositories, synthetic grouping, or migration `006` are **experimental** if they exist only in the working tree and are not integrated coverage.

### Missing Coverage to Add

| Scenario | Required assertion |
|---|---|
| Serialization contract and tenancy | Exact integrated payload is accepted; optional/missing facility and unresolved tenant behavior are explicit; no `Guid.Empty` cross-tenant facts remain. |
| At-least-once receiving | Retry/redelivery across process restart does not duplicate quantity; stale observations do not win. |
| Formulary event through Cosmos | Integrated source event updates canonical snapshot, active mappings, archive/remap behavior, and SQL without direct event-to-SQL writes. |
| Container Management wrapper | Consumer handles actual nested command body and attributes, including category-only `Updated` events. |
| Location create/move/delete | Canonical state and closure rows converge under duplicate/stale messages and bootstrap reconciliation. |
| Mixed dispensability | One product across Dispense/340B/Returns/Waste and virtual containers returns correct filtered quantities/counts. |
| Single/multiple facility counts | Distinct containers for one facility and distinct facilities for multiple facilities. |
| Paging | Stable total count and no duplicate/missing results across sort ties. |
| Product Details NDCs | Active filtering and agreed zero-stock behavior produce exactly one row per NDC with only confirmed fields. |
| CycleCount true-up | Completion fetches final item data and converges idempotently under duplicate/racing observations. |
| Quarantine race | Removal before/after receiving converges to the same canonical state. |
| Recall replacement/deactivation | Event-ID snapshots replace prior targets and project without inventory rewrite. |
| TenantConfig reconciliation | Full snapshots converge despite absent event ID/order/deletion signal, or the direct dependency is explicitly retained/tested. |

## Seed Data Direction

The integrated local seeders directly seed Cosmos setup and SQL fixtures. That is useful for focused local/query tests but does not prove external-event integration.

Representative target fixtures should cover:

- at least two tenants once tenant identity is implemented;
- at least two facilities sharing one equivalency/grouping key;
- multiple containers in one facility;
- physical `Dispense`, `340B`, `Returns`, and `Waste` containers;
- receiving/restock virtual containers with non-null `VirtualPurpose`;
- mixed dispensable/non-dispensable inventory;
- serialized and non-serialized facts;
- multiple lots/expiries and multiple NDCs;
- zero-stock mapped NDCs if Product confirms them;
- missing equivalency only if P1 permits it, using the agreed grouping rule;
- missing category and unresolved tenant/NDC cases;
- hierarchy move/delete and deterministic paging ties.

Seed through canonical Cosmos documents and projection processors for end-to-end tests where practical. Keep direct SQL fixtures for focused repository tests, but never use them as the only evidence that source events populate the read model.

## Recommended Direction

Use normalized SQL facts and dimensions as the MVP correctness model:

- Cosmos remains canonical for every external event and mutation API.
- `ContainerInventorySummary` remains the SQL inventory fact.
- `Formulary` and `NdcFormularyMap` remain product dimensions projected from canonical Formulary state.
- `LocationNode` owns category and virtual purpose; `LocationClosure` owns hierarchy relationships.
- Products derives dispensability and location counts from joined facts at read time.
- Product Details initially serves only confirmed fields, especially one row per NDC under the agreed active/zero-stock rule.
- `FacilityFormularyAggregate`, `ContainerDim`, and redundant metadata are removed eventually, only after normalized readers/tests land and representative performance is measured.
- Experimental direct Formulary SQL, synthetic grouping, and tenant-aware prototypes may inform implementation but are not integrated capability or architecture commitments.