Skip to main content

Item Store LastCountValue and LastCountDate Fields Implementation Plan

Linked Work Items​

  • User Story 207661: Spike: Technical Documentation for Using Last Count Date and Last Count in Publix Pro Chat

Overview​

Implement support for two new fields (LastCountValue and LastCountDate) coming from the Item Store DBWS job feed. These fields will be ingested nightly into the ItemStore table and made available to Publix Pro Chat for answering queries about item count information (e.g., "What item has the highest count date in this aisle?" or "List items with the highest count date").

Current State Analysis​

Existing Infrastructure​

Item Store ETL Pipeline:

  • Source: ItemStoreFeed Delta Lake feed from ADLS (abfss://publixpro-idm-feed@<storage>.dfs.core.windows.net/ItemStoreFeed)
  • Notebook: src/etl/notebooks/main/item-store-full-refresh.py
  • Staging Table: ItemStoreStaging
  • Target Table: ItemStore
  • Job Schedule: Currently manual/on-demand (documented as nightly in some jobs)
  • Processing Pattern: Full refresh with deduplication, staging table merge pattern

Item Store Table Schema:

CREATE TABLE [dbo].[ItemStore]
(
    [StoreNumber] [int] NOT NULL,
    [ItemCode] [int] NOT NULL,
    [ActivationStatus] [varchar](50) NULL,
    [RetailPrice] [nvarchar](max) NULL,
    [RssCode] [int] NULL,
    [RssDesc] [nvarchar](max) NULL,
    [SaleDescription] [nvarchar](max) NULL,
    [LastDeliveryDate] [date] NULL,
    [IsBOGO] [bit] NOT NULL,
    [IsOnSale] [bit] NOT NULL,
    [CasePack] [decimal](10, 2) NULL,
    [LastUpdatedDateItemStoreInfo] [datetime] NULL,
    [LastUpdatedDateLocationInfo] [datetime] NULL,
    [BalanceOnHandUom] [nvarchar](5) NULL,
    [SellableUom] [nvarchar](5) NULL,
    [SaleRanking] [float] NOT NULL DEFAULT(1000),
    [CreatedBy] [nvarchar](50) NOT NULL,
    [LastUpdatedBy] [nvarchar](50) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [LastUpdatedDateRow] [datetime] NOT NULL,
    CONSTRAINT [UQ_ItemStore_StoreNumber_ItemCode] UNIQUE CLUSTERED 
    (
        [StoreNumber] ASC,
        [ItemCode] ASC
    )
)

Domain Entity (C#):

public class ItemStore : Item
{
    public int StoreNumber { get; init; }
    public string? ActivationStatus { get; set; }
    public string? RetailPrice { get; set; }
    // ... other properties
    public string? LastDeliveryDate { get; set; }
    // NEW FIELDS WILL GO HERE
}

Desired End State​

Database Schema​

ItemStore and ItemStoreStaging tables will include:

  • LastCountValue [decimal](11, 4) NULL - The count value from the latest inventory count
  • LastCountDate [datetime] NULL - The date/time when the count was performed

Application Layer​

  • C# domain entity ItemStore updated with nullable properties
  • Repository methods return count fields
  • Publix Pro Chat queries can filter/sort by count date and value

ETL Pipeline​

  • Item Store Full Refresh job reads and ingests the new fields
  • Fields properly transformed and validated
  • Staging → Production merge handles new columns

Publix Pro Chat Query Capabilities​

Model can answer questions like:

  • "What item has the highest count date in aisle 5?"
  • "List all items counted in the last 24 hours"
  • "Show items with the most recent count date"
  • "Which items have not been counted recently?"
  • "What is the last count date for items in RSS 123?"
  • "Show me all items with recent count dates in the Dairy RSS"

Verification​

  • Query ItemStore table and confirm fields are populated
  • Run test queries through the application API
  • Ask Publix Pro Chat count-related questions and validate responses
  • Verify nightly refresh updates the fields

What We're NOT Doing​

  • Creating a separate table for count history (only storing latest count)
  • Implementing count trending or historical analysis
  • Building UI-specific count visualizations
  • Adding manual count entry functionality
  • Modifying the source DBWS job (upstream system)
  • Changing the BalanceOnHand table structure
  • Implementing real-time count updates (nightly refresh only)
  • Adding count-specific business logic or validation rules
  • Creating dedicated count-focused API endpoints

Implementation Approach​

Use the established staging table merge pattern:

  1. Add columns to database schema (staging first, then production)
  2. Update ETL notebook to read and transform new fields
  3. Update domain entities and repositories
  4. Enhance Publix Pro Chat query capabilities to surface count information
  5. Test end-to-end with sample data

Phase 1: Database Schema Updates​

Overview​

Add LastCountValue and LastCountDate columns to ItemStoreStaging and ItemStore tables to support the new fields from the upstream feed.

Changes Required​

1. Update ItemStoreStaging Table​

File: src/db/ProChatDB/Tables/ItemStoreStaging.sql

Add two new columns after existing inventory-related fields (after BalanceOnHandUom and SellableUom):

ALTER TABLE [dbo].[ItemStoreStaging]
ADD 
    [LastCountValue] [decimal](11, 4) NULL,
    [LastCountDate] [datetime] NULL;
GO

Rationale:

  • decimal(11, 4) matches the precision of BalanceOnHand table's count field
  • datetime matches BalanceOnHandLastCountDate pattern
  • NULL allows for items that haven't been counted yet
  • Placed near other inventory fields for logical grouping

2. Update ItemStore Production Table​

File: src/db/ProChatDB/Tables/ItemStore.sql

Add the same columns to the production table:

ALTER TABLE [dbo].[ItemStore]
ADD 
    [LastCountValue] [decimal](11, 4) NULL,
    [LastCountDate] [datetime] NULL;
GO

3. Add Index for Count Date Queries​

File: src/db/ProChatDB/Tables/ItemStore.sql

Create a non-clustered index to optimize count date queries:

CREATE NONCLUSTERED INDEX [IX_ItemStore_LastCountDate] 
ON [dbo].[ItemStore] ([LastCountDate] DESC)
INCLUDE ([LastCountValue])
GO

Rationale:

  • Descending order for "most recent count" queries
  • Covering index includes LastCountValue to avoid key lookups
  • Similar to existing IX_BalanceOnHand_LastUpdatedDateUtc pattern

4. Update Merge Stored Procedure (if exists)​

File: src/db/ProChatDB/Stored Procedures/CleanupItemStoreTable.sql

Check if the cleanup procedure needs to handle the new columns. Review merge logic to ensure new fields are included in the merge operation.

Success Criteria​

Automated Verification:​

  • SQL scripts execute without errors
  • Tables have the new columns with correct data types
  • Index is created successfully
  • SELECT * FROM ItemStoreStaging includes new columns
  • SELECT * FROM ItemStore includes new columns

Manual Verification:​

  • Column definitions match specification (decimal(11, 4), datetime, NULL)
  • Index exists: sp_helpindex 'ItemStore' shows IX_ItemStore_LastCountDate
  • No data loss in existing columns
  • Existing queries continue to work

Phase 2: ETL Pipeline Updates​

Overview​

Update the Item Store Full Refresh Databricks notebook to read, transform, and load the new LastCountValue and LastCountDate fields from the ItemStoreFeed.

Changes Required​

1. Update Notebook Field Mapping​

File: src/etl/notebooks/main/item-store-full-refresh.py

Locate the section where ItemStoreFeed columns are read and add the new fields to the selection:

# After existing ItemStoreFeed read operation (around line 100-120)
item_store_feed = spark.read.format("delta").load(item_store_feed_url)

# Ensure new fields are selected (if not using SELECT *)
item_store_selected = item_store_feed.select(
    "ItemCode",
    "StoreNumber",
    "ActivationStatus",
    "RetailPrice",
    # ... existing fields ...
    "LastCountValue",      # NEW
    "LastCountDate",       # NEW
    "LastUpdatedDateTime",
    # ... remaining fields ...
)

2. Add Field Validation/Transformation​

File: src/etl/notebooks/main/item-store-full-refresh.py

Add transformation logic after the existing transformations (around line 140-160):

from pyspark.sql.functions import col, when

# Add after existing transformations (retail price cleaning, RSS splitting, etc.)

# Log presence of count data
count_data_present = item_store_feed.filter(
    col("LastCountValue").isNotNull() | col("LastCountDate").isNotNull()
).count()

applogger.trace(f"Records with count data: {count_data_present} out of {item_store_feed.count()}")

# Optional: Add data quality check
# Validate that LastCountDate is reasonable (not in future, not too old)
from pyspark.sql.functions import current_timestamp, datediff

item_store_validated = item_store_feed.withColumn(
    "LastCountDate",
    when(
        col("LastCountDate").isNotNull() & 
        (datediff(current_timestamp(), col("LastCountDate")) < 0),
        None  # Null out future dates
    ).otherwise(col("LastCountDate"))
)

3. Update Column Mapping for SQL Write​

File: src/etl/notebooks/main/item-store-full-refresh.py

Ensure the new columns are included in the write operation to ItemStoreStaging:

# Verify columns match the staging table schema
final_columns = [
    "StoreNumber",
    "ItemCode",
    "ActivationStatus",
    "RetailPrice",
    # ... existing columns ...
    "LastCountValue",      # NEW
    "LastCountDate",       # NEW
    "CreatedBy",
    "LastUpdatedBy",
    "CreatedDate",
    "LastUpdatedDateRow"
]

item_store_final = item_store_validated.select(*final_columns)

4. Update Telemetry Logging​

File: src/etl/notebooks/main/item-store-full-refresh.py

Add telemetry to track count data ingestion:

# Add after row count telemetry
applogger.trace("Count data statistics:")
applogger.trace(f"  - Records with LastCountValue: {item_store_final.filter(col('LastCountValue').isNotNull()).count()}")
applogger.trace(f"  - Records with LastCountDate: {item_store_final.filter(col('LastCountDate').isNotNull()).count()}")
applogger.trace(f"  - Average LastCountValue (where not null): {item_store_final.agg({'LastCountValue': 'avg'}).collect()[0][0]}")

Success Criteria​

Automated Verification:​

  • Notebook executes without errors: Run job in DEV environment
  • Databricks job completes successfully
  • Application Insights shows telemetry for count fields
  • ItemStoreStaging table populated with count data

Manual Verification:​

  • Inspect staging table: SELECT TOP 100 StoreNumber, ItemCode, LastCountValue, LastCountDate FROM ItemStoreStaging WHERE LastCountDate IS NOT NULL
  • Verify data quality: No future dates in LastCountDate
  • Check telemetry logs in Application Insights for count statistics
  • Confirm row counts match expected feed size
  • Validate that existing fields are not impacted

Phase 3: Domain Entity and Repository Updates​

Overview​

Update C# domain entities and repository methods to expose the new count fields to the application layer.

Changes Required​

1. Update ItemStore Domain Entity​

File: src/services/Domain/Entities/Item/ItemStore.cs

Add two new properties after existing inventory-related properties:

namespace Publix.S0ITMOLM.PProChat.Domain.Entities.Item;

public class ItemStore : Item
{
    public int StoreNumber { get; init; }
    public string? ActivationStatus { get; set; }
    public string? RetailPrice { get; set; }
    // ... existing properties ...
    public string? BalanceOnHandUom { get; init; }
    public string? SellableUom { get; set; }
    
    // NEW: Inventory count information
    public decimal? LastCountValue { get; set; }
    public DateTime? LastCountDate { get; set; }
    
    public string? LastDeliveryDate { get; set; }
    // ... remaining properties ...
}

Rationale:

  • Nullable types match database schema
  • decimal? for LastCountValue (matches DB precision)
  • DateTime? for LastCountDate (C# datetime maps to SQL datetime)
  • Grouped with other inventory fields for readability

2. Update ItemStoreRepository​

File: src/services/Infrastructure/Repositories/ItemStoreRepository.cs

Update the SQL query to include new fields in the SELECT statement:

// Locate the query that selects from ItemStore table (likely in GetByStoreAndItems or similar)
// Add LastCountValue and LastCountDate to the field list

private const string SelectItemStoreQuery = @"
    SELECT 
        is.StoreNumber,
        is.ItemCode,
        is.ActivationStatus,
        is.RetailPrice,
        -- ... existing fields ...
        is.BalanceOnHandUom,
        is.SellableUom,
        is.LastCountValue,     -- NEW
        is.LastCountDate,      -- NEW
        is.LastDeliveryDate,
        -- ... remaining fields ...
    FROM ItemStore is
    -- ... joins and WHERE clause ...
";

Update Dapper mapping if explicit mapping is used:

// If using manual mapping (check existing patterns in the repository)
reader.Read<ItemStore>(row => new ItemStore
{
    StoreNumber = row.GetInt32("StoreNumber"),
    ItemCode = row.GetInt32("ItemCode"),
    // ... existing mappings ...
    LastCountValue = row.IsDBNull("LastCountValue") ? null : row.GetDecimal("LastCountValue"),
    LastCountDate = row.IsDBNull("LastCountDate") ? null : row.GetDateTime("LastCountDate"),
    // ... remaining mappings ...
});

3. Update Repository Interface (if defined)​

File: src/services/Application/Abstractions/Repositories/IItemStoreRepository.cs

No changes needed unless new query methods are added specifically for count data. Existing methods will automatically return the new fields via the updated entity.

4. Update Unit Tests​

File: src/services/Tests/Publix.S0ITMOLM.PProChat.UnitTests/Domain/Entities/ItemStoreTests.cs

Add test cases for new properties:

[Fact]
public void ItemStore_ShouldHaveLastCountValue()
{
    // Arrange & Act
    var itemStore = new ItemStore
    {
        StoreNumber = 123,
        ItemCode = 456,
        LastCountValue = 42.5m,
        LastCountDate = DateTime.Parse("2025-12-01T10:30:00"),
        BalanceOnHand = "50.0"
    };

    // Assert
    Assert.Equal(42.5m, itemStore.LastCountValue);
    Assert.Equal(DateTime.Parse("2025-12-01T10:30:00"), itemStore.LastCountDate);
}

[Fact]
public void ItemStore_LastCountFields_CanBeNull()
{
    // Arrange & Act
    var itemStore = new ItemStore
    {
        StoreNumber = 123,
        ItemCode = 456,
        LastCountValue = null,
        LastCountDate = null
    };

    // Assert
    Assert.Null(itemStore.LastCountValue);
    Assert.Null(itemStore.LastCountDate);
}

5. Update Repository Tests​

File: src/services/Tests/Publix.S0ITMOLM.PProChat.UnitTests/Infrastructure/Repositories/ItemStoreRepositoryTests.cs

Update test data setup to include count fields:

// In test setup methods, add count data to mock results
var mockItemStoreData = new ItemStore
{
    StoreNumber = 1,
    ItemCode = 100,
    ActivationStatus = "Orderable",
    // ... existing test data ...
    LastCountValue = 25.5m,
    LastCountDate = DateTime.Parse("2025-12-12T08:00:00")
};

Success Criteria​

Automated Verification:​

  • Build completes successfully: dotnet build
  • Unit tests pass: dotnet test
  • No compilation errors or warnings
  • Code coverage maintained or improved

Manual Verification:​

  • ItemStore entity has LastCountValue and LastCountDate properties
  • Properties are nullable as expected
  • Repository returns count data when querying ItemStore
  • Test data includes count field examples
  • Swagger/API docs reflect new fields (if auto-generated)

Phase 4: Publix Pro Chat Query Enhancement​

Overview​

Enable Publix Pro Chat to understand and answer queries about item count information by ensuring count fields are available in query responses and adding query examples.

Changes Required​

1. Review Item Projection Service​

File: src/services/Application/Common/Services/ItemProjectionService.cs

Verify that the service projects all ItemStore fields, including the new count fields, to the response DTOs. No changes should be needed if the service uses automapper or full entity projection.

// Verify the projection includes new fields
// If manual mapping is done, add:
public ItemDto ProjectItemStore(ItemStore itemStore)
{
    return new ItemDto
    {
        StoreNumber = itemStore.StoreNumber,
        ItemCode = itemStore.ItemCode,
        // ... existing fields ...
        LastCountValue = itemStore.LastCountValue,
        LastCountDate = itemStore.LastCountDate,
        // ... remaining fields ...
    };
}

2. Update Inventory Search Tool Implementation​

File: src/services/Application/Common/OpenAI/ToolImplementations/SearchAggregateInventoryLevelsToolImplementation.cs

Ensure the tool returns count information when providing inventory data:

// In the search result building logic, include count fields
var inventoryInfo = new
{
    itemCode = item.ItemCode,
    itemName = item.ItemName,
    balanceOnHand = item.BalanceOnHand,
    lastCountValue = item.LastCountValue,      // NEW
    lastCountDate = item.LastCountDate,        // NEW
    lastDeliveryDate = item.LastDeliveryDate,
    // ... other fields ...
};

3. Update Item Catalog Search Tool​

File: src/services/Application/Common/OpenAI/ToolImplementations/SearchItemCatalogToolImplementation.cs

If this tool provides item details, ensure count fields are included:

// Verify count fields are in the response structure
// Add to result object if not already present

4. Update OpenAI Response Definitions​

File: src/services/Application/Common/OpenAI/ResponseDefinitions/InventorySearchResponseDefinition.cs

Add count fields to the response schema that defines what Publix Pro Chat can access:

// Add to the schema definition
public class InventoryItemResponse
{
    public int ItemCode { get; set; }
    public string ItemName { get; set; }
    // ... existing properties ...
    public decimal? LastCountValue { get; set; }      // NEW
    public DateTime? LastCountDate { get; set; }      // NEW
    public string? LastDeliveryDate { get; set; }
    // ... remaining properties ...
}

5. Add Query Example to System Prompt (Optional)​

File: Look for system prompt configuration (may be in configuration files or OpenAI setup)

Add examples of count-related queries to help the model understand capabilities:

Example queries you can answer:
- "What item has the highest count date in aisle 5?"
- "List items counted in the last 24 hours"
- "Which items have the most recent count?"
- "Show items with count date after December 1st"
- "What is the last count date for items in RSS 123?"
- "Show me items with recent count dates in the Dairy RSS"
- "Which RSS has items with the oldest count dates?"

Success Criteria​

  • Build completes: dotnet build
  • API returns count fields in item responses
  • Response schemas include LastCountValue and LastCountDate
  • No serialization errors
  • Ask Publix Pro Chat: "What items have been counted recently in store 1?"
  • Ask Publix Pro Chat: "Show me the item with the highest count date in aisle 3"
  • Verify Publix Pro Chat uses count data in its responses
  • Check that null count values are handled gracefully

Phase 5: Testing and Documentation​

Overview​

Comprehensive end-to-end testing of the count fields integration and documentation updates.

Testing Strategy​

1. Database Testing​

Manual SQL Queries:

-- Verify data exists
SELECT TOP 100 
    StoreNumber, 
    ItemCode, 
    LastCountValue, 
    LastCountDate,
    LastUpdatedDateRow
FROM ItemStore 
WHERE LastCountDate IS NOT NULL
ORDER BY LastCountDate DESC;

-- Check count date distribution
SELECT 
    CAST(LastCountDate AS DATE) as CountDate,
    COUNT(*) as ItemsCounted,
    AVG(LastCountValue) as AvgCount,
    MIN(LastCountValue) as MinCount,
    MAX(LastCountValue) as MaxCount
FROM ItemStore
WHERE LastCountDate IS NOT NULL
GROUP BY CAST(LastCountDate AS DATE)
ORDER BY CountDate DESC;

-- Verify recent counts for a specific store
SELECT 
    i.ItemCode,
    i.ItemName,
    is.LastCountValue,
    is.LastCountDate,
    is.BalanceOnHand
FROM ItemStore is
INNER JOIN Item i ON is.ItemCode = i.ItemCode
WHERE is.StoreNumber = 1 
  AND is.LastCountDate IS NOT NULL
  AND is.LastCountDate >= DATEADD(day, -7, GETDATE())
ORDER BY is.LastCountDate DESC;

Validation Checks:

  • No future dates in LastCountDate
  • LastCountValue is reasonable (>= 0, typically < 10000)
  • Count dates align with expected refresh schedule
  • Null values exist for uncounted items

2. ETL Pipeline Testing​

Steps:

  1. Run Item Store Full Refresh job in DEV environment
  2. Monitor Databricks job execution
  3. Check Application Insights for telemetry
  4. Verify staging table population
  5. Confirm production table merge

Verification:

  • Job completes without errors
  • Telemetry shows count field statistics
  • Row counts match expectations
  • No data quality issues flagged

3. Application API Testing​

Test Cases:

# 1. Get item with count data
curl -X GET "https://{api-base-url}/api/search?storeNumber=1&itemCode=12345" \
  -H "Authorization: Bearer {token}"

# Expected: Response includes lastCountValue and lastCountDate

# 2. Search items in a location
curl -X POST "https://{api-base-url}/api/search" \
  -H "Authorization: Bearer {token}" \
  -H "Content-Type: application/json" \
  -d '{
    "storeNumber": 1,
    "query": "items in aisle 5",
    "includeInventory": true
  }'

# Expected: Items include count information

Validation:

  • API returns LastCountValue as decimal or null
  • API returns LastCountDate as ISO 8601 datetime or null
  • Serialization handles nulls correctly
  • Response time not significantly impacted

4. Publix Pro Chat Testing​

Test Queries:

QueryExpected Behavior
"What items were counted today in store 1?"Returns items with LastCountDate = today
"Show me the item with the most recent count date in aisle 5"Returns item with MAX(LastCountDate) in that aisle
"List items counted in the last 48 hours"Filters by LastCountDate >= 2 days ago
"Which items have a count value over 50?"Filters by LastCountValue > 50
"Has item 12345 been counted recently?"Shows LastCountDate for that item
"What is the last count date for items in RSS 123?"Returns items in RSS 123 with their LastCountDate
"Show items with recent count dates in the Dairy RSS"Filters items by RSS description and recent count dates
"Which RSS has items with the oldest count dates?"Groups by RSS and shows MIN(LastCountDate)

Validation:

  • Model understands "count date" and "count value" terminology
  • Model can filter by count date ranges
  • Model can sort by count date (most recent first)
  • Model handles items with null count data gracefully
  • Model provides accurate count information in responses
  • Model can filter count queries by RSS code or RSS description
  • Model can aggregate count data by RSS

5. Integration Testing​

End-to-End Scenario:

  1. Run nightly ETL job
  2. Verify count data in database
  3. Query API for item with count data
  4. Ask Publix Pro Chat count-related question
  5. Validate response accuracy

Verification:

  • Data flows from feed → staging → production
  • Application layer accesses count data correctly
  • Publix Pro Chat leverages count data in responses
  • No errors in any layer

Documentation Updates​

1. Update ETL Job Documentation​

File: docs/etl/jobs/item-store-full-refresh.md

Add to the Schemas section:

| ItemStoreFeed | LastCountValue | Count value from latest inventory count |
| ItemStoreFeed | LastCountDate | Datetime when count was performed |

Add to Processing Steps:

19. Include LastCountValue and LastCountDate fields in ItemStore output
20. Log count data statistics for monitoring

2. Update API Documentation​

File: docs/api/search-endpoints.md (or similar)

Add to response schema:

### ItemStore Response Fields
- `lastCountValue` (decimal, nullable): The inventory count value from the most recent count
- `lastCountDate` (datetime, nullable): The date and time when the item was last counted

3. Update Domain Entity Documentation​

Add XML comments to the C# properties:

/// <summary>
/// The count value from the most recent inventory count for this item at this store.
/// </summary>
public decimal? LastCountValue { get; set; }

/// <summary>
/// The date and time when this item was last counted at this store.
/// Refreshed nightly from the Item Store feed.
/// </summary>
public DateTime? LastCountDate { get; set; }

4. Create README for Count Fields​

File: docs/features/inventory-count-tracking.md (NEW)

# Inventory Count Tracking

## Overview
The Item Store table tracks inventory count information for items at each store location.

## Fields
- **LastCountValue**: The count quantity from the most recent physical inventory count
- **LastCountDate**: When the count was performed

## Data Source
- Feed: ItemStoreFeed (DBWS Job)
- Refresh: Nightly via Item Store Full Refresh job
- Storage: ItemStore table

## Query Examples
- "What items were counted today?"
- "Show the most recently counted items"
- "Which items have a high count value?"
- "What is the last count date for items in RSS 123?"
- "Show items in the Dairy RSS with recent count dates"

## Notes
- Fields are nullable (items may not have been counted)
- Only latest count is stored (no historical tracking)
- Refreshed nightly with full feed load

Success Criteria​

Automated Verification:​

  • All unit tests pass: dotnet test
  • Build completes without warnings: dotnet build
  • ETL job runs successfully in DEV/TST
  • API integration tests pass (if they exist)

Manual Verification:​

  • Database queries return count data
  • ETL job telemetry shows count field statistics
  • API responses include count fields
  • Publix Pro Chat answers count-related questions accurately
  • Documentation updated and reviewed
  • No performance degradation

Nightly Refresh Considerations​

Current State​

The Item Store Full Refresh job is documented as running nightly, though the trigger configuration shows manual/on-demand in all environments.

Validation Needed​

Confirm with team: Is nightly refresh acceptable for count data?

  • If yes: No changes needed, document refresh schedule
  • If no: Consider adding real-time count update capability (out of scope)

Refresh Schedule Impact​

Acceptable Scenarios:

  • Count data is informational (not transactional)
  • Day-old count data is sufficient for queries
  • Counts are performed during business hours and reflected next day

Risk Scenarios:

  • If counts need to be immediately available
  • If intra-day count updates are critical
  • If users expect real-time count information

Recommendation​

Nightly refresh is acceptable given:

  • Similar pattern used for BalanceOnHand (also updated nightly)
  • Count data is typically static after EOD processing
  • Publix Pro Chat queries are informational, not transactional
  • Infrastructure already supports nightly batch updates

Document clearly: "Count data is refreshed nightly. Counts performed today will be visible in the system the following day."

Performance Considerations​

Database Impact​

Index Strategy:

  • Add IX_ItemStore_LastCountDate for date-based queries
  • Include LastCountValue in covering index
  • Similar to existing IX_BalanceOnHand_LastUpdatedDateUtc pattern

Storage Impact:

  • LastCountValue: decimal(11, 4) = 9 bytes per row
  • LastCountDate: datetime = 8 bytes per row
  • Total: ~17 bytes per row
  • For 1M rows: ~16 MB additional storage (negligible)

Query Performance:

  • Date range queries: Optimized by index
  • Sorting by count date: Index supports DESC order
  • Filtering by count value: Covered in index

ETL Impact​

Processing Time:

  • Minimal: Just two additional columns in SELECT
  • No complex transformations required
  • Validation overhead is negligible

Data Volume:

  • Same row count as existing ItemStore feed
  • No additional joins or lookups needed

Application Impact​

API Response Size:

  • Additional ~30 bytes per item (decimal + datetime)
  • For 100-item response: ~3 KB increase (negligible)

Serialization:

  • Standard JSON serialization for decimal/datetime
  • No custom converters needed

Memory:

  • Negligible impact on entity size
  • No caching implications

Rollback Plan​

If issues arise after deployment:

Database Rollback​

-- Remove index
DROP INDEX [IX_ItemStore_LastCountDate] ON [dbo].[ItemStore];

-- Remove columns (WARNING: data loss)
ALTER TABLE [dbo].[ItemStore] DROP COLUMN [LastCountValue];
ALTER TABLE [dbo].[ItemStore] DROP COLUMN [LastCountDate];
ALTER TABLE [dbo].[ItemStoreStaging] DROP COLUMN [LastCountValue];
ALTER TABLE [dbo].[ItemStoreStaging] DROP COLUMN [LastCountDate];

Code Rollback​

  1. Revert C# entity changes
  2. Revert repository query updates
  3. Revert ETL notebook changes
  4. Redeploy previous application version

ETL Rollback​

  1. Re-run job with previous notebook version
  2. Monitor for errors
  3. Verify existing fields still populate

Note: Column removal causes data loss. Consider leaving columns but nulling values if rollback is temporary.

Migration Notes​

Deployment Order​

  1. Phase 1: Database - Add columns to staging and production tables
  2. Phase 2: ETL - Update notebook to populate new fields
  3. Phase 3: Application - Deploy code with entity/repository updates
  4. Phase 4: Publix Pro Chat - Enable count-based queries
  5. Phase 5: Testing - Validate end-to-end

Environment Progression​

  1. DEV: Full implementation and testing
  2. TST: Validation with test data
  3. STG: Pre-production verification
  4. PRD: Production deployment

Data Population Timeline​

  • Day 0: Deploy database schema changes
  • Day 0: Deploy ETL updates
  • Day 0: Deploy application updates
  • Day 1 (after nightly job): Count data available
  • Day 2+: Normal operation with count data

Backward Compatibility​

Fully backward compatible:

  • Nullable columns (no NOT NULL constraints)
  • Existing queries unaffected
  • Application handles null values
  • No breaking changes to APIs

References​

Source Code​

  • ETL Notebook: src/etl/notebooks/main/item-store-full-refresh.py
  • Database Schema: src/db/ProChatDB/Tables/ItemStore.sql
  • Domain Entity: src/services/Domain/Entities/Item/ItemStore.cs
  • Repository: src/services/Infrastructure/Repositories/ItemStoreRepository.cs

Documentation​

  • ETL Job Docs: docs/etl/jobs/item-store-full-refresh.md
  • Similar Pattern: BalanceOnHand table with LastCountDate field
  • Balance on Hand tracking (similar date field pattern)
  • Last Delivery Date (similar datetime field)
  • Sale Ranking (similar numeric scoring field)

Summary​

This implementation plan adds support for LastCountValue and LastCountDate fields from the Item Store DBWS feed, enabling Publix Pro Chat to answer count-related queries.

Key Points:

  • Uses established staging table merge pattern
  • Minimal schema changes (2 columns + 1 index)
  • Nightly refresh via existing ETL job
  • No breaking changes or backward compatibility issues
  • Follows existing patterns (BalanceOnHand precedent)
  • Comprehensive testing strategy
  • Clear documentation updates

Estimated Effort: 1-2 days

Risk Level: Low (additive changes only)

Dependencies:

  • Upstream DBWS feed must include the new fields
  • Nightly ETL job schedule confirmed with team

Next Steps:

  1. Validate with team: Is nightly refresh acceptable?
  2. Confirm source feed field names match specification
  3. Execute Phase 1 (Database) in DEV environment
  4. Proceed through phases sequentially