test/backlog_buget_260111.md

SMS → n8n → LLM → PostgreSQL → Telegram

Текущая архитектура workflow (09.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 для отчётности.[^1]

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

---

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),
llm_processed_at TIMESTAMP WITH TIME ZONE,
human_verified   BOOLEAN DEFAULT FALSE,
human_verified_at TIMESTAMP WITH TIME ZONE

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

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, дата и пр.[^1]

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.[^1]

3.3. PostgreSQL: create_from_sms (INSERT)

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

3.4. Set: Edit Fields

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

3.5. HTTP Request: post_to_llm

• Метод: POST.
• URL: локальный LM Studio API (http://<LM_STUDIO_IP>:1234/v1/chat/completions).
• Модель: Qwen2.5‑7B‑Instruct (через LM Studio).[^1]

Тело запроса (JSON, Expression):

{
  "model": "qwen2.5-7b-instruct",
  "messages": [
    {
      "role": "system",
      "content": "Ты эксперт по категоризации расходов семейного бюджета в России. Анализируй банковские SMS и извлекай данные.\n\nТВОЯ ЗАДАЧА:\n- Определить контрагента (магазин, сервис, банк и т.п.).\n- Определить категорию расхода (еда, транспорт, подписки, одежда, здоровье, дом, техника, развлечения, авто, услуги и т.п.).\n\nВАЖНО:\n- Никогда не используй значения 'Неизвестно', 'Не определён', 'Требует уточнения' или похожие.\n- Даже если не уверен, выбери наиболее вероятного контрагента и категорию по тексту SMS.\n- Для контрагента используй удобное для человека название (например, 'Пятёрочка', 'Магнит', 'Яндекс Такси').\n- Если название контрагента написано латиницей или капсом (например, PYATEROCHKA, PEREKRESTOK, YANDEXTAXI), считай, что это русские бренды и приведи их к удобному русскому названию для человека: «Пятёрочка», «Перекрёсток», «Яндекс Такси» и т.п.\n\nСтарайся находить наиболее близкий известный российский бренд по написанию, а не считать его неизвестным.\n\nДОПУСТИМЫЕ КАТЕГОРИИ:\nИспользуй ТОЛЬКО следующие значения поля \"category\" (строго как написано, без синонимов):\n\"Продукты\" - продуктовые магазины, продовольственные товары\n\"Авто\" - топливо, парковки, платные дороги, ремонт авто и т.д.\n\"Здоровье\" - аптеки, поликлиники, больницы и т.д.\n\"Арчи\" - зоотовары, вет. клиники, вет. аптеки и т.д.\n\"ЖКХ\" - коммунальные платежи и т.д.\n\"Дом\" - мелочи для интерьера, напольные покрытия, мебель, техника для дома, бытовые мелочи и т.д. сюда можно отнести товары от контрагента OZON\n\"Проезд\" - все что связано с общественным транспортом (электрички, метро, такси и т.д.)\n\"Одежда\" - одежда и обувь\n\"Химия\" - бытовая химия и т.д.\n\"Косметика\" - косметика\n\"Инвестиции\" - перевод во вклады, покупка акций и т.д.\n\"Развлечения\" - любые развлечения (кино, музеи, концерты, отпуск и т.д.)\n\"Общепит\" - кафе, рестораны, столовые и другая еда вне дома\n\"Штрафы\" - любые штрафы\n\"Налоги\" - государственные налоги\n\"Подписки\" - обязательные переодические платежи (подписки, связь, интернет и т.д.)\n\"Перевод\" - перевод денежных средств\n\"Наличные\" - снятие или внесение наличных\n\"Подарки\" - сотовые телефоны, драгоценности, цветы и т.д.\n\"Спорт\" - покупка билетов (слотов) на спортивные мероприятия, покупка спортивного инвентаря, в том числе одежда и обувь из спортивных магазинов\n\nНикогда не придумывай новые формулировки категорий и не используй синонимы.\nЕсли не уверен, выбери наиболее подходящую категорию из этого списка.\n\nОтвечай ТОЛЬКО валидным JSON без каких‑либо пояснений."
    },
    {
      "role": "user",
      "content": "Проанализируй банковскую SMS и верни только JSON.\n\nSMS:\n{{ $json.sms_text }}\n\nВерни строго валидный JSON одного объекта вида:\n{\"counterparty\": \"\", \"category\": \"\"}\n\nГде:\n- \"counterparty\" — человекопонятное название контрагента на русском (например, \"Пятёрочка\", \"Магнит\", \"Яндекс Такси\").\n- \"category\" — ОДНА из допустимых категорий из системного промпта. Не придумывай других категорий и не используй синонимы.\n\nНе добавляй никаких других полей и комментариев. Только JSON."
    }
  ],
  "temperature": 0.2,
  "max_tokens": 256
}

3.6. Code: parse_from_llm

Актуальная логика:

• Берёт choices[^0].message.content из ответа.
• Убирает возможные обёртки json / (markdown).
• Парсит JSON вида:

{"counterparty": "...", "category": "..."}

• Объединяет с исходными данными из create_from_sms.
• Без subcategory и confidence.
• Подстраховывает пустые поля дефолтами.[^1]

Код:

// Получаем content от LLM
let content = $input.first().json.choices[^0].message.content;

// Убираем markdown-обёртку ```json ... ``` если есть
content = content
  .replace(/```json\n?/gi, '')
  .replace(/```\n?/g, '')
  .trim();

// Парсим очищенный JSON
const llmResponse = JSON.parse(content);

// Берём все данные из первоначальной транзакции
const originalData = $('create_from_sms').first().json;

// Объединяем: базовые данные + результаты LLM
return {
  ...originalData,
  counterparty: llmResponse.counterparty || originalData.counterparty || 'Продукты',
  category: llmResponse.category || originalData.category || 'Продукты'
};

3.7. PostgreSQL: update_llm_fields (UPDATE)

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

3.8. Telegram Send Message: edit_or_confirm

• Resource: Message.
• Operation: Send Message.
• Chat ID: {{ $json.chatId }}
• Text:

Требуется подтверждение транзакции #{{ $json.id }}

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

Подтвердить или изменить?

• Reply Markup (Inline Keyboard):
  • ✅ Подтвердить → confirm_{{ $json.id }}
  • ✏️ Изменить категорию → edit_category_{{ $json.id }}
  • ✏️ Изменить контрагента → edit_counterparty_{{ $json.id }}.[^1]

---

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.[^1]

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

4.2. IF: split_event_type

• Condition: {{ $json.callback_query }} Is Empty.
• TRUE → текстовое сообщение пользователя → ветка редактирования.
• FALSE → callback от кнопки → ветка подтверждения/редактирования.[^1]

---

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.[^1]
• Разбирает callback:

const parts = callbackData.split('_');
const action = parts;                    // 'confirm' | 'edit'
const field  = parts.length === 3 ? parts[^1] : 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.[^1]

5.2. IF: confirm_or_edit

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

5.3. IF: check_if_edited (ветка подтверждения)

• Condition: {{ $json.isEditedCard }} Equal true.
• TRUE → подтверждение после редактирования.
• FALSE → первое подтверждение без правок.[^1]

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

• PostgreSQL SELECT: row_select_by_id
  • Operation: Select
  • Table: transactions
  • Where: id = {{ $json.transactionId }}
  • Always Output Data = ON.[^1]
• PostgreSQL UPDATE: update_after_confirm
  • Operation: Update
  • Table: transactions
  • Columns:
    • human_verified = true;
    • human_verified_at = {{ $now }}
  • Where: id = {{ $json.transactionId }}
  • Always Output Data = ON (на выходе есть строка транзакции).[^1]

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

• Code: parse_card_text
  • Парсит counterparty и category из текста карточки по regex.
  • Возвращает: transactionId, counterparty, category, chatId, messageId.[^1]
• PostgreSQL UPDATE: update_edited_transaction
  • Operation: Update
  • Table: transactions
  • Columns:
    • counterparty = {{ $json.counterparty }};
    • category = {{ $json.category }};
    • human_verified = true.
  • Where: id = {{ $json.transactionId }}
  • Always Output Data = ON (строка транзакции).[^1]
• Code: prepare_confirmation_data

const parseData = $('parse_card_text').first().json;
const tx        = $input.first().json; // результат update_edited_transaction

return {
  transactionId: parseData.transactionId,
  chatId:        parseData.chatId,
  messageId:     parseData.messageId,
  counterparty:  parseData.counterparty,
  category:      parseData.category,
  raw_json: {
    signed_amount: tx.raw_json.signed_amount,
    currency:      tx.raw_json.currency,
  },
};

5.4. Merge: merge_confirm_paths

• Type: Merge
• Mode: Append
• Input 1: update_after_confirm (первое подтверждение).
• Input 2: prepare_confirmation_data (после редактирования).[^1]

Важно: обе ветки несут в себе данные транзакции (signed_amount, currency, counterparty, category, chatId, messageId) к моменту входа в confirmation_message.[^1]

5.5. Telegram Answer Callback Query: popup_confirm

• Resource: Callback.
• Operation: Answer Callback Query.
• Query ID: {{ $json.callback_query.id }}
• Text: ✅ Транзакция подтверждена и сохранена.[^1]

5.6. Telegram Send Message: confirmation_message

• Resource: Message.
• Operation: Send Message.
• Chat ID: {{ $('answer_trigger').first().json.callback_query.message.chat.id }}
• Text:

✅ Транзакция подтверждена

Сумма: {{ $json.raw_json.signed_amount }} {{ $json.raw_json.currency }}
Контрагент: {{ $json.counterparty }}
Категория: {{ $json.category }}
Подтверждено: {{ $now.plus({hours: 3}).toFormat('dd.MM HH:mm') }}

---

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 → редактируем контрагента.[^1]

6.1.3. PostgreSQL INSERT: add_session_category

• Table: user_sessions
• Columns:
  • chatId = {{ $json.chatId }}
  • transactionId = {{ $json.transactionId }}
  • waitingFor = category
  • tempCounterparty = {{ $json.counterparty }}
  • tempCategory = {{ $json.category }}.[^1]

6.1.4. PostgreSQL INSERT: add_session_counterparty

• Аналогично, но waitingFor = 'counterparty'.[^1]

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.[^1]

7.2. PostgreSQL SELECT: get_session

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

Возвращает:

{
  "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

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'
};

7.5. PostgreSQL SELECT: get_current_transaction

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

7.6. Code: merge_changes

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

const tempCategory =
  sessionData?.tempCategory != null ? sessionData.tempCategory : currentData.category;

const tempCounterparty =
  sessionData?.tempCounterparty != null ? 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:
  • ✅ Подтвердить → confirm_{{ $json.id }}
  • ✏️ Изменить категорию → edit_category_{{ $json.id }}
  • ✏️ Изменить контрагента → edit_counterparty_{{ $json.id }}.[^1]

---

8. Интеграция с Google Sheets

8.1. Структура таблицы

• Отдельный документ семейного бюджета в Google Sheets.
• Лист, например Transactions.
• Колонки:

1. дата
2. контрагент
3. сумма
4. категория
5. примечание (заполняется пользователем вручную).

8.2. Доступ через Google Service Account

• В Google Cloud проекте budget создан сервисный аккаунт.
• Включены API: Google Sheets API, Google Drive API.
• Скачан JSON‑ключ сервисного аккаунта; в n8n созданы credentials типа Google Service Account, использован весь JSON или поля client_email + private_key
• В самой таблице сервисный аккаунт добавлен как редактор по client_email.

8.3. Узел Google Sheets после merge_confirm_paths

• После ноды merge_confirm_paths добавлен узел Google Sheets (v2).
• Credentials: Google Service Account.
• Resource: Sheet Within Document.
• Operation: Append Row.
• Document: семейный бюджет.
• Sheet: лист с транзакциями (например, Transactions).

Mapping колонок (Map Each Column Manually):

• дата → {{ $json.received_at }}
• контрагент → {{ $json.counterparty }}
• сумма → {{ $json.raw_json?.signed_amount ?? $json.signed_amount }}
• категория → {{ $json.category }}
• примечание → "" (пусто, заполняется человеком).

Фильтрация только подтверждённых транзакций:

• Ветка к Google Sheets подключена после подтверждения (update_after_confirm / update_edited_transaction через merge_confirm_paths), где human_verified = true гарантирован.

---

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

9.1. LLM / категории (Приоритет 1)

• Собрать статистику по качеству автокатегоризации и при необходимости скорректировать описания категорий/словарь.

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

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

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

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

---

Версия документа: 4.2 Дата: 2026‑01‑09 Статус: Архитектура workflow обновлена: LLM работает с фиксированным набором категорий, подтверждённые транзакции пишутся в Google Sheets, дальнейшая работа — автоматизация запуска и улучшения UX.