WeatherData should link to Thing via thing_id FK

[kbighorse]

Summary

Add thing_id FK to WeatherData model following the same pattern used for ChemistrySampleInfo to prevent orphan records.

Current State

WeatherData (db/nma_legacy.py) is a flat table with no FK relationships:

class WeatherData(Base):
    __tablename__ = "NMA_WeatherData"

    location_id: Mapped[Optional[uuid.UUID]] = mapped_column("LocationId", UUID(as_uuid=True))
    point_id: Mapped[str] = mapped_column("PointID", String(10))
    weather_id: Mapped[Optional[uuid.UUID]] = mapped_column("WeatherID", UUID(as_uuid=True))
    object_id: Mapped[int] = mapped_column("OBJECTID", Integer, primary_key=True)

The backfill (transfers/backfill/weather_data.py) loads data without linking to Thing, allowing orphan records.

Required Changes

1. Model (db/nma_legacy.py)

• Change PK from object_id to new id column (autoincrement)
• Add thing_id FK (NOT NULL) to Thing
• Add @validates decorator for orphan prevention
• Add ORM relationship to Thing

class WeatherData(Base):
    __tablename__ = "NMA_WeatherData"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    thing_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("thing.id", ondelete="CASCADE"), nullable=False
    )
    # ... existing columns (object_id becomes regular column) ...
    
    thing: Mapped["Thing"] = relationship("Thing", back_populates="weather_data")
    
    @validates("thing_id")
    def validate_thing_id(self, key, value):
        if value is None:
            raise ValueError("WeatherData requires a parent Thing")
        return value

2. Thing model (db/thing.py)

Add reverse relationship:

weather_data: Mapped[List["WeatherData"]] = relationship(
    "WeatherData",
    back_populates="thing",
    cascade="all, delete-orphan",
    passive_deletes=True,
)

3. Migration

• Add id column as new PK
• Change object_id to regular column
• Add thing_id column (NOT NULL)
• Add FK constraint with CASCADE delete

4. Backfill (transfers/backfill/weather_data.py)

• Build Thing ID cache (like ChemistrySampleInfo)
• Filter to valid Things using PointID → Thing.name
• Set thing_id in row dict

5. Tests

Write failing tests first (TDD approach), then implement:

• Test Thing → WeatherData relationship
• Test cascade delete
• Test orphan prevention (ValueError)

Reference

See ChemistrySampleInfo implementation for pattern:

• Model: db/nma_legacy.py
• Backfill: transfers/backfill/chemistry_sampleinfo.py
• Tests: tests/test_nma_chemistry_lineage.py

[kbighorse]

Related PRs & Current Status

Related PRs

• PR BDMS-471,472,473 #400 (merged Jan 2026) - Added NMA_WeatherData model to db/nma_legacy.py
• PR NO-TICKET: Consolidate backfill into transfers #364 (merged Jan 2026) - Integrated WeatherData transfer into main pipeline
• PR [BDMS-497] Create Weather Data Admin page #426 (merged Jan 2026) - Created Weather Data Admin page

Current Implementation

The NMA_WeatherData model exists but is explicitly marked as OUT OF SCOPE for FK refactoring:

# db/nma_legacy.py lines 568-584
class NMA_WeatherData(Base):
    """
    Legacy WeatherData table from AMPAPI.

    Note: This table is OUT OF SCOPE for refactoring (not a Thing child).
    """
    __tablename__ = "NMA_WeatherData"

    location_id: Mapped[Optional[uuid.UUID]] = mapped_column("LocationId", UUID(as_uuid=True))
    point_id: Mapped[str] = mapped_column("PointID", String(10))
    weather_id: Mapped[Optional[uuid.UUID]] = mapped_column("WeatherID", UUID(as_uuid=True))
    object_id: Mapped[int] = mapped_column("OBJECTID", Integer, primary_key=True)

Decision Needed

The "OUT OF SCOPE" comment suggests an architectural decision was made to not link WeatherData to Thing. This issue should be:

1. Closed as won't-fix if WeatherData is intentionally Location-based (not Thing-based)
2. Kept open if the FK refactoring is still planned

@jirhiker @chasetmartin - Can you clarify the intended relationship for WeatherData?

[jirhiker]

@kbighorse Linking to things was not considered part of the 1:1 migration and was intended to be accomplished during the refactor process