Skip to content

Data Model Overview.md

Jump to bottom
Kelsey Smuczynski edited this page Mar 24, 2026 · 1 revision

Data Model Overview

Core Domain Hierarchy

The codebase centers on this field-data chain:

Location → Thing → FieldEvent → FieldActivity → Sample → Observation
Model Description
Location A geographic point (Lat/Lon in WGS84) and site metadata
Thing A physical monitored feature at a location (e.g., a well, spring, or sensor); holds construction metadata
FieldEvent A single site visit on a specific date by field personnel
FieldActivity A specific task performed during a field event (e.g., manual water level measurement)
Sample A physical or digital "slice" of data collected during an activity
Observation The actual measured value or result (e.g., pH=7.2, depth=15.4m); links to a Parameter

Core Supporting Models

  • Contact, Phone, Email, Address
  • Group and GroupThingAssociation
  • Asset and AssetThingAssociation
  • Sensor and Deployment
  • Parameter and AnalysisMethod
  • LexiconCategory, LexiconTerm, LexiconTriple

Cross-Cutting Metadata Patterns

Several models use mixins or related tables for shared metadata:

  • Release status (ReleaseMixin)
  • Audit fields
  • Notes
  • Data provenance (DataProvenanceMixin)
  • Permission history
  • Status history

Thing and Location are especially important aggregation points.

Water-Well-Specific Depth

Thing contains many well-specific attributes, including:

  • depth, casing, pump, completion, formation zone
  • location history, contacts, field events, deployments
  • groups, screens, measuring point history, monitoring frequency history

Legacy NMA_* Models

The database includes a large family of legacy-oriented models in db/nma_legacy.py. These are still actively used by the transfer pipeline and admin interface:

  • Chemistry sample info
  • Field parameters and hydraulics
  • Soil/rock results and stratigraphy
  • Surface water data and photos
  • Weather data and photos
  • Radionuclides, major/minor/trace chemistry
  • Pressure-daily water levels
  • NGWMN legacy views

Schema/Model Coupling

When a core model changes, you typically need to update all of:

  1. db/ model definitions
  2. Corresponding schemas/
  3. Alembic migrations
  4. Tests
  5. Transfer logic (if the field participates in legacy migration)

ERD

The repo contains generated ERD artifacts at the project root:

  • schema_erd.dot
  • schema_erd.png
  • schema_erd.svg

Regenerate with: python scripts/generate_erd.py

External Integrations

System Role
PostgreSQL + PostGIS Primary data store with spatial extensions
Authentik OIDC/OAuth2 provider for Admin interface and API auth
pygeoapi OGC API exposure at /ogcapi
Google App Engine Primary hosting environment
Google Cloud SQL Managed PostgreSQL in production
Google Cloud Storage Database backups, restore artifacts, transfer metrics/logs
GitHub Actions CI/CD pipeline for staging and production deployments
Apitally API traffic analytics and monitoring
Sentry Error tracking and release management
ArcGIS Feature Service Project-area boundary import

Data Flow Categories

  1. Legacy NM_Aquifer transfers — transfers/ pipeline migrates historical data into the new schema
  2. CLI and API bulk CSV uploads — ongoing data loads for well inventory and water level records; see CSV Ingestion and Validation
  3. Derived geospatial views — materialized views for pygeoapi (ogc_* resources)
  4. Post-Alembic data migrations — corrective or transitional data changes tracked in data_migration_history

Clone this wiki locally