Skip to content

Home

Jump to bottom
Sweta Kutty edited this page Jun 26, 2026 · 23 revisions

Environmental Monitoring Data - WR Project Overview

Table of Contents

  1. Project Summary
  2. Architecture
  3. Code Structure
  4. Data Workflow
  5. Environment Configuration
  6. Search & Download

Storage Limits

The weekly csv datasync stores a significant amount of data to the PVC in OpenShift. We're currently capped at 40GB (a request has been made to increase this to 60GB).

You can check the utilization of the PVC in OpenShift here: https://console.apps.silver.devops.gov.bc.ca/k8s/ns/d23a53-prod/resourcequotas/storage-quota

Requests to increase resource limits are here: https://developer.gov.bc.ca/docs/default/component/platform-developer-docs/docs/automation-and-resiliency/request-quota-adjustment-for-openshift-project-set/

Project Summary

The Environmental Monitoring Data - WR (nr-enmods-wr) project is a full-stack application that manages environmental sampling observations and provides a searchable interface for retrieving water quality data. The system automates the ingestion of CSV observation data from AQI's S3 bucket, processes it through staging and operational tables, and exposes search capabilities through a React frontend backed by a NestJS API. It also exports spatial location data from AQI's APIs to NRS ObjectStore in multiple GIS formats.

Key Technologies:

  • Frontend: React/TypeScript with Vite
  • Backend: NestJS with TypeScript
  • Database: PostgreSQL with PostGIS extensions
  • Infrastructure: Docker, Kubernetes/OpenShift, Flyway migrations
  • Automation: GitHub Actions with scheduled cron workflows
  • Cloud Storage: AQI S3 (source data), NRS ObjectStore (spatial exports)

Architecture

The system follows a modern microservices-inspired architecture with clear separation of concerns:

┌─────────────────────────────────────────────────────────────┐
│                    Frontend (React/TypeScript)              │
│  - Basic Search (Location, Permit, Media, Observation Type) │
│  - Advanced Search (Method, Agency, Classification, etc.)   │
│  - CSV Download Results                                     │
└──────────────────────────┬──────────────────────────────────┘
                           │ HTTP/REST
┌──────────────────────────▼──────────────────────────────────┐
│          Backend API (NestJS)                               │
│  ┌────────────────┬─────────────┬──────────────────┐        │
│  │  Search Svc    │  Geodata    │  S3 Sync Log     │        │
│  │  (Query)       │  (Location) │  (Metrics)       │        │
│  └────────────────┴─────────────┴──────────────────┘        │
└──────────────────────────┬──────────────────────────────────┘
                           │ TypeORM
┌──────────────────────────▼──────────────────────────────────┐
│      PostgreSQL Database                                    │
│  ┌───────────────────────────────────────────────────────┐  │
│  │ AQI_CSV_IMPORT_OPERATIONAL  (Live Search Data)        │  │
│  │ AQI_CSV_IMPORT_STAGING      (Staging/Import Buffer)   │  │
│  │ Materialized Views          (Dropdown Lists)          │  │
│  │ FileInfo, S3SyncLog, etc.   (Metadata)                │  │
│  └───────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│        AQI S3 & NRS ObjectStore                             │
│  - CSV observation data files (from AQI S3)                 │
│  - GeoPackage, GDB, CSV spatial exports (to NRS ObjectStore)│
└─────────────────────────────────────────────────────────────┘

Code Structure

Backend Directory Layout

backend/src/
├── app.module.ts                    # Root NestJS module (imports all services)
├── app.controller.ts                # Health checks, root endpoints
├── app.service.ts                   # Core application logic
├── main.ts                          # Application bootstrap
├── ormconfig.ts                     # Database configuration
├── metrics.controller.ts            # Prometheus metrics endpoint
│
├── search/                          # Observation Search Module
│   ├── search.service.ts            # Query builder, CSV streaming, statistics
│   ├── search.controller.ts         # REST endpoints for search/download
│   └── dto/
│       └── basicSearch.dto.ts       # Search filter criteria
│
├── observations/                    # Observations Data Module
│   ├── observations.service.ts      # Observation CRUD operations
│   ├── observations.controller.ts   # Observation endpoints
│   └── entities/
│       └── observation.entity.ts    # Observation data model
│
├── geodata/                         # Geodata Sync Module
│   ├── geodata.service.ts           # Cron job for location sync
│   ├── geodata.controller.ts        # Manual trigger endpoints
│   └── entities/
│       └── file-info.entity.ts      # File metadata tracking
│
├── materialized-views/              # Dropdown Data Views
│   ├── mv_aqi_sampling_agency.entity.ts
│   ├── mv_aqi_project.entity.ts
│   ├── mv_aqi_location_type.entity.ts
│   ├── mv_aqi_medium.entity.ts
│   ├── mv_aqi_observed_property_*.entity.ts  # Multiple views
│   └── ... (18 total materialized views)
│
├── aqi-csv-import-operational/      # CSV Import Status Module
│   ├── aqi-csv-import-operational.service.ts
│   └── entities/
│       └── aqi-csv-import-operational.entity.ts
│
├── s3_sync_log/                     # S3 Sync Logging Module
│   ├── s3_sync_log.service.ts       # Query sync history
│   └── entities/
│       └── s3_sync_log.entity.ts    # Sync attempt tracking
│
├── auth/                            # Authentication Module
│   ├── jwtauth.guard.ts             # JWT token validation
│   └── ... (Keycloak/IDIR integration)
│
├── common/                          # Shared Utilities
├── enum/                            # Enums and Constants
├── middleware/                      # HTTP Middleware (Logging, etc.)
├── util/                            # Helper Functions
└── validation/                      # Data Validation Rules

