Merchant model refactor — summary

Short version. Full detail in merchant-model-refactor.md.

What changes

The merchant stops being an establishment and becomes the entity that owns the transaction. It’s the single unit of categorization for every transaction, however it arrived.

categorization_rules is deleted. Its regex coverage moves into merchant_aliases.
Merchants and categories are both global. No user-scoped rows in either.
A merchant maps to many categories, weighted by occurrences — not one fixed category.
Personalization lives in user_merchant_categories. That’s what makes a correction stick, and it’s the evidence essentiality reads.

Manual entries become merchants too: “aula de surf com o Pedro” resolves to a merchant Aula de surf, extracted by AI. The raw phrase stays on the transaction.

Tables

Table	Grain	Purpose
`merchants`	global	identity — brands and generic concepts alike
`merchant_aliases`	global	descriptor variants; `match_mode` = exact or prefix. This is the AI response cache
`merchant_categories`	merchant × category	cross-user prior, a rollup of the table below
`user_merchant_categories`	user × merchant × category	the facts — occurrences, spend, dates
`essentiality_merchant_scores`	user × merchant × category × cycle	snapshotted per budget cycle
~~`categorization_rules`~~		deleted

The two *_categories tables look near-identical and can’t be merged: the global one is the sum of the per-user ones. Source rows and their rollup don’t belong in one table.

Category essentiality already exists as BudgetCategoryAllocation.categoryEssentiality — don’t add a second table for it.

How a transaction resolves

Stage A — which merchant?

merchant name, exact
alias, exact (longest key first)
alias, prefix
gateway token (IFD*… → iFood)
AI extraction — creates the merchant

Stage B — which category?

this user’s user_merchant_categories, highest occurrences
the global merchant_categories prior
AI

Then write back: user_merchant_categories +1 occurrence, +spend. A correction decrements the old pair and increments the new — and never touches anything global.

The three risks

Merchant fragmentation from AI. Open-ended names produce Churrasco, Churrascaria, Churrasco com amigos as three merchants with split counts. Fixed by giving the AI a match-or-create tool schema with candidate ids as an enum, a write-time similarity guard, and a merge script. Never auto-merge.
Regex coverage regression. The rules catch a long tail no exact alias will. Phase 0 exists to measure this rather than guess.
Cold-start latency. More names reach AI once the rules are gone. Seed generic concepts hard.

Phases

#
0	Eval harness	baseline the current pipeline before deleting anything
1	Schema & migration	new tables, drop `categorization_rules`, enable `pg_trgm`
2	Delete the rules tier	`keyword-rules.ts`, `RULE_SEED`, the rule branch
3	Resolution service	two stages, thread `userId` through
4	AI extraction & hygiene	match-or-create schema, guards, merge script
5	Transaction paths	write-back, lazy creation on import
6	Seed rewrite	rules → aliases, priors, gateway rows
7	Backfill	build `user_merchant_categories` from existing history
8	Mobile	comments and one union type; wire field names unchanged
9	Tests

Order matters: Phase 7 before 6 backfills against an incomplete dictionary.

Worth knowing before you start

Every merchant needs a self-alias, or it’s unreachable from any longer phrase.
Don’t put raw manual phrases in merchant_aliases — no other user types that sentence, and it fills a shared table with personal text.
Always write the alias on an AI resolution, even a low-confidence one. The alias table is the cache: a descriptor costs one AI call the first time anyone imports it and zero after. Skipping uncertain answers means re-paying forever; a wrong merchant is one merge-script run.
Dedupe descriptors by normalized key before the AI call — 200 statement rows is usually 50–70 unique descriptors.
createMany uses skipDuplicates — don’t increment occurrences for skipped rows.

Cost

One batched call per import, roughly 1.5K in / 1.5K out at ~60 unique descriptors: about $0.01 on Haiku 4.5, $0.03 on Sonnet 5, $0.05 on Opus 5. The cache is what actually bounds it — repeat imports of the same card are nearly all hits, and descriptors overlap heavily across Brazilian users. ai-categorizer.ts already reads the model from config, so this is a config dial, not a code change.