# Methodology

## 1. Sources
34 DOL OFLC H-1B/LCA disclosure workbooks (xlsx), supplied as downloaded; names, URLs and SHA-256 in `SOURCE_INVENTORY.csv`. FY2010 to FY2019 are annual files (FY2011, FY2012, FY2014 and FY2015 are the "Q4" full-year cumulative files). FY2020 to FY2025 are quarterly files. FY2021 Q2, FY2021 Q3 and FY2023 Q2 are cumulative year-to-date files. Each file's period coverage was measured from its decision dates, not assumed from its name. Nine layout families are handled; the layout is detected from the header signature, and header drift inside a fiscal year (FY2025 Q1 vs Q2-Q4) is resolved per file.

Only rows with VISA_CLASS = H-1B are kept (FY2010 has no such column: every row is kept and flagged as assumed H-1B). Blank padding rows are skipped. Every kept row has a lineage key (`source_key`, `source_row`; `source_row` is the worksheet row number with the header as row 1, and it also counts non-H-1B rows, so values have gaps). No source row is deleted.

Fields never read into the product (the free-text fields that are released are redacted, see the last section): employer FEIN, employer street address and phone (used only as in-memory identity evidence, never output), the 36 point-of-contact, attorney, agent and preparer columns, law-firm fields.

## 2. Row roles (unique applications vs status records)
A DOL fiscal-year file is a list of decisions in that period. One application (case number) can appear again later, for example certified then withdrawn. Each row gets one role:
- `ORIGINAL_DECISION`: the first decision record of the application in the loaded files.
- `LATER_STATUS_EVENT`: a later record of an application that appears earlier or whose original certification date precedes the file period (evidence tokens: `ORIG_CERT_DATE_BEFORE_PERIOD`, `SUBMIT_DATE_PROXY_BEFORE_PERIOD`, `EARLIER_LOADED_ROW`, `DUPLICATE_CASE_NUMBER_WITHIN_FILE`).
- `REPEATED_SNAPSHOT`: a row identical in status, decision date and original certification date to the same case's row in an earlier file. Produced by cumulative year-to-date files. Not an application and not an event. Kept with lineage, excluded from status counts.
8,918,577 rows = 8,215,630 original decisions + later status events + 377,296 repeated snapshots. 22,593 case numbers have no original decision in the loaded files (`ORPHAN_ORIGINAL_PERIOD_NOT_LOADED`): their original decision predates FY2010 or falls outside the files.

An independent pure-Python re-implementation of the role rule (not sharing the pipeline SQL) agrees on every row.

## 3. DOL status classification
`case_status` is kept as filed. `dol_stats_status` maps each row to DOL's published categories. CERTIFIED-WITHDRAWN rows are classed WITHDRAWN when the original certification date precedes the file period (FY2016 onward), else CERTIFIED. This rule was fitted on FY2018 and FY2019 and reproduces DOL's published totals exactly on every later file that has an official target (except the quarters in LIMITATIONS). DOL does not document it, so it is labeled **provisional**.
FY2010 to FY2015 files have no original certification date. There a CERTIFIED-WITHDRAWN row is classed WITHDRAWN when the case was submitted more than 14 days before the file period start, else CERTIFIED (`dol_stats_status_basis` says so on every row). The 14-day threshold applies only to rows whose filed status is CERTIFIED-WITHDRAWN; no other status is reclassified. Without that threshold the roles do not reproduce. Against DOL's published FY2014 and FY2015 totals this proxy lands within 0.9% in aggregate. That is aggregate agreement, not a row-level accuracy or error bound. FY2010 to FY2013 have no published target to check.

## 4. SOC codes
The filed code is preserved (`soc_code_raw`, `soc_title_raw`); `soc_code_std` only fixes the format. The vintage (2000, 2010 or 2018) is the classification in which the code exists, with the expected vintage per file set from measured code evidence and a documented fallback order. Rows per vintage and the evidence are stored (`soc_vintage_used`, `soc_vintage_basis`, `soc_vintage_evidence`). The 2010-to-2018 switch in DOL's files is gradual: FY2022 Q4 to FY2023 Q4 mix both vintages.
Mapping to SOC 2018 (`soc_mapping_class`) uses the BLS 2010-to-2018 crosswalk. `soc2018_code` is filled only for one-to-one mappings. Split mappings carry a candidate list and no guess. Codes in no vintage (for example legacy 15-1799) and unreadable codes are kept as filed.

## 5. Wages
Filed wage values and units are kept. An annual figure (`wage_annual_from_calc`, `_to_calc`) is computed only for supported units (Year, Hour, Week, Bi-Weekly, Month) and flagged `OK_CONVERTED` or `OK_AS_FILED_ANNUAL`. Ambiguous and unsupported cases are excluded from panel wage statistics and counted. A possible wrong-unit entry is reported in `wage_unit_correction_candidate_not_applied` and never corrected. FY2015 wages are one text field ("70500 - 140000") parsed to low and high. FY2010 has no offered-wage unit column; the unit is inferred from the prevailing-wage unit (median offered-to-prevailing ratio 1.05, 98.9% of rows in the plausible range 0.5 to 3).

