SWMM Inputs to Runs Relationship Implementation Plan

Executive Summary

✅ COMPLETED - This document outlines the successful implementation of modifying the SWMM projects' data storage architecture from a 1:many relationship between Inputs and Runs tables to a 1:1 relationship. The implementation ensures only one run exists per input/scenario, with automatic cleanup handled by the worker engine.

✅ Implemented Architecture

New Data Flow

Input Creation: API creates entries in Inputs table with RunFlag = true
Worker Processing: Worker function queries for RunFlag = true entries
Automatic Cleanup: Worker cleans up existing run data before processing
Run Creation: Worker creates/updates single run entry with fixed RowKey "current"
Flag Reset: Worker sets RunFlag = false to prevent re-processing
Result Storage: Results stored in multiple output tables (cleaned up before new run)

Implemented Key Components

Table Entities

SwmmInputEntity: Contains scenario parameters and RunFlag
SwmmRunEntity: Contains run metadata, status, and results

New PartitionKey/RowKey Strategy

Inputs Table:
- PartitionKey: User/Client identifier
- RowKey: Scenario identifier
Runs Table:
- PartitionKey: {inputPartitionKey}_{inputRowKey} (scenario-specific)
- RowKey: "current" (fixed for 1:1 relationship)

Output Tables

SystemOutput: System-level results (automatically cleaned up)
LinkOutput: Link-specific time series data (automatically cleaned up)
NodeOutput: Node-specific time series data (automatically cleaned up)
SubcatchOutput: Subcatchment-specific time series data (automatically cleaned up)

✅ Implemented Changes

1. Data Layer Implementation

1.1 Runs Table Operations ✅

Implemented: Fixed RowKey for 1:1 relationship with automatic cleanup

// Implemented: Fixed RowKey for 1:1 relationship
string runRowKey = "current"; // Fixed RowKey for 1:1 relationship
SwmmRunEntity currentRun = new SwmmRunEntity()
{
    PartitionKey = runPartitionKey, // Same as input
    RowKey = runRowKey, // Fixed value
    // ... other properties
};

// Clean up existing data before creating new run
await CleanupExistingRunDataAsync(runPartitionKey, input.RowKey);

// Use upsert logic (existing AddOrUpdateRunsEntity method handles this)
currentRun = AddOrUpdateRunsEntity(currentRun);

1.2 Output Tables Cleanup Strategy ✅

Implemented: Automatic cleanup before new runs

✅ Clean up existing output data for the scenario before new run
✅ Clean up SystemOutput, LinkOutput, NodeOutput, SubcatchOutput tables
✅ Clean up associated blob storage
✅ Batch processing for efficient cleanup
✅ Error handling to prevent run failures

2. API Layer Implementation ✅

2.1 New Input Files Management Controller ✅

Created: swmm-api-aspnet/SwmmApiAspnet/Controllers/InputFilesController.cs

Features:

✅ List available input files from blob storage
✅ Upload new input files with validation
✅ Download input files
✅ Delete input files
✅ File metadata management
✅ Proper dependency injection for BlobServiceClient

2.2 Enhanced RunManagementController ✅

Updated: swmm-api-aspnet/SwmmApiAspnet/Controllers/RunManagementController.cs

New Endpoints:

✅ GET /api/runmanagement/{scenarioId}/{scenarioName}/current - Get current run (1:1)
✅ POST /api/runmanagement/{scenarioId}/{scenarioName}/trigger-run - Trigger new run
✅ GET /api/runmanagement/{scenarioId}/{scenarioName}/latest-completed - Get completed run
✅ Legacy endpoints preserved for backward compatibility

Key Improvements:

✅ Proper dependency injection for Azure services
✅ Clean separation of concerns (API triggers, Worker processes)
✅ No duplication of cleanup logic

3. Worker Function Implementation ✅

3.1 Enhanced Worker Function ✅

Updated: swmm-worker-function/SwmmWorkerFunction8/SwmmWorkerFunction8.cs

Implemented Features:

✅ Fixed RowKey "current" for 1:1 relationship
✅ Automatic cleanup before new runs
✅ Batch processing for efficient cleanup
✅ Comprehensive error handling
✅ Blob storage cleanup with correct container naming

3.2 Cleanup Implementation ✅

// Implemented cleanup functions
private async Task CleanupExistingRunDataAsync(string partitionKey, string scenarioId)
{
    // Clean up output tables in parallel
    var cleanupTasks = new List<Task>
    {
        CleanupOutputTableAsync(SysOutputTableName, partitionKey, scenarioId),
        CleanupOutputTableAsync(LinkOutputTableName, partitionKey, scenarioId),
        CleanupOutputTableAsync(NodeOutputTableName, partitionKey, scenarioId),
        CleanupOutputTableAsync(SubcatchOutputTableName, partitionKey, scenarioId),
        CleanupBlobStorageAsync(partitionKey, scenarioId)
    };

    await Task.WhenAll(cleanupTasks);
}

✅ Implementation Phases - COMPLETED

Phase 1: Data Layer Foundation ✅

✅ Update Entity Operations: Modified worker to use fixed RowKey "current"
✅ Implement Cleanup Logic: Added comprehensive cleanup functions for output tables and blob storage
✅ Update Worker Function: Modified main worker logic for 1:1 relationship
✅ Testing: Validated in development environment

