@pipeworx/h1b

Connect: https://pipeworx.io/mcp — every tool in the catalog, including @pipeworx/h1b’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: 3

US H-1B visa sponsorship and wage data from DOL Labor Condition Application (LCA) disclosures — “does company X sponsor H-1B”, “what does role Y pay at company X”, “who sponsors the most data engineers in Austin”.

Tools

• h1b_employer_sponsorship(employer, year?) — filing count, salary range, top titles/locations for an employer.
• h1b_salary(job_title, employer?, city?, year?, limit?) — real disclosed base-salary stats + a sample of records for a role.
• h1b_top_sponsors(job_title, city?, year?, limit?) — employers ranked by filing count for a role, with median salary.

Argument names/shapes are unchanged from before fleet #2514 — only where the answer is served from changed.

Two-tier source (fleet #2514)

Primary: DOL’s own LCA disclosure files for recent federal fiscal years. Older years come from h1bdata.info. Every DOL-sourced response carries data_as_of.

Fallback: h1bdata.info, a third-party aggregator of the same DOL data. Used only when the requested year is outside the local mirror’s loaded window, or the DB is unreachable — this pack must never regress below its pre-#2514 behavior while the mirror is partially backfilled. Every response carries a source field saying which one actually answered it; the pack never silently swaps sources without saying so.

A fallback zero is never presented as a confident “no” (fleet #3198). If the DB read itself failed this call (no credentials, a timeout, or a transient error — source_status: "primary_unreachable"), a zero-row h1bdata.info answer returns sponsors_h1b: null and a message saying the primary source was never actually consulted, not disproven. Only when the primary source answered fine but the year is genuinely outside its loaded window (source_status: "out_of_window") does a fallback zero stand as a real (if third-party, lag/rename-prone) “no”. dbRpc retries once immediately on a transient failure before falling back at all, so a single network blip doesn’t by itself demote a mirror-covered query to the weaker source. h1b_employer_sponsorship’s matched_employers is also now ranked exact-match first (then name-contains variants), each tagged name_match, since the employer-name match is a plain substring and can legitimately pull back more than one distinct legal name.

Why a mirror at all: the live h1bdata.info scrape caps at 20,000 raw HTML table rows per query (fetchLcaLive’s safety cap) — for any employer or job title with more filings than that, salary stats and top_sponsors rankings were being computed from a silently truncated, non-representative slice. For DOL-sourced years, top_sponsors aggregates over every matching row, with no such cap (see total_all_filings in 222_h1b_lca_disclosures.sql’s h1b_top_sponsors RPC, a window-function sum computed before the row-limit).

Fiscal year, not calendar year, on the local mirror. DOL’s files are organized by federal fiscal year (Oct 1 - Sep 30); year is passed straight through as that fiscal year when served locally. h1bdata.info’s own “year” filter is a different, unverified semantic (most likely calendar year) — a query answered by each path for the same year value is not guaranteed to cover the identical 12 months. The response’s note/source fields say which happened; this is a real, disclosed difference, not papered over.

PERSONAL-DATA RULE (mandatory, local-copy rule — task #2514)

DOL’s own record layout (LCA_Record_Layout_FY2024_Q4.pdf, read in full 2026-09-29) carries named-individual contact fields that are never read into the mirror — scripts/h1b-lca-transform.py uses an ALLOWLIST of source columns (not a blocklist), so a column DOL adds later is excluded by default:

• EMPLOYER_POC_{LAST,FIRST,MIDDLE}_NAME, EMPLOYER_POC_EMAIL, EMPLOYER_POC_PHONE — the employer’s named point of contact.
• AGENT_ATTORNEY_{LAST,FIRST,MIDDLE}_NAME, AGENT_ATTORNEY_EMAIL_ADDRESS, AGENT_ATTORNEY_PHONE — the attorney/agent representing the employer.
• PREPARER_{LAST,FIRST,MIDDLE}_NAME, PREPARER_EMAIL — whoever filled out the form.

DOL’s file already excludes the foreign worker’s own name/address (DOL’s own cover note names only “Attorney’s FEIN and Attorney’s State Bar Number” as withheld — no worker PII was ever in this dataset). Kept: EMPLOYER_NAME / TRADE_NAME_DBA and employer address down to city/state/country (no street), plus job/wage/worksite fields — this identifies the employer making a mandatory public disclosure, not a private individual.

Data source

• https://www.dol.gov/agencies/eta/foreign-labor/performance — DOL OFLC performance-data page, links each fiscal year’s disclosure file.
• Verified live 2026-09-29 (not documented by DOL, reverse-engineered from decision_date distributions — the record-layout PDF’s cover text is WRONG about this for closed years):
  • The current, still-open fiscal year’s latest quarterly file is cumulative from Oct 1 of that FY through the end of the named quarter (FY2026_Q3.xlsx spans Oct 2025-Jun 2026). Lives at https://www.dol.gov/media/LCA_Disclosure_Data_<label>.xlsx. Load only the single latest file for an open year.
  • Once a fiscal year closes, DOL’s archived quarterly files for that year are each discrete — decision_date falls only within that one quarter (verified: FY2025_Q3.xlsx = Apr-Jun 2025 only, FY2025_Q4.xlsx = Jul-Sep 2025 only — NOT the full year, despite the record-layout PDF literally stating “Reporting Period: October 1 … through September 30” on its cover page). Lives at https://www.dol.gov/sites/dolgov/files/ETA/oflc/pdfs/LCA_Disclosure_Data_<label>.xlsx. A closed year needs all four of its quarter files for full coverage; they do not overlap.
• No auth, no rate limit observed. US federal administrative data — public domain.

Ingest harness

• supabase/migrations/222_h1b_lca_disclosures.sql — h1b_lca_disclosures table + h1b_lca_ingest_runs bookkeeping + three RPCs (h1b_lca_search, h1b_top_sponsors, h1b_lca_coverage). RLS on; anon/authenticated have no grants; service_role only.
• scripts/h1b-lca-transform.py — streams one .xlsx file row-by-row via openpyxl(read_only=True) (never buffers a whole workbook — a full fiscal year runs 80-250MB uncompressed), allowlist-selects the non-personal columns, and computes base_salary_annual from WAGE_RATE_OF_PAY_FROM/WAGE_UNIT_OF_PAY (Hour×2080, Week×52, Bi-Weekly×26, Month×12, Year×1).
• scripts/h1b-lca-upsert.sh — per file: download (tries the current-year URL, falls back to the closed-year archive URL), transform, \copy into a staging table, then INSERT ... ON CONFLICT (case_number) DO UPDATE (CASE_NUMBER is DOL’s own stable per-application id — no synthetic key needed).
• .github/workflows/h1b-lca-refresh.yml — workflow_dispatch only (deliberately no schedule: — Bruce’s “supa stagger” rule queues big Supabase loads one at a time, overnight, on purpose; GitHub’s schedule: trigger has also been unreliable fleet-wide since ~2026-09-09). Default files input is the “latest 8 quarters” the task asked for, with verified zero overlap and zero gap: 2026:FY2026_Q3 (cumulative, = FY2026 Q1+Q2+Q3) + 2025:FY2025_Q1,2025:FY2025_Q2,2025:FY2025_Q3,2025:FY2025_Q4 (four discrete quarters) + 2024:FY2024_Q4 (one discrete quarter) — 6 files, Jul 2024-Jun 2026.

Observed sizes/rows (live, 2026-09-29 — proof-of-transform run, not a full load)

Estimated total for the default 6-file load: ~1.2-1.3M rows, ~650MB of xlsx downloads. Every .xlsx sheet carries a large trailing block of genuinely-empty phantom rows past the real data (e.g. FY2026_Q3’s declared sheet dimension is 1,032,736 rows for 437,496 real ones) — this is a DOL export artifact, not a bug in the transform script; those rows are dropped and counted as “without CASE_NUMBER”.

Known source data-quality trap

DOL’s raw WAGE_RATE_OF_PAY_FROM/WAGE_UNIT_OF_PAY occasionally disagree in a way that produces an absurd base_salary_annual (an observed FY2026_Q3 row annualizes to ~$453M — almost certainly a unit-entry error by the filer, kept verbatim rather than “corrected”, same discipline as sba_loans keeping literal duplicate/conflicting source rows). median-based stats are resistant to this; a max in a response can occasionally show one of these outliers — that is the source data, not a mirror defect.

Gotchas

• The live DOL .xlsx header spells the H-1B-dependent-employer column H_1B_DEPENDENT (underscore) — the record-layout PDF spells it H-1B_DEPENDENT (hyphen). Trust the live file; verified against FY2026_Q3.xlsx 2026-09-29.
• Do not load more than one file per still-open fiscal year, and do not load a closed year’s file without all four of its quarters — see “Data source” above. Getting this wrong either duplicates rows (double-counting an overlapping cumulative + discrete file) or leaves silent gaps.

Tools

• h1b_employer_sponsorship — Check whether a US employer sponsors H-1B visas and profile their sponsorship, from DOL Labor Condition Application (LCA) disclosures. Answers ‘does company X sponsor H-1B / green cards’ for recruitin
• h1b_salary — Look up real H-1B base salaries for a job title from DOL LCA disclosures — a market wage benchmark backed by actual filed salaries (not estimates). Answers ‘what do H-1B software engineers earn at com
• h1b_top_sponsors — Find which US employers sponsor the most H-1B visas for a given job title (optionally in a specific city) — a candidate-sourcing / target-account signal for recruiting. Answers ‘which companies sponso