Frontend Directory Layout

frontend/src/
├── main.tsx                         # React entry point
├── App.tsx                          # Root component
├── keycloak.ts                      # IDIR authentication config
├── config.js                        # API endpoints, environment config
│
├── pages/                           # Route pages
│   ├── BasicSearch.tsx              # Basic search interface
│   ├── AdvancedSearch.tsx           # Advanced search interface
│   └── Results.tsx                  # Search results display
│
├── components/                      # Reusable UI components
│   ├── SearchFilters/               # Filter form builders
│   ├── ResultsTable/                # Results display grid
│   └── ... (UI widgets)
│
├── service/                         # API integration
│   ├── api.service.ts               # REST API client
│   ├── auth.service.ts              # Authentication logic
│   └── search.service.ts            # Search API wrapper
│
├── store/                           # State management
└── util/                            # Frontend helpers

Database Schema Key Tables

Data Tables:

  • aqi_csv_import_operational - Live searchable observations (20M+ rows, growing)
  • aqi_csv_import_staging - Temporary staging area during imports
  • aqi_csv_import_tmp - Swap buffer during staging→operational transition

Metadata Tables:

  • file_info - Tracks processed GeoPackage files
  • s3_sync_log - Records each data synchronization attempt

Materialized Views (18 total):

Dropdown options are computed once per week via materialized views and cached in the database. This is necessary because the source aqi_csv_import_operational table contains 20+ million records and growing, making it computationally expensive to calculate unique values on-demand.

Examples:

  • mv_aqi_sampling_agency - Unique agencies collecting samples
  • mv_aqi_project - Projects and their names
  • mv_aqi_analyzing_agency - Labs analyzing samples
  • mv_aqi_medium - Sample media types (water, soil, etc.)
  • mv_aqi_observed_property_* - Various analytical properties
  • mv_aqi_location_* - Location and location group hierarchies
  • And 6 more for methodology, QC, classification, and work orders

Why Materialized Views?

Querying 20 million operational records to extract unique values would be extremely slow if done on every page load. Instead:

  1. Weekly refresh: During the data sync job, all materialized views are rebuilt from fresh staging data
  2. Pre-computed distinctness: Each view contains only unique values for its category
  3. Instant queries: Frontend calls the materialized views API endpoint once at app startup
  4. Redux caching: Values are stored in Redux state and reused for the entire user session
  5. Result: Dropdown initialization takes milliseconds instead of seconds

Data Workflow

The system has TWO independent scheduled jobs that work together:

  1. Daily Spatial/Location Export - Exports sampling location data and spatial analysis to GeoPackage files
  2. Weekly Observation Data Sync - Loads CSV observation data from external systems into the search database

Daily Spatial Data Export (GeodataService)

Runs daily on a configurable schedule (default: 2 AM Pacific Time):

