Input File Blob Storage - Design Document

Version: 1.0 Date: October 20, 2025 Status: Design Phase

---

📋 Table of Contents

1. Overview
2. Goals and Requirements
3. Architecture Design
4. Database Schema
5. Storage Strategy
6. Component Specifications
7. Migration Strategy
8. Security Considerations
9. Configuration
10. Future Enhancements

---

1. Overview

1.1 Purpose

Migrate EPANET input file storage from local file system to Azure Blob Storage for improved scalability, centralized management, and cloud-native architecture.

1.2 Current State

• Scenarios store input file paths as strings (InputFile column)
• Files stored on local file system
• Each EpanetConsoleCore instance needs local file access
• No centralized file management
• Difficult to share files across environments

1.3 Target State

• Input files stored in Azure Blob Storage
• InputFile entity as a proper database model with FK relationship
• EpanetConsoleCore downloads files to temp directories on demand
• Centralized file management through API
• Support for multiple storage providers (Azure primary, local for testing)

1.4 Key Design Decisions

✅ No File Validation on Upload

• Files are stored as-is without parsing or validation
• EPANET will validate during simulation runs
• Simplifies upload process and reduces complexity

✅ Container-per-Network Architecture

• Each network/client gets its own blob container
• Scoped access using SAS tokens (not account keys)
• Better security isolation and access control

---

2. Goals and Requirements

2.1 Functional Requirements

• FR-1: Store EPANET input files in Azure Blob Storage
• FR-2: Support multiple storage providers (Azure, Local)
• FR-3: Download files to temp directories for simulation runs
• FR-4: Automatic cleanup of temporary files
• FR-5: Upload files via API
• FR-6: Download files via API
• FR-7: Soft delete for input files
• FR-8: List and manage input files

2.2 Non-Functional Requirements

• NFR-1: Minimize changes to existing simulation logic
• NFR-2: Support concurrent access to same input file
• NFR-3: Handle large files (up to 500MB)
• NFR-4: Secure access using scoped credentials
• NFR-5: Backward compatibility during migration
• NFR-6: Automatic temp file cleanup (prevent disk bloat)

---

3. Architecture Design

3.1 System Overview

┌─────────────────────────────────────────────────────────────┐
│                     Frontend (React)                         │
│  - Upload INP files                                         │
│  - Manage input files                                       │
│  - Assign files to scenarios                                │
└──────────────────────┬──────────────────────────────────────┘
                       │ HTTPS
┌──────────────────────▼──────────────────────────────────────┐
│                   API (ASP.NET Core)                         │
│  - InputFileController (upload, list, download)             │
│  - ScenarioController (modified to use InputFileId)         │
│  - IInputFileStorageService (abstraction)                   │
└──────────────────────┬──────────────────────────────────────┘
                       │
       ┌───────────────┴───────────────┐
       │                               │
┌──────▼───────────────┐    ┌──────────▼──────────────────────┐
│  PostgreSQL Database │    │   Azure Blob Storage            │
│  - InputFiles        │    │   Container per Network:        │
│  - Scenarios         │    │     - network-client1           │
│    (FK to InputFile) │    │       * file1.inp               │
└──────┬───────────────┘    │       * file2.inp               │
       │                    │     - network-client2           │
       │                    │       * file1.inp               │
       │                    └─────────┬───────────────────────┘
       │                              │ SAS Token Auth
┌──────▼──────────────────────────────▼───────────────────────┐
│              EpanetConsoleCore                               │
│  1. Receive scenario ID from API                            │
│  2. Load Scenario + InputFile from DB                       │
│  3. Get scoped SAS token for container                      │
│  4. Download INP file from blob → temp directory            │
│  5. Run EPANET simulation with local temp file              │
│  6. Cleanup temp directory after run                        │
└─────────────────────────────────────────────────────────────┘

3.2 Data Flow

Upload Flow:

User → Frontend → API → Blob Storage
                     ↓
              Database (InputFile record)

