Netpulse_SasS/server/migrations/0070_sla.sql

-- =====================================================================
-- NetPulse :: 0070_sla.sql
-- Звіти SLA: доступність за період, порахована РАЗ і збережена як факт.
--
-- ЩО ВЖЕ БУЛО
--
-- 0008 завела core.sla_targets і core.sla_periods. За півтора року в них
-- не з'явилось жодного рядка, бо коду, який їх пише, немає. Тобто це не
-- «доробити наявне», а «вирішити, як воно взагалі рахується», — і
-- рішення тут дорожчі за код.
--
-- ГОЛОВНА ПАСТКА, І ЧОМУ ВОНА НЕ ВИДНА
--
-- Наївний звіт про доступність рахують по сирих вимірах: узяли
-- ts.icmp_samples за квартал, поділили відповіді на спроби, показали
-- 99.94%. Число виглядає правильним і сьогодні воно правильне.
--
-- Через два місяці той самий запит на той самий квартал дасть інше
-- число. Причина — 0005: ts.icmp_samples живе 35 діб, і 0064 зробила цей
-- строк ще й РЕДАГОВАНИМ з веб-форми. Тобто дані під звітом зникають
-- хвостом уперед, а запит цього не помічає: він не бачить різниці між
-- «за 1 квітня втрат не було» й «за 1 квітня рядків уже немає». Звіт
-- мовчки їде вгору до 100%.
--
-- Наслідок називається просто: документ, який показали клієнту або
-- аудитору, через два місяці не відтворюється. Це найгірша можлива
-- властивість звіту — гірша за відверто неправильне число, бо
-- неправильне помітно одразу.
--
-- ЗВІДСИ ДВА РІШЕННЯ, І ВОНИ Й Є ЦЯ МІГРАЦІЯ
--
-- Перше: рахувати по годинних згортках ts.icmp_1h, а не по сирих даних.
-- З усіх рівнів деталізації рівно в цього немає політики видалення
-- (0005: «1h-роллапи не видаляємо: це база для SLA-звітів»), і рівно
-- йому retention_policy.go ставить нижню межу 30 діб із поясненням
-- «місячні звіти читають саме звідси». Реальні горизонти такі:
--
--     ts.icmp_samples   35 діб    квартал не покриває взагалі
--     ts.icmp_5m       400 діб    квартал покриває, але це політика,
--                                 яку 0064 дала міняти з форми
--     ts.icmp_1h       без строку  єдине, що продукт уже пообіцяв тримати
--
-- Друге, важливіше: ЗАКРИТИЙ період не перераховується ніколи. Щойно
-- період скінчився й згортки під ним устоялись, рядок у core.sla_periods
-- пишеться один раз і стає фактом. Далі його читають, а не рахують.
-- Тому навіть якщо завтра прибрати ts.icmp_1h цілком, звіт за минулий
-- квартал лишиться тим самим числом — у ньому вже не дані, а висновок.
--
-- ЧОМУ НЕ ts.device_status_history, ХОЧА ВОНА Й ЗВЕТЬСЯ «ІСТОРІЄЮ СТАНІВ»
--
-- Спокуса очевидна: там лежать переходи «вгору/вниз» із точністю до
-- секунди, а не відра по годині. Три причини проти, і третя вирішальна.
--
--   1. Це журнал ЗМІН, а не станів. Щоб знати стан хоста о 00:00 1 квітня,
--      треба знайти останній рядок ПЕРЕД періодом — а він може бути
--      як завгодно старим. 0064 завела цій таблиці редагований строк
--      зберігання, тобто саме той рядок і зникне першим.
--   2. Її пише applyDeviceStatus, тобто вона показує стан, який вирішив
--      конвеєр алертів. У ньому вже враховані придушення й антифлап —
--      дві політики, змішані в одному числі, яке потім показують
--      аудитору як вимір.
--   3. І головне: вона не вміє сказати «ми не знали». Перехід пишеться
--      ЛИШЕ при зміні стану. Коли зонд відвалився, вимірів не надходить,
--      переходу немає — і таблиця стверджує, що хост був «up» усю добу
--      мовчання. Тобто джерело, яке за побудовою рахує «даних немає» як
--      «працювало», — саме та помилка, від якої цей файл захищає.
--
-- ts.icmp_1h цього не робить: у ній є samples і down_samples. Година без
-- рядка — це година без даних, і сплутати її з робочою нічим.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. Цілі SLA
-- ---------------------------------------------------------------------

