Category Inference

MUST NOT: Read or re-record transaction rows

You MUST NOT attempt to categorize by reading transactions and writing them back one row at a time. This is both token-prohibitive and semantically wrong — category inference is a metadata overlay, not a re-ingestion.

MUST: Use list_distinct_merchants as the sole data source

All merchant data used in this workflow comes from list_distinct_merchants. This tool returns deduplicated merchant names with transaction counts and totals. It is the correct and only acceptable source for categorization input.

MUST: Paginate if distinct merchant count exceeds 500

If the first call returns total > 500, you MUST process in paginated batches. Use limit=500, offset=0, then limit=500, offset=500, etc., until all merchants are categorized. Do not skip pagination — large accounts can have hundreds of distinct merchant names.

MUST: Write to inferred_category overlay only

Category inference MUST write to the inferred_category metadata field via bulk_update_transactions. It MUST NOT overwrite the original category field, and MUST NOT touch subject, predicate, content, or embedding columns.

Metadata Overlay Contract

The finance butler's fact layer is write-once on core columns. Category inference uses the metadata overlay pattern instead:

Query and display tools (spending_summary, list_transactions, dashboard) apply the following precedence when resolving a transaction's effective category:

category (explicit) > inferred_category (LLM-assigned) > uncategorized

You do not need to update any query logic — the overlay is transparently consumed.

Standard Category Taxonomy

Use exactly these category labels. Do not invent new categories.

Ordering

This skill SHOULD run AFTER merchant normalization. Running after normalization means distinct merchants are already collapsed into canonical names (e.g., the three Whole Foods store variants are a single "Whole Foods" entry). This reduces the merchant list size and produces more accurate, consistent categorization.

Workflow

Step 1: Query Distinct Merchants

Call list_distinct_merchants to get the compact merchant list:

result = list_distinct_merchants(min_count=1)

Use normalized_merchant values when available (post-normalization). The tool returns normalized_merchant in each entry when the overlay has been set.

Inspect the response:

• total — total number of distinct merchants
• merchants — list of {merchant, normalized_merchant (if set), count, total_amount} entries
• If total > 500, proceed in paginated batches (see CRITICAL CONSTRAINTS)

If total == 0, report "No merchants found." and stop.

Step 2: Recall Known Categories from Memory

Before running any LLM inference, check whether this butler has seen and categorized any of these merchants before:

memory_search(
    query="merchant category inference",
    tags=["merchant-category"],
    limit=100,
)

Also try targeted recall for high-frequency merchants you see in the list:

memory_recall(topic="merchant_category:Netflix")

Memory fact schema for merchant categories (written in Step 7):

• subject: "merchant_category:<CANONICAL_NAME>" — the clean canonical merchant name
• predicate: "merchant_category"
• content: "subscriptions" — the assigned category label
• tags: ["merchant-category", "category-inference", "<category-label>"]

Collect all known mappings into a lookup table before proceeding.

Step 3: Auto-Apply Known Categories

For every merchant in the list that matches a known category mapping, call bulk_update_transactions immediately — no LLM review needed:

bulk_update_transactions(updates=[
    {
        "match": {"merchant_pattern": "Netflix%"},
        "set": {"inferred_category": "subscriptions"},
    },
    {
        "match": {"merchant_pattern": "Whole Foods%"},
        "set": {"inferred_category": "groceries"},
    },
    # ... one entry per known merchant→category mapping
])

Track which merchants from the list have been handled so they are excluded from the LLM review in Step 4.

Step 4: LLM Categorizes Unknown Merchants

Present the remaining merchants (those not covered by known mappings) to yourself for categorization. For each merchant, you have count (transaction frequency) and total_amount (aggregate spend) as context.

Assign each merchant exactly one category from the standard taxonomy above. Apply these heuristics:

• Use normalized_merchant when available — it is the canonical name and gives better signal than the raw bank string
• High-frequency merchants deserve careful review — sort by count descending and prioritize the top entries; a miscategorization at 50 transactions has more impact than one at 2 transactions
• Grocery vs shopping boundary: wholesale clubs (Costco, Sam's Club) → groceries; general merchandise retailers (Target, Walmart, Amazon) → shopping unless the merchant is clearly a grocery-only outlet
• Delivery services: food delivery apps (DoorDash, Uber Eats) → dining; package delivery companies (FedEx, UPS) → shopping (proxy for the underlying purchase)
• Uber/Lyft: default to transport; if you see strong evidence of Uber Eats charges, use dining
• Amazon: default to shopping (catches most cases); do not attempt to subcategorize Amazon purchases
• Gym memberships: healthcare if clearly a fitness facility; subscriptions if it is a streaming/digital wellness app
• When genuinely ambiguous: prefer other over a forced fit, and note the ambiguity in the report

Produce a list of assignments: [(merchant_pattern, category), ...]

Step 5: Apply Category Assignments

For each assignment from Step 4, call bulk_update_transactions:

bulk_update_transactions(updates=[
    {
        "match": {"merchant_pattern": "Trader Joe%"},
        "set": {"inferred_category": "groceries"},
    },
    {
        "match": {"merchant_pattern": "UBER%"},
        "set": {"inferred_category": "transport"},
    },
    # ... one entry per merchant
])

Pattern guidance for merchant_pattern:

• Use SQL ILIKE syntax: % matches any sequence, _ matches one character
• When operating on normalized merchants (post-normalization run), prefer exact prefix matches — the names are already clean: "Netflix%", "Whole Foods%"
• When operating on raw merchants (normalization skipped), use broader patterns to catch bank-string variants: "NETFLIX%", "WHOLEFDS%", "WHOLE FOODS%"
• Keep patterns specific enough to avoid cross-merchant collisions

Accumulate the total_matched and total_updated counts returned by each bulk_update_transactions call.

Step 6: Report to User

After all updates are applied, report a concise summary:

Category inference complete.

Distinct merchants processed:  54
  Auto-applied (known mappings):  8  (from memory)
  LLM-categorized:               46

Transactions updated: 892

Breakdown by category:
  groceries      187  (21%)
  dining         162  (18%)
  subscriptions   94  (11%)
  shopping       201  (23%)
  transport       89  (10%)
  utilities       47   (5%)
  healthcare      38   (4%)
  entertainment   31   (3%)
  other           43   (5%)

If pagination was used, note the batch count:

Processed in 2 batches (1,100 distinct merchants total).

Step 7: Remember New Category Mappings

For each new merchant→category mapping produced by LLM in Step 4 (i.e., not already in memory), store a memory fact so future runs can auto-apply it:

# Resolve or create the merchant entity first (follow memory-classification skill).