b86c0b2d13
mttr generated column is now integer minutes (source raw.mttr is decimal hours), analytics-friendly. Drop is_alarm/is_auto_created/is_auto_closed generated columns — all constant `false` in tickets.inc since alarms are filtered at ingest (still present in raw for audit; loader still filters on raw->>'is_alarm'). is_actionable kept. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
25 lines
1.7 KiB
SQL
25 lines
1.7 KiB
SQL
-- 06_inc_mttr_minutes.sql — fleettickets · mttr -> minutes; drop constant flags
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
-- 1. Redefine the mttr generated column as integer MINUTES (source is decimal
|
|
-- hours) — numeric is the analytics-friendly type for avg/percentile/buckets.
|
|
-- 2. Drop the is_alarm / is_auto_created / is_auto_closed generated columns: we
|
|
-- filter is_alarm=true at ingest, so all three are constant `false` in
|
|
-- tickets.inc (zero information). They remain in `raw` (audit + the loader's
|
|
-- pre-insert filter still reads raw->>'is_alarm'); is_actionable is kept.
|
|
-- raw is untouched; only derived columns change. Idempotent.
|
|
-- ─────────────────────────────────────────────────────────────────────────────
|
|
|
|
SET search_path = tickets, public;
|
|
|
|
-- mttr: hours -> integer minutes
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS mttr;
|
|
ALTER TABLE tickets.inc
|
|
ADD COLUMN IF NOT EXISTS mttr numeric GENERATED ALWAYS AS
|
|
(round(NULLIF(raw->>'mttr','')::numeric * 60)) STORED;
|
|
|
|
COMMENT ON COLUMN tickets.inc.mttr IS 'Mean time to resolve, in MINUTES (source raw.mttr is decimal hours).';
|
|
|
|
-- drop the constant boolean flags (still available in raw)
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS is_alarm;
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS is_auto_created;
|
|
ALTER TABLE tickets.inc DROP COLUMN IF EXISTS is_auto_closed;
|