Merchant Normalization

MUST NOT: Read or re-record transaction rows

You MUST NOT attempt to normalize merchants by reading transactions and writing them back one row at a time. This is both token-prohibitive and semantically wrong — normalization 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 normalization 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 processed. Do not skip pagination — large accounts can have hundreds of distinct raw merchant strings.

MUST: Write to normalized_merchant overlay only

Normalization MUST write to the normalized_merchant metadata field via bulk_update_transactions. It MUST NOT modify the original merchant field, and MUST NOT touch subject, predicate, content, or embedding columns. The original merchant value is the immutable provenance anchor and deduplication key.

Metadata Overlay Contract

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

Query and display tools (spending_summary, list_transactions, dashboard) prefer normalized_merchant when present and fall back to merchant when absent. You do not need to update any query logic — the overlay is transparently consumed.

Workflow

Step 1: Query Distinct Merchants

Call list_distinct_merchants to get the compact merchant list:

result = list_distinct_merchants(
    unnormalized_only=True,   # only merchants without a normalized_merchant overlay
    min_count=2,              # skip one-off merchants unlikely to need normalization
)

Inspect the response:

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

If total == 0, report "All merchants are already normalized." and stop.

Step 2: Recall Known Mappings from Memory

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

memory_search(
    query="merchant normalization alias",
    tags=["merchant-alias"],
    limit=100,
)

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

memory_recall(topic="merchant_alias:WHOLEFDS")

Memory fact schema for aliases (written in Step 7):

• subject: "merchant_alias:<RAW_PATTERN>" — the raw bank string or a prefix/pattern
• predicate: "normalizes_to"
• content: "Whole Foods" — the clean canonical name
• tags: ["merchant-alias", "normalization"]

Collect all known mappings into a lookup table before proceeding.

Step 3: Auto-Apply Known Mappings

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

bulk_update_transactions(updates=[
    {
        "match": {"merchant_pattern": "WHOLEFDS%"},
        "set": {"normalized_merchant": "Whole Foods"},
    },
    {
        "match": {"merchant_pattern": "AMZN MKTP US%"},
        "set": {"normalized_merchant": "Amazon"},
    },
    # ... one entry per known alias
])

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

Step 4: LLM Normalizes Unknown Merchants

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

Group obvious variants into a single canonical name. Apply these heuristics:

• Strip store numbers and location suffixes: "WHOLEFDS MKT #10456 AUSTIN TX" → "Whole Foods"
• Expand well-known abbreviations: "SQ *" prefix → Square payment, keep merchant after *; "AMZN" → "Amazon"
• Clean up all-caps: "NETFLIX.COM" → "Netflix", "SPOTIFY AB" → "Spotify"
• Group card-reader variants: "PAYPAL *MERCHANTNAME", "PAYPAL*MERCHANTNAME" → same merchant
• Preserve intentional multi-word names: do not over-collapse — "Blue Bottle Coffee" and "Philz Coffee" are separate merchants
• Use title case for the canonical form, unless the merchant's brand style is otherwise (e.g., "eBay", "YouTube")
• When unsure, use the most human-readable form without inventing information

Produce a mapping list: [(raw_pattern, canonical_name), ...]

Step 5: Apply LLM Normalization

For each group from Step 4, call bulk_update_transactions:

bulk_update_transactions(updates=[
    {
        "match": {"merchant_pattern": "SQ *BLUE BOTTLE%"},
        "set": {"normalized_merchant": "Blue Bottle Coffee"},
    },
    {
        "match": {"merchant_pattern": "NETFLIX%"},
        "set": {"normalized_merchant": "Netflix"},
    },
    # ... one entry per canonical group
])

Pattern guidance for merchant_pattern:

• Use SQL ILIKE syntax: % matches any sequence of characters, _ matches one character
• Prefer prefix patterns ("WHOLEFDS%") over exact matches to catch minor variant strings
• For Square-prefixed merchants: "SQ *BLUE BOTTLE%" or "SQ*BLUE BOTTLE%" covers both space variants
• Avoid overly broad patterns like "AMAZON%" if it would also match unrelated merchants; prefer "AMZN MKTP US%" or "AMAZON.COM%" for precision

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:

Merchant normalization complete.

Distinct merchants processed:  87
  Auto-applied (known aliases):  12  (from memory)
  LLM-normalized:                71
  Skipped (single-occurrence):    4

Transactions updated: 1,243

If pagination was used, note the batch count:

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

Step 7: Remember New Normalization Rules

For each new mapping produced by the 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).
# resolve_or_create_merchant_entity is shorthand for the try/except pattern below —
# use the real MCP calls (as shown in the worked example's Step 7):