┌──────────────────────────────────────────────────────────────────┐
│        DAILY SPATIAL EXPORT WORKFLOW (GeodataService)            │
│        Runs on: GEODATA_REFRESH_CRON schedule                    │
│                                                                  │
│  1. FETCH EXTENDED ATTRIBUTES                                    │
│     └─ Query AQI API for field metadata                          │
│     └─ Get Closed Date, Established Date, Well Tag IDs           │
│                                                                  │
│  2. FETCH PREVIOUS GEOPACKAGE                                    │
│     └─ Load last successfully exported GeoPackage from S3        │
│     └─ Track timestamp of last export                            │
│                                                                  │
│  3. FETCH UPDATED SAMPLING LOCATIONS                             │
│     └─ Query AQI API for locations modified since last export    │
│     └─ Paginate through results (cursor-based)                   │
│     └─ Transform to GeoJSON format                               │
│                                                                  │
│  4. GENERATE NEW GEOPACKAGE                                      │
│     └─ Create GeoPackage file with sampling locations            │
│     └─ Include extended attributes (closed date, etc.)           │
│     └─ Generate File Geodatabase (GDB) format                    │
│     └─ Generate CSV export of locations                          │
│                                                                  │
│  5. PERFORM SPATIAL ANALYSIS                                     │
│     └─ Intersect new locations with existing boundaries          │
│     └─ Generate derivative GeoPackage files                      │
│     └─ Create location group hierarchies                         │
│                                                                  │
│  6. UPLOAD TO NRS OBJECTSTORE                                    │
│     └─ Upload GeoPackage files to NRS ObjectStore                │
│     └─ Upload Geodatabase exports to NRS ObjectStore             │
│     └─ Upload CSV files to NRS ObjectStore                       │
│     └─ Store in: objectstore://bucket/folder/                    │
│                                                                  │
│  7. LOG FILE METADATA                                            │
│     └─ Save file_name and date_created to FILE_INFO table        │
│     └─ Track all exported files for next cycle                   │
│     └─ Clean up temporary files from /tmp/geodata/               │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Key Details:

  • Service: GeodataService.processAndUpload() (Cron decorator triggers it)
  • Frequency: Daily (default 2 AM PT, configurable via GEODATA_REFRESH_CRON)
  • Data Source: AQI APIs (sampling locations and extended attributes)
  • Destination: NRS ObjectStore + PostgreSQL (FileInfo table)
  • Time Zone: America/Vancouver (Pacific Time)
  • Duration: Varies based on data volume (typically 15-30 minutes)

What Gets Exported:

  • GeoPackage files uploaded to NRS ObjectStore
  • File Geodatabase (.gdb) exports uploaded to NRS ObjectStore
  • CSV location exports uploaded to NRS ObjectStore
  • Location group hierarchies and spatial analysis derivatives
  • Extended attributes (well tags, closure dates, etc.)

Weekly Observation Data Sync

Runs weekly on a configurable schedule (typical: Sunday 1 AM Pacific Time):

┌──────────────────────────────────────────────────────────────────┐
│         WEEKLY OBSERVATION SYNC WORKFLOW                         │
│         CSV Files → Staging → Operational Table                  │
│         Runs on: OBS_REFRESH_CRON schedule                       │
│                                                                  │
│  1. FETCH & LOAD                (AQI S3 → PostgreSQL Staging)    │
│     └─ Pull CSV files from AQI's S3 bucket                       │
│     └─ Stream into AQI_CSV_IMPORT_STAGING table if               |
|        files changed since last import                           │
│     └─ Log start time in S3_SYNC_LOG                             │
│                                                                  │
│  2. REFRESH MATERIALIZED VIEWS  (Generate Dropdown Data)         │
│     └─ Execute REFRESH on all 18 materialized views              │
│     └─ Updates occur in ~seconds (based on staging data)         │
│     └─ Ensures UI dropdowns show latest values                   │
│                                                                  │
│  3. LOG SUCCESS METRICS        (Update S3_SYNC_LOG)              │
│     └─ Record row counts, timestamps, processing duration        │
│     └─ Calculate statistics (unique locations, date ranges, etc.)│
│     └─ Store in S3_SYNC_LOG for monitoring                       │
│                                                                  │
│  4. SWAP TABLES                (Staging → Operational)           │
│     └─ Execute SQL function: run_aqi_table_swap()                │
│     └─ Pre-build all indexes on STAGING table                    │
│     └─ Atomically rename tables:                                 │
│        - OPERATIONAL → TMP (backup)                              │
│        - STAGING → OPERATIONAL (make live)                       │
│        - TMP → STAGING (reset for next week)                     │
│     └─ Search service now queries fresh data                     │
│     └─ Log completion time and swap status                       │
│                                                                  │
└──────────────────────────────────────────────────────────────────┘

Key Details:

  • Trigger: Cron schedule via NestJS @nestjs/schedule module
  • Frequency: Weekly (configurable, default: Sunday 1 AM PT)
  • Data Source: CSV files in AWS S3 bucket
  • Destination: PostgreSQL observation tables + materialized views
  • Atomicity: Zero-downtime atomic table swap
  • Fallback: Previous operational data retained as backup during swap

Detailed Job Documentation

Daily Spatial Export Job

Cron Expression: Controlled by GEODATA_REFRESH_CRON environment variable

Example Schedules:

  • 0 2 * * * = Every day at 2 AM
  • 0 2 * * 0 = Every Sunday at 2 AM
  • 0 2 * * 1-5 = Every weekday at 2 AM

What It Does:

