# 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`

```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`.

### 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`, дата и пр.

### 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 вида:

```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, пример:

```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:

```javascript
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]);
```

- Определяет, редактировалась ли карточка:

```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`.

### 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:

```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:

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

#### 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:

```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`.

### 7.2. PostgreSQL SELECT: `get_session`

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

Возвращает:

```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`

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

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

Выход: `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`:

```javascript
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:

```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
**Статус:** Основной функционал реализован, финальная отладка цикла редактирования/подтверждения в процессе; интеграция с внешними отчётами и автоматизация запуска в планах.