Our Grant Search Was Lying to Users. Here's How We Caught It.

GrantSnag holds 11,747,807 grant records pulled from private foundation 990-PF filings. Keyword search over them was bad in three distinct ways, and only one of them was slowness.

This is a technical post. If you are a nonprofit leader who uses GrantSnag, the short version is that keyword search now returns complete, honest results in well under a second, and further down I explain exactly what it still gets wrong. If you write software, the interesting part is that two of the three problems were failures of honesty rather than performance.

Lie #1: the truncated sample

Keyword search called a function that returned the top 200 grants matching your term, ordered by year descending, and then ranked funders from that slice.

The year column is an integer. Four or five distinct values across the whole corpus. So ORDER BY year DESC LIMIT 200 had almost no ordering to work with. It grabbed an unstable sample of whatever the newest filing year happened to be, and the sample changed between identical queries.

The consequence was worse than bad ranking. Search "disaster" and you match 22,025 grants across 4,066 funders. The interface saw 200 of those grants. Any funder without a grant in the newest year was not ranked low; it was absent. We were showing you a slice of one year and calling it the answer.

The fix was to stop ranking in the application and aggregate in the database instead. search_funders_by_keyword now groups by funder in SQL and returns a true count(*) and sum(amount) over every match, plus corpus-wide totals for the summary line. The years and states used by the client-side filters are computed over all matches too, so filtering no longer needs a re-query.

Lie #2: the swallowed timeout

The peer and browse code paths did this:

return data || []

data is null when the query errors. So a database timeout became an empty array, and an empty array rendered as:

No results found. Try a different spelling or search term.

Measured on production as an anonymous user, peer search for "education" returned Postgres error 57014, a statement timeout, after 30.3 seconds. Then it told the user to check their spelling.

Peer and browse are 84% of our search traffic. For broad terms, our most-used feature spent half a minute failing and then blamed the person typing.

Every search path now surfaces the error, and 57014 gets an honest message. A query that cannot complete says so. This did not make anything faster; it made it stop lying, which turned out to be the prerequisite for finding the real problem.

Lie #3: the funder who cannot be found

Here is the one that actually mattered, and no amount of speed fixes it.

A 990-PF tells you who a foundation funded. It does not tell you what the foundation cares about. The grant description field is overwhelmingly FOR OPERATIONAL USE or GENERAL SUPPORT. 28% of funders write nothing but that. For those foundations, every keyword search over grant text matches nothing, forever, no matter how fast the query runs.

And when the description is specific, it is specific about the wrong thing. A foundation gives a grant to an organization called Sportable. The description says "general operating support." Nothing in that record contains the words "assistive technology," "adaptive sports," or "disability," even though that is precisely what the money did. The funder is a perfect match for a search it can never satisfy.

The insight the whole rebuild rests on: what a funder cares about is described in detail, by the organizations it chose to fund. Sportable's own 990 says what Sportable does. If we can connect a grant to its recipient's mission statement, we can characterize the funder using text the funder never wrote.

So we built the association. For every grant, resolve the recipient to its own 990 filing, and credit the funder with the keywords in that recipient's mission.

Making the association honest

This only works if the recipient matching is accurate, and "accurate" has to be a measured number, not a hope.

Coverage first. Of all grants, how many can we tie to a recipient mission at all:

| Path | Coverage | |---|---| | Recipient EIN present in the filing | 21.8% | | Exact match on normalized name, unambiguous | 40.8% | | Ambiguous name, resolved by intersection | +2.1% | | Combined | 51.6% of grants, ~56% of dollars |

Those are per-grant numbers, and it is worth being clear about what they translate to, because the unit you actually search is the funder. A funder needs one resolved recipient to earn a keyword. Its other grants failing to resolve costs nothing. Across the cache, 41.1% of the 622,728 funder-to-keyword associations exist only because a recipient's mission supplied them, and 4,566 funders out of 43,172 are findable at all only that way. Half the grants not resolving does not mean half the funders are missing.

Then precision, which is the number that matters. The 1.88 million grants that do carry a recipient EIN are a labeled holdout: we already know the right answer for them. So we ran the name matcher across those grants blind, ignoring the EIN entirely, and compared its output to the truth.

