Синк и метрика

Синк календаря и расчёт метрики

Ядро проекта: ICS → bookings → сравнение с фактом от датчика.

Дорожная карта: пошаговая сборка руками

Всё, что дальше в этом документе (готовый JSON flow, JSON панели), — это справочная реализация, а не то, что нужно импортировать в первую очередь. По плану наставника эта фаза — та, где ученик работает самостоятельно: парсер, upsert, SQL метрики и дашборд собираются руками, шаг за шагом, с проверкой на каждом шаге. Готовый JSON — это ответ в конце учебника: смотреть, если застряли больше чем на 40 минут, не раньше.

Каждый шаг ниже — это «собери один узел / одну функцию → разверни → проверь конкретную вещь → иди дальше». Не переходите к следующему шагу, если проверка не прошла: следующий шаг опирается на то, что предыдущий работает правильно.

Шаг 0 · Подготовка окружения (5 минут)

В Node-RED: Menu → Manage palette → Install — проверить, что node-red-contrib-postgresql уже стоит (он нужен для существующего flow датчика, должен быть).
Больше ничего ставить не нужно. Получение ICS (http request / file in / exec) и разворачивание RRULE — это разбор текста и работа с датами, штатные возможности JavaScript в function-ноде. Внешний пакет (node-ical) для этого не нужен — он бы означал правку settings.js и перезапуск Node-RED ради того, что можно сделать десятком строк без единой зависимости. Ниже (Шаг 4) — рабочий код, ничего доустанавливать не придётся.
В .env/nodered.env — завести переменные из «Источника ICS»: ICS_SOURCE_TYPE, ICS_URL или ICS_FILE, ICS_WINDOW_DAYS, ROOM_ID.

Проверка шага: новый function-узел с телом msg.payload = typeof Intl.DateTimeFormat; return msg;, подключенный к debug-ноде, после деплоя и инжекта должен показать "function" — это встроенный в JS Intl, на нём и строится вся работа с таймзонами дальше, никакой установки не требует.

Шаг 1 · Пустой скелет: Inject → Debug

Прежде чем писать логику — собрать самый простой рабочий путь: inject → debug. Задеплоить, нажать на inject, увидеть сообщение в сайдбаре debug.

Зачем этот шаг, который «ничего не делает»: он отделяет проблемы механики Node-RED (задеплоить, увидеть debug-панель) от проблем логики, которые появятся дальше. Если что-то не так — понятно, что дело не в парсере.

Шаг 2 · Определить источник

Между inject и debug — вставить function-ноду: прочитать env.get('ICS_SOURCE_TYPE'), env.get('ICS_URL'), env.get('ICS_FILE'), положить в msg.sourceType / msg.url / msg.filename.

Проверка: debug показывает те значения, что реально стоят в .env — если поменять ICS_SOURCE_TYPE в .env и перезапустить Node-RED, debug должен это отразить.

Шаг 3 · Получить сырой текст ICS — по одной ветке

Добавить switch по msg.sourceType с тремя выходами (url / file / generated). Собирать и проверять по одной ветке, не все три сразу:

generated — проще всего начать отсюда. exec-нода: команда python3 /путь/gen_ics.py. Дальше file in, читающая записанный meetingroom.ics. Debug после file in должен показать текст, начинающийся с BEGIN:VCALENDAR.
file — file in, filename берётся из msg.filename (свойство Filename = msg.filename в конфиге ноды). Тот же результат в debug.
url — http request (GET, Return: a UTF-8 string), url — динамически из msg.url. Тот же результат.

Проверка: во всех трёх ветках msg.payload — одна и та же по формату строка ICS, независимо от источника. Это и есть та самая «обработка не знает, откуда взялся ICS» из «Источника ICS» — проверьте её здесь руками, а не поверьте на слово.

Шаг 4 · Развернуть RRULE в экземпляры

Это самое важное место всего flow — не торопиться. Своя реализация, без внешних пакетов: разбор ICS — это просто текст (см. Часть 1), а разворачивание RRULE для нужного проекту подмножества (FREQ=WEEKLY/DAILY, BYDAY, COUNT/UNTIL, EXDATE) — цикл по дням с проверкой дня недели. Полный RFC 5545 разворачивать не нужно — то же самоограничение, что и везде в проекте (см. план наставника: «не обработка всех крайних случаев RFC 5545»).

Что должна делать function-нода:

