grant-outreach-engine/docs/features/ingestion.md

Data-spine ingestion (Phase 1)

How grant opportunities and NH nonprofit orgs enter the outreach database. All ingestion runs as DBOS scheduled workflows in apps/outreach-worker; every source has a thin impure client, a pure normalize layer (unit-tested on fixtures), and a workflow that upserts through @novelpad/outreach-core/server actions — never inline DB calls.

Grant upserts key on grants.source_url; org registry upserts key on case-insensitive (name, city, state); IRS enrichment applies to org rows by id and re-bands ICP via icpBandForRevenue ($100K–$5M = primary).

Schedules

Workflow	Cron (UTC)	Source
ingestGrants	0 3 * * *	Grants.gov Search2
ingestPndRss	30 3 * * *	Philanthropy News Digest RFP feed
expireGrants	0 * * * *	(sweep: status='expired' past close_date)
enrichOrgs	0 5 * * *	ProPublica Nonprofit Explorer
embedGrants	15 4 * * *	gemini-embedding-001 over open-grant synopses → grants.synopsis_embedding (first paid AI call; ~pennies/batch)
ingestNhdojOrgs	0 4 1 * *	NHDOJ Charitable Trusts registry PDF
ingest990pf	0 6 2 * *	IRS BMF + e-file index + batch ZIPs → funders/funder_grants → synthesized foundation grants

Sources

Grants.gov

Source: apps/outreach-worker/src/sources/grants-gov/ — client.ts (fetch), normalize.ts (pure transform), pagination.ts (pure pagination arithmetic). Wired into apps/outreach-worker/src/workflows/ingest-grants.ts.

Cadence: Nightly, 0 3 * * * (03:00 UTC), registered as a DBOS scheduled workflow (ingestGrants, ExactlyOncePerInterval).

API: Public Grants.gov Search2 API, no key required.

• POST https://api.grants.gov/v1/api/search2 — enumerates opportunities, filtered to oppStatuses: 'posted' and nonprofit eligibility codes 12|13 (501(c)(3) and non-501(c)(3) nonprofits). Paginated via rows/startRecordNum.
• POST https://api.grants.gov/v1/api/fetchOpportunity — full detail (synopsis: description, applicant types, award amounts, response date) for a single opportunity id.
• Both endpoints wrap responses in an { errorcode, msg, data } envelope. Any non-2xx HTTP status or errorcode !== 0 throws immediately with a descriptive message — the client never silently drops or swallows an upstream failure.

Caps (per nightly run):

• Search enumeration: up to 1000 hits (SEARCH_HIT_CAP). Count logged via console.log.
• Detail fetch: up to 200 opportunities (DETAIL_FETCH_CAP), each a separate fetchOpportunity call with a ~250ms politeness delay between requests. When the search result set exceeds the cap, the excess is dropped for that run (picked up on a later run) and a console.warn states exactly how many opportunities were skipped — never a silent truncation.
• Upsert: batched in groups of 100 via serverInsertGrants.

Normalization rules (normalizeGrantsGovOpportunity, pure — no I/O):

• sourceUrl (the upsert key): https://www.grants.gov/search-results-detail/{opportunityId} — stable across re-crawls.
• funder: agencyDetails.agencyName → synopsis.agencyName → search hit's agency → an agency code fallback → literal 'Unknown federal agency' if nothing is present.
• synopsis: HTML-stripped from synopsis.synopsisDesc (block tags become newlines, common entities decoded); null if empty after stripping.
• awardFloor / awardCeiling / expectedAwardsCount: parsed from string-or-number wire values (handles "$1,500,000"-style formatting), rounded to whole dollars/counts; null on unparseable input.
• openDate / closeDate: parsed from the search hit's MM/DD/YYYY fields (falling back to the detail's postingDate/responseDate); any invalid, out-of-range, or calendar-rollover date (e.g. 02/30/2026) resolves to null rather than a garbage Date.
• eligibilityEntityTypes: trimmed, non-empty descriptions from synopsis.applicantTypes; null if none.
• geographicScope: always null — federal grants are national by default and Grants.gov has no reliable field to derive a narrower scope from; left unset rather than guessed.
• programAreas: null (not yet derived from this source).
• applicationEffortEstimate: 'unknown'; matchRequirement / applicationFormSupported: false; status: 'open'; source: 'grants_gov'; lastVerifiedAt: set to the normalization timestamp.