Simulation Flow:

API → EpanetConsoleCore → Database (load scenario + input file)
                        ↓
                   Blob Storage (download INP file)
                        ↓
                   Temp Directory (local INP file)
                        ↓
                   EPANET Simulation
                        ↓
                   Cleanup Temp Files

---

4. Database Schema

4.1 InputFile Entity (NEW)

/// <summary>
/// Represents a stored EPANET input file.
/// Files are stored in Azure Blob Storage (or local file system for testing).
/// </summary>
public class InputFile
{
  // Primary Key
  public int Id { get; set; }

  // Basic Information
  public string Name { get; set; }  // e.g., "Network_2024_Baseline.inp"
  public string Description { get; set; }

  // Storage Configuration
  public StorageProvider StorageProvider { get; set; }  // Azure, Local, etc.
  public string ContainerName { get; set; }  // e.g., "network-client1"
  public string BlobName { get; set; }  // e.g., "baseline-2024.inp" or GUID

  // File Properties
  public long FileSizeBytes { get; set; }
  public string FileHash { get; set; }  // SHA256 for integrity checks
  public string ContentType { get; set; }  // "text/plain"

  // Status
  public bool IsActive { get; set; }  // Soft delete flag

  // Audit Fields
  public DateTime UploadedAt { get; set; }
  public string UploadedBy { get; set; }  // User ID or system identifier
  public DateTime? LastAccessedAt { get; set; }

  // Navigation Properties
  public ICollection<Scenario> Scenarios { get; set; }
}

/// <summary>
/// Storage provider types
/// </summary>
public enum StorageProvider
{
  AzureBlob = 1,      // Primary: Azure Blob Storage
  LocalFileSystem = 2  // Testing/Development: Local file system
}

Table Definition:

CREATE TABLE "InputFiles" (
  "Id" SERIAL PRIMARY KEY,
  "Name" VARCHAR(255) NOT NULL,
  "Description" TEXT,
  "StorageProvider" INTEGER NOT NULL,
  "ContainerName" VARCHAR(255) NOT NULL,
  "BlobName" VARCHAR(500) NOT NULL,
  "FileSizeBytes" BIGINT NOT NULL,
  "FileHash" VARCHAR(64),
  "ContentType" VARCHAR(100),
  "IsActive" BOOLEAN NOT NULL DEFAULT true,
  "UploadedAt" TIMESTAMP NOT NULL DEFAULT NOW(),
  "UploadedBy" VARCHAR(255),
  "LastAccessedAt" TIMESTAMP,
  CONSTRAINT "UQ_InputFile_Container_Blob" UNIQUE ("ContainerName", "BlobName")
);

CREATE INDEX "IX_InputFiles_IsActive" ON "InputFiles" ("IsActive");
CREATE INDEX "IX_InputFiles_ContainerName" ON "InputFiles" ("ContainerName");

4.2 Scenario Entity Changes

Before:

public class Scenario
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string InputFile { get; set; }  // ❌ Remove this
  // ... other properties
}

After:

public class Scenario
{
  public int Id { get; set; }
  public string Name { get; set; }

  // NEW: Foreign key to InputFile
  public int? BaseInputFileId { get; set; }  // Nullable during migration
  public InputFile BaseInputFile { get; set; }  // Navigation property

  // ... other properties unchanged
}

Migration SQL:

-- Add FK column
ALTER TABLE "Scenarios"
ADD COLUMN "BaseInputFileId" INTEGER;

-- Add FK constraint (after data migration)
ALTER TABLE "Scenarios"
ADD CONSTRAINT "FK_Scenarios_InputFiles"
FOREIGN KEY ("BaseInputFileId")
REFERENCES "InputFiles" ("Id");

-- Eventually remove old column (after full migration)
-- ALTER TABLE "Scenarios" DROP COLUMN "InputFile";

---

5. Storage Strategy

5.1 Container Configuration (Instance-Level)

✅ FINAL DECISION: Instance-Level Configuration

