Adds forgejo.viktorbarzin.me/viktor/payslip-ingest as a second push
target alongside the existing registry.viktorbarzin.me/payslip-ingest.
Phase 4 drops the registry.viktorbarzin.me line after the bake.
Plan: github.com/ViktorBarzin/infra docs/plans/2026-05-07-forgejo-
registry-consolidation-plan.md.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
Migration 0008 + ORM model for payslip_ingest.rsu_vest_events.
Purpose: broker-sync (separate repo) will parse Schwab "Release
Confirmation" emails and populate this table, enabling Panel 15 of
the UK payslip dashboard to reconcile:
payslip.rsu_vest ↔ SUM(rsu_vest_events.gross_value_gbp)
RSU-attributed PAYE ↔ SUM(rsu_vest_events.tax_withheld_gbp)
Schema carries both the raw USD figures (fmv_at_vest_usd,
tax_withheld_usd, shares_*) and the GBP-translated values
(gross_value_gbp, tax_withheld_gbp) plus the FX rate used — the
dashboard joins on GBP, audits keep USD.
Idempotent on `external_id` — broker-sync emits a stable
`schwab:{date}:{ticker}:VEST:{shares_vested}` for each vest event.
The broker-sync postgres sink that writes here is pending a real email
fixture (current parser is heuristic-only) and a cross-service DB grant
for broker-sync's K8s ServiceAccount. Follow-up under code-860.
Part of: code-860
Adds daily sync of Meta payroll deposits from ActualBudget into
payslip_ingest.external_meta_deposits, enabling the dashboard to overlay
bank deposits against payslip net_pay and surface parser drift on net.
- Migration 0007: new table external_meta_deposits, unique on
actualbudget_tx_id, indexed on deposit_date.
- payslip_ingest.sync.actualbudget: narrow client for the
jhonderson/actual-http-api sidecar (list accounts + transactions).
Filters on payee regex (META|FACEBOOK, word-boundary). Idempotent
upsert — ON CONFLICT DO NOTHING on actualbudget_tx_id. Surfaces
clear error if the transactions endpoint is missing so the operator
can switch to a SQLite-mount fallback.
- CLI command: `python -m payslip_ingest sync-meta-deposits` driven by
4 env vars (ACTUALBUDGET_HTTP_API_URL, API_KEY, ENCRYPTION_PASSWORD,
BUDGET_SYNC_ID).
- Tests: 5 — regex positive/negative, full sync insert, idempotency,
404-endpoint failure mode.
Part of: code-860
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
Path filter `payslip-ingest/**` and context `payslip-ingest/` were
leftovers from when this lived as a subdirectory in the /home/wizard/code
monorepo. In this standalone repo there's no such subdirectory, so the
filter never matched and builds never ran on push. Drop the filter and
point context + dockerfile at the repo root.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Meta UK pays one performance bonus per tax year (2 historically, 1
since the comp cycle change). If the same bonus amount appears on a
second payslip in the same tax_year — typically from the extractor
misreading a YTD figure as a current-period row — summing the column
on the dashboard exaggerates total comp by 2x.
`_dedup_bonus` keeps the first occurrence per (tax_year, amount) and
stores subsequent matches with bonus=0. Original figure is preserved
in raw_extraction for auditability; a warning is logged each time.
Fixes the 2021 tax year inflation flagged by the user. Existing
duplicates need a one-shot SQL cleanup (backfill task code-7z0).
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
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>
0001_initial.py declares revision='0001', not '0001_initial'. My 0002
migration had down_revision='0001_initial', so alembic couldn't splice
it into the chain and silently ran 'upgrade head' as a no-op on pod
startup. The rsu_vest/rsu_offset columns never got created and every
INSERT from the new code failed with 'column does not exist'.
UK payslips for equity-comp employees report RSU vests as notional pay
for HMRC only. A paired same-magnitude deduction (Shares Retained /
Stock Tax Withholding / RSU Offset) nets it back out of cash. The UK
payslip's income_tax line shows tax on the grossed-up total, but the
actual RSU tax is handled by Schwab (US broker) via share sale. No
cash flows through UK payroll for RSU.
Previously the extractor folded RSU notional into gross_pay and
income_tax, which inflated the dashboard numbers — a payslip with
£25k RSU vest looked like 2x salary with 80% tax rate.
Changes:
- schema: add rsu_vest + rsu_offset fields (default 0).
- db + alembic 0002: add two new NUMERIC(12,2) columns with server
default 0 (backward-compatible; existing rows get 0).
- validate_totals: include rsu_offset in deductions sum so the
gross + rsu_vest inflation is properly netted out.
- extraction prompt: explicit rules for identifying RSU lines by the
common Meta/Sage/Workday labels, and to NOT put them in
other_deductions.
Dashboards in a follow-up commit: cash_gross = gross_pay - rsu_vest,
effective tax rate based on cash metrics.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
The Paperless 'payslip' tag has been applied over the years to P60 annual
summaries, performance/year-end letters, Compensation_EMEA/PSC letters,
comp-review letters, and RSU grant agreements. These are legitimate
financial docs but not monthly payslips, and including them pollutes
the dashboards (a P60 amount is ~12x a single month).
Filter by title regex before hitting Claude so we skip cheaply and
don't burn extraction credit on them. Status returned is
'skipped_non_payslip' to distinguish from the 'already-ingested' skip.
Covers: P60*, *performance*(letter|year-end)*, compensation_emea,
*psc*, comp-letter, rsu grant*. New parameterized tests cover both
the exclude list and representative real payslip titles.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
A single doc that isn't a real payslip (e.g., an RSU letter wrongly tagged
as payslip in Paperless) makes Claude return pay_date=null, which pydantic
rejects with ValidationError. Previously this killed the whole backfill at
the first bad doc, leaving 60 of 88 docs unprocessed.
Catch + log + continue so the backfill processes every doc. Failed docs
can be re-tagged or fixed individually later.
Without this, each extraction took 5-10 minutes because the base64'd PDF
expanded to ~300KB of prompt tokens. poppler-utils ships pdftotext which
turns a 200KB PDF into ~3KB of plain text in milliseconds. Claude (Haiku)
then processes the text in seconds.
- Dockerfile installs poppler-utils in the runtime stage (one-liner).
- _build_prompt() tries pdftotext -layout first; falls back to base64 if
pdftotext is missing (local dev) or the PDF is unreadable (scanned image).
- Agent file documents the PAYSLIP_TEXT fast path — still handles
PDF_BASE64 for fallback.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Real UK payslip extractions routinely take 5-10min end-to-end (Haiku
processing 100-300KB base64'd PDFs). With 10 retries × 5s = 50s we'd
abort while another extraction was still in-flight. Bump to 90 retries
× 10s = 900s wait — enough to cover the server-side timeout_seconds=600
plus some slack.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Real UK payslip PDFs are 100-200KB base64'd, which means ~300-500KB of prompt
tokens. Claude (even Haiku) takes 1-5 minutes to process and emit structured JSON.
The original 120s ceiling timed out before extraction could finish.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
The payslip_ingest schema must exist before Alembic creates its
alembic_version tracking table inside it. Add CREATE SCHEMA IF NOT EXISTS
at the top of do_run_migrations.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Extracted from /home/wizard/code monorepo into its own repo so Woodpecker CI
can watch it. Identical content to /home/wizard/code commit e426028.
See README.md for overview, env vars, and Paperless workflow config.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
