Unique index on associations table does not enforce uniqueness for NonSequentialTimeSeries

[daniel-thom]

Problem

The unique index by_c_vn_tst_hash on time_series_associations includes resolution as a column:

CREATE UNIQUE INDEX by_c_vn_tst_hash ON time_series_associations
  (owner_uuid, time_series_type, name, resolution, features)

For NonSequentialTimeSeries, resolution is NULL. In SQLite, NULL != NULL in unique indexes, so two rows with identical (owner_uuid, type, name, features) but resolution = NULL will not trigger a constraint violation. The index silently fails to enforce uniqueness for this type.

The application-level check in TimeSeriesMetadataStore.add() catches duplicates before insertion, so this is not currently causing data corruption — but the database-level safety net is ineffective.

Suggested fix

Use COALESCE in the index expression to replace NULL with a sentinel:

CREATE UNIQUE INDEX by_c_vn_tst_hash ON time_series_associations
  (owner_uuid, time_series_type, name, COALESCE(resolution, ''), features)

This keeps NULL in the column (semantically correct) while making the index treat all NULLs as equal. It also extends naturally to any future type that lacks a resolution.

Location: src/infrasys/utils/metadata_utils.py:245-248 (create_indexes)