разобрать текст ICS на блоки VEVENT (строки KEY;PARAM=..:значение, TZID — это параметр у DTSTART/DTEND/EXDATE);
привести время каждого поля к UTC. Без стороннего пакета это делается через встроенный Intl.DateTimeFormat с timeZone — узнать реальное смещение зоны для конкретной даты (учитывает и DST, если он вообще где-то есть) и вычесть его. Достаточно 1–2 итераций «предположили → уточнили», это классический приём, а не хак;
для VEVENT без RRULE — взять start/end как есть, если попадают в окно [сейчас, сейчас + ICS_WINDOW_DAYS];
для VEVENT с RRULE — идти день за днём от DTSTART до конца окна (или UNTIL, если он раньше), на каждом шаге проверять день недели против BYDAY, считать совпадения до COUNT; выкинуть те, что совпали с EXDATE;
сложить результат в массив {uid, starts_at, ends_at} и отдельно собрать множество всех uid, встретившихся в фиде (пригодится в шаге 6).

Не подглядывать в готовый код в разделе «Готовый flow для Node-RED» (ниже, в конце Части 3) раньше, чем 40 минут своей попытки — сама структура алгоритма уже описана в Части 1 выше и в пунктах 1–5.

Проверка (обязательна, не пропускать):

на генераторе (gen_ics.py) итоговый массив содержит на 4 экземпляра больше, чем событий в исходном ICS — потому что weekly-standup (RRULE:...COUNT=12) разворачивается в несколько встреч внутри ICS_WINDOW_DAYS=30, а не остаётся одной записью;
если временно поставить ICS_WINDOW_DAYS=7 — экземпляров weekly-standup должно стать меньше (окно короче, попадает не 4, а 1);
ни одна пара (uid, starts_at) не повторяется — проверить new Set(instances.map(i => i.uid + i.starts_at)).size === instances.length прямо в function-ноде и вывести в debug.

Если количество не сходится — почти всегда время: перевод в UTC через Intl.DateTimeFormat действительно применяется к каждой дате перед сравнением или нет? Сверить на встрече с известным временем (см. «Таймзоны — источник тихих ошибок» в Части 1), не гадать.

Шаг 5 · Собрать SQL и параметры (пока без записи в БД)

Function-нода, которая строит msg.query (текст INSERT ... ON CONFLICT ... из Части 3 ниже) и msg.params — массив [room_id, uids[], starts[], ends[]].

Проверка — прежде чем подключать к БД: debug должен показать msg.query как валидный SQL-текст (перечитать глазами: все $1..$4 на месте, кавычки не съехали) и msg.params — массив из ровно 4 элементов, где второй-четвёртый — массивы одинаковой длины.

Шаг 6 · Записать в bookings — и проверить в БД, а не поверить Node-RED

Подключить postgresql-ноду (используя существующий конфиг подключения к TimescaleDB — тот же, что у датчика). query в самой ноде оставить пустым — она возьмёт msg.query/msg.params.

Проверка — через DBeaver/psql, не через debug Node-RED:

SELECT count(*), min(starts_at), max(starts_at) FROM bookings;

Число строк должно совпасть с количеством экземпляров из Шага 4.

Проверка идемпотентности (обязательный отдельный прогон): нажать inject ещё раз, не меняя ничего. count(*) не должен вырасти — если вырос, значит ON CONFLICT не сработал (чаще всего — перепутан состав составного ключа).

Шаг 7 · Пометка отмен

Ещё одна function (SQL из Части 3 ниже, параметры [room_id, ICS_WINDOW_DAYS, freshUids]) + вторая postgresql-нода.

Проверка — руками имитировать отмену: удалить одно VEVENT из gen_ics.py (или дать EXDATE), перегенерировать, прогнать flow ещё раз:

SELECT booking_uid, cancelled FROM bookings WHERE booking_uid = 'evt-N@example.org';

cancelled должен стать true. Вернуть событие обратно, прогнать снова — cancelled должен снова стать false (см. ON CONFLICT ... cancelled = false в апсерте).

Шаг 8 · Метрика — SQL руками в DBeaver

Взять запрос из Части 4 ниже, выполнить на своих данных. Сверить порядок величин со здравым смыслом (часов простоя не может быть больше часов забронировано), затем — обязательно проверить чувствительность к порогу no-show (таблица там же, в Части 4) на своих данных, не только на модельных четырёх бронях из примера.

Шаг 9 · Панель в Grafana — руками через UI, не через импорт JSON

Add panel → New panel → выбрать датасорс → Edit → Code → вставить один из двух SQL-запросов из панели «план против факта» (Часть 5 ниже) как отдельные query A и B → тип панели Time series → в Overrides руками настроить вторую ось и заливку для ряда с бронированием. Готовый JSON-объект панели — сверяться после того, как получилось руками, не вместо.

Шаг 10 · Финальная проверка

Пройти по чек-листу в Части 6 (в конце документа) целиком, не только по пунктам, которые уже случайно совпали.

Часть 1 · Что сложного в календарях

Три вещи, о которые спотыкаются все, кто делает это впервые.

RRULE — одна встреча, много экземпляров

Еженедельный статус описан в ICS одной записью с правилом повторения:

UID:weekly-standup@example.org
DTSTART;TZID=Europe/Moscow:20260706T100000
RRULE:FREQ=WEEKLY;BYDAY=MO;COUNT=12

Это 12 встреч, но один UID. Отсюда составной первичный ключ в схеме:

PRIMARY KEY (booking_uid, starts_at)

С ключом по одному booking_uid каждый еженедельный синк перетирал бы экземпляры друг другом, и в базе осталась бы одна встреча вместо двенадцати. Это решение уже заложено в вашей схеме — здесь просто объясняется, зачем.

Парсер обязан развернуть серию в отдельные экземпляры. Для нужного проекту подмножества RRULE (FREQ=WEEKLY/DAILY, BYDAY, COUNT/UNTIL) это делает цикл по дням со сравнением дня недели — без сторонних пакетов, штатным JS/Python. Библиотеки вроде node-ical или icalendar+dateutil.rrule умеют то же самое и больше (полный RFC 5545), но тянуть их ради этого проекта не обязательно — устанавливать npm-пакет в Node-RED означает лезть в settings.js и перезапускать сервис, а разворачивание нужного подмножества RRULE — это десяток строк без единой зависимости (см. Шаг 4 дорожной карты выше).

EXDATE и отмены — событие может исчезнуть

Из серии можно исключить одну дату (EXDATE) или отменить встречу целиком (STATUS:CANCELLED, либо запись просто пропадает из фида).

Отмены не удаляем — в схеме для этого есть флаг:

cancelled boolean NOT NULL DEFAULT false

Причина в комментарии к вашей же схеме: «отмены сами по себе метрика». Забронировали и отменили за час — это тоже информация об использовании помещения. Удалив строку, вы теряете факт.

Таймзоны — источник тихих ошибок

ICS хранит время с TZID, база — timestamptz. Правило простое: парсер приводит всё к UTC, БД хранит UTC, Grafana показывает в локальной зоне.

В .env основного стека уже стоит TZ=UTC с пометкой «критично для TimescaleDB» — придерживаемся того же.

Ошибка на 3 часа не выглядит как ошибка: график просто «сдвинут», и это можно не заметить месяцами. Проверять на конкретной встрече с известным временем.

Часть 1.5 · Кто именно собирает данные: не Grafana

Частая путаница: Grafana ICS не читает и в базу не пишет. Grafana — это только слой отображения: она умеет посылать SQL-запросы к TimescaleDB и рисовать результат. У неё нет ни планировщика для периодического опроса внешнего URL, ни прав на запись в таблицы.

Сбор и сопоставление данных календаря с тем, что уже лежит в БД, — задача Node-RED (он уже в стеке и уже пишет в TimescaleDB из MQTT, значит для него это естественное расширение, а не новый компонент):

    flowchart LR
    A["ICS: url / file / generated"] -->|HTTP GET или чтение файла| B["Node-RED:<br/>развернуть RRULE,<br/>upsert в bookings"]
    B --> C[("TimescaleDB<br/>bookings")]
    D["Датчик → MQTT"] --> E["Node-RED (уже есть):<br/>occupancy_hourly"]
    E --> F[("TimescaleDB<br/>occupancy_hourly")]
    C --> G["Grafana:<br/>читает обе таблицы,<br/>рисует план vs факт"]
    F --> G

Node-RED здесь выполняет две независимые роли, которые встречаются только в базе: пишет факт (уже работает) и пишет план (новая часть, ниже — готовый flow). Сопоставление этих двух рядов — это не отдельный сервис, а просто SQL-запрос (см. Часть 4), который Grafana выполняет в момент отрисовки панели.

Часть 2 · Окно синхронизации

Ключевое правило, которое легко упустить:

Окно разворачивания RRULE должно совпадать с окном пометки отмен.

Если разворачивать серию на 30 дней вперёд, а отмены искать только на 7 — встреча, отменённая на 20-й день, останется в базе как активная. Одна константа:

ICS_WINDOW_DAYS=30

Алгоритм синка (идемпотентный, выполняется по расписанию):

1. получить ICS (url | file | generated)
2. развернуть события в окне [now, now + WINDOW_DAYS]
3. для каждого экземпляра → UPSERT в bookings
4. брони в окне, которых НЕТ в свежем фиде → cancelled = true
5. записать время успешного синка

Шаг 4 — то самое соответствие окон. Шаг 5 нужен, чтобы отличить «отмен не было» от «синк не отработал».

Часть 3 · Идемпотентный upsert

Синк должен переживать повторный запуск без дублей и без потери данных:

INSERT INTO bookings
    (booking_uid, starts_at, ends_at, room_id, source, cancelled, updated_at)
