# ADR-042: Vendor Rate Card Comparison Baseline Is Latest by created_at, Not Lowest Price

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising rate card comparison baseline decision in vendor_price_compare_v
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: vendor_price_compare_v live; comparison reference is latest active rate card
    changed_via: adr-kit (360lm)
```

## Context

The Vendors PWA allows recording multiple rate cards for the same vendor–product pair over time (negotiated price can change quarterly, annually, or per contract). When displaying a price comparison (e.g. "current price vs. reference price"), the system must choose which rate card is the baseline reference. Candidates are: the lowest historical price, the first-entered price, or the most recently created active price. The business intent is to compare current actual spend against the currently operative agreement, not against the best historical deal.

## Decision

`vendor_price_compare_v` selects the comparison baseline as the rate card with the **latest `created_at` among active cards** for each vendor–product pair. "Active" means `is_active = true` and `valid_to IS NULL OR valid_to >= CURRENT_DATE`.

Rationale: the most recently negotiated price is the operative agreement. Comparing against the lowest historical price would show permanent "overprice" even when the latest contract is properly priced — this would generate false alerts.

**User communication requirement:** this baseline logic must be explained to admin users who manage rate cards. The risk is that users accidentally create duplicate rate cards (editing by adding a new one instead of updating the existing), which shifts the baseline to the duplicate. A "duplicate rate card" warning was added to the rate card creation form.

**Decision Maker:** hkl

## Alternatives Considered

- **Lowest historical price as baseline.** Rejected: business question is "are we paying per the current agreement?" not "are we paying the best price we ever got?"; lowest price baseline generates permanent alerts whenever prices are legitimately renegotiated upward.
- **First-entered price as baseline.** Rejected: the oldest rate card is the least relevant for current purchasing decisions; as price agreements age out, comparison becomes meaningless.
- **User-marked "reference card" (explicit flag per vendor–product).** Rejected: requires admin to mark a reference card every time a new rate is entered; this is exactly the manual overhead the rate card system is designed to avoid; "most recent active" achieves the same result automatically.
- **Average of all active cards.** Rejected: averaging across multiple simultaneously-active rate cards for different volumes/tiers obscures the operative price; most vendor–product pairs have a single active rate card at any time.

## Consequences

**Positive:**
- Comparison always reflects the currently operative negotiated agreement.
- No admin action needed to "update the reference" when a new rate is negotiated — most-recent-active is automatic.
- Price comparison alerts are actionable (they indicate a deviation from the current agreement, not from the best historical deal).

**Negative / Trade-offs:**
- Accidentally-created duplicate rate cards silently shift the baseline — user education and duplicate warnings are necessary.
- A vendor with multiple simultaneously-active rate cards (e.g. different prices for different volume tiers) will have an arbitrary baseline (the most recently created of the active set) — this scenario requires manual reference card marking.

**Risks and mitigations:**
- User edits a rate card by adding a new one → baseline shifts to the accidentally-created entry: mitigated by "duplicate rate card" warning (same vendor + product + overlapping validity dates); admin can deactivate the accidental entry.
- Rate card validity expires but no new card is entered → comparison view shows "no active rate card": this is a data gap, not a code bug; admin receives a "rate card expiring" alert before `valid_to` date.

## Related Decisions

- ADR-038 (capture-to-catalog trigger) — transaction line captures update `last_known_price` in vendor_catalog, which is separate from rate card comparison but uses the same payee + product pair.
- ADR-036 (custodian.payees shared) — vendor identity for rate cards uses the shared payees table.

## References

- `memory/dbt_vendors.md` — "Comparison reference = latest by created_at (not lowest, not first)"; Known Gotchas note
- `finance/vendors/index.html` — vendor_price_compare_v usage in price comparison display