jorgecuadros-platform/RESUME.md

Resume Notes — Jorge Cuadros & Assoc. Unified Platform

Comprehensive state-of-the-world doc for picking this project back up. Read this before doing anything else in a fresh session — it front-loads everything that took multiple rounds of investigation to establish.

Companion doc: the full architecture/migration plan is PLAN.md in this repo — that is the source of truth for the design. (It began as ~/.claude/plans/logical-yawning-tome.md on the old Windows machine; that copy is gone and no longer authoritative.) This file is the "what happened and what's next" companion, not a replacement. Read both.

---

1. The goal

Jorge Cuadros & Assoc. runs two lines of business — property/utility management and insurance brokerage — out of separate, decades-old MS Access databases, plus a third Access file that's the office's own bank checking register. The same people are customers of both lines but there's no shared customer record between systems. Goal: one platform with a single unified customer record, from which staff see and manage that customer's utility services and insurance policies and shared billing/transaction history — replacing the Access files and the old, insecure PHP internal app.

There is also a separate, pre-existing customer-facing portal (PHP + MySQL, with a companion mobile app) that customers use to view statements, make payments, and order propane. That portal is out of scope to rebuild — it stays exactly as-is — but the new platform has to keep it supplied with live data. That constraint is what drove the database-engine and infrastructure decisions below.

2. Where everything lives (file paths)

Paths below are the current macOS machine. The project moved Windows → macOS on 2026-07-22; anything still written as C:\Users\ricar\... in older notes is stale.

Source data (do not modify — read-only references), all in ~/Downloads/JorgeCuadros-Legacy/:

• UTILITIES.accdb — utilities business, 52 tables, ~538MB
• SEGUROS 16.mdb — insurance frontend shell, no data tables, but holds all of the insurance line's Reports/Forms/Queries
• SEGUROS 16_be.mdb — insurance backend, 64 tables, ~882MB
• SCOTHIA.mdb — office's own Scotiabank checking register ("chequera"), 7 tables, ~3MB
• utility_dbo.sql — customer portal's live DB dump (1.3 GB, 55 tables)
• jorgecuadros.sql — older/partial export (38 MB, 11 tables), not the portal live DB
• Full structural reference for all three, usable without Windows or the original files: docs/LEGACY_DATABASES.md — every table, every column with type/nullability, the cross-reference keys between the three databases, and every known data-quality quirk (the UTF-16 decode bug, the corrupted MULT row, near-duplicate snapshot tables, etc.), all generated from a live read of the real files via migration/catalog_schema.py. Regenerate it if the source files change; the raw JSON it's built from is checked in at migration/catalog.json.
• Queries/Forms/Reports reference: docs/LEGACY_DATABASES_OBJECTS.md — none of this is visible via ODBC/pyodbc; it required DAO COM automation (migration/catalog_objects.py, needs pywin32) instead. Found 311 Reports, 271 Forms, and 1,274 Queries (751 "real," the rest Access-internal hidden subquery caches) across the three populated files — importantly, SEGUROS 16.mdb (which has zero data tables) turned out to hold all of the insurance line's Reports/Forms/Queries; SEGUROS 16_be.mdb is confirmed pure data storage. The real queries' full SQL text is the best available record of actual business logic (billing math, renewal batching) — worth reading before reimplementing any given feature from scratch. Raw JSON checked in at migration/objects.json.
• jorgecuadros_app.sql / jorgecuadros_app (1).sql (on the old machine) — MySQL dumps of the portal's tracking/analytics sidecar DB (browse_tracking, devices push-tokens, task_tracking). Not the portal's real data DB; superseded by utility_dbo.sql above.

Customer-facing portal (out of scope to rebuild, but the sync target):

• ~/PhpstormProjects/my-jorgecuadros-web — PHP/mysqli, ~397 files, core in scripts/functions.php. Reads/writes utility_dbo.

Old internal app (reference-only, not being built on):

• jorgecuadros-intra-webapp (on the old machine) — PHP, MySQL (webapp_jorgecuadros). Its db/webapp_jorgecuadros.sql is a useful reference for field mappings/business logic. Code itself is not reused — see §4.

New platform (the actual deliverable):

• ~/WebstormProjects/jorgecuadros-platform — the repo. Is a git repo, branch master, 21 commits, remote git.mancinas.io/rmancinas/jorgecuadros-platform.

The plan document:

• PLAN.md in this repo — full architecture, source-data inventory per table, target data model, migration strategy, infrastructure/sync design, locked decisions, build sequencing. This is now the source of truth for the design (the original ~/.claude/plans/logical-yawning-tome.md lived on the old Windows machine). This RESUME.md is the "what happened / what's next" companion.

Staged data (gitignored, regenerable):

• migration/output/stg_utilities|stg_seguros|stg_scothia/*.parquet — regenerate in ~2 min with load_staging.py --output-dir ./output. Every transform step reads from here.

3. Key decisions (locked — see PLAN.md → "Decisions (locked)")

Decision	Answer	Why
Stack	Next.js (React/TS) + NestJS (TS) + Prisma	Type safety, parameterized queries by default (kills the SQL-injection class of bug the old app had everywhere)
Database engine	MySQL (not Postgres — reversed mid-session)	The customer-facing portal's PHP code (mysqli) isn't being rewritten, and shared hosting can't run Postgres. Using MySQL everywhere avoids a cross-engine sync layer.
Repo	New repo, not built on jorgecuadros-intra-webapp	That repo has SQL injection in every query (src/core/db.php string-concatenates $_POST) and plaintext password comparison (src/core/auth.php) — not worth patching
Historical data	Migrate everything, no cutoff	Source tables are small (largest ~16k rows); completeness is cheap
Customer portal	Stays as-is, not rebuilt	Explicit user decision
Infrastructure	Internal server (private) + new VPS (Tailscale-linked) running a MySQL replica	Internal server has no inbound internet exposure; shared hosting can't be a replication target; a VPS you control can be both a real replication node and internet-reachable for the portal
Auth mechanism	Session-based (Passport + express-session), Argon2 password hashing	Implemented already — see §5

4. What is built and verified

Everything below was run and confirmed working, not just written. §8 carries the per-module detail and the running status; this section is the structural tour.

4.1 Repo scaffold

• jorgecuadros-platform/ — npm workspaces (apps/*, packages/*)
• apps/api — NestJS. Builds clean under strict TypeScript (npx nest build in apps/api, zero errors).
  • src/main.ts — global ValidationPipe (whitelist + forbid unknown fields — structural replacement for the old app's total lack of input validation), session middleware, CORS.
  • src/auth/ — AuthService.validateUser() verifies passwords with argon2.verify() (replaces passwd = '$password' plaintext SQL comparison in the old app), LocalStrategy, SessionSerializer, AuthenticatedGuard (replaces manually-called validate_session()), AuthController (/auth/login, /auth/me, /auth/logout).
  • src/users/, src/prisma/ (global PrismaModule/PrismaService).
• apps/web — Next.js App Router shell. Builds clean (npx next build).
• packages/database — Prisma schema + generated client.

4.2 Prisma schema (packages/database/prisma/schema.prisma)

Full target data model implementing the plan's design, validated and generating a working client against MySQL:

• Identity: Customer, CustomerLegacyRef (generalizes the old customer_mapping bridge table — one row per legacy record folded into a unified customer, with provenance)
• Insurance: InsuranceProvider, PolicyType, Policy (consolidates INCENDIO/MULT/M EMPR/6 auto-table variants/LICENCIAS into one table with a type discriminator), PolicyPaymentInstallment (unpivots the 4 hardcoded payment-installment columns found on every legacy policy table), Vehicle (unpivots MCA2's 3 hardcoded vehicle slots), InsuredDriver, PolicyBeneficiary, Claim, Adjuster, PolicyDocument
• Utilities: Property (shared with insurance — the actual unification point), PropertyService, ServiceDocument, TrustAccount
• Shared ledger: Transaction (unifies all the EFECTIVO*/billing-period snapshot tables), ExchangeRate, TypeTransaction
• Bank register (SCOTHIA): BankTransaction, BusinessLineCategory
• Admin: User (hashed passwords, roles), ActivityLog, EmailTemplate, EmailCampaign, EmailLog

Every model sourced from a legacy Access table carries legacySourceDb/legacySourceTable/legacyId provenance columns for traceability and idempotent re-runs. Long-text fields (notes, observations, description, etc.) are explicitly @db.Text — MySQL's default String is VARCHAR(191) and would silently truncate them otherwise (this was caught and fixed during the Postgres→MySQL swap).

Regenerate the client any time with:

cd jorgecuadros-platform
DATABASE_URL="mysql://user:pass@localhost:3306/placeholder" npx prisma generate --schema=packages/database/prisma/schema.prisma

(A real DATABASE_URL isn't needed for generate/validate, just a syntactically valid one. A live dev DB is available now — see §7 — so prisma db push works too.)

4.3 Docker Compose / Dockerfiles

• docker-compose.yml — mysql:8.4 + api + web services, healthchecked.
• docker/api.Dockerfile, docker/web.Dockerfile — multi-stage builds.
• .env.example — DATABASE_URL, SESSION_SECRET, WEB_ORIGIN, NEXT_PUBLIC_API_ORIGIN.
• Not run — this environment has no Docker installed (docker --version fails). Untested beyond visual review; verify on a machine with Docker before relying on it.

4.4 Migration pipeline (migration/) — run end-to-end against real data

• config.py — manifest of the Access source files (SOURCE_ROOT + per-source exclude lists for confirmed-scratch tables, with reasoning in comments)
• extract.py — shells out to mdbtools (mdb-tables / mdb-export, Homebrew). Rewritten from the original pyodbc + Windows Access ODBC version during the macOS move; public interface (connect/list_tables/read_table) unchanged. mdbtools also sidesteps both bugs the pyodbc path needed workarounds for: it reads accented-column tables (PROPANO, FALTANTES AGUA, TIT) cleanly instead of hitting a UTF-16 decode error, and it doesn't abort a whole table on MULT's corrupted row.
  • What mdbtools cannot do is read Forms/Reports/Queries. Those were already captured on Windows via DAO COM and are frozen in migration/objects.json + docs/LEGACY_DATABASES_OBJECTS.md — nothing is lost, but they can't be re-extracted on this machine.
• load_staging.py — dumps every non-excluded table into either Parquet (--output-dir, no DB needed) or MySQL (--database-url, one database per source: stg_utilities/stg_seguros/stg_scothia). 82 tables staged, zero unhandled errors.
• reconcile.py → RECONCILIATION.md — the duplicate/distinct pass (step 2). See §8 step 3.
• transform_*.py, prune_empty_customers.py, blob_extract.py — steps 3–4, all idempotent (truncate + rebuild).
• run_all.py — the entry point. Runs every step in dependency order. See the ⚠️ in §7 for why you should never run a single transform on its own.
• dbenv.py — --env <name> reads deploy/.env.<name> for the target DB.
• requirements.txt — pandas, pyarrow, sqlalchemy, pymysql, boto3 (no pyodbc — that was the Windows path).

To rerun (from migration/, venv at migration/.venv):

./.venv/bin/python load_staging.py --output-dir ./output   # re-extract from Access (needs mdbtools + the source files)
./.venv/bin/python run_all.py --env dev                    # full transform+load; add --stage to re-extract first
./.venv/bin/python run_all.py --env dev --sync             # additive sync: upsert legacy by provenance, keep manual rows, prune legacy empties

--sync mode (Phase B) upserts legacy-owned rows by their provenance keys and preserves manual rows (legacyId IS NULL); every transform reuses each row's existing PK, rebuilds legacy-owned children by scoped delete + reinsert, and drops legacy rows gone from source. Verified end-to-end against dev 2026-07-24 — see §6 item 6.

5. Infrastructure & sync architecture (designed, not yet built)

• Internal server — on-prem, private IP 192.168.1.xx, no inbound internet exposure. Runs the platform + canonical MySQL (source of truth).
• New VPS (Hetzner or DigitalOcean — not yet provisioned) — becomes what mysql.freakma.com resolves to. Runs a MySQL replica. Because it's infrastructure the user controls (unlike shared hosting), it can be a real MySQL replication target.
• Tailscale — mesh VPN joining the internal server and the VPS, so they can reach each other without opening inbound ports anywhere.
• Internal → VPS: one-way native MySQL replication (binlog/GTID) for the subset of data the portal needs to read (statements, balances, customer profile). Internal-only tables (staff notes, adjuster info, activity logs) are deliberately excluded from what replicates.
• VPS → Internal: the portal also writes (payment submissions, propane orders) — one-way replication can't carry that back, and multi-master MySQL replication was deliberately ruled out as too fragile for this system's size. Instead: unreplicated "inbox" tables on the VPS (payment_submissions, propane_order_requests) that the portal writes to directly, polled every 1–5 min by a worker on the internal server (over Tailscale) that turns new rows into real records.

6. Open items

Resolved since this section was first written (kept as a pointer, not a to-do): utility_dbo schema (full dump on disk), CI/CD (Gitea Actions), i18n (Spanish-first), and the reconciliation pass (done, then corrected) are all closed. See §3 and §8.

Still open:

1. VPS not yet provisioned — provider (Hetzner vs DigitalOcean), size, Tailscale + MySQL replica setup. Pure ops task; the design is settled (§5). This is the only genuinely blocking item left on the roadmap.

2. Sync worker not built — unblocked now that utility_dbo and the portal code are on disk, but depends on the VPS existing. Portal write points to poll: peticion_gas, PayPal payments, notifications_settings, verification_codes.

3. Old external-DB credential — the old repo's dbConnection.php has a hardcoded plaintext MySQL password committed to git history. Not carried into the new platform, but rotate it regardless; it is already exposed.

4. bank_transactions.categoryId is null on all 22354 rows — RESOLVED as won't-build (plan step 7). The concept→ramo classifier was investigated and dropped: concepto is a payee name (0 of 22354 match a category), and TABLA RAMODOS is a property-management expense chart of accounts + owner names, not the insurance/servicios/fideicomiso split it was assumed to be — so a classifier would invent data rather than produce a business-line view. The /banco module intentionally has no category dimension. See §8 step 7(b).

5. TRASPASOS PAYPAL is a clearing account, not a customer — carries -7.03M MXN over 309 movements and therefore tops the adeudo worklist. Deliberately not special-cased in code; needs a business decision on how to model it.

6. DB Operations — Phase B (additive sync) — VERIFIED END-TO-END against dev DB 2026-07-24. Phase A provides the admin-only /operaciones page + ops API module (ability db:manage, ADMIN), ingest folder, backup, restore, and destructive re-import. Phase B enables SYNC: OpsService creates a safety backup and runs run_all.py --sync; transforms upsert legacy-owned rows by provenance keys while preserving existing PKs and rows whose legacyId IS NULL (manual). Prisma enforces provenance uniqueness for properties, policies, transactions, and bank transactions (the vehicle unique was removed — one legacy policy row carries up to 3 vehicles that share a legacyId, so provenance is not unique per vehicle; vehicles are rebuilt by scoped delete + reinsert). Sync skips blob extraction, and runs a manual-safe prune (prune_empty_customers.py --sync — only prunes empties that carry a legacy ref, never manually-added customers) because the customer upsert otherwise re-creates every previously-pruned empty from Parquet.

   The as-written sync was broken and had never been run; a batch of bugs were fixed on 2026-07-24 before it passed (fresh-uuid child FKs in policies/properties, unconditional child inserts, a zip(customers, refs) mispairing in transform_customers, invalid vehicle unique, lookup tables built with fresh uuids but never upserted, a updatedAt=NOW() on a table with no such column, and report crashes on NULL legacySourceTable for manual rows). Verified with migration/ verify_sync.py-style harness: two consecutive run_all.py --sync runs both exit 0 and pass 32/32 assertions (stable PKs, manual-row preservation, changed-row updates, legacy-delete, no child duplication, zero FK orphans), idempotent (customers stable at 1537). Schema pushed to dev, Prisma client regenerated, API build clean. Migration/web changes uncommitted as of this update. Still open before production: run the same sync from the /operaciones UI (OpsService path) and against a prod-shaped DB.

7. Environment notes (current macOS machine)

• macOS (Darwin 25.5.0), zsh. Node v22.23.0. Python 3.14.6 in migration/.venv. Homebrew, Docker, MySQL/MariaDB client all present.
• mdbtools installed via Homebrew — the extraction toolchain. No Access ODBC driver (and none needed).
• npm is pnpm-aliased, and pnpm ignores the workspaces field. Consequences:
  • there is no root node_modules/.bin. Binaries live per-app: apps/api/node_modules/.bin/nest, apps/web/node_modules/.bin/next.
  • Prisma CLI is run as npx prisma@5.
• Dev servers (both must be up to use the UI). ⚠️ Ports come from the env files, not the framework defaults — apps/api/.env sets PORT=4501 and WEB_ORIGIN=http://localhost:4500, and apps/web/.env.local points at NEXT_PUBLIC_API_ORIGIN=http://localhost:4501. This doc said :3001/:3000 until 2026-07-27; that was wrong and cost a debugging detour.
  • API cd apps/api && ./node_modules/.bin/nest start --watch → :4501
  • Web cd apps/web && ./node_modules/.bin/next dev -p 4500 → :4500
  • Dev login: admin@jorgecuadros.local, password from apps/api/scripts/seed-user.mjs (SEED_PASSWORD env overrides the default).
• Dev DB: 192.168.4.212:3307 (cubex Swarm stack jorgecuadros-dev-db). Credentials in gitignored deploy/.env.dev. MinIO for documents: 192.168.4.212:9100, bucket jorgecuadros-documents.

Traps worth knowing before you lose an hour to one:

• ⚠️ Never run a single transform_*.py on its own — use run_all.py. Each step truncates what it owns, so a lone run orphans everything downstream. prune_empty_customers.py must re-run after any ledger change, and blob_extract.py must follow properties + policies or the uploaded MinIO objects end up with no rows pointing at them.
• ⚠️ Never run next build while next dev is running — they share .next and the dev server starts serving blank white pages. Recovery: kill the dev server, rm -rf apps/web/.next, restart.
• ⚠️ mdb-export formats numerics per Access column type (5000 from one table, 27000.0000 from another). Never string-compare staged Parquet numerics across two tables — canonicalize first. This exact trap produced a wrong, locked migration decision that shipped 12386 duplicate rows into the ledger (§8 step 4).
• Shell on this machine: head is aliased to an HTTP HEAD tool — use /usr/bin/head. grep --include=*.md trips zsh globbing — quote the pattern.

8. Plan locked — next actions

All four previously-open decisions are now locked (2026-07-22). See PLAN.md → "Decisions (locked)" for the authoritative record:

• Extraction toolchain (macOS): mdbtools (installed + verified against the real files).
• i18n: Spanish-first.
• CI/CD: Gitea Actions on git.mancinas.io → registry → Portainer.
• utility_dbo: resolved — full dump (utility_dbo.sql, 1.3 GB, 55 tables) and the portal codebase (~/PhpstormProjects/my-jorgecuadros-web) are both on disk.

Environment: moved Windows → macOS (2026-07-22). See §7 for the current machine.

Execution queue. Steps 1–6 below are done; they are kept because each carries the data findings and corrections that came out of doing it. Skip to the ⏭ marker at the end for what's actually next.

1. Port the extraction layer to mdbtools. DONE. Rewrote migration/extract.py to shell out to mdb-tables/mdb-export instead of pyodbc. Keep the same public interface (connect/list_tables/read_table) so load_staging.py and config.py are unchanged beyond the already-fixed SOURCE_ROOT. Carry over the two hard-won fixes conceptually: accented-column tables (mdbtools reads PROPANO cleanly — verified) and the corrupted MULT row (mdb-export's -b / error handling; confirm the bad row is skipped, not fatal).

2. Re-run staging DONE — staged Parquet regenerated on this machine (load_staging.py --output-dir ./output), 82 tables.

3. Reconciliation pass (plan step 2) — DONE (migration/reconcile.py → RECONCILIATION.md), and corrected 2026-07-22. Current verdicts:

   • EFECTIVO_BACKUP is a stale backup copy of EFECTIVO — 12386 of its 12387 rows are verbatim duplicates (customer + timestamp-to-the-second + amount + concept text), leaving 1 new row. Load EFECTIVO in full, de-dup BACKUP on the business key. Never de-dup on folio — it is per-table sequential and collides (12363 shared numbers, 12204 of them on different payments).
   • The billing tables (datos2/FEE ANUAL/fee15) are disjoint period runs — union all, no de-dup.
   • COBRO3 is a charge batch, not a customer snapshot — DATGRAL is the sole master.

   ⚠️ This file and PLAN.md previously said the opposite about EFECTIVO ("near-disjoint ledgers, migrate both"). That was a bug, not a finding — see the Shared ledger entry in step 4 below for the root cause and the fix. If you read a doc, comment, or commit message from before 2026-07-22 that says "migrate both, no folio de-dup", it is stale. The authoritative rules live in PLAN.md migration step 2.

4. Transform + load (plan step 3) — IN PROGRESS.

   • Customers — DONE (migration/transform_customers.py). Loaded into the dev DB: 1682 customers (1172 utilities master + 510 insurance-only), 2242 legacy refs (all traceable), 560 insurance rows linked via num_util with 0 broken refs, 542 merged identities spanning both business lines; linked customers enriched with insurance-only ID-doc fields. COBRO3 excluded. Re-runnable (truncate+rebuild); needs staged Parquet present (load_staging.py --output-dir ./output first).
   • Properties — DONE (migration/transform_properties.py): 1519 properties (0 orphans), 3486 services, 553 trust accounts from DATMEX/PROFILE; PROFILE flags matched 1519/1519.
   • Policies — DONE (migration/transform_policies.py): config-driven consolidation of all insurance lines into policies (2378: AUTO 1307 / MULT 760 / LICENCIAS 306 / M_EMPR 5; 10 skipped for unresolved customer, 0 orphans) + 4678 installments, 1110 vehicles, 513 insured_drivers, 126 beneficiaries, 1 claim, 5 policy_types, 15 insurance_providers, 17 adjusters. Unmodeled coverage columns preserved verbatim in coveragesJson. Verified a unified customer (EARWOOD, DAVID) carrying both a utility property+services and 2 MULT policies — the cross-line customer view works at the data layer.
   • Shared ledger — DONE (migration/transform_transactions.py): 33475 transactions (UTILITY 33180 / INSURANCE 295, 0 orphans) unioning EFECTIVO (13695) plus only the 1 business-key-new row from EFECTIVO_BACKUP, all three billing tables (datos2/FEE ANUAL/fee15), the FM3 fee stream (amount=fee+tax+multa), IVA 2015 (nominal date), and insurance EFECTIVO; plus 79 type_transactions and 2301 exchange_rates. Skipped 22 no-customer + 272 no-date + 12417 EFECTIVO_BACKUP duplicates. Corrected 2026-07-22 — this used to load 45861 rows. reconcile.py had string-compared monto, which mdb-export serializes at a different precision per Access column type (5000 vs 27000.0000), so it saw 2 overlapping rows instead of 12386 and ruled EFECTIVO_BACKUP an independent ledger. It is a stale backup copy: 12386 of its 12387 rows match an EFECTIVO row on customer + timestamp-to-the-second + amount + concept text. The ledger was double-counting those payments, roughly doubling every customer's historical receipt total. Both reconcile.py (canonicalizes numeric key columns now) and transform_transactions.py (de-dups on the business key, never on folio — folio collides) are fixed, and run_all.py --env dev has been re-run end to end. Lesson for any future reconciliation: never compare mdb-export output as raw strings across two tables — canonicalize numerics first.
   • Bank register — DONE (migration/transform_bank.py): 22354 bank_transactions from SCOTHIA DATOS I/E as signed amounts (income +, expense -; net +899,375.77), 66 business_line_categories. No customer FK; categoryId left null (concept->ramo classifier is a later enhancement).
   • Documents — DONE (migration/blob_extract.py, migration step 4): carves embedded files out of the Access OLE wrapper (magic-byte detection) and uploads to MinIO on cubex (stack jorgecuadros-dev-minio, S3 at 192.168.4.212:9100, bucket jorgecuadros-documents), writing service_documents/policy_documents pointer rows. Loaded 70 documents (3 service bills + 67 policy foto/docs, 0 orphans, ~290 MB). Data finding: the LONGBINARY columns are almost entirely empty — DATMEX's real bill-scan columns are ILUZ/IAGUA/IPREDIAL/ITEL (not doc_1/doc_2), but only 3 cells populated across 1520 rows; the *_MENS tables are mail-merge templates (correctly excluded). The 538MB/882MB source files are mostly Access bloat, not documents. Migration steps 1-4 COMPLETE.
   • Customer module (plan step 3) — DONE: apps/api/src/customers/ (list/search/detail/stats)
     • apps/web /clientes and /clientes/[id], Spanish-first, verified against real data.
   • Insurance module (plan step 4) — DONE: apps/api/src/policies/ (GET /policies with search over policy number / customer / agent / plate / driver name, vigencia buckets active|expiring|expired|undated, ramo + aseguradora + liquidada filters, 5 sorts; /policies/stats, /policies/facets, /policies/:id) + web /polizas (renewals-first browser, clickable stat cells) and /polizas/[id]. Cross-links both ways with the customer view. Data finding: the policies.total column is dead — only 2 of 2378 rows are non-zero (1585 are literally 0, 791 null) and one of those two is lower than its own net premium, so every premium headline and the premium sort use netPremium (populated on 2377/2378). This also fixed a live bug on the customer detail page, which was showing "$0.00 Total" for 1585 policies.
   • Utilities module (plan step 5) — DONE: apps/api/src/properties/ (GET /properties with search over address / customer / service account number / meter / trust number / phones, filters for service kind, municipality, trust bank, trust bucket (with|without|active|expiring|expired|undated) and hasServices, 5 sorts; /properties/stats, /properties/facets, /properties/:id) + web /servicios (renewals-first property browser with clickable stat cells and a clickable service-mix strip) and /servicios/[id] (services, fideicomiso, linked policy, owner + sibling properties, owner-level utility ledger, documents). Cross-links both ways with the customer and policy views. Data findings: (a) the trust renewal date staff chase is trust_accounts.dueDate2 — DATMEX's vence2, one year after vence1 on 531 of 541 dated trusts (18 due within 30 days, 119 already overdue); (b) properties.zone is effectively dead (1444 of 1519 null, the rest near-unique), so it is not a facet; (c) the municipality that bills a property lives in the predial service's notes (ROSARITO 566 / TIJUANA 221 / ENSENADA 152, 939/939 populated) — that is the real geographic filter. PropertyService.notes means something different per kind (municipality / CFE PAR-IMPAR cycle / gas supply type), so the UI labels it per kind. 240 of 1519 properties have no service rows at all — surfaced as its own bucket.
   • Billing / statements module (plan step 6) — DONE: apps/api/src/billing/ (GET /billing movement browser with search over customer / referencia / cheque / concepto / periodo, filters for línea, moneda, cargo-vs-abono, concepto (typeId), origin table and a from/to date range, 5 sorts, and totals for the whole filtered set; GET /billing/balances per-customer balances with owing/credit/settled buckets and 4 sorts; /billing/stats, /billing/facets, /billing/customers/:id) + web /estado-cuenta (two tabs: "Saldos por cliente" worklist and "Movimientos" ledger) and /estado-cuenta/[id] (the statement: balance per currency, the same balance split by business line, cargos por concepto with proportional bars, and the full movement table with a running balance). Cross-links from the customer and property detail pages. Data findings: (a) transactions.amount is a signed ledger — every charge type is negative without exception (WATER 3115/3117, ELECTRIC 2191/2191, PROPERTY TAXES 926/926, TRUST FEE 188/188) and every deposit type positive (CHECK/CASH DEPOSIT, PAYPAL, all of EFECTIVO), so SUM(amount) is the balance and negative = the customer owes. (b) Currency is not summable. 912 of the 1269 customers with a ledger move in both MXN and USD; the charge side (datos2/FEE ANUAL/fee15) is MXN-only while receipts arrive in both, and no per-movement exchange rate was ever stored. Every figure in the module is per currency; the balance filter/sort takes a currency argument rather than collapsing. (c) type_transactions.nameEs is entirely null — the legacy TYPE OF TRX table has an ESPAÑOL column but all 79 rows are empty, so the API can only return English names. labels.ts:TX_TYPE_LABELS supplies Spanish for the real service/payment categories; the rest of the 79 "types" are payee names (LORETO GONZALEZ, ALBERCAS VALLARTA…) that fall through untranslated, which is correct. (d) The biggest debtor by far is "TRASPASOS PAYPAL" (-7.03M MXN over 309 movements) — a house/clearing account, not a person. Left in rather than special-cased, but it will head the adeudo worklist until someone decides how to model it.
   • Bank register module (plan step 7) — DONE: apps/api/src/bank/ (GET /bank register browser with search over concepto/beneficiario, cheque reference, notes and amountInWords; filters for direction (income|expense|void), cleared status and a from/to date range, 5 sorts, and income/expense/net totals for the whole filtered set; /bank/stats, /bank/facets (year list), /bank/summary year/month rollup with a running net figure) + web /banco (two tabs: "Movimientos" register and "Resumen por periodo" with clickable year→month drill-down). Added to the AppShell nav as "Chequera". Verified end-to-end in the browser: totals reconcile (6948 income + 14615 expense + 791 void = 22354; net +899,375.77 matches stats; 2013 months open at $0 and close at the year's net $794,295.78). Design decisions / data findings: (a) Kept separate from /estado-cuenta on purpose — this is the office's own chequera, not customer money; the two ledgers are never summed or shown together. Distinct nav entry, distinct page, distinct API module. (b) No category/ramo dimension, and the concept→ramo classifier was NOT built (closes open item §6.4): the data cannot support it. DATOS E/I have no ramo column to migrate; concepto is a payee name (PAYPAL, CFE, ~1,900 individuals), and 0 of 22354 concepts match a business_line_categories name; and the 66 TABLA RAMODOS rows are a property-management expense chart of accounts (Payroll, Pool Labor, Gardening) + owner names, not the insurance/servicios/fideicomiso split the migration comment implied. A classifier would invent data, so categoryId stays null and the module does not filter on it. Register is browsable by date/payee/amount/cheque instead. (c) Single currency (MXN). SUPERSEDED 2026-07-27 by the multi-bank chequera (step 11, docs/RECEIPT_CAPTURE_SPEC.md §3). The office keeps more than one register, so bank_transactions now carries a required bankAccountId and every read in the module is scoped to exactly one BankAccount, whose currency the movements inherit — there is still no currency column on the movement itself, because a real bank account doesn't mix currencies. All 22,669 migrated rows are the Utilities/Scotiabank MXN account (backfilled by migration/backfill_bank_accounts.py, which run_all.py runs before transform_bank.py), which is why every amountInWords is still spelled out in PESOS. There is deliberately no "all accounts" option: summing an MXN and a USD register would repeat the currency-collapsing mistake the billing module warns against. (d) The "acumulado" is net movement since the register opened, not a bank balance — SCOTHIA carries no opening balance (its ban table holds only the bank's name), so the running total starts at 0 in 2013. Labelled as such in the UI so it is never read as a statement balance. (e) Sign convention (from transform_bank.py): positive = ingreso, negative = egreso, exactly zero = a cancelled/void cheque (787 of 791 say CANCELADO/VOID) — voids are excluded from both the income and expense sides. (f) Multi-account since 2026-07-27. /banco opens on an account picker (the last account is remembered per browser) and reads every figure in that account's currency; /banco/cuentas manages banks and accounts under a new MANAGER bank:manage-accounts ability. Accounts are never deleted — the bankAccountId FK is required, so a used account can only be closed (active: false), which hides it from new captures but keeps its history readable. An account's currency is immutable after creation, since its booked movements are denominated in it.
   • Full pipeline reproducible in one command: run_all.py --env <env> runs customers → properties → policies → transactions → prune → bank accounts → bank → blobs in order (all idempotent); add --stage to re-extract from the Access files first. Verified end-to-end against dev.

5. Infra — DONE. Dev MySQL deployed to the cubex Swarm via the Portainer API as stack jorgecuadros-dev-db (MySQL 8.4, 192.168.4.212:3307, node cubex labeled jorgecuadros_db=true); Prisma schema pushed (26 tables). Stack file deploy/jorgecuadros-db.stack.yml deploys prod from the same file as jorgecuadros-prod-db on :3306. MinIO for documents deployed as jorgecuadros-dev-minio.

6. Staff web UI — DONE for all five modules (§8 step 4 + step 7: clientes, polizas, servicios, estado-cuenta, banco). Spanish-first, session-cookie auth against the API, verified against real migrated data. All app-layer feature work is complete.

---

• Sync implementation — DONE + VALIDATED end-to-end against dev 2026-07-24. run_all.py --sync performs the non-destructive legacy upsert path for customers, properties, policies, transactions, and bank rows, plus a manual-safe empty-customer prune. It preserves manual rows and stable legacy-owned primary keys; the admin SYNC job auto-creates a pre-sync backup. The as-written code was broken and had never been run — a batch of bugs was fixed before it passed (see §6 item 6). Two consecutive syncs both exit 0 and pass 32/32 assertions (added, changed, removed, and manually-created rows), idempotent. Remaining: exercise the same path from the /operaciones admin UI and against a prod-shaped DB.
• Plan step 9: portal sync worker remains separate and blocked on VPS provisioning. This Phase B feature synchronizes Access source files into the internal platform; it does not yet poll utility_dbo inbox tables or replicate portal-facing data to a VPS.
• Small / open: (a) TRASPASOS PAYPAL clearing account still tops the adeudo worklist (§6.4d) — a business modelling call, not code. (b) Credential rotation on the old repo's exposed MySQL password. (c) The /estado-cuenta browser visual pass. DONE 2026-07-24 — verified vs dev: Anular buttons admin-gated, voided rows struck + excluded from totals, clicking Anular voids end-to-end (note: it uses a blocking window.confirm). Customer-detail mini tx list now also strikes voided rows ("(anulado)" tag) — was the last void-UI gap.

---

Statement OCR intake (/recibos) — DONE 2026-08-01

Plan step 11 §2 (docs/RECEIPT_CAPTURE_SPEC.md §2). The last big utilities feature: staff scan the month's utility bills and the machine proposes customer

• amount per page, instead of keying 300+ statements per company by hand. Built in apps/api/src/statements/ and apps/web/src/app/recibos/, posting through step 11 §1.2's BillingService.createBatch seam (source: "OCR", per-document captureRef) so machine and hand capture share one write path and one audit trail. Abilities statement:ingest / statement:review, both STAFF.

Verified end to end against the live dev API + MinIO, not just built: real CFE and Telnor scans uploaded over HTTP, OCR'd, matched, confirmed against a check, and the resulting rows checked in MySQL — negative (charge) amounts, captureSource = OCR, concept auto-derived from the batch's service kind, captureRef linking each transaction back to its page. Re-confirming a posted batch is refused. All test data was removed afterwards.

Everything here was decided from 10 real scanned statements (46 pages), not from the sample-free spec. Shipped-parser results on them: provider 46/46, account reference 43/46, amount 42/46, due date 44/46; matched against the dev database, 39/46 (85%) exact auto-match, 40/46 (87%) identified. The rest are real review cases (one shared account number, three phones not on file, one clave not in the book, one page too poor to read).

Findings that corrected the spec, each of which changed the build:

• The scans have no text layer at all — they are camera images of paper, so OCR is mandatory rather than a convenience, and they arrive bundled, one customer per page.
• Clave catastral is not predial. DATMEX.clave (934 rows, KA903009) is what CESPT and predial bills print; DATMEX.predial — which PROPERTY_TAX.accountNumber holds — has only 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 was left untouched. This is the question that had been blocking predial matching.
• Gas was recoverable after all. The spec said no legacy gas number existed; in fact 160 of 334 DATMEX.gas values are real account numbers (the rest are ESTACIONARIO/CILINDRO descriptors). Recovered into GAS.meterNumber.
• Phone is one billed line per property (534 / 18 / 1 across phone1/2/3), so TELEPHONE — a new ServiceKind — backfills from phone1 only.
• Never match on the printed name. A CESPT receipt for account 5365218 reads ARNAIZ ROSAS ELSA AURORA; the office's book, corroborated by the clave, has CATT, RANDY. The name on a utility bill is the registrant, not the current owner.

migration/backfill_statement_match_fields.py closes those three data gaps on an existing database (idempotent, wired into run_all.py after transform_properties.py, which now produces them directly on a full rebuild). Applied to dev: 934 claves, 160 gas numbers, 534 TELEPHONE rows.

Implementation notes worth keeping:

• OCR is self-hosted Tesseract behind an OcrProvider interface — the provider question is closed on measured accuracy, and a managed API stays a one-line swap in statements.module.ts. tesseract-ocr, tesseract-ocr-data-spa and poppler-utils were added to the API image; if they are missing the module reports itself unavailable and only this feature is disabled.
• Payment barcodes beat printed labels. One CFE label OCR'd a digit too many while its barcode was correct, so the barcode is the source and the label the cross-check; disagreement forces review.
• Detect the provider by brand first, layout only as a fallback — and never interleave the two passes. A scanned CESPT header came back as E BAJA ES PAGO / EALIFORNIA, which is why the layout fallback exists; a Telnor page contains words a CFE layout rule would otherwise claim, which is why ordering matters.
• Parse amounts by separator position. A real Telnor bill OCR'd as $ 649,00; stripping commas as thousands separators turns that into $64,900.
• Two of the three layouts are line-oriented, but the CESPT "RECIBO" is a table whose values sit under column headers — that one needs the word boxes, which is why OcrPage carries geometry and not just text.
• Confirming a document whose matched service had no reference writes the reference back (only into an empty field, and only when exactly one blank service of that kind is a candidate), so gas and any other cold start is a one-time cost rather than a permanent queue.
• Handwritten folder numbers on the bills (9, 405) are not used for matching — Tesseract read 405 as 205.

Open: whether the CFE charge should be the rounded barcode/headline figure ($268, what is paid at the window — what the parser uses today) or the exact breakdown total ($268.88). One question for Jorge.