VALUES ($1, $2, $3, $4, 'ics', false, now())
ON CONFLICT (booking_uid, starts_at) DO UPDATE SET
    ends_at    = EXCLUDED.ends_at,
    cancelled  = false,           -- встреча снова в фиде → отмена снята
    updated_at = now();

Поля organizer и title намеренно не заполняются — см. приватность.

Пометка отмен одним запросом:

UPDATE bookings
   SET cancelled = true, updated_at = now()
 WHERE room_id = $1
   AND starts_at BETWEEN now() AND now() + ($2 || ' days')::interval
   AND booking_uid <> ALL($3);      -- $3 — массив UID из свежего фида

Готовый flow для Node-RED

Реализация Части 2–3 одним flow, в тех же соглашениях, что и рабочий flow телеметрии (sensor_data, MQTT → Postgres): узел записи — postgresql (строчными), SQL и параметры собираются в предыдущей function-ноде как msg.query / msg.params, а сама postgres-нода держит поле query пустым. Конфиг подключения — тот же, что уже используется для датчиков (timescaledb (nodered)), новый заводить не нужно. Внешние npm-пакеты не нужны вовсе: разворачивание RRULE (Шаг 4 дорожной карты выше) — обычный function-узел на встроенном JS, без правки settings.js, без Manage palette, без перезапуска Node-RED.

Три источника (Часть 3 файла «Источник ICS») — три ветки одного switch, дальше общий путь: развернуть RRULE → собрать msg.query + msg.params → upsert → пометить отмены.

