Build log: how GrantCue ingests grant data

This grant data pipeline build log covers how GrantCue ingests grant opportunities: where they come from, how one normalizer handles portals that share nothing, and how the catalog decides what actually changed. Sources are read on a rolling schedule, normalized through per-source adapters, hashed, and upserted into a single table. Deduplication happens at three separate points, and every one of them earned its place.

Key facts

• GrantCue reads the federal Grants.gov API plus 31 live state sources covering 28 states, each behind its own adapter class.
• Change detection is a SHA-256 hash over 14 source-owned fields, so downstream enrichment never looks like a source edit.
• Every write to the catalog goes through one resolver that returns insert, update, or skip.
• Duplicates get recorded, never merged automatically, and retiring a grant that vanished from its portal is opt-in per source.
• Jobs that download and parse large archives run on a local worker instead of a serverless function.

Where does the grant data come from?

It comes from the federal Grants.gov API, 31 live state sources, and IRS Form 990-PF filings that are treated as historical funder research rather than open opportunities. Grants.gov is a real API with real pagination. Most of the state sources are not. They are SharePoint OData lists, a Coveo search endpoint, a ServiceNow portal, a Drupal site, WordPress pages, ASP.NET Web Forms tables, and a few HTML grids that were never meant to be read by anything except a browser.

The coverage doc I keep in the repo also lists 22 states that got evaluated and set aside because there is nothing clean to read. Minnesota has a central portal, but its list sits behind a bot wall. Wyoming publishes through a Power BI dashboard. Several states are simply fragmented across agencies with no central feed at all. I record those outcomes in the same matrix as the wins so I never re-derive a conclusion from a partial sample.

How does one pipeline handle that many different portals?

Every source implements the same small abstract interface: fetch a page, fetch a single record, normalize a record. The base class owns the parts nobody should rewrite per state, including the pagination loop, a rate-limit delay derived from the source's configured requests per minute, date parsing, text cleanup, and number parsing. The date parser returns nothing for values like "rolling", "ongoing", "continuous", "tbd", and "varies" instead of pretending they are dates, which is the kind of small decision that saves a lot of downstream nonsense.

Where portals share a platform, one base covers several states. A single eCivis Grants Network adapter serves Arizona, Rhode Island, and Indiana with nothing but a tenant token swapped. One SharePoint base serves Maryland and West Virginia. One WebGrants storefront base serves Iowa, Missouri, and North Dakota. That pattern is the only reason coverage grew as fast as it did, and it is the same scheduled data work shape I build for clients.

Scheduling is a dispatcher, not a monolith. A cron runs every 20 minutes and picks up to five sources whose next sync time is null or in the past. The run has a wall-clock budget, and any source that would start with less than 90 seconds remaining gets deferred rather than half-run. A source that fails or times out gets a two-hour backoff written onto its next sync time. Timeouts abort cooperatively through an AbortSignal, so a stalled adapter stops paginating instead of continuing to write in the background after the request gave up on it.

How does GrantCue decide whether a grant changed?

By hashing it. The content hash is SHA-256 over 14 fields the source owns: title, agency, description, opportunity number, three dates, four amount and count fields, eligibility, funding category, and the cost-sharing flag. Text is lowercased and whitespace-collapsed before hashing, and numbers are stripped of formatting, so cosmetic churn upstream does not register as a change.

Enrichment status and embeddings are deliberately excluded from that hash. If they were included, every enrichment pass would make the row look freshly edited by its source. The field list is also fixed on purpose. Adding one field would change every stored hash at once and trigger a full re-sync of the entire catalog, which is a comment I left in the file specifically so future me does not casually append a field.

The resolver compares the new hash to the stored one and returns insert, update, or skip. Skip rows get their last-synced timestamp bumped and nothing else. The resolver also merges defensively: a detail-only refresh that comes back without a title falls back to the stored title before falling back to the external ID, and description and agency work the same way. Without that, one bad refresh would quietly wipe good data.

How are duplicates caught?

At three points, for three different reasons.

The first layer exists for a blunt reason: Postgres cannot update the same row twice in one statement, so a feed that repeats an ID fails the whole batch unless the second copy is dropped first. The second is the ordinary case and does the vast majority of the work. The third runs after the write. A batch function scores an identical content hash as a certain match, and a same-agency title with trigram similarity above 0.7 as a probable one, then records both in a separate duplicates table. Nothing is merged or deleted automatically, because a wrong merge is much harder to notice than a visible duplicate.