Container names are configured per-deployment in appsettings.json, NOT stored in the database.

Configuration Pattern:

{
  "InputFileStorage": {
    "ContainerName": "hydrotrek-inputfiles",  // ← Instance-specific
    "Provider": "LocalFileSystem"
  }
}

Container Naming Convention:

{instance-identifier}-inputfiles

Examples:
- hydrotrek-inputfiles       (default/shared)
- clienta-inputfiles          (Client A instance)
- clientb-inputfiles          (Client B instance)
- dev-inputfiles              (development)
- prod-inputfiles             (production)

Benefits:

• ✅ Each deployment uses its own container
• ✅ No user input required (infrastructure concern)
• ✅ Easy to migrate containers (change config)
• ✅ Security isolation between instances
• ✅ Scoped access using container-specific SAS tokens
• ✅ Independent lifecycle management per instance
• ✅ Billing/usage tracking per container

Multi-Tenant Architecture:

Dev Instance:      ContainerName = "dev-inputfiles"
Client A Instance: ContainerName = "clienta-inputfiles"
Client B Instance: ContainerName = "clientb-inputfiles"

Each instance only sees/accesses its own container.

5.2 Blob Naming Strategy

✅ FINAL DECISION: User Name + Timestamp + .inp

Implemented Pattern:

{sanitized-user-name}-{yyyyMMdd-HHmmss}.inp

Examples:
- My Network Model-20251021-143022.inp
- Summer Peak Scenario-20251022-091540.inp
- Test Network-20251020-100000.inp

Implementation:

// In InputFileController.cs
var timestamp = DateTime.UtcNow.ToString("yyyyMMdd-HHmmss");
var sanitizedName = SanitizeFileName(name);  // User's display name
var blobName = $"{sanitizedName}-{timestamp}.inp";

Database vs Storage:

• Database Name field: User-friendly display name (e.g., "My Network Model")
• Database BlobName field: Unique storage identifier (e.g., "My Network Model-20251021-143022.inp")
• BlobName is UNIQUE in the database (enforced by unique index)

Benefits:

• ✅ Guaranteed Uniqueness: Timestamp ensures no collisions
• ✅ Human-Readable: Easy to identify files in storage browser
• ✅ Natural Versioning: Upload same name → new version with newer timestamp
• ✅ Audit Trail: Timestamp shows when file was uploaded
• ✅ No Manual Versioning: Automatic version management
• ✅ Sortable: Files naturally sort by upload time in storage

Example: Multiple Versions of Same File

Database:
ID | Name             | BlobName                                    | Uploaded
1  | Network Model v1 | Network Model v1-20251021-143022.inp       | 2025-10-21 14:30
2  | Network Model v1 | Network Model v1-20251022-091540.inp       | 2025-10-22 09:15

Storage (hydrotrek-inputfiles container):
- Network Model v1-20251021-143022.inp  ← Old version
- Network Model v1-20251022-091540.inp  ← New version

User Workflow:

1. User uploads "Network Model v1" → Creates blob: Network Model v1-20251021-143022.inp
2. User makes changes, uploads again with same name "Network Model v1" → Creates blob: Network Model v1-20251022-091540.inp
3. Both files coexist, different scenarios can reference either version
4. Users can deactivate old versions (soft delete) when ready

Alternatives Considered:

❌ Option 1: User Name Only

• Not unique, would cause upload failures

❌ Option 2: Pure GUID

• Not human-readable, hard to manage in storage

⚠️ Option 3: User Name + Short GUID

• More compact than timestamp
• Less meaningful for humans
• Timestamp chosen for better audit trail

Container Name Strategy: ✅ FINAL DECISION: Instance-Level Configuration

Container names are NOT stored in the database. Instead, they are configured per-deployment in appsettings.json:

{
  "InputFileStorage": {
    "ContainerName": "hydrotrek-inputfiles",  // ← Instance-specific
    "Provider": "LocalFileSystem"
  }
}