Philanthropy News Digest RSS

Status (2026-07-16): feed retired upstream. Every historical feed path (/feeds/rfps.rss and variants) now returns the site's HTML shell — PND is a Next.js app with client-side data and no <link rel=alternate> feeds. The workflow runs and upserts zero rows. Rework candidate: drive their internal JSON API / __NEXT_DATA__ instead. Low priority — PND was "cheap incremental coverage"; Grants.gov + NH state sources carry the corpus.

Nightly ingestion of the Philanthropy News Digest "RFPs" RSS feed (https://philanthropynewsdigest.org/feeds/rfps.rss, overridable via PND_RFP_FEED_URL).

• Client — src/sources/pnd-rss/client.ts: fetchPndRfpFeed(feedUrl) does a plain fetch, throwing on any non-2xx response.
• Normalize — src/sources/pnd-rss/normalize.ts: parsePndFeed(xml) is a pure function (fast-xml-parser's XMLParser, with isArray forcing <item> to always parse as an array so single-item feeds don't collapse to a bare object) that maps each RSS <item> to a NewGrantInput:
  • title → title; link → sourceUrl (items with no link are skipped — there's nothing stable to upsert-key on)
  • description → synopsis, after stripping HTML tags and decoding named + numeric entities
  • funder — best-effort regex over the title, then the description: captures the leading proper-noun phrase before an announcing verb (accepting / invites / seeks / announces / offers); falls back to 'See RFP'
  • closeDate — best-effort regex for Deadline: <date> / due <date> phrasing (both month-name and ISO-ish date forms); invalid or absent → null
  • applicationEffortEstimate: 'unknown', source: 'pnd_rss', openDate: null, lastVerifiedAt: new Date()
• Workflow — src/workflows/ingest-pnd-rss.ts: scheduled nightly at 30 3 * * * (ingestPndRss), following the same registration pattern as ingest-grants.ts (module-scope deps registry populated via setIngestPndRssDeps before DBOS.launch(), dual workflow+scheduled registration, globalThis double-registration guard). Steps: fetch (3 retries) → parse (pure) → upsert via serverInsertGrants in batches of 100.

ProPublica Nonprofit Explorer enrichment

Daily scheduled workflow (enrichOrgs, cron 0 5 * * *) that fills in IRS-derived fields — EIN, NTEE code, most-recent-filing total revenue, fiscal year-end month — for orgs discovered by the other ingestion sources but never resolved against the IRS.

Flow:

1. serverListOrgsNeedingEnrichment(db, { limit: 200 }) — orgs with no EIN and no revenue on file, oldest-updatedAt first.
2. For each org, sequentially (never forEach+async — the client's politeness delay depends on awaiting each call before starting the next):
   • searchOrganizations(org.name, org.state) against Nonprofit Explorer's search.json.
   • pickBestMatch({ name, city }, candidates) — pure, conservative fuzzy match (see below). Returns null rather than guess when ambiguous.
   • On no match: serverEnrichOrg(db, org.id, { ein: null, nteeCode: null, totalRevenue: null, fiscalYearEnd: null }) — stamps updatedAt so the org drops out of the next run's serverListOrgsNeedingEnrichment result without pretending to have real data. icpBandForRevenue still runs inside serverEnrichOrg and correctly returns 'unknown' for a null revenue.
   • On match: getOrganization(ein) for filing history, extractEnrichment(detail), then serverEnrichOrg(db, org.id, enrichment).
3. Per-org failures are caught, logged, and counted — one bad org doesn't kill the batch. If failures exceed 20% of the attempted batch, the workflow rethrows (systemic-failure signal for DBOS retry/alerting) after logging attempted/resolved/unresolved/failed counts.

Matching (src/sources/propublica/match.ts): normalizeOrgName lowercases, strips punctuation (apostrophes drop silently, other punctuation becomes a separator), strips legal-suffix/article noise tokens (inc, corp, the, of, nh, the phrase new hampshire), and collapses whitespace. pickBestMatch prefers an exact normalized-name match, falls back to token-set Jaccard similarity ≥ 0.8, and disqualifies any candidate whose known city differs from the target's known city — on both paths, since same-legal-name-different-town is exactly the ambiguous case worth refusing rather than guessing. Ties break by city match, then shortest Levenshtein distance on the normalized name. A wrong EIN silently poisons downstream revenue/ICP-band data with no cheap way to detect it later, so every ambiguous case resolves to null (org stays in next run's backlog) instead of a best-effort guess.

Extraction (src/sources/propublica/extract.ts): extractEnrichment zero-pads the numeric EIN to 9 digits, passes through ntee_code (null-safe), and — from filings_with_data — picks the filing with the highest tax_prd_yr for totalRevenue and derives fiscalYearEnd as the zero-padded MM from that filing's tax_prd (YYYYMM, e.g. 202306 → '06'). Empty filing history yields totalRevenue: null, fiscalYearEnd: null.

Wiring: apps/outreach-worker/src/main.ts imports setEnrichOrgsDeps from ./workflows/enrich-orgs.js (which also registers the workflow/scheduled function as an import side effect) and calls setEnrichOrgsDeps({ db }) before DBOS.launch(), alongside the existing setIngestGrantsDeps/setExpireGrantsDeps calls.

NHDOJ Charitable Trusts registry

Monthly re-scan of the NH Department of Justice Charitable Trusts Unit's registry PDF — a roster of registered charitable organizations, not a grants feed. This is the org side of the pipeline: it upserts into orgs (keyed on case-insensitive (name, city, state), same as the ProPublica enrichment source), not grants. A brand-new registrant (inserted: true) is itself a signal worth tracking downstream — it's a segment often actively seeking first-time funding.

• Extract — src/sources/nhdoj/extract-pdf-text.ts: extractPositionedText(pdfBytes) is the only impure layer. Uses pdfjs-dist's legacy Node build (pdfjs-dist/legacy/build/pdf.mjs) with getDocument({ data, useSystemFonts: true }), returning one PositionedTextItem[] ({ str, x, y }, taken from each text item's transform matrix [4]/[5]) per page.
  • Node-runtime caveat: no disableWorker option exists on DocumentInitParameters, and none is needed — the legacy build self-detects isNodeJS at module load and unconditionally sets PDFWorker.#isWorkerDisabled = true, falling back to an in-process "fake worker" automatically (see PDFWorker#_initialize in the bundled pdf.mjs). No GlobalWorkerOptions.workerSrc setup is required for text extraction.
• Parse — src/sources/nhdoj/parse-registry.ts: pure row reconstruction over PositionedTextItem[][], entirely unit-testable on synthetic fixtures (no real PDF needed).
  • reconstructRegistryRows(pages, options?) groups items into visual lines by y (±2pt tolerance, configurable), buckets each line's items into 3 columns (name/city/status) by x-position, and stitches multi-line org names back together — a continuation line has text only in the name column. Column boundaries are inferred per page from that page's header row token x-positions (Organization/Name, City, Status); pages whose header doesn't repeat (e.g. page 2+) reuse the last-inferred boundaries, or accept an explicit columnBoundaries: [nameX, cityX, statusX] override. Throws if a page has data but no boundaries can be determined at all — a layout change should fail loudly, not misparse silently.
  • normalizeRegistryRows(rows) maps free-text status onto 'good_standing' (good standing / current / active) | 'lapsed' (lapsed / delinquent / suspended / expired / revoked) | 'unknown' (anything else, including blank), collapses whitespace in names/cities, and drops non-registrant artifact rows (repeated header, page-number footers, Page X of Y, NHDOJ letterhead).
• Workflow — src/workflows/ingest-nhdoj-orgs.ts: scheduled monthly at 0 4 1 * * (ingestNhdojOrgs), same registration pattern as ingest-grants.ts (module-scope deps registry via setIngestNhdojOrgsDeps, dual workflow+scheduled registration, globalThis guard). Steps: fetch PDF bytes from NHDOJ_REGISTRY_PDF_URL (3 retries) → extract + parse (pure, not a DBOS step) → serverUpsertOrgFromRegistry per row in a plain for...of loop (sourceRegistry: 'nhdoj_charitable_trusts') → console.log of rows parsed / upserted / newly-inserted counts.
  • Config gap ≠ outage: if NHDOJ_REGISTRY_PDF_URL is unset, the workflow logs a console.warn and returns without throwing. NHDOJ has no stable URL for the registry PDF — it changes whenever they republish — so a hard failure here would page on-call for a config gap rather than a real problem.
  • Not yet wired into apps/outreach-worker/src/main.ts — the module registers itself as a side effect of being imported (per the pattern above), but main.ts needs an explicit import (for registration-before-launch ordering) plus a setIngestNhdojOrgsDeps({ db }) call before DBOS.launch():

    import { setIngestNhdojOrgsDeps } from "./workflows/ingest-nhdoj-orgs.js";
    // ...
    setIngestNhdojOrgsDeps({ db });
    

IRS 990-PF funder precedent

Private foundations almost never post an open RFP — Grants.gov/PND/NHDOJ never see them. This vertical instead builds a "precedent index": it discovers NH-registered private foundations from the IRS Business Master File (BMF), cross-references the IRS e-file index to find each one's latest 990-PF filing, parses grants actually paid to NH nonprofits out of that filing's XML, and — for foundations with enough NH giving history — synthesizes a grants row summarizing that history as a lead ("this foundation funds orgs like you"). Unlike every other source in this doc, it upserts into both funders/funder_grants (the raw precedent data) and grants (the synthesized lead), keyed by grants.source_url like the rest.

Source: apps/outreach-worker/src/sources/irs-990pf/ — bmf.ts (BMF fetch + parse), index-csv.ts (e-file index fetch + filter), parse-990pf-xml.ts (990-PF XML → grants paid + application info), zip.ts (batch-ZIP selective extraction). Wired into apps/outreach-worker/src/workflows/ingest-990pf.ts.

Cadence: Monthly, 0 6 2 * * (06:00 UTC on the 2nd), registered as a DBOS scheduled workflow (ingest990pf, ExactlyOncePerInterval).

Data sources, all Akamai-fronted (same browser-header workaround as fetchNhdojRegistryPdf):

1. https://www.irs.gov/pub/irs-soi/eo_nh.csv — BMF state extract (~1.6MB). PF_FILING_REQ_CD === '1' identifies 990-PF filers.
2. https://apps.irs.gov/pub/epostcard/990/xml/{year}/index_{year}.csv — e-file index (~28MB). Filtered in a manual line-by-line scan (no full-file line array, no giant intermediate row array) to RETURN_TYPE === '990PF' rows whose EIN is in the BMF-discovered target set, keeping only the latest TAX_PERIOD per EIN.
3. https://apps.irs.gov/pub/epostcard/990/xml/{year}/{XML_BATCH_ID}.zip — batch ZIPs (100-400MB each), containing {OBJECT_ID}_public.xml per filing. Streamed straight to a temp file (os.tmpdir(), never buffered whole in the JS heap during download), then read back once and selectively extracted via fflate's unzipSync({ filter }) — only the wanted entries are inflated. The temp file is deleted after each batch (finally, so a mid-batch failure still cleans up).

Caps (per monthly run):

• Batches processed: up to 4 (IRS_990PF_MAX_BATCHES_PER_RUN), the batches with the most target-EIN hits first. Deferred batches are logged by id + hit count via console.warn — never a silent truncation. No processed_batches checkpoint is needed: re-parsing is idempotent (serverReplaceFunderGrantsForYear replaces per tax year) and the target object-id set shrinks on its own as funders' latestTaxYear/latestObjectId advance.
• Skip filter: an (EIN, filing) pair is skipped entirely — no download credited against it — when the e-file index's selected OBJECT_ID for that EIN already matches the funder's recorded latestObjectId (serverListFunderLatestObjectIds, packages/outreach-core/src/funders/queries/list-funder-latest-object-ids.server.ts), i.e. nothing changed since the last run.
• Years scanned: current + previous (IRS_990PF_YEARS, comma-sep override) — a filing's e-file index year need not equal its TAX_PERIOD year, so each selected filing tracks the actual index year its batch ZIP lives under (IndexedFiling.indexYear in the workflow), not a value re-derived from TAX_PERIOD.

XML parsing (parse990PfXml, pure — no I/O): removeNSPrefix: true handles filing-software-dependent namespace prefixes. Grants paid (SupplementaryInformationGrp/GrantOrContributionPdDurYrGrp[]) resolve the recipient name from RecipientBusinessName/BusinessNameLine1Txt, falling back to RecipientPersonNm; amounts round to whole dollars; a row with neither name field is dropped (no usable recipient identity). Application info (ApplicationSubmissionInfoGrp) captures whatever of RecipientNm/FormAndInfoAndMaterialsTxt/SubmissionDeadlinesTxt/RestrictionsOnAwardsTxt is present into a plain object, null if none are. Tax year prefers ReturnHeader/TaxYr, falling back to the calendar year of TaxPeriodEndDt, falling back (in the workflow, not the pure parser) to the filing's TAX_PERIOD (YYYYMM) when the XML has neither. Every group defaults to empty/null on absence rather than throwing — a missing Part XV section is normal for a foundation with no formal application process.

Synthesis (buildSynthesizedGrant, pure): for every funder with ≥2 grants paid into NH (serverListFunderSynthesisData), builds one grants row: source: 'irs_990pf', funderEin set, sourceUrl the funder's ProPublica Nonprofit Explorer page (stable, human-followable, and distinct from the funder's own funders.ein upsert key so re-running never collides with a same-funder public-RFP row). synopsis is composed prose (location, in-state grant count + typical/median amount, up to 8 recent grant purposes, application-info sentence when present). awardCeiling/awardFloor derive from the aggregated median/max in-state amounts. geographicScope is deliberately left null — the synthesis query only sees grants paid into NH, not the funder's total giving footprint, so there's no way to tell from this data whether NH is an exclusive restriction; asserting it would fake a hard geography gate the data doesn't support. closeDate: null (rolling), applicationEffortEstimate: 'unknown', status: 'open'.

