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 and ensure they have properly enforced FK relationships with orphan prevention, following the pattern established in ChemistrySampleInfo.

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	❌ Missing	Need to add for LocationID

NMA Legacy Models

Model	thing_id FK	NOT NULL	@validates	Reverse Rel	Status
ChemistrySampleInfo	✅	✅	✅	✅	Complete
Stratigraphy	✅	✅	❌	✅	Needs @validates
NMAHydraulicsData	✅	✅	❌	❌	Needs @validates + reverse rel
NMARadionuclides	✅	✅	✅	❌	Needs reverse rel
AssociatedData	✅	❌	❌	❌	Needs NOT NULL + @validates + reverse rel
SoilRockResults	✅	❌	❌	❌	Needs NOT NULL + @validates + reverse rel

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
• Create alembic migration for schema changes
• Unit tests for orphan prevention and cascade delete

Reference Implementation

• Model: db/nma_legacy.py - ChemistrySampleInfo
• Tests: tests/test_chemistry_sampleinfo_legacy.py

Related

• PR Well data relationships should enforce parent-child integrity #416 - Feature spec for well data relationships