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