@pipeworx/sba-loans

Connect: https://pipeworx.io/mcp — every tool in the catalog, including @pipeworx/sba-loans’s. Install: one-click buttons

No MCP client? Skip the connection: POST https://gateway.pipeworx.io/v1/tools/search_packs {"query":"..."} to find a tool below, GET /v1/tools/<name> for its schema, POST the same URL with arguments for the data — see For AI agents.

Tools: 6

U.S. Small Business Administration loan data — “did this business get an SBA loan, from whom, how much, and did it pay it off” — from SBA’s own FOIA CSV extracts.

• 7(a) + 504 (fleet #1921), refreshed quarterly.
• PPP (fleet #2869): business borrowers only, as of 2024-09-30.

Tools

• sba_loan_lookup(borrower_name, state?, program?, limit?) — a borrower’s loan(s): amount, lender, approval date, status, charge-off detail.
• sba_largest_loans(program?, naics_code?, state?, lender_name?, limit?) — largest loans by any combination of filters.
• sba_lender_league_table(program?, state?, naics_code?, limit?) — lenders ranked by loan count and dollar volume.
• sba_charge_off_aggregate(group_by, program?, state?, naics_code?, limit?) — charge-off rate/amount grouped by naics | state | lender | program.
• ppp_loan_lookup(borrower_name, state?, zip?, limit?) — a business’s PPP loan(s): amount, jobs_reported, NAICS, servicing/originating lender, approval date, first or second draw, forgiveness amount and date. Names match from their FIRST word ("Sumter Coatings" finds SUMTER COATINGS, INC.); with zip, a word inside the name matches too.
• ppp_aggregate(group_by, state?, order_by?, limit?) — PPP totals by naics | state | lender (servicing lender), nationally or within one state.

Every PPP response carries data_as_of and a coverage line stating how many loans were excluded as individual borrowers. If the mirror is not loaded (or mid-reload) the PPP tools fail with ppp_not_loaded — they never answer “no loan found” from an empty table.

Auth

Keyless. The gateway supplies data access to this pack automatically. No caller-facing key. The PPP tools work only through the hosted gateway; a standalone build says so (ppp_unavailable) rather than returning nothing.

PERSONAL-DATA RULE (mandatory, harris-county precedent)

Many 7(a)/504 (and, when built, PPP) borrowers are sole proprietors — individual humans, not companies. Street addresses (borrower AND lender-side: BorrStreet, BankStreet, CDC_Street, ThirdPartyLender_Street) are dropped at ingest — scripts/sba-loans-transform.py never reads those source columns into the row it emits, so they cannot reach a stored row or a response no matter what the source CSV contains. Kept: business/borrower name, city, state, ZIP, NAICS, lender identity, amounts, dates, status. This is the same rule applied to Harris County civil dockets (supabase/migrations/dockets_005_harris_drop_pii.sql): the standing public-data sourcing rule covers bulk public loan records, not bulk redistribution of individuals’ home addresses.

PPP adds two rules (fleet #2869)

scripts/ppp/transform.py reads a whitelist of columns, so the PPP file’s street columns AND its owner-demographic columns (Race, Ethnicity, Gender, Veteran) are never read. It then DROPS individual borrowers: SBA-redacted names (Exemption 6), sole proprietors / self-employed / independent contractors / single-member LLCs whose name carries no business marker, <person> DBA <business>, and any other borrower whose name is person-shaped and starts or ends with a given name. Measured on 413,167 sampled rows: 70% kept, 120,362 individual + 3,601 person-shaped + 33 redacted dropped. Every run prints its own counts.

Data sources

• https://data.sba.gov/dataset/7a-504-foia — dataset page. Portal trap: the CKAN root /dataset/7-a-504-foia (with the hyphen) 404s; the real slug has no hyphen between “7” and “a”.

• Direct CSV downloads (quarterly asof_YYMMDD stamp in the filename): https://data.sba.gov/sites/default/files/uploaded_resources/FOIA_7a_FY2020_Present_asof_<stamp>.csv, ..._FY2010_FY2019_..., and FOIA_504_FY2010_Present_asof_<stamp>.csv. Two more decades exist (7(a) FY1991-1999, FY2000-2009; 504 FY1991-2009, ~520MB more) — not ingested in phase 1 to keep the first load disciplined; easy to add later via the same transform script if demand shows up.

• No developer API — this is a bulk-CSV-only source.

• PPP: https://data.sba.gov/dataset/ppp-foia — .../distribution/SBA-OCA-2022-07-001/public_150k_plus_<stamp>.csv and public_up_to_150k_{1..12}_<stamp>.csv (5.33 GB total, stamp 240930).

Ingest harness

• supabase/migrations/195_sba_loans.sql — unified sba_loans table (both programs; 504’s CDC and 7(a)‘s bank are normalized into lender_name/lender_city/lender_state, with 504’s separate ThirdPartyLender kept in its own columns rather than forced into the same slot) + RPC functions the tools above call.

• scripts/sba-loans-transform.py — per-program CSV → unified-schema CSV, computing a deterministic loan_key (there is no natural unique loan id in the source — LocationID is a lender/office code reused across unrelated loans, confirmed live 2026-09-13, not a loan identifier). loan_key hashes the fields that identify a specific loan (program, location_id, borrower, approval date, amount, NAICS) and excludes the fields SBA revises over a loan’s life (status, PIF/charge-off dates, charge-off amount) — so a quarterly re-ingest updates a loan’s outcome in place via ON CONFLICT (loan_key) DO UPDATE, instead of duplicating the row.

• scripts/sba-loans-upsert.sh — download → transform → batched staging-table drain into sba_loans (same crash-safe, disk-safe shape as scripts/usaspending-upsert.sh: batches commit independently, a failed run drops its staging table rather than leaving it to grow Supabase disk forever).

• .github/workflows/sba-loans-refresh.yml — quarterly cron (workflow_dispatch for manual/backfill runs) on a GH-hosted runner, because this needs a pooler connection string held as a GitHub Actions secret for the psql \copy, not a Cloudflare Worker.

• PPP: .github/workflows/ppp-refresh.yml (manual; mode=build is a dry run, mode=load replaces the PPP tables) runs scripts/ppp/refresh.sh: download → derive-given-names.py → transform.py → build-sqlite.py (schema.sql, pre-computed ppp_agg) → chunked import and row-count verification. scripts/ppp/test_transform.py runs first and stops the ingest if the personal-data rule regresses.

Gotchas

• 7(a) and 504 spell “paid in full” differently in the source: "P I F" (7(a), with spaces) vs "PIF" (504, no spaces). sba_charge_off_aggregate only depends on the CHGOFF code, which IS consistent across both programs (verified live 2026-09-13: 300/2,050/751/3,313 for CHGOFF/EXEMPT/CANCLD/“P I F” in a 7(a) sample; 177/1,036/926/3,790 for the 504 equivalents) — but don’t assume other status strings line up between programs.
• 504 has no SBAGuaranteedApproval, InitialInterestRate, FixedorVariableInterestInd, RevolverStatus, or SoldSecMrktInd — those columns are NULL for every 504 row by design (7(a)-only fields), not a data-quality issue.
• Do not fold this pack into finra, sec-adv, or any other financial-data pack — it is a different agency, different licence-free public-domain data, and its own table.
• PPP jobs_reported is self-reported on the 2020-21 application. It is a dated headcount claim, not a current or audited figure.
• PPP lookups are prefix matches on a normalized name (accents folded, & → AND, leading THE and trailing Inc/LLC/Co removed). The same normalization lives in transform.py and src/ppp.ts; scripts/ppp/name-norm-fixtures.json pins both.

Tools

• sba_loan_lookup — Look up a small business’s SBA 7(a) or 504 loan(s) by borrower name. Returns individual loan records: amounts, lender, approval date, status (P I F / PIF = paid in full, CHGOFF = charged off, CANCLD =
• sba_largest_loans — Largest SBA 7(a)/504 loans, ranked by gross approval amount, filterable by program, NAICS code, borrower state, and/or lender name. Answers “what are the biggest SBA loans in NAICS X” or “largest SBA
• sba_lender_league_table — Lender league table — ranks lenders by SBA loan count and total dollar volume. For 7(a) loans “lender” is the originating bank; for 504 loans it is the CDC (Certified Development Company) that books t
• sba_charge_off_aggregate — Charge-off rate and dollar amount, aggregated by NAICS code, state, lender, or program. Answers “which industries have the highest SBA loan charge-off rate” or “charge-off amount by state”. charge_off
• ppp_loan_lookup — Look up a business’s Paycheck Protection Program (PPP) loan(s) by borrower name. Returns each loan’s approved amount, jobs_reported (the headcount the business stated on its 2020-21 application), NAIC
• ppp_aggregate — Paycheck Protection Program (PPP) totals grouped by NAICS industry code, borrower state, or servicing lender: loan count, total approved, loans and dollars forgiven, forgiveness rate, and total jobs r