Legacy ran a year-end corte: it summed the closing year, wrote that total back
as each customer's Jan-1 BALANCE FORWARD, and started the next year clean. The
platform inherited those opening rows but never the years behind them — only
the current year's charges were ever staged, so every prior year held receipts
and no bills. Rendering one would have shown a customer credits with nothing
owed against them, which is worse than showing nothing.
The archives are whole-database Access snapshots named for the period they
hold, so `2025.accdb` is discovered by filename, staged, and loaded through the
existing DATOS2 branch — a snapshot's `datos2` is the identical shape, one year
older. Only the ledger and DATGRAL come out of a snapshot; everything else in
it is a year-stale copy of a live table. The cash side is deliberately left
behind: EFECTIVO is a lifetime journal, so the snapshot's copy is a subset of
the live one and importing it would double-book every prior-year receipt.
Period travels with the row, in legacySourceTable as `datos2@2025`. That tag,
not the date, is what a year view should filter on: the archives are not
cleanly bounded (2025 carries ten undated rows and two dated into 2026) and
legacy never filtered by date either — its reader is `SELECT ... FROM \`2025\``.
The tag also keeps legacyId safe, since it is a positional ordinal that
restarts at 0 in every archive and would otherwise collide row-for-row.
Two guards, because attaching a prior year by NUMid is the one thing here that
can go quietly wrong:
- Reissued numbers are skipped, not imported. Comparing each archive's
DATGRAL against the live one, 13 names moved since 2025 and 40 since 2024;
most are the same customer re-described, but a few are a different
household holding a recycled number, and filing their ledger under the new
owner would show a stranger's charges. Sharing any word of three or more
characters separates a rename from a reissue. Names are compared
legacy-to-legacy: `customers.name` has been through blank-name recovery,
and comparing to it reported 121 drifts where there are 13.
- Every period is checked against the corte identity it must satisfy —
SUM(year N) == BALANCE FORWARD(N+1) — and the result is reported per year.
A truncated export, a file dropped under the wrong year, or a botched
customer match all fail loudly here. 2025 reconciles 1,159/1,167 (99.3%)
and 2024 1,144/1,156 (99.0%); the recycle guard raised 2024 from 98.2%.
Balances are untouched: BALANCE_FLOOR_JOIN floors on the newest BALANCE FORWARD
per customer, so rows behind it are already excluded from every balance read.
Uploads go through the existing ingest endpoint, allowlisted by an anchored
`AAAA.accdb` pattern that also keeps a caller-supplied name inside the ingest
directory. The Operaciones page grows an entry point for an archive that has no
row yet, reading the period off the chosen file's own name.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
datosfreak's NOPAGO is the legacy "still owed" flag, and the website reads
it directly — account.statement.php splits the statement on NOPAGO = 0 vs
NOPAGO = 1 and renders the latter as "Outstanding Bills Requiring
Attention". transform_transactions.py hardcoded 0, so all 40,421 rows came
across settled and that section renders empty for anyone served off the
platform. Not a missing column: a missing section, with no error.
Only the three DATOS2-shaped tables carry the flag (76 rows set in datos2,
0 in FEE ANUAL and fee15); the EFECTIVO/FM3 cash streams have no such
column and keep the 0 default. Sync mode gets outstanding=VALUES(...) too,
so an additive sync corrects rows already loaded rather than leaving them
settled forever.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The EFECTIVO ledgers have no type column — in Access the transaction type
is implied by which table a row lives in — so unlike DATOS2 there was no
string to map and typeId came out NULL on all 13,496 rows.
That is not just a blank label. handleGetAccountDetails in
my.jorgecuadros.com identifies payments by matching TYPEOFTRX against
('PAYMENT THANK YOU', 'PAYPAL', 'CASH DEPOSIT', 'CHECK DEPOSIT') to reset
the running balance in mode=current. An unlabelled payment is not
recognised, so the balance silently diverges from legacy — 285 rows across
129 customers in the current year alone.
"CASH DEPOSIT" is measured, not chosen: matching the unlabelled rows to the
live site on (NUMid, date, amount) resolves unanimously to that label —
66/66 in the current-year `datosfreak` and 100/100 in the prior-year `2025`
table, the only two periods the site allowlists.
The FM3 fee streams (EFECTIVO FM3 627, CHEQUE FM3 157) have the same
missing-type problem and are deliberately left NULL: every row predates both
exposed periods, so nothing can be matched against a legacy label and none
can reach a customer. Guessing "CHECK DEPOSIT" there would feed the
payment-detection list on no evidence.
type_id_for(None) returns None, so call sites without a label are unchanged.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Two fields the customer-facing site reads were being dropped on the way in
from Access.
transform_transactions.py mapped DATOS2's type string through the Access
`TYPE OF TRX` table and stored NULL on a miss. That table is a stale
pick-list rather than a constraint — staff free-text straight into DATOS2 —
so 78 distinct values covering 3,939 rows never resolved, including
BALANCE FORWARD (1,188) and ANNUAL FEE (1,116). Nothing else on
`transactions` carries the type text, so those rows lost their label
outright and rendered blank. Now mints a type_transactions row from the
literal string when the lookup lacks it; nameEs stays NULL since only the
lookup has translations.
transform_customers.py never carried DATGRAL.TIPO, leaving
customers.minimumBalance empty on every row despite the column existing.
TIPO is the minimum-balance threshold (100/200/300/500; 1,017 of 1,172
customers carry one), not an account type as the name suggests — the
customer app shows it as `minBalance`. Added to the insert list and to the
ON DUPLICATE KEY UPDATE clause, without which --sync would silently skip
it on existing rows.
Both land on the next `run_all.py --sync` reload.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The --sync path had never been run and was broken in several ways. Fixed and
verified against the dev DB (two consecutive syncs, both exit 0, 32/32
assertions: stable PKs, manual-row preservation, changed-row updates,
legacy-delete, no child duplication, zero FK orphans; idempotent).
- policies/properties: reuse each legacy row's existing id (by provenance)
BEFORE building child rows, so children no longer point at a discarded fresh
uuid; rebuild legacy-owned children via scoped delete + reinsert.
- customers: replace zip(customers, refs) (mispaired almost every row) with a
ref-grouped id remap; names now restore and no spurious customers appear.
- drop the invalid Vehicle @@unique(legacySourceTable, legacyId) — one legacy
policy row carries up to 3 vehicles sharing a legacyId; handle via delete+reinsert.
- upsert lookup tables (policy_types, insurance_providers, type_transactions,
adjusters) by natural name and remap child FKs instead of inserting fresh
uuids that nothing points at.
- transactions: drop updatedAt=NOW() (no such column); guard report formatting
on NULL legacySourceTable (manual rows). Same report guard in bank.
- add manual-safe prune (prune_empty_customers.py --sync, in SYNC_STEPS): prune
only legacy-owned empties, never manually-added customers.
web: customer-detail mini tx list now strikes voided rows with an "(anulado)"
tag (was the last void-UI rendering gap; /estado-cuenta already handled it).
docs: RESUME.md updated — Phase B sync marked verified end-to-end, void-UI
browser pass recorded.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Working-tree checkpoint of in-progress work carried across prior
sessions on the feat/crud-rbac branch, committed so it lands on the
remote alongside the CI changes.
- Operaciones admin panel: apps/api/src/ops (ingest upload, backup /
restore / re-import jobs) wired into app.module + RBAC abilities, and
the apps/web/src/app/operaciones page. docker-compose gets INGEST_DIR
/ BACKUP_DIR volumes; .gitignore excludes migration/ingest + backups.
- migration/sync.py plus transform_*.py / run_all / config / dbenv /
blob_extract adjustments for the additive sync path.
- crud/rbac phase-5 web bits: AppShell, api/labels/types libs, globals.
- schema.prisma + PLAN/RESUME doc updates.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The reconciliation pass ruled EFECTIVO and EFECTIVO_BACKUP "near-disjoint
ledgers" and the transform loaded both in full. That verdict was a bug, not
a finding.
reconcile.py compared the business key (cl, fecha, monto, conepto) as raw
strings, on the stated premise that "every table went through the same
mdb-export path, so identical source values serialize identically". They
don't: mdb-export formats a numeric column from its Access column type, so
the same amount is emitted as `5000` from one table and `27000.0000` from
the other. No two rows could ever match on `monto`, which is why the pass
reported 2 overlapping rows.
Canonicalizing numeric key columns first shows 12386 of EFECTIVO_BACKUP's
12387 rows already exist verbatim in EFECTIVO — same customer, same
timestamp to the second, same amount, same concept text — leaving exactly
one genuinely new row. The ledger was carrying 12386 duplicated payments,
roughly doubling every customer's historical receipt total.
- reconcile.py: add canon(), which parses a key column to a number when
nearly every populated cell parses and re-emits it at fixed precision.
Applied in keyset() and in the folio-conflict comparison. Rewrite the
group-1 verdict and the module docstring's method note.
- transform_transactions.py: share a business-key `seen` set between the
two efectivo_like() calls. EFECTIVO loads first and wins collisions.
De-dup on the business key, never on folio — folio is per-table
sequential and collides on 12204 different payments.
- Regenerate RECONCILIATION.md. Groups 2 and 3 re-checked under the fix;
their verdicts are unchanged.
Ledger after re-running run_all.py --env dev: 45861 -> 33475 rows.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
migration/transform_transactions.py unions every cash/billing ledger into
`transactions` per the reconciliation rules: both EFECTIVO tables (no folio
de-dup, near-disjoint), all three billing tables (disjoint periods), the FM3
fee stream (amount = fee+tax+multa), IVA 2015 (nominal date), and insurance
EFECTIVO (domain INSURANCE). Also loads the type_transactions (EN/ES) and
exchange_rates lookups. Customer FK resolves through customer_legacy_refs;
rows with no resolvable customer/date are skipped and counted.
Loaded (dev): 45861 transactions (UTILITY 45566 / INSURANCE 295, 0 orphans),
79 type_transactions, 2301 exchange_rates.
migration/transform_bank.py loads SCOTHIA DATOS I/E into bank_transactions as
signed amounts (income +, expense -) and TABLA RAMODOS into
business_line_categories. Deliberately customer-independent (office's own
checking account). Loaded (dev): 22354 bank_transactions (net +899,375.77),
66 categories; categoryId left null (concept->ramo classifier is future work).
run_all.py: pipeline now customers -> properties -> policies -> transactions
-> bank, all idempotent. Verified full end-to-end run against dev.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
rmancinasandClaude Opus 5