backend: legacy per-metric thresholds 500 on every read once a row exists (DECIMAL value vs f64 entity)

[ktursunov]

Problem

The legacy per-metric thresholds API (/v1/metrics/{id}/thresholds*) is unusable: the moment the first threshold row is created, every read of the table returns a canonical 500 — the create's own read-back, list, update, and delete. The INSERT itself succeeds, so one successful-looking create permanently poisons the endpoint for that tenant. This is an analytics-api entity/schema mismatch, not a UI or data-pipeline issue.

Context

thresholds is the legacy per-metric threshold store (predates the metric_threshold admin table). Its value column is DECIMAL(20,6), but the SeaORM entity maps it as a bare f64 — and sqlx-mysql cannot decode MySQL NEWDECIMAL into f64. Writes coerce fine server-side; reads fail decoding.

Steps to reproduce

Fastest isolated check (any deployed instance or the e2e rig):

# 1. create a metric (201) — $API is the analytics base URL, $T a tenant id
MID=$(curl -s -X POST "$API/v1/metrics" -H "X-Insight-Tenant-Id: $T" -H 'Content-Type: application/json' \
  -d '{"name":"thr-repro","description":"x","query_ref":"SELECT 1 AS one FROM system.one"}' | jq -r .id)
# 2. create a threshold → HTTP 500 (the INSERT lands; the read-back fails)
curl -si -X POST "$API/v1/metrics/$MID/thresholds" -H "X-Insight-Tenant-Id: $T" -H 'Content-Type: application/json' \
  -d '{"field_name":"one","operator":"ge","value":1.0,"level":"good"}' | head -1
# 3. every subsequent read of the endpoint also 500s
curl -si "$API/v1/metrics/$MID/thresholds" -H "X-Insight-Tenant-Id: $T" | head -1

Expected: 201 with the created threshold; 200 with {items:[…]}.
Actual: 500 application/problem+json (cf.core.err.internal.v1~) on both.

Evidence

• Server log (the decode failure, from a live reproduction):

  analytics::api::handlers: failed to list thresholds error=Query Error: error occurred
  while decoding column "value": mismatched types; Rust type `core::option::Option<f64>`
  (as SQL type `DOUBLE`) is not compatible with SQL type `DECIMAL`
  

• The row IS inserted despite the 500 (SELECT HEX(id), field_name, operator, value FROM thresholds returns the created row with value = 1.000000), confirming write-path OK / read-path broken.

• Found by the e2e endpoint contract suite in e2e: API endpoint contract suite + status-aware blocking coverage gate #1661 (api/test_metric_thresholds.py, currently xfail'd against this issue).

Root cause

• Column: m20260414_000001_init.rs#L86 — Thresholds::Value is decimal_len(20, 6).
• Entity: entities.rs#L38 — #[sea_orm(column_type = "Decimal(Some((20, 6)))")] pub value: f64.
• Every entity read decodes value and hits the sqlx type mismatch; the handlers wrap it as internal: handlers.rs#L1054 (create read-back), handlers.rs#L992 (list).

The admin table documents the same pitfall and avoids it with raw-SQL casts (repository.rs#L4: "All SELECTs use raw SQL with CAST(... AS DOUBLE) for the DECIMAL"). Note SeaORM's select_as attribute is NOT a fix on MySQL/MariaDB — the cast expression is not re-aliased, so decoding then fails with no column found for name: value (verified). The API is f64 end-to-end (CreateThresholdRequest.value in, Threshold.value out), so the DECIMAL column adds only a silent 6-dp quantization between two f64 endpoints.

Other notes

metric_threshold (admin) is unaffected — different read path. A DECIMAL→DOUBLE migration was validated end-to-end on the #1661 branch before being scoped out of that PR (test-only); the e2e lifecycle passed with it.