Exact-name matching scored 93.4%. Weighting by grant volume, only 27.9% of its mistakes attach genuinely wrong keywords (most errors are near-misses between organizations that do similar work), which puts the effective keyword error rate at roughly 1.84%.

Two decisions worth explaining:

We did not use fuzzy or trigram matching. This is the obvious reach for a name-matching problem and we tested it. It measured worse. Exact matching on a normalized name is a dictionary lookup; trigram similarity across 2.7 million distinct organization names is an all-pairs problem, and at this corpus size the extra recall it bought came bundled with enough garbage to lower precision. The normalization does the work instead: uppercase, punctuation to spaces, drop a leading "THE" and trailing legal suffixes like INC or LLC. Words like FOUNDATION, TRUST, and FUND are deliberately left alone, because those distinguish real organizations from each other.

For ambiguous names, we intersect instead of guessing. About 15,500 normalized names map to more than one organization. "BOYS AND GIRLS CLUB" is not one nonprofit. Rather than pick a chapter, we take only the keywords every candidate agrees on. Every Boys and Girls Club does youth work, whichever one this grant went to. Unanimity is required, so precision on this path is higher than on the main name path, at the cost of returning fewer keywords per grant.

The size band, and why it is a trade-off

Associating keywords with funders creates a new problem: the funders that match everything are the ones you can never apply to.

A foundation making $20 million grants is moving money between institutions. A small nonprofit will not be eligible and will never be. At the other end, a foundation distributing $80 million in $300 increments is running an employee matching-gift program. Nobody applies for those, and if they could, it would not be worth the hour.

Both dominate any ranking built on volume. So funders qualify only if their median grant falls between $1,000 and $1,000,000, with at least 10 recipients.

The cutoff sounds arbitrary written as a number, but the data has a gap and the threshold sits inside it. Below the line, the Pfizer Foundation runs a $250 median across 22,101 recipients. Above it, Enterprise Holdings Foundation sits at $2,500, the Jack Kent Cooke Foundation at $8,760, the Doug Flutie Jr. Foundation at $10,000. Very little lives in between, because a median of a few hundred dollars means a matching-gift program and a median in the thousands means a grants program. Those are two kinds of organization, not two ends of one distribution.

But I want to be straight about what this costs, because a median is a blunt instrument.

Take the Pfizer Foundation. In our data it made 38,925 grants with a median of $250, and the sub-$1,000 grants say MATCHING GIFTS, VOLUNTEER PROGRAM, ANNUAL GIVING CAMPAIGN, exactly the payroll plumbing the band exists to remove. But the same foundation made 204 grants of $100,000 or more, totalling $127.3 million, and those read UKRAINE HUMANITARIAN RESPONSES, DISASTER RESPONSE - TURKEY/SYRIA EARTHQUAKE, TO REDUCE INFECTIOUS DISEASE IN CHILDREN UNDER 5 IN ETHIOPIA.

That is a bimodal funder: 79% of its grant count is employee matching, 78% of its dollars is a real strategic program. Keying on the median discards the whole organization. We accept that here, because those large grants go to international health and disaster INGOs rather than the small nonprofits we serve. But it is right by luck of who they fund, not by the logic the filter applies. Bimodal funders are a known hole.

Precomputing: cost is not popularity

The term that breaks a database is not the term users type most.

"Education" was searched twice in ninety days. It matches 760,062 grants, and the live aggregation took 52 seconds against a 30-second timeout. Match-set size, not query frequency, predicts cost, which means a cache built from search logs would have optimized precisely the wrong terms.

So we mined the corpus itself, offline, on a desktop machine. A term earns a place in the cache if either:

• mission_df ≥ 2,000: at least 2,000 organizations describe themselves with it, so it is vocabulary users actually type; or
• grant_df ≥ 10,000: it appears in enough grant text that a live search would be slow, so it is a term the cache exists to protect

That yields 3,144 terms, precomputed into 622,728 term-to-funder rows covering 43,172 funders, with the band and DAF filters already applied at build time. A companion totals table stores real corpus-wide counts, because the cached table keeps only the top 200 funders per term and would otherwise report educ as 200 funders instead of 59,120.

