経費カテゴリルール仕様

概要

経費の勘定科目は、取引先名や摘要のルールで先に決め、足りないときだけ AI に聞きます。このページは、そのルールを DB に持ち、修正から学習するための実装仕様です。

本文は英語のエンジニア向け仕様です。 プロダクトの使い方だけ知りたい方は、経費まわりの機能リファレンスから読んでください。

いまの状態

フロントにローカル分類ルール（ベンダー・キーワード）がある
AI が決められないと category: 18（OTHERS）が返る
DB に永続ルールはまだない

目指す状態

DB 駆動のルール管理と優先度
ユーザーが直した内容からの学習
ルール CRUD API と AI 分類との接続

技術メモ: 1. Database Schema

技術メモ: 1.1 expense_category_rules (Main Rules Table)

CREATE TABLE expense_category_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Ownership
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    -- NULL = system default rule (applies to all users)
    -- UUID = user-specific override rule

    -- Rule Type
    rule_type VARCHAR(20) NOT NULL CHECK (rule_type IN ('vendor', 'keyword', 'combined')),
    -- 'vendor': Match against vendor name
    -- 'keyword': Match against description/memo
    -- 'combined': Match both vendor AND keyword

    -- Matching Pattern
    match_pattern VARCHAR(500) NOT NULL,
    -- Case-insensitive substring match
    -- Examples: 'nexco', 'ガソリン', 'jr東日本'

    match_pattern_normalized VARCHAR(500) GENERATED ALWAYS AS (
        LOWER(REGEXP_REPLACE(match_pattern, '[\s\-_\.]', '', 'g'))
    ) STORED,
    -- Normalized version for efficient matching

    -- Secondary Pattern (for 'combined' rule_type)
    secondary_pattern VARCHAR(500),
    -- Only used when rule_type = 'combined'

    -- Category Mapping
    suggested_category INTEGER NOT NULL,
    -- ExpenseCategory enum value (0-26)

    -- Confidence & Priority
    base_confidence DECIMAL(3,2) NOT NULL DEFAULT 0.80,
    -- Base confidence score (0.50-0.98)
    -- Higher = more certain this match is correct

    priority INTEGER NOT NULL DEFAULT 50,
    -- Processing order (higher = checked first)
    -- Range: 1-100
    -- System defaults: 50
    -- User overrides: 70-90
    -- Exact matches: 80-100

    -- Rule Metadata
    rule_name VARCHAR(200),
    -- Human-readable name: "高速道路 → 旅費交通費"

    rule_description TEXT,
    -- Explanation for auditing

    -- Usage Statistics
    match_count INTEGER DEFAULT 0,
    last_matched_at TIMESTAMPTZ,

    -- Learning Feedback
    user_correction_count INTEGER DEFAULT 0,
    -- How many times users changed this suggestion

    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_system BOOLEAN NOT NULL DEFAULT false,
    -- is_system = true for seed data, cannot be deleted by users

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT valid_confidence CHECK (base_confidence >= 0.30 AND base_confidence <= 0.98),
    CONSTRAINT valid_priority CHECK (priority >= 1 AND priority <= 100),
    -- Upper bound tracks the highest ExpenseCategory id. Widened to 28 in
    -- 20260725160000 when 租税公課 (27) and 雑費 (28) were added; a new category
    -- must widen this constraint or every insert using it fails with 23514.
    CONSTRAINT valid_category CHECK (suggested_category >= 0 AND suggested_category <= 28)
);

-- Indexes for efficient matching
CREATE INDEX idx_category_rules_active ON expense_category_rules(is_active) WHERE is_active = true;
CREATE INDEX idx_category_rules_user ON expense_category_rules(user_id);
CREATE INDEX idx_category_rules_type ON expense_category_rules(rule_type);
CREATE INDEX idx_category_rules_pattern ON expense_category_rules(match_pattern_normalized);
CREATE INDEX idx_category_rules_priority ON expense_category_rules(priority DESC);

-- Unique constraint: prevent duplicate patterns per user
CREATE UNIQUE INDEX idx_category_rules_unique
ON expense_category_rules(COALESCE(user_id, '00000000-0000-0000-0000-000000000000'), rule_type, match_pattern_normalized)
WHERE is_active = true;

