L&I Certified Payroll — XML Export

What this does

For government (prevailing-wage) jobs, WA L&I requires a certified payroll report filed online at least monthly. Instead of re-typing every worker, day, and wage by hand on the L&I website, this tool builds the official upload file (an XML file) from the data already in the portal. Emily picks the government project and the week(s), the tool builds the file, she downloads it, and uploads that one file to L&I — which then creates the certified payroll report(s) automatically.

Where to find it

On the dashboard (bellaterra.services), the tile 🏛️ L&I Certified Payroll. The page has three tabs: ① Generate XML, ② Workers, and ③ Trade map.

One-time setup (do once, then it's reused)

1. ③ Trade map tab — for each cost code, pick the L&I trade (4-letter code) and type the job classification exactly as filed on your Statement of Intent.
2. ② Workers tab — enter each worker's full SSN and home address (L&I requires these in the file). Stored in a locked, payroll-only place — never shown publicly.

Each time you file (per government job + pay period)

1. Project Setup: on the government job, fill in the L&I Intent # and County, then Save. (These fields appear only on Government jobs.)
2. ① Generate tab: pick the project and the week range, click Check & build.
3. Fix any red ✕ items it lists (it tells you exactly what's missing and links you to it).
4. Auto-fill wages & taxes: open "Auto-fill from the Payroll Northwest journal," upload the EMAILJRN…pdf that PNW emails you for that period. It fills each worker's Gross, Federal withholding, and FICA automatically — matched by employee number.
5. Eyeball the numbers (adjust any cell if needed), then click ⬇ Download L&I XML.
6. Upload that file to L&I's Prevailing Wage system.

The Payroll Northwest auto-fill (why it's reliable)

The PNW "Payroll Journal" PDF lists each worker's employee number, Gross, FED, and FICA. That employee number is the same number as in our system (e.g. Rena = 1509, Emily = 33775, Liz = 1630), so the tool matches by number — not by name-spelling — which makes it accurate. Tested against a real journal: all 18 workers read correctly. Do one pay period at a time.

What's already proven

The only thing not yet tested is a real government job's hours — because Bella Terra hasn't run one since the portal launched. The engine is ready for when that happens.

Good to know

• Fringe/benefits are reported as paid in cash on the paycheck (the full prevailing rate is the hourly wage; benefit lines are $0). That matches how Bella Terra pays.
• Gross pay in the file comes from the Payroll Northwest numbers (the real paycheck), not from timecards. Timecard hours are shown only as a sanity check.
• SSNs and addresses are kept in a locked, sign-in-only table — not exposed to the public site.

For Rena / technical reference

• Pushed to main on 2026-06-12, commit 6074bb2 → Netlify.
• New page: li-certified-payroll.html. Tile in index.html. Intent #/County added to project-setup.html.
• Database (run in Supabase): new locked tables payroll_identity and li_trade_map; new columns projects.li_intent_id and projects.li_county. SQL kept at _security/li_certified_payroll.sql.
• Official schema saved at _li-cpr-schema.xsd for future validation.