-- Часовий пояс цілі, і це не косметика.
--
-- «Доступність за квартал» — це календарний квартал у поясі організації,
-- а не 92 доби від UTC-опівночі. Різниця для Києва — від двох до трьох
-- годин на кожній межі періоду, і саме в них найчастіше й ставлять
-- планові роботи. Пояс лежить на цілі, а не береться з core.tenants при
-- кожному розрахунку: тенант може переїхати між поясами, і тоді старий
-- звіт мовчки почав би описувати інші 92 доби.
ALTER TABLE core.sla_targets
  ADD COLUMN IF NOT EXISTS tz text NOT NULL DEFAULT 'UTC';

-- Ціль можна вимкнути, не видаляючи. Видалення забирає за собою всі
-- закриті періоди (ON DELETE CASCADE на sla_periods.sla_target_id), тобто
-- «більше не рахуємо цю ціль» і «зітерти торішні звіти» — це різні
-- наміри, і в них мають бути різні кнопки.
ALTER TABLE core.sla_targets
  ADD COLUMN IF NOT EXISTS enabled boolean NOT NULL DEFAULT true;

-- Скільки періоду треба ЗНАТИ, щоб узагалі виносити вердикт.
--
-- Це найважливіше поле в таблиці. Без нього період, у якому зонд
-- пролежав три тижні, дав би «100% доступності» — бо серед тих вимірів,
-- що дійшли, справді не було жодної втрати. Формально правда, по суті
-- брехня.
--
-- Тому вердикт («виконано» / «порушено») виноситься лише коли покриття
-- не нижче за цей поріг. Нижче — період позначається як «недостатньо
-- даних» і не зараховується НІ в який бік. Мовчазне «зелено» тут
-- заборонено за побудовою.
ALTER TABLE core.sla_targets
  ADD COLUMN IF NOT EXISTS min_coverage_pct numeric(5,2) NOT NULL DEFAULT 95.00
    CHECK (min_coverage_pct >= 0 AND min_coverage_pct <= 100);

-- Тип періоду обмежується явно. Досі це був вільний text із коментарем
-- «daily | weekly | monthly | quarterly» — тобто домовленість, яку не
-- перевіряє ніхто, а розрахунок за незнайомим значенням мовчки дав би
-- порожній звіт.
ALTER TABLE core.sla_targets
  DROP CONSTRAINT IF EXISTS sla_targets_period_kind_chk;
ALTER TABLE core.sla_targets
  ADD CONSTRAINT sla_targets_period_kind_chk
  CHECK (period_kind IN ('daily','weekly','monthly','quarterly'));

COMMENT ON COLUMN core.sla_targets.tz IS
  'Пояс, у якому ріжуться календарні межі періоду';
COMMENT ON COLUMN core.sla_targets.min_coverage_pct IS
  'Нижче цього покриття вердикт не виноситься: період позначається як «недостатньо даних»';
COMMENT ON COLUMN core.sla_targets.business_hours IS
  'НЕ РЕАЛІЗОВАНО (0070). Заповнене поле не звужує розрахунок, а лише додає попередження в період';

-- Пояс за замовчуванням береться з тенанта — один раз, при накаті.
-- Далі вони живуть окремо; див. міркування про переїзд вище.
UPDATE core.sla_targets t
SET tz = COALESCE(NULLIF(n.timezone, ''), 'UTC')
FROM core.tenants n
WHERE n.id = t.tenant_id AND t.tz = 'UTC';

