feat: apply category rules on creation

Reviewed-on: #31

CHANGELOG.md

@@ -1,5 +1,11 @@
 # Changelog
 
+## [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.7.5",
   "private": true,
   "scripts": {
     "dev": "tsx watch src/app.ts",

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(

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(

shared/package.json

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

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/index.ts

@@ -24,6 +24,7 @@ export type {
 
 export type {
   CategoryRule,
+  CreateCategoryRuleResponse,
   GetCategoryRulesParams,
   CreateCategoryRuleRequest,
   UpdateCategoryRuleRequest,