Rationale:

• Each hosted instance (dev, client1, client2) uses its own container
• Simplifies user experience (no container input required)
• Container choice is infrastructure, not user concern
• Easy to change containers by updating config

5.3 Scoped Access with SAS Tokens

IMPORTANT: Do NOT use account-level connection strings or access keys in EpanetConsoleCore.

Access Pattern:

1. EpanetConsoleCore needs to download a file
2. Load InputFile from database → get ContainerName
3. Generate container-scoped SAS token (read-only, 1-hour expiry)
4. Download blob using SAS token
5. SAS token expires automatically

SAS Token Configuration:

var sasBuilder = new BlobSasBuilder
{
  BlobContainerName = containerName,
  BlobName = blobName,
  Resource = "b",  // Blob
  StartsOn = DateTimeOffset.UtcNow.AddMinutes(-5),  // Clock skew
  ExpiresOn = DateTimeOffset.UtcNow.AddHours(1),
  Protocol = SasProtocol.Https
};

// Read-only permission
sasBuilder.SetPermissions(BlobSasPermissions.Read);

var sasToken = sasBuilder.ToSasQueryParameters(storageSharedKeyCredential);

Configuration Pattern:

{
  "InputFileStorage": {
    "AzureBlob": {
      "ServiceUrl": "https://{account}.blob.core.windows.net",
      "Containers": {
        "network-client1": {
          "SasToken": "sv=2021-06-08&ss=b&srt=sco&sp=r&se=...",
          "UseSharedKey": false
        },
        "network-client2": {
          "SasToken": "sv=2021-06-08&ss=b&srt=sco&sp=r&se=...",
          "UseSharedKey": false
        }
      },
      // Only for upload operations (API tier)
      "AdminAccountKey": "encrypted-key-here"
    }
  }
}

Security Notes:

• ✅ SAS tokens are container-scoped, not account-scoped
• ✅ Read-only permissions for simulation runs
• ✅ Short expiry time (1 hour)
• ✅ HTTPS-only protocol
• ✅ Account keys only in API tier for upload operations
• ✅ EpanetConsoleCore never has account keys

---

6. Component Specifications

6.1 Storage Service Interface

/// <summary>
/// Interface for input file storage operations.
/// Implementations: AzureBlobStorageService, LocalFileStorageService
/// </summary>
public interface IInputFileStorageService
{
  /// <summary>
  /// Uploads a file to storage and returns the blob name.
  /// </summary>
  Task<string> UploadAsync(
    Stream fileStream,
    string containerName,
    string fileName,
    CancellationToken cancellationToken = default);

  /// <summary>
  /// Downloads a file to a temporary local path and returns the path.
  /// </summary>
  Task<string> DownloadToTempFileAsync(
    string containerName,
    string blobName,
    string localPath,
    CancellationToken cancellationToken = default);

  /// <summary>
  /// Deletes a file from storage (soft delete if supported).
  /// </summary>
  Task DeleteAsync(
    string containerName,
    string blobName,
    CancellationToken cancellationToken = default);

  /// <summary>
  /// Checks if a file exists in storage.
  /// </summary>
  Task<bool> ExistsAsync(
    string containerName,
    string blobName,
    CancellationToken cancellationToken = default);

  /// <summary>
  /// Gets file size in bytes.
  /// </summary>
  Task<long> GetFileSizeAsync(
    string containerName,
    string blobName,
    CancellationToken cancellationToken = default);

  /// <summary>
  /// Generates a time-limited read-only SAS token for a container.
  /// </summary>
  Task<string> GenerateReadSasTokenAsync(
    string containerName,
    TimeSpan expiry);
}

6.2 Azure Blob Storage Implementation

public class AzureBlobStorageService : IInputFileStorageService
{
  private readonly BlobServiceClient _blobServiceClient;
  private readonly ILogger<AzureBlobStorageService> _logger;
  private readonly StorageSharedKeyCredential _sharedKeyCredential;