-- ---------------------------------------------------------------------
-- 2. Закриті періоди
-- ---------------------------------------------------------------------

-- Найважливіша зміна файлу: рядок періоду перестає залежати від того,
-- чи живий ще хост.
--
-- Було: sla_periods.device_id REFERENCES inv.devices(id) ON DELETE CASCADE.
-- Тобто повне видалення хоста (0057) заднім числом стирало його звіти —
-- і докладний коментар у devices_purge.go чесно перелічує «періоди SLA»
-- серед того, що зникає каскадом.
--
-- Для будь-якої іншої таблиці це правильно. Для цієї — ні, і причина та
-- сама, з якої переживає видалення журнал аудиту: звіт, показаний
-- клієнту, не може перестати існувати тому, що хтось прибрав хост із
-- переліку. Квартал не «розраховується заново без цього хоста» — він уже
-- відбувся.
--
-- Тому ключ знімається, а id лишається звичайною колонкою. Наслідок,
-- який треба знати: після повного видалення хоста в періодах лишається
-- uuid, за яким уже нікого немає. Саме заради цього поруч з'являється
-- знімок імені — інакше звіт показував би стовпчик із голими uuid.
ALTER TABLE core.sla_periods
  DROP CONSTRAINT IF EXISTS sla_periods_device_id_fkey;

ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS device_name text NOT NULL DEFAULT '';

-- Час, розкладений на чотири взаємно виключні частини. Разом вони дають
-- clock_sec, і саме тому їх чотири, а не два:
--
--   maintenance_sec  вікно обслуговування — годинник зупинено
--   up_sec           виміряно, хост відповідав
--   downtime_sec     виміряно, хост не відповідав
--   unknown_sec      не виміряно нічим: зонд мовчав, хост був вимкнений,
--                    даних просто немає
--
-- Остання й є вся суть. Тримати її окремою колонкою означає, що
-- «система не знала» фізично неможливо сплутати ані з «працювало», ані
-- з «лежало»: щоб збрехати, довелось би свідомо додати unknown_sec до
-- up_sec, а це видно в коді, а не ховається в SQL.
ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS up_sec      bigint NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS unknown_sec bigint NOT NULL DEFAULT 0,
  -- Повна тривалість періоду В МЕЖАХ ЖИТТЯ ХОСТА. Хост, заведений
  -- 20 травня, не має «недоступності» за 1–19 травня: його не було.
  -- Без цієї колонки різницю між «нема даних» і «нема хоста» довелось би
  -- відновлювати з inv.devices, якого після видалення теж уже немає.
  ADD COLUMN IF NOT EXISTS clock_sec   bigint NOT NULL DEFAULT 0,
  -- Частка періоду, про яку взагалі є вимір. Число, за яким читач
  -- вирішує, чи вірити uptime_pct.
  ADD COLUMN IF NOT EXISTS coverage_pct numeric(6,3) NOT NULL DEFAULT 0;

-- Знімки того, ПРОТИ ЧОГО міряли. Ціль живе далі й може змінитись —
-- 99.5% підняли до 99.9%, поріг покриття зсунули. Закритий звіт мусить
-- пам'ятати умови, що діяли тоді, інакше торішній «виконано» одного дня
-- стане «порушено» без жодної події в мережі.
ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS target_pct       numeric(5,3),
  ADD COLUMN IF NOT EXISTS min_coverage_pct numeric(5,2),
  ADD COLUMN IF NOT EXISTS tz               text NOT NULL DEFAULT 'UTC';

-- З якого відношення взято числа. Сьогодні завжди 'icmp_1h'; колонка
-- потрібна на той день, коли з'явиться інше джерело: без неї старі рядки
-- й нові виглядали б однаково, а порівнювати їх було б не можна.
ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'icmp_1h';