Workflow (ingest-990pf.ts): same registration pattern as ingest-grants.ts (module-scope deps registry via setIngest990pfDeps, dual workflow+scheduled registration, globalThis guard, runIngest990PfNow() accessor for run-once.ts). Steps: fetch BMF (3 retries) → upsert every parsed foundation as a funders row (per-row step, same pattern as ingest-nhdoj-orgs.ts) → fetch each target year's e-file index (3 retries, per-year) → pure selectFilings → skip-filter against recorded latestObjectId → group into batches, sort by hit count, cap → per batch (2 retries — a 100-400MB download is expensive to retry 3x): download, extract, parse, serverUpsertFunder (filing-derived fields) + serverReplaceFunderGrantsForYear per filing → synthesis step. Every stage logs its counts via console.log/console.warn.

Core addition: packages/outreach-core/src/funders/queries/list-funder-latest-object-ids.server.ts (serverListFunderLatestObjectIds) — the one core addition this vertical needed, following the existing action/query barrel pattern; everything else in packages/outreach-core/src/funders/ (the funders/funder_grants schema, serverUpsertFunder, serverReplaceFunderGrantsForYear, serverListFunderSynthesisData) pre-existed this vertical.

First-run field findings (2026-07-16)

• NHDOJ registry: 8-column layout (Reg. No. | Charity Name | Address | City | State | Zip | Status | Report Due), single-letter statuses (G/X/S legend), includes out-of-state charities registered to solicit in NH. Parsed 13,709 registrants → 13,632 orgs (6,266 NH). Reg. No. is the stable upsert key (orgs.registration_number). mm.nh.gov sits behind Akamai: bare curl gets 403; Node fetch with browser-like headers passes (client sends them). NHDOJ_REGISTRY_PDF_PATH overrides the URL for supervised runs.
• ProPublica: a state-scoped search with zero hits returns 404, not an empty list — the client maps 404 → no candidates (org resolves unresolved). Before the fix this tripped the 20% systemic-failure breaker.
• Grants.gov: first pass ingested 200 opportunities (search cap 1000 / detail cap 200); the hourly expiry sweep correctly expired a same-day deadline immediately.
• Enrichment queue prioritizes NH + good-standing orgs before the out-of-state tail; ~60% of NH orgs resolve an EIN per batch, the rest record an all-null attempt.