## 6. Employer identity (R5-v1, provisional)
1. Names are normalized to a key (case, punctuation, accents, HTML artifacts), `employer_key_l1`, giving `employer_id_l1`.
2. Within a name key, evidence components (address, phone where available, sector, state) are connected. A name is split into several ids only when components are positively disconnected (`SPLIT_R5`); otherwise it stays one id.
3. Legal-suffix and spacing variants are NOT merged (that would join AAKASH INC and AAKASH LLC); they share a `candidate_group_id` instead.
This is a conservative rule with measured limits: an era-stratified audit of 120 groups found no false merge among spelling merges, 11 of 40 sampled splits that look like one company across offices, and 23 of 40 unmerged variant groups that look like the same company. See `IDENTITY_AUDIT_ERAS.md`. No accuracy rate is claimed.

## 7. Panel
Grain: employer_id x filed SOC (`soc_code_filed_std`) x SOC vintage x fiscal year of the file. Unique-application columns (`unique_applications_*`) count original decisions and are additive across years. The `fy_*` columns are DOL-style status-record counts (later events included, repeated snapshots excluded) for comparing with DOL's fiscal-year tables. Wage statistics use originally certified applications with a usable annual wage, with the excluded counts alongside. The panel is built by one SQL over the case table and re-derived with pandas by the builder (11 measures, all 1,916,599 cells) and, in a separate review by a second AI reviewer working from the released files, 27 measure columns over 5,200 sampled rows and all cells (see RELEASE_CERTIFICATION_REPORT.md section 11). Wage percentiles are rounded to whole dollars. Neither check is a human audit.

## 8. Reconciliation to DOL
Each file's processed, certified, denied and withdrawn counts (all visa classes, from the raw layer) are compared with DOL's published statistics for the matching period. Results and exceptions are in `RELEASE_CERTIFICATION_REPORT.md` and `reference/DOL_RECONCILIATION.csv`. DOL restates published quarter figures between releases; the latest published figure is the target and the report names which release was used.

## Privacy redaction (patch 2)
Order of operations: employer identity (R5-v1) was assigned on the original strings, in memory, as before. After that the released tables were rewritten with (1) identifier-like text redacted in 17 free-text columns and (2) every identifier derived from an excluded field replaced by an opaque id. Row order, column order and all other cells are unchanged.
Patterns (applied to the original strings): e-mail address; `NN-NNNNNNN`; US-style phone `NNN-NNN-NNNN`, `(NNN) NNN-NNNN`, `NNN NNN NNNN`; runs of 9 or more digits in name-like columns (`employer_name_raw`, `employer_dba_raw`, `employer_key_l1`, `job_title`, `employer_city`, `primary_ws_city`, `naics_code`, `soc_title_raw`, `ws_city`, `ws_county`, `pw_source_raw`, `pw_source_year_raw`, panel `employer_name_display`, `soc_title_modal_filed`); runs of 10 or more digits in `ws_postal`; digit-only values of 9 or more digits in `wage_from_raw`, `wage_to_raw`, `pw_raw`.
Opaque ids: the legacy ids are sorted and numbered; the new id is the first characters of SHA-256 of a fixed label and that number, so it cannot be inverted from the release.
Cells changed by column: cases.employer_city 11; cases.employer_dba_raw 694; cases.employer_key_l1 1670; cases.employer_name_raw 1958; cases.job_title 22; cases.naics_code 3; cases.primary_ws_city 6; cases.pw_raw 7; cases.wage_from_raw 10; cases.wage_to_raw 3; panel.employer_name_display 142; worksites.pw_raw 9; worksites.pw_source_year_raw 2; worksites.wage_from_raw 10; worksites.wage_to_raw 3; worksites.ws_city 6; worksites.ws_postal 52.
Verification (`qa/PRIVACY_VERIFICATION.json`, script `reproduction/privacy/verify_privacy.py` in the project repository): row counts identical; every column that was not redacted or remapped is identical row by row (order-sensitive hash); each remapped id column is a one-to-one relabelling with the same NULL pattern; changed cells equal the ledger exactly; panel key unique; the case-to-application employer-id agreement is unchanged; a regular-expression scan of every text column finds no remaining e-mail, tax-id-format number, formatted phone or run of 9 or more digits (documented exceptions: SOC codes typed with extra digits, 9-digit ZIP+4 values, wage amounts with cents); none of the replaced legacy ids remains. The panel tie-out from the case files was repeated on the redacted files (`qa/release_tieout_patch2.json`).