The GeodataService fetches the latest sampling location data from the external AQI system and exports it as spatial data formats:

  1. Fetch Extended Attributes

    • Queries AQI API for metadata attributes (closed dates, established dates, well tags)
    • Stores in memory for enriching location data
    • Used to track location lifecycle information

  2. Download Previous Export (from S3)

    • Queries FILE_INFO table for most recent export
    • Downloads that GeoPackage file from ObjectStore as baseline
    • Compares timestamps to identify which locations changed since last run

  3. Fetch Updated Locations from AQI API

    • Queries AQI system for locations modified since last export time
    • Uses cursor-based pagination for large datasets (API returns 1000 records per request)
    • Transforms location data to GeoJSON format
    • Collects extended attributes for each location

  4. Generate GeoPackage & Derivatives

    • Creates new GeoPackage file with updated locations
    • Generates File Geodatabase (.gdb) format for ArcGIS compatibility
    • Exports location data as CSV
    • Creates location group hierarchies and related datasets
    • Performs spatial intersections with administrative/political boundaries

  5. Upload to S3 (ObjectStore)

    • Uploads all generated files to configured S3 bucket
    • Bucket structure: s3://BUCKET/FOLDER/[geopackage|gdb|csv]
    • Example: s3://enmods_test/Data_Catalogue_TEST/sampling_locations_20250116_120515.gpkg

  6. Log Metadata & Clean Up

    • Records file name and creation timestamp in FILE_INFO table
    • Deletes temporary files from /tmp/geodata/ to free disk space
    • Logs total processing duration

Configuration Environment Variables:

# Cron Schedule
GEODATA_REFRESH_CRON="0 2 * * *"              # Daily at 2 AM

# External AQI APIs
BASE_URL_BC_API="https://bcenv-enmods-test.aqsamples.ca/api/"
SAMPLING_LOCATIONS_ENDPOINT="v1/samplinglocations"
EXTENDED_ATTRIBUTES_ENDPOINT="v1/extendedattributes"

# NRS ObjectStore (destination for spatial exports)
OBJECTSTORE_URL="https://nrs.objectstore.gov.bc.ca"
OBJECTSTORE_BUCKET="enmods_test"              # Bucket name (test vs prod)
OBJECTSTORE_FOLDER="Data_Catalogue_TEST"      # Folder within bucket

Outputs:

  • GeoPackage files uploaded to NRS ObjectStore
  • File Geodatabase exports uploaded to NRS ObjectStore
  • CSV location exports uploaded to NRS ObjectStore
  • FILE_INFO database table entries
  • Application logs with timing/diagnostics

Typical Duration: 15-30 minutes (depends on number of location updates)

Weekly Observation Data Sync Job

Cron Expression: Controlled by OBS_REFRESH_CRON environment variable

Example Schedules:

  • 0 1 * * 0 = Every Sunday at 1 AM
  • 0 8 * * 1 = Every Monday at 8 AM
  • 0 */6 * * * = Every 6 hours

What It Does:

The observation sync pulls CSV files from AQI's S3 bucket, loads them into a staging table, refreshes dropdown data, and then atomically swaps the staging and operational tables to make the new data live.

  1. Stream CSV from AQI S3 to Staging Table

    • Application reads CSV files from AQI's S3 bucket
    • Parses CSV content
    • Transforms to match AQI_CSV_IMPORT_STAGING schema
    • Bulk inserts rows via TypeORM repository
    • Tracks import start time in S3_SYNC_LOG table

    Key Services Involved:

    • Main cron handler that orchestrates import
    • ObservationsService.bulkUpsert() - Database insertion

    Database State After Step 1:

    • AQI_CSV_IMPORT_STAGING contains fresh data
    • Original AQI_CSV_IMPORT_OPERATIONAL unchanged

  2. Refresh Materialized Views

    • After data loads into staging, the system executes REFRESH on all 18 materialized views
    • Each view rebuilds from fresh staging data in seconds

    Why this matters:

    The operational table contains 20+ million records. Computing unique values on-demand would require expensive DISTINCT queries across the entire dataset each time a user opens the app. Materialized views pre-compute these unique values during the weekly sync, allowing the frontend to fetch them instantly.

    Examples of what refreshes:

    • mv_aqi_sampling_agency → Unique agencies collecting samples
    • mv_aqi_medium → All media types (water, soil, etc.)
    • mv_aqi_project → Distinct projects and project names
    • And 15 more views for methods, locations, agencies, classifications, etc.

    Database Operations:

    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_aqi_sampling_agency;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_aqi_medium;
