Netpulse_SasS/server/migrations/0061_telegram_callbacks.sql

-- =====================================================================
-- NetPulse :: 0061_telegram_callbacks.sql
-- Приймач натискань кнопок Telegram: чий палець, звідки продовжити
-- читати оновлення й хто саме підтвердив алерт.
--
-- Що зараз. Сповіщення в Telegram іде з двома кнопками — «Підтвердити»
-- і «Заглушити 1 год» (notify.go, inline_keyboard). Кнопки видно,
-- натиснути можна, і не стається НІЧОГО: приймача немає. Гірше за
-- бездіяльність те, як це виглядає з телефона — Telegram малює на
-- кнопці годинник і крутить його, доки бот не відповість на
-- answerCallbackQuery. Не відповідає ніхто, тож годинник висить до
-- таймауту. Тобто в найпомітнішому місці продукту стоїть обіцянка,
-- яка не виконується, і виглядає це не як «ще не зробили», а як
-- «зламалось».
--
-- Чому взагалі потрібні таблиці, адже натискання приходить з усім
-- потрібним усередині. Не з усім. У callback_query є chat_id,
-- message_id і telegram-акаунт того, хто натиснув. Немає трьох речей,
-- і кожна з них тут окремою таблицею:
--
--   1. Хто це в NetPulse. Підтвердження алерту записується від
--      конкретного користувача (alr.alerts.acked_by), і «підтвердив
--      хтось із чату» — це не відповідь. Потрібне зіставлення
--      telegram user_id → core.users.
--   2. Одноразовий код, яким людина це зіставлення заводить. Питати в
--      неї числовий telegram user_id безглуздо: вона його не знає.
--   3. Місце в черзі оновлень getUpdates. Без нього перезапуск
--      процесу програє двічі: або втрачає натискання, або переграє
--      добову історію (Telegram тримає невибрані оновлення 24 години)
--      і глушить хости о десятій ранку за кнопкою, натиснутою вночі.
--
-- Чого тут навмисно НЕМАЄ: зв'язку «повідомлення Telegram → алерт».
-- Спокуса завести його є — редагувати ж повідомлення після дії треба.
-- Але редагувати треба РІВНО те повідомлення, кнопку якого натиснули,
-- а його chat_id і message_id приходять у самому callback_query.
-- Таблиця тут відповідала б на питання, якого ніхто не ставить, і
-- при цьому вимагала б підтримки в актуальному стані. Довідка «яким
-- повідомленням це поїхало» вже є: alr.notifications.external_id
-- зберігає message_id з першої міграції алертів.
-- =====================================================================

-- ---------------------------------------------------------------------
-- Хто натиснув
-- ---------------------------------------------------------------------

-- Зіставлення telegram-акаунта з користувачем NetPulse.
--
-- Прив'язка тенантна, а не глобальна, хоч core.users і глобальні. Один
-- підрядник обслуговує кілька кабінетів і в кожному є окремим
-- користувачем; глобальна прив'язка означала б, що його натискання в
-- чаті одного клієнта підтверджує алерт від імені його ж облікового
-- запису в іншому — того, який до цього чату стосунку не має.
CREATE TABLE core.telegram_accounts (
  id         uuid PRIMARY KEY DEFAULT core.new_id(),
  tenant_id  uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

  -- Ідентифікатор акаунта в Telegram. bigint, а не int: у Bot API це
  -- 64-бітне число, і на нових акаунтах воно вже не вміщається в 32
  -- біти. Саме це поле — предмет перевірки «чий палець»; @username
  -- поруч лежить лише для показу й для нього не годиться, бо його
  -- можна змінити або зайняти після чужої відмови.
  tg_user_id   bigint NOT NULL,
  tg_username  text NOT NULL DEFAULT '',
  -- Ім'я на момент прив'язки — щоб у профілі було видно, ЯКИЙ саме
  -- акаунт прив'язано, коли @username порожній (він необов'язковий).
  tg_name      text NOT NULL DEFAULT '',

  linked_at      timestamptz NOT NULL DEFAULT now(),
  -- Коли цим акаунтом востаннє щось робили. Дає відповідь на «ця
  -- прив'язка ще жива чи лишилась від людини, яка звільнилась торік».
  last_action_at timestamptz
);

-- Один telegram-акаунт — один користувач у межах кабінету.
--
-- Без цього обмеження двоє могли б прив'язати один і той самий
-- telegram, і підтвердження записувалось би від того з них, кого
-- першим поверне запит, — тобто випадково.
CREATE UNIQUE INDEX telegram_accounts_tg_uniq
  ON core.telegram_accounts (tenant_id, tg_user_id);

-- І навпаки: у користувача не більше одного telegram у кабінеті.
-- Два прив'язані акаунти означали б, що людина має два обличчя в
-- журналі підтверджень, і жодне з них не головне.
CREATE UNIQUE INDEX telegram_accounts_user_uniq
  ON core.telegram_accounts (tenant_id, user_id);

ALTER TABLE core.telegram_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE core.telegram_accounts FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core.telegram_accounts
  USING (tenant_id = core.current_tenant())
  WITH CHECK (tenant_id = core.current_tenant());

GRANT SELECT, INSERT, UPDATE, DELETE ON core.telegram_accounts
  TO netpulse_app, netpulse_worker;

COMMENT ON TABLE core.telegram_accounts IS
  'Зіставлення telegram user_id із користувачем NetPulse: від чийого імені записується підтвердження з кнопки';
COMMENT ON COLUMN core.telegram_accounts.tg_user_id IS
  'Той самий id, що приходить у callback_query.from.id — єдина перевірювана ознака особи';

-- ---------------------------------------------------------------------
-- Чим людина заводить прив'язку
-- ---------------------------------------------------------------------

-- Одноразовий код: людина бере його в профілі й шле боту «/link КОД».
--
-- Чому саме так, а не полем «ваш telegram id» у профілі. Свого
-- числового id людина не знає й дізнатись його може лише через
-- сторонніх ботів — тобто ми б відправляли користувача віддати свою
-- ідентичність невідомо кому заради нашої ж форми. Крім того, поле
-- вводу дозволяє вписати ЧУЖИЙ id: прив'язка без доказу володіння
-- акаунтом — це спосіб підставити колегу під чужі підтвердження.
-- Повідомлення боту таким доказом є: його не надіслати за іншого.
--
-- Напрям обміну теж має значення. Код народжується в NetPulse, де
-- людина вже увійшла під своїм паролем, і пред'являється в Telegram.
-- Зворотний напрям (бот дає код, людина вставляє його в UI) вимагав би
-- від нас довіри до того, що код принесла та сама людина, — а він на
-- цьому шляху встигає полежати в буфері обміну спільного чату.
CREATE TABLE core.telegram_link_codes (
  -- sha256 коду, а не сам код. Той самий підхід, що в запрошеннях
  -- зондів (0023): рядок у базі не має бути чинним доступом. Код
  -- живе рівно двічі — на екрані профілю й у повідомленні боту.
  code_hash  bytea PRIMARY KEY,

  tenant_id  uuid NOT NULL REFERENCES core.tenants(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

  -- Чверть години. Стільки триває шлях «побачив код → відкрив
  -- Telegram → надіслав». Довший строк перетворює код на пароль, який
  -- лежить у чиємусь чаті; коротший не переживає пошуку потрібного
  -- чату в телефоні.
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  -- Одноразовість тут справжня, на відміну від квитків завантаження
  -- (0038): повторний запит браузера код не переграє, а от підглянутий
  -- у груповому чаті — переграє залюбки.
  used_at    timestamptz
);

-- Прибирання протухлих кодів іде за цим індексом.
CREATE INDEX telegram_link_codes_expiry_idx ON core.telegram_link_codes (expires_at);

-- Один незужитий код на людину: другий запит замінює перший, а не
-- додає ще один чинний. Інакше кожне відкриття сторінки профілю
-- залишало б по живому коду, і всі вони лишались би дійсними.
CREATE UNIQUE INDEX telegram_link_codes_user_uniq
  ON core.telegram_link_codes (tenant_id, user_id) WHERE used_at IS NULL;

ALTER TABLE core.telegram_link_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE core.telegram_link_codes FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON core.telegram_link_codes
  USING (tenant_id = core.current_tenant())
  WITH CHECK (tenant_id = core.current_tenant());

GRANT SELECT, INSERT, UPDATE, DELETE ON core.telegram_link_codes
  TO netpulse_app, netpulse_worker;

COMMENT ON TABLE core.telegram_link_codes IS
  'Одноразові коди прив''язки telegram-акаунта; шукаються поза тенантним контекстом — кабінет з''ясовується з самого рядка';

-- ---------------------------------------------------------------------
-- Де ми зупинились у черзі оновлень
-- ---------------------------------------------------------------------

-- Курсор getUpdates.
--
-- Спершу про те, чому взагалі getUpdates, а не вебхук. Вебхук Telegram
-- вимагає, щоб сервер відповідав на 443/80/88/8443 за ДІЙСНИМ
-- сертифікатом на ДОМЕН. Наше розгортання — самопідписаний TLS на
-- IP-адресі, домену немає. setWebhook такий стенд просто не прийме, і
-- «зробимо вебхук, а сертифікат потім» означало б код, який не працює
-- у жодній наявній інсталяції. Довге опитування натомість не вимагає
-- від нас ані вхідного порту, ані імені, ані сертифіката — з'єднання
-- ініціює сам сервер.
--
-- Ключ — не канал і не тенант, а сам бот. getUpdates ексклюзивний:
-- вибране оновлення другому читачеві вже не дістанеться. Один бот
-- цілком може обслуговувати кілька каналів (різні чати, різні кабінети
-- в self-hosted), і курсор на канал означав би, що два канали крадуть
-- оновлення один в одного через раз.
CREATE TABLE alr.telegram_cursors (
  -- sha256 токена бота. Сам токен лежить зашифрованим у core.secrets і
  -- тут не повторюється: копія секрету в допоміжній таблиці — це той
  -- самий секрет, тільки про який забули.
  bot_hash       bytea PRIMARY KEY,

  -- update_id, з якого читати далі. Telegram вважає оновлення
  -- підтвердженим, коли наступний getUpdates приходить з offset більшим
  -- за його update_id, — тому значення тут завжди «останній оброблений
  -- + 1».
  next_update_id bigint NOT NULL,
  updated_at     timestamptz NOT NULL DEFAULT now()
);

-- tenant_id тут немає, і це навмисно: рядок описує не кабінет, а стан
-- нашого читання чужої черги. RLS 0011 накочується на таблиці з
-- tenant_id, тож ця під неї не підпадає й без винятків.
GRANT SELECT, INSERT, UPDATE, DELETE ON alr.telegram_cursors
  TO netpulse_app, netpulse_worker;

COMMENT ON TABLE alr.telegram_cursors IS
  'Місце в черзі getUpdates для кожного бота: без нього перезапуск або губить натискання, або переграє добову історію';