WellData and Location (anything that becomes a Thing) children should have enforced FK associations with orphan prevention

[kbighorse]

Summary

Audit all models that are logical children of Thing or Location and ensure they have properly enforced FK relationships with orphan prevention.

Jira Reference

BDMS-461: NM Aquifer WellData and Location children (Thing and cascading) should have enforced FK associations with orphan prevention

Migration Criteria (from Jira)

1. Persist NM_Aquifer LocationID (PK, GUID) from Location table in new Thing
2. Persist NM_Aquifer WellID (PK, GUID) from WellData table in Ocotillo Thing
3. Use proper foreign key relationships from NM_Aquifer identifiers in cascading transfer scripts (such as water levels)

Feature Spec

See features/admin/well_data_relationships.feature for business requirements.

Audit Results

Thing Model

Field	Status	Notes
nma_pk_welldata	✅ Exists	Stores WellID from NM_Aquifer
nma_pk_location	✅ Added	Stores LocationID from NM_Aquifer

NMA Legacy Models (Updated for Integer PK Schema)

Model	Parent FK	NOT NULL	@validates	Reverse Rel	CASCADE	Status
NMA_Chemistry_SampleInfo	location_id	✅	✅	✅	✅	Complete
NMA_Stratigraphy	thing_id	✅	✅	✅	✅	Complete
NMA_HydraulicsData	thing_id	✅	✅	✅	✅	Complete
NMA_Radionuclides	thing_id	✅	✅	✅	✅	Complete
NMA_AssociatedData	thing_id	✅	✅	✅	✅	Complete
NMA_Soil_Rock_Results	thing_id	✅	✅	✅	✅	Complete

Note: NMA_Chemistry_SampleInfo FKs to Location (not Thing) because:

• LocationId matches Location.nma_pk_location with 99.95% match rate
• Only ~2 truly orphan records (vs ~71k with Thing matching)
• Avoids creating stub Things just for FK satisfaction

Acceptance Criteria

• Add nma_pk_location field to Thing
• nma_pk_welldata already exists in Thing
• Add @validates to Stratigraphy
• Add @validates to NMAHydraulicsData
• Add reverse relationship hydraulics_data to Thing
• Add reverse relationship radionuclides to Thing
• Make AssociatedData.thing_id NOT NULL + add @validates
• Add reverse relationship associated_data to Thing
• Make SoilRockResults.thing_id NOT NULL + add @validates
• Add reverse relationship soil_rock_results to Thing
• Change ChemistrySampleInfo to FK to Location (better match rate)
• Add reverse relationship chemistry_sample_infos to Location
• Create alembic migration for schema changes
• Unit tests for orphan prevention and cascade delete

Implementation Summary

All work completed in PR #416 (feature/thing-fk-enforcement):

Migrations

• 76e3ae8b99cb_enforce_thing_fk_for_nma_legacy_models.py - Added NOT NULL constraints and validators
• 3cb924ca51fd_refactor_nma_tables_to_integer_pks.py - Refactored to Integer PKs with nma_ prefixed legacy columns
• h1i2j3k4l5m6_chemistry_sampleinfo_fk_to_location.py - Changed Chemistry FK from Thing to Location

Key Changes

1. Integer PK Schema: All NMA legacy models now use Integer id as PK with nma_ prefixed legacy UUID columns for audit
2. FK Enforcement: All parent FK columns are NOT NULL with ondelete="CASCADE"
3. Chemistry → Location: ChemistrySampleInfo FKs to Location for 99.95% match rate
4. Validators: All models have @validates preventing orphan records
5. Reverse Relationships: Thing and Location models have back_populates for all child models
6. Tests: Comprehensive unit and integration tests for FK enforcement and cascade delete

Test Coverage

• tests/test_stratigraphy_legacy.py
• tests/test_hydraulics_data_legacy.py
• tests/test_radionuclides_legacy.py
• tests/test_associated_data_legacy.py
• tests/test_soil_rock_results_legacy.py
• tests/test_chemistry_sampleinfo_legacy.py
• tests/test_nma_chemistry_lineage.py
• tests/integration/test_nma_legacy_relationships.py

Reference Implementation

• Model: db/nma_legacy.py - All NMA models follow consistent pattern
• Thing: db/thing.py - Reverse relationships defined
• Location: db/location.py - Chemistry reverse relationship defined
• Tests: See test files listed above

Related

• PR Chemistry relationships should enforce parent-child integrity #416 - Feature implementation
• Issue Update skipped tests in test_thing.py to match current API response schema #437 - Follow-up: Update skipped tests in test_thing.py