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

15 KiB
Raw Permalink Blame History

SMS → n8n → LLM → PostgreSQL → Telegram → Google Sheets

Текущая архитектура workflow (11.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,
chat_id         BIGINT,

-- 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
  • idx_chat_categories на (chat_id, category, received_at DESC) для персонализации

2.2. Таблица user_sessions

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

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

2.3. Таблица user_category_stats

Кэш популярных категорий пользователей для персонализации.

chat_id BIGINT,
category VARCHAR(100),
count INTEGER DEFAULT 1,
last_used TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (chat_id, category)

Индекс: idx_user_cats на (chat_id, count DESC)

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 в нормализованный объект.

Типы операций (action):

const rules = [
  { action: 'salary',     re: /зарплат/i },
  { action: 'reversal',   re: /(отмена|возврат)/i },
  { action: 'payment',    re: /(оплата|покупк)/i },
  { action: 'transfer',   re: /перевод/i },
  { action: 'writeoff',   re: /списан/i },
  { action: 'income',     re: /(поступлен|поступление|зачислен)/i },
  { action: 'withdrawal', re: /снятие/i },
  { action: 'deposit',    re: /внесение/i }
];

Логика знаков:

// Проверка валидности суммы
if (amount === null || amount === 0) {
  throw new Error('Не удалось распарсить сумму из SMS');
}

let signed_amount = amount;

// Расходные операции (минус)
if (['payment', 'transfer', 'writeoff', 'withdrawal'].includes(action)) {
  signed_amount = -Math.abs(amount);
}

// Приходные операции (плюс)
if (['salary', 'income', 'deposit', 'reversal'].includes(action)) {
  signed_amount = Math.abs(amount);
}

Результат: объект с полями action, amount, signed_amount, currency, balance, received_at, raw_sms, sms_text, sms_sender, raw_json.

3.3. PostgreSQL: create_from_sms (INSERT)

  • Operation: Insert
  • Table: transactions
  • Поля: все базовые из parse_from_sms (без LLM-полей)
  • Options: Always Output Data = ON

3.4. Set: Edit Fields

Урезает объект перед LLM (data minimization): sms_text, action, amount, currency, balance.

3.5. HTTP Request: post_to_llm

  • Метод: POST
  • URL: http://<LM_STUDIO_IP>:1234/v1/chat/completions
  • Модель: Qwen2.5-7B-Instruct

System prompt (фрагмент):

ДОПУСТИМЫЕ КАТЕГОРИИ:
"Продукты", "Авто", "Здоровье", "Арчи", "ЖКХ", "Дом", "Проезд", "Одежда", 
"Химия", "Косметика", "Инвестиции", "Развлечения", "Общепит", "Штрафы", 
"Налоги", "Подписки", "Перевод", "Наличные", "Подарки", "Спорт", "Поступления"

КАТЕГОРИЗАЦИЯ ПО ТИПУ ОПЕРАЦИИ:
- Для снятия наличных категория ВСЕГДА "Наличные".
- Для внесения наличных категория ВСЕГДА "Поступления".
- Для зарплаты или поступлений категория ВСЕГДА "Поступления".
- Для возвратов/отмен категория ВСЕГДА "Поступления".
- Для переводов определяй категорию по контрагенту.

Параметры: temperature: 0.2, max_tokens: 256

3.6. Code: parse_from_llm

let content = $input.first().json.choices.message.content;
content = content.replace(/```json\n?/gi, '').replace(/```\n?/g, '').trim();
const llmResponse = JSON.parse(content);
const originalData = $('create_from_sms').first().json;

return {
  ...originalData,
  counterparty: llmResponse.counterparty || originalData.counterparty || 'Продукты',
  category: llmResponse.category || originalData.category || 'Продукты'
};

3.7. PostgreSQL: update_llm_fields (UPDATE)

  • Table: transactions
  • Where: id = {{ $json.id }}
  • Columns: counterparty, category, llm_processed_at = NOW(), human_verified = FALSE
  • Options: Always Output Data = ON

3.8. Telegram Send Message: edit_or_confirm

  • Chat ID: {{ $json.chatId }}
  • Text: карточка транзакции с суммой, контрагентом, категорией, датой
  • Inline Keyboard:
    • ✅ Подтвердить → confirm_{{ $json.id }}
    • ✏️ Изменить категорию → edit_category_{{ $json.id }}
    • ✏️ Изменить контрагента → edit_counterparty_{{ $json.id }}

4. Ветка 2. Telegram Trigger и разветвление

4.1. Telegram Trigger: answer_trigger

  • Updates: callback_query, message
  • Все события (кнопки и текст) приходят в один триггер

4.2. IF: split_event_type

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

5. Ветка 3. Подтверждение транзакции

5.1. Code: parse_from_callback

Извлекает из callback: action, field, transactionId, chatId, messageId, isEditedCard, counterparty, category.

5.2. IF: confirm_or_edit

  • TRUE → подтверждение
  • FALSE → редактирование

5.3. IF: check_if_edited

  • TRUE → подтверждение после редактирования
  • FALSE → первое подтверждение

Первое подтверждение

  • PostgreSQL SELECT: row_select_by_id
  • PostgreSQL UPDATE: update_after_confirm
    • Columns: human_verified = true, human_verified_at = {{ $now }}, chat_id = {{ $('answer_trigger').first().json.callback_query.message.chat.id }}

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

  • Code: parse_card_text
  • PostgreSQL UPDATE: update_edited_transaction
    • Columns: counterparty, category, human_verified = true, chat_id = {{ $('answer_trigger').first().json.callback_query.message.chat.id }}
  • Code: prepare_confirmation_data
const messageData = $('parse_card_text').first().json;
const parseData = $input.first().json;

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

5.4. Merge: merge_confirm_paths

  • Mode: Append
  • Объединяет обе ветки подтверждения

5.5. Telegram Answer Callback Query: popup_confirm

5.6. Telegram Send Message: confirmation_message

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

6.1. PostgreSQL Execute Query: empty_session

Удаляет старую сессию и пробрасывает данные.

6.2. IF: which_field_to_edit

  • TRUE → категория
  • FALSE → контрагент

6.3. PostgreSQL INSERT: add_session_category / add_session_counterparty

Создает сессию в user_sessions.

6.4. Telegram Send Message: category_edit / counterparty_edit

Запрашивает ввод нового значения.

7. Ветка 5. Обработка текстового ответа

7.1. PostgreSQL SELECT: get_session

7.2. Code: merge_user_and_session

7.3. Code: parse_text_input

7.4. PostgreSQL SELECT: get_current_transaction

7.5. Code: merge_changes

7.6. Telegram Send Message: show_updated_card

Отображает обновленную карточку с надписью "не сохранено".

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

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

Колонки: дата, действие, контрагент, сумма, категория, примечание

8.2. Узел Google Sheets (после merge_confirm_paths)

  • Operation: Append Row
  • Credentials: Google Service Account

Mapping:

дата: {{ $json.received_at }}
действие: {{ $json.raw_json.action === 'salary' ? 'Зарплата' : $json.raw_json.action === 'reversal' ? 'Возврат' : $json.raw_json.action === 'payment' ? 'Оплата' : $json.raw_json.action === 'transfer' ? 'Перевод' : $json.raw_json.action === 'writeoff' ? 'Списание' : $json.raw_json.action === 'income' ? 'Поступление' : $json.raw_json.action === 'withdrawal' ? 'Снятие' : $json.raw_json.action === 'deposit' ? 'Внесение' : $json.raw_json.action }}
контрагент: {{ $json.counterparty }}
сумма: {{ String($json.raw_json.signed_amount).replace('.', ',') }}
категория: {{ $json.category }}
примечание:

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

9.1. Архитектурные улучшения (Приоритет 1)

  • Переход на stateless редактирование: Хранить состояние в callback_data кнопок вместо таблицы user_sessions, убрать 7 узлов workflow, снизить нагрузку на БД на 70%
  • Inline-клавиатура для категорий: Заменить текстовый ввод на кнопки с персонализированными популярными категориями (топ-3 за 30 дней) + полный список
  • Кэширование популярных категорий: Использовать таблицу user_category_stats для быстрого получения часто используемых категорий
  • Fallback для длинных callback_data: MD5-хэш + временное хранилище для значений >64 байт

9.2. LLM и категоризация (Приоритет 2)

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

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

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

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

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

Версия документа: 5.0 Дата: 2026-01-11 Статус: Добавлены типы операций (withdrawal/deposit), логика знаков, категория "Поступления", поле chat_id, экспорт в Google Sheets с колонкой "действие". Следующий этап — переход на stateless редактирование и inline-клавиатуры для категорий.

Reference in New Issue View Git Blame Copy Permalink