Migrate Legacy `SiteNotes` from `WaterLevels` CSV to `Notes` table for Field Form Support

[ksmuczynski]

Description

To support the generation of field forms and ensure continuity of site-specific historical knowledge, the legacy SiteNotes field from the legacy WaterLevels table needs to be systematically mapped and transferred into the new database structure. Currently, these notes exist only in legacy CSV/table formats and are not accessible to the field form generation logic or the new API schemas.

Context

This work is required to preserve historical context for field technicians and to ensure that legacy "Site Notes" are visible when preparing for field events.

The handling of empty states ensures that downstream clients (like field form generators) can reliably iterate over the site_notes array without checking for null values.

Proposed Changes

1. Lexicon Update: Add Site Notes (legacy) to the note_type category in the lexicon to categorize these migrated records correctly.
2. Data Trasfer Logic:

• Update the WaterLevelTransferer to extract unique SiteNotes per PointID.
• Prepend the date (YYYY-MM-DD: ) to the note content to preserve historical context.
• Implement filtering to exclude missing (NaN), blank, or whitespace-only records.
• Deduplicate notes that have identical content and occur on the same day for the same PointID.
• Map valid notes to the Notes table with:
  • target_table: 'thing'
  • target_id: Linked thing_id
  • note_type: 'Site Notes (legacy)'

3. Database Model Extension: Add a site_notes property to the Thing model in db/thing.py to facilitate easy retrieval of these categorized notes.
4. API Schema Update: Include site_notes in the BaseThingResponse (and inherited WellResponse) in schemas/thing.py so they are exposed via the /things endpoints.

Acceptance Criteria

• Site Notes (legacy) exists in core/lexicon.json.
• WaterLevels transfer process successfully creates Notes records for non-blank legacy notes.
• Data Integrity (Database):
  • Notes in the database are prefixed with their legacy measurement date.
  • Duplicate notes from different days result in separate historical entries.
  • Duplicate notes from the same day are merged into one.
  • Blank or NULL legacy notes from the source CSV do not result in new records in the Notes table.
• API Representation:
  • Responses for Wells include a site_notes array containing the migrated content.
  • Empty State Handling: When no valid legacy notes exist for a Thing (i.e., source data was missing or blank), the API MUST return a valid empty list [] for the site_notes field, rather than null or omitting the field entirely.

Notes

The legacy SiteNotes field could benefit from additional note categorization (e.g there are SiteNotes records whose context would be better categorized as note_type = Sampling Procedure or note_type = Access). This will be treated as a separate issue to be addressed at a later time.

[github-actions]

Linked Jira ticket created: BDMS-609