@pipeworx/perm

Connect: https://pipeworx.io/mcp — every tool in the catalog, including @pipeworx/perm’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 green-card labor certification data from DOL PERM (Program Electronic Review Management, Form ETA-9089) disclosures — “does company X sponsor green cards”, “what does role Y pay at company X under PERM”, “who sponsors the most data engineers for green cards”. The permanent-labor-certification sibling of @pipeworx/h1b (which covers the LCA / temporary-visa side).

Tools

• perm_employer_certifications(employer, year?) — filing count, offered-wage range, top titles/locations for an employer. Total count first, data_as_of on every response.
• perm_salary(job_title, employer?, city?, year?, limit?) — real disclosed offered-wage stats + a sample of records for a role.
• perm_top_sponsors(job_title, city?, year?, limit?) — employers ranked by filing count for a role, with median offered wage.

Auth

Keyless. Local mirror only — no caller credential is needed or accepted.

Local mirror, no live fallback

Why a mirror: PERM, like LCA, is published only as bulk quarterly/annual .xlsx files — it is not on the apiprod.dol.gov v4 gateway that dol-whd/osha/ebsa already use (checked live 2026-10-17: not present in that gateway’s 45-dataset catalog). A local Supabase mirror is this pack’s only source.

Unlike h1b, there is no live fallback. LCA has h1bdata.info, a long-standing third-party aggregator of the same DOL data; no comparable third-party mirror of PERM disclosures was found. So when a query falls outside the mirror’s currently loaded fiscal-year window, or the mirror cannot be read at all, every tool returns a coverage_status field and a null (not false) sponsorship/count — never a bare empty result that reads as “this employer doesn’t sponsor” when the real reason is that the mirror has not loaded that year yet, or has not completed its first load. Three distinguishable states, same discipline as h1b’s fleet #3198 fix:

• not_loaded_yet — the mirror’s ingest table is genuinely empty (the first refresh has never run). “This data source has not completed its first load yet.”
• mirror_unreachable — the coverage read itself failed (no credentials, a timeout, a transient DB error). “Not confirmation of zero filings — the data source was never actually consulted this call.”
• out_of_window — the mirror answered fine, but the requested fiscal year is outside its loaded range. A real (if incomplete-coverage) signal.

