fix: preserve overlapping import deduplication

Reviewed-on: #40

CHANGELOG.md

@@ -1,5 +1,71 @@
 # Changelog
 
+## [Backend 0.10.3] - 2026-08-24
+
+### Fixed
+
+- Treat VTB savings-account deposits and closures as internal transfers when importing PDF-to-JSON statements.
+
+## [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
+
+- Invalid non-string trade identifiers now return validation errors instead of server errors.
+
+## [Backend 0.8.2] - 2026-08-20
+
+### Added
+
+- Added a database integration test for repeated portfolio imports and trade deduplication.
+
+## [Backend 0.8.1] - 2026-08-20
+
+### Fixed
+
+- Hardened portfolio import validation, preserved decimal precision, added fallback trade keys, and covered validation with a runnable test.
+
+## [Backend 0.8.0 / Shared 0.4.0] - 2026-08-20
+
+### Added
+
+- Added portfolio JSON import for broker reports with idempotent trades, position snapshots, and separate portfolio tables.
+
 ## [Backend 0.7.5 / Shared 0.3.1] - 2026-08-20
 
 ### Added

backend/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@family-budget/backend",
-  "version": "0.7.5",
+  "version": "0.10.4",
   "private": true,
   "scripts": {
     "dev": "tsx watch src/app.ts",
@@ -9,8 +9,13 @@
     "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:import:direction": "tsx src/services/import.test.ts",
     "test:llm": "tsx src/scripts/testLlm.ts"
   },
   "dependencies": {

backend/src/app.ts

@@ -19,6 +19,7 @@ import accountsRouter from './routes/accounts';
 import categoriesRouter from './routes/categories';
 import categoryRulesRouter from './routes/categoryRules';
 import analyticsRouter from './routes/analytics';
+import portfolioRouter from './routes/portfolio';
 
 const app = express();
 app.set('trust proxy', 1);
@@ -43,6 +44,7 @@ app.use('/api/accounts', accountsRouter);
 app.use('/api/categories', categoriesRouter);
 app.use('/api/category-rules', categoryRulesRouter);
 app.use('/api/analytics', analyticsRouter);
+app.use('/api/import/portfolio', portfolioRouter);
 
 app.use(
   (

backend/src/db/migrate.ts

@@ -238,6 +238,64 @@ const migrations: { name: string; sql: string }[] = [
         CHECK (status IN ('active', 'closed'));
     `,
   },
+  {
+    name: '008_portfolio_tables',
+    sql: `
+      CREATE TABLE IF NOT EXISTS portfolio_reports (
+        id BIGSERIAL PRIMARY KEY,
+        account_id BIGINT NOT NULL REFERENCES accounts(id),
+        source_hash TEXT NOT NULL,
+        report_period_from DATE NOT NULL,
+        report_period_to DATE NOT NULL,
+        reported_at TIMESTAMPTZ,
+        imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+        UNIQUE (account_id, source_hash)
+      );
+      CREATE TABLE IF NOT EXISTS portfolio_positions (
+        id BIGSERIAL PRIMARY KEY,
+        report_id BIGINT NOT NULL REFERENCES portfolio_reports(id) ON DELETE CASCADE,
+        instrument TEXT NOT NULL,
+        isin TEXT,
+        quantity NUMERIC NOT NULL,
+        price NUMERIC,
+        valuation NUMERIC
+      );
+      CREATE TABLE IF NOT EXISTS portfolio_trades (
+        id BIGSERIAL PRIMARY KEY,
+        account_id BIGINT NOT NULL REFERENCES accounts(id),
+        source_id TEXT NOT NULL,
+        operation_id UUID NOT NULL,
+        instrument TEXT NOT NULL,
+        isin TEXT,
+        concluded_at TIMESTAMPTZ NOT NULL,
+        side TEXT NOT NULL,
+        quantity NUMERIC NOT NULL,
+        price_currency TEXT,
+        price NUMERIC,
+        settlement_currency TEXT,
+        settlement_amount NUMERIC,
+        nkd NUMERIC,
+        settlement_commission NUMERIC,
+        trade_commission NUMERIC,
+        order_id TEXT,
+        trade_id TEXT,
+        venue TEXT,
+        comment TEXT,
+        UNIQUE (account_id, source_id)
+      );
+      CREATE INDEX IF NOT EXISTS ix_portfolio_trades_account_date
+        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/routes/portfolio.ts

@@ -0,0 +1,22 @@
+import { Router } from 'express';
+import { asyncHandler } from '../utils';
+import * as portfolioService from '../services/portfolio';
+
+const router = Router();
+
+router.post(
+  '/',
+  asyncHandler(async (req, res) => {
+    try {
+      res.status(201).json(await portfolioService.importPortfolio(req.body));
+    } catch (error) {
+      if (error instanceof Error && /Invalid|Duplicate|must be|required|numeric/.test(error.message)) {
+        res.status(422).json({ error: 'VALIDATION_ERROR', message: error.message });
+        return;
+      }
+      throw error;
+    }
+  }),
+);
+
+export default router;

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,25 +2,63 @@ 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 })),
 });
 
