feat: complete analytics category filtering

Reviewed-on: #34

CHANGELOG.md

@@ -1,5 +1,59 @@
 # Changelog
 
+## [Frontend 0.11.0 / Backend 0.10.0 / Shared 0.5.0] - 2026-08-21
+
+### Added
+
+- Added category filtering to analytics summary and category breakdown, including savings-account interest handling in the filtered results.
+
+## [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
+
+- Creating a category rule now applies it atomically to all matching unconfirmed transactions and returns the affected count.
+
 ## [Backend 0.7.4] - 2026-08-20
 
 ### Fixed

backend/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@family-budget/backend",
-  "version": "0.7.4",
+  "version": "0.10.0",
   "private": true,
   "scripts": {
     "dev": "tsx watch src/app.ts",
@@ -9,6 +9,9 @@
     "migrate": "tsx src/db/migrate.ts",
     "migrate:prod": "node dist/db/migrate.js",
     "test:analytics": "tsx src/services/analyticsSemantics.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/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.ts

@@ -8,7 +8,7 @@ const router = Router();
 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 +18,7 @@ router.get(
       from: from as string,
       to: to as string,
       accountId: accountId ? Number(accountId) : undefined,
+      categoryId: categoryId ? Number(categoryId) : undefined,
       onlyConfirmed: onlyConfirmed === 'true',
     });
     res.json(result);
@@ -27,7 +28,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 +38,7 @@ router.get(
       from: from as string,
       to: to as string,
       accountId: accountId ? Number(accountId) : undefined,
+      categoryId: categoryId ? Number(categoryId) : undefined,
       onlyConfirmed: onlyConfirmed === 'true',
     });
     res.json(result);

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

@@ -32,6 +32,8 @@ async function testQueries(): Promise<void> {
     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 });
     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);

backend/src/services/analytics.ts

@@ -14,6 +14,7 @@ interface BaseParams {
   from: string;
   to: string;
   accountId?: number;
+  categoryId?: number;
   onlyConfirmed?: boolean;
 }
 
@@ -61,6 +62,11 @@ function buildBaseConditions(
     values.push(params.accountId);
     idx++;
   }
+  if (params.categoryId != null) {
+    conditions.push(`t.category_id = $${idx}`);
+    values.push(params.categoryId);
+    idx++;
+  }
   if (params.onlyConfirmed) {
     conditions.push('t.is_category_confirmed = TRUE');
   }

backend/src/services/categoryRules.ts

@@ -55,22 +55,38 @@ export async function getRules(params: GetCategoryRulesParams): Promise<Category
 
 export async function createRule(
   body: CreateCategoryRuleRequest,
-): Promise<CategoryRule> {
+): Promise<CategoryRule & { applied: number }> {
   const pattern = body.pattern.trim();
   const matchType = body.matchType ?? 'contains';
   const priority = body.priority ?? 100;
   const requiresConfirmation = body.requiresConfirmation ?? false;
 
-  const { rows } = await pool.query(
-    `INSERT INTO category_rules (pattern, match_type, category_id, priority, requires_confirmation)
-     VALUES ($1, $2, $3, $4, $5)
-     RETURNING *`,
-    [pattern, matchType, body.categoryId, priority, requiresConfirmation],
-  );
-
-  const catResult = await pool.query('SELECT name FROM categories WHERE id = $1', [body.categoryId]);
-  rows[0].category_name = catResult.rows[0].name;
-  return toRule(rows[0]);
+  const client = await pool.connect();
+  try {
+    await client.query('BEGIN');
+    const { rows } = await client.query(
+      `INSERT INTO category_rules (pattern, match_type, category_id, priority, requires_confirmation)
+       VALUES ($1, $2, $3, $4, $5) RETURNING *`,
+      [pattern, matchType, body.categoryId, priority, requiresConfirmation],
+    );
+    const match = matchType === 'starts_with'
+      ? `t.description ILIKE $1 || '%'`
+      : `t.description ILIKE '%' || $1 || '%'`;
+    const applied = await client.query(
+      `UPDATE transactions t SET category_id = $2, is_category_confirmed = $3, updated_at = NOW()
+       WHERE (t.category_id IS NULL OR t.is_category_confirmed = FALSE) AND ${match}`,
+      [pattern, body.categoryId, !requiresConfirmation],
+    );
+    const catResult = await client.query('SELECT name FROM categories WHERE id = $1', [body.categoryId]);
+    rows[0].category_name = catResult.rows[0].name;
+    await client.query('COMMIT');
+    return { ...toRule(rows[0]), applied: applied.rowCount ?? 0 };
+  } catch (error) {
+    await client.query('ROLLBACK');
+    throw error;
+  } finally {
+    client.release();
+  }
 }
 
 export async function updateRule(

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

frontend/package.json

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

frontend/src/api/rules.ts

@@ -1,5 +1,6 @@
 import type {
   CategoryRule,
+  CreateCategoryRuleResponse,
   GetCategoryRulesParams,
   CreateCategoryRuleRequest,
   UpdateCategoryRuleRequest,
@@ -24,8 +25,8 @@ export async function getCategoryRules(
 
 export async function createCategoryRule(
   data: CreateCategoryRuleRequest,
-): Promise<CategoryRule> {
-  return api.post<CategoryRule>('/api/category-rules', data);
+): Promise<CreateCategoryRuleResponse> {
+  return api.post<CreateCategoryRuleResponse>('/api/category-rules', data);
 }
 
 export async function updateCategoryRule(

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();
@@ -112,6 +118,13 @@ export function AnalyticsPage() {
               ))}
             </select>
           </div>
+          <div className="field">
+            <label className="field__label">Категория</label>
+            <select value={categoryId} onChange={(e) => setCategoryId(e.target.value)}>
+              <option value="">Все категории</option>
+              {categories.map((c) => <option key={c.id} value={c.id}>{c.name}</option>)}
+            </select>
+          </div>
           <div className="field field--checkbox">
             <label className="field__label field__label--checkbox">
               <input

shared/package.json

@@ -1,6 +1,6 @@
 {
   "name": "@family-budget/shared",
-  "version": "0.3.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;
 }
 

shared/src/types/category-rule.ts

@@ -26,6 +26,10 @@ export interface CreateCategoryRuleRequest {
   requiresConfirmation?: boolean;
 }
 
+export interface CreateCategoryRuleResponse extends CategoryRule {
+  applied: number;
+}
+
 export interface UpdateCategoryRuleRequest {
   pattern?: string;
   categoryId?: number;

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

@@ -24,6 +24,7 @@ export type {
 
 export type {
   CategoryRule,
+  CreateCategoryRuleResponse,
   GetCategoryRulesParams,
   CreateCategoryRuleRequest,
   UpdateCategoryRuleRequest,
@@ -38,6 +39,10 @@ export type {
   StatementTransaction,
   ImportStatementResponse,
   Import,
+  PortfolioFile,
+  PortfolioPosition,
+  PortfolioTrade,
+  ImportPortfolioResponse,
 } from './import';
 
 export type {