-- ... (18 views total, referenced in V1.0.5__aqi_materialized_vws.sql)

    Performance Result:

    • Without materialized views: Frontend would need to query 20M+ record table for each dropdown → seconds of latency
    • With materialized views: Frontend fetches pre-computed unique values → milliseconds
    • Redux caching: Values cached in Redux store on app startup → instant filter selections with zero network calls

    User Impact:

    • Frontend dropdowns populated with latest options (refreshed weekly)
    • No stale/removed values remain in filters
    • Basic Search and Advanced Search stay current
    • Snappy dropdown performance thanks to Redux in-memory caching

  3. Log Success Metrics to S3_SYNC_LOG

    • Throughout import, system collects:
      • Row counts: How many observations loaded
      • Processing duration: Total cycle time
      • Status transitions: IN_PROGRESS → SUCCESS or FAILED
      • Timestamps: start_time and finish_time for monitoring
      • Source folder: Which S3 folder was processed

    Example S3_SYNC_LOG records:

    id  | process_name              | status    | start_time       | finish_time      | rows_affected
--- | ------------------------- | --------- | ---------------- | ---------------- | --------
1   | public.AQI_AWS_S3_SYNC    | SUCCESS   | 2025-01-15 08:00 | 2025-01-15 08:15 | 45,234
2   | public.AQI_AWS_S3_SYNC    | SUCCESS   | 2025-01-08 08:00 | 2025-01-08 08:14 | 44,891

    Monitoring API Endpoint:

    • GET /s3-sync-logs - Lists all sync attempts with status/timing

  4. Swap Staging to Operational

    • After verification of staging data quality, atomic swap occurs
    • Ensures zero downtime during transition

    The Swap Process:

    -- Pre-swap state
OPERATIONAL (indexed)        STAGING (new data)       TEMP (empty)
├─ 44.8M rows                ├─ 45.2M rows
├─ All indexes built         ├─ All indexes built
└─ Being queried             └─ Ready to go live

           ↓ Atomic Swap (milliseconds)

-- Post-swap state
OPERATIONAL (indexed)        STAGING (backup)         TEMP (old data)
├─ 45.2M rows                ├─ 44.8M rows
├─ All indexes built         └─ Previous live data
└─ Search queries run here

    SQL Function: run_aqi_table_swap() in migrations/sql/V1.0.8__staging_operational_swap.sql

    Step-by-step Atomicity:

    • Verify staging readiness

      • Check row counts are reasonable
      • Confirm all indexes rebuilt successfully
      • No corruption or errors detected

    • Rename tables (atomic operation)

      ALTER TABLE public.AQI_CSV_IMPORT_OPERATIONAL RENAME TO aqi_csv_import_tmp;
ALTER TABLE public.AQI_CSV_IMPORT_STAGING RENAME TO aqi_csv_import_operational;
ALTER TABLE public.aqi_csv_import_tmp RENAME TO aqi_csv_import_staging;
      • Happens in ~1-2 milliseconds
      • All indexes follow the table rename
      • No data moved, only metadata changed
      • Search queries automatically hit fresh data

    • Truncate old data from new staging

      TRUNCATE TABLE public.aqi_csv_import_staging;
      • Reset staging to empty state
      • Ready for next week's import

    • Log completion

      • Record swap status as SUCCESS
      • Set finish_time in S3_SYNC_LOG
      • Alert on any failures

Why This Design:

  • Atomicity: Either swap fully succeeds or rolls back completely
  • Zero downtime: Readers never locked; queries seamlessly transition
  • No data duplication: Single set of data in one table at a time
  • Fast recovery: Previous data remains in backup until overwritten

Configuration Environment Variables:

# Cron Schedule
OBS_REFRESH_CRON="0 1 * * 0"                # Weekly Sunday 1 AM

# AQI S3 (CSV observation data source)
# CSV files are accessed from AQI's S3 bucket
# Credentials configured separately for AQI S3 access

Outputs:

  • Populated AQI_CSV_IMPORT_OPERATIONAL table (live search data)
  • Refreshed materialized views (dropdown options)
  • S3_SYNC_LOG entries (audit trail)
  • Application logs with timing

Typical Duration: 5-20 minutes (depends on CSV file size and row count)

User Experience Flow

  1. User opens Frontend → React app loads, displays search page
  2. Redux initializes dropdown data → On first load, fetch all dropdown options once from materialized views
  3. Dropdown state cached in Redux store → All subsequent filter selections use in-memory Redux state (no API calls)
  4. User selects from dropdowns → Values retrieved from Redux store instantly
  5. User clicks Search → Frontend sends query to /search endpoint with selected filters
  6. Backend queries → SearchService hits AQI_CSV_IMPORT_OPERATIONAL table
  7. Results stream to CSV → Browser downloads file with matching observations
  8. File contains data → From last week's successful swap + refresh cycle

Redux Dropdown Strategy:

Dropdown values are cached in Redux state to provide optimal performance:

  • Single query per session: Dropdown data is fetched from materialized views once when the app loads
  • In-memory caching: Redux store holds all dropdown options (agencies, media types, methods, etc.)
  • Instant UI updates: Filter selections are snappy with no network latency
  • Weekly updates: New dropdown options appear after the weekly materialized view refresh
  • Minimal API calls: Reduces database load by eliminating repeated dropdown queries