  public AzureBlobStorageService(
    IConfiguration configuration,
    ILogger<AzureBlobStorageService> logger)
  {
    var serviceUrl = configuration["InputFileStorage:AzureBlob:ServiceUrl"];
    var accountKey = configuration["InputFileStorage:AzureBlob:AdminAccountKey"];
    var accountName = new Uri(serviceUrl).Host.Split('.')[0];

    _sharedKeyCredential = new StorageSharedKeyCredential(accountName, accountKey);
    _blobServiceClient = new BlobServiceClient(new Uri(serviceUrl), _sharedKeyCredential);
    _logger = logger;
  }

  public async Task<string> UploadAsync(
    Stream fileStream,
    string containerName,
    string fileName,
    CancellationToken cancellationToken = default)
  {
    var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
    await containerClient.CreateIfNotExistsAsync(cancellationToken: cancellationToken);

    var blobClient = containerClient.GetBlobClient(fileName);

    await blobClient.UploadAsync(
      fileStream,
      overwrite: false,  // Prevent accidental overwrites
      cancellationToken: cancellationToken);

    _logger.LogInformation($"Uploaded {fileName} to {containerName}");

    return fileName;
  }

  public async Task<string> DownloadToTempFileAsync(
    string containerName,
    string blobName,
    string localPath,
    CancellationToken cancellationToken = default)
  {
    var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
    var blobClient = containerClient.GetBlobClient(blobName);

    // Ensure directory exists
    var directory = Path.GetDirectoryName(localPath);
    if (!string.IsNullOrEmpty(directory))
    {
      Directory.CreateDirectory(directory);
    }

    await blobClient.DownloadToAsync(localPath, cancellationToken);

    _logger.LogInformation($"Downloaded {blobName} from {containerName} to {localPath}");

    return localPath;
  }

  public async Task<string> GenerateReadSasTokenAsync(
    string containerName,
    TimeSpan expiry)
  {
    var sasBuilder = new BlobSasBuilder
    {
      BlobContainerName = containerName,
      Resource = "c",  // Container
      StartsOn = DateTimeOffset.UtcNow.AddMinutes(-5),
      ExpiresOn = DateTimeOffset.UtcNow.Add(expiry),
      Protocol = SasProtocol.Https
    };

    sasBuilder.SetPermissions(BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List);

    var sasToken = sasBuilder.ToSasQueryParameters(_sharedKeyCredential).ToString();

    return sasToken;
  }

  // Other methods...
}

6.3 Temp File Manager

/// <summary>
/// Manages temporary file directories for EPANET runs.
/// </summary>
public class TempFileManager
{
  private static readonly string TempBasePath =
    Path.Combine(Path.GetTempPath(), "HydroTrek", "EpanetInputFiles");

  /// <summary>
  /// Creates a unique temp directory for a scenario run.
  /// Format: {basepath}/scenario_{id}_{guid}
  /// </summary>
  public static string CreateTempDirectory(int scenarioId)
  {
    var guid = Guid.NewGuid().ToString("N").Substring(0, 8);
    var dirPath = Path.Combine(TempBasePath, $"scenario_{scenarioId}_{guid}");
    Directory.CreateDirectory(dirPath);
    return dirPath;
  }

  /// <summary>
  /// Cleans up a temp directory and all its contents.
  /// </summary>
  public static void CleanupTempDirectory(string dirPath)
  {
    try
    {
      if (Directory.Exists(dirPath))
      {
        Directory.Delete(dirPath, recursive: true);
      }
    }
    catch (Exception ex)
    {
      // Log but don't throw - cleanup is best effort
      Console.WriteLine($"Warning: Failed to cleanup {dirPath}: {ex.Message}");
    }
  }

