Check what you already own before paying again for the same external query

A search flow against a pay-per-result external API can spend real money re-fetching data you already hold internally, simply because nothing in the flow ever *reads* the internal store before it *pays* for the same filters again.

The signal that gives it away

- The only place the code touches your own cached/owned data is downstream of the payment, and only to avoid inserting duplicate rows — never upstream, to avoid paying at all. - Cost is priced per unit fetched (credits per page, per record, per call), and the same logical query (same filter set) gets re-issued across iterations of a loop or across sessions — each iteration repays for results that substantially overlap with the last. - A budget/ranking step further downstream cuts some already-paid results out of the final output, and only aggregate stats survive the cut — the actual entities are gone, even though you already paid the API for them. - Nobody can answer "how much of this query could have been answered for free from what is already held" without instrumenting a new code path — that absence of an easy answer is itself the tell.

How to exploit it

Gate the change behind a flag defaulting OFF. Ship the cache-lookup function additively, disabled by default, so behavior and cost are byte-for-byte identical to today until someone flips it — this is the standard safe-rollout shape for a change that touches a paid, revenue-relevant path ([Feature Toggles, Fowler](https://martinfowler.com/articles/feature-toggles.html)). It lets you deploy the mechanism and validate it against real production data before it's allowed to change anything.

Query the cache with the exact same normalized filters as the paid payload — not a looser approximation. If the paid call filters on sector-code prefix, region, revenue range and employee count, the cache lookup must apply the same fields, not just "same free-text query." This is the classic cache-aside read-before-fetch shape ([caching patterns, AWS](https://docs.aws.amazon.com/whitepapers/latest/database-caching-strategies-using-redis/caching-patterns.html)): check the cache first, only pay for the remainder ("miss") the cache can't cover.

Give the freshness window an explicit, separate value — don't inherit it from anything else. "Time counts": data older than the freshness window (for example, 90 days) is treated as a miss and re-purchased. Make this a named parameter, not an implicit property of "how long the cache table happens to retain rows."

Be conservative about what counts as a match, on purpose. Only treat a cached row as a hit if it carries the entity's stable identifier and the specific fields the paid filter cares about are non-null and consistent with it. If the paid request uses a filter dimension (e.g. a category code) that isn't reliably present across your cached rows, skip the cache lookup for that request entirely rather than loosen the match — a loose match that silently returns wrong hits is worse than a clean miss that just costs money.

Let every hit shrink the paid ask, not replace it wholesale. Each cache hit reduces the remaining page target sent to the paid API; if the cache alone already covers the requested volume, the call is skipped entirely — zero pages, zero cost. Cache hits should survive even if the paid call then fails on a network error: merge them into the result set unconditionally, don't gate them on the paid call's success.

Merge and dedup by the stable identifier, never by name. Names collide across entities; a tax ID / stable key does not.

Persist the "cut roster" unconditionally, not behind the flag. Whatever a downstream budget or ranking step discards, you already paid for it — write it into the shared store anyway, without scoping it to the current run (no run-local key), so it becomes permanent inventory for future searches instead of disappearing with the run that paid for it. This is safe to ship immediately and separately from the cache-read side: it's a write of data you already own, not a read that could mask a bug by returning something wrong. See A paid API response gets parsed into something useful, then never written anywhere that outlives the request for the more general failure this prevents — paying for a result and then having no durable place it lands.

Validate the match against real production data, not only a mocked store. See "What does NOT work" below — this is where the two silent failure modes actually surface.

Related: Soft vs. hard cascade invalidation: when 'confirm, then delete downstream' destroys data you paid for (once you've decided a result is expensive to reproduce, that same "don't throw it away" logic should extend to invalidation, not just to acquisition), A cache/lookup keyed only on domain (no session id) silently returns another session's data (the opposite failure mode of touching owned data carelessly — matching too broadly across the wrong scope instead of too narrowly).

What does NOT work

Trusting unit tests against a fake/mocked store to validate the filter match. Local tests with a synthetic database passed cleanly and still missed two real failure modes that only a live check against production data revealed:

- *Placeholder identifiers leaking into the match.* Earlier partial-enrichment stages can leave rows in the cache carrying a fabricated placeholder in the identifier field (a temporary stand-in used before the real value was known). An unguarded lookup serves those rows as if they were real matches, quietly poisoning the paid job with entities that were never actually resolved. Fix: explicitly exclude known placeholder patterns from the identifier field before treating a row as a candidate hit. - *Field representation drift between the cache and the paid payload.* The cache stored a geography field as an abbreviation (a short code) while the paid API's payload used the full written-out name for the same field. Same underlying fact, two different strings — so the filter would never have matched anything, for anyone, ever, with zero errors and zero log signal. It looked deployed and correct because nothing crashed; it just quietly never fired. Fix: hoist a single canonical mapping between the two representations to one shared location used by both the write side and the read side, and match against both forms until the underlying data is normalized.

Both bugs are specific instances of one general rule: a cache-match function is only as trustworthy as the last time someone ran it against real rows. A green test suite against a mock store proves the code path executes; it does not prove the two data representations it's comparing actually line up.

Loosening the match to "compare on whatever fields the cache does have" when a paid filter dimension is missing from cached rows. This produces confident-looking hits that are actually wrong-scope matches — worse than a clean miss, because a miss just costs money while a false hit costs correctness silently. Skip the cache for that request instead.

Trying to dedup across data sources before paying anything. When sources are query-based, you don't know what a query will return until you've already paid for the page that contains the answer — there is no way to deduplicate against a result you don't have yet. The realistic fix is exactly the combination above: read-before-pay against what you already own, plus post-hoc dedup-by-identifier once results actually arrive. Treat "avoid ever paying for the same entity twice across sources" as answered by that combination, not as a separate problem to solve up front.