Persist NM_Aquifer Legacy Identity and Relationships When Migrating to Thing

[chasetmartin]

JIRA Ticket

Summary

The migration to Thing is currently breaking NM_Aquifer legacy primary/foreign key relationships.
The core issue is failure to persist legacy NM_Aquifer identifiers (GUIDs) from Location and WellData as stable identities in Thing, which prevents downstream tables from maintaining correct foreign key relationships.

---

Background

Thing is acting as an identity unification layer for legacy NM_Aquifer entities (e.g., Location, Well, Site). However, current migrations treat Thing as a newly generated surrogate instead of a continuation of legacy identity.

As a result:

• Legacy PKs (e.g., LocationID, WellID) are not reliably persisted in Thing
• Downstream tables (e.g., WaterLevels, WeatherData) cannot reconstruct correct relationships
• Orphan prevention and FK enforcement fail because parent identity is ambiguous or lost

Issue 363 (adding thing_id FKs and cascade rules) is necessary but insufficient unless legacy identity is preserved first.

---

Core Requirements (Identity Preservation)

For NM_Aquifer entities that map to Thing:

1. Persist Legacy Primary Keys

   • Store Location and WellData NM_Aquifer GUIDs in Thing as a unique field, not primary key.
   • One legacy GUID → exactly one Thing
   • No regeneration or remapping of identity

2. Explicit Legacy → Thing Mapping

   • Location → Thing must persist LocationID
   • WellData → Thing must persist WellID
   • Mapping must be deterministic and cacheable

3. Foreign Key Continuity

   • Cascading transfer scripts must:
     • Resolve thing_id via legacy GUID lookup
     • Don't infer relationships indirectly (e.g., via PointID non-unique columns)
   • All child tables must reference the same Thing created from the legacy parent

---

Downstream Enforcement (After Identity Is Correct)

Once identity continuity is guaranteed, enforce relational integrity:

1. Child Models

• thing_id FK with NOT NULL
• ondelete="CASCADE"
• Python-level orphan prevention via @validates

2. Thing Reverse Relationships

• cascade="all, delete-orphan"
• passive_deletes=True

---

Acceptance Criteria

• Location and WellData NM_Aquifer legacy GUIDs are persisted in Thing
• No duplicate Thing rows for a single legacy entity
• Cascading transfers resolve parents via legacy GUIDs
• All Thing children have enforced thing_id FKs
• Orphan creation is impossible at DB and ORM levels
• Tests assert identity continuity across migrations

---

References

- Issue 363

[chasetmartin]

@kbighorse I think this is one of the top-level migration issue we discussed on Friday that cascades down to the children. But let me know if this doesn't make sense to you.

[kbighorse]

This issue has been fully implemented via PR #416 and related work.

Acceptance Criteria Verified

✅ Location and WellData NM_Aquifer legacy GUIDs persisted in Thing

• Thing.nma_pk_welldata stores legacy WellID
• Thing.nma_pk_location stores legacy LocationID
• Location.nma_pk_location preserves original UUID

✅ No duplicate Thing rows for a single legacy entity

• Transfer scripts use legacy GUID lookup to prevent duplicates

✅ Cascading transfers resolve parents via legacy GUIDs

• test_well_found_by_legacy_welldata_id verifies lookup
• test_well_found_by_legacy_location_id verifies lookup

✅ All Thing children have enforced thing_id FKs

• NMA_HydraulicsData, NMA_Stratigraphy, NMA_Radionuclides, NMA_AssociatedData, NMA_Soil_Rock_Results all have thing_id FK NOT NULL with ondelete='CASCADE'

✅ Orphan creation impossible at DB and ORM levels

• @validates('thing_id') decorators prevent None values
• FK constraints enforce referential integrity

✅ Tests assert identity continuity

• tests/integration/test_nma_legacy_relationships.py has comprehensive tests

Related

• Closes WellData and Location (anything that becomes a Thing) children should have enforced FK associations with orphan prevention #363 (via PR Chemistry relationships should enforce parent-child integrity #416)
• PR Chemistry relationships should enforce parent-child integrity #416 - Chemistry relationships enforce parent-child integrity