SWMM Inputs and Runs to Scenarios Merge Plan

Executive Summary

This plan outlines the consolidation of the Inputs and Runs Azure tables into a single Scenarios table, eliminating the artificial 1:1 relationship and simplifying the data architecture.

New Unified Schema Design

SwmmScenarioEntity (Replaces SwmmInputEntity + SwmmRunEntity)

public class SwmmScenarioEntity : ITableEntity
{
    // Azure Table Storage Properties
    public string PartitionKey { get; set; }  // ScenarioId
    public string RowKey { get; set; }        // "scenario" (fixed)
    public DateTimeOffset? Timestamp { get; set; }
    public ETag ETag { get; set; }

    // Core Scenario Properties (from Inputs)
    public string Name { get; set; }                    // NEW: Human-readable name
    public string Description { get; set; }             // NEW: Scenario description
    public string InputFileName { get; set; }
    public int SiteArea { get; set; }
    public string TimeZone { get; set; }
    public DateTime CreatedAt { get; set; }             // NEW: Creation timestamp
    public string CreatedBy { get; set; }               // NEW: Creator identifier
    public bool IsActive { get; set; }                  // NEW: Active status

    // Simulation Parameters (from Inputs)
    public string StartTime { get; set; }
    public string EndTime { get; set; }
    public int RainfallYear { get; set; }
    public int ReportingTimeStep { get; set; }
    public bool RunFlag { get; set; }                   // Triggers worker processing

    // Run Results (from Runs - nullable for scenarios without runs)
    public string? Status { get; set; }                 // Run status
    public double? Flow { get; set; }                   // Total flow volume
    public double? Flooding { get; set; }               // Total flooding volume
    public DateTime? RunStartTime { get; set; }         // When run started
    public DateTime? RunEndTime { get; set; }           // When run completed
    public DateTime? CompletedAt { get; set; }          // Completion timestamp
    public string? ErrorMessage { get; set; }           // Error details if failed
    public TimeSpan? Duration { get; set; }             // Run duration
}

Affected Projects and Files

1. Core Data Layer (swmm5_core)

Files to Update:

• GqcStorage1/TableEntities/SwmmInputEntity.cs → DELETE
• GqcStorage1/TableEntities/SwmmRunEntity.cs → DELETE
• GqcStorage1/TableEntities/SwmmScenarioEntity.cs → CREATE NEW
• GqcStorage1/ConstantsSwmm.cs → Update table names
• GqcStorage1/Mappers/RequestToEntityMapper.cs → Complete rewrite
• GqcStorage1/Mappers/EntityToDtoMapper.cs → Update mappings

Changes Required:

• Replace all SwmmInputEntity and SwmmRunEntity references
• Update table name constants: INPUTS_TABLE → SCENARIOS_TABLE
• Remove RUNS_TABLE constant
• Rewrite all mapping methods

2. Worker Functions

Projects to Update:

• swmm-worker-function/SwmmWorkerFunction8/ → Update table references
• swc-worker-function/SwcWorkerFunction8/ → Update table references

Changes Required:

• Update table client references
• Modify worker logic to update single table
• Update result storage logic

3. API Layer (swmm-api-aspnet)

Controllers to Update:

• Controllers/ScenarioManagementController.cs → Major refactor
• Controllers/RunManagementController.cs → DELETE (functionality merged)
• Controllers/ScenarioController.cs → Update table references
• Controllers/NodeController.cs → Update table references
• Controllers/LinkController.cs → Update table references
• Controllers/SubcatchmentController.cs → Update table references
• Controllers/SystemController.cs → Update table references

Changes Required:

• Remove dual table client references
• Consolidate API endpoints
• Update all database queries
• Merge run management functionality into scenario management

4. Frontend (swmm-frontend-react)

Files to Update:

• src/pages/ScenarioSelectPage.jsx → Update API calls
• src/pages/ScenarioRunSelectPage.jsx → DELETE (functionality merged)
• src/contexts/RunContext.jsx → DELETE (functionality merged)
• src/contexts/ScenarioContext.jsx → CREATE NEW (unified context)
• src/components/runs/RunsTable.jsx → DELETE
• src/components/scenarios/ScenariosTable.jsx → Update to show run status
• src/constants.jsx → Update API endpoints
• src/routes.jsx → Remove run-specific routes

Changes Required:

• Remove separate run management UI
• Integrate run status into scenario views
• Update API endpoint calls
• Simplify data flow

Migration Plan

Phase 1: Preparation

1. Create new SwmmScenarioEntity

   • Design and implement new entity class
   • Add all required properties
   • Create migration utilities

2. Update Constants and Configuration

   • Update ConstantsSwmm.cs with new table names
   • Update connection strings and table references
   • Create backward compatibility constants

3. Create Data Migration Script

   • Script to merge existing Inputs and Runs data
   • Handle data validation and cleanup
   • Create rollback procedures

Phase 2: Core Data Layer

1. Update Mappers

   • Rewrite RequestToEntityMapper.cs
   • Update EntityToDtoMapper.cs
   • Create new unified DTOs

2. Update Storage Utilities

   • Modify SwmmStorageUtility.cs
   • Update all table client references
   • Test data access patterns

Phase 3: API Layer

1. Consolidate Controllers

   • Merge RunManagementController into ScenarioManagementController
   • Update all API endpoints
   • Remove duplicate functionality

2. Update Legacy Controllers

   • Update ScenarioController.cs
   • Update NodeController.cs, LinkController.cs, etc.
   • Test all API endpoints

Phase 4: Frontend Updates

1. Unify User Interface

   • Merge scenario and run management pages
   • Create unified context for scenario management
   • Update all components

2. Update API Integration

   • Update all API calls
   • Remove run-specific endpoints
   • Test user workflows

Phase 5: Worker Updates

1. Update Worker Functions

   • Modify worker logic for single table
   • Update result storage
   • Test worker processing

2. Data Migration Execution

   • Run migration script in production
   • Validate data integrity
   • Monitor system performance

Phase 6: Cleanup

1. Remove Legacy Code

   • Delete old entity classes
   • Remove unused API endpoints
   • Clean up frontend components

2. Documentation and Testing

   • Update API documentation
   • Complete integration testing
   • Performance optimization

Benefits of This Approach

1. Simplified Architecture

   • Single source of truth for scenario data
   • Eliminated data synchronization issues
   • Reduced complexity in data access

2. Improved Performance

   • Fewer database queries
   • Reduced data duplication
   • Simplified caching strategies

3. Better User Experience

   • Unified scenario management interface
   • Clearer data relationships
   • Simplified workflows

4. Easier Maintenance

   • Single table to maintain
   • Simplified API endpoints
   • Reduced code complexity

Conclusion

This migration will significantly simplify the data architecture while maintaining all existing functionality. The estimated 9-10 week timeline allows for thorough testing and gradual deployment, minimizing risks to the production system.