rmancinasandClaude Opus 5
e77e5546d8
Five documents asserted the SES_* secrets were unset in Gitea. They now exist, so all five are corrected rather than leaving the claim to rot in whichever one a reader opens first. Replaces the blocker with the two things creating the secrets does NOT establish, since both fail in ways that look identical to a missing config: SES_FROM must be a verified identity in SES_REGION, and the account must be out of the SES sandbox — in sandbox SES only delivers to verified recipients, so a sweep across 815 policyholders would fail almost every send while the configuration reads as correct. Recommends running the first sweep with debug on, which diverts every recipient and, on the pólizas side, leaves the avisos pending so a failed test consumes nothing. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
698 lines
53 KiB
Markdown
698 lines
53 KiB
Markdown
# 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`](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`](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`](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`](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:
|
||
```bash
|
||
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`):
|
||
```bash
|
||
./.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
|
||
|
||
> As-built reference: **`docs/STATEMENT_OCR.md`** (written 2026-08-02) — the
|
||
> parsers, the matcher's scoped-field rules, confirm/learning semantics and the
|
||
> API surface. `docs/RECEIPT_CAPTURE_SPEC.md` §2 stays the design and the
|
||
> measured evidence. This section is the session record of building it.
|
||
|
||
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.
|
||
|
||
## Policy OCR capture (`/polizas/captura`) — DONE 2026-08-01, unplanned
|
||
|
||
**This feature was not in any spec.** It is what the statement OCR work above
|
||
turned into once the pipeline existed. Having built render → OCR → parse →
|
||
match → review for CFE/CESPT/Telnor receipts, the same shape obviously fits
|
||
the *other* stack of paper this office keys in by hand every week: the carrier
|
||
policy PDFs behind every `Policy` row. Full write-up in `docs/POLICY_OCR.md`.
|
||
|
||
The pipeline was reused rather than copied. `OcrModule` was **extracted out of
|
||
`StatementsModule`** in the same commit so `PolicyOcrModule` could inject
|
||
`OCR_PROVIDER` without taking on the statement pipeline — that extraction was
|
||
blocking, not tidying; the policy module could not resolve the provider at all
|
||
until it existed. `StatementsModule` imports it now and binds nothing itself,
|
||
so the Tesseract-vs-managed-API decision stays one line in one file for both
|
||
features.
|
||
|
||
Screens mirror Captura exactly: `/polizas/nuevo` is the manual tab,
|
||
`/polizas/captura` the automática one, both rendering `PolicyCaptura.tsx`, with
|
||
the batch review queue at `/polizas/captura/[id]`. Abilities `policy:ingest` /
|
||
`policy:ocr-review`, both STAFF — same trust tier as statement OCR, and for the
|
||
same reason: nothing reaches the books unconfirmed.
|
||
|
||
**The statement pipeline's central assumption inverts here, and that is the
|
||
thing to remember.** Utility statements arrive bundled *one customer per page*,
|
||
so there a page is a document and the parser runs per page. A policy PDF is the
|
||
opposite: the GMX certificate is one policy spread across two pages (contract
|
||
header on page 1, the per-coverage table on page 2). So every page's text is
|
||
concatenated and the parser and matcher run **once per file**. Consequences:
|
||
`PolicyOcrDocument.pageNumber` is repurposed as the file ordinal within the
|
||
batch (the `(batchId, pageNumber)` unique constraint still holds), `ocrConfidence`
|
||
is the mean across the file's pages, and a file that fails to parse yields
|
||
exactly one `OCR_FAILED` row.
|
||
|
||
`storageKey` points at the **source PDF**, not a rendered page image, so the
|
||
review screen embeds the exact artifact the office received and gets the
|
||
browser's native PDF scrolling, zoom and text selection for free. The page PNGs
|
||
are still written for future re-OCR, but nothing treats them as the document's
|
||
identity. (The statement side is the reverse, because there a page *is* the
|
||
document.)
|
||
|
||
Findings worth keeping:
|
||
|
||
- **The GMX certificate has no premium on it at all.** Not intermittently
|
||
missing — the figure lives on GMX's separate `recibo` PDF. The parser leaves
|
||
the premium fields null and pushes a note saying so, confirm never overwrites
|
||
an existing `Policy.netPremium` with null, and the optional ledger write is
|
||
gated on staff ticking `postPremium` *and* a premium actually parsing.
|
||
Without that second gate a premium-less certificate would book a $0 charge on
|
||
every confirm.
|
||
- **Match on `Policy.policyNumber`, never the printed insured name.** Same
|
||
registrant-vs-current-owner drift that rules names out on the utility side.
|
||
Zero hits means a new policy and confirm creates the row under a picked
|
||
customer; more than one hit is surfaced for a human, never auto-picked —
|
||
duplicate numbers across related parties do occur.
|
||
- Deductible and loss participation are stored as **strings** (`"5%"`,
|
||
`"USD 1,000"`): they are printed as a mix of percentages, amounts and free
|
||
text, and normalising them would lose the distinction.
|
||
- Carrier-portal PDFs are usually **born-digital**, so the text layer wins and
|
||
no OCR runs at all most of the time — same precedence rule as the statement
|
||
pipeline.
|
||
- The digit-confusion map and the amount-by-separator-position parser are
|
||
**duplicated on purpose** rather than imported, to keep the module
|
||
self-contained. Fix a bug in one, check the other.
|
||
|
||
8/8 parser tests, all against verbatim text from one real document
|
||
(`HC_Folio_000767_Traduccion.pdf`).
|
||
|
||
**Open:** GMX is the only carrier implemented — the dispatcher is a
|
||
`[provider, pattern]` table plus a parser map, so a second carrier is a
|
||
function and two entries, but no other layout has been seen. Reading the
|
||
premium off the separate `recibo` PDF and pairing it to its certificate is the
|
||
obvious next piece; it is what would let `postPremium` stop being a manual
|
||
tick. And nothing versions a re-issued policy — confirm updates the existing
|
||
row, so there is no record that this is the 2027 issue of that number.
|
||
|
||
|
||
## Notificaciones (`/notificaciones`) — DONE 2026-08-01 → 08-02
|
||
|
||
Two features that were spec'd separately turned out to be one screen. The four
|
||
legacy mass-email jobs (`docs/MASS_EMAIL_NOTIFICATIONS.md`, ported from
|
||
`email.notifications/send*.php`) and the insurance renewal avisos
|
||
(`docs/INSURANCE_FEATURES_SPEC.md` §1) both mean *tell a customer something by
|
||
email*, so they are **tabs of one screen over one log**, not two menu entries.
|
||
`/renovaciones` is an alias that lands on the Pólizas tab, the same pattern
|
||
Captura uses.
|
||
|
||
- **Servicios tab** — the four jobs (pagos pendientes, confirmación de pago,
|
||
estado de cuenta, fideicomiso), individually or "Ejecutar todos". Ability
|
||
`notification:send` (MANAGER); STAFF sees the log read-only.
|
||
- **Pólizas tab** — pending avisos at 30/15 days before expiry and 7 days
|
||
after, sent one at a time or as a sweep. Ability `renewal:send` (MANAGER).
|
||
|
||
**One send log for the whole platform.** `email_notification_log` is not
|
||
job-specific: renewals write it too (`RENEWAL_NOTICE` / `POLICIES`) through the
|
||
same `NotificationLogService`. That is what makes "Registro de envíos" complete
|
||
— the failures and no-email skips exist *only* there. `RenewalNotice` was not
|
||
made redundant by it: that row is **gating** state (one per policy+generation,
|
||
drives the pending list), the log is **history** (every attempt). `level` is
|
||
therefore per-type and unreadable without its `notificationType` — 0/1
|
||
yellow/red on `ACCOUNT_STATUS`, the aviso generation 1/2/3 on
|
||
`RENEWAL_NOTICE`.
|
||
|
||
**Manual mark-as-sent was dropped on purpose.** The spec called for it; a
|
||
button that marks a notice sent without sending anything is a button that lets
|
||
the list claim a customer was told when they were not. `POST /renewals/send`
|
||
replaced it — sending from the list *is* the marking.
|
||
|
||
**`app_settings` is the operator-config seam this work introduced.**
|
||
`SettingsService` resolves every key **db → env → default** and reports which
|
||
rung a value came from, so an existing deployment keeps behaving exactly as it
|
||
did until somebody saves in the UI. Three keys today: the summary recipients
|
||
(was `NOTIFICATION_ADMIN_EMAILS`, now a fallback) and the two sweep cadences.
|
||
Credentials deliberately stay in the environment — SES keys, `DATABASE_URL`
|
||
and S3 config are deployment identity, must exist before the app can reach its
|
||
own database, and a table only widens who can read them.
|
||
|
||
**The send flags are global, and that was a real bug fix (08-02).** The
|
||
`debug` / `ignoreDayRestriction` / `useEmailLimit` panel lived inside the
|
||
Servicios tab, so there was **no way to test a renewal aviso without mailing a
|
||
real customer**. It now lives in the shell above the tabs and both halves read
|
||
it. On the pólizas path `debug` does three things, and all three are required
|
||
together: it diverts the mail, it skips the `RenewalNotice` upsert, and it does
|
||
not advance the sweep's `lastSuccessfulAt`. Miss the third and `renewalWindow()`
|
||
narrows back to a single day on the next real run — a test send would silently
|
||
destroy the letters it only pretended to send. Flags are per-visit UI state and
|
||
are **never persisted**; a stored `debug` would survive a reload and swallow
|
||
real customer mail until somebody noticed.
|
||
|
||
**Both cadences are operator-editable (08-02).** The renewal sweep's
|
||
`@Cron("0 6 * * *")` literal lasted one day. `NotificationScheduleService` now
|
||
owns both: the owning services register a handler in `onModuleInit`, the
|
||
service compiles the stored `{hour, minute, weekdays}` to a cron expression and
|
||
installs it in `SchedulerRegistry`, and saving from the UI reinstalls the job —
|
||
no restart, which was the point. It lives in its own module for the same reason
|
||
as `NotificationLogModule`: `NotificationsModule` and `RenewalsModule` both need
|
||
it and neither may import the other. Defaults preserve prior behaviour exactly
|
||
(pólizas 06:00 daily, servicios **off** — a default that starts mailing 260
|
||
customers after a deploy is not a default, it's an incident). A scheduled run
|
||
never inherits the UI flags: no `debug`, and no `ignoreDayRestriction`, since an
|
||
automatic run on the operator's own cadence is precisely the case the
|
||
Mon/Wed/Fri gate was written for.
|
||
|
||
Implementation notes worth keeping:
|
||
|
||
- `cron` had to become a **direct dependency of `apps/api`**. It is a
|
||
transitive dep of `@nestjs/schedule`, but pnpm's strict layout does not hoist
|
||
it, so `import { CronJob } from "cron"` does not resolve without it.
|
||
- The pólizas sweep already had a DB lock (`scheduled_job_states`); the
|
||
servicios run-all does not, and relies on the deployment being
|
||
single-replica, which it is on galactus today.
|
||
- Wire shapes of the four jobs are byte-for-byte the legacy PHP responses,
|
||
quirks included (Job 1 reports `result`, not `request`).
|
||
|
||
**Open:** the `SES_*` Gitea secrets were created 2026-08-02, so the feature is
|
||
no longer blocked — but it has not shipped (master is well past the newest tag)
|
||
and two things nobody has checked decide whether mail leaves the building:
|
||
`SES_FROM` must be a verified identity in `SES_REGION`, and the AWS account
|
||
must be out of the SES sandbox, which otherwise restricts delivery to verified
|
||
recipients and would fail a real sweep while looking correctly configured. Run
|
||
the first sweep with `debug` on. Still open beyond that: the 78 policyholders
|
||
with no email are logged as `SKIPPED_NO_EMAIL` but have no printable worklist,
|
||
and the notice body is English-only (`Customer` carries no language
|
||
preference).
|