539 lines
19 KiB
Markdown
539 lines
19 KiB
Markdown
# SMS → n8n → LLM → PostgreSQL → Telegram
|
||
|
||
Текущая архитектура workflow (07.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 и т.п. — планируется).[file:46]
|
||
|
||
Проект развернут на Synology NAS: PostgreSQL. n8n в Docker и LM Studio — на локальной машине.[file:46]
|
||
|
||
***
|
||
|
||
## 2. База данных
|
||
|
||
### 2.1. Таблица `transactions`
|
||
|
||
```sql
|
||
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`.[file:46]
|
||
|
||
### 2.2. Таблица `user_sessions`
|
||
|
||
Используется для хранения состояния редактирования в Telegram (какую транзакцию и какое поле сейчас меняет пользователь).
|
||
|
||
```sql
|
||
"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`, дата и пр.[file:46]
|
||
|
||
### 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`.[file:46]
|
||
|
||
### 3.3. PostgreSQL: `create_from_sms` (INSERT)
|
||
|
||
- Operation: **Insert**
|
||
- Table: `transactions`
|
||
- Поля: все базовые из `parse_from_sms` (без LLM‑полей).
|
||
- Options: **Always Output Data = ON** — чтобы сразу получить `id` созданной строки.[file:46]
|
||
|
||
### 3.4. Set: `Edit Fields`
|
||
|
||
- Урезает объект перед LLM (data minimization): оставляет только то, что нужно модели:
|
||
- `sms_text`, `action`, `amount`, `currency`, `balance`.[file:46]
|
||
|
||
### 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).[file:46]
|
||
|
||
### 3.6. Code: `parse_from_llm`
|
||
|
||
- Берёт `choices[^0].message.content` из ответа.
|
||
- Убирает возможные обёртки ```json /```.[file:46]
|
||
- Парсит JSON вида:
|
||
|
||
```json
|
||
{"counterparty": "...", "category": "...", "subcategory": "...", "confidence": 0.0}
|
||
```
|
||
|
||
- Мёрджит с исходными данными из `create_from_sms`.
|
||
- Гарантирует значения по умолчанию, если LLM что‑то не вернула:
|
||
- `counterparty`: `"Не определён"`
|
||
- `category`: `"Неизвестно"`
|
||
- `subcategory`: `"Требует уточнения"`.[file:46]
|
||
|
||
### 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`.[file:46]
|
||
|
||
### 3.8. Telegram Send Message: `edit_or_confirm`
|
||
|
||
- Resource: Message.
|
||
- Operation: Send Message.
|
||
- Chat ID: `{{ $json.chatId }}`
|
||
- Text:
|
||
|
||
```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 }}`.[file:46]
|
||
|
||
***
|
||
|
||
## 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`.[file:46]
|
||
|
||
Все события (клики по кнопкам и текстовые сообщения) приходят в этот один триггер.
|
||
|
||
### 4.2. IF: `split_event_type`
|
||
|
||
- Condition: `{{ $json.callback_query }}` **Is Empty**.
|
||
- TRUE → текстовое сообщение пользователя → ветка редактирования.
|
||
- FALSE → callback от кнопки → ветка подтверждения/редактирования.[file:46]
|
||
|
||
***
|
||
|
||
## 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`.[file:46]
|
||
- Разбирает callback:
|
||
|
||
```javascript
|
||
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]);
|
||
```
|
||
|
||
- Определяет, редактировалась ли карточка:
|
||
|
||
```javascript
|
||
const isEditedCard = messageText && messageText.includes('не сохранено');
|
||
```
|
||
|
||
- Парсит текущие значения из текста карточки:
|
||
|
||
```javascript
|
||
const counterpartyMatch = messageText.match(/Контрагент:\s*(.+?)(?:\n|$)/);
|
||
const categoryMatch = messageText.match(/Категория:\s*(.+?)(?:\n|$)/);
|
||
```
|
||
|
||
- Возвращает:
|
||
- `action`, `field`, `transactionId`, `chatId`, `messageId`,
|
||
- `messageText`, `isEditedCard`, `originalCallbackData`,
|
||
- `counterparty`, `category`.[file:46]
|
||
|
||
### 5.2. IF: `confirm_or_edit`
|
||
|
||
- Condition: `{{ $json.action }}` Equal `confirm`.
|
||
- TRUE → ветка подтверждения.
|
||
- FALSE → ветка редактирования.[file:46]
|
||
|
||
### 5.3. IF: `check_if_edited` (ветка подтверждения)
|
||
|
||
- Condition: `{{ $json.isEditedCard }}` Equal `true`.
|
||
- TRUE → подтверждение после редактирования.
|
||
- FALSE → первое подтверждение без правок.[file:46]
|
||
|
||
#### 5.3.1. FALSE (первое подтверждение)
|
||
|
||
- PostgreSQL SELECT: `row_select_by_id`
|
||
- Operation: Select
|
||
- Table: `transactions`
|
||
- Where: `id = {{ $json.transactionId }}`
|
||
- Always Output Data = ON.[file:46]
|
||
- 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 (на выходе есть строка транзакции).[file:46]
|
||
|
||
#### 5.3.2. TRUE (подтверждение после редактирования)
|
||
|
||
- Code: `parse_card_text`
|
||
- Парсит `counterparty` и `category` из текста карточки по regex.
|
||
- Возвращает: `transactionId`, `counterparty`, `category`, `chatId`, `messageId`.[file:46]
|
||
- 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 (строка транзакции).[file:46]
|
||
- Code: `prepare_confirmation_data`
|
||
|
||
```javascript
|
||
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` (после редактирования).[file:46]
|
||
|
||
Важно: обе ветки несут в себе данные транзакции (`signed_amount`, `currency`, `counterparty`, `category`, `chatId`, `messageId`) к моменту входа в `confirmation_message`.
|
||
|
||
### 5.5. Telegram Answer Callback Query: `popup_confirm`
|
||
|
||
- Resource: Callback.
|
||
- Operation: Answer Callback Query.
|
||
- Query ID: `{{ $json.callback_query.id }}`
|
||
- Text: `✅ Транзакция подтверждена и сохранена`.[file:46]
|
||
|
||
### 5.6. Telegram Send Message: `confirmation_message`
|
||
|
||
- Resource: Message.
|
||
- Operation: Send Message.
|
||
- Chat ID: `{{ $('answer_trigger').first().json.callback_query.message.chat.id }}`
|
||
- Text:
|
||
|
||
```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:
|
||
|
||
```sql
|
||
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 → редактируем контрагента.[file:46]
|
||
|
||
#### 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 }}`.[file:46]
|
||
|
||
#### 6.1.4. PostgreSQL INSERT: `add_session_counterparty`
|
||
|
||
- Аналогично, но `waitingFor = 'counterparty'`.[file:46]
|
||
|
||
### 6.2. Запрос на ввод значения
|
||
|
||
#### 6.2.1. Telegram Send Message: `category_edit`
|
||
|
||
- Chat ID: `{{ $json.chatId }}`
|
||
- Text:
|
||
|
||
```text
|
||
Введите новую категорию для транзакции #{{ $json.transactionId }}
|
||
|
||
ВАЖНО: Лимит 12 символов
|
||
```
|
||
|
||
#### 6.2.2. Telegram Send Message: `counterparty_edit`
|
||
|
||
- Chat ID: `{{ $json.chatId }}`
|
||
- Text:
|
||
|
||
```text
|
||
Введите нового контрагента для транзакции #{{ $json.transactionId }}
|
||
|
||
ВАЖНО: Лимит 12 символов
|
||
```
|
||
|
||
***
|
||
|
||
## 7. Ветка 5. Обработка текстового ответа пользователя
|
||
|
||
### 7.1. answer_trigger → split_event_type (TRUE)
|
||
|
||
Приходит `message` от пользователя (без `callback_query`).
|
||
TRUE‑ветка `split_event_type` ведёт в `get_session`.[file:46]
|
||
|
||
### 7.2. PostgreSQL SELECT: `get_session`
|
||
|
||
- Operation: Select
|
||
- Table: `user_sessions`
|
||
- Where: `chatId = {{ $json.message.chat.id }}`
|
||
- Always Output Data = ON.[file:46]
|
||
|
||
Возвращает:
|
||
|
||
```json
|
||
{
|
||
"chatId": "...",
|
||
"transactionId": "...",
|
||
"waitingFor": "category" | "counterparty",
|
||
"tempCounterparty": "...",
|
||
"tempCategory": "...",
|
||
"createdAt": "..."
|
||
}
|
||
```
|
||
|
||
### 7.3. Code: `merge_user_and_session`
|
||
|
||
```javascript
|
||
const sessionData = $input.first().json;
|
||
const userMessage = $('split_event_type').first().json;
|
||
|
||
return {
|
||
message: userMessage.message,
|
||
session: sessionData
|
||
};
|
||
```
|
||
|
||
### 7.4. Code: `parse_text_input`
|
||
|
||
```javascript
|
||
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.[file:46]
|
||
|
||
### 7.6. Code: `merge_changes`
|
||
|
||
```javascript
|
||
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:
|
||
|
||
```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 }}`.[file:46]
|
||
|
||
***
|
||
|
||
## 8. Планируемые доработки
|
||
|
||
### 8.1. Интеграция с Google Sheets (Приоритет 1)
|
||
|
||
- [ ] Создать таблицу семейного бюджета в Google Sheets.
|
||
- [ ] Настроить Google Service Account и подключить Google Sheets node в n8n.
|
||
- [ ] Добавить Google Sheets **после** `merge_confirm_paths`:
|
||
- записывать только подтверждённые транзакции;
|
||
- колонки: Дата | Контрагент | Сумма | Категория | Подкатегория.
|
||
- [ ] Настроить форматирование (цвета для доходов/расходов).[file:46]
|
||
|
||
### 8.2. Автоматизация запуска (Приоритет 2)
|
||
|
||
- [ ] Автозапуск LM Studio при старте системы (service/systemd).
|
||
- [ ] Docker `restart: always` для n8n и PostgreSQL.
|
||
- [ ] Health‑check LM Studio API и уведомления в Telegram при падении.[file:46]
|
||
|
||
### 8.3. Улучшения и UX (Приоритет 3)
|
||
|
||
- [ ] Удалить подкатегорию.
|
||
- [ ] Удалить confidence из промпта и на всех этапах Workflow.
|
||
- [ ] Скорректировать промпт.
|
||
- [ ] Обработка исторических данных (2025 год и далее) для накопления статистики.
|
||
- [ ] Fine‑tuning модели LLM на собственных размеченных данных.
|
||
- [ ] Кнопка «❌ Отклонить» для удаления/игнорирования ошибочных транзакций.
|
||
- [ ] Ограничения и валидация пользовательского ввода (длина, спецсимволы).
|
||
- [ ] Периодическая очистка старых записей в `user_sessions` (например, старше 1 часа).
|
||
- [ ] Добавление исходного текста SMS в карточку (с безопасным форматированием или экранированием).
|
||
- [ ] Аналитика по категориям, отчёты и экспорт (CSV/Excel/BI).[file:46]
|
||
|
||
***
|
||
|
||
**Версия документа:** 4.1
|
||
**Дата:** 2026‑01‑07
|
||
**Статус:** Архитектура workflow зафиксирована, дальнейшая работа — интеграция с отчётностью и улучшения UX.[file:46]
|