River Flows System - Design Specifications

System Architecture

1. Application Structure

riverflows/
├── management/
│   └── commands/
│       ├── download_process_hdf.py    # Main entry point (Vanilla)
│       ├── process_hdf_rsms.py        # Main entry point (RSMS)
│       ├── upsert_flows.py           # Flow data processing (Vanilla)
│       ├── upsert_flows_rsms.py      # Flow data processing (RSMS)
│       ├── upsert_dailies.py         # Daily aggregation (Vanilla)
│       └── [other management commands]
├── models.py                         # Database models
├── views.py                          # API endpoints
├── urls.py                           # URL routing
├── hdf_utils.py                      # HDF5 processing utilities
└── settings.py                       # Configuration

2. Database Design

Vanilla Implementation (ORSANCO)

TimescaleDB Integration

• Utilizes TimescaleDB extension for time-series data
• Implements hypertables for efficient time-based queries
• Uses composite primary keys (time, station) for data integrity

Core Tables

1. RiverFlow

   • TimescaleModel base
   • High-frequency flow measurements
   • Optimized for time-series queries

2. DailyRiverFlow

   • TimescaleModel base
   • Pre-aggregated daily statistics
   • Reduces query load for daily analysis

3. Station

   • Links HDF5 indices to physical locations
   • Maintains river and reach relationships
   • Stores geographic coordinates

RSMS Implementation

SQL Server Integration

• Standard SQL Server tables
• Custom indexing for RSMS requirements
• Bulk upsert operations

Core Tables

1. HEC_RASFlow

   • Stores flow measurements with area calculations
   • Includes river mile and tributary mile information
   • Optimized for bulk operations

2. HEC_RASFilesProcessed

   • Tracks processed HDF files
   • Maintains processing history
   • Supports file tracking

3. RivermileIndex

   • Maps HDF indices to physical locations
   • Includes river mile and tributary mile data
   • Supports station identification

3. Data Processing Pipeline

Vanilla Implementation

RSMS Implementation

Key Processing Steps

1. File Management

   • FTP/SFTP download
   • Local file extraction
   • Processed file tracking

2. Data Transformation

   • HDF5 to DataFrame conversion
   • Timestamp normalization
   • Station index mapping
   • Area calculations (RSMS only)

3. Database Operations

   • Bulk upsert operations
   • Transaction management
   • Error handling

4. API Design

REST Endpoints

1. Flow Data

   • /api/flows/ - List/Create flows
   • /api/flows/<id>/ - Retrieve/Update/Delete flow
   • /api/station-flows/<pk>/ - Station-specific flows

2. Daily Data (Vanilla only)

   • /api/daily-flows/ - Aggregated daily data
   • Filtering by river, reach, station

3. Reference Data

   • /api/rivers/ - River list
   • /api/reaches/ - Reach list
   • /api/stations/ - Station list

Query Parameters

• startdate: Start timestamp
• enddate: End timestamp
• station: Station ID
• river: River name
• reach: Reach name

5. Performance Considerations

Database Optimization

• Vanilla: TimescaleDB hypertables and compression
• RSMS: SQL Server table optimization
• Both: Appropriate indexing strategies

Processing Optimization

• Chunked data processing
• Parallel file processing
• Memory-efficient HDF5 reading
• Transaction batching

6. Error Handling

Processing Errors

• File processing tracking
• Transaction rollback
• Error logging
• Retry mechanisms

API Errors

• HTTP status codes
• Error messages
• Input validation
• Rate limiting

7. Security Implementation

Authentication

• Django REST framework authentication (Vanilla)
• SQL Server authentication (RSMS)
• API key management
• Permission controls

Data Protection

• Secure FTP/SFTP connections
• Encrypted credentials
• Input sanitization
• Access logging

8. Monitoring and Maintenance

Logging

• Processing status
• Error tracking
• Performance metrics
• API access logs

Maintenance

• Database cleanup
• File system management
• Index optimization
• Backup procedures