During data sync, some timestamp fields contained values that were not in a valid IOS 8601 timestamp format. As a result, the system sttempted to interpret part of the timestamp(for example, -16:05) as a timezone offset. Since -16.05 is not a valid timezone, the sync job failed when attempting to insert the data into a TIMESTAMPZ column. The following timestamp fields required validation ans conversion: field_visit_start_time field_visit_end_time observed_date_time observed_date_time_start observed_date_time_end anaylzed_date_time lab_arrival_date_time lab_prepared_date_time

During the copying process, each timestamp value is validated to ensure it conforms to the ISO 8601 standard. If a timestamp is found to be invalid it is automaticalyy transformed into a valid timestamp before written to database. Example Input: 2026-03-05T16:00-16:05
Output: 2026-03-05T16:00:00-07:00 In the example the invalid timezone offset is corrected and the timestamp is reformatted into a valid ISO 8601 representation.

The transformation is performed using Miller, a command-line tool that can process and transform CSV data as it is streamed. As each row is processed: Timestamp fields are validated. Invalid timestamp values are converted into a valid ISO 8601 format. The corrected timestamp is parsed and stored in the database as a TIMESTAMPTZ value, ensuring consistent UTC-based storage. This approach allows data synchronization to continue successfully.

Environment Configuration

All behavior is parameterized through .env file variables. This allows different configurations per deployment environment (dev, test, prod).

Database Configuration

# PostgreSQL Connection
POSTGRES_PASSWORD="enmodswr_password"           # DB password
POSTGRES_USER="enmodswr_user"                  # DB user
POSTGRES_DATABASE="enmodswr"                   # Database name
POSTGRES_HOST="localhost"                      # Host (localhost in dev, service name in k8s)

When to use:

  • Development: localhost with local PostgreSQL
  • Docker Compose: Service name (e.g., postgres)
  • OpenShift/K8s: Service DNS name (e.g., postgres.default.svc.cluster.local)

Data Source Configuration

# AQI System API
BASE_URL_BC_API="https://bcenv-enmods-test.aqsamples.ca/api/"  # External AQI API base
API_KEY="API_KEY_VALUE"                      # API key for AQI system (secret!)
AUTH_TOKEN="AUTH_TOKEN_VALUE"                # User auth token (secret!)
# Different keys for test vs production environments

What this does:

  • Defines which external AQI system to pull from
  • Different API keys per environment (test vs prod)
  • Used by GeodataService to fetch sampling locations and extended attributes
  • Note: API_KEY and AUTH_TOKEN are sensitive—store in secure environment variables only

S3 / Object Storage Configuration

# NRS ObjectStore (for spatial data exports)
OBJECTSTORE_URL="https://nrs.objectstore.gov.bc.ca"  # NRS ObjectStore endpoint
OBJECTSTORE_ACCESS_KEY="ACCESS_KEY"                  # ObjectStore access key (secret!)
OBJECTSTORE_SECRET_KEY="SECRET_KEY"                  # ObjectStore secret key (secret!)
OBJECTSTORE_BUCKET="enmods_test"                     # Bucket name (enmods_test, enmods_prod, etc.)
OBJECTSTORE_FOLDER="Data_Catalogue_TEST"             # Folder path within bucket

# AQI S3 (for CSV observation data source)
# AQI provides CSV files in their S3 instance
# Credentials configured separately for AQI S3 access

# Location data endpoints (from AQI APIs)
SAMPLING_LOCATIONS_ENDPOINT="v1/samplinglocations"
SAMPLING_LOCATION_GROUPS_ENDPOINT="v1/samplinglocationgroups"
EXTENDED_ATTRIBUTES_ENDPOINT="v1/extendedattributes"

What this does:

  • NRS ObjectStore: Where spatial exports are uploaded (GeoPackage, GDB, CSV from GeodataService)
  • AQI S3: Source of CSV observation data files (imported weekly via ObservationsService)
  • AQI APIs: Source of sampling location and extended attribute data (queried daily by GeodataService)
  • Different credentials and bucket names per environment (test vs prod)
  • Note: OBJECTSTORE_ACCESS_KEY and OBJECTSTORE_SECRET_KEY are sensitive—store in secure environment variables only

Scheduling Configuration

# *** These are NOT in the provided .env but would be set in deployment configs ***
# See values.yaml for production and test environments

OBS_REFRESH_CRON="0 1 * * 0"           # Sync CSV observations from AQI S3 every Sunday 1 AM
GEODATA_REFRESH_CRON="0 2 * * *"        # Export spatial data to NRS ObjectStore daily 2 AM