What happens when a grant vanishes from its portal?

By default, nothing. Retiring records is opt-in per source with three modes: off, shadow, and enforce. Only a complete, error-free full snapshot is eligible in the first place. Pagination errors, duplicate external IDs, unexpectedly empty feeds, and large count drops all skip the lifecycle decision entirely. In shadow mode I watch the missing-candidate counts against the live portal without acting on them. In enforce mode a grant has to be absent across a configured number of consecutive complete snapshots before it is marked inactive with a reason of source withdrawn, and a later snapshot containing it reactivates it.

Every one of those guardrails exists because a partial scrape and a portal that deleted everything look identical from inside the code. A separate job runs every six hours and counts five specific integrity problems, including grants marked enriched but missing a description or agency, expired grants still flagged active, and any source whose last three sync results all failed.

What broke?

Three failures cost real time, and all three are the same category: the pipeline appeared to work.

SharePoint pagination. SharePoint OData ignored the skip parameter and kept returning the first page, so Maryland's list looped re-fetching page one until the sync timed out with zero records ingested. West Virginia fit in a single page, so it passed cleanly and hid the bug. The fix was to follow the next-link URL verbatim and stop when it is absent, with a page-count guard on top.

Silent truncation. The lookup for existing catalog rows used one query that the database client caps at 1000 rows by default. On Grants.gov that meant several hundred existing grants came back as not found, were treated as new inserts with fresh IDs, and the upsert then tried to overwrite live primary keys, breaking foreign keys on a related table. Chunking that lookup into batches of 500 fixed it.

Null IDs in mixed batches. When one upsert batch mixed rows that carried an ID with rows that did not, the REST layer normalized every row to the same column set and sent a null ID for the inserts, violating a not-null constraint. The practical effect was that no new grant from a source could be synced in after the initial load. Generating the ID client-side made the batch uniform. Debugging that one felt a lot like the back office build where the failure was in the write path rather than the read path.

What is still rough?

Plenty, and pretending otherwise would make this a worse log. Only Grants.gov has a follow-up enrichment pipeline, so rows from other sources that arrive without a description or agency are marked exhausted immediately rather than sitting pending forever with nothing to process them. That is honest bookkeeping, not a good outcome.

Several state sources are agency-scoped rather than statewide, including a state education department and a state criminal justice agency, and the docs say so in plain terms so nobody reads coverage as more than it is. Massachusetts syncs list-only because traversing detail pages triggers robot challenges. New York is list-only until detail discovery is reliable. And the heaviest job, streaming and parsing IRS archives, runs on a Windows machine under Task Scheduler because that work exceeds what a serverless request can do on time and memory. That works for one operator. It is not an answer I would ship to a customer, and moving it to a permanent scheduler is documented but not done.

The general shape here scales down well. Most businesses do not need 31 adapters, but the same ideas apply to any internal tool that pulls from an outside system: normalize early, hash to detect change, make writes idempotent, and never let a partial read look like a complete one. That is the same reasoning behind replacing a spreadsheet with a real system.

Common questions

Why not write one generic scraper instead of dozens of adapters?

Because the portals share no structure. One returns OData JSON, another returns a Coveo search payload, another returns server-rendered ASP.NET tables. What they can share is the contract underneath: fetch, normalize, and emit the same row shape. That is what the base class enforces, and it is why a state that matches an existing base is roughly a one-hour add.

How often does each source sync?

Each source carries its own frequency, and a dispatcher picks whichever sources are due every 20 minutes, up to five per run. Nothing runs on a fixed daily schedule anymore, which spreads load across the day and keeps one slow portal from blocking everything behind it.

Does the pipeline ever delete grants?

No. The strongest action available is marking a grant inactive with a recorded reason, and that only happens for sources explicitly set to enforce mode after the grant is absent across several consecutive complete snapshots. Duplicates get recorded in their own table rather than removed.

Why does part of it run outside the cloud?

Downloading and parsing large source archives can exceed a serverless request's time and memory limits. Those jobs run on a local worker that streams archives without storing them on disk and claims work in bounded batches with expiring leases, so a crash mid-run is recoverable rather than corrupting.

Could this approach work on a much smaller dataset?

Yes, and it usually should. The hashing, the single write path, and the insert/update/skip decision are worth having even with one data source, because they are what let you re-run an import safely. Most of the complexity in GrantCue comes from source count, not from the pattern itself. If you want to see what else I have built with it, the portfolio has more.