-- closed — межа між «прикидкою» й «фактом».
--
-- false: період ще триває або згортки під ним не встоялись; число
--        показують із позначкою «попередній розрахунок» і перераховують
--        скільки завгодно разів.
-- true:  період закрито. Далі його читають. Перерахунок можливий лише
--        через явну дію людини, і кожен такий перерахунок видно —
--        див. revision.
ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS closed boolean NOT NULL DEFAULT false;

-- Скільки разів це число вже переписували.
--
-- Не лічильник заради лічильника. Перерахунок закритого періоду —
-- законна дія (виправили пояс, дозаповнили вікно обслуговування), але
-- вона МАЄ лишати слід у самому звіті. Інакше два роздруки того самого
-- кварталу з різними числами неможливо розрізнити, і правий завжди той,
-- у кого папірець свіжіший.
ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS revision int NOT NULL DEFAULT 1;

-- Чого цей розрахунок НЕ врахував.
--
-- Перелік коротких ключів: 'rrule_ignored', 'business_hours_ignored',
-- 'device_purged'. Живе в самому рядку, а не в логах, бо читати його
-- має той, хто дивиться на звіт через рік, — а логів за той день уже
-- немає. Мовчазна відмова врахувати щось є брехнею; названа вголос —
-- ні.
ALTER TABLE core.sla_periods
  ADD COLUMN IF NOT EXISTS warnings jsonb NOT NULL DEFAULT '[]'::jsonb;

COMMENT ON COLUMN core.sla_periods.unknown_sec IS
  'Час, про який немає жодного виміру. Ніколи не додається ні до up_sec, ні до downtime_sec';
COMMENT ON COLUMN core.sla_periods.clock_sec IS
  'Тривалість періоду в межах життя хоста: створений чи видалений посеред періоду рахується частково';
COMMENT ON COLUMN core.sla_periods.closed IS
  'true — факт, не перераховується; false — попередній розрахунок';
COMMENT ON COLUMN core.sla_periods.revision IS
  'Скільки разів закритий період перераховували руками';

-- Перелік періодів однієї цілі — головний запит сторінки.
CREATE INDEX IF NOT EXISTS sla_periods_target_idx
  ON core.sla_periods (sla_target_id, period);

-- ---------------------------------------------------------------------
-- 3. Незмінність закритого періоду — на рівні бази
-- ---------------------------------------------------------------------

-- Правило «закрите не переписують» тримається тригером, а не домовленістю
-- в Go.
--
-- ПРИЧИНА: обіцянка «звіт за минулий квартал не змінюється» коштує рівно
-- стільки, скільки коштує найслабший шлях запису. Шляхів уже два (REST
-- і фоновий такт), третій з'явиться разом із наступною задачею, і саме
-- він забуде перевірку. Перевірка ж, що стоїть на таблиці, не має
-- обхідного шляху взагалі.
--
-- НАСЛІДОК: перерахувати закритий період можна лише свідомо — виставивши
-- app.sla_reopen у 'on' у СВОЇЙ транзакції. Це не захист від адміністратора
-- бази (він зніме тригер), це захист від власного коду, написаного через
-- півроку іншою людиною.
CREATE OR REPLACE FUNCTION core.sla_period_guard() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.closed
     AND COALESCE(current_setting('app.sla_reopen', true), '') <> 'on' THEN
    RAISE EXCEPTION 'закритий період SLA не переписують'
      USING ERRCODE = 'restrict_violation',
            -- Підказка тут не з ввічливості: без неї перше зіткнення з
            -- цим тригером виглядає як поломка бази, а не як
            -- спрацювання правила.
            HINT = 'для свідомого перерахунку: SET LOCAL app.sla_reopen = ''on''';
  END IF;

  -- NEW у тригері DELETE не існує, тож повертати його не можна: інакше
  -- перше ж видалення НЕЗАКРИТОГО періоду впало б на порожньому записі.
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;
  RETURN NEW;
END $$;

COMMENT ON FUNCTION core.sla_period_guard() IS
  'Забороняє правку й видалення закритих періодів SLA без явного app.sla_reopen';

