Phase B of RSU tax spike fix. Vest-month spikes on the dashboard trace to
variant-A slips (2019–mid-2022) where `cash_income_tax` is NULL — the
dashboard's COALESCE fallback returns full PAYE, masquerading as cash tax.
Three changes:
1. Widen variant-A Taxable Pay regex. Original pattern only matched
`Taxable Pay : This Period £...`; add case-insensitive variants that
tolerate missing/different colons, elided "This", and uppercase labels.
Covers older 2019-2020 templates that failed the previous match.
2. New `backfill_cash_income_tax` module — walks every NULL-cash-tax row
with rsu_vest > 0, re-downloads the PDF from Paperless, runs the
widened regex parser, falls back to Claude for taxable_pay extraction
if regex still misses, and derives cash_income_tax pro-rata. Records
provenance in new `cash_income_tax_source` column (regex/claude/
fallback_null). Idempotent — only touches NULL rows.
3. Migration 0006 adds the `cash_income_tax_source` audit column.
CLI: `python -m payslip_ingest backfill-cash-tax [--limit N]`. Meant to
run as a one-shot K8s Job after `alembic upgrade head`.
Part of: code-860
Meta variant-B payslips gross up Taxable Pay for RSU and compute PAYE on
the grossed-up figure, so `income_tax` on the slip is the total PAYE
(cash + RSU-attributed). Dashboards that stacked the raw figure made
vest-month tax look ~2x higher than "cash tax paid". Introduce
`cash_income_tax = income_tax * (gross_pay - pension_sacrifice) /
taxable_pay` as a derived column alongside the raw figure. Dashboards
can now stack cash vs RSU-attributed tax as separate segments.
Also capture YTD column values of `RSU Tax Offset` and `RSU Excs Refund`
from the Payments grid — needed for reconciliation against HMRC annual
figures.
P60 ingest: new parser under `parsers/p60.py` anchoring on statutory
HMRC line labels (`Tax year to 5 April YYYY`, `Employer PAYE reference`,
`In this employment` pay/tax row, NI letter bands). Processor routes
documents carrying the `p60` Paperless tag to `_handle_p60` which
writes to the new `payslip_ingest.p60_reference` table (one row per
tax_year+employer). App lifespan resolves the tag id at startup; missing
tag disables dispatch without breaking payslip ingest. Paperless tag
creation + webhook config are manual follow-ups.
Migrations:
- 0004 — cash_income_tax + ytd_rsu_tax_offset + ytd_rsu_excs_refund on
payslip, all nullable.
- 0005 — p60_reference table with (tax_year, employer) unique +
paperless_doc_id unique for idempotent re-uploads.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
## Context
After the first v2 backfill (commit f62c533), 72 of 73 real payslips
landed correctly, but three residual failure modes remained:
1. **doc_id=215** — a 1442-byte empty-text PDF that Claude
hallucinated a `pay_date=1900-01-01 / gross=0 / net=0` row for.
Data poison waiting to happen.
2. **doc_id=39** — a P60 End of Year Certificate. Got tagged
`payslip` in Paperless, has no Paperless title, so the title-based
filter couldn't catch it; the regex parser then happily pulled
bogus numbers out of the P60 layout.
3. **doc_id=49** — a real June 2021 variant-A payslip with an
`EE Discount BIK` line in BOTH Payments and Deductions at 12.00.
The parser was configured to drop `EE Discount BIK` from
`other_deductions` (treating it as a known mapped field), which
caused validate_totals to fail by exactly 12.00.
## This change
### processor.py — defence in depth
- **`NON_PAYSLIP_CONTENT_RE`** — new regex run against the first
500 chars of pdftotext output. Catches `P60 End of Year
Certificate` and `Take-home income per month` (Viktor's comp
estimation spreadsheet). First-500-char scoping keeps it from
false-positiving a legit payslip that mentions "P60" in a
footer.
- **Post-extraction sanity checks** — reject a ProcessResult if
`pay_date.year < 2010` (Viktor joined Meta in 2019) or if
`gross_pay == net_pay == 0`. These raise rather than insert,
so the backfill's existing `except Exception` block logs and
continues without poisoning the DB. Supersedes the 1900-01-01
case that would otherwise slip through.
### meta_uk.py — variant A BIK fix
Removed `EE Discount BIK` from `VARIANT_A_DEDUCTIONS_KNOWN`. That
set filters items OUT of `other_deductions` (because they have
dedicated schema fields). `EE Discount BIK` has no dedicated
field — it should stay in `other_deductions` like Private Dental
and Private Medical so the validation math balances.
### Fixtures + tests
- New fixture `meta_uk_2021_06_variant_a_bik.txt` — real
pdftotext from doc_id=49 — encodes the BIK-in-both-columns
case so a regression would fail this fixture's validation test.
- `test_parses_variant_a_with_ee_discount_bik` — explicitly
asserts `EE Discount BIK` lands in `other_deductions`.
- `test_rejects_implausible_pay_date`, `test_rejects_zero_gross_zero_net`
— cover the two sanity-check branches.
- `test_skips_p60_by_content_when_title_is_null` — covers the
content-based non-payslip filter.
## Test Plan
### Automated
```
$ poetry run pytest
============================== 57 passed in 2.42s ==============================
$ poetry run ruff check .
All checks passed!
$ poetry run mypy .
Success: no issues found in 24 source files
```
### Manual verification (after deploy + re-run backfill)
Expected DB shape:
- Total rows ≈ 71 (88 paperless tags − 15 non-payslip titles −
2 null-title non-payslips caught by content filter)
- `validated = true` on ≥99% of rows
- No `pay_date < 2010` rows
- No rows with employer IS NULL
## Reproduce locally
1. `cd payslip-ingest && poetry run pytest`
2. Expected: 57 passed, including the 3 new processor tests and
the 5 parametrised fixture-total-validation tests.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
## Context
The initial v2 parser (commit 9741816) only handled the modern template
(variant B, 2024+). Of Viktor's 73 real payslips in Paperless, 30 from
2021-07 through 2023-11 failed entirely — Claude fallback hit errors on
them and the rows never landed. Investigation via `kubectl exec` +
pdftotext on a sample of the failing docs revealed two previously-unseen
layouts that the parser needs to handle directly:
- **Variant A** (2019 → mid-2022): single-column Description/This Period/
This Year. Parenthesized negatives `(152.90)`. Date format `Date : 31
Aug 2021`. Employer is `Facebook UK Ltd` (not `Limited`). RSU lines:
`RSU Gain Taxable` + `RSU Gain Nicable` + `RSU Net Cash UK` on the
earnings side with a matching `RSU Net Gain` on the deductions side.
BIK items (Private Dental/Medical) appear on both sides — net zero in
the gross, but the deduction-side copy must land in other_deductions
for the validation formula to hold.
- **Variant C** (late-2022 → 2023): side-by-side Payments|Deductions|
Year To Date (note capital "To", vs variant B's lowercase "to"). Date
format `Pay Date : 30.11.2022` (dots, not slashes). RSU labels use the
abbreviated `RSU Gain Taxabl` / `Nicabl` and still include the `RSU
Net Gain` offset. `Company Name : Facebook UK Limited` preamble.
Variant B (2024+) is unchanged.
## This change
### Parser refactor
- `EMPLOYER_RE = re.compile(r"Facebook UK (?:Limited|Ltd)\b")` — matches
all three eras.
- `AMOUNT_RE` now accepts both `-1,234.56` and `(1,234.56)` — variant A's
accounting-style parenthesized negatives normalize to `-1234.56` in
`_to_decimal`.
- `_parse_date` tries three formats in order: slash (B), dot (C), word (A).
- `_is_variant_b_or_c` collapses B and C into one detector (both have the
side-by-side header with `Year [Tt]o Date`); their parsers share code
because the column mechanics are identical — only the RSU-label set and
date format differ.
- `_parse_variant_a` is a full rewrite: single-column rows split by the
two `Total ...` anchors (payments → deductions), pay_date from the
header's `Date : ...`, gross from first Total, net from the trailing
`Net Pay` line, taxable_pay from the `Taxable Pay : This Period £X`
line at the bottom.
- RSU_VEST_LABELS is a shared set covering 8 aliases; rsu_vest sums every
matching payment line. rsu_offset maps to `RSU Net Gain` on the
deduction side when present (absent in variant B, present in A and C).
### Fixtures switched to real pdftotext output
Removed the two synthetic fixtures that no longer reflected real Meta
output (`meta_uk_2019_07.txt`, `meta_uk_2024_03_bonus_sacrificed.txt`)
and replaced with real pdftotext captures:
- `meta_uk_2021_08_variant_a.txt` (doc_id=43)
- `meta_uk_2022_11_variant_c.txt` (doc_id=53)
The remaining synthetic fixtures (`2025_03`, `2026_02`) stay because
they encode specific bonus/no-bonus scenarios and the numbers are
derived from the real Feb-2026 sample in the plan.
## Tests
- 10 parser tests: one per variant (A/B/C) + totals validation across
all 4 fixtures + the existing non-Meta/empty-input guards. All pass.
- 52 total tests across the repo, all green.
## Test Plan
### Automated
```
$ poetry run pytest
============================== 52 passed in 1.66s ==============================
$ poetry run ruff check .
All checks passed!
$ poetry run mypy .
Success: no issues found in 24 source files
```
### Manual verification (after deploy)
1. TRUNCATE + re-run backfill — expect 73 real payslips to extract via
regex (≥95% hit rate), 42 → 70+ validated rows.
2. Sample a row for each variant via psql: employer, rsu_vest, and
taxable_pay should all be populated.
## Reproduce locally
1. `poetry run pytest tests/test_meta_uk_parser.py -v`
2. Expected: 10 passed, each fixture validates totals to within 2p.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
## Context
v1 shipped a Claude Haiku-based extractor that validated only 10/71
backfilled rows. Haiku fumbles the arithmetic on pension salary-sacrifice,
conflates RSU vest with regular earnings, and occasionally misreads YTD
vs this-period columns — so 86% of rows land with validated=false and the
downstream dashboards under-report take-home.
Meta UK uses a stable two-variant template (pre/post 2022-01-31 boundary),
so a regex parser is both faster (ms vs. 30-90s + $0.01-0.05/call) and
more accurate. v2 introduces that parser as the primary path, keeps
Claude as the fallback for non-Meta payslips, and surfaces new fields
the dashboard needs to attribute PAYE between cash salary and RSU vests
correctly.
## This change
### Parser (new)
`payslip_ingest/parsers/meta_uk.py` detects the layout variant by header
presence:
- **Variant A** (pre-2022): vertical Description/This Period/This Year.
`AE Pension EE` is a positive deduction against a pre-sacrifice gross —
maps to `pension_employee` for the existing validation formula to hold.
- **Variant B** (post-2022): side-by-side Payments | Deductions | Year to
Date. `AE Pension EE` is NEGATIVE in Payments (salary sacrifice) — maps
to `pension_sacrifice` and is already netted into Total Payment.
`rsu_vest = RSU Tax Offset + RSU Excs Refund` (Meta's template inflates
Taxable Pay without using a matching offset deduction).
Column boundaries come from the header row's anchor positions; each data
row slices into 3 cells and the last numeric token per cell is the amount.
Anchor misses raise ParserError so the caller falls back to Claude rather
than silently returning bad data.
### New fields
Schema + DB + Claude prompt gain:
- `salary`, `bonus`, `pension_sacrifice` — earnings decomposition for the
dashboard's bonus-sacrifice visibility and earnings-breakdown chart
- `taxable_pay`, `ytd_tax_paid`, `ytd_taxable_pay`, `ytd_gross` — powers
the YTD-effective-rate method of attributing cash tax vs RSU tax, which
is the only method that's accurate month-to-month
All new columns default to 0 / null so v1 rows continue to round-trip.
### Orchestration
processor.py tries `parse_meta_uk(pdftotext(pdf))` first. On success the
result goes straight to the DB — zero Claude tokens spent, extraction in
milliseconds. On ParserError it falls through to ClaudeExtractor as before.
ProcessResult gains an `extractor` field ("meta_uk_regex" | "claude") so
backfill logs show the hit rate.
## Tests
- `test_meta_uk_parser.py` — 11 tests covering variant A, variant B
(standard + bonus month + bonus-sacrificed month), malformed inputs,
and end-to-end totals validation for all 4 golden fixtures.
- `test_processor.py` — 2 new tests proving the regex-first short-circuit
and the Claude fallback on non-Meta inputs.
Fixtures under `tests/fixtures/` are hand-crafted `pdftotext -layout`
emulations — real Meta numbers from the plan's sample payslips for
variant B, synthesized realistic variant A and bonus-sacrificed samples.
0001_initial.py reformat is yapf cleanup touched during the session's
format pass; not a behavior change.
## Test Plan
### Automated
```
$ poetry run pytest
============================= test session starts ==============================
collected 53 items
tests/test_extractor.py ..... [ 9%]
tests/test_meta_uk_parser.py ........... [ 30%]
tests/test_paperless.py ...... [ 41%]
tests/test_processor.py .............. [ 67%]
tests/test_schema.py .... [ 75%]
tests/test_tax_year.py ........ [ 90%]
tests/test_webhook.py ..... [100%]
============================== 53 passed in 1.66s ==============================
$ poetry run ruff check .
All checks passed!
$ poetry run mypy .
Success: no issues found in 24 source files
$ poetry run yapf --style pyproject.toml --diff --recursive payslip_ingest tests
(no output — all files are yapf-clean)
```
### Manual Verification
Smoke-test the parser against a real Meta payslip PDF on the deploy host:
```
# After 0003 migration applied to prod DB
$ poetry run python -c "
from payslip_ingest.parsers import parse_meta_uk
import subprocess
text = subprocess.check_output(['pdftotext', '-layout', '/path/to/real.pdf', '-']).decode()
p = parse_meta_uk(text)
print(p.model_dump_json(indent=2))
"
```
Expected: JSON with salary/bonus/rsu_vest/pension_sacrifice populated and
`validate_totals(p)` returning True.
## Reproduce locally
1. `cd payslip-ingest && poetry install`
2. `poetry run pytest tests/test_meta_uk_parser.py -v`
3. Expected: 11 tests pass, each fixture validates totals within 2p.
Closes: code-un1
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Viktor Barzin