[
  {"id":"mr-ics-sync","type":"tab","label":"MeetingRoom · ICS sync","disabled":false,"info":"Синк календаря переговорной в bookings. Без внешних npm-пакетов — RRULE разворачивается в function-ноде на встроенном JS."},
  {"id":"n-inject","type":"inject","z":"mr-ics-sync","name":"каждые ICS_SYNC_INTERVAL","props":[{"p":"payload"}],"repeat":"","crontab":"*/15 * * * *","once":true,"onceDelay":5,"topic":"sync","payload":"","payloadType":"date","x":150,"y":80,"wires":[["n-source"]]},
  {"id":"n-source","type":"function","z":"mr-ics-sync","name":"определить источник (.env)","func":"const type = env.get('ICS_SOURCE_TYPE') || 'generated';\nmsg.sourceType = type;\nmsg.url = env.get('ICS_URL');\nmsg.filename = env.get('ICS_FILE') || '/opt/meetingroom/meetingroom.ics';\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":380,"y":80,"wires":[["n-switch"]]},
  {"id":"n-switch","type":"switch","z":"mr-ics-sync","name":"ICS_SOURCE_TYPE","property":"sourceType","propertyType":"msg","rules":[{"t":"eq","v":"url","vt":"str"},{"t":"eq","v":"file","vt":"str"},{"t":"eq","v":"generated","vt":"str"}],"checkall":"true","outputs":3,"x":620,"y":80,"wires":[["n-http"],["n-file"],["n-exec"]]},
  {"id":"n-http","type":"http request","z":"mr-ics-sync","name":"GET ICS_URL","method":"GET","ret":"txt","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","authType":"","x":860,"y":40,"wires":[["n-expand"]]},
  {"id":"n-file","type":"file in","z":"mr-ics-sync","name":"читать ICS_FILE","filename":"","filenameType":"msg","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":860,"y":80,"wires":[["n-expand"]]},
  {"id":"n-exec","type":"exec","z":"mr-ics-sync","command":"python3 /opt/meetingroom/gen_ics.py","addpay":false,"append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"сгенерировать (gen_ics.py)","x":860,"y":120,"wires":[["n-exec-read"],[],[]]},
  {"id":"n-exec-read","type":"file in","z":"mr-ics-sync","name":"читать meetingroom.ics","filename":"/opt/meetingroom/meetingroom.ics","filenameType":"str","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":1090,"y":120,"wires":[["n-expand"]]},
  {"id":"n-expand","type":"function","z":"mr-ics-sync","name":"развернуть RRULE (свой парсер, без пакетов)","func":"// Своя реализация без внешних npm-пакетов (см. документацию: не нужно лезть\n// в settings.js ради этого). Поддерживает то, что нужно проекту: FREQ=WEEKLY/\n// DAILY, BYDAY, COUNT/UNTIL, EXDATE. Полный RFC 5545 — не цель.\nconst DAY_MAP = { SU: 0, MO: 1, TU: 2, WE: 3, TH: 4, FR: 5, SA: 6 };\nconst TZ_RE = /TZID=([^:;]+)/;\n\nfunction tzOffsetMinutes(utcGuess, timeZone) {\n    const dtf = new Intl.DateTimeFormat('en-US', {\n        timeZone, hourCycle: 'h23',\n        year: 'numeric', month: '2-digit', day: '2-digit',\n        hour: '2-digit', minute: '2-digit', second: '2-digit'\n    });\n    const p = dtf.formatToParts(utcGuess).reduce((a, x) => (a[x.type] = x.value, a), {});\n    const asUtc = Date.UTC(+p.year, +p.month - 1, +p.day, +p.hour, +p.minute, +p.second);\n    return (asUtc - utcGuess.getTime()) / 60000;\n}\n\n// \"20260706T100000\" + TZID=Europe/Moscow -> Date в UTC. Без стороннего пакета,\n// только встроенный Intl: предположили — уточнили, 1-2 итерации достаточно.\nfunction parseICSDate(raw, tzid) {\n    const m = raw.match(/^(\\d{4})(\\d{2})(\\d{2})T(\\d{2})(\\d{2})(\\d{2})Z?$/);\n    const [, y, mo, d, h, mi, s] = m.map(Number);\n    if (raw.endsWith('Z') || !tzid) return new Date(Date.UTC(y, mo - 1, d, h, mi, s));\n    let guess = new Date(Date.UTC(y, mo - 1, d, h, mi, s));\n    for (let i = 0; i < 2; i++) {\n        const offset = tzOffsetMinutes(guess, tzid);\n        guess = new Date(Date.UTC(y, mo - 1, d, h, mi, s) - offset * 60000);\n    }\n    return guess;\n}\n\nfunction parseICS(text) {\n    const unfolded = text.replace(/\\r?\\n[ \\t]/g, '');\n    const blocks = unfolded.split('BEGIN:VEVENT').slice(1);\n    return blocks.map(block => {\n        const body = block.split('END:VEVENT')[0];\n        const ev = { exdates: [] };\n        for (const line of body.split(/\\r?\\n/).filter(Boolean)) {\n            const sep = line.indexOf(':');\n            const left = line.slice(0, sep);\n            const value = line.slice(sep + 1);\n            const [key] = left.split(';');\n            const tzMatch = left.match(TZ_RE);\n            const tzid = tzMatch ? tzMatch[1] : null;\n            if (key === 'UID') ev.uid = value;\n            else if (key === 'DTSTART') ev.start = parseICSDate(value, tzid);\n            else if (key === 'DTEND') ev.end = parseICSDate(value, tzid);\n            else if (key === 'RRULE') ev.rrule = Object.fromEntries(value.split(';').map(p => p.split('=')));\n            else if (key === 'EXDATE') ev.exdates.push(parseICSDate(value, tzid).getTime());\n            else if (key === 'STATUS' && value === 'CANCELLED') ev.cancelled = true;\n        }\n        return ev;\n    });\n}\n\n// Разворот RRULE день за днём — простой и корректный способ для WEEKLY/DAILY,\n// быстрее не нужно: окно синка — 30 дней, не годы.\nfunction expandRRULE(rule, dtstart, windowEnd) {\n    const freq = rule.FREQ;\n    if (freq !== 'WEEKLY' && freq !== 'DAILY') {\n        throw new Error('поддерживаются только FREQ=WEEKLY и FREQ=DAILY (см. ограничения проекта)');\n    }\n    const count = rule.COUNT ? Number(rule.COUNT) : Infinity;\n    const until = rule.UNTIL ? parseICSDate(rule.UNTIL, null) : null;\n    const wantedDays = freq === 'WEEKLY' && rule.BYDAY\n        ? new Set(rule.BYDAY.split(',').map(d => DAY_MAP[d]))\n        : new Set([dtstart.getUTCDay()]);\n\n    const results = [];\n    let cursor = new Date(dtstart);\n    let occurrences = 0;\n    const hardLimit = until && until < windowEnd ? until : windowEnd;\n\n    while (cursor <= hardLimit && occurrences < count) {\n        if ((freq === 'DAILY' || wantedDays.has(cursor.getUTCDay())) && cursor >= dtstart) {\n            results.push(new Date(cursor));\n            occurrences++;\n        }\n        cursor = new Date(cursor.getTime() + 24 * 3600 * 1000);\n    }\n    return results;\n}\n\nconst windowDays = Number(env.get('ICS_WINDOW_DAYS') || 30);\nconst now = new Date();\nconst windowEnd = new Date(now.getTime() + windowDays * 24 * 3600 * 1000);\n\nconst events = parseICS(msg.payload);\nconst instances = [];\nconst freshUids = new Set();\n\nfor (const ev of events) {\n    if (!ev.uid || !ev.start) continue;\n    freshUids.add(ev.uid);\n    if (ev.cancelled) continue;\n    const duration = ev.end.getTime() - ev.start.getTime();\n\n    if (ev.rrule) {\n        const starts = expandRRULE(ev.rrule, ev.start, windowEnd)\n            .filter(d => !ev.exdates.includes(d.getTime()));\n        for (const s of starts) {\n            instances.push({ uid: ev.uid, starts_at: s.toISOString(), ends_at: new Date(s.getTime() + duration).toISOString() });\n        }\n    } else if (ev.start >= now && ev.start <= windowEnd) {\n        instances.push({ uid: ev.uid, starts_at: ev.start.toISOString(), ends_at: ev.end.toISOString() });\n    }\n}\n\nmsg.payload = instances;\nmsg.freshUids = Array.from(freshUids);\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1090,"y":80,"wires":[["n-upsert-params"]]},
  {"id":"n-upsert-params","type":"function","z":"mr-ics-sync","name":"query для upsert bookings","func":"// Тот же приём, что в 'state -> rows' основного flow: SQL и параметры\n// собираются здесь, postgresql-нода ниже просто выполняет msg.query/msg.params.\nconst rows = msg.payload;\nconst room = env.get('ROOM_ID') || 'meeting-1';\n\nmsg.query =\n    \"INSERT INTO bookings (booking_uid, starts_at, ends_at, room_id, source, cancelled, updated_at) \" +\n    \"SELECT uid, starts_at, ends_at, $1, 'ics', false, now() \" +\n    \"FROM unnest($2::text[], $3::timestamptz[], $4::timestamptz[]) AS t(uid, starts_at, ends_at) \" +\n    \"ON CONFLICT (booking_uid, starts_at) DO UPDATE SET \" +\n    \"ends_at = EXCLUDED.ends_at, cancelled = false, updated_at = now();\";\nmsg.params = [room, rows.map(r => r.uid), rows.map(r => r.starts_at), rows.map(r => r.ends_at)];\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1330,"y":80,"wires":[["n-upsert"]]},
  {"id":"n-upsert","type":"postgresql","z":"mr-ics-sync","name":"bookings UPSERT","query":"","postgreSQLConfig":"ac8416347cb8cc3d","split":false,"rowsPerMsg":1,"outputs":1,"x":1560,"y":80,"wires":[["n-cancel-params"]]},
  {"id":"n-cancel-params","type":"function","z":"mr-ics-sync","name":"query для пометки отмен","func":"const room = env.get('ROOM_ID') || 'meeting-1';\nconst windowDays = env.get('ICS_WINDOW_DAYS') || '30';\n\nmsg.query =\n    \"UPDATE bookings SET cancelled = true, updated_at = now() \" +\n    \"WHERE room_id = $1 \" +\n    \"AND starts_at BETWEEN now() AND now() + ($2 || ' days')::interval \" +\n    \"AND booking_uid <> ALL($3);\";\nmsg.params = [room, windowDays, msg.freshUids];\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1330,"y":140,"wires":[["n-mark-cancelled"]]},
  {"id":"n-mark-cancelled","type":"postgresql","z":"mr-ics-sync","name":"bookings пометить отмены","query":"","postgreSQLConfig":"ac8416347cb8cc3d","split":false,"rowsPerMsg":1,"outputs":1,"x":1560,"y":140,"wires":[["n-debug"]]},
  {"id":"n-debug","type":"debug","z":"mr-ics-sync","name":"итог синка","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":1780,"y":140,"wires":[]}
]

Импортировать: Node-RED → меню → Import → вставить JSON → Import to new flow (конфиг ac8416347cb8cc3d уже существует в инстансе — импорт подхватит его по id, повторно вводить пароль не нужно).

Что адаптировать под себя:

ROOM_ID в .env — если переговорных несколько, таблица bookings уже на это рассчитана (поле room_id); значение должно совпадать с тем, что используется в rooms/sensor_data для того же помещения;
команда в n-exec — путь к gen_ics.py на конкретном сервере;
если для записи в bookings нужна отдельная роль (не nodered, у которой и так уже есть право писать во всё) — заведите новый postgreSQLConfig и подставьте его id вместо ac8416347cb8cc3d в обеих postgresql-нодах.

Часть 4 · Метрика — главный результат

Теперь то, ради чего всё делалось. Сравниваем два ряда: план (bookings) и факт (occupancy_hourly от датчика).

-- Занятость внутри каждой брони: сколько времени в помещении реально были люди.
WITH booked AS (
    SELECT booking_uid, starts_at, ends_at, room_id,
           EXTRACT(EPOCH FROM (ends_at - starts_at)) / 3600.0 AS booked_hours
      FROM bookings
     WHERE NOT cancelled
       AND starts_at >= now() - interval '30 days'
       AND ends_at   <= now()
),
fact AS (
    SELECT b.booking_uid, b.starts_at, b.booked_hours,
           -- средняя доля присутствия за время брони (0..1)
           COALESCE(AVG(o.occupancy_ratio), 0) AS occ_ratio
      FROM booked b
      LEFT JOIN occupancy_hourly o
             ON o.bucket >= date_trunc('hour', b.starts_at)
            AND o.bucket <  b.ends_at
     GROUP BY b.booking_uid, b.starts_at, b.booked_hours
)
SELECT
    count(*)                                              AS "броней",
    round(sum(booked_hours)::numeric, 1)                  AS "часов забронировано",
    round(sum(booked_hours * (1 - occ_ratio))::numeric, 1) AS "часов простояло",
    round(100.0 * sum(booked_hours * (1 - occ_ratio))
                / NULLIF(sum(booked_hours), 0), 0)        AS "% простоя",
    count(*) FILTER (WHERE occ_ratio < 0.20)              AS "no-show",
    round(100.0 * count(*) FILTER (WHERE occ_ratio < 0.20)
                / NULLIF(count(*), 0), 0)                 AS "% no-show"
  FROM fact;

Про порог no-show — важная оговорка

Порог 0.20 («если людей было меньше 20 % времени брони — считаем, что не пришли») — соглашение, а не факт. Он влияет на результат, и это надо показывать, а не прятать.

На модельных данных (4 брони: 85 %, 2 %, 45 %, 0 %):

Порог	no-show	Доля
5 %	2 из 4	50 %
10 %	2 из 4	50 %
20 %	2 из 4	50 %
30 %	2 из 4	50 %
50 %	3 из 4	75 %

Вывод устойчив в диапазоне 5–30 % и меняется при 50 %. Значит в отчёте надо либо приводить чувствительность, либо явно обосновать выбранный порог.

Это ровно то, что отличает инженерный отчёт от красивой картинки: названа граница, за которой вывод перестаёт держаться. На собеседовании такой абзац стоит больше, чем весь дашборд.

Метрика «часов простояло» от порога не зависит — она считается через непрерывную долю occ_ratio. Поэтому основным выводом лучше делать её, а no-show давать как дополнительную иллюстрацию.

Проверка честности данных

Прежде чем верить цифрам:

-- Полнота: за полный час ожидаем ~120 отсчётов (heartbeat раз в 30 с)
SELECT bucket, samples, occupancy_ratio
  FROM occupancy_hourly
 WHERE bucket > now() - interval '7 days' AND samples < 100
 ORDER BY bucket;

Часы с samples заметно меньше 120 — датчик молчал (отвал Wi-Fi, питание). Такие интервалы из расчёта исключать, иначе «простой» окажется отказом техники, а не поведением людей. Поле samples в вашем агрегате для этого и заведено.

Часть 5 · Дашборд

Три панели, больше не нужно:

Панель	Тип	Что показывает
«План против факта»	Time series	две линии: брони (ступенькой) и `occupancy_ratio`
«Простой внутри броней»	Stat	часы и % — главная цифра
«Топ часов дня»	Bar chart	когда бронируют, но не приходят

Первая панель — самая наглядная: видно, как бронь есть, а присутствия нет. Именно её показывают заказчику.

Панель «план против факта» — готовый JSON

Вставляется в существующий дашборд Eureka - dostępność (uid: grcl5jk) как ещё один элемент массива panels. Датасорс, dataset и стиль — те же, что и у остальных панелей этого дашборда (smarthome_db через grafana-postgresql-datasource, uid: efsyl0y9fcmiof); подписи — на польском, чтобы не выбиваться из остальных панелей.

Ряд «план» строится прямо в SQL через generate_series — отдельная таблица для почасовых броней не нужна, брони уже лежат в bookings интервалами. Ряд «факт» — тот же occupancy_hourly.occupancy_ratio, что и на панели «Wykorzystanie pokoju w ciągu godziny»; переиспользуется, а не считается заново.

{
  "datasource": { "type": "grafana-postgresql-datasource", "uid": "efsyl0y9fcmiof" },
  "fieldConfig": {
    "defaults": {
      "color": { "mode": "palette-classic" },
      "custom": {
        "axisBorderShow": false,
        "axisCenteredZero": false,
        "axisColorMode": "text",
        "axisLabel": "",
        "axisPlacement": "auto",
        "barAlignment": 0,
        "barWidthFactor": 0.6,
        "drawStyle": "line",
        "fillOpacity": 15,
        "gradientMode": "none",
        "hideFrom": { "legend": false, "tooltip": false, "viz": false },
        "insertNulls": false,
        "lineInterpolation": "stepAfter",
        "lineWidth": 2,
        "pointSize": 5,
        "scaleDistribution": { "type": "linear" },
        "showPoints": "never",
        "showValues": false,
        "spanNulls": false,
        "stacking": { "group": "A", "mode": "none" },
        "thresholdsStyle": { "mode": "off" }
      },
      "mappings": [],
      "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": 0 } ] }
    },
    "overrides": [
      {
        "matcher": { "id": "byName", "options": "Zarezerwowane" },
        "properties": [
          { "id": "custom.fillOpacity", "value": 40 },
          { "id": "custom.lineWidth", "value": 0 },
          { "id": "custom.axisPlacement", "value": "left" },
          { "id": "min", "value": 0 },
          { "id": "max", "value": 1 },
          { "id": "color", "value": { "mode": "fixed", "fixedColor": "orange" } }
        ]
      },
      {
        "matcher": { "id": "byName", "options": "Obecność, %" },
        "properties": [
          { "id": "custom.axisPlacement", "value": "right" },
          { "id": "custom.drawStyle", "value": "line" },
          { "id": "unit", "value": "percent" },
          { "id": "min", "value": 0 },
          { "id": "max", "value": 100 },
          { "id": "color", "value": { "mode": "fixed", "fixedColor": "blue" } }
        ]
      }
    ]
  },
  "gridPos": { "h": 8, "w": 24, "x": 0, "y": 16 },
  "id": 5,
  "options": {
    "legend": { "calcs": [], "displayMode": "list", "placement": "bottom", "showLegend": true },
    "tooltip": { "hideZeros": false, "mode": "multi", "sort": "none" }
  },
  "pluginVersion": "12.4.1",
  "targets": [
    {
      "dataset": "smarthome_db",
      "editorMode": "code",
      "format": "table",
      "rawQuery": true,
      "rawSql": "-- план: 1, если час пересекается с активной бронью\nWITH buckets AS (\n  SELECT generate_series(\n           date_trunc('hour', $__timeFrom()),\n           date_trunc('hour', $__timeTo()),\n           interval '1 hour') AS bucket\n)\nSELECT b.bucket AS time,\n       (EXISTS (\n          SELECT 1 FROM bookings bk\n           WHERE NOT bk.cancelled\n             AND bk.starts_at < b.bucket + interval '1 hour'\n             AND bk.ends_at   > b.bucket\n       ))::int AS \"Zarezerwowane\"\n  FROM buckets b\n ORDER BY b.bucket;",
      "refId": "A",
      "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 }
    },
    {
      "dataset": "smarthome_db",
      "editorMode": "code",
      "format": "table",
      "rawQuery": true,
      "rawSql": "-- факт: доля присутствия за час (та же метрика, что уже используется на панели\n-- \"Wykorzystanie pokoju w ciągu godziny\")\nSELECT bucket AS time, round(occupancy_ratio * 100) AS \"Obecność, %\"\nFROM occupancy_hourly\nWHERE $__timeFilter(bucket)\nORDER BY bucket;",
      "refId": "B",
      "sql": { "columns": [ { "parameters": [], "type": "function" } ], "groupBy": [ { "property": { "type": "string" }, "type": "groupBy" } ], "limit": 50 }
    }
  ],
  "title": "Rezerwacja a rzeczywista obecność",
  "type": "timeseries"
}

Как добавить: Dashboard settings → JSON Model, вставить этот объект внутрь массива "panels" (запятая после последнего существующего панеля), сохранить. id: 5 — следующий свободный после уже занятых 1/2/3/4; gridPos.y: 16 кладёт панель во всю ширину новой строкой под уже существующими.

Читается так: оранжевая заливка «Zarezerwowane» — интервалы бронирования; синяя линия «Obecność, %» — фактическое присутствие. Место, где заливка есть, а линия у нуля, — тот самый простой внутри брони, который и есть результат проекта.

Часть 6 · Проверка

RRULE-серия развернулась в N экземпляров — проверено вручную: gen_ics.py даёт 26 событий (1 серия + 25 разовых); разворот и через icalendar + dateutil.rrule (Python), и через свой парсер без зависимостей (Шаг 4 дорожной карты, тот же код, что в n-expand) даёт одинаковый результат — экземпляры weekly-standup совпадают с COUNT=12 в правиле, ограниченным окном синка, составной ключ (booking_uid, starts_at) уникален на всех экземплярах
повторный синк не создал дублей (count(*) не вырос)
удалённая из фида встреча получила cancelled = true
встреча вернулась в фид → cancelled снялся
время в БД в UTC — проверено: 10:00 Europe/Moscow в исходном ICS корректно превращается в 07:00+00:00 при приведении к UTC
часы с samples < 100 исключены из расчёта
метрика посчиталась на месячном окне
проверена чувствительность к порогу no-show

Частично проверено. Генератор и разворачивание RRULE (включая приведение к UTC и уникальность составного ключа) прогнаны на реальном коде из этой страницы — работают. Upsert, пометка отмен и SQL метрики пока проверены только логически и на модельном примере (таблица порогов no-show) — нужен живой TimescaleDB, чтобы закрыть оставшиеся пункты.

Что дальше

Результат в портфолио — как из этой цифры сделать то, о чём говорят на собеседовании.

Источник ICS В портфолио