family_budget/docs/backlog/db.md

Модель БД (PostgreSQL)

Общие принципы

• Используется PostgreSQL, развёрнутый локально (например, на Synology).
• Основные сущности:
  • accounts — банковские счета пользователя;
  • categories — категории расходов, доходов и переводов;
  • transactions — движения средств по счетам;
  • category_rules — правила автоматической категоризации транзакций;
  • sessions — серверные сессии авторизации.
• Все суммы хранятся в минорных единицах (копейки) как BIGINT.
• Время хранится в TIMESTAMPTZ (временная зона сохраняется).

Таблица accounts

Предназначение: хранение информации о счетах, по которым загружаются выписки.

Структура:

• id BIGSERIAL PRIMARY KEY — внутренний идентификатор счёта в системе.
• bank TEXT NOT NULL — код/имя банка (например, "VTB").
• account_number TEXT NOT NULL — номер счёта в банке (как в выписке).
• currency TEXT NOT NULL — код валюты счёта (например, "RUB").
• alias TEXT — человекочитаемый алиас счёта (например, "Текущий", "Накопительный"). При создании счёта через импорт alias = NULL; пользователь задаёт его позже через SPA.

Ограничения и индексы:

• Уникальность комбинации (bank, account_number) — один и тот же счёт в банке не должен дублироваться.

Рекомендуемый DDL:

CREATE TABLE accounts (
  id             BIGSERIAL PRIMARY KEY,
  bank           TEXT NOT NULL,
  account_number TEXT NOT NULL,
  currency       TEXT NOT NULL,
  alias          TEXT
);

CREATE UNIQUE INDEX ux_accounts_bank_number
  ON accounts(bank, account_number);

Таблица categories

Предназначение: хранение категорий для классификации транзакций.

Структура:

• id BIGSERIAL PRIMARY KEY — идентификатор категории.
• name TEXT NOT NULL — отображаемое имя категории (например, "Продукты", "ЖКХ").
• type TEXT NOT NULL — тип категории:
  • "expense" — расходная категория;
  • "income" — доходная категория;
  • "transfer" — переводы между собственными счетами.
• is_active BOOLEAN NOT NULL DEFAULT TRUE — используется ли категория.

Ограничения:

• CHECK-ограничение: type IN ('expense', 'income', 'transfer').

Рекомендуемый DDL:

CREATE TABLE categories (
  id        BIGSERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  type      TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);

ALTER TABLE categories
  ADD CONSTRAINT chk_categories_type
  CHECK (type IN ('expense', 'income', 'transfer'));

Начальный набор из 23 категорий заполняется seed-миграцией (см. category.md).

Таблица transactions

Предназначение: хранение всех операций по счетам с привязкой к accounts и categories.

Структура:

• id BIGSERIAL PRIMARY KEY — внутренний идентификатор транзакции.
• account_id BIGINT NOT NULL REFERENCES accounts(id) — каждая транзакция жёстко привязана к одному счёту.
• operation_at TIMESTAMPTZ NOT NULL — дата и время операции.
• amount_signed BIGINT NOT NULL — сумма операции в копейках; знак отражает тип движения (приход/расход).
• commission BIGINT NOT NULL — комиссия по операции в копейках.
• description TEXT NOT NULL — описание операции из выписки.
• direction TEXT NOT NULL — направление движения:
  • "income" — приход;
  • "expense" — расход;
  • "transfer" — перевод между своими счетами / на другие свои счета.
• fingerprint TEXT NOT NULL — вычисляемый хэш для обеспечения идемпотентности импорта.
• category_id BIGINT REFERENCES categories(id) — ссылка на категорию; NULL для некатегоризированных транзакций.
• is_category_confirmed BOOLEAN NOT NULL DEFAULT FALSE — признак того, что категория подтверждена пользователем (явно или неявно через правило без requires_confirmation).
• comment TEXT — пользовательский комментарий к транзакции.
• created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — время создания записи в БД.
• updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — время последнего обновления записи.

Ограничения и индексы:

• Внешний ключ account_id ссылается на accounts(id).
• Внешний ключ category_id ссылается на categories(id).
• Уникальный индекс (account_id, fingerprint) обеспечивает идемпотентность: одна и та же операция не может быть загружена дважды.
• CHECK-ограничение: direction IN ('income', 'expense', 'transfer').

Рекомендуемый DDL:

CREATE TABLE transactions (
  id                     BIGSERIAL PRIMARY KEY,
  account_id             BIGINT NOT NULL REFERENCES accounts(id),
  operation_at           TIMESTAMPTZ NOT NULL,
  amount_signed          BIGINT NOT NULL,
  commission             BIGINT NOT NULL,
  description            TEXT NOT NULL,
  direction              TEXT NOT NULL,
  fingerprint            TEXT NOT NULL,
  category_id            BIGINT REFERENCES categories(id),
  is_category_confirmed  BOOLEAN NOT NULL DEFAULT FALSE,
  comment                TEXT,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX ux_transactions_account_fingerprint
  ON transactions(account_id, fingerprint);

ALTER TABLE transactions
  ADD CONSTRAINT chk_transactions_direction
  CHECK (direction IN ('income', 'expense', 'transfer'));

Таблица category_rules

Предназначение: хранение правил автоматической категоризации транзакций на основе текста описания.

Структура:

• id BIGSERIAL PRIMARY KEY — идентификатор правила.
• pattern TEXT NOT NULL — строка-шаблон, вводимая пользователем через SPA (в простом виде).
• match_type TEXT NOT NULL — тип сопоставления:
  • "contains" — простое вхождение подстроки;
  • "starts_with" — строка начинается с шаблона;
  • "regex" — регулярное выражение (формируется и/или проверяется в коде на основе пользовательского ввода).
  • MVP: при создании правила принимается только "contains". Расширение до "starts_with" | "regex" запланировано.
• category_id BIGINT NOT NULL REFERENCES categories(id) — ссылка на категорию.
• priority INT NOT NULL DEFAULT 0 — приоритет правила; чем выше число, тем раньше правило применяется при конфликте.
• requires_confirmation BOOLEAN NOT NULL DEFAULT FALSE — если TRUE, транзакции, категоризированные этим правилом, получают is_category_confirmed = FALSE и требуют ручного подтверждения пользователем. Используется для правил с неоднозначным соответствием (например, маркетплейсы: OZON, WILDBERRIES).
• is_active BOOLEAN NOT NULL DEFAULT TRUE — активно ли правило.
• created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — время создания правила.

Логика приоритета:

• При авто-категоризации для транзакции ищутся все правила, которые ей соответствуют.
• Если совпало несколько правил, выбирается правило с максимальным priority.
• Это позволяет задавать общие правила с низким приоритетом и более точные (например, по конкретным мерчантам) с высоким приоритетом.

Рекомендуемый DDL:

CREATE TABLE category_rules (
  id                     BIGSERIAL PRIMARY KEY,
  pattern                TEXT NOT NULL,
  match_type             TEXT NOT NULL,
  category_id            BIGINT NOT NULL REFERENCES categories(id),
  priority               INT NOT NULL DEFAULT 0,
  requires_confirmation  BOOLEAN NOT NULL DEFAULT FALSE,
  is_active              BOOLEAN NOT NULL DEFAULT TRUE,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Таблица sessions

Предназначение: хранение серверных сессий авторизации с поддержкой таймаута по бездействию.

Структура:

• id TEXT PRIMARY KEY — идентификатор сессии (UUID).
• created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — время создания сессии.
• last_activity_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — время последней активности; обновляется при каждом запросе с действительной сессией.
• is_active BOOLEAN NOT NULL DEFAULT TRUE — флаг активности; устанавливается в FALSE при логауте или истечении таймаута.

Рекомендуемый DDL:

CREATE TABLE sessions (
  id               TEXT PRIMARY KEY,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_activity_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  is_active        BOOLEAN NOT NULL DEFAULT TRUE
);

Таблица budgets (после MVP)

Не входит в MVP. Таблица запланирована для поддержки бюджетов/лимитов по категориям на будущих этапах. Подробности см. в analytics.md, секция 5.3.

Взаимосвязь JSON → БД при импорте

1. По полям bank и statement.accountNumber ищется или создаётся запись в accounts. Если счёт создан впервые, alias = NULL.
2. Для каждой транзакции из transactions:
   • суммы в JSON 1.0 уже в копейках — записываются как есть;
   • вычисляется fingerprint на основе комбинации полей (например, accountNumber + operationAt + amountSigned + commission + normalizedDescription);
   • определяется direction:
     • amountSigned > 0 → "income";
     • amountSigned < 0 → "expense";
     • "transfer" — определяется по фиксированным ключевым фразам банка в description (например, для ВТБ: "Перевод между своими счетами", "Внутри ВТБ" и т.п.). Если ключевые фразы не сработали — остаётся "income" / "expense" по знаку суммы;
   • выполняется попытка вставки в transactions;
   • при срабатывании уникального ограничения (account_id, fingerprint) запись считается дубликатом и пропускается;
   • при импорте is_category_confirmed всегда = FALSE, category_id = NULL.
3. Импорт атомарный: при ошибке валидации любой транзакции весь файл откатывается (возвращается 422).
4. Категория (category_id) заполняется на следующих этапах:
   • автокатегоризация по category_rules:
     • если у сработавшего правила requires_confirmation = FALSE → is_category_confirmed = TRUE;
     • если requires_confirmation = TRUE → is_category_confirmed = FALSE (требуется ручное подтверждение);
   • ручное редактирование в SPA.