River Flows System - Design Specifications

System Architecture

1. Application Structure

riverflows/
├── management/
│   └── commands/
│       ├── download_process_hdf.py    # Main entry point
│       ├── upsert_flows.py           # Flow data processing
│       ├── upsert_dailies.py         # Daily aggregation
│       └── [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

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

3. Data Processing Pipeline

HDF5 Processing

1. File Download (download_process_hdf.py)
   ↓
2. HDF5 Extraction (hdf_utils.py)
   ↓
3. Data Conversion (upsert_flows.py)
   ↓
4. Database Insertion (bulk_upsert_copy)
   ↓
5. Daily Aggregation (upsert_dailies.py)

Key Processing Steps

1. File Management

   • FTP download with paramiko
   • Local file extraction
   • Processed file tracking

2. Data Transformation

   • HDF5 to DataFrame conversion
   • Timestamp normalization
   • Station index mapping

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

   • /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

• TimescaleDB hypertables for time-series data
• Composite indexes on (time, station)
• Bulk insert operations
• Pre-aggregated daily data

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
• API key management
• Permission controls

Data Protection

• Secure FTP 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