  /// <summary>
  /// Cleans up orphaned temp directories older than specified age.
  /// Should be called on startup or via scheduled task.
  /// </summary>
  public static void CleanupOrphanedTempFiles(TimeSpan maxAge)
  {
    if (!Directory.Exists(TempBasePath))
    {
      return;
    }

    var cutoffTime = DateTime.UtcNow - maxAge;

    foreach (var dir in Directory.GetDirectories(TempBasePath))
    {
      try
      {
        var dirInfo = new DirectoryInfo(dir);
        if (dirInfo.LastWriteTimeUtc < cutoffTime)
        {
          Directory.Delete(dir, recursive: true);
          Console.WriteLine($"Cleaned up orphaned temp directory: {dir}");
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Warning: Failed to cleanup orphaned directory {dir}: {ex.Message}");
      }
    }
  }
}

6.4 EpanetConsoleCore Changes

Modified Program.cs:

public static async Task Main(string[] args)
{
  // ... existing argument parsing

  logger = HTLogger.HTLogger.GetDefaultLogger();

  // Cleanup orphaned temp files on startup
  TempFileManager.CleanupOrphanedTempFiles(TimeSpan.FromHours(24));

  if (exportOnly)
  {
    await ExportScenarioInputFile(pgConnStr, scenarioId);
  }
  else
  {
    await dwdRun(pgConnStr);
  }
}

private static async Task dwdRun(string pgConnStr)
{
  string tempDir = null;
  string localInpPath = null;

  try
  {
    // ... existing context initialization

    var scenario = await enContext.Scenarios
      .Include(s => s.Options)
      .Include(s => s.BaseInputFile)  // NEW: Include InputFile
      .FirstOrDefaultAsync(s => s.Id == scenarioId);

    if (scenario == null)
    {
      throw new Exception($"Scenario {scenarioId} not found");
    }

    // NEW: Download input file from blob storage
    if (scenario.BaseInputFile == null)
    {
      throw new Exception($"Scenario {scenarioId} has no base input file assigned");
    }

    logger.LogInformation($"Preparing input file: {scenario.BaseInputFile.Name}");

    // Create temp directory
    tempDir = TempFileManager.CreateTempDirectory(scenarioId);
    localInpPath = Path.Combine(tempDir, "network.inp");

    // Get storage service and download file
    var storageService = GetStorageService(scenario.BaseInputFile.StorageProvider);
    await storageService.DownloadToTempFileAsync(
      scenario.BaseInputFile.ContainerName,
      scenario.BaseInputFile.BlobName,
      localInpPath
    );

    logger.LogInformation($"Input file downloaded to: {localInpPath}");

    // Update LastAccessedAt
    scenario.BaseInputFile.LastAccessedAt = DateTime.UtcNow;
    await enContext.SaveChangesAsync();

    // Continue with existing simulation logic
    var filenameBase = Path.GetFileNameWithoutExtension(localInpPath);
    var inpBase = Path.Combine(Path.GetDirectoryName(localInpPath), filenameBase);
    epanetWrapper = new EpanetWrapper(localInpPath, $"{inpBase}.rpt", $"{inpBase}.out", logger);

    // ... rest of existing dwdRun() logic unchanged
  }
  finally
  {
    // Cleanup temp directory
    if (tempDir != null)
    {
      TempFileManager.CleanupTempDirectory(tempDir);
      logger.LogInformation($"Cleaned up temp directory: {tempDir}");
    }
  }
}

private static IInputFileStorageService GetStorageService(StorageProvider provider)
{
  return provider switch
  {
    StorageProvider.AzureBlob => new AzureBlobStorageService(configuration, logger),
    StorageProvider.LocalFileSystem => new LocalFileStorageService(configuration, logger),
    _ => throw new NotSupportedException($"Storage provider {provider} not supported")
  };
}

---

7. Migration Strategy

7.1 Migration Phases

Phase 1: Database Schema

1. Create InputFiles table
2. Add BaseInputFileId column to Scenarios (nullable)
3. Add foreign key constraint

Phase 2: Data Migration

1. For each unique InputFile path in Scenarios:
   • Create corresponding InputFile record
   • Store as LocalFileSystem provider initially
   • Update Scenarios.BaseInputFileId to point to new record

Phase 3: Code Deployment

1. Deploy API with InputFile support
2. Deploy EpanetConsoleCore with blob download logic
3. Test with LocalFileSystem provider

Phase 4: Blob Migration

1. Upload existing INP files to Azure Blob Storage
2. Update InputFile records to point to blobs
3. Change StorageProvider to AzureBlob

Phase 5: Cleanup

1. Verify all scenarios work with blob storage
2. Remove old InputFile column from Scenarios
3. Remove local INP files

7.2 Migration Scripts

Database Migration:

-- ============================================================================
-- PHASE 1: Create InputFiles table
-- ============================================================================
CREATE TABLE "InputFiles" (
  "Id" SERIAL PRIMARY KEY,
  "Name" VARCHAR(255) NOT NULL,
  "Description" TEXT,
  "StorageProvider" INTEGER NOT NULL DEFAULT 2,  -- LocalFileSystem initially
  "ContainerName" VARCHAR(255) NOT NULL,
  "BlobName" VARCHAR(500) NOT NULL,
  "FileSizeBytes" BIGINT NOT NULL DEFAULT 0,
  "FileHash" VARCHAR(64),
  "ContentType" VARCHAR(100) DEFAULT 'text/plain',
  "IsActive" BOOLEAN NOT NULL DEFAULT true,
  "UploadedAt" TIMESTAMP NOT NULL DEFAULT NOW(),
  "UploadedBy" VARCHAR(255),
  "LastAccessedAt" TIMESTAMP,
  CONSTRAINT "UQ_InputFile_Container_Blob" UNIQUE ("ContainerName", "BlobName")
);

CREATE INDEX "IX_InputFiles_IsActive" ON "InputFiles" ("IsActive");
CREATE INDEX "IX_InputFiles_ContainerName" ON "InputFiles" ("ContainerName");

-- Add FK column to Scenarios (nullable during migration)
ALTER TABLE "Scenarios"
ADD COLUMN "BaseInputFileId" INTEGER;

-- ============================================================================
-- PHASE 2: Migrate existing data
-- ============================================================================

-- Create InputFile records for each unique InputFile path
INSERT INTO "InputFiles"
  ("Name", "Description", "StorageProvider", "ContainerName", "BlobName", "UploadedAt", "UploadedBy")
SELECT DISTINCT
  -- Extract filename from path
  CASE
    WHEN "InputFile" LIKE '%/%' THEN substring("InputFile" from '([^/]+)$')
    WHEN "InputFile" LIKE '%\\%' THEN substring("InputFile" from '([^\\]+)$')
    ELSE "InputFile"
  END as "Name",
  'Migrated from legacy scenario' as "Description",
  2 as "StorageProvider",  -- LocalFileSystem
  'legacy-files' as "ContainerName",  -- Default container for migrated files
  "InputFile" as "BlobName",  -- Store original path temporarily
  NOW() as "UploadedAt",
  'system-migration' as "UploadedBy"
FROM "Scenarios"
WHERE "InputFile" IS NOT NULL
  AND "InputFile" != '';

-- Update Scenarios to reference InputFiles
UPDATE "Scenarios" s
SET "BaseInputFileId" = (
  SELECT i."Id"
  FROM "InputFiles" i
  WHERE i."BlobName" = s."InputFile"
  LIMIT 1
)
WHERE s."InputFile" IS NOT NULL
  AND s."InputFile" != '';

-- ============================================================================
-- PHASE 3: Add constraints (after verification)
-- ============================================================================

-- Make BaseInputFileId NOT NULL (after all scenarios migrated)
-- ALTER TABLE "Scenarios" ALTER COLUMN "BaseInputFileId" SET NOT NULL;

-- Add FK constraint
ALTER TABLE "Scenarios"
ADD CONSTRAINT "FK_Scenarios_InputFiles"
FOREIGN KEY ("BaseInputFileId")
REFERENCES "InputFiles" ("Id")
ON DELETE RESTRICT;  -- Prevent deletion of files in use

-- ============================================================================
-- PHASE 4: Cleanup (after blob migration complete)
-- ============================================================================

-- Rename old column for safety
-- ALTER TABLE "Scenarios" RENAME COLUMN "InputFile" TO "InputFile_OLD";

-- Eventually drop old column
-- ALTER TABLE "Scenarios" DROP COLUMN "InputFile_OLD";

---

8. Security Considerations

8.1 Access Control

Container-Level Access:

• Each container has its own scoped SAS token
• Tokens are read-only for simulation operations
• Tokens expire after 1 hour
• HTTPS-only protocol enforced

Account-Level Keys:

• Only stored in API tier configuration
• Never exposed to EpanetConsoleCore in production
• Used only for upload operations
• Should be rotated regularly

Configuration Security:

• SAS tokens stored encrypted in configuration
• Account keys encrypted at rest
• Use Azure Key Vault for production secrets
• Separate credentials per environment

8.2 Network Security

• All blob access over HTTPS
• Optional: Restrict blob access to specific IP ranges
• Optional: Use private endpoints for blob access
• Optional: Enable Azure Storage firewall rules

8.3 Data Integrity

• File hash (SHA256) stored in database
• Verify hash after download (optional)
• Detect file corruption or tampering
• Soft delete for recovery

---

9. Configuration

9.1 API Configuration (appsettings.json)

{
  "ConnectionStrings": {
    "PSQLDB": "Host=...;Database=...;Username=...;Password=..."
  },

  "InputFileStorage": {
    "Provider": "AzureBlob",

    "AzureBlob": {
      "ServiceUrl": "https://hydrotrekst.blob.core.windows.net",
      "AdminAccountKey": "encrypted-key-from-keyvault",

      "Containers": {
        "network-client1": {
          "Description": "Client 1 network files"
        },
        "network-client2": {
          "Description": "Client 2 network files"
        }
      },

      "SasTokenExpiry": "01:00:00",
      "MaxUploadSizeMB": 500,
      "AllowedExtensions": [".inp"]
    },

    "LocalFileSystem": {
      "BasePath": "C:\\HydroTrek\\InputFiles",
      "MaxUploadSizeMB": 500
    },

    "TempFileCleanup": {
      "Enabled": true,
      "CleanupIntervalMinutes": 60,
      "MaxAgeHours": 24
    }
  }
}

9.2 EpanetConsoleCore Configuration

{
  "ConnectionStrings": {
    "PSQLDB": "Host=...;Database=...;Username=...;Password=..."
  },

  "InputFileStorage": {
    "Provider": "AzureBlob",

    "AzureBlob": {
      "ServiceUrl": "https://hydrotrekst.blob.core.windows.net",

      "Containers": {
        "network-client1": {
          "SasToken": "sv=2021-06-08&ss=b&srt=sco&sp=rl&se=2025-12-31T23:59:59Z&st=..."
        },
        "network-client2": {
          "SasToken": "sv=2021-06-08&ss=b&srt=sco&sp=rl&se=2025-12-31T23:59:59Z&st=..."
        }
      }
    },

    "LocalFileSystem": {
      "BasePath": "C:\\HydroTrek\\InputFiles"
    }
  },

  "TempFileCleanup": {
    "Enabled": true,
    "MaxAgeHours": 24
  }
}

---

10. Future Enhancements

10.1 Short Term

• File upload progress tracking
• Duplicate file detection (by hash)
• Bulk file upload
• File preview in UI

10.2 Medium Term

• File versioning system
• Automatic backup to secondary storage
• File usage analytics
• Cost tracking per container

10.3 Long Term

• Multi-region replication
• CDN integration for faster downloads
• Automatic file compression
• AWS S3 support as alternative provider
• Git-like version control for INP files

---

Document History

Version	Date	Author	Changes
1.0	2025-10-20	AI Assistant	Initial design document

---

End of Document