Cron Format: minute hour day month dayofweek

Examples:

  • 0 2 * * 0 = Sunday 2 AM
  • 0 8 * * 1 = Monday 8 AM
  • 0 */6 * * * = Every 6 hours
  • 0 0 * * * = Daily midnight
  • @weekly = Every Sunday midnight

Where configured:

  • Dev/Local: .env file
  • Test/Prod: charts/app/values.yaml (Helm values)
  • GitHub Actions: .github/workflows/scheduled.yml

What happens:

  • Backend uses @nestjs/schedule module to register cron jobs
  • GeodataService.processAndUpload() - Runs on GEODATA_REFRESH_CRON
  • Orchestrates full ETL: load → refresh views → swap tables → log metrics

Search & Download

Search Architecture (with Materialized View & Redux Caching)

The search system provides two interfaces. All dropdown options are populated from materialized views on app startup and cached in Redux for instant interactions.

1. Basic Search

Filter by commonly used fields (values from materialized views):

  • Location Name
  • Location Type (from mv_aqi_location_type)
  • Permit Number
  • Media Type (from mv_aqi_medium) - water, soil, etc.
  • Observed Property (from mv_aqi_observed_property_*)

2. Advanced Search

Additional filtering (values from materialized views):

  • Analytical Method (from mv_aqi_analysis_method)
  • Analyzing Agency (lab) (from mv_aqi_analyzing_agency)
  • Observed Property ID
  • Work Order Number
  • Sampling Agency (from mv_aqi_sampling_agency)
  • Collection Method (from mv_aqi_collection_method)
  • QC Sample Type (from mv_aqi_qc_type)
  • Data Classification (from mv_aqi_data_classification)
  • Sample Depth (from mv_aqi_sample_depth)
  • Units (from mv_aqi_units)
  • Specimen ID
  • Projects (from mv_aqi_project)
  • Date Range

Dropdown Loading:

Dropdown values are fetched once when the app loads and cached in Redux state. This single API call replaces what would otherwise be hundreds of individual dropdown API calls throughout the user's session.

Query Execution

Endpoint: POST /search

Process:

  1. Frontend sends BasicSearchDto with selected filters
  2. SearchService.formulateSqlQuery() builds parameterized WHERE clause
  3. Constructs safe SQL: 
    SELECT * FROM aqi_csv_import_operational
WHERE location_id = ANY($1)
  AND locationType = $2
  AND medium = ANY($3)
  AND observed_date_time_start >= $4
  -- ... (and more conditions)
LIMIT 100,000
  4. Queries execute against AQI_CSV_IMPORT_OPERATIONAL table
  5. Results stream to CSV file in /data/ directory
  6. Browser downloads CSV

Return value: CSV file with matching observations (max 100,000 rows)

Statistics Collection

While streaming results to CSV, the system collects:

  • recordCount: Total rows matched
  • uniqueLocations: How many unique location IDs
  • minObservationDate: Earliest date in results
  • maxObservationDate: Latest date in results

These are returned in HTTP response headers for audit/monitoring.

Dropdown Data Flow & Redux Caching

Architecture:

PostgreSQL                Frontend (React/Redux)
───────────────────────  ──────────────────────
20M+ obs records         1. App loads
                         2. Fetch dropdown data
18 Materialized   ────→  3. Redux store initialized
Views w/ unique          4. Dropdown options cached
values                   5. User selects filters
                         6. Redux provides values
                         (No API calls for filter selections)

Data Flow:

  1. Weekly: Materialized views refresh during data sync job
  2. Weekly: All unique values pre-computed and stored in views
  3. App startup: Frontend queries materialized views API endpoint once
  4. Redux: All dropdown options stored in Redux state machine
  5. User interaction: Filter selections read from Redux store (instant, no network calls)
  6. Weekly: New/updated dropdown options appear after next materialized view refresh

Why Redux?

  • Performance: No network call for every dropdown interaction
  • UX: Instant filter selections
  • Scalability: Eliminates 100+ potential API calls per user session
  • Reliability: Works offline after initial load

Performance Optimization

Indexes on OPERATIONAL table:

  • Rebuilt weekly after swap (in V1.0.8__staging_operational_swap.sql)
  • Cover frequently searched columns:
    • location_id
    • locationType
    • medium
    • observed_date_time_start/end
    • observed_property_id
    • sampling_agency
    • analyzing_agency
    • project
    • analysis_method
    • collection_method
    • qc_type
    • data_classification
    • depth_upper
    • lab_batch_id
    • specimen_name

Result: Queries on 20M+ rows complete in <1 second

Consuming APIs

All the APIs that have been implemented for WR can be found at: https://nr-enmods-wr-test-frontend.apps.silver.devops.gov.bc.ca/api/docs

