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) as stable identities in Thing, which prevents downstream tables from maintaining correct foreign key relationships.

All models that become or reference a Thing must preserve their original NM_Aquifer primary keys and use those identifiers consistently during cascading transfers.

---

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 all NM_Aquifer entities that map to Thing:

1. Persist Legacy Primary Keys

   • Store NM_Aquifer GUIDs in Thing (e.g., legacy_id, source_pk, or equivalent)
   • One legacy GUID → exactly one Thing
   • No regeneration or remapping of identity

2. Explicit Legacy → Thing Mapping

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

3. Foreign Key Continuity

   • Cascading transfer scripts must:
     • Resolve thing_id via legacy GUID lookup
     • Never infer relationships indirectly (e.g., via PointID only)
   • 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

3. Backfills

• Build O(1) legacy GUID → thing_id cache
• Filter to valid Things
• Skip and log orphan legacy records (do not auto-create parents)

---

Acceptance Criteria

• All necessary 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.