Employer-name match grading. Both perm_search’s SQL (employer_name ILIKE '%' || p_employer || '%') and the stats RPC’s grouping are plain substring matches, so a query like “Amazon” can legitimately pull back more than one distinct legal name (verified live against the real FY2025 file: AMAZON.COM SERVICES LLC / Amazon.com Services LLC / Amazon.Com Services Llc / Amazon Data Services, Inc. all appear). matched_employers ranks exact-match first (after stripping case/punctuation and common entity suffixes), then name-contains variants, each tagged name_match — same employerNameKey/gradeEmployerMatch/ rankEmployerVariants logic h1b uses (fleet #3198).

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

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

• EMP_POC_{LAST,FIRST,MIDDLE}_NAME, EMP_POC_EMAIL, EMP_POC_PHONE and every EMP_POC_ADDR* field — the employer’s named point of contact.
• ATTY_AG_{LAST,FIRST,MIDDLE}_NAME, ATTY_AG_EMAIL, ATTY_AG_PHONE, every ATTY_AG_ADDRESS*/STATE_BAR_NUMBER/GOOD_STANDING* field — the individual attorney or agent representing the employer (ATTY_AG_LAW_FIRM_NAME, a business name, is also dropped: unused by any tool here).
• DECL_PREP_{LAST,FIRST,MIDDLE}_NAME, DECL_PREP_EMAIL — whoever signed the employer declaration.
• Every FOREIGN_WORKER_*/COUNTRY_OF_CITIZENSHIP/CLASS_OF_ADMISSION field — PERM’s layout carries no foreign-worker name field at all (confirmed by reading the full layout), but these still describe the sponsored worker’s own nationality/education/experience, and no tool here needs them.

Kept: EMP_BUSINESS_NAME/EMP_TRADE_NAME and employer city/state/country/ FEIN/NAICS (no street — same discipline as h1b_lca_disclosures), plus job/wage/worksite fields and case status/dates — this identifies the employer making a mandatory public disclosure, not a private individual.

Data sources

• U.S. Department of Labor, Office of Foreign Labor Certification — PERM disclosure data. Published quarterly, by federal fiscal year (Oct 1 - Sep 30). No auth, no rate limit observed. US federal administrative data — public domain.
• Verified live 2026-10-17: a closed fiscal year’s own Q4-labelled file IS the full fiscal year already — DIFFERENT from h1b’s LCA mirror, where a closed year’s quarterly files are each discrete and all four are needed. PERM_Record_Layout_FY2025_Q4.pdf’s own cover page states “Reporting Period: October 1, 2024 through September 30, 2025”. So exactly ONE file per fiscal year gives full-year coverage. The still-open current fiscal year’s latest quarterly file is likewise cumulative from Oct 1 (FY2026_Q3.pdf: “October 1, 2025 through June 30, 2026”).

NEW FORM ONLY

DOL’s PERM form (ETA-9089) changed 2023-06-01. Verified live 2026-10-17 against two real downloads: an FY2024 file carries the OLD 155-column shape (e.g. a single EMP_CONTACT_NAME field, no JOB_OPP_WAGE_FROM), while FY2025 and later use the NEW 137-column shape this pack’s transform expects. scripts/perm-transform.py’s REQUIRED_SOURCE check refuses an old-form file outright rather than silently mis-mapping it — old-form historical backfill (pre-FY2025) is future work, not attempted here. The mirror currently loads FY2025 onward.

Ingest harness

• supabase/migrations/259_perm_disclosures.sql — perm_disclosures table + perm_ingest_runs bookkeeping + four RPCs (perm_search, perm_stats, perm_top_sponsors, perm_coverage). RLS on; anon/authenticated have no grants; service_role only.
• scripts/perm-transform.py — streams one .xlsx file row-by-row via openpyxl(read_only=True) (never buffers a whole workbook), allowlist-selects the non-personal columns, and computes base_salary_annual from JOB_OPP_WAGE_FROM/JOB_OPP_WAGE_PER (Hour×2080, Week×52, Bi-Weekly×26, Month×12, Year×1), with the same filer-typo guard h1b’s transform uses (an “Hour”-tagged four/five-figure amount is treated as already annual; any annualized result above $5,000,000 is nulled rather than served as data).
• scripts/perm-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/perm-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: 2026:FY2026_Q3 (current open year) + 2025:FY2025_Q4 (full FY2025).

Observed sizes/rows (live, 2026-10-17 — proof-of-transform dry run, store not yet loaded)

Gotchas

• case_status values observed live in the real FY2025 file: Certified, Certified - Expired (with spaces around the dash — not Certified-Expired as the record-layout prose might suggest), Denied, Withdrawn. “Certified” filters match the first two.
• Do not add an FY2024-or-earlier file to the refresh without first building an old-form transform path — see “NEW FORM ONLY” above.
• perm_employer_certifications/perm_salary/perm_top_sponsors all return null (never false/0) for sponsorship/counts when the mirror could not answer — check coverage_status before treating an empty result as a real negative.

Tools

• perm_employer_certifications — Check whether a US employer sponsors green cards (PERM labor certification) and profile their sponsorship, from DOL PERM (Form ETA-9089) disclosures. Answers ‘does company X sponsor green cards’ for r
• perm_salary — Look up real green-card (PERM) offered wages for a job title from DOL PERM disclosures — a market wage benchmark backed by actual certified filings (not estimates). Answers ‘what do green-card-sponsor
• perm_top_sponsors — Find which US employers sponsor the most green cards (PERM labor certifications) for a given job title (optionally in a specific city) — a candidate-sourcing / target-account signal for recruiting. An