+const duplicateStatement = {
+  schemaVersion: '1.0', bank: 'TEST',
+  statement: { accountNumber: 'fingerprint-test', currency: 'RUB', openingBalance: 0, closingBalance: 200, exportedAt: '2026-08-20T12:00:00+03:00' },
+  transactions: Array.from({ length: 2 }, () => ({ operationAt: '2026-08-20T00:00:00+03:00', amountSigned: 100, commission: 0, description: 'Пополнение' })),
+};
+const overlapStatement = {
+  ...duplicateStatement,
+  statement: { ...duplicateStatement.statement, accountNumber: 'overlap-test' },
+};
+const singleStatement = {
+  ...overlapStatement,
+  statement: { ...overlapStatement.statement, closingBalance: 100 },
+  transactions: overlapStatement.transactions.slice(0, 1),
+};
+
 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' });
+    const duplicateResult = await importStatement(duplicateStatement);
+    if ('status' in duplicateResult) throw new Error(duplicateResult.message);
+    assert.deepEqual(duplicateResult, {
+      accountId: duplicateResult.accountId,
+      isNewAccount: true,
+      accountNumberMasked: 'fingerp******test',
+      imported: 2,
+      duplicatesSkipped: 0,
+      totalInFile: 2,
+    });
+    await importStatement(singleStatement);
+    const overlapResult = await importStatement(overlapStatement);
+    if ('status' in overlapResult) throw new Error(overlapResult.message);
+    assert.deepEqual(overlapResult, {
+      accountId: overlapResult.accountId,
+      isNewAccount: false,
+      accountNumberMasked: 'overla******test',
+      imported: 1,
+      duplicatesSkipped: 1,
+      totalInFile: 2,
+    });
     console.log('import metadata SQL: OK');
   } finally {
-    await pool.query('DELETE FROM transactions WHERE account_id IN (SELECT id FROM accounts WHERE bank = \'TEST\' AND account_number = \'metadata-test\')');
-    await pool.query("DELETE FROM imports WHERE account_id IN (SELECT id FROM accounts WHERE bank = 'TEST' AND account_number = 'metadata-test')");
-    await pool.query("DELETE FROM accounts WHERE bank = 'TEST' AND account_number = 'metadata-test'");
+    await pool.query("DELETE FROM transactions WHERE account_id IN (SELECT id FROM accounts WHERE bank = 'TEST' AND account_number IN ('metadata-test', 'fingerprint-test', 'overlap-test'))");
+    await pool.query("DELETE FROM imports WHERE account_id IN (SELECT id FROM accounts WHERE bank = 'TEST' AND account_number IN ('metadata-test', 'fingerprint-test', 'overlap-test'))");
+    await pool.query("DELETE FROM accounts WHERE bank = 'TEST' AND account_number IN ('metadata-test', 'fingerprint-test', 'overlap-test')");
     await pool.end();
   }
 }

backend/src/services/import.test.ts

@@ -0,0 +1,12 @@
+import assert from 'node:assert/strict';
+import { computeFingerprint, determineDirection } from './import';
+
+assert.equal(determineDirection(1, 'Перечисление средств на счет N 123 со счета N 456'), 'transfer');
+assert.equal(determineDirection(1, 'Перечисление средств на вклад N 123'), 'transfer');
+assert.equal(determineDirection(-1, 'Перечисление суммы вклада при закрытии'), 'transfer');
+assert.equal(determineDirection(-1, 'Оплата покупки'), 'expense');
+assert.equal(determineDirection(1, 'Выплата процентов'), 'income');
+const duplicateTransaction = { operationAt: '2026-08-20T00:00:00+03:00', amountSigned: 100, commission: 0, description: 'Пополнение' };
+assert.notEqual(computeFingerprint('fingerprint-test', duplicateTransaction, 0), computeFingerprint('fingerprint-test', duplicateTransaction, 1));
+
+console.log('import direction: OK');

