# ADR-038: Vendor Catalog Is Auto-Populated by Trigger on Transaction Line Capture

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising capture-to-catalog pattern in fn_line_enrich_catalog trigger
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: Trigger live; vendor catalog grows organically from transaction line captures
    changed_via: adr-kit (360lm)
```

## Context

The vendor catalog (`custodian.vendor_catalog`) records which vendors supply which products and at what price. Maintaining this manually requires a separate catalog-entry workflow — after paying a vendor, the user would need to separately enter "this vendor supplied product X at price Y." This adds friction and is routinely skipped, leaving the catalog empty. The transaction line data (vendor + product + amount) is already captured during the payment workflow. A DB trigger on `transaction_lines` INSERT can extract this data and upsert the catalog automatically.

## Decision

`fn_line_enrich_catalog()` is a trigger function on `custodian.transaction_lines` (AFTER INSERT). When a line is inserted with `source_type = 'advance'` (vendor advance payment):
- UPSERT into `custodian.vendor_catalog` with: payee_id (vendor), product_id, last_known_price (from line amount), last_supply_date (from transaction date).
- Conflict resolution: ON CONFLICT (payee_id, product_id) DO UPDATE — always overwrites with the latest values.

The catalog grows organically through normal payment workflow usage — no separate catalog-maintenance UI required.

**Scope limitation:** the trigger fires only for `source_type = 'advance'` lines. Receipt lines and expense lines do NOT auto-enrich the catalog (they record costs already incurred, not vendor supply agreements). This is a known, deliberate constraint.

**Decision Maker:** hkl

## Alternatives Considered

- **Manual catalog entry (separate form, required before payment).** Rejected: adds a blocking step to the payment workflow; users skip it and the catalog remains empty; the product–vendor link is already implicit in the payment — no new information is required from the user.
- **Post-payment prompt ("Would you like to add this to the catalog?").** Rejected: optional prompts are routinely dismissed; catalog still ends up sparse; pattern tested in earlier UX iteration and abandoned.
- **Nightly batch job that reads transaction_lines and rebuilds the catalog.** Rejected: catalog is stale for up to 24h; recent payments are invisible in catalog searches; adds an external cron dependency when a trigger achieves the same result instantly.
- **Enrich catalog for ALL source_types (advance + receipt + expense).** Partially rejected: receipt lines and expense lines record consumption costs, not vendor supply prices — upsert would overwrite negotiated vendor advance prices with higher consumption costs; scope limited to advance lines to preserve price integrity.

## Consequences

**Positive:**
- Zero additional user effort — catalog builds from normal payment workflow.
- `last_known_price` and `last_supply_date` are always fresh after any vendor payment.
- Vendor price comparison (`vendor_price_compare_v`) has data from first payment without manual seeding.

**Negative / Trade-offs:**
- Receipt and expense lines do not enrich the catalog — if a product is only bought via receipts (not advance payments), it never appears in the catalog.
- Catalog reflects "last paid price," not "negotiated price" — if a vendor charges different prices per order, the catalog shows only the most recent.
- Trigger fires on every advance line INSERT — if bulk imports are performed, the trigger fires N times; acceptable for current volumes, not for bulk import tooling.

**Risks and mitigations:**
- Price drift (catalog shows anomalously high or low price from a one-off order): mitigated by `vendor_price_compare_v` showing last-known vs. historical range; users can see if the last price is an outlier.
- Trigger misfires on source_types other than advance: trigger has explicit `IF NEW.source_type = 'advance' THEN` guard.

## Related Decisions

- ADR-037 (soft polymorphic FK on transaction_lines) — the trigger reads source_type to determine whether to enrich; this is why source_type must always be set correctly.
- ADR-042 (rate card comparison = latest by created_at) — vendor_price_compare_v uses the enriched catalog data.
- ADR-007 (SECURITY DEFINER) — trigger function writes vendor_catalog; requires SECURITY DEFINER if catalog table has restricted grants.

## References

- `memory/dbt_vendors.md` — "Capturing transfers grows the catalog organically"; fn_line_enrich_catalog
- `finance/vendors/index.html` — advance line capture that triggers catalog enrichment
- `custodian.vendor_catalog` — schema: payee_id, product_id, last_known_price, last_supply_date