Merchant model refactor — plan

1. The concept

Today: Merchant is an establishment — a global dictionary of real-world places, with raw statement descriptors hanging off it as MerchantAlias, and a single categoryId per merchant. Manual transactions never touch it: a row typed as “Aluguel” gets a merchantName string and takes its category from a regex in categorization_rules. Categorization is split across two unrelated mechanisms, and nothing learns per user.

Target: the merchant is the entity that owns the transaction — the destination of the funds — and it is the single unit of categorization for every transaction regardless of how it entered. categorization_rules is deleted.

Four ideas carry the design:

1. Merchant stays global. No user-scoped merchants. Uber and “churrasco de quinta com os amigos” are both global merchant rows — the second one resolved to the merchant Churrasco by AI extraction. The seed already works this way: Restaurante, Padaria, Aluguel, Condominio, Farmacia are generic concepts standing in as merchants, not establishments.
2. Category is global too. No per-user categories.
3. A merchant maps to many categories, weighted by occurrences — not one default. That distribution exists at two grains, and they are not peers: the per-user rows are the facts, the global row is their rollup.
4. Personalization lives entirely in the user × merchant × category grain. That’s what makes a correction stick, and it’s the evidence the essentiality algorithm reads.

The two grains

occurrences answers two different questions, so it lives in two tables:

A user’s correction writes only their own row, so it can never re-categorize a merchant for everyone. Once a user has a row for a merchant, the global prior is never consulted for them again.

2. Target schema