The cache key is a stem, not the word you typed. Normalization mirrors Postgres exactly (lowercase, English stopwords removed, Snowball stemmer), so "education" is stored and retrieved as educ, and "educational programs" hits the same entry.

(A pedantic detail: the intermediate aggregate holds 3,145 terms. One has no funders that survive the quality filters, so it exists in neither published table and quietly falls through to live search.)

What happens off the list

A cache of 3,144 terms answers a lot of searches and none of the specific ones. Search "adaptive sports veterans" and you are off the list by definition.

Those queries run live, and they run the same association: a funder is credited if its own grant text matches (baseline) or if one of its recipients' missions matches (attributed), unioned together. UNION, not UNION ALL. A grant matching both ways is one grant. The mission side searches 628,823 recipient missions joined back to grants on a normalized-name key that has to reproduce the offline normalizer exactly, which we verified on 500 of 500 sampled names against production.

So the cache is a speed layer. The association is the search.

The 106-second query, and why the index was innocent

When the live path first went up, a three-word phrase took 106 seconds.

The instinct is to add an index. The index already existed. It had been built hours earlier and the planner was simply refusing to use it.

Two rewrites fixed it, neither of which added data or indexes:

Materializing the mission CTE. Without MATERIALIZED, Postgres estimated the full-text match would return one row, decided an index was not worth it, and sequentially scanned all 628,823 missions. Forcing materialization: 1,562 ms → 27 ms.

Replacing a join with an array. This was the 106-second one. Written as a JOIN against the grants table, the planner chose a hash join over a sequential scan of all 11,747,807 grants, evaluating four nested regexp_replace calls on every single row, and ignored the recipient-key index entirely. It estimated 3.2 million matching rows from a set of 55 recipients. Collecting those recipients with array_agg and matching with = ANY((SELECT …)::text[]) gave the planner a shape it could reason about: 109,100 ms → 104 ms.

A thousand-fold speedup with no new data structures. The lesson we took: when a query is catastrophically slow and the index looks right, suspect the estimate before you suspect the storage.

Warm, off-list searches now run in 20 to 90 milliseconds. A first touch on cold pages can take a couple of seconds. Our shared_buffers is 256 MB against a working set over 3 GB, so a cold query pays for thousands of random heap reads. That is a provisioning question, not a query one, and it is honestly measured rather than hidden: the same phrase measured 3,183 ms cold and 58 ms on the next run.

What is still broken

48.4% of grants still resolve to no recipient mission, and what that costs is keywords, not funders. A funder with fifty grants needs one of them to resolve. What you lose is the narrow term: a foundation that made a single grant to an adaptive-sports nonprofit, plus forty to organizations we could not match, may never pick up "adaptive sports" even though it belongs there. So the failure mode is a specific keyword missing from a funder you can otherwise find, not a funder missing from your results. Coverage improves as name matching improves, and we are not done.

Roughly 1.84% of attributed keywords are wrong. That is the measured consequence of 93.4% name precision. If a funder looks like an odd fit for your search, that is the most likely reason.

Bimodal funders are excluded wholesale, as described above. A median-based band cannot see a foundation that runs two different programs.

The live path applies no quality filters. The DAF exclusion, the size band, and the 10-recipient minimum are baked into the cached table at build time. Off-list phrases do not get them. Right now, a search for "adaptive sports for veterans" returns six funders of which three are donor-advised-fund conduits, ranked first, second, and sixth: Donor Advised Charitable Giving, Fidelity Investments Charitable Gift Fund, and National Philanthropic Trust. Those pass money through rather than accept applications, and the cached path filters them out. So the same funder can be invisible on a cached phrase and top-ranked on an off-list one, purely because of how you phrased it. Fixing this needs per-funder statistics queryable at runtime, which we have not built yet.

One guard does not guard. Our search function declares SET statement_timeout = '30s'. It does not fire. Postgres arms the timer when a statement begins, and a function setting the value partway through cannot start a clock that was never started. We confirmed this directly with a function declaring a 2-second timeout running a 5-second sleep to completion. What actually protected users was a 30-second timeout set on the database roles themselves. The function-level setting reads like a safety net and is decorative.

None of this is a finished system. But it is a search that returns everything that matches, tells you when it fails, and can find a foundation that never wrote down what it cares about.

You can try it on keyword search.