backend/src/services/import.ts

@@ -6,14 +6,18 @@ import type { StatementFile, ImportStatementResponse } from '@family-budget/shar
 const TRANSFER_PHRASES = [
   'перевод между своими счетами',
   'перевод средств на счет',
+  'перечисление средств на счет',
+  'перечисление средств на вклад',
+  'перечисление суммы вклада при закрытии',
   'внутри втб',
 ];
 const CASHBACK_KEYWORD = 'зачисление';
 const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
 
-function computeFingerprint(
+export function computeFingerprint(
   accountNumber: string,
   tx: { operationAt: string; amountSigned: number; commission: number; description: string; sourceId?: string },
+  sourcePosition?: number,
 ): string {
   if (tx.sourceId) {
     const raw = [accountNumber, tx.sourceId.trim()].join('|');
@@ -26,12 +30,13 @@ function computeFingerprint(
     String(tx.amountSigned),
     String(tx.commission),
     tx.description.trim(),
+    ...(sourcePosition === undefined ? [] : [String(sourcePosition)]),
   ].join('|');
   const hash = crypto.createHash('sha256').update(raw, 'utf-8').digest('hex');
   return `sha256:${hash}`;
 }
 
-function determineDirection(amountSigned: number, description: string): string {
+export function determineDirection(amountSigned: number, description: string): string {
   const lower = description.toLowerCase();
   for (const phrase of TRANSFER_PHRASES) {
     if (lower.includes(phrase)) return 'transfer';
@@ -136,7 +141,7 @@ function validateSemantics(data: StatementFile): ValidationError | null {
   const operationIds = new Set<string>();
   for (let i = 0; i < data.transactions.length; i++) {
     const fp = computeFingerprint(data.statement.accountNumber, data.transactions[i]);
-    if (fps.has(fp)) {
+    if (data.transactions[i].sourceId && fps.has(fp)) {
       return { status: 422, error: 'VALIDATION_ERROR', message: `Duplicate fingerprint found within file at transaction index ${i}` };
     }
     fps.add(fp);
@@ -218,9 +223,15 @@ export async function importStatement(
 
     // Insert transactions
     const insertedIds: number[] = [];
+    const fallbackFingerprintOccurrences = new Map<string, number>();
 
-    for (const tx of data.transactions) {
-      const fp = computeFingerprint(data.statement.accountNumber, tx);
+    for (const [sourcePosition, tx] of data.transactions.entries()) {
+      const fallbackFingerprint = computeFingerprint(data.statement.accountNumber, tx);
+      const occurrence = fallbackFingerprintOccurrences.get(fallbackFingerprint) ?? 0;
+      fallbackFingerprintOccurrences.set(fallbackFingerprint, occurrence + 1);
+      const fp = !tx.sourceId && occurrence > 0
+        ? computeFingerprint(data.statement.accountNumber, tx, occurrence)
+        : fallbackFingerprint;
       const isCashbackCommissionImport =
         tx.amountSigned === 0 &&
         tx.commission > 0 &&
@@ -235,11 +246,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/portfolio.integration.test.ts

@@ -0,0 +1,29 @@
+import assert from 'node:assert/strict';
+import { pool } from '../db/pool';
+import { importPortfolio } from './portfolio';
+
+const payload = {
+  schemaVersion: 'broker-portfolio-1.0', bank: 'PORTFOLIO_TEST', accountNumber: 'portfolio-test',
+  reportPeriod: { from: '2026-08-01', to: '2026-08-20' }, reportedAt: '2026-08-20T00:00:00+03:00',
+  positions: [{ instrument: 'Test Bond', isin: 'RU0000000001', quantity: '1.000', price: '100.123456', valuation: '100.123456' }],
+  trades: [{ instrument: 'Test Bond', isin: 'RU0000000001', concludedAt: '2026-08-10T10:00:00+03:00', side: 'Покупка', quantity: '1.000', settlementAmount: '100.123456' }],
+};
+
+async function run(): Promise<void> {
+  try {
+    const first = await importPortfolio(payload);
+    const second = await importPortfolio(payload);
+    assert.equal(first.importedTrades, 1);
+    assert.equal(second.duplicateTrades, 1);
+    const rows = await pool.query('SELECT COUNT(*)::int AS count FROM portfolio_trades WHERE account_id = $1', [first.accountId]);
+    assert.equal(rows.rows[0].count, 1);
+    console.log('portfolio import SQL: OK');
+  } finally {
+    await pool.query("DELETE FROM portfolio_trades WHERE account_id IN (SELECT id FROM accounts WHERE bank = 'PORTFOLIO_TEST' AND account_number = 'portfolio-test')");
+    await pool.query("DELETE FROM portfolio_reports WHERE account_id IN (SELECT id FROM accounts WHERE bank = 'PORTFOLIO_TEST' AND account_number = 'portfolio-test')");
+    await pool.query("DELETE FROM accounts WHERE bank = 'PORTFOLIO_TEST' AND account_number = 'portfolio-test'");
+    await pool.end();
+  }
+}
+
+run();

backend/src/services/portfolio.test.ts

@@ -0,0 +1,13 @@
+import assert from 'node:assert/strict';
+import { deriveTradeSourceId, validatePortfolio } from './portfolio';
+
+const trade = {
+  instrument: 'Bond', isin: 'RU0000000001', concludedAt: '2026-08-20T10:00:00+03:00',
+  side: 'Покупка', quantity: '1.000', settlementAmount: '1234.50',
+};
+
+assert.equal(deriveTradeSourceId(trade), 'RU0000000001|2026-08-20T10:00:00+03:00|Покупка|1.000|1234.50');
+assert.throws(() => validatePortfolio(null), /object/);
+assert.throws(() => validatePortfolio({ schemaVersion: 'broker-portfolio-1.0', bank: 'B', accountNumber: 'A', reportPeriod: { from: '2026-08-20', to: '2026-08-20' }, reportedAt: null, positions: [], trades: [{ ...trade, sourceId: 42 }] }), /sourceId/);
+assert.throws(() => validatePortfolio({ ...{ schemaVersion: 'broker-portfolio-1.0', bank: 'B', accountNumber: 'A', reportPeriod: { from: '2026-08-21', to: '2026-08-20' }, reportedAt: null, positions: [], trades: [] } }), /period/);
+console.log('portfolio validation: OK');

backend/src/services/portfolio.ts

@@ -0,0 +1,103 @@
+import crypto from 'crypto';
+import { pool } from '../db/pool';
+import type { ImportPortfolioResponse, PortfolioFile, PortfolioTrade } from '@family-budget/shared';
+
+const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
+
+function decimalValue(value: unknown, field: string, required = false): string | null {
+  if (value == null || value === '') {
+    if (required) throw new Error(`${field} is required`);
+    return null;
+  }
+  const result = String(value).replace(/\s/g, '').replace(',', '.');
+  if (!/^-?(?:\d+\.?\d*|\.\d+)$/.test(result)) throw new Error(`${field} must be numeric`);
+  return result;
+}
+
+export function deriveTradeSourceId(trade: PortfolioTrade): string {
+  if (trade.sourceId?.trim()) return trade.sourceId.trim();
+  return [trade.isin || trade.instrument, trade.concludedAt, trade.side, decimalValue(trade.quantity, 'quantity', true), decimalValue(trade.settlementAmount, 'settlementAmount') || ''].join('|');
+}
+
+export function validatePortfolio(body: unknown): asserts body is PortfolioFile {
+  if (!body || typeof body !== 'object') throw new Error('Portfolio file must be an object');
+  const data = body as PortfolioFile;
+  if (data.schemaVersion !== 'broker-portfolio-1.0' || typeof data.bank !== 'string' || !data.bank.trim() || typeof data.accountNumber !== 'string' || !data.accountNumber.trim()) throw new Error('Invalid portfolio file');
+  if (!data.reportPeriod?.from || !data.reportPeriod?.to || Number.isNaN(Date.parse(data.reportPeriod.from)) || Number.isNaN(Date.parse(data.reportPeriod.to)) || data.reportPeriod.from > data.reportPeriod.to) throw new Error('Invalid report period');
+  if (data.reportedAt !== null && (typeof data.reportedAt !== 'string' || Number.isNaN(Date.parse(data.reportedAt)))) throw new Error('Invalid reportedAt');
+  if (!Array.isArray(data.positions) || !Array.isArray(data.trades)) throw new Error('positions and trades must be arrays');
+  const sourceIds = new Set<string>();
+  for (const trade of data.trades) {
+    if (!trade || typeof trade.instrument !== 'string' || !trade.instrument.trim() || typeof trade.concludedAt !== 'string' || Number.isNaN(Date.parse(trade.concludedAt)) || typeof trade.side !== 'string' || !trade.side.trim()) throw new Error('Invalid trade');
+    if (trade.sourceId !== undefined && typeof trade.sourceId !== 'string') throw new Error('sourceId must be a string');
+    if (trade.operationId !== undefined && typeof trade.operationId !== 'string') throw new Error('operationId must be a string');
+    const sourceId = deriveTradeSourceId(trade);
+    if (sourceIds.has(sourceId)) throw new Error(`Duplicate sourceId: ${sourceId}`);
+    sourceIds.add(sourceId);
+    if (trade.operationId && !UUID_RE.test(trade.operationId)) throw new Error(`Invalid operationId: ${trade.sourceId}`);
+    decimalValue(trade.quantity, 'quantity', true);
+  }
+  for (const position of data.positions) {
+    if (!position || typeof position.instrument !== 'string' || !position.instrument.trim()) throw new Error('Invalid position');
+    decimalValue(position.quantity, 'position.quantity', true);
+    decimalValue(position.price, 'position.price');
+    decimalValue(position.valuation, 'position.valuation');
+  }
+}
+
+export async function importPortfolio(body: unknown, db: Pick<typeof pool, 'connect'> = pool): Promise<ImportPortfolioResponse> {
+  validatePortfolio(body);
+  const data = body;
+  const sourceHash = crypto.createHash('sha256').update(JSON.stringify(data)).digest('hex');
+  const client = await db.connect();
+  try {
+    await client.query('BEGIN');
+    const accountResult = await client.query(
+      `INSERT INTO accounts (bank, account_number, currency, account_type)
+       VALUES ($1, $2, 'RUB', 'brokerage')
+       ON CONFLICT (bank, account_number) DO UPDATE SET account_type = COALESCE(accounts.account_type, 'brokerage')
+       RETURNING id`,
+      [data.bank, data.accountNumber],
+    );
+    const accountId = Number(accountResult.rows[0].id);
+    const reportResult = await client.query(
+      `INSERT INTO portfolio_reports (account_id, source_hash, report_period_from, report_period_to, reported_at)
+       VALUES ($1, $2, $3, $4, $5)
+       ON CONFLICT (account_id, source_hash) DO NOTHING
+       RETURNING id`,
+      [accountId, sourceHash, data.reportPeriod.from, data.reportPeriod.to, data.reportedAt],
+    );
+    if (reportResult.rows.length === 0) {
+      const existing = await client.query('SELECT id FROM portfolio_reports WHERE account_id = $1 AND source_hash = $2', [accountId, sourceHash]);
+      await client.query('COMMIT');
+      return { accountId, reportId: Number(existing.rows[0].id), importedTrades: 0, duplicateTrades: data.trades.length, positions: 0 };
+    }
+    const reportId = Number(reportResult.rows[0].id);
+    for (const position of data.positions) {
+      await client.query(
+        `INSERT INTO portfolio_positions (report_id, instrument, isin, quantity, price, valuation)
+         VALUES ($1, $2, $3, $4, $5, $6)`,
+        [reportId, position.instrument, position.isin ?? null, decimalValue(position.quantity, 'position.quantity', true), decimalValue(position.price, 'position.price'), decimalValue(position.valuation, 'position.valuation')],
+      );
+    }
+    let importedTrades = 0;
+    for (const trade of data.trades) {
+      const sourceId = deriveTradeSourceId(trade);
+      const result = await client.query(
+        `INSERT INTO portfolio_trades
+         (account_id, source_id, operation_id, instrument, isin, concluded_at, side, quantity, price_currency, price, settlement_currency, settlement_amount, nkd, settlement_commission, trade_commission, order_id, trade_id, venue, comment)
+         VALUES ($1, $2, COALESCE($3::uuid, gen_random_uuid()), $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
+         ON CONFLICT (account_id, source_id) DO NOTHING`,
+        [accountId, sourceId, trade.operationId ?? null, trade.instrument, trade.isin ?? null, trade.concludedAt, trade.side, decimalValue(trade.quantity, 'quantity', true), trade.priceCurrency ?? null, decimalValue(trade.price, 'price'), trade.settlementCurrency ?? null, decimalValue(trade.settlementAmount, 'settlementAmount'), decimalValue(trade.nkd, 'nkd'), decimalValue(trade.settlementCommission, 'settlementCommission'), decimalValue(trade.tradeCommission, 'tradeCommission'), trade.orderId ?? null, trade.tradeId ?? null, trade.venue ?? null, trade.comment ?? null],
+      );
+      importedTrades += result.rowCount ?? 0;
+    }
+    await client.query('COMMIT');
+    return { accountId, reportId, importedTrades, duplicateTrades: data.trades.length - importedTrades, positions: data.positions.length };
+  } catch (error) {
+    await client.query('ROLLBACK');
+    throw error;
+  } finally {
+    client.release();
+  }
+}

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],
   );

docs/backlog/api_import.md

@@ -91,7 +91,7 @@
 
 - `statement.currency` соответствует допустимому коду валюты (MVP: `"RUB"`).
 - `operationAt` у всех транзакций — валидная дата (парсится без ошибок).
-- Отсутствуют дубликаты fingerprint внутри одного файла.
+- Повторяющиеся `sourceId` внутри одного файла отклоняются; одинаковые операции без `sourceId` различаются по позиции в массиве `transactions`.
 
 Ответ при ошибке:
 
@@ -120,10 +120,11 @@
 Для каждой транзакции вычисляется SHA-256 от полей, соединённых разделителем `|`:
 
 ```text
-accountNumber|operationAt|amountSigned|commission|normalizedDescription
+accountNumber|operationAt|amountSigned|commission|normalizedDescription[|sourcePosition]
 ```
 
 - `normalizedDescription` — `description` после `trim`.
+- `sourcePosition` — порядковый номер повторяющейся операции в массиве `transactions`; добавляется, только если одинаковые операции без `sourceId` повторяются в одном файле.
 - Суммы подставляются в том виде, в котором пришли в JSON (числовое представление).
 - Разделитель `|` исключает коллизии при склейке полей разной длины.
 

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.3.1",
+  "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;
 }
 

shared/src/types/import.ts

@@ -46,3 +46,50 @@ export interface ImportStatementResponse {
   duplicatesSkipped: number;
   totalInFile: number;
 }
+
+export interface PortfolioFile {
+  schemaVersion: 'broker-portfolio-1.0';
+  bank: string;
+  accountNumber: string;
+  reportPeriod: { from: string; to: string };
+  reportedAt: string | null;
+  positions: PortfolioPosition[];
+  trades: PortfolioTrade[];
+}
+
+export interface PortfolioPosition {
+  instrument: string;
+  isin?: string | null;
+  quantity: string | number;
+  price?: string | number | null;
+  valuation?: string | number | null;
+}
+
+export interface PortfolioTrade {
+  sourceId?: string;
+  operationId?: string;
+  instrument: string;
+  isin?: string | null;
+  concludedAt: string;
+  side: string;
+  quantity: string | number;
+  priceCurrency?: string | null;
+  price?: string | number | null;
+  settlementCurrency?: string | null;
+  settlementAmount?: string | number | null;
+  nkd?: string | number | null;
+  settlementCommission?: string | number | null;
+  tradeCommission?: string | number | null;
+  orderId?: string | null;
+  tradeId?: string | null;
+  venue?: string | null;
+  comment?: string | null;
+}
+
+export interface ImportPortfolioResponse {
+  accountId: number;
+  reportId: number;
+  importedTrades: number;
+  duplicateTrades: number;
+  positions: number;
+}

shared/src/types/index.ts

@@ -39,6 +39,10 @@ export type {
   StatementTransaction,
   ImportStatementResponse,
   Import,
+  PortfolioFile,
+  PortfolioPosition,
+  PortfolioTrade,
+  ImportPortfolioResponse,
 } from './import';
 
 export type {