# Bellingham Phase 1 Ingestion — Report

*August 28, 2026. First city in the AI-automation-exposure model (see candidate-cities-recon.md). All source documents fetched directly from cob.org; all parsing validated against the documents' own printed totals.*

## What was built

```
bellingham/
  raw/budget/            2026 Adopted Budget PDF (199 pp.)
  raw/job-descriptions/  352 duty-statement PDFs (complete public library)
  raw/pay-schedules/     22 pay schedule PDFs (2024-2027 plans)
  raw/cbas/              54 CBA/MOU/LOA PDFs (all 9 units' current contracts)
  data/
    budget_positions.csv       344 classification rows × 15 departments
                               (title, FTE 2025 revised / 2026 adopted, unit)
    citywide_fte_history.csv   FTEs by employee group, 2017-2026
    job_descriptions_index.csv 352 titles w/ dept, unit, classification, grade
    job_description_texts.jsonl  full text of every duty statement
    pay_titles_2026.csv        389 title→grade→step-schedule rows, 12 plans
    positions_joined.csv       the position → duty statement → pay join
tools/                   re-runnable parsers (venv: .venv)
```

## Coverage (2026-adopted, 1,121.7 FTE across 319 active classification rows)

| Join | Rows | FTE share |
|---|---|---|
| Position → pay schedule | 275/319 | **91%** |
| Position → duty statement (JD) | 212/319 | 55% |
| Full position → JD → pay | 199/319 | 54% |

- **Pay is nearly complete.** Of the 9% unmatched FTEs, 47.8 FTE are department temporary-labor pools that have no schedule by design. True non-temp no-pay remainder ≈ 60 FTE (Fleet Mechanic, Park Field Supervisor, a few managers).
- **The JD gap is structural, not a matching failure.** The public library covers civilian/administrative titles (~70% of civilian FTEs) but **excludes uniformed civil-service ranks entirely** — no Patrol Officer, Sergeant, Detective, fire Captain, Battalion Chief, or Dispatcher specs — and is thin on trades (Plant Operator, Fleet Mechanic, Utility Worker levels). Those specs sit with the Civil Service Commission → records request, or reconstruct duties from CBAs/postings.
- Uniformed ranks *do* carry pay: Plans D (police), C (fire), P (What-Comm), M (114F), F (fire staff officers, formula), O (FOP, formula) all parsed, so rank-level salary structure is complete even where duty statements are missing.

## Source-quality findings (the `notable` file for this ingestion)

1. **The budget book disagrees with itself by small amounts.** Parks & Rec's rows sum to 103.7/102.3 (2025/2026) but its printed subtotal says 103.8/102.2 — and the department TOTAL line uses the row-sum for 2025 and the printed subtotal for 2026. Citywide, department tables sum to 1,073.9 regular FTE for 2026 vs the 10-year history page's 1,073.1. Our data reproduces the tables faithfully; the source is internally inconsistent by ~0.8 FTE.
2. **~Half the budget's position pages render text as per-character runs** (scrambled under normal PDF extraction). Parser rebuilds lines from character coordinates — reusable for other cob.org PDFs.
3. **The JD library is working-assignment-level, not classification-level.** One budget class ("Park Technician") maps to several working specs ("– Grounds", "– Athletic Fields"); the civil-service class name lives in the index's Classification column. The join keeps all matching specs per class (`jd_files_all`).
4. **5 of 59 labor-agreement links on cob.org are dead (404)**, including the superseded 2023-24 librarians' CBA (current 2025-27 one is fine).
5. **The posted librarian pay schedules (Plan L, all 3 years) are image-only scans** with no text layer; values were transcribed from the 114L CBA's Appendix A instead.
6. **Pay plans are a 10-plan patchwork** — hourly (G, P) vs monthly (S, E1/E2, D, C, M, L) vs pure formulas (F = 8/10/12% over top-step Captain; FOP = 18% over top-step Sergeant; Mayor = 5% over highest dept head, by BMC). The formula plans mean some salaries only exist relative to other units' bargaining outcomes.
7. **What-Comm Technology Manager** appears in the budget with a 2026 value only (new position, column-position-assigned; flagged in data).

## Known limitations / next steps

- **Civil-service class specs** (uniformed + some trades): public-records request to the Civil Service Commission, or mine CBA articles for duty language.
- **Name-level payroll** (GovSalaries has 1,321 employees, 2025): bot-blocked; needs a browser-based pull. Would also let us reconcile budgeted vs filled.
- **Time series**: 2025/2023-24 budget PDFs exist on the same page pattern; the parser's page range would need adjusting per year.
- **Entity graph** (Phase 2): school district, Port, WTA, PUD, Whatcom County, and the pending Regional Fire Authority (would move ~182 uniformed fire FTEs + dispatch off-books; ballot target early 2027).
- **Next city**: Everett (occupation-code joins), then Spokane Valley (contract-city comparator).

## Reproducing

```
cd "City Comparison"
./.venv/bin/python tools/parse_bellingham_budget_positions.py   # budget → CSVs
./.venv/bin/python tools/scrape_bellingham_job_descriptions.py  # JD library
./.venv/bin/python tools/parse_bellingham_pay_schedules.py      # pay plans
./.venv/bin/python tools/build_bellingham_join.py               # the join
```