# 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 ` reads `deploy/.env.` 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 ``` ## 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) — IMPLEMENTED, verification pending.** 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 now 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 now enforces provenance uniqueness for properties, policies, transactions, vehicles, and bank transactions. Sync intentionally skips prune/blob steps so manual customers and document pointers are not removed. Python compilation plus API/web production builds pass; still required before production use: push updated Prisma schema and run an end-to-end sync against a disposable/dev DB proving stable PKs, manual-row preservation, changed-row updates, and legacy-delete handling. ## 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): - API `cd apps/api && ./node_modules/.bin/nest start --watch` → `:3001` - Web `cd apps/web && ./node_modules/.bin/next dev` → `:3000` - 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).** `bank_transactions` has no currency column and every `amountInWords` is spelled out in PESOS — so, unlike the customer ledger, everything here is one currency and not split per-currency. (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. - Full pipeline reproducible in one command: `run_all.py --env ` runs customers → properties → policies → transactions → prune → 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, validation pending.** `run_all.py --sync` performs the non-destructive legacy upsert path for customers, properties, policies, transactions, and bank rows. It preserves manual rows and stable legacy-owned primary keys; the admin SYNC job automatically creates a pre-sync backup. Next validation: apply schema changes, then exercise sync against a disposable DB with added, changed, removed, and manually-created rows. - **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 — `/banco` was verified in-browser this session; `/estado-cuenta` still worth a look.