erDiagram
    users ||--o{ transactions : owns
    users ||--o{ user_merchant_categories : learns
    users ||--o{ budget_cycles : runs

    merchants ||--o{ merchant_aliases : "resolved by"
    merchants ||--o{ merchant_categories : "global prior"
    merchants ||--o{ user_merchant_categories : "per user"
    merchants ||--o{ transactions : owns
    merchant_aliases ||--o{ transactions : matched

    transaction_categories ||--o{ merchant_categories : weighted
    transaction_categories ||--o{ user_merchant_categories : weighted
    transaction_categories ||--o{ transactions : classifies

    budget_cycles ||--o{ essentiality_merchant_scores : snapshots
    budget_cycles ||--o{ budget_category_allocations : snapshots
    merchants ||--o{ essentiality_merchant_scores : scored
    transaction_categories ||--o{ essentiality_merchant_scores : within
    transaction_categories ||--o{ budget_category_allocations : scored

    merchants {
        uuid id PK
        string normalized_name UK
        string display_name
        string source "seed | ai"
        int hit_count
    }
    merchant_aliases {
        uuid id PK
        uuid merchant_id FK
        string normalized_alias UK
        string match_mode "exact | prefix"
    }
    merchant_categories {
        uuid id PK
        uuid merchant_id FK
        uuid category_id FK
        int occurrences "rollup"
        int user_count
    }
    user_merchant_categories {
        uuid id PK
        uuid user_id FK
        uuid merchant_id FK
        uuid category_id FK
        int occurrences "the facts"
        bigint total_spend_in_cents
        datetime last_seen_at
    }
    transactions {
        uuid id PK
        uuid user_id FK
        uuid merchant_id FK
        uuid merchant_alias_id FK
        uuid category_id FK
        string merchant_name "raw text"
        bigint amount_in_cents
        datetime occurred_at
    }
    transaction_categories {
        uuid id PK
        string value UK
        string name
        uuid kind_id FK
    }
    essentiality_merchant_scores {
        uuid id PK
        uuid budget_cycle_id FK
        uuid merchant_id FK
        uuid category_id FK
        uuid user_id
        decimal score
    }
    budget_category_allocations {
        uuid id PK
        uuid budget_cycle_id FK
        uuid category_id FK
        decimal category_essentiality "already exists"
    }
    budget_cycles {
        uuid id PK
        uuid user_id FK
        date cycle_month
    }
    users {
        uuid id PK
        string auth_subject UK
    }

The one relationship the diagram cannot draw: merchant_categories is derived from user_merchant_categories by the rollup job — not joined to it. Everything else above is a real foreign key.

/// Global merchant registry. Brands and generic concepts alike ("Uber", "Churrasco").
model Merchant {
  id             String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  displayName    String   @map("display_name") @db.VarChar(120)
  normalizedName String   @unique @map("normalized_name") @db.VarChar(120)
  source         String   @default("seed")          // seed | ai
  confidence     Decimal? @db.Decimal(4, 3)
  hitCount       Int      @default(0) @map("hit_count")
  createdAt      DateTime @default(now()) @map("created_at")
  updatedAt      DateTime @updatedAt @map("updated_at")

  aliases            MerchantAlias[]
  categoryPriors     MerchantCategory[]
  userCategories     UserMerchantCategory[]
  transactions       Transaction[]
  essentialityScores EssentialityMerchantScore[]

  @@map("merchants")
}

/// Raw descriptor variants from statements. Never receives free-text manual entries — see §4.
model MerchantAlias {
  id              String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  merchantId      String   @map("merchant_id") @db.Uuid
  normalizedAlias String   @unique @map("normalized_alias") @db.VarChar(120)
  matchMode       String   @default("exact") @map("match_mode") @db.VarChar(10)  // exact | prefix
  createdAt       DateTime @default(now()) @map("created_at")

  merchant     Merchant      @relation(fields: [merchantId], references: [id], onDelete: Cascade)
  transactions Transaction[]

  @@index([merchantId], map: "idx_merchant_aliases_merchant")
  @@index([matchMode], map: "idx_merchant_aliases_match_mode")
  @@map("merchant_aliases")
}

/// Cross-user prior — a ROLLUP of UserMerchantCategory. Never written by a request path.
model MerchantCategory {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  merchantId  String   @map("merchant_id") @db.Uuid
  categoryId  String   @map("category_id") @db.Uuid
  occurrences Int      @default(0)
  userCount   Int      @default(0) @map("user_count")
  computedAt  DateTime @default(now()) @map("computed_at")

  merchant Merchant            @relation(fields: [merchantId], references: [id], onDelete: Cascade)
  category TransactionCategory @relation(fields: [categoryId], references: [id], onDelete: Cascade)

  @@unique([merchantId, categoryId], map: "uq_merchant_categories_merchant_category")
  @@index([merchantId, occurrences], map: "idx_merchant_categories_merchant_occurrences")
  @@map("merchant_categories")
}

/// The facts. One row per user per merchant per category. Evidence for essentiality.
model UserMerchantCategory {
  id                String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId            String   @map("user_id") @db.Uuid
  merchantId        String   @map("merchant_id") @db.Uuid
  categoryId        String   @map("category_id") @db.Uuid
  occurrences       Int      @default(0)
  totalSpendInCents BigInt   @default(0) @map("total_spend_in_cents")
  firstSeenAt       DateTime @default(now()) @map("first_seen_at")
  lastSeenAt        DateTime @default(now()) @map("last_seen_at")

  user     User                @relation(fields: [userId], references: [id], onDelete: Cascade)
  merchant Merchant            @relation(fields: [merchantId], references: [id], onDelete: Cascade)
  category TransactionCategory @relation(fields: [categoryId], references: [id], onDelete: Cascade)

  @@unique([userId, merchantId, categoryId], map: "uq_user_merchant_categories")
  @@index([userId, merchantId], map: "idx_user_merchant_categories_user_merchant")
  @@map("user_merchant_categories")
}

/// Essentiality of a merchant within a category, for one user, snapshotted per budget cycle.
model EssentialityMerchantScore {
  id            String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId        String   @map("user_id") @db.Uuid
  merchantId    String   @map("merchant_id") @db.Uuid
  categoryId    String   @map("category_id") @db.Uuid
  budgetCycleId String   @map("budget_cycle_id") @db.Uuid
  score         Decimal  @db.Decimal(5, 4)
  createdAt     DateTime @default(now()) @map("created_at")

  @@unique([budgetCycleId, merchantId, categoryId], map: "uq_essentiality_merchant_scores")
  @@index([userId, merchantId], map: "idx_essentiality_merchant_scores_user_merchant")
  @@map("essentiality_merchant_scores")
}

// model CategorizationRule — DELETED

Transaction changes: merchantId stays, merchantName stays (the raw text as typed or imported), plus optional merchantAliasId recording which alias matched — useful provenance for debugging resolution and for measuring alias coverage.

Two things to settle in Phase 1, both consequences of decisions above:

• TransactionCategory.userId should be dropped along with @@unique([userId, value]) → value @unique. Categories are global now, and every query already filters userId: null. Leaving a vestigial nullable column invites exactly the ambiguity we removed.
• essentiality_category_scores already exists — BudgetCategoryAllocation.categoryEssentiality is keyed @@unique([budgetCycleId, categoryId]), which is the identical grain (a BudgetCycle already carries userId). Don’t add a second table; use the column that’s there. If you want category essentiality decoupled from a budget run, that’s a different decision — but then the existing column should move, not be duplicated.

For the same reason, EssentialityMerchantScore.userId is denormalized: budgetCycleId already implies the user. Kept for query convenience; the unique key deliberately omits it.

3. Resolution pipeline

Two stages. Stage A answers who is this?, stage B answers what category is it for this user?

resolve(userId: string, rawName: string) -> { merchantId, categoryId, source, confidence }

Stage A — find the merchant

1. Merchant.normalizedName exact, on normalizeName(rawName).
2. MerchantAlias exact, on lookupKeys(rawName) — longest key first.
3. MerchantAlias prefix (matchMode = 'prefix') — the ex-regex stems.
4. MerchantAlias exact on the gateway token — see below.
5. AI extraction — creates the merchant (§4).

Longest-key-first ordering replaces regex priority: the full normalized string is tried before individual tokens, so MERCADO LIVRE matches its own alias before the MERCADO token is tested. That’s how the old MERCADO(?! ?LIVRE) lookahead and the SALARY-before-TRANSFERS priority survive without regex. lookupKeys() already orders this way — the full-string aliases just need seeding.

Every merchant carries a self-alias. Tier 2 searches merchant_aliases, not merchant names, so without a row whose normalizedAlias equals the merchant’s own normalizedName a merchant is reachable only by an exact full-string match at tier 1 — never from a longer phrase containing it. Creating a merchant must always create that alias in the same transaction. The current seed already does this (primaryAlias = normalizeMerchant(entry.name)); the AI-creation path must too.

Note the residual limit: lookupKeys() emits the full string and single tokens, not intermediate n-grams. A multi-word merchant like Aula de surf is therefore not reachable from “aula de surf sábado” — that lands in AI, where the pg_trgm candidate pass surfaces it and the model returns decision: "match". Correct outcome, one AI call. Add token aliases for multi-word merchants that recur.

The gateway token (tier 4)

normalizeMerchant() strips everything before a * as a payment-processor prefix. For real card descriptors that throws away the most informative token:

IFD*INCONFIDENTES ALIMENTBELO HORIZONTBR
  → strips IFD*  →  INCONFIDENTES ALIMENTBELO HORIZONTBR
  → tokens: INCONFIDENTES, ALIMENTBELO, HORIZONTBR   — none of which is iFood

The prefix list conflates two different things. Aggregators (IFD, BKG, RAPPI) are the counterparty — the funds go to iFood, and the restaurant name is unrecoverable anyway. Acquirers and wallets (CIELO, REDE, STONE, GETNET, PAGSEGURO, APLPAY) are genuine noise.

Don’t enumerate them in code. Have lookupKeys() emit the pre-star token as an extra key and let merchant_aliases decide: IFD has a row pointing at iFood, CIELO doesn’t. Aggregator vs acquirer becomes data — the seed grows it, and rows can be added without a deploy.

Ordering last is what makes it safe: MP*NETFLIX resolves to Netflix, because a recognizable tail always beats the gateway. GENERIC_STAR_PREFIX already extracts 2–6 character prefixes, so the parsing exists; only the key emission is new.

Trailing location noise

Card networks pack merchant name, city and country into fixed-width fields and truncate, producing ALIMENTBELO HORIZONTBR — ALIMENT + BELO HORIZONT + BR. Strip a trailing country code and match against a known-city list in normalizeName(). Cheap, and it recovers a meaningful share of tokens across a whole statement.

Stage B — find the category

1. UserMerchantCategory for (userId, merchantId), highest occurrences.
2. MerchantCategory prior for merchantId, highest occurrences.
3. AI classification (same call as extraction when stage A missed).

Both stages stay batchable — that’s what keeps a statement import to one AI call.

Write-back

Making the prior a real rollup rather than an incrementally-maintained counter means corrections self-heal: fix the user row, and the next rollup fixes the prior. No decrement bookkeeping across two tables.

4. AI extraction

The AI’s contract changes from classify into an enum to also emit a merchant name. This is the riskiest new piece.

Current AiCategorizer.categorizeBatch returns { descriptor, category, confidence } against a closed category enum. An open-ended merchantName is the problem: left unguarded the model emits Churrasco, Churrasco com amigos, and Churrascaria for near-identical inputs, and the merchant table fragments into near-duplicates with split occurrence counts. It degrades quietly — nothing fails, the numbers just stop meaning anything.

Three defences, in order of leverage.

1. Make the AI pick, not invent. Trigram-search existing merchants first (pg_trgm on normalized_name), then put those ids in the tool schema as an enum so the constraint is structural rather than a prompt hint the model may ignore:

input_schema: {
  descriptor:        string,
  decision:          enum ["match", "create"],
  merchant_id:       enum [...candidate ids from pg_trgm...],   // required when "match"
  new_merchant_name: string,                                    // required when "create"
  category:          enum [...category values...],
  confidence:        number
}

When retrieval surfaces a real candidate the model is picking from an enum again — the job it already does reliably. Generation happens only on a genuine miss. This removes most of the fragmentation surface before any cleanup logic exists.

2. Guard on write, without trusting the model. Before inserting a new merchant, trigram-check the normalized name against existing rows; above ~0.8 similarity (tune on real data), reuse instead of insert. Deterministic, and it still catches the case where the model ignored its candidates. Also run normalizeName() over the returned name and upsert on normalizedName so casing and accent variants collapse, and skip creation entirely below AI_LEARN_MIN_CONFIDENCE.

3. Be able to merge. You will not get to zero, so make fragmentation recoverable instead of permanent: a script that repoints merchant_aliases and transactions.merchant_id, sums user_merchant_categories.occurrences into the survivor, and deletes the loser. Pair it with a similarity report in the MerchantCategory rollup job — flag merchant pairs above threshold, plus AI-created merchants stuck at a hitCount of 1 or 2.

Never auto-merge. Churrascaria is a restaurant and Churrasco is an event; a similarity check will happily flag them as the same merchant. The report is a review queue, not an action.

merchant_aliases is the AI response cache

There is no separate cache table. Every AI resolution writes an alias row, and stage A tier 2 reads it before the AI is ever called again:

normalized_alias (unique)  →  merchant_id  →  default_category_id

A descriptor therefore costs one AI call the first time anyone imports it, and zero forever after. Because the table is global, cost per import falls as it fills — descriptors overlap heavily across Brazilian users (iFood, Uber, Netflix, Pão de Açúcar).

Always write the alias, including on low-confidence resolutions. Skipping the row for uncertain answers means re-paying for the same hopeless descriptor on every future import; the confidence column carries the uncertainty instead, and the merge script fixes a wrong merchant later. An uncached miss is a recurring bill; a wrong-but-cached merchant is one script run. (The alternative — a resolution_failed_at column that suppresses retries for N days — is more machinery for the same outcome. Start without it.)

This supersedes an earlier draft rule that statement imports should only mint a merchant after a descriptor recurred. That rule saved merchants at the cost of re-paying for every first sighting, and fragmentation is already handled by the two guards above.

Dedupe by normalized key before the call regardless: 200 rows is usually 50–70 unique descriptors, so both the AI batch and the write volume shrink accordingly.

Raw manual phrases never become aliases. “churrasco de quinta com os amigos” resolves to the merchant Churrasco; the phrase stays on Transaction.merchantName and nowhere else. lookupKeys() token-splitting already yields CHURRASCO, so the second time that user types the phrase it resolves at stage A.2 with no AI and no stored alias. Storing the phrase globally would be pure cost — no other user types that exact sentence — while filling a shared table with things like “Empréstimo pro João Silva”. merchant_aliases receives statement-shaped descriptors only.

5. What replaces the regex rules

• Whole words (RESTAURANTE, POSTO, HOSPITAL, CINEMA, ALUGUEL, CONDOMINIO, PIX, TED) → generic merchants with the word as an exact alias. Token-splitting already handles them.
• Stems that exact aliases cannot cover (~16): DROGA\w*, PIZZA\w*, SUPERMERCAD\w*, MEDIC\w*, TELEFON\w*, CHURRASC\w*, SORVETE\w*, INGRESSO\w*, TRANSFEREN\w*, INVESTIMENT\w*, VENCIMENTO\w*, PSICOL\w*, FISIOTERAP\w*, DENTIST\w*, ATACAD\w*, HAMBURGU\w* → matchMode: 'prefix' aliases.

Seeding these aggressively is what keeps AI off the hot path for manual entry, where the user is waiting on the category step to prefill.

6. Execution phases

Phase 0 — Eval harness (before anything is deleted)

• A fixture of real descriptors with expected categories: the seeded demo transactions plus an exported statement, a few hundred rows.
• scripts/eval-categorization.ts reporting overall accuracy and hit rate per tier.
• Run it against the current pipeline first. That baseline is the only way to know whether deleting the rules tier cost coverage, and exactly which descriptors broke. Re-run after each phase.

Without this, every remaining decision — how hard to seed, prefix vs exact, where to set the confidence gates — is argued instead of measured.

Phase 1 — Schema & migration

• Rewrite Merchant (drop categoryId), MerchantAlias (add matchMode); add MerchantCategory, UserMerchantCategory, EssentialityMerchantScore; delete CategorizationRule.
• Drop TransactionCategory.userId; value becomes @unique.
• Transaction: add merchantAliasId.
• prisma migrate dev (project convention — not db push).
• Hand-edit: backfill merchants.normalized_name, migrate existing merchants.category_id into merchant_categories rows, DROP TABLE categorization_rules, remap transactions.category_source = 'rule' → 'dictionary'.
• Enable pg_trgm for the Phase 4 candidate search.

Phase 2 — Delete the rules tier

• Delete src/modules/transactions/categorization/keyword-rules.ts.
• categorization.service.ts: drop ensureRules, compiledRules, RULE_CONFIDENCE, ruleWriteback, and 'rule' from CategorizationSource.
• Delete CATEGORIZATION_RULE_IDS and RULE_SEED from prisma/seed.ts.

Phase 3 — Resolution service

• Rewrite CategorizationService around the two stages; thread userId through every call site (POST /transactions/categorize and /categorize/batch are currently user-agnostic and need the auth subject).
• Rename utils/normalize-merchant.ts → utils/normalize-name.ts (normalizeMerchant → normalizeName, merchantLookupKeys → lookupKeys); add prefix-candidate support. The normalization logic itself is good and stays.
• New service owning the UserMerchantCategory upsert / decrement path.

Phase 4 — AI extraction & merchant hygiene

• Extend AiCategorizer to the match-or-create tool schema in §4.
• pg_trgm candidate retrieval feeding the merchant_id enum.
• Write-time similarity guard + normalizeName collapse + confidence gate on creation.
• Name-shape validation: reject over three words or containing digits — such rows categorize the transaction but create no merchant.
• scripts/merge-merchants.ts: repoint aliases and transactions, sum occurrences, delete the loser. Ship it in this phase, not later — it’s what makes the rest recoverable.
• Near-duplicate report in the rollup job (Phase 3), reviewed manually. No auto-merge.

Phase 5 — Transaction paths

• transactions.service.ts create(): always write UserMerchantCategory on the confirmed category (today it only reads, via resolveMerchantIds).
• update(): category change → decrement old, increment new; name change → re-resolve.
• createMany(): one batched resolution pass (already batched — keep it), deduping descriptors by normalized key first. Every AI resolution writes its alias row so the next import hits the cache (§4).
• createMany() uses skipDuplicates — rows skipped as duplicates must not increment UserMerchantCategory, or occurrence counts inflate on every re-upload of the same statement. Derive the write-back set from what createMany actually inserted, not from the input dtos.
• repeat(): carries merchantId through; add merchantAliasId.

Phase 6 — Seed rewrite

• MERCHANT_SEED entries gain normalizedName; their categoryId becomes a MerchantCategory prior row with a seeded occurrences weight.
• Convert RULE_SEED per §5 (whole words → exact, ~16 stems → prefix).
• Seed full-string aliases needed for longest-first disambiguation (MERCADO LIVRE before MERCADO).
• seedDemoTransactions() must write UserMerchantCategory rows for the demo user.

Phase 7 — Backfill

For every transaction with a merchantName: resolve the merchant, set merchantId, and build UserMerchantCategory rows grouped by (userId, merchantId, categoryId) with occurrences, totalSpendInCents, firstSeenAt / lastSeenAt from the grouped rows. Then run the MerchantCategory rollup once.

Highest-value step: every user’s personal mapping boots from their own confirmed history, so essentiality starts with real evidence instead of zero.

Phase 8 — Mobile

• src/stores/statementImport/statementImport.store.ts:180 — drop "rule" from the accepted categorySource union.
• src/features/newTransaction/NewTransactionSteps.tsx:179-184 — the comment about learning into a shared dictionary needs updating; category learning is per-user now.
• src/services/transactionsApi.ts:24-29 and src/stores/transactions/transactions.store.type.ts:21-27 — doc comments on merchant_id semantics.

Field names on the wire (merchant_name, merchant_id) are unchanged, so this phase is comments and one union type.

Phase 9 — Tests

categorization.service.spec.ts (largest rewrite — rule-tier assertions out, two-stage and user-prior-beats-global assertions in), transactions.service.spec.ts, NewTransactionSteps.test.tsx, transactions.store.test.ts.

7. Risks

• Merchant fragmentation from AI extraction. The single biggest new risk, and it degrades quietly — you only notice when the merchant table holds four spellings of one concept and every occurrence count is split between them. All three defences in §4 are load-bearing; the merge script especially, since prevention alone never reaches zero.
• Regex coverage regression. The rules catch a long tail no exact alias will. Before deleting keyword-rules.ts, run the old and new pipelines over the seeded demo transactions plus a real statement export and diff the categories. Unit tests will not surface this.
• Cold-start latency on manual entry. Removing the rules tier pushes more names into AI, and the manual-entry UI waits on it to prefill the category step. The prefix tier and generous generic-merchant seeding are what bound it.
• Phase ordering. Phase 7 before Phase 6 backfills against an incomplete dictionary, leaving transactions unresolved that should have matched.
• Rollup staleness. MerchantCategory is only as fresh as the last job run. Fine — it’s read only on cold start, where being a day stale is harmless.

8. Worked example

A manual entry that misses every dictionary tier — the case that motivated the refactor.

Input: the user types aula de surf com o Pedro, R$ 120,00, expense, no category picked.

Stage A — find the merchant

normalizeName() uppercases, strips accents and collapses whitespace → AULA DE SURF COM O PEDRO. lookupKeys() then yields, longest first:

AULA DE SURF COM O PEDRO      full string
AULA                          token
SURF                          token
PEDRO                         token

DE and COM are dropped as stopwords, O for being under three characters.

1. merchants.normalized_name exact on the full string — miss.
2. merchant_aliases exact on all four keys — miss.
3. merchant_aliases prefix — miss. No seeded stem covers surfing.
4. AI. A pg_trgm search over merchants.normalized_name returns nothing above threshold, so the merchant_id enum is empty and the model’s only option is create.

It returns decision: "create", new_merchant_name: "Aula de surf", category: "LEISURE", confidence: 0.82. Note what it dropped: com o Pedro is the user’s framing, not the merchant.

Validation passes — three words, no digits, confidence above the 0.6 gate. The write-time trigram guard finds no existing merchant above 0.8 similarity, so Aula de surf is created together with its self-alias AULA DE SURF.

Stage B — find the category

1. user_merchant_categories for this user and merchant — none, it was created a moment ago.
2. merchant_categories prior — none, same reason.
3. The AI response already carries LEISURE.

The app prefills LEISURE. The user accepts.

What gets written

transactions              merchant_id    → Aula de surf
                          merchant_name  = "aula de surf com o Pedro"   ← raw, as typed
                          category_id    → LEISURE
                          amount_in_cents = 12000

merchants                 Aula de surf   source: ai, confidence: 0.82, hit_count: 1
merchant_aliases          AULA DE SURF   match_mode: exact

user_merchant_categories  (user, Aula de surf, LEISURE)
                          occurrences: 1, total_spend_in_cents: 12000

The raw phrase and the canonical merchant both survive, in different columns. The user still sees their own words on the transaction; the ledger counts a merchant.

flowchart TD
    A["aula de surf com o Pedro<br/>R$ 120,00"] --> B["normalizeName + lookupKeys<br/>AULA · SURF · PEDRO"]
    B --> C{merchants.normalized_name}
    C -- miss --> D{merchant_aliases exact}
    D -- miss --> E{merchant_aliases prefix}
    E -- miss --> F["pg_trgm candidates<br/>none above threshold"]
    F --> G["AI: create<br/>Aula de surf · LEISURE · 0.82"]
    G --> H{"validate<br/>≤3 words · no digits · ≥0.6"}
    H -- pass --> I["create merchant<br/>+ self-alias AULA DE SURF"]
    C -- hit --> J[stage B: category]
    D -- hit --> J
    E -- hit --> J
    I --> J
    J --> K["user_merchant_categories<br/>+1 occurrence · +12000 cents"]
    K --> L["transaction saved<br/>raw phrase kept on the row"]

The second time

Three weeks later the user types aula de surf sábado.

Keys are now AULA DE SURF SABADO, AULA, SURF, SABADO — none of which equals the alias AULA DE SURF, because lookupKeys() emits the full string and single tokens but no intermediate n-grams. So this reaches AI again. The difference is that pg_trgm now surfaces Aula de surf as a candidate, its id goes into the enum, and the model returns decision: "match".

One AI call, no second merchant, and user_merchant_categories.occurrences climbs to 2 on the same row. That is defence #1 from §4 doing exactly its job — without the candidate enum this is precisely where Aula de surf sábado would have been born as a rival merchant.

Adding SURF as a token alias on that merchant removes the AI call entirely from the third occurrence onward. Worth doing for multi-word merchants that recur; not worth doing pre-emptively for every one.

If the user had corrected the category

Say they changed LEISURE to HEALTH. update() decrements (user, Aula de surf, LEISURE) and increments (user, Aula de surf, HEALTH). Nothing global moves — merchant_categories still says this merchant usually means leisure for everyone else, and picks the correction up on the next rollup only if enough users agree. The next time this user types anything resolving to Aula de surf, stage B tier 1 finds HEALTH and never consults the prior.