docs: update analytics validation changelog

Reviewed-on: #38

CHANGELOG.md

@@ -1,5 +1,41 @@
 # Changelog
 
+## [Backend 0.10.2] - 2026-08-24
+
+### Fixed
+
+- Analytics endpoints now reject invalid account and category filter IDs with a validation error.
+
+## [Frontend 0.11.1] - 2026-08-21
+
+### Fixed
+
+- Preserve the explicit «Без категории» analytics filter value when requesting data.
+
+## [Frontend 0.11.0 / Backend 0.10.1 / Shared 0.5.0] - 2026-08-21
+
+### Fixed
+
+- Corrected analytics period boundaries for partial week/month ranges and added a usable «Без категории» filter with SQL coverage.
+
+## [Backend 0.9.2] - 2026-08-20
+
+### Added
+
+- Extended import integration coverage to verify source positions for same-date operations.
+
+## [Backend 0.9.1] - 2026-08-20
+
+### Added
+
+- Added runnable coverage for date and amount sorting tie-breakers.
+
+## [Backend 0.9.0] - 2026-08-20
+
+### Added
+
+- Preserved source-array transaction positions and used them as stable tie-breakers for same-date history sorting.
+
 ## [Backend 0.8.3] - 2026-08-20
 
 ### Fixed