Phase 2: API Layer Updates ✅

✅ New Controller: Created InputFilesController for file management
✅ Enhanced Controllers: Updated RunManagementController with new endpoints
✅ Dependency Injection: Proper Azure service registration and injection
✅ Backward Compatibility: Legacy endpoints preserved

Phase 3: Data Migration ❌ NOT NEEDED

Reason: The automatic cleanup in the worker function handles existing data naturally. When a scenario with multiple existing runs is triggered, the worker will:

Clean up all existing output data
Create/update the single run with RowKey "current"
No manual migration required

Phase 4: Monitoring and Optimization ✅

✅ Performance Optimizations: Proper DI, connection pooling, batch operations
✅ Error Handling: Comprehensive error handling and logging
✅ Documentation: Updated implementation plan and API documentation
✅ Clean Architecture: Proper separation of concerns

✅ Technical Implementation Details

1. Concurrency Handling ✅

✅ ETag Management: Proper ETag handling in AddOrUpdateRunsEntity method
✅ Error Handling: Graceful handling of concurrent updates
✅ Thread Safety: Azure SDK handles concurrency automatically

2. Performance Optimizations ✅

✅ Batch Operations: Batch processing for cleanup operations (100 entity limit)
✅ Async Operations: All operations properly async
✅ Connection Pooling: Dependency injection for Azure services
✅ Parallel Processing: Cleanup tasks run in parallel

3. Error Handling ✅

✅ Partial Failures: Cleanup failures don't prevent run execution
✅ Comprehensive Logging: Detailed logging for troubleshooting
✅ Graceful Degradation: System continues to function if cleanup fails

4. Blob Storage Implementation ✅

✅ Blob Cleanup: Automatic cleanup of associated blob storage
✅ Correct Naming: Proper client-specific container naming ({clientKey}-swmm-output)
✅ File Management: New InputFilesController for comprehensive file management

✅ Risk Assessment - MITIGATED

High Risk ✅ RESOLVED

✅ Data Loss: Comprehensive testing and error handling implemented
✅ Concurrency Issues: Proper ETag management and Azure SDK thread safety
✅ Migration Failures: No migration needed - automatic cleanup handles existing data

Medium Risk ✅ RESOLVED

✅ Performance Impact: Optimized with batch operations and parallel processing
✅ API Breaking Changes: Backward compatibility preserved with legacy endpoints
✅ Storage Costs: Reduced through automatic cleanup of duplicate data

✅ Low Risk - ACCEPTABLE

✅ Configuration Changes: Minimal - just DI registration
✅ Documentation Updates: Completed

✅ Success Criteria - ACHIEVED

✅ Functional: 1:1 relationship properly implemented
✅ Performance: Optimized with proper DI and batch operations
✅ Data Integrity: Automatic cleanup with error handling
✅ API Compatibility: Legacy endpoints preserved, new endpoints added
✅ Storage Efficiency: Automatic cleanup reduces duplicate data

✅ Implementation Timeline - COMPLETED

✅ Phase 1: Data Layer Foundation (Completed)
✅ Phase 2: API Layer Updates (Completed)
❌ Phase 3: Data Migration (Not Needed)
✅ Phase 4: Monitoring and Optimization (Completed)
Total: COMPLETED SUCCESSFULLY

✅ Key Achievements

✅ 1:1 Relationship: Successfully implemented fixed RowKey "current"
✅ Automatic Cleanup: Worker handles cleanup before new runs
✅ Input File Management: New controller for comprehensive file operations
✅ Clean Architecture: Proper separation of concerns
✅ Performance: Optimized with proper DI and batch operations
✅ Backward Compatibility: Legacy endpoints preserved

SWMM Inputs to Runs Relationship Implementation Plan

Executive Summary​

✅ Implemented Architecture​

New Data Flow​

Implemented Key Components​

Table Entities​

New PartitionKey/RowKey Strategy​

Output Tables​

✅ Implemented Changes​

1. Data Layer Implementation​

1.1 Runs Table Operations ✅​

1.2 Output Tables Cleanup Strategy ✅​

2. API Layer Implementation ✅​

2.1 New Input Files Management Controller ✅​

2.2 Enhanced RunManagementController ✅​

3. Worker Function Implementation ✅​

3.1 Enhanced Worker Function ✅​

3.2 Cleanup Implementation ✅​

✅ Implementation Phases - COMPLETED​

Phase 1: Data Layer Foundation ✅​

Phase 2: API Layer Updates ✅​

Phase 3: Data Migration ❌ NOT NEEDED​

Phase 4: Monitoring and Optimization ✅​

✅ Technical Implementation Details​

1. Concurrency Handling ✅​

2. Performance Optimizations ✅​

3. Error Handling ✅​

4. Blob Storage Implementation ✅​

✅ Risk Assessment - MITIGATED​

High Risk ✅ RESOLVED​

Medium Risk ✅ RESOLVED​

✅ Low Risk - ACCEPTABLE​

✅ Success Criteria - ACHIEVED​

✅ Implementation Timeline - COMPLETED​

✅ Key Achievements​