Staff key 300+ utility statements per company per month by hand. This adds
the ingest -> split -> OCR -> match -> review pipeline that proposes customer
and amount per page instead (RECEIPT_CAPTURE_SPEC §2), posting through the
existing BillingService.createBatch seam with source=OCR and a per-document
captureRef so machine and hand capture share one write path and audit trail.
Everything was designed against 10 real scanned statements (46 pages of CFE,
CESPT and Telnor bills) rather than from the sample-free spec. The scans have
no text layer at all — they are camera images — so OCR is mandatory, and they
arrive bundled one customer per page. Measured on those pages the parser
identifies the provider 46/46 and reads an account reference 43/46; against
the dev database that is 39/46 (85%) exact auto-match, 40/46 identified, with
the rest genuine review cases. That closes the OCR-provider question in favour
of self-hosted Tesseract: it clears the bar for a queue where a human confirms
every row, and OcrProvider keeps a managed API a one-line swap.
The samples corrected three things the spec had wrong or unknown:
- Clave catastral is NOT predial. DATMEX.clave (934 rows) is what CESPT and
predial bills print; DATMEX.predial, which PROPERTY_TAX.accountNumber holds,
has 663 distinct values across 1135 rows and appears on no statement. The
clave now lives on Property.cadastralKey as the matcher's secondary key;
predial is left untouched. This had been blocking predial matching.
- Gas was recoverable: 160 of 334 DATMEX.gas values are real account numbers
(the rest are ESTACIONARIO/CILINDRO descriptors), now in GAS.meterNumber.
- Phone is one billed line per property (534/18/1 across phone1/2/3), so the
new TELEPHONE ServiceKind backfills from phone1 only, not three rows.
Matching is scoped to one column per service kind and never reads the customer
name — a CESPT receipt prints ARNAIZ ROSAS ELSA AURORA for an account this
office holds under CATT, RANDY, because the printed name is the registrant,
not the current owner. Where a provider prints a payment barcode it beats the
printed label (one CFE label OCR'd a digit too many while its barcode was
correct) and the two cross-check, with disagreement forcing review.
Confirming a document whose service had no reference writes it back, so gas
and any other cold start is a one-time cost rather than a permanent queue.
Verified end to end against the live dev API and MinIO: real scans uploaded
over HTTP, matched, confirmed against a check, and the resulting rows checked
in MySQL (negative amounts, captureSource=OCR, concept derived from the batch
kind, captureRef linking back to each page). Re-confirming a posted batch is
refused. Test data was removed afterwards.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The --sync path had never been run and was broken in several ways. Fixed and
verified against the dev DB (two consecutive syncs, both exit 0, 32/32
assertions: stable PKs, manual-row preservation, changed-row updates,
legacy-delete, no child duplication, zero FK orphans; idempotent).
- policies/properties: reuse each legacy row's existing id (by provenance)
BEFORE building child rows, so children no longer point at a discarded fresh
uuid; rebuild legacy-owned children via scoped delete + reinsert.
- customers: replace zip(customers, refs) (mispaired almost every row) with a
ref-grouped id remap; names now restore and no spurious customers appear.
- drop the invalid Vehicle @@unique(legacySourceTable, legacyId) — one legacy
policy row carries up to 3 vehicles sharing a legacyId; handle via delete+reinsert.
- upsert lookup tables (policy_types, insurance_providers, type_transactions,
adjusters) by natural name and remap child FKs instead of inserting fresh
uuids that nothing points at.
- transactions: drop updatedAt=NOW() (no such column); guard report formatting
on NULL legacySourceTable (manual rows). Same report guard in bank.
- add manual-safe prune (prune_empty_customers.py --sync, in SYNC_STEPS): prune
only legacy-owned empties, never manually-added customers.
web: customer-detail mini tx list now strikes voided rows with an "(anulado)"
tag (was the last void-UI rendering gap; /estado-cuenta already handled it).
docs: RESUME.md updated — Phase B sync marked verified end-to-end, void-UI
browser pass recorded.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Working-tree checkpoint of in-progress work carried across prior
sessions on the feat/crud-rbac branch, committed so it lands on the
remote alongside the CI changes.
- Operaciones admin panel: apps/api/src/ops (ingest upload, backup /
restore / re-import jobs) wired into app.module + RBAC abilities, and
the apps/web/src/app/operaciones page. docker-compose gets INGEST_DIR
/ BACKUP_DIR volumes; .gitignore excludes migration/ingest + backups.
- migration/sync.py plus transform_*.py / run_all / config / dbenv /
blob_extract adjustments for the additive sync path.
- crud/rbac phase-5 web bits: AppShell, api/labels/types libs, globals.
- schema.prisma + PLAN/RESUME doc updates.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
migration/transform_properties.py loads properties, property_services and
trust_accounts from staged DATMEX/PROFILE, resolving each property's customer
FK through customer_legacy_refs. Services are derived from DATMEX's own
account/route/meter fields (the authoritative data); PROFILE flags — merged
best-effort on (numer_id,casa,direccion), which matched 1519/1519 — only
refine each service's `active`. Trust accounts are 1:1 from DATMEX trust
fields; TRUSTVENCE (overlapping) deferred to reconciliation; blobs are step 4.
Loaded/validated (dev): 1519 properties (0 orphans, 1 blank id skipped),
3486 services (ELECTRIC 1118 / PROPERTY_TAX 939 / WATER 859 / GAS 335 /
OTHER 115 / FEDERAL_ZONE 76 / CABLE 41 / ALARM 3), 553 trust accounts —
counts track the PROFILE enrollment flags.
Reproducibility (asked: dev must be redoable in prod):
- migration/dbenv.py: single DB-target source = deploy/.env.<env>'s
DATABASE_URL; connect(env) + env_arg() (--env, default dev).
- transform_customers.py / transform_properties.py now take --env instead of
hardcoding .env.dev.
- migration/run_all.py: runs every step in dependency order against --env
(optional --stage re-extracts from Access first). Reproducing dev->prod is
`run_all.py --env prod` after deploying the prod stack + prisma db push.
All steps are idempotent (truncate+rebuild); re-run yields identical counts.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
rmancinasandClaude Opus 5