admin/test
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
test/backlog_buget_260106.md
vakabunga 643eddb5fc initial commit
2026-01-16 18:37:32 +03:00

22 KiB
Raw Permalink Blame History

SMS → n8n → LLM → PostgreSQL → Telegram

Текущая архитектура workflow и статус проекта (05.01.2026)

1. Общий контур решения

Источник данных — банковские SMS, которые попадают в n8n через Webhook. Дальше SMS:

  1. парсятся в нормализованный JSON;
  2. сохраняются в PostgreSQL (таблица transactions);
  3. обогащаются локальной LLM (Qwen2.5 через LM Studio);
  4. отправляются в Telegram‑бота в виде карточки с кнопками;
  5. проходят через human‑in‑the‑loop: пользователь подтверждает или редактирует контрагента и категорию;
  6. после финального подтверждения данные фиксируются в БД и могут быть отправлены во внешние системы (Google Sheets и т.п. — планируется).

Проект развернут на Synology NAS: PostgreSQL. n8n в Docker и LM Studio — на локальной машине.

2. База данных

2.1. Таблица transactions

id BIGSERIAL PRIMARY KEY,
raw_sms         TEXT NOT NULL,
sms_text        TEXT NOT NULL,
sms_sender      VARCHAR(50),
action          VARCHAR(20) NOT NULL,
amount          NUMERIC(15, 2),
signed_amount   NUMERIC(15, 2),
currency        VARCHAR(3) DEFAULT 'RUB',
balance         NUMERIC(15, 2),
received_at     TIMESTAMP WITH TIME ZONE NOT NULL,
processed_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
raw_json        JSONB NOT NULL,

-- LLM / Human-in-the-loop
counterparty    VARCHAR(255),
category        VARCHAR(100),
subcategory     VARCHAR(100),
llm_processed_at TIMESTAMP WITH TIME ZONE,
human_verified   BOOLEAN DEFAULT FALSE,
human_verified_at TIMESTAMP WITH TIME ZONE

Есть индексы по дате, действию, контрагенту, категории и флагу human_verified.

2.2. Таблица user_sessions

Используется для хранения состояния редактирования в Telegram (какую транзакцию и какое поле сейчас меняет пользователь).

"chatId"         BIGINT PRIMARY KEY,
"transactionId"  BIGINT NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
"waitingFor"     VARCHAR(20) NOT NULL,           -- 'category' | 'counterparty'
"createdAt"      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
"tempCounterparty" VARCHAR(255),
"tempCategory"     VARCHAR(100)

3. Ветка 1. Обработка входящих SMS (Webhook → LLM → БД → Telegram)

3.1. Webhook (Node: Webhook)

  • Method: POST.
  • Принимает JSON с полями SMS от прокси/смс‑шлюза.
  • Входные данные: raw_sms, sms_text, sms_sender, дата и пр.

3.2. Code: parse_from_sms

  • Преобразует входящий JSON из Webhook в нормализованный объект:
    • action (тип операции: payment/credit/cash и т.п.);
    • amount, signed_amount, currency;
    • balance;
    • received_at;
    • raw_sms, sms_text, sms_sender;
    • raw_json — исходный JSON целиком.

Результат — один объект NormalizedTransaction.

3.3. PostgreSQL: create_from_sms (INSERT)

  • Operation: Insert
  • Table: transactions
  • Поля: все базовые из parse_from_sms (без LLM‑полей).
  • Options: Always Output Data = ON — чтобы сразу получить id созданной строки.

3.4. Set: Edit Fields

  • Урезает объект перед LLM (data minimization): оставляет только то, что нужно модели:
    • sms_text, action, amount, currency, balance.