This page will list all the APIs and give some usage examples. Below you will find more detailed description about the following APIs as they are the most important ones:

  1. Search and Download
  2. Spatial Reporting

Search and Download

The Search API allows clients to query the backend for specific data records based on various filters and criteria. It is typically used to retrieve environmental monitoring data, observations, or other domain-specific records stored in the system. The API supports query parameters for filtering, sorting, and pagination.

The general format for this search API is ${WR-URL}/api/v1/search/downloadReport

In order to use this API, a user can either use Postman to consume it or they can use the cURL command. The cURL command is as follows: curl --location ${WR-URL}/api/v1/search/downloadReport.

If you copy-paste the command from above then you will be given an error saying "Please provide at least one search criteria." If you are wondering how to add search criteria to the API then follow the instructions below.

Available search criteria:

  1. locationTypeCustomId
  2. locationName -- note that the parameter name is locationName but you MUST use the value of location ID, i.e., for the location name use location id
  3. permitNumber
  4. fromDate -- the format should be YYYY-MM-DDT08:00:00.000Z
  5. toDate -- the format should be YYYY-MM-DDT08:00:00.000Z
  6. media
  7. projects
  8. observedProperty
  9. workOrderNoText
  10. samplingAgency
  11. analyzingAgency
  12. analyticalMethod -- note that the parameter name is analyticalMethod but you MUST use the value of analytical method ID, i.e., for the analytical method name use analytical method id
  13. collectionMethod
  14. qcSampleType
  15. dataClassification
  16. sampleDepth
  17. labBatchId
  18. specimenId

Using a single search criteria

Here is an example of how to add a search criteria to your cURL statement

General format: curl --location ${WR-URL}/api/v1/search/downloadReport?criteriaName={valueAsText}

Real-world examples:

Location Name

curl --location ${WR-URL}/api/v1/search/downloadReport?locationName=E273189

Analytical Method

curl --location ${WR-URL}/api/v1/search/downloadReport?analyticalMethod=X138

Using more than one search criteria

Location Name and Analytical Method

curl --location ${WR-URL}/api/v1/search/downloadReport?locationName=E273189&analyticalMethod=X138

Downloading the file

If you were to use the commands from above, then the result set would just be printed in the terminal session or wherever you made the cURL command from. In order to download the file all you have to do is append a > {fileName}.csv to the command. So, the command will now look like

curl --location ${WR-URL}/api/v1/search/downloadReport?locationName=E273189&analyticalMethod=X138 > ${fileName}.csv

Using a URL to download the exports

There is another way to download reports, this method is as easy as pasting a link in your browser and it will download a file. It allows the user to make multiple selections of the same parameter (see example below). If you wish to add more parameters then you can follow the same pattern described above. Essentially, simply using the URL will respond with the contents of the CSV instead of having to click the link to download the file.

e.g. clicking this should result in the file downloading https://nr-enmods-wr-test-frontend.apps.silver.devops.gov.bc.ca/api/v1/search/downloadReport?analyticalMethod=0550,0105,X316,0200

Note that with this method of downloading a file, we're keeping an HTTP connection active and waiting for the file to be created, so there is a limit as to how long we can keep the connection open. If it's open for too long, the response "Request timeout - export took too long" is returned. The max wait time is 5 minutes.

Monitoring & Troubleshooting

Health Check Endpoints

  • GET /health - Liveness probe (is app running?)
  • GET /health/ready - Readiness probe (can accept requests?)
  • GET /metrics - Prometheus metrics (CPU, memory, request latency, database pool status)

Sync Status

  • GET /s3-sync-logs - Lists all sync attempts with status/timing

Logs

  • Application logs in stdout (captured by container runtime)
  • Query logs (when PRISMA_LOGGING=query) show all SQL
  • Cron job progress logged with timestamps and error details

Deployment Environments

Development

  • Local PostgreSQL on localhost:5432
  • .env file with all configuration
  • Manual cron job triggers (via API endpoint)

Test

  • OpenShift pod with Postgres service
  • Helm values in charts/app/values-test.yaml
  • OpenShift Secret: nr-enmods-wr-test-backend (contains all environment variables)
  • Scheduled workflows via GitHub Actions
  • S3 bucket: enmods_test

Production

  • OpenShift pod with Postgres service
  • Helm values in charts/app/values-prod.yaml
  • OpenShift Secret: nr-enmods-wr-prod-backend (contains all environment variables)
  • Scheduled workflows via GitHub Actions (with approval gate)
  • S3 bucket: enmods_prod
  • Database backups enabled
  • Rolling deployments (zero downtime) ✅ Container Native - Docker, Kubernetes ready

For questions or to contribute, see CONTRIBUTING.md.

Clone this wiki locally