DROP TRIGGER IF EXISTS trg_sla_period_guard ON core.sla_periods;
CREATE TRIGGER trg_sla_period_guard
  BEFORE UPDATE OR DELETE ON core.sla_periods
  FOR EACH ROW EXECUTE FUNCTION core.sla_period_guard();

-- ПОБІЧНИЙ НАСЛІДОК, ЯКИЙ ТРЕБА ЗНАТИ НАПЕРЕД
--
-- Тригер стоїть і на DELETE, тобто його бачать КАСКАДИ. Два місця, де це
-- проявиться:
--
--   * видалення цілі SLA — оброблено в DeleteSLATarget: воно виставляє
--     app.sla_reopen у своїй транзакції, бо «видалити ціль» справді
--     означає «разом із її звітами»;
--   * жорстке видалення кабінету (DELETE FROM core.tenants) — упаде.
--     У продукті такого шляху немає, кабінет прибирається м'яко
--     (deleted_at), але тести й ручне прибирання бази роблять саме це.
--     Ліки — той самий SET app.sla_reopen = 'on' перед видаленням.
--
-- Це навмисно не пом'якшено «дозволити каскади»: каскад, який мовчки
-- зносить закриті звіти, — рівно те, від чого написаний цей тригер.

-- ---------------------------------------------------------------------
-- 4. Вікна обслуговування
-- ---------------------------------------------------------------------

-- Функції в базі під це НЕ заводиться, і про причину варто сказати тут,
-- бо перше бажання — саме її й написати.
--
-- Вікна треба (а) відібрати за селектором і (б) ОБ'ЄДНАТИ, бо вони
-- перетинаються: 02:00–04:00 плюс 03:00–05:00 — це три години зупиненого
-- годинника, а не чотири. Проста сума дала б завищення, до того ж
-- непомітне: доступність просто виходила б трохи кращою, ніж є.
--
-- Обидві половини вже мають місце в коді. Селектор розбирає
-- selectorSQL — той самий, яким придушуються алерти; розійтись їм не
-- можна, бо інакше алерт придушено, а SLA зіпсовано. А об'єднання
-- відрізків — це та сама арифметика інтервалів, якою й так ріжеться
-- життя хоста в періоді, і в Go вона перевіряється тестом без бази,
-- тоді як у SQL — лише на живому Postgres.
--
-- Тому тут лишається одне: індекс під вибірку вікон уже є
-- (mw_active_idx, gist(tenant_id, period) з 0007), і додавати нема чого.

-- ---------------------------------------------------------------------
-- 5. Права
-- ---------------------------------------------------------------------

-- Нових ключів прав не заводимо, і це рішення, а не лінощі.
--
-- Дивитись звіт — devices:read: доступність своєї мережі бачить кожен,
-- хто взагалі бачить моніторинг. Ховати її немає від кого, а от не
-- побачити наближення до порога вчасно коштує грошей за договором.
--
-- Заводити цілі й закривати періоди — settings:write. Ціль SLA — це
-- зобов'язання організації перед клієнтом, того самого класу, що й
-- строки зберігання: вона не про один хост, а про те, під чим
-- підписались. А закриття періоду ще й незворотне за наслідками.
--
-- Окремі ключі sla:read/sla:write виглядали б охайніше, але кожен новий
-- ключ треба роздати ролям, показати на екрані прав і пояснити — тобто
-- заплатити людині за розрізнення, якого вона не просила. Завести їх
-- пізніше можна; забрати роздане право назад — уже ні.
GRANT SELECT, INSERT, UPDATE, DELETE ON core.sla_targets, core.sla_periods
  TO netpulse_app, netpulse_worker;

-- RLS тут окремо не вмикається: обидві таблиці мають tenant_id і були
-- захоплені циклом 0011 ще при створенні. Перевірено переліком політик,
-- а не припущенням, — саме на такому припущенні 0063 і знайшла шість
-- зв'язкових таблиць без жодної політики.