backend/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@family-budget/backend",
-  "version": "0.8.3",
+  "version": "0.10.2",
   "private": true,
   "scripts": {
     "dev": "tsx watch src/app.ts",
@@ -9,8 +9,10 @@
     "migrate": "tsx src/db/migrate.ts",
     "migrate:prod": "node dist/db/migrate.js",
     "test:analytics": "tsx src/services/analyticsSemantics.test.ts",
+    "test:analytics:query": "tsx src/routes/analytics.test.ts",
     "test:portfolio": "tsx src/services/portfolio.test.ts",
     "test:portfolio:db": "NODE_ENV=test tsx src/services/portfolio.integration.test.ts",
+    "test:transactions": "tsx src/services/transactions.test.ts",
     "test:analytics:db": "NODE_ENV=test tsx src/services/analytics.integration.test.ts",
     "test:import:db": "NODE_ENV=test tsx src/services/import.integration.test.ts",
     "test:llm": "tsx src/scripts/testLlm.ts"

backend/src/db/migrate.ts

@@ -287,6 +287,15 @@ const migrations: { name: string; sql: string }[] = [
         ON portfolio_trades(account_id, concluded_at DESC);
     `,
   },
+  {
+    name: '009_transaction_source_position',
+    sql: `
+      ALTER TABLE transactions ADD COLUMN IF NOT EXISTS source_position BIGINT NOT NULL DEFAULT 0;
+      UPDATE transactions SET source_position = id WHERE source_position = 0;
+      CREATE INDEX IF NOT EXISTS ix_transactions_date_position
+        ON transactions(operation_at, source_position, id);
+    `,
+  },
 ];
 
 export async function runMigrations(): Promise<void> {

backend/src/routes/analytics.test.ts

@@ -0,0 +1,12 @@
+import assert from 'node:assert/strict';
+import { parseOptionalId } from './analytics';
+
+assert.equal(parseOptionalId(undefined, 0), undefined);
+assert.equal(parseOptionalId('0', 0), 0);
+assert.equal(parseOptionalId('1', 1), 1);
+assert.equal(parseOptionalId('0', 1), null);
+assert.equal(parseOptionalId('abc', 0), null);
+assert.equal(parseOptionalId('1.5', 0), null);
+assert.equal(parseOptionalId(['1'], 0), null);
+
+console.log('analytics query validation: OK');

backend/src/routes/analytics.ts

@@ -5,10 +5,34 @@ import type { Granularity } from '@family-budget/shared';
 
 const router = Router();
 
+export function parseOptionalId(value: unknown, minimum: number): number | null | undefined {
+  if (value === undefined) return undefined;
+  if (typeof value !== 'string' || !/^\d+$/.test(value)) return null;
+
+  const id = Number(value);
+  return Number.isSafeInteger(id) && id >= minimum ? id : null;
+}
+
+router.use((req, res, next) => {
+  const accountId = parseOptionalId(req.query.accountId, 1);
+  if (accountId === null) {
+    res.status(400).json({ error: 'BAD_REQUEST', message: 'accountId must be a positive integer' });
+    return;
+  }
+
+  const categoryId = parseOptionalId(req.query.categoryId, 0);
+  if (categoryId === null) {
+    res.status(400).json({ error: 'BAD_REQUEST', message: 'categoryId must be a non-negative integer' });
+    return;
+  }
+
+  next();
+});
+
 router.get(
   '/summary',
   asyncHandler(async (req, res) => {
-    const { from, to, accountId, onlyConfirmed } = req.query;
+    const { from, to, accountId, categoryId, onlyConfirmed } = req.query;
     if (!from || !to) {
       res.status(400).json({ error: 'BAD_REQUEST', message: 'from and to are required' });
       return;
@@ -18,6 +42,7 @@ router.get(
       from: from as string,
       to: to as string,
       accountId: accountId ? Number(accountId) : undefined,
+      categoryId: categoryId !== undefined ? Number(categoryId) : undefined,
       onlyConfirmed: onlyConfirmed === 'true',
     });
     res.json(result);
@@ -27,7 +52,7 @@ router.get(
 router.get(
   '/by-category',
   asyncHandler(async (req, res) => {
-    const { from, to, accountId, onlyConfirmed } = req.query;
+    const { from, to, accountId, categoryId, onlyConfirmed } = req.query;
     if (!from || !to) {
       res.status(400).json({ error: 'BAD_REQUEST', message: 'from and to are required' });
       return;
@@ -37,6 +62,7 @@ router.get(
       from: from as string,
       to: to as string,
       accountId: accountId ? Number(accountId) : undefined,
+      categoryId: categoryId !== undefined ? Number(categoryId) : undefined,
       onlyConfirmed: onlyConfirmed === 'true',
     });
     res.json(result);
@@ -62,7 +88,7 @@ router.get(
       from: from as string,
       to: to as string,
       accountId: accountId ? Number(accountId) : undefined,
-      categoryId: categoryId ? Number(categoryId) : undefined,
+      categoryId: categoryId !== undefined ? Number(categoryId) : undefined,
       onlyConfirmed: onlyConfirmed === 'true',
       granularity: granularity as Granularity,
     });

backend/src/services/analytics.integration.test.ts

@@ -28,15 +28,33 @@ async function testQueries(): Promise<void> {
     );
     await insert(otherAccountId, '2026-07-01T12:00:00+03:00', -99_000, categoryId.expense, 'analytics-test-6');
     await insert(accountId, '2026-08-01T12:00:00+03:00', -7_000, categoryId.expense, 'analytics-test-7');
+    await client.query(
+      "INSERT INTO transactions (account_id, operation_at, amount_signed, commission, description, direction, fingerprint, category_id, is_category_confirmed) VALUES ($1, '2026-07-07T12:00:00+03:00', -2_500, 0, 'uncategorized', 'expense', 'analytics-test-uncategorized', NULL, FALSE)",
+      [accountId],
+    );
 
     const params = { from: '2026-07-01', to: '2026-07-31', accountId, onlyConfirmed: true };
     const summary = await getSummary(params, client);
     assert.deepEqual({ expense: summary.totalExpense, income: summary.totalIncome, net: summary.net, transferOut: summary.transferOutflow, interest: summary.interestIncome }, { expense: 6_000, income: 15_000, net: 9_000, transferOut: 3_000, interest: 1_500 });
+    const categorySummary = await getSummary({ ...params, categoryId: categoryId.expense }, client);
+    assert.deepEqual({ expense: categorySummary.totalExpense, income: categorySummary.totalIncome }, { expense: 6_000, income: 0 });
+    const uncategorized = await getSummary({ ...params, categoryId: 0, onlyConfirmed: false }, client);
+    assert.equal(uncategorized.totalExpense, 2_500);
+    assert.equal((await getSummary({ ...params, categoryId: 0, onlyConfirmed: true }, client)).totalExpense, 0);
+    assert.deepEqual((await getByCategory({ ...params, categoryId: 0, onlyConfirmed: false }, client)).map((item) => item.amount), [2_500]);
+    assert.deepEqual((await getByCategory({ ...params, categoryId: 0, onlyConfirmed: true }, client)), []);
     assert.equal((await getSummary({ ...params, from: '2026-08-01', to: '2026-08-31' }, client)).totalExpense, 7_000);
     assert.deepEqual((await getByCategory(params, client)).map((item) => item.amount), [6_000]);
     const timeseries = await getTimeseries({ ...params, granularity: 'month' }, client);
     assert.equal(timeseries[0].expenseAmount, 6_000);
     assert.equal(timeseries[0].incomeAmount, 15_000);
+    const partial = await getTimeseries({ ...params, from: '2026-07-02', to: '2026-07-03', granularity: 'month' }, client);
+    assert.equal(partial[0].expenseAmount, 0);
+    assert.equal(partial[0].incomeAmount, 20_000);
+    const uncategorizedSeries = await getTimeseries({ ...params, categoryId: 0, onlyConfirmed: false, granularity: 'month' }, client);
+    assert.equal(uncategorizedSeries[0].expenseAmount, 2_500);
+    const confirmedUncategorizedSeries = await getTimeseries({ ...params, categoryId: 0, onlyConfirmed: true, granularity: 'month' }, client);
+    assert.equal(confirmedUncategorizedSeries[0].expenseAmount, 0);
   } finally {
     await client.query('ROLLBACK');
     client.release();

backend/src/services/analytics.ts

@@ -14,6 +14,7 @@ interface BaseParams {
   from: string;
   to: string;
   accountId?: number;
+  categoryId?: number;
   onlyConfirmed?: boolean;
 }
 
@@ -61,6 +62,13 @@ function buildBaseConditions(
     values.push(params.accountId);
     idx++;
   }
+  if (params.categoryId != null) {
+    conditions.push(params.categoryId === 0 ? 't.category_id IS NULL' : `t.category_id = $${idx}`);
+    if (params.categoryId !== 0) {
+      values.push(params.categoryId);
+      idx++;
+    }
+  }
   if (params.onlyConfirmed) {
     conditions.push('t.is_category_confirmed = TRUE');
   }
@@ -196,6 +204,8 @@ export async function getTimeseries(
   }
 
   const txConditions: string[] = [
+    't.operation_at::date >= $1::date',
+    't.operation_at::date <= $2::date',
     't.operation_at::date >= p.period_start',
     't.operation_at::date <= p.period_end',
   ];
@@ -207,8 +217,11 @@ export async function getTimeseries(
     values.push(params.accountId);
   }
   if (params.categoryId != null) {
-    txConditions.push(`t.category_id = $${idx++}`);
-    values.push(params.categoryId);
+    if (params.categoryId === 0) txConditions.push('t.category_id IS NULL');
+    else {
+      txConditions.push(`t.category_id = $${idx++}`);
+      values.push(params.categoryId);
+    }
   }
   if (params.onlyConfirmed) {
     txConditions.push('t.is_category_confirmed = TRUE');

backend/src/services/import.integration.test.ts

@@ -2,18 +2,20 @@ import assert from 'node:assert/strict';
 import { pool } from '../db/pool';
 import { importStatement } from './import';
 
-const makeStatement = (sourceId: string) => ({
+const makeStatement = (sourceIds: string[]) => ({
   schemaVersion: '1.0', bank: 'TEST',
   statement: { accountNumber: 'metadata-test', currency: 'RUB', openingBalance: 0, closingBalance: 100, exportedAt: '2026-08-20T12:00:00+03:00' },
-  transactions: [{ operationAt: '2026-08-20T10:00:00+03:00', amountSigned: 100, commission: 0, description: 'Пополнение', sourceId }],
+  transactions: sourceIds.map((sourceId) => ({ operationAt: '2026-08-20T10:00:00+03:00', amountSigned: 100, commission: 0, description: 'Пополнение', sourceId })),
 });
 
 async function run(): Promise<void> {
   try {
-    await importStatement(makeStatement('first'));
+    await importStatement(makeStatement(['first', 'first-second']));
+    const positions = await pool.query("SELECT source_position FROM transactions t JOIN accounts a ON a.id = t.account_id WHERE a.bank = 'TEST' AND a.account_number = 'metadata-test' ORDER BY source_position");
+    assert.deepEqual(positions.rows.map((row) => Number(row.source_position)), [0, 1]);
     const account = await pool.query("UPDATE accounts SET account_type = 'savings', status = 'closed' WHERE bank = 'TEST' AND account_number = 'metadata-test' RETURNING id");
     assert.equal(account.rows.length, 1);
-    await importStatement(makeStatement('second'));
+    await importStatement(makeStatement(['second']));
     const result = await pool.query('SELECT account_type, status FROM accounts WHERE id = $1', [account.rows[0].id]);
     assert.deepEqual(result.rows[0], { account_type: 'savings', status: 'closed' });
     console.log('import metadata SQL: OK');

backend/src/services/import.ts

@@ -219,7 +219,7 @@ export async function importStatement(
     // Insert transactions
     const insertedIds: number[] = [];
 
-    for (const tx of data.transactions) {
+    for (const [sourcePosition, tx] of data.transactions.entries()) {
       const fp = computeFingerprint(data.statement.accountNumber, tx);
       const isCashbackCommissionImport =
         tx.amountSigned === 0 &&
@@ -235,11 +235,11 @@ export async function importStatement(
 
       const result = await client.query(
         `INSERT INTO transactions
-           (account_id, operation_id, operation_at, amount_signed, commission, description, direction, fingerprint, category_id, is_category_confirmed, import_id)
-         VALUES ($1, COALESCE($2::uuid, gen_random_uuid()), $3, $4, $5, $6, $7, $8, $9, $10, $11)
+           (account_id, operation_id, operation_at, amount_signed, commission, description, direction, fingerprint, category_id, is_category_confirmed, import_id, source_position)
+         VALUES ($1, COALESCE($2::uuid, gen_random_uuid()), $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
          ON CONFLICT DO NOTHING
          RETURNING id`,
-        [accountId, tx.operationId ?? null, tx.operationAt, tx.amountSigned, tx.commission, tx.description, dir, fp, categoryId, isCategoryConfirmed, importId],
+        [accountId, tx.operationId ?? null, tx.operationAt, tx.amountSigned, tx.commission, tx.description, dir, fp, categoryId, isCategoryConfirmed, importId, sourcePosition],
       );
 
       if (result.rows.length > 0) {

backend/src/services/transactions.test.ts

@@ -0,0 +1,13 @@
+import assert from 'node:assert/strict';
+import { transactionOrderBy } from './transactions';
+
+assert.equal(
+  transactionOrderBy('date', 'desc'),
+  't.operation_at DESC, t.source_position ASC, t.id ASC',
+);
+assert.equal(
+  transactionOrderBy('date', 'asc'),
+  't.operation_at ASC, t.source_position DESC, t.id DESC',
+);
+assert.match(transactionOrderBy('amount', 'desc'), /t\.amount_signed DESC.*t\.operation_at DESC.*t\.source_position ASC.*t\.id DESC/);
+console.log('transaction ordering: OK');

backend/src/services/transactions.ts

@@ -7,13 +7,18 @@ import type {
   UpdateTransactionRequest,
 } from '@family-budget/shared';
 
+export function transactionOrderBy(sortBy: 'date' | 'amount', sortOrder: 'asc' | 'desc'): string {
+  const direction = sortOrder === 'asc' ? 'ASC' : 'DESC';
+  if (sortBy === 'amount') return `t.amount_signed ${direction}, t.operation_at DESC, t.source_position ASC, t.id DESC`;
+  return `t.operation_at ${direction}, t.source_position ${direction === 'ASC' ? 'DESC' : 'ASC'}, t.id ${direction === 'ASC' ? 'DESC' : 'ASC'}`;
+}
+
 export async function getTransactions(
   params: GetTransactionsParams,
 ): Promise<PaginatedResponse<Transaction>> {
   const page = params.page ?? 1;
   const pageSize = [10, 50, 100].includes(params.pageSize ?? 50) ? (params.pageSize ?? 50) : 50;
-  const sortBy = params.sortBy === 'amount' ? 't.amount_signed' : 't.operation_at';
-  const sortOrder = params.sortOrder === 'asc' ? 'ASC' : 'DESC';
+  const orderBy = transactionOrderBy(params.sortBy === 'amount' ? 'amount' : 'date', params.sortOrder === 'asc' ? 'asc' : 'desc');
 
   const conditions: string[] = [];
   const values: unknown[] = [];
@@ -84,7 +89,7 @@ export async function getTransactions(
      JOIN accounts a ON a.id = t.account_id
      LEFT JOIN categories c ON c.id = t.category_id
      ${where}
-     ORDER BY ${sortBy} ${sortOrder}
+     ORDER BY ${orderBy}
      LIMIT $${idx++} OFFSET $${idx++}`,
     [...values, pageSize, offset],
   );

frontend/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@family-budget/frontend",
-  "version": "0.10.2",
+  "version": "0.11.1",
   "private": true,
   "type": "module",
   "scripts": {

frontend/src/pages/AnalyticsPage.tsx

@@ -1,12 +1,14 @@
 import { useState, useEffect, useCallback } from 'react';
 import type {
   Account,
+  Category,
   AnalyticsSummaryResponse,
   ByCategoryItem,
   TimeseriesItem,
   Granularity,
 } from '@family-budget/shared';
 import { getAccounts } from '../api/accounts';
+import { getCategories } from '../api/categories';
 import { getSummary, getByCategory, getTimeseries } from '../api/analytics';
 import {
   PeriodSelector,
@@ -29,8 +31,10 @@ function getDefaultPeriod(): PeriodState {
 export function AnalyticsPage() {
   const [period, setPeriod] = useState<PeriodState>(getDefaultPeriod);
   const [accountId, setAccountId] = useState<string>('');
+  const [categoryId, setCategoryId] = useState<string>('');
   const [onlyConfirmed, setOnlyConfirmed] = useState(false);
   const [accounts, setAccounts] = useState<Account[]>([]);
+  const [categories, setCategories] = useState<Category[]>([]);
   const [summary, setSummary] = useState<AnalyticsSummaryResponse | null>(
     null,
   );
@@ -40,6 +44,7 @@ export function AnalyticsPage() {
 
   useEffect(() => {
     getAccounts().then(setAccounts).catch(() => {});
+    getCategories({ isActive: true }).then(setCategories).catch(() => {});
   }, []);
 
   const fetchAll = useCallback(async () => {
@@ -63,6 +68,7 @@ export function AnalyticsPage() {
       from: period.from,
       to: period.to,
       ...(accountId ? { accountId: Number(accountId) } : {}),
+      ...(categoryId !== '' ? { categoryId: Number(categoryId) } : {}),
       ...(onlyConfirmed ? { onlyConfirmed: true } : {}),
     };
 
@@ -80,7 +86,7 @@ export function AnalyticsPage() {
     } finally {
       setLoading(false);
     }
-  }, [period, accountId, onlyConfirmed]);
+  }, [period, accountId, categoryId, onlyConfirmed]);
 
   useEffect(() => {
     fetchAll();
@@ -122,6 +128,14 @@ export function AnalyticsPage() {
               Только подтверждённые
             </label>
           </div>
+          <div className="field">
+            <label className="field__label">Категория</label>
+            <select value={categoryId} onChange={(e) => setCategoryId(e.target.value)}>
+              <option value="">Все категории</option>
+              <option value="0">Без категории</option>
+              {categories.map((c) => <option key={c.id} value={c.id}>{c.name}</option>)}
+            </select>
+          </div>
         </div>
       </div>
 

shared/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@family-budget/shared",
-  "version": "0.4.0",
+  "version": "0.5.0",
   "private": true,
   "main": "dist/index.js",
   "types": "dist/index.d.ts",

shared/src/types/analytics.ts

@@ -4,6 +4,7 @@ export interface AnalyticsSummaryParams {
   from: string;
   to: string;
   accountId?: number;
+  categoryId?: number;
   onlyConfirmed?: boolean;
 }
 
@@ -31,6 +32,7 @@ export interface ByCategoryParams {
   from: string;
   to: string;
   accountId?: number;
+  categoryId?: number;
   onlyConfirmed?: boolean;
 }