3.5. HTTP Request: post_to_llm

  • Метод: POST.
  • URL: локальный LM Studio API (http://<LM_STUDIO_IP>:1234/v1/chat/completions).
  • Тело: промпт с описанием SMS, типа операции и суммы.
  • Модель: Qwen2.5‑7B‑Instruct (через LM Studio).

3.6. Code: parse_from_llm

  • Берёт choices[^0].message.content из ответа.
  • Убирает возможные обёртки json /.
  • Парсит JSON вида:
{"counterparty": "...", "category": "...", "subcategory": "...", "confidence": 0.0}
  • Мёрджит с исходными данными из create_from_sms.
  • Гарантирует значения по умолчанию, если LLM что‑то не вернула:
    • counterparty: "Не определён"
    • category: "Неизвестно"
    • subcategory: "Требует уточнения".

3.7. PostgreSQL: update_llm_fields (UPDATE)

  • Operation: Update
  • Table: transactions
  • Where: id = {{ $json.id }}
  • Поля для обновления:
    • counterparty, category, subcategory;
    • llm_processed_at = NOW();
    • human_verified = FALSE.
  • Options: Always Output Data = ON — чтобы вернуть актуальную строку с chatId.

3.8. Telegram Send Message: edit_or_confirm

  • Chat ID: {{ $json.chatId }} (expression).
  • Parse Mode: HTML (или Markdown, без использования опасных спецсимволов).
  • Text, пример:
Требуется подтверждение транзакции #{{ $json.id }}

Сумма: {{ $json.signed_amount }} {{ $json.currency }}
Контрагент: {{ $json.counterparty }}
Категория: {{ $json.category }}
Дата: {{ $json.received_at }}

Подтвердить или изменить?
  • Reply Markup (Inline Keyboard, через визуальный редактор ноды):
    • Row 1:
      • Text: ✅ Подтвердить, Callback Data: confirm_{{ $json.id }}
    • Row 2:
      • Text: ✏️ Изменить категорию, Callback Data: edit_category_{{ $json.id }}
      • Text: ✏️ Изменить контрагента, Callback Data: edit_counterparty_{{ $json.id }}

4. Ветка 2. Единый Telegram Trigger и разветвление по типу события

4.1. Telegram Trigger: answer_trigger

  • Credential: Telegram Bot.
  • Trigger On: callback_query.
  • Updates: callback_query, message (оба включены).
  • Base URL: https://api.telegram.org.

Все события (клики по кнопкам и текстовые сообщения) приходят в этот один триггер.

4.2. IF: split_event_type

  • Condition: {{ $json.callback_query }} Is Empty.
  • TRUE → это текстовое сообщение пользователя → в ветку редактирования (parse_text_input).
  • FALSE → это callback от кнопки → в parse_from_callback.

5. Ветка 3. Подтверждение транзакции (без/после редактирования)

5.1. Code: parse_from_callback

Вход: объект update с callback_query. Код (концептуально):

  • Получает:
    • callbackData = $json.callback_query.data;
    • chatId = $json.callback_query.message.chat.id;
    • messageId = $json.callback_query.message.message_id;
    • messageText = $json.callback_query.message.text.
  • Разбирает callback:
const parts = callbackData.split('_');
const action = parts;        // 'confirm' | 'edit'
const field = parts.length === 3 ? parts[^2] : null; // 'category' | 'counterparty'
const transactionId = parseInt(parts[parts.length - 1]);
  • Определяет, редактировалась ли карточка:
const isEditedCard = messageText && messageText.includes('не сохранено');
  • Парсит текущие значения из текста карточки:
const counterpartyMatch = messageText.match(/Контрагент:\s*(.+?)(?:\n|$)/);
const categoryMatch    = messageText.match(/Категория:\s*(.+?)(?:\n|$)/);
  • Возвращает объект с:
    • action, field, transactionId, chatId, messageId,
    • messageText, isEditedCard, originalCallbackData,
    • counterparty, category.

5.2. IF: confirm_or_edit

  • Condition: {{ $json.action }} Equal confirm.
  • TRUE → ветка подтверждения.
  • FALSE → ветка редактирования.

5.3. IF: check_if_edited

Работает только в ветке подтверждения.

  • Condition: {{ $json.isEditedCard }} Equal true.
  • TRUE → карточка уже обновлялась (на ней есть маркер «не сохранено»).
  • FALSE → первое подтверждение без редактирования.

5.3.1. FALSE (первое подтверждение)

  • PostgreSQL SELECT: row_select_by_id
    • Operation: Select
    • Table: transactions
    • Where: id = {{ $json.transactionId }}
    • Always Output Data = ON.
  • PostgreSQL UPDATE: update_after_confirm
    • Operation: Update
    • Table: transactions
    • Columns:
      • human_verified = true;
      • human_verified_at = {{ $now }}.
    • Where: id = {{ $json.transactionId }}.

5.3.2. TRUE (после редактирования)

  • Code: parse_card_text
    • Парсит из текста карточки:
      • counterparty и category;
      • использует те же regex, что и в parse_from_callback.
    • Возвращает: transactionId, counterparty, category, chatId, messageId.
  • PostgreSQL UPDATE: update_edited_transaction
    • Operation: Update
    • Table: transactions
    • Columns:
      • counterparty = {{ $json.counterparty }};
      • category = {{ $json.category }};
      • human_verified = true.
    • Where: id = {{ $json.transactionId }}.
  • Code: prepare_confirmation_data
    • Приводит данные к единому формату для вывода:
      • transactionId, chatId, messageId, counterparty, category.

5.4. Merge: merge_confirm_paths

  • Type: Merge
  • Mode: Append
  • Input 1: update_after_confirm (первое подтверждение).
  • Input 2: prepare_confirmation_data (после редактирования).
  • Выход: в ноду popup_confirm.

5.5. Telegram Answer Callback Query: popup_confirm

  • Query ID: {{ $json.callback_query.id }}
  • Text: ✅ Транзакция подтверждена и сохранена.

5.6. Telegram Edit Message Text: confirmation_message

  • Chat ID: {{ $json.chatId }}
  • Message ID: {{ $json.messageId }}
  • Text:
✅ Транзакция #{{ $json.transactionId }} подтверждена

Контрагент: {{ $json.counterparty }}
Категория: {{ $json.category }}

6. Ветка 4. Редактирование транзакции с циклом

6.1. Построение сессии редактирования

После confirm_or_edit, если action = 'edit', управление идёт в:

6.1.1. PostgreSQL Execute Query: empty_session

  • Удаляет старую сессию и пробрасывает входные данные дальше.
  • Query:
DELETE FROM user_sessions WHERE "chatId" = {{ $json.chatId }};

SELECT 
  '{{ $json.action }}'::text            AS "action",
  '{{ $json.field }}'::text             AS "field",
  {{ $json.transactionId }}::bigint     AS "transactionId",
  {{ $json.chatId }}::bigint           AS "chatId",
  {{ $json.messageId }}::bigint        AS "messageId",
  {{ $json.isEditedCard }}::boolean    AS "isEditedCard",
  '{{ $json.originalCallbackData }}'::text AS "originalCallbackData",
  '{{ $json.counterparty }}'::text     AS "counterparty",
  '{{ $json.category }}'::text         AS "category";

6.1.2. IF: which_field_to_edit

  • Condition: {{ $json.field }} Equal category.
  • TRUE → редактируем категорию.
  • FALSE → редактируем контрагента.

6.1.3. PostgreSQL INSERT: add_session_category

  • Table: user_sessions
  • Columns:
    • chatId = {{ $json.chatId }}
    • transactionId = {{ $json.transactionId }}
    • waitingFor = category (Fixed)
    • tempCounterparty = {{ $json.counterparty }}
    • tempCategory = {{ $json.category }}

6.1.4. PostgreSQL INSERT: add_session_counterparty

Аналогично, но:

  • waitingFor = counterparty.

6.2. Запрос на ввод значения

6.2.1. Telegram Send Message: category_edit

  • Chat ID: {{ $json.chatId }}
  • Text:
Введите новую категорию для транзакции #{{ $json.transactionId }}

ВАЖНО: Лимит 12 символов

6.2.2. Telegram Send Message: counterparty_edit

  • Chat ID: {{ $json.chatId }}
  • Text:
Введите нового контрагента для транзакции #{{ $json.transactionId }}

ВАЖНО: Лимит 12 символов

Далее пользователь вводит текст в ответ.

7. Ветка 5. Обработка текстового ответа пользователя

7.1. answer_trigger → split_event_type (TRUE)

Приходит message от пользователя (без callback_query).

TRUE‑ветка split_event_type ведёт в get_session.

7.2. PostgreSQL SELECT: get_session

  • Operation: Select
  • Table: user_sessions
  • Where: chatId = {{ $json.message.chat.id }}
  • Always Output Data = ON.

Возвращает:

{
  "chatId": "...",
  "transactionId": "...",
  "waitingFor": "category" | "counterparty",
  "tempCounterparty": "...",
  "tempCategory": "...",
  "createdAt": "..."
}

7.3. Code: merge_user_and_session

Объединяет данные сообщения и сессии:

const sessionData = $input.first().json;
const userMessage = $('split_event_type').first().json;

return {
  message: userMessage.message,
  session: sessionData
};

7.4. Code: parse_text_input

Работает уже поверх структуры { message, session }:

const userText = $input.first().json.message.text;
const chatId   = $input.first().json.message.chat.id;

// Игнорируем команды
if (userText.startsWith('/')) {
  throw new Error('Команда проигнорирована');
}

const sessionData = $input.first().json.session;

if (!sessionData || !sessionData.transactionId) {
  throw new Error('Сессия не найдена. Начните редактирование заново из карточки транзакции.');
}

return {
  newValue: userText.trim(),
  chatId: chatId,
  transactionId: parseInt(sessionData.transactionId),
  changedField: sessionData.waitingFor       // 'category' | 'counterparty'
};

Выход: newValue, chatId, transactionId, changedField.

7.5. PostgreSQL SELECT: get_current_transaction

  • Operation: Select
  • Table: transactions
  • Where: id = {{ $json.transactionId }}
  • Always Output Data = ON.

Возвращает текущую строку транзакции (с LLM‑значениями).

7.6. Code: merge_changes

Применяет изменение без записи в БД, с учётом данных из user_sessions:

const textInput   = $('parse_text_input').first().json;
const sessionData = $('get_session').first().json;
const currentData = $input.first().json;

const tempCategory    = sessionData?.tempCategory    ?? currentData.category;
const tempCounterparty = sessionData?.tempCounterparty ?? currentData.counterparty;

const updatedCategory =
  textInput.changedField === 'category'
    ? textInput.newValue
    : tempCategory;

const updatedCounterparty =
  textInput.changedField === 'counterparty'
    ? textInput.newValue
    : tempCounterparty;

return {
  ...currentData,
  category: updatedCategory,
  counterparty: updatedCounterparty,
  chatId: textInput.chatId
};

Результат — «виртуально» обновлённая транзакция, которая ещё не записана в БД.

7.7. Telegram Send Message: show_updated_card

  • Chat ID: {{ $json.chatId }}
  • Text:
Данные обновлены (не сохранено)

Сумма: {{ $json.signed_amount }} {{ $json.currency }}
Контрагент: {{ $json.counterparty }}
Категория: {{ $json.category }}
Дата: {{ $json.received_at }}

ВАЖНО: Лимит 12 символов на поле
  • Inline Keyboard (аналогично edit_or_confirm):
    • ✅ Подтвердить → confirm_{{ $json.id }}
    • ✏️ Изменить категорию → edit_category_{{ $json.id }}
    • ✏️ Изменить контрагента → edit_counterparty_{{ $json.id }}

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

8. Статус проекта на 05.01.2026

8.1. Реализовано

  • Полная цепочка обработки SMS:
    • Webhook → parse_from_sms → create_from_sms → post_to_llm → parse_from_llm → update_llm_fields → edit_or_confirm.
  • Единый Telegram Trigger:
    • разделение message / callback через split_event_type.
  • Ветка подтверждения:
    • первое подтверждение → human_verified = TRUE;
    • подтверждение после редактирования → парсинг карточки и финальное обновление counterparty/category.
  • Ветка редактирования с сессиями:
    • хранение состояния в user_sessions (какую транзакцию и какое поле редактируем);
    • временные поля tempCounterparty, tempCategory для сохранения уже внесённых изменений;
    • цикл «Изменить → ввести текст → обновлённая карточка → снова изменить / подтвердить» — логика нод выстроена, корректность проходит по шагам в ручных тестах.

8.2. На чём остановились

  • Полный автоматический цикл редактирования почти отлажен:
    • контрагент и категория при последовательных правках больше не должны затирать друг друга значениями LLM из БД;
    • требуется ещё один‑два полных сквозных теста «несколько правок подряд → финальное подтверждение → проверка строки в transactions», чтобы зафиксировать, что все граничные случаи (несколько итераций, смена поля, отмена и т.п.) работают стабильно.

9. Планируемые доработки

(частично перенесено из предыдущей версии документа и актуализировано)

9.1. Интеграция с Google Sheets (Приоритет 1)

  • Создать таблицу семейного бюджета в Google Sheets.
  • Настроить Google Service Account и подключить Google Sheets node в n8n.
  • Добавить ноду Google Sheets после merge_confirm_paths:
    • записывать только подтверждённые транзакции;
    • колонки: Дата | Контрагент | Сумма | Категория | Подкатегория.
  • Настроить форматирование (цвета для доходов/расходов).

9.2. Автоматизация запуска (Приоритет 2)

  • Автозапуск LM Studio при старте системы (service/systemd).
  • Docker restart: always для n8n и PostgreSQL.
  • Health‑check LM Studio API и уведомления в Telegram при падении.

9.3. Улучшения и UX (Приоритет 3)

  • Удалить подкатегорию.
  • Удалить confidence из промпта и на всех этапах Workflow.
  • Скорректировать промпт.
  • Обработка исторических данных (2025 год и далее) для накопления статистики.
  • Потенциальный fine‑tuning модели LLM на собственных размеченных данных.
  • Кнопка «❌ Отклонить» для удаления/игнорирования ошибочных транзакций.
  • Ограничения и валидация пользовательского ввода (длина, спецсимволы).
  • Периодическая очистка старых записей в user_sessions (например, старше 1 часа).
  • Добавление исходного текста SMS в карточку (с безопасным форматированием или экранированием).
  • Аналитика по категориям, отчёты и экспорт (CSV/Excel/BI).

Версия документа: 4.0 Дата: 2026‑01‑05 Статус: Основной функционал реализован, финальная отладка цикла редактирования/подтверждения в процессе; интеграция с внешними отчётами и автоматизация запуска в планах.

Reference in New Issue View Git Blame Copy Permalink