-- RLS Policies
ALTER TABLE expense_category_rules ENABLE ROW LEVEL SECURITY;

-- Users can read system rules + their own rules
CREATE POLICY "Users can read system and own rules"
ON expense_category_rules
FOR SELECT
USING (user_id IS NULL OR user_id = auth.uid());

-- Users can only manage their own rules
CREATE POLICY "Users can manage own rules"
ON expense_category_rules
FOR ALL
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid() AND is_system = false);

-- Admins can manage system rules (service role)
CREATE POLICY "Service role can manage system rules"
ON expense_category_rules
FOR ALL
USING (auth.jwt() ->> 'role' = 'service_role');

技術メモ: 1.2 expense_category_corrections (User Feedback/Learning)

CREATE TABLE expense_category_corrections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

    -- Original Data
    original_vendor VARCHAR(500),
    original_description TEXT,
    original_amount DECIMAL(15,2),

    -- AI/Rule Suggestion
    suggested_category INTEGER NOT NULL,
    suggestion_source VARCHAR(20) NOT NULL,
    -- 'ai', 'vendor_rule', 'keyword_rule', 'user_history'
    suggestion_confidence DECIMAL(3,2),
    matched_rule_id UUID REFERENCES expense_category_rules(id),

    -- User Correction
    corrected_category INTEGER NOT NULL,
    correction_reason TEXT,
    -- Optional: user can explain why they changed it

    -- Context
    session_id UUID REFERENCES expense_wizard_sessions(id),
    item_id UUID REFERENCES expense_wizard_items(id),

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT different_categories CHECK (suggested_category != corrected_category)
);

-- Indexes
CREATE INDEX idx_corrections_user ON expense_category_corrections(user_id);
CREATE INDEX idx_corrections_vendor ON expense_category_corrections(LOWER(original_vendor));
CREATE INDEX idx_corrections_category ON expense_category_corrections(corrected_category);
CREATE INDEX idx_corrections_created ON expense_category_corrections(created_at DESC);

-- RLS
ALTER TABLE expense_category_corrections ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own corrections"
ON expense_category_corrections
FOR ALL
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

技術メモ: 2. API Endpoints

技術メモ: 2.1 Rule Management

エンドポイント: GET /api/expense-wizard/category-rules

Get all active category rules (system + user's own).

Query Parameters:

rule_type: Filter by type ('vendor', 'keyword', 'combined')
category: Filter by suggested_category
search: Search in match_pattern

Response:

{
  "success": true,
  "data": {
    "rules": [
      {
        "id": "uuid-...",
        "ruleType": "vendor",
        "matchPattern": "nexco",
        "suggestedCategory": 3,
        "baseConfidence": 0.95,
        "priority": 90,
        "ruleName": "NEXCO → 旅費交通費",
        "isSystem": true,
        "matchCount": 1234
      }
    ],
    "total": 350,
    "systemCount": 300,
    "userCount": 50
  }
}

エンドポイント: POST /api/expense-wizard/category-rules

Create a new user-specific rule.

Request:

{
  "ruleType": "vendor",
  "matchPattern": "カインズホーム草津店",
  "suggestedCategory": 9,
  "baseConfidence": 0.9,
  "priority": 75,
  "ruleName": "カインズ草津店 → 消耗品費"
}

Response:

{
  "success": true,
  "data": {
    "rule": {
      "id": "uuid-...",
      "ruleType": "vendor",
      "matchPattern": "カインズホーム草津店",
      "suggestedCategory": 9,
      "baseConfidence": 0.9,
      "priority": 75,
      "isSystem": false,
      "createdAt": "2026-01-26T..."
    }
  }
}

エンドポイント: PATCH /api/expense-wizard/category-rules/:ruleId

Update a user's rule (cannot modify system rules).

エンドポイント: DELETE /api/expense-wizard/category-rules/:ruleId

Delete a user's rule (cannot delete system rules).

技術メモ: 2.2 Categorization API

エンドポイント: POST /api/expense-wizard/categorize

Get category suggestion for expense data.

Request:

{
  "vendor": "NEXCO中日本",
  "description": "高速道路料金 東京IC-名古屋IC",
  "amount": 8500,
  "date": "2026-01-20"
}

Response:

{
  "success": true,
  "data": {
    "suggestedCategory": 3,
    "categoryName": "旅費交通費",
    "confidence": 0.95,
    "source": "vendor_rule",
    "matchedPattern": "nexco",
    "matchedRuleId": "uuid-...",
    "alternatives": [
      { "category": 15, "categoryName": "燃料費", "confidence": 0.4 }
    ],
    "amountWarning": null
  }
}

Categorization Priority:

User's custom rules (priority 70-90)
System vendor rules (priority 50-90)
System keyword rules (priority 40-70)
AI categorization (fallback)
User's historical patterns (learning)

エンドポイント: POST /api/expense-wizard/categorize/batch

Batch categorization for multiple items.

Request:

{
  "items": [
    {
      "id": "item-1",
      "vendor": "NEXCO",
      "description": "高速代",
      "amount": 3000
    },
    { "id": "item-2", "vendor": "ENEOS", "description": "給油", "amount": 5000 }
  ]
}

Response:

{
  "success": true,
  "data": {
    "results": [
      { "itemId": "item-1", "suggestedCategory": 3, "confidence": 0.95 },
      { "itemId": "item-2", "suggestedCategory": 15, "confidence": 0.95 }
    ]
  }
}

技術メモ: 2.3 Learning/Feedback API

エンドポイント: POST /api/expense-wizard/category-corrections

Record user correction for learning.

Request:

{
  "originalVendor": "スターバックス",
  "originalDescription": "コーヒー",
  "originalAmount": 500,
  "suggestedCategory": 1,
  "suggestionSource": "vendor_rule",
  "correctedCategory": 20,
  "correctionReason": "来客用の購入なので福利厚生費",
  "sessionId": "uuid-...",
  "itemId": "uuid-..."
}

Backend Behavior:

Record the correction
Update matched_rule's user_correction_count
If correction count > threshold (e.g., 5), reduce rule confidence
Optionally create user-specific override rule

技術メモ: 3. Business Logic

技術メモ: 3.1 Category Matching Algorithm

interface CategorizationResult {
  suggestedCategory: number
  confidence: number
  source: "user_rule" | "vendor_rule" | "keyword_rule" | "ai" | "user_history"
  matchedRuleId: string | null
  alternatives: Array<{ category: number; confidence: number }>
}

async function categorizeExpense(
  userId: string,
  vendor: string,
  description: string,
  amount: number
): Promise<CategorizationResult> {
  // 1. Get all applicable rules (user's + system), sorted by priority DESC
  const rules = await getActiveRules(userId)

  // 2. Normalize input for matching
  const vendorNorm = normalizeString(vendor)
  const descNorm = normalizeString(description)

  // 3. Try matching rules in priority order
  for (const rule of rules) {
    let matched = false

    switch (rule.rule_type) {
      case "vendor":
        matched = vendorNorm.includes(rule.match_pattern_normalized)
        break
      case "keyword":
        matched = descNorm.includes(rule.match_pattern_normalized)
        break
      case "combined":
        matched =
          vendorNorm.includes(rule.match_pattern_normalized) &&
          descNorm.includes(normalizeString(rule.secondary_pattern))
        break
    }

    if (matched) {
      // Update match statistics
      await updateRuleMatchCount(rule.id)

      // Apply amount adjustments
      const adjustedConfidence = applyAmountAdjustments(
        rule.suggested_category,
        rule.base_confidence,
        amount
      )

      return {
        suggestedCategory: rule.suggested_category,
        confidence: adjustedConfidence,
        source: rule.user_id
          ? "user_rule"
          : rule.rule_type === "vendor"
            ? "vendor_rule"
            : "keyword_rule",
        matchedRuleId: rule.id,
        alternatives: getAlternatives(rule.suggested_category, amount),
      }
    }
  }

  // 4. No rule matched - try AI categorization
  const aiResult = await aiCategorize(vendor, description, amount)
  if (aiResult && aiResult.confidence > 0.6) {
    return {
      suggestedCategory: aiResult.category,
      confidence: aiResult.confidence,
      source: "ai",
      matchedRuleId: null,
      alternatives: aiResult.alternatives,
    }
  }

  // 5. Check user's historical patterns
  const historyResult = await getUserHistoricalCategory(userId, vendor)
  if (historyResult) {
    return {
      suggestedCategory: historyResult.category,
      confidence: 0.7,
      source: "user_history",
      matchedRuleId: null,
      alternatives: [],
    }
  }

  // 6. Default to OTHERS
  return {
    suggestedCategory: 18, // OTHERS
    confidence: 0.3,
    source: "ai",
    matchedRuleId: null,
    alternatives: [],
  }
}

技術メモ: 3.2 Amount-Based Adjustments

function applyAmountAdjustments(
  category: number,
  baseConfidence: number,
  amount: number
): number {
  let confidence = baseConfidence

  // Fixed asset threshold (Japan: ¥100,000)
  if (amount >= 300000) {
    // Very high value - might be depreciation
    if (category === 9 || category === 14) {
      // SUPPLIES or EQUIPMENT
      confidence -= 0.15
    }
  } else if (amount >= 100000) {
    // Mid-range - might need consideration
    if (category === 9) {
      // SUPPLIES
      confidence -= 0.05
    }
  }

  // Very small amounts
  if (amount < 1000) {
    if (category === 0 || category === 19) {
      // ENTERTAINMENT types
      confidence -= 0.1
    }
  }

  return Math.max(0.3, Math.min(0.98, confidence))
}

技術メモ: 4. Migration from Frontend Rules

技術メモ: 4.1 Seed Data Generation

The frontend use-auto-categorize.ts contains comprehensive rules that should be migrated to the database as system defaults.

Migration Script:

-- Example: Insert vendor rules from frontend
INSERT INTO expense_category_rules (
  user_id, rule_type, match_pattern, suggested_category,
  base_confidence, priority, rule_name, is_active, is_system
) VALUES
-- Transportation
(NULL, 'vendor', 'jr', 3, 0.95, 90, 'JR → 旅費交通費', true, true),
(NULL, 'vendor', 'nexco', 3, 0.95, 90, 'NEXCO → 旅費交通費', true, true),
(NULL, 'vendor', 'pasmo', 3, 0.95, 85, 'PASMO → 旅費交通費', true, true),
(NULL, 'vendor', 'suica', 3, 0.95, 85, 'Suica → 旅費交通費', true, true),
-- ... (generate from VENDOR_CATEGORY_MAP)

-- Fuel
(NULL, 'vendor', 'eneos', 15, 0.95, 90, 'ENEOS → 燃料費', true, true),
(NULL, 'vendor', '出光', 15, 0.95, 90, '出光 → 燃料費', true, true),
-- ... etc

-- Keywords
(NULL, 'keyword', '新幹線', 3, 0.95, 80, '新幹線 → 旅費交通費', true, true),
(NULL, 'keyword', 'ガソリン', 15, 0.95, 85, 'ガソリン → 燃料費', true, true);
-- ... (generate from KEYWORD_CATEGORY_MAP)

技術メモ: 4.2 Migration Command

# Generate seed SQL from frontend rules
npm run generate:category-rules-seed

# Apply migration
supabase db push

# Verify
supabase db execute "SELECT COUNT(*) FROM expense_category_rules WHERE is_system = true"

技術メモ: 5. Integration Guide

技術メモ: 5.1 Frontend Integration

Update use-expense-wizard.ts to use backend categorization:

const categorizeWithBackend = async (
  item: WizardItem
): Promise<CategoryResult> => {
  try {
    // Try backend first
    const response = await apiClient.post("/expense-wizard/categorize", {
      vendor: item.extractedVendor,
      description: item.extractedDescription,
      amount: item.extractedAmount,
      date: item.extractedDate,
    })

    return {
      suggestedCategory: response.data.suggestedCategory,
      confidence: response.data.confidence,
      alternatives: response.data.alternatives,
    }
  } catch (error) {
    // Fallback to local categorization
    console.warn("Backend categorization failed, using local:", error)
    return localCategorize(item)
  }
}

技術メモ: 5.2 Recording Corrections

When user changes category:

const handleCategoryChange = async (
  itemId: string,
  originalCategory: number,
  newCategory: number,
  item: WizardItem
) => {
  // Update local state
  updateItemCategory(itemId, newCategory)

  // Record correction for learning
  if (originalCategory !== newCategory) {
    try {
      await apiClient.post("/expense-wizard/category-corrections", {
        originalVendor: item.extractedVendor,
        originalDescription: item.extractedDescription,
        originalAmount: item.extractedAmount,
        suggestedCategory: originalCategory,
        suggestionSource: item.categorySource || "ai",
        correctedCategory: newCategory,
        sessionId: sessionId,
        itemId: itemId,
      })
    } catch (error) {
      console.warn("Failed to record correction:", error)
    }
  }
}

技術メモ: Appendix: ExpenseCategory Enum Reference

ID	Name	Japanese
0	ENTERTAINMENT	接待交際費
1	MEETING_EXPENSES	会議費
2	OFFICE_SUPPLIES	事務用品費
3	TRAVEL_TRANSPORT	旅費交通費
4	ADVERTISING	広告宣伝費
5	UTILITIES	水道光熱費
6	RENT	賃借料
7	COMMUNICATION	通信費
8	OUTSOURCING	外注費
9	SUPPLIES	消耗品費
10	REPAIRS	修繕費
11	INSURANCE	保険料
12	TRAINING	研修費
13	LEGAL_FEES	支払手数料
14	EQUIPMENT	備品費
15	FUEL	燃料費
16	PRE_ESTABLISHMENT	開業費
17	ESTABLISHMENT	創立費
18	OTHERS	その他
19	ENTERTAINMENT_DINING	接待飲食費
20	WELFARE_BENEFITS	福利厚生費
21	BOOKS_PUBLICATIONS	新聞図書費
22	MEMBERSHIP_DUES	諸会費
23	DONATIONS	寄付金
24	RECRUITMENT	採用費
25	VEHICLE_EXPENSES	車両費
26	LEASE_PAYMENTS	リース料

技術メモ: Implementation Checklist

フェーズ 1: Database

Create expense_category_rules table
Create expense_category_corrections table
Implement RLS policies
Create indexes

フェーズ 2: Seed Data

Generate seed SQL from frontend rules
Insert ~300 system vendor rules
Insert ~150 system keyword rules
Verify data integrity

フェーズ 3: API Endpoints

GET /category-rules (list)
POST /category-rules (create)
PATCH /category-rules/:id (update)
DELETE /category-rules/:id (delete)
POST /categorize (single)
POST /categorize/batch (batch)
POST /category-corrections (feedback)

フェーズ 4: Integration

Update expense wizard to use backend categorization
Add fallback to local categorization
Implement correction recording
Add rule management UI (optional)

フェーズ 5: Learning System (Future)

Analyze correction patterns
Auto-adjust rule confidence based on feedback
Auto-generate user rules from corrections
Implement ML-based categorization improvement

ツールを試す簿記を学ぶなぜ作ったか料金ドキュメント利用規約経費精算ウィザード仕様アカウント復旧 / 認証強化

読み込み中

経費カテゴリルール仕様

概要

目次

技術メモ: 1. Database Schema

技術メモ: 1.1 expense_category_rules (Main Rules Table)

技術メモ: 1.2 expense_category_corrections (User Feedback/Learning)

技術メモ: 2. API Endpoints

技術メモ: 2.1 Rule Management

エンドポイント: GET /api/expense-wizard/category-rules

エンドポイント: POST /api/expense-wizard/category-rules

エンドポイント: PATCH /api/expense-wizard/category-rules/:ruleId

エンドポイント: DELETE /api/expense-wizard/category-rules/:ruleId

技術メモ: 2.2 Categorization API

エンドポイント: POST /api/expense-wizard/categorize

エンドポイント: POST /api/expense-wizard/categorize/batch

技術メモ: 2.3 Learning/Feedback API

エンドポイント: POST /api/expense-wizard/category-corrections

技術メモ: 3. Business Logic

技術メモ: 3.1 Category Matching Algorithm

技術メモ: 3.2 Amount-Based Adjustments

技術メモ: 4. Migration from Frontend Rules

技術メモ: 4.1 Seed Data Generation

技術メモ: 4.2 Migration Command

技術メモ: 5. Integration Guide

技術メモ: 5.1 Frontend Integration

技術メモ: 5.2 Recording Corrections

技術メモ: Appendix: ExpenseCategory Enum Reference

技術メモ: Implementation Checklist

フェーズ 1: Database

フェーズ 2: Seed Data

フェーズ 3: API Endpoints

フェーズ